mysql

发布日期: 2020-05-16

作者: 小明

文章字数: 3.1k

阅读时长: 14 分

阅读次数:

10主从复制（Replication）

1. 介绍

两台或以上数据库实例，通过二进制日志，实现数据的“同步”关系。

2-主从复制前提（搭建过程）

大家提到的：
# 时间同步
# 至少2台以上实例,要有不同角色换分，server_id 
# 主库开binlog
# 网络通畅
# 专门开启一个复制用户
# 开启专用复制线程
# “补课”
# 确认复制起点

#总结 
# 1. 需要2台以上数据库实例，时间同步，网络通畅，Server_id不同，区分不同角色（主库，从库）
# 2. 主库开启binlog，建立专用复制用户。
# 3. 从库需要提前“补课”
# 4. 从库： 主库的链接信息，确认复制起点
# 5. 从库： 开启专用的复制线程

3. 搭建

2台以上的MySQL实例(同版本、同平台),具备不同的server_id,server_uuid

3307	：主库
3308	：从库
3309	：从库

1-启动

[root@db01 3308]# systemctl start mysqld3307
[root@db01 3308]# systemctl start mysqld3308
[root@db01 3308]# systemctl start mysqld3309

2-查询

[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "select @@server_id ;"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@db01 3308]# mysql -S /tmp/mysql3308.sock -e "select @@server_id ;"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[root@db01 3308]# mysql -S /tmp/mysql3309.sock -e "select @@server_id ;"
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

