• 【Mysql学习】MySQL 常见错误

    转载：https://www.cnblogs.com/keme/p/10972415.html

     1. Too many connections

    ERROR 1040 (HY000): Too many connections

    导致结果：

    连接数过多，导致连接不上数据库，业务无法正常进行

    该错误发生在有max_connections个客户连接了mysqld服务器， 应该重启mysqld， 用更大的max_connections变量值

    1
    2
    3
    4
    5
    6
    7
    8
    #默认连接数
    mysql> show variables like '%max_connection%';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
    1 row in set (0.00 sec)

    　　

    解决问题思路：

    1、首先先要考虑在我们 MySQL 数据库参数文件里面，对应的 max_connections 这个参数值是不是设置的太小了，导致客户端连接数超过了数据库所承受的最大值。

    • 该值默认大小是 151，可以根据实际情况进行调整。

    • 对应解决办法：set global max_connections=500

    这样调整会有隐患，因为我们无法确认数据库是否可以承担这么大的连接压力，就好比原来一个人只能吃一斤牛肉，但现在却非要让他吃 10斤牛肉，他肯定接受不了。反应到服务器上面，就有可能会出现宕机的可能。

    所以这又反映出了，在新上线一个业务系统的时候，要做好压力测试。保证后期对数据库进行优化调整。

    2. Packet too large

    结果：

    如果写入大数据时，因为默认的配置太小，插入和更新操作会因为 max_allowed_packet 参数限制，而导致失败。

    mysql根据max_allowed_packet参数来限制server接受的数据包大小。

    当一个MySQL客户或mysqld服务器得到一个max_allowed_packet个字节长的包， 它发出一个Packet too large错误并终止连接。

    1
    2
    3
    4
    5
    6
    mysql> show variables like 'max_allowed_packet';
    +--------------------+---------+
    | Variable_name      | Value   |
    +--------------------+---------+
    | max_allowed_packet | 4194304 |
    +--------------------+---------+

    默认是4M大小

    可以使用mysqld的命令行选项设置max_allowed_packet为一个更大的尺寸。 例如， 如果将一个全长的BLOB存入一张表中， 需要用max_allowed_packet=24M选项来启动mysql。

    Max_allowed_packet的取值范围是1024B~1GB

    当然不要乱设置，根据具体环境要求，设置太大业务

    1
    2
    3
    4
    # 具体设置max_allowed_packet大小
    mysql> set @@global.max_allowed_packet=
    #在my.cnf 加入这个
    max_allowed_packet= 10M

    　

    3. 线上要修改mysql参数，怎么避免mysql 重启

    首先确定改参数是动态参数还是静态参数

    如果是静态参数还是要重启服务才会生效，动态参数则不用

    这时候要修改全局变量, 必须要显示指定"GLOBAL"或者"@@global.", 同时必须要有SUPER权限.

    例如修改最大连接数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    #默认连接数
    mysql> show variables like '%max_connection%';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
    1 row in set (0.00 sec)
     
    #修改连接数为500
    mysql> set @@global.max_connections=500;
    Query OK, 0 rows affected (0.00 sec)
     
    #查看是否修改成功
    mysql> show variables like '%max_connection%';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 500   |
    +-----------------+-------+
     
    # 在my.cnf 的[mysqld]下面加上 max_connections=500就可以了，也不用重启服务

    　　

    4. root密码忘了怎么办

    忘记了MySQL的root用户的口令 在my.cnf中添加skip-grant-tables=1选项重启mysqld

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    [root@mysql-150 ~]# mysql -u root -h 127.0.0.1
    mysql> flush privileges;
    mysql> grant all privileges on *.* to root@'localhost' identified by '456789';
    mysql> exit
    # 在my.cnf 将skip-grant-tables=1选项去掉
    # 重启mysqld之后就可以用最新的密码登录
    [root@mysql-150 ~]# vim /etc/my.cnf
    [root@mysql-150 ~]# service mysql restart
    Shutting down MySQL............ SUCCESS!
    Starting MySQL. SUCCESS!
    [root@mysql-150 ~]# mysql -u root -p456789 -h 127.0.0.1

    　　

    5. 账号被锁定

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    # 创建一个用户
    mysql> create user keme@'localhost' identified by '123456';
    # 给一个只读权限
    mysql> grant select on *.* to keme@'localhost';
     
    # 可以从本地登录
    [root@mysql-150 ~]# mysql -u keme -p123456
     
    # 把keme@'localhost' 给lock住，不让其使用
    mysql> alter user keme@'localhost' account lock;
     
    # 在看看能不能从本地登录
    [root@mysql-150 ~]# mysql -u keme -p123456
    mysql: [Warning] Using a password on the command line interface can be insecure.
    ERROR 3118 (HY000): Access denied for user 'keme'@'localhost'. Account is locked.
     
    # 查看该用户是否锁定
    mysql> select host,user,account_locked from mysql.user where user='keme';
    +-----------+------+----------------+
    | host      | user | account_locked |
    +-----------+------+----------------+
    | localhost | keme | Y              |
    +-----------+------+----------------+
    Y已锁定
    # 然后解锁该keme用户
    mysql> alter user keme@'localhost' account unlock;
     
    # 再去登录keme用户
    [root@mysql-150 ~]# mysql -u keme -p123456

    　　

    6. 环境变量未设置

    例如执行： mysqldump提示： -bash： command not found 是 环境变量设置的问题

    临时添加：

    1
    2
    # 首先要确定mysql 的安装位置
    shell> export PATH=$PATH:/usr/local/mysql/bin

    　　

    永久设置：

    1
    2
    3
    4
    # 在/etc/profile 中末尾添加
    PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
    export PATH
    保存退出后执行： source /etc/bash_profile即可。

    　　

    7. SQL MODE

    MySQL服务器可以以不同的SQL模式来操作， 并且可以为不同客户端应用不同模式。 这样每个应用程序可以根据自己的需求来定制服务器的操作模式

    模式定义MySQL应支持哪些SQL语法， 以及应执行哪种数据验证检查。 这样可以更容易地在不同的环境中使用MySQL， 并结合其它数据库服务器使用MySQL。

    查看当前的sql_mode

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> show variables like 'sql_mode';
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | Variable_name | Value                                                                                                                                     |
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
     
    mysql> select @@sql_mode;
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | @@sql_mode                                                                                                                                |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +-------------------------------------------------------------------------------------------------------------------------------------------+

    　　

    7.1 主要的SQL_Mode值包括：

    • ANSI

    更改语法和行为， 使其更符合标准SQL。

    • STRICT_TRANS_TABLES
    • TRADITIONAL

    使MySQL的行为象“传统”SQL数据库系统。 该模式的简单描述是当在列中插入不正确的值时“给 出 错误 而不是警告” 等同STRICT_TRANS_TABLES、 STRICT_ALL_TABLES、NO_ZERO_IN_DATE、 NO_ZERO_DATE、 ERROR_FOR_DIVISION_BY_ZERO、 NO_AUTO_CREATE_USER。

    sql mode常用值

    •  ONLY_FULL_GROUP_BY

    对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

    但这有个条件：如果查询是主键列或是唯一索引且非空列，分组列根据主键列或者唯一索引且空（null）则sql 分组查询有效

    • NO_AUTO_VALUE_ON_ZERO

     该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

    • STRICT_TRANS_TABLES

    在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

    为事务存储引擎启用严格模式， 也可能为非事务存储引擎启用严格模式。

    严格模式控制MySQL如何处理非法或丢失的输入值。 有几种原因可以使一个值为非法。 例如， 数据类型错 误， 不适合列， 或超出范围。 当新插入的行不包含某列的没有显示定义DEFAULT子句的值，则该值被丢失。 对于事务表， 当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时， 如果语句中有非法或丢失值， 则会出现错误。 语句被放弃并回滚。

    •  NO_ZERO_IN_DATE

    在严格模式下，不允许日期和月份为零

    • NO_ZERO_DATE

    设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

    • ERROR_FOR_DIVISION_BY_ZERO

    在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL

    • NO_AUTO_CREATE_USER

    禁止GRANT创建密码为空的用户

    • NO_ENGINE_SUBSTITUTION

    如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

    • PIPES_AS_CONCAT

    将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

    举例：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    # 创建一个测试表
    CREATE TABLE `employee` (
    `eid` int(11) NOT NULL,
    `ename` varchar(64) DEFAULT NULL,
    `sex` int(11) DEFAULT NULL,
    PRIMARY KEY (`eid`)
    ) ENGINE=InnoDB;
    # 插入几条数据
    insert into employee (eid,ename,sex) values (1,'keme',18),(2,'xixi',22),(3,'yj',18),(4,'kk',18),(5,'yy',18),(6,'xx',35);
     
    # 设置当前会话的sql_mode为如下
    mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
     
    mysql> select eid,ename,count(*) from employee group by ename;
    +-----+-------+----------+
    | eid | ename | count(*) |
    +-----+-------+----------+
    |   1 | keme  |        1 |
    |   4 | kk    |        1 |
    |   2 | xixi  |        1 |
    |   6 | xx    |        1 |
    |   3 | yj    |        1 |
    |   5 | yy    |        1 |
    +-----+-------+----------+
     
    # 重新设置当前的sql_mode 为如下
    mysql> set @@sql_mode='ONLY_FULL_GROUP_BY';
    mysql> select eid,ename,count(*) from employee group by ename;
    ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'beta.employee.eid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    mysql> select eid,ename ,count(*) from employee group by eid;
    +-----+-------+----------+
    | eid | ename | count(*) |
    +-----+-------+----------+
    |   1 | keme  |        1 |
    |   2 | xixi  |        1 |
    |   3 | yj    |        1 |
    |   4 | kk    |        1 |
    |   5 | yy    |        1 |
    |   6 | xx    |        1 |
    +-----+-------+----------+
    6 rows in set (0.00 sec)
     
     
     
    mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
    mysql> insert into employee values(7,'ke','male');
    ERROR 1366 (HY000): Incorrect integer value: 'male' for column 'sex' at row 1
     
    mysql> set @@sql_mode='ANSI';
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> select @@sql_mode;
    +--------------------------------------------------------------------------------+
    | @@sql_mode                                                                     |
    +--------------------------------------------------------------------------------+
    | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
    +--------------------------------------------------------------------------------+
     
    #改成ANSI模式就可以插入成功了，只不过识别成了0
    mysql> insert into employee values(7,'ke','male');
    Query OK, 1 row affected, 1 warning (0.01 sec)
     
    mysql> select * from employee where eid=7;
    +-----+-------+------+
    | eid | ename | sex  |
    +-----+-------+------+
    |   7 | ke    |    0 |
    +-----+-------+------+
    1 row in set (0.00 sec)
     
     
    mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
     
    mysql> select @@sql_mode;
    +--------------------------------------------+
    | @@sql_mode                                 |
    +--------------------------------------------+
    | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
    +--------------------------------------------+
    1 row in set (0.00 sec)
     
    mysql> insert into employee values (8,'ww',17/0);
    Query OK, 1 row affected (0.00 sec)
     
    mysql> select * from employee  where eid=8;
    +-----+-------+------+
    | eid | ename | sex  |
    +-----+-------+------+
    |   8 | ww    | NULL |
    +-----+-------+------+
     
     
    mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
     
    mysql> select @@sql_mode;
    +-----------------------------------------------------------------------+
    | @@sql_mode                                                            |
    +-----------------------------------------------------------------------+
    | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
    +-----------------------------------------------------------------------+
    1 row in set (0.00 sec)
     
    mysql> insert into employee values (9,'ee',18/0);
    ERROR 1365 (22012): Division by 0
     
    mysql> alter table employee modify ename varchar(5);
    mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
    mysql> insert into employee values (9,'qweradsf',11);
    ERROR 1406 (22001): Data too long for column 'ename' at row 1
     
    mysql> set @@sql_mode='ANSI';
    mysql> insert into employee values (9,'qweradsf',11);
    mysql> select * from employee where eid=9;
    +-----+-------+------+
    | eid | ename | sex  |
    +-----+-------+------+
    |   9 | qwera |   11 |
    +-----+-------+------+
     
     
    mysql> set @@sql_mode='TRADITIONAL';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
     
    mysql> select @@sql_mode;
    TRADITIONAL模式有如下值：
    |STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

    　　

    8. 用户的资源限制

    MySQL提供了对每个用户的资源限制管理

    MAX_QUERIES_PER_HOUR ： 一个用户在一个小时内可以执行查询的次数（基本包含 所 有 语 句 ）

    MAX_UPDATES_PER_HOUR：一个用户在一个小时内可以执行修改的次数（仅包含修 改数据库或表的语句）

    MAX_CONNECTIONS_PER_HOUR：允许用户每小时连接的次数

    MAX_USER_CONNECTIONS：一个用户可以在同一时间连MySQL实例的数量

    通过执行create user/alter user设置/修改用户的资源限制

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # 创建一个用户并设置其资源限制
    CREATE USER 'keme1'@'localhost' IDENTIFIED BY
    '123456' WITH MAX_QUERIES_PER_HOUR 20
    MAX_UPDATES_PER_HOUR 10
    MAX_CONNECTIONS_PER_HOUR 5
    MAX_USER_CONNECTIONS 2;
    #keme1 这个用户 一个小时可以查询20次， 修改10次，一个小时可以连接5次，同一时刻只允许两个用户
     
    #取消某项资源限制既是把原先的值修改成0
    mysql> alter user 'keme1'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
     
    # 当针对某个用户的max_user_connections非0时， 则忽略全局系统参数max_user_connections， 反之则全局系统参数生效

    　　

    9. 主从同步错误

    一般主从同步错误首先要考虑是不是在从库中误操作导致的。结果发现，有人在从库中进行了一条针对有主键表的 sql 语句的插入，导致主库再插入相同 sql 的时候，主从状态出现异常。发生主键冲突的报错。

    解决方法：

    在确保主从数据一致性的前提下，可以在从库进行错误跳过。

    像从库如果不提供什么服务的话可以在从库中开启 read_only 参数，禁止在从库进行写入操作，还有用户必须没有super 权限，设置read_only才会生效。

    9.1 一般主从复制错误的解决办法

    这是正常的状态

    10.0.0.150 是主

    10.0.0.151 是从

    先模拟故障

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    # 这是本次的表结构
    mysql> show create table students;
    | students | CREATE TABLE `students` (
      `sid` int(11) NOT NULL,
      `sname` varchar(20) DEFAULT NULL,
      `sex` int(11) DEFAULT NULL,
      PRIMARY KEY (`sid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    #主库执行，是个空表
    mysql> select * from students;
    Empty set (0.00 sec)
     
    #在从库 ，给students 加1条数据：
    mysql> insert into students  values (1,'keme',0);
    mysql> show slave statusG;
    ...
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
    ...
    看主从状态是正常的
    # 从库查看students 数据
    mysql> select * from students;
    +-----+-------+------+
    | sid | sname | sex  |
    +-----+-------+------+
    |   1 | keme  |    0 |
    +-----+-------+------+
     
     
    # 在主库查看students 表
    mysql> select * from students;
    Empty set (0.00 sec)
     
    # 插入相同主键的值
    mysql> insert into students values (1,'keme',1);
     
    # 查看students表
    mysql> select * from students;
    +-----+-------+------+
    | sid | sname | sex  |
    +-----+-------+------+
    |   1 | keme  |    1 |
    +-----+-------+------+
     
     
    # 查看从库状态
    mysql> show slave statusG;
    ...
    Slave_IO_Running: Yes
    Slave_SQL_Running: No
    Last_SQL_Error: Could not execute Write_rows event on table beta.students; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000023, end_log_pos 11789
    ...
    # 主从状态不一致了，造成的原因是主键冲突

    　　

    解决办法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    # 停止从库
    mysql> stop slave;
     
    # 在从库删除主键冲突的那条语句， 把主库执行的那条语句在从库执行
    mysql> delete from students where sid=1;
    mysql> insert into students values (1,'keme',1);
     
    # 同步跳过临时错误
    mysql> set global sql_slave_skip_counter = 1;
    mysql> start slave;
    mysql> show slave statusG;
    ...
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    ...
     
    # 主库再次插入数据，看看从库是不是能够同步
    mysql> insert into students  values (2,'keme',1);
     
    # 从库查看
    mysql> select * from students;
    +-----+-------+------+
    | sid | sname | sex  |
    +-----+-------+------+
    |   1 | keme  |    1 |
    |   2 | keme  |    1 |
    +-----+-------+------+
    2 rows in set (0.00 sec)
     
    # OK,同步成功了， 一般主从错误也就解决了

    　　

    那这时候有问题，主从问题不一致了， 主上面插入了很多数据， 这时候该怎么解决了。

    首先主从问题不一致了，你的监控预警机制了，给你发短信或者钉钉，这时候你应该尽快去修复从库，比如就像上面跳过临时同步错误，暂时让其恢复正常同步。

    其次 后期就是用pt工具：比如用pt-table-checksum 找出主从表数据不一致的， pt-table-sync进行修复从库

     

    9.2 GTID 主从复制错误解决办法

    现在修改我的主从模式为GTID，这是我的测试环境随便改，

    生产环境不能这样瞎改

    搭建GTID主从时，需要注意的 mysql 参数：

    server_id：设置 mysql 实例的 server_id,每个实例的server_id必须不一样

    gtid_mode=on：MYSQL 实例开启GTID 模式。

    enforce_gtid_consitency=on ：使用GTID模式复制时，需要开启此参数，用来保证GTID的一致性。

    log-bin=on ：Msql 做主从必须开启binlog

    log-slave-updates=1 ：觉得slave 从master 接收到的更新且执行完之后，执行的binlog是否记录到slave的binlog中，建议开启

    binlog_format=row ：强烈建议binlog_format使用row格式 在mysql 5.7.6 版本以后默认就是row

    skip-slave-start=1 ：当slave 数据库启动的时候，slave 不会自动开启复制

     

    主库操作，在[mysqld] 加一下参数，我这个做过主从， 只加一部分参数

    1
    2
    3
    4
    5
    # my.cnf 中内容
    [mysqld]
    gtid-mode=on
    enforce-gtid-consistency=on
    log-slave-updates=1

    　　

    从库操作

    1
    2
    3
    4
    5
    6
    # my.cnf 中内容
    [mysqld]
    gtid-mode=on
    enforce-gtid-consistency=on
    log-slave-updates=1
    skip-slave-start=1

    重启主从数据库

    　

    在从库 操作重新设置主从库的复制关系

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> CHANGE MASTER TO
    MASTER_HOST = '10.0.0.150',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = '123456',
    MASTER_AUTO_POSITION = 1;
    mysql> start slave;
     
    #查看主从状态
    mysql> show slave statusG;
    ...
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
    ...

    　　

     

    如果是在GTID模式下出现复制报错， 则使用SQL_SLAVE_SKIP_COUNTER语句会报错

    在GTID 模式的复制情况下，如果slave 发生错误，则可以通过跳过该事务的方式恢复主从复制。

     现在人为制造slave错误

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    # 在从库的sutdents 表插入一条数据
    mysql> select * from students;
    +-----+-------+------+
    | sid | sname | sex  |
    +-----+-------+------+
    |   1 | keme  |    1 |
    |   2 | keme  |    1 |
    +-----+-------+------+
    mysql> insert into students values (3,'keme',0);
     
    # 在查看从库的数据
    mysql> select * from students;
    +-----+-------+------+
    | sid | sname | sex  |
    +-----+-------+------+
    |   1 | keme  |    1 |
    |   2 | keme  |    1 |
    |   3 | keme  |    0 |
    +-----+-------+------+
     
    # 主库也插入主键为3这条数据，引发主从同步错误
    mysql> insert into students values (3,'keme',1);

    　　

     主从报错了：

     从图中可以看出，出错事务的binlog文件为mysql-bin.000026

    开始位置(Exec_Master_Log_Pos)是154 ，结束位置是(end_log_pos ) 395,可以去主库分析下binlog ，看一下发生冲突的事务是哪个。

    可以看到接收并且执行了GTID事件 是

    1
    2
    3
    4
    5
    6
    从库执行了这些
    5a13910d-1496-11e9-8375-000c29f859ce:1-3,
    f6c31435-38dd-11e9-ac93-000c299bcbee:1-53096
     
    收到却没执行的事务号：
    Retrieved_Gtid_Set: f6c31435-38dd-11e9-ac93-000c299bcbee:53097

    　　

    可以看出发现冲突的事务号是：f6c31435-38dd-11e9-ac93-000c299bcbee:53097，这时候就要确定哪一个事务发生了冲突，还可以直接从show slave statusG;结果中通过比对的方式找到冲突位置。

    严谨起见，通过对binlog 内容分析得知冲突事务是插入了一条数据，主键为3。在从库中查看这条记录是否真的存在

    1
    2
    3
    4
    5
    6
    mysql> select * from students where sid=3;
    +-----+-------+------+
    | sid | sname | sex  |
    +-----+-------+------+
    |   3 | keme  |    0 |
    +-----+-------+------+

    　　

    发现slave 中存在这条记录了，这时，可以通过跳过该事务的方式来放弃该事务在slave上的执行，使slave 能够正常运行。

    基于GTID模式的复制，跳过一个事务，需要利用一个空事务来完成。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    mysql> stop slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
     
    mysql> set GTID_NEXT='f6c31435-38dd-11e9-ac93-000c299bcbee:53097';
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> begin;commit;
    Query OK, 0 rows affected (0.00 sec)
     
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> set GTID_NEXT='AUTOMATIC';
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)

    　　

    查看slave 状态

     哪主从库数据是否一致，就看3那条

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    # 主库数据
    mysql> select * from students where sid=3;
    +-----+-------+------+
    | sid | sname | sex  |
    +-----+-------+------+
    |   3 | keme  |    1 |
    +-----+-------+------+
     
    #从库数据
    mysql> select * from students where sid=3;
    +-----+-------+------+
    | sid | sname | sex  |
    +-----+-------+------+
    |   3 | keme  |    0 |
    +-----+-------+------+

    　　

    解决不一致数据：

    1 手动修改或者插入

    2 用pt用具 来修复或者检查不一致数据

     

    由于我这是我的本地环境，我只手动修改数据,再看主从状态

    1
    2
    3
    mysql> update students set  sex=1 where sid=3;
    Query OK, 1 row affected (0.03 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    注：不止是要主从解决错误，还要主从数据的一致性　

    如果是生产的核心库主从不一致，一定要查明原因，不然老是 dba 或者运维 背锅

    还有如果主从不一致性实在是太多太多不一致了，就重做数据库吧

    如果检查的数据某几张表不一致的情况下，可以把这几张道出来，恢复到从库

    10. 数据库总会出现中文乱码的情况

    为什么我的数据库总会出现中文乱码的情况。一堆中文乱码不知道怎么回事?当向数据库中写入创建表，并插入中文时，会出现这种问题。此报错会涉及数据库字符集的问题。

    10.1 解决乱码的几个方面

    对于中文乱码的情况，从三个方面

    • 数据终端： 就是我们连接数据库的工具设置为utf8
    • 操作系统层面：linux 系统通过 在命令争端查看当前编码echo $LANG或者locale

    如何修改了系统编码了：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # centos 6.x 版本是/etc/sysconfig/i18n
    修改这个文件
    shell> vim /etc/sysconfig/i18n
    # 这一行改为utf8
    LANG=en_US.UTF-8
    # 修改完，不要重启，立即生效如下
    shell> source /etc/sysconfig/i18n
     
     
    # centos 7.x 版本是/etc/locale.conf 这个文件
    [root@mysql-150 ~]# vim /etc/locale.conf
    LANG="en_US.UTF-8"
    #立即生效
    [root@mysql-150 ~]# source /etc/locale.conf

    　　

    • 数据库层面：

    在参数文件中的[mysqld] 下，加入相应utf8字符集

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    # 注意数据库的系统版本
    5.6.x 和 5.7.x设置字符集参数不一样，8.x和5.7.x设置是一样的
    #查看当前数据库的字符集参数，查看当前字符集参数
    mysql> show variables like '%character%';
     
    # 查看数据库支持的字符编码，和编码的排序规则
    mysql> show character set;
     
    # 修改sutdents表中sname 字段的字符编码
    mysql> alter table students modify sname varchar(66) character set gbk;
    Query OK, 3 rows affected (0.06 sec)
    Records: 3  Duplicates: 0  Warnings: 0
     
    #看看表结构
    mysql> show create table students;
    ...
    | students | CREATE TABLE `students` (
      `sid` int(11) NOT NULL,
      `sname` varchar(66) CHARACTER SET gbk DEFAULT NULL,
      `sex` int(11) DEFAULT NULL,
      PRIMARY KEY (`sid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    ...
     
    # 查看连接级字符集和排序规则
    mysql> show variables like '%collation%';
    +----------------------+-----------------+
    | Variable_name        | Value           |
    +----------------------+-----------------+
    | collation_connection | utf8_general_ci |
    | collation_database   | utf8_general_ci |
    | collation_server     | utf8_general_ci |
    +----------------------+-----------------+

    　　

    从上面示例可以得出：

    如果修改数据库字符集，需要从以下考

    • 列级别字符集

    • 表级别字符集

    • 库级别字符集

    • mysql 实例字符集

    10.2 怎么合理修改mysql字符集了

    在/etc/my.cnf 加一下参数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    vim /etc/my.cnf
    [mysqld]
    init-connect='SET NAMES utf8'
    character-set-server=utf8
    然后去数据库操作：
    mysql> set @@global.character_set_server=utf8;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> set @@global.init_connect='SET NAMES utf8';
    Query OK, 0 rows affected (0.00 sec)
    # 注 用户操作的时候看看有没有super权限，对super用户权限 set names 不生效

    　　

    有人说，修改完还是乱码， 这时候就乱码是哪个库的字符集，哪个表的字符集，哪个字段的字符集，还有操作系统字符集，程序连接的字符集，这些都的查看。

    在/etc/my.cnf中init-connect='SET NAMES utf8'是什么意思:

    让每个客户端连接都自动设置字符集，但缺点是对拥有super权限的用户不生效

    init_connect表示服务器为每个连接的客户端执行的字符串。字符串由一个或多个SQL语句组成。要想指定多个语句，用分号间隔开 。

     比如：

    1
    2
    3
    4
    5
    6
    # 举例init_connect
    mysql> SET @@GLOBAL.init_connect='SET AUTOCOMMIT=0;set names
    utf8';
    shell> vim my.cnf
    [mysqld]
    init_connect='SET AUTOCOMMIT=0;set names utf8'

    10.3 连接级字符集和排序规则

    • 每个数据库客户端连接都有自己的字符集和排序规则属性，

      客户端发送的语句的字符集是由character_set_client决定，

      而与服务端交互时会根据character_set_connection和collation_connection两个参数将接收到的语句转化。当涉及到显示字符串的比较时，由collation_connection参数决定，

      而当比较的是字段里的字符串时则根据字段本身的排序规则决定

    • character_set_result参数决定了语句的执行结果以什么字符集返回给客户端

    • 客户端可以很方便的调整字符集和排序规则，比如使用SET NAMES 'charset_name' [COLLATE 'collation_name']表明后续的语句都以该字符集格式传送给服务端，而执行结果也以此字符集格式返回。

    　　

    set names 字符集

    1
    2
    3
    4
    set names charset_name 语句相当于执行了以下三行语句：
    SET character_set_client = charset_name;
    SET character_set_results = charset_name;
    SET character_set_connection = charset_name;

    　　

    或者执行SET CHARACTER SET 'charset_name'命令 :此命令和set names非常类似，唯一不同是将connection的字符集设置为当前数据库的字符集，所以相当于执行以下三行语句：

    1
    2
    3
    SET character_set_client = charset_name;
    SET character_set_results = charset_name;
    SET character_set_connection = @@character_set_database;

    小结: 中文乱码从：数据终端，操作系统，数据库

    数据库从： 全局数据库server字符集——>数据库字符集——> 表字符集——> 列字符集

    10.4 表情乱码不能识别

    修改存表情字段的字符集为utf8mb4　　

    11 can't opet file(errno:24)

    有的时候，数据库跑得好好的，突然报不能打开数据库文件的错误了。

    解决思路：

    首先我们要先查看数据库的 error log。然后判断是表损坏，还是权限问题。还有可能磁盘空间不足导致的不能正常访问表，操作系统的限制也要关注下,相关应用限制也要关注下;

    1
    2
    3
    #ulimit -n 查看系统的最大打开文件数
    [root@mysql-150 ~]# ulimit -n
    65535

    查看数据库的打开文件数

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'open_files_limit';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | open_files_limit | 5000  |
    +------------------+-------+
    # 根据业务实际情况修改打开文件数一般足够用了，低版本的打开文件数，可能有点小， 注意一下

    哪就是其他的问题了，可能是表的权限，也可能是表出问题，根据错误日志，具体分析　　

    处理方法

    • repair table tablename

    • chown mysql.mysql 权限 目录

    • 清理磁盘中的垃圾数据

    12. sleep 线程过多怎么解决

    结果：严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。

    12.1 知道 sleep 线程过多原因

    首先要知道到底是什么原因导致的 sleep 线程过多的：

    1. 程序逻辑问题，导致连接一直不释放；

    2. mysql 参数的问题，是不是参数配置的不合理，一直不释放连接；

    3. mysql 语句的问题，数据库查询不够优化，过度耗时。

    4. 大并发情况问题，导致 sleep 情况过多；

    12.2 临时解决 sleep 线程

    很多人都是重启大法，重启大法确实好， 能够释放，生产重启对业务有影响的，不能随便重启的

    shell脚本+cron计划任务，来kill sleep 线程，这个不靠谱啊， 你不知道 sleep 线程，里面是不是还有事务还在执行没有提交，也是sleep 状态，这个kill 操作有点莽夫，对生产数据数据库还是要理智啊。

    我临时解决的办法：

    1. 对用户资源做限制，看看那个用户连接的sleep线程比较多，对这个用户连接多的做一些限制，比如一个小时可以连接多少次啊等等

    2. 修改 mysql 参数问题 ，修改wait_timeout 和interactive_timeout默认都是28800秒有，也就是8个小时以后才释放空链接。

    例子：

    　

    不同用户登录到数据库，wait_timeout和interactive_timeout都是28800秒

    修改参数，我生产环境设置的是半个小时，也就是1800秒

    1
    2
    3
    4
    5
    6
    7
    mysql> set global wait_timeout=1800;
    mysql> set global interactive_timeout=1800;
     
    shell> vim my.cnf
    [mysqld]
    wait_timeout=1800
    interactive_timeout=1800

    　　

    这样修改完，由于已近保持的会话连接需要等到8个小时才会释放， 所以修改了wait_timeout和interactive_timeout不会立即生效的原因，这时候就要修改连接过多的用户资源了来释放sleep线程了

    如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    #开启两个会话窗口
    mysql> # 重新登录了会话，wait_timeout和interactive会生效
    mysql> show variables like 'wait_timeout';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | wait_timeout  | 1800  |
    +---------------+-------+
    1 row in set (0.00 sec)
     
    mysql> show variables like 'interactive_timeout';
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | interactive_timeout | 1800  |
    +---------------------+-------+
    1 row in set (0.00 sec)
     
    #session2
    mysql> #这个是其他用户连接的mysql，这个会话一直没有断开，参数还是28800
    mysql> show variables like 'wait_timeout';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | wait_timeout  | 28800 |
    +---------------+-------+
    1 row in set (0.01 sec)
     
    mysql> show variables like 'interactive_timeout';
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | interactive_timeout | 28800 |
    +---------------------+-------+
    1 row in set (0.00 sec)

    13.3 怎么根本解决了

    这时候就要知道 造成 sleep 线程过多的原因来解决：

    1. 程序执行完毕，应该显式调用mysql_close

    2. 程序中根据业务访问情况，选择长连接还是短连接

    3. 能逐步分析系统的SQL查询，找到查询过慢的SQL优化

    4. 合理设置mysql参数值

    作者：gtea 博客地址：https://www.cnblogs.com/gtea
  • 相关阅读:
    Python2 和 Python3的区别 更新中
    CentOS下图形界面安装_Orcaale 11g
    Nmap_使用介绍
    shell_innobackup增量备份步骤
    shell_跳板机推送公钥
    shell_clean_log
    shell_xtrabackup_backup_mysql
    gitlab免密登录
    gitlab安装与部署
    git合并分支
  • 原文地址：https://www.cnblogs.com/gtea/p/13114635.html
Copyright © 2020-2023  润新知