[root@db01 3308]# mysql -S /tmp/mysql3309.sock -e "select @@server_uuid ;"
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 1c920eb6-901a-11ea-a2a5-000c29248f69 |
+--------------------------------------+
[root@db01 3308]# mysql -S /tmp/mysql3308.sock -e "select @@server_uuid ;"
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 195bb724-901a-11ea-a083-000c29248f69 |
+--------------------------------------+
[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "select @@server_uuid ;"
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 15ac32d2-901a-11ea-9ee5-000c29248f69 |
+--------------------------------------+
[root@db01 3308]#

3-主库：开启binlog, 创建复制用户。

vim  /data/3307/my.cnf
server_id=7
log_bin=/data/3307/mysql-bin

[root@db01 3308]# systemctl restart mysqld3307
[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "select @@log_bin ;"

[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to repl@'10.0.1.%' identified by '123';"
[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "select user,host ,plugin from mysql.user;"

4-主库备份恢复到从库

[root@db01 3308]# mysqldump -S /tmp/mysql3307.sock  -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/full.sql
[root@db01 3308]# mysql -S /tmp/mysql3308.sock</data/full.sql 
[root@db01 3308]# mysql -S /tmp/mysql3309.sock</data/full.sql

5-告知从库复制的信息：

[root@db01 ~]# grep "\-- CHANGE MASTER TO" /tmp/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444;
------------
或者查看备份

help change master to 

CHANGE MASTER TO
  MASTER_HOST='10.0.1.200',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;

从库

[root@db01 ~]# mysql -S /tmp/mysql3308.sock
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;


[root@db01 ~]# mysql -S /tmp/mysql3309.sock
CHANGE MASTER TO
 MASTER_HOST='10.0.0.51',
 MASTER_USER='repl',
 MASTER_PASSWORD='123',
 MASTER_PORT=3307,
 MASTER_LOG_FILE='mysql-bin.000002',
 MASTER_LOG_POS=444,
 MASTER_CONNECT_RETRY=10;

6- 在从库中开启专用复制线程

[root@db01 ~]# mysql -S /tmp/mysql3308.sock
start slave;

[root@db01 ~]# mysql -S /tmp/mysql3309.sock
start slave;

7-验证主从状态

[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "show slave status\G"|grep Running: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "show slave status\G"|grep Running: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

如果搭建不成，可以执行以下命令，从开始来过步骤重新来过。（没问题别整！！！！！）

[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "stop slave;reset slave all;"
[root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "stop slave;reset slave all;"

3-主从复制的原理

1-简要说明：

(1) change ,start slave 语句后，从库获取主库复制信息，启动复制线程
(2) IO线程,读取M.info 获取连接信息，连主库，主库分配DUMP
(3) IO线程,通过M.info获取复制信息，向主库Dump请求 
(4) DUMP 返回日志。
(5) IO线程 接日志 。TCPIP 缓存。
(6) IO线程写日志到relaylog，更新M.info 
(7) SQL线程读取R.info ,执行新的relay。更新R.info 
(8) relaylog定期自动删除
(9) 主库有通知从库有新日志产生

2-涉及到的线程

###2.1-主库：

binlog_dump_thread ,负责接收slave请求和传送主库binlog给slave

mysql> show processlist;

+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host       | db   | Command     | Time | State                                                         | Info             |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  6 | repl | db01:34638 | NULL | Binlog Dump | 2045 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  7 | repl | db01:34640 | NULL | Binlog Dump | 2015 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  8 | root | localhost  | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+

3-从库：

IO线程：

连接主库DUMP线程,请求Master日志、接收Master日志、存储日志（relay-log）。

SQL线程

 回放relaylog

查看状态

mysql> show slave status \G

3-涉及到的文件

1-主库：

binlog日志文件

2-从库：

# relay-log 中继日志 
命名方式： 
datadir/HOSTNAME-relay-bin.00000N

作用： 
存储获取到的binlog

3-从库信息文件

命名方式：  
datadir/master.info
作用： 
记录主库ip  port  user  password  binlog位置点等信息。

中继日志应用信息

命名方式： 
relay-log.info
作用： 记录SQL 线程回放到的位置点信息。

4-如何监控主从复制

1-主库

[root@db01 data]# mysql -S /tmp/mysql3307.sock -e "show processlist" |grep "Dump"

7    repl    db01:34640    NULL    Binlog Dump    5355    Master has sent all binlog to slave; waiting for more updates    NULL
9    repl    db01:34642    NULL    Binlog Dump    687    Master has sent all binlog to slave; waiting for more updates    NULL

[root@db01 data]# mysql -S /tmp/mysql3307.sock -e "show slave hosts;"
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         9 |      | 3309 |         7 | 1c920eb6-901a-11ea-a2a5-000c29248f69 |
|         8 |      | 3308 |         7 | 195bb724-901a-11ea-a083-000c29248f69 |
+-----------+------+------+-----------+--------------------------------------+

2-从库

[root@db01 data]# mysql -S /tmp/mysql3308.sock -e "show slave status \G"

5-主从复制的监控

主库连接信息、binlog位置信息（master.info）

show processlist;
show slave hosts;
show slave status \G
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 619

5.1从库的relay-log的执行情况，来自于R.info，一般用做判断主从延时

Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 495
Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 619
Seconds_Behind_Master: 0

5.2从库中relay-log的回放信息（relay-log.info）

Relay_Log_File: db01-relay-bin.000006
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 444

5.3从库线程状态，具体报错信息

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error:

5.4过滤复制相关信息：

Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table:

5.5落后于主库的秒数

Seconds_Behind_Master: 0

5.6延时从库的配置信息。

SQL_Delay: 0
SQL_Remaining_Delay: NULL

5.7-GTID相关复制信息：

Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

疑惑：

1. 一堆pos 功能

（1） IO 已经获取到的主库Binlog的位置点（master.info）
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
作用： IO下次请求日志时，起点位置。

（2） SQL 回放到的relaylog位置点。(relay-log.info)
Relay_Log_File: db01-relay-bin.000006
Relay_Log_Pos: 320


（3）SQL回放的realylog位置点，对应的主库binlog的位置点(relay-log.info)
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 600

作用： 计算主从复制延时日志量。

4-主从复制故障

1-如何监控

[root@db01 data]# mysql -S /tmp/mysql3308.sock -e "show slave status \G"
Slave_IO_Running: Yes                # IO线程工作状态： YES、NO、Connecting  
Slave_SQL_Running: Yes               # SQL线程工作状态：YES、NO
Last_IO_Errno: 0                     # IO故障代码：2003,1045,1040,1593,1236
Last_IO_Error:                       # IO线程报错详细信息  
Last_SQL_Errno: 0                    # SQL故障代码：  1008，1007
Last_SQL_Error:                      # IO线程报错详细信息

故障原因：

IO线程故障 
5.2.1 连接主库失败 
## user,password,IP,Port,plugin
## 主库无法连接：网络、宕机、防护墙、最大连接数上限


连接主库：
    （1） 网络，端口，防火墙
    （2） 用户 ，密码，授权
          replication slave
    （3） 主库连接数上限
     mysql> select @@max_connections;
    （4） 版本不统一  5.7 native  ， 8.0 sha2

2-故障模拟：

(1)主库宕机

 systemctl stop mysqld3307 
 show slave status\G 
 还原: 
 systemctl start mysqld3307 
 mysql -S /tmp/mysql3308.sock  -e "start slave;"

(2) 模拟用户密码错误

  mysql -S /tmp/mysql3307.sock  -e "alter user repl@'10.0.0.%' identified by '11212'" 
  mysql -S /tmp/mysql3308.sock  -e "start slave;  show slave status\G " 
 还原： 
   mysql -S /tmp/mysql3307.sock  -e "alter user repl@'10.0.0.%' identified by '123'" 
   mysql -S /tmp/mysql3308.sock  -e "start slave;  show slave status\G "

(3) 连接数上限

 mysql -S /tmp/mysql3307.sock  -e " set global max_connections=2;" 
 mysql -S /tmp/mysql3307.sock
 mysql -S /tmp/mysql3307.sock
 mysql -S /tmp/mysql3308.sock  -e "stop slave; start slave;  show slave status\G " 
 还原：  
 mysql -S /tmp/mysql3307.sock  -e " set global max_connections=200;" 
 mysql -S /tmp/mysql3308.sock  -e "stop slave; start slave;  show slave status\G "

排查方法：

[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3300
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.51' (111)
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.52 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (113)
[root@db01 ~]# mysql -urepla -p123 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repla'@'db01' (using password: YES)
[root@db01 ~]# mysql -urepl -p1123 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)
[root@db01 ~]#

3-高级故障模拟二

1-请求日志

主库日志损坏、缺失。

主从的server_id、Server_uuid相同。

2-server_id 故障重现：

[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> set global server_id=8;
[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> select @@server_id;
[root@db01 ~]# mysql -S /tmp/mysql3308.sock
mysql> stop slave;start slave;show slave status;

回退： 
[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> set global server_id=7;
[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> select @@server_id;
[root@db01 ~]# mysql -S /tmp/mysql3308.sock
mysql> start slave;show slave status;

3-主库日志损坏故障重现：

[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> reset master;

[root@db01 ~]# mysql -S /tmp/mysql3308.sock
mysql> start slave;show slave status;

4-测试环境处理方法（主从的数据当前是一致的）：

[root@db01 ~]# mysql -S /tmp/mysql3308.sock
# 将所有线程停止。
mysql> stop slave;                
# 将从库复制信息清空（master.info,relay-log.info清空，show slave status看不到信息了）
mysql> reset slave all;  
mysql> CHANGE MASTER TO
  MASTER_HOST='10.0.1.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
mysql> start slave;

5-生产中需要额外考虑什么情景？

需要重构主从：
1. 备份恢复.
2. change master to ; start slave

5.3 SQL线程故障
SQL线程主要工作：回放relaylog中的日志事件，可以理解为后台执行SQL语句。

realy-log 损坏。
处理方法：重构。

++++++++++++++正确的姿势++++++++++++++++++++
生产中如果要 reset master; 
1. 找业务不繁忙期间，停业务5分钟。
2. 等待从库重放完所有主库日志
3. 主库 reset master ;
4. 从库重新同步主库日志
mysql> stop slave;
mysql> reset slave all;
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
start slave;
+++++++++++++++++++++++++++++++++++++

方法1：备份主库+change master to + start slave;

方法2：找到问题点+ change master + start slave;

思路： 如何找到问题位置点。
        1. 找到SQL已经回放到什么位置了。
        SQL回放的realylog位置点，对应的主库binlog的位置点(relay-log.info)
        Relay_Log_File: db01-relay-bin.000006
        Relay_Log_Pos: 320
        ----》 
        2. 找到主库相应位置点：
        Relay_Master_Log_File: mysql-bin.000001
        Exec_Master_Log_Pos: 600
        3.  change master to  mysql-bin.000001 600

第三方工具：

pt工具

4-执行SQL出问题？

主要做什么工作？
回放relay-log中的日志。可以理解为执行relay-log SQL .
1.3.2 SQL线程故障本质？
为什么SQL线程执行不了SQL语句。
1.3.3 原因整理。
创建的对象已经存在
需要操作的对象不存在
约束冲突。
以上问题： 大几率出现在从库写入或者双主结构中容易出现。
参数,版本
------------------------------------------
（1）主从节点配置不一样： 平台、版本、参数、SQL_MODE
  调整成一致。
（2）修改的对象不存在（库、表、用户）
 从库被写入了。 双主架构。异步方式主从，导致数据不一致。
（3）创建的对象已存在（库、表、用户、约束冲突）
 从库被写入了。 双主架构。

故障模拟：

(1)先在从库 create database oldguo charset=utf8;
(2)在主库   create database oldguo charset=utf8mb4;
(3)检查从库SQL线程状态
Slave_SQL_Running: No
Last_Error: Error 'Can't create database 'oldguo'; database exists' on query. Default database: 'oldguo'. Query: 'create database oldguo'

处理故障

（1）思路1： 一切以主库为准
    将从库进行反操作一下。重启线程
    mysql> drop database oldguo;
    mysql> start slave;

（2）思路2: 以从库为准，跳过此次复制错误
    方法一：
    stop slave; 
    set global sql_slave_skip_counter = 1;
    #将同步指针向下移动一个，如果多次不同步，可以重复操作。
    start slave;

注意：如果要用此种方法，一定要保证此时故障数据是完全一致时，才能采用。

方法二：不推荐
/etc/my.cnf
slave-skip-errors = 1032,1062,1007

常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突


总结： SQL线程故障规避方法
1. 从库只读 ，读写分离中间件。
2. 不使用双主结构。PXC、MGR替代。
3. 半同步、增强半同步复制等，或者PXC、MGR替代。

（3）思路2：暴力方法，遇到自动跳过。
    /etc/my.cnf
    slave-skip-errors = 1032,1062,1007

    常见错误代码:
    1007:对象已存在
    1032:无法执行DML
    1062:主键冲突,或约束冲突

（4）思路4： 
     重新搭建主从：备份恢复+ 重新构建。

第三方工具：帮助我们检查主从数据一致，并可以修复主从不一致情况。
pt工具主从有关。
https://www.jianshu.com/p/c97228b6f60c

转载规则

《10-主从复制（Replication）》由小明采用知识共享署名 4.0 国际许可协议进行许可。

11-mysql-主从复制高级进阶

1-延时从库1-介绍普通的主从复制，处理物理故障损坏比较擅长。如果主库出现了DROP DATABASE操作。延时从库：主库做了某项操作之后，从库延时多长时间回放（SQL）。可以处理逻辑损坏。 2-配置mysql>stop slave

2020-05-19 mysql

leetcode mysql

09- mysql_备份恢复

mysql_备份恢复1-注意事项1. 关于备份恢复方面的职责（1）备份、恢复策略的设计。备份周期、备份工具、备份方式、恢复方式全部流程化（2）日常备份检查日志、备份内容（3）定期的恢复演练（4）数据故

2020-05-16 mysql