MySQL复制

MySQL 复制

1. 复制的原理
2. 复制的重点参数
3. 传统复制搭建（非GTID模式）

3.1 环境准备
3.2 在master上操作
3.3 在slave上操作

4. 主从复制故障处理

4.1 错误号1062处理
4.2 错误号1032处理

复制允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。默认情况下，复制是异步的。根据配置，您可以复制数据库中的所有数据库，所选数据库甚至选定的表。

1. 复制的原理

replication的工作原理分为以下3个步骤：

1）主服务器（master）把数据更改记录到二进制日志（binlog）中。
2）从服务器（slave）的I/O thread请求主服务器binlog，主服务器通过I/O dump thread传送binlog日志到从服务器的I/O thread，然后I/O thread把日志写入到中继日志（relay log）中。
3）从服务器通过SQL thread重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据库的最终一致性。

2. 复制的重点参数

log-bin
搭建主从复制，必须开启二进制日志
server_id
MySQL在同一组主从结构中的唯一标识（主从服务器上该参数不能一致）
server_uuid
在MySQL 5.7中，除了用户提供的–server-id之外，服务器还生成一个真正的UUID。这可用作全局只读变量server_uuid。
启动时，MySQL服务器自动获取UUID，如下所示：
- 尝试读取和使用文件data_dir/auto.cnf中编写的UUID（其中data_dir是服务器的数据目录）。
- 如果找不到data_dir/auto.cnf，则生成新的UUID并将其保存到此文件，必要时创建该文件。
read_only
启用read_only系统变量后，除具有SUPER权限的用户外，服务器不允许客户端更新。默认情况下禁用此变量。
服务器还支持super_read_only系统变量（默认情况下禁用），它具有以下作用：
- 如果启用了super_read_only，则服务器禁止客户端更新，即使是具有SUPER权限的用户也是如此。
- 将super_read_only设置为ON会隐式强制read_only为ON。
- 将read_only设置为OFF会隐式强制将super_read_only强制为OFF。
binlog_format
二进制日志的格式，必须使用row模式
log_slave_updates
从服务器是否应将其SQL线程执行的更新记录到其自己的二进制日志中。
binlog_error_action
控制服务器无法写入二进制日志时发生的情况
在MySQL5.7.7后，默认值为ABORT_SERVER,之前版本默认值为IGNORE_ERROR
- ABORT_SERVER：服务器在遇到二进制日志的此类错误时停止日志记录并关闭。在服务器重新启动时，将提交所有先前准备的和二进制记录的事务，同时中止由于错误而准备但未进行二进制记录的任何事务。
- IGNORE_ERROR：服务器遇到以上错误时，MySQL会在错误日志中记录错误，并强制关闭binlog功能。
binlog-do-db
使用该参数可选择性复制数据库，如binlog-do-db=test,表示只复制test库。
binlog-ignore-db
该参数是忽略某个库的复制。如binlog-ignore-db=test，表示除了test库，其他库都复制。
gtid_mode
控制是否启用基于GTID的日志记录以及日志可以包含的事务类型
- ON：新的和复制的事务都必须是GTID事务。
- ON_PERMISSIVE：新事务是GTID事务，复制事务可以是GTID也可以不是GTID事务
- OFF_PERMISSIVE：新事务不是GTID事务，复制事务可以是GTID也可以不是GTID事务
- OFF：不产生GTID，只接受不带GTID的事务
enforce_gtid_consistency
启用后，服务器通过允许仅执行可使用GTID安全记录的语句来强制执行GTID一致性。在启用基于GTID的复制之前，必须将此选项设置为ON。
可以配置–enforce-gtid-consistency的值为：
- OFF:允许所有事务违反GTID一致性。
- ON:不允许任何事务违反GTID一致性。
- WARN:允许所有事务违反GTID一致性，但在这种情况下会生成警告。在MySQL 5.7.6中添加了WARN。
gtid_next
指定要执行的下一个语句的GTID.
gtid_next可以采用以下任何值：
- AUTOMATIC: 默认值，使用下一个自动生成的全局事务ID。
- ANONYMOUS：事务没有全局标识符，仅由文件和位置标识。
- UUID：NUMBER格式的全局事务ID。
gtid_purged
从二进制日志中清除的所有GTID的集合
gtid_executed_compression_period
每处理多少次事务时，压缩mysql.gtid_executed表。设置为0表示此表未压缩。默认值1000
gtid_owned
此只读变量包含一个列表，其内容取决于其范围。当与会话范围一起使用时，该列表包含此客户端拥有的所有GTID;当与全局范围一起使用时，它包含所有GTID及其所有者的列表。
relay_log
记录从库I/O thread从主库读取来的binlog内容
relay_log_info_repository
应用二进制日志中的内容，并将binlog应用到的位置记录到relay.info文件中，建议使用表记录(mysql.slave_relay_log_info)
master_info_repository
把master.info（主从状态，配置信息）记录下来，默认记录到file里，建议使用表记录(mysql.slave_master_info)。
relay_log_purge
禁用或启用自动清除中继日志文件。默认值为1（ON）。
relay_log_recovery
为了让从库是crash safe的，必须设置relay_log_recovery=1。该参数的含义是：当从库发生崩溃或者重启时，它会把那些未执行完的中继日志删除，并会向主库重新获取binlog，再次生成relay log来完成恢复。建议开启，默认是关闭状态。
REPLICATE_DO_DB=name
CHANGE REPLICATION FILTER Syntax
只复制指定的库，在从库上使用。相当于--replicate-do-db。
REPLICATE_IGNORE_DB
在复制中，排除指定的库，在从库上使用。相当于--replicate-ignore-db。
REPLICATE_DO_TABLE
只复制指定的表，在从库上使用。相当于--replicate-do-table。
REPLICATE_IGNORE_TABLE
排除指定的表，在从库上使用。相当于--replicate-ignore-table。
REPLICATE_WILD_DO_TABLE
使用通配符复制指定的表。如
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.old%');复制db1库中，old开头的表。相当于--replicate-wild-do-table。
REPLICATE_WILD_IGNORE_TABLE
在复制中，排除通配符的的表，如CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%');，排除new开头的表。相当于--replicate-wild-ignore-table。
REPLICATE_REWRITE_DB
在从服务器上替换主服务器上指定数据库的新名称后，对从服务器执行更新。相当于--replicate-rewrite-db=from_name->to_name。
slave_net_timeout
该参数是设置在多少秒没收到主库传来的binlog之后，从库认为是网络超时，从库的I/O thread会重新连接主库。从MySQL5.7.7后默认值为60s，以前版本默认值为3600s
slave_parallel_type
从库使用多线程（slave_parallel_workers大于0）时，此变量指定用于决定允许哪些事务在slave上并行执行的策略。该变量对未启用多线程的slave没有影响。
LOGICAL_CLOCK：作为主服务器上相同二进制日志组提交的一部分的事务在从服务器上并行应用。根据事务的时间戳跟踪事务之间的依赖关系，以便在可能的情况下提供额外的并行化。设置此值后，可以在主服务器上使用binlog_transaction_dependency_tracking系统变量，以指定写入集用于并行化而不是时间戳，如果写入集可用于事务并且与时间戳相比提供改进的结果。
DATABASE：更新不同数据库的事务并行应用。仅当数据被分区为多个数据库时才适用此值，这些数据库在主服务器上独立并同时更新。必须没有跨数据库约束，因为可能会在从属设备上违反这些约束。
slave_parallel_workers
设置多个线程来并发执行relay log中主库提交的事务，最大值为1024.

3. 传统复制搭建（非GTID模式）

非GTID模式复制，基于binlog和position方式来搭建复制。

3.1 环境准备

类型	ip	prot	server-id	是否开启binlog	binlog格式	log_slave_updates参数
master	192.168.56.100	3306	1003306	log-bin = /data/mysql/mysql3306/logs/my3306_binlog	binlog_format = row	log_slave_updates=1
slave	192.168.56.200	3306	2003306	log-bin = /data/mysql/mysql3306/logs/my3306_binlog	binlog_format = row	log_slave_updates=1

从库开启binlog，并且开启log_slave_updates参数，让从库也写binlog，方便后期扩展。

3.2 在master上操作

1） 创建复制账号

create user 'repl'@'%' identified by 'wanbin';

grant replication slave on *.* to 'repl'@'%';

2） master数据库利用xtrabackup备份至slave上

innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --stream=tar ./ |ssh root@mysqldb2 "cat - > /data/backup/dbback`date +%Y%m%d_%H%M%S`.tar"

3.3 在slave上操作

解压

mkdir dbback20180918_163934

tar -xvf dbback20180918_163934.tar -C dbback20180918_163934

prepare 备份

innobackupex --apply-log /data/backup/dbback20180918_163934/

恢复备份

cd /data/mysql/mysql3306/

mv data data_bak

mkdir data

innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/dbback20180918_163934/

chown -R mysql:mysql /data/mysql/mysql3306/data

mysqld --defaults-file=/etc/my.cnf &

配置主从

cd /data/mysql/mysql3306/data

# cat xtrabackup_info
uuid = ff24a119-bb1e-11e8-b580-080027b0b461
name = 
tool_name = innobackupex
tool_command = --defaults-file=/etc/my3306.cnf -ubackup -pmysql --stream=tar ./
tool_version = 2.4.12
ibbackup_version = 2.4.12
server_version = 5.7.23-log
start_time = 2018-09-18 16:39:35
end_time = 2018-09-18 16:44:03
lock_time = 0
binlog_pos = filename 'my3306_binlog.000069', position '635'
innodb_from_lsn = 0
innodb_to_lsn = 4105428323
partial = N
incremental = N
format = tar
compact = N
compressed = N
encrypted = N


执行help change master to 可以获取change master to命令

CHANGE MASTER TO
  MASTER_HOST='192.168.56.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='wanbin',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='my3306_binlog.000069',
  MASTER_LOG_POS=635;

开始主从复制

start slave;


#如果想分别指定启动线程，可以使用如下命令
START SLAVE IO_THREAD;
START SLAVE SQL_THREAD;

#同样关闭命令：
STOP SLAVE;

#分别关闭命令：
STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;

查看主从复制状态

show slave status\G

...
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
...
                   Last_Error: Error 'Operation CREATE USER failed for 'repl'@'%'' on query. Default database: ''. Query: 'CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*F9E8175DA937ECCE88EE1CF18505FBDB049417E1''

#SQL thread线程没有起来，并报相应的错误。

#后经查看，是change master命令，指定的位置不对，导致的错误。


#错误解决：

stop slave;

reset slave all;

CHANGE MASTER TO
  MASTER_HOST='192.168.56.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='wanbin',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='my3306_binlog.000069',
  MASTER_LOG_POS=635;

start slave;

查看状态
show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my3306_binlog.000069
          Read_Master_Log_Pos: 787
               Relay_Log_File: mysqldb2-relay-bin.000002
                Relay_Log_Pos: 476
        Relay_Master_Log_File: my3306_binlog.000069
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 787
              Relay_Log_Space: 686
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1003306
                  Master_UUID: 7390a401-b705-11e8-9ed9-080027b0b461
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 17c50389-9537-11e8-a44c-08002712513f:1-2,
b019ea48-b729-11e8-99cb-08002712513f:1,
e4382832-949d-11e8-97ba-080027793430:1-154
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

SHOW SLAVE STATUS的主要变量

变量	说明
Slave_IO_State	显示当前IO线程的状态，上述状态显示的是等待主服务发送二进制日志
Master_Log_File	显示当前同步的主服务器的binlog，上述显示当前同步的是主服务器的my3306_binlog.000071
Read_Master_Log_Pos	显示当前同步到主服务器上二进制日志的偏移量位置，单位是字节。
Relay_Master_Log_File	当前中继日志同步的二进制日志
Relay_Log_File	显示当前写入的中继日志
Relay_Log_Pos	显示当前写入中继日志的偏移量位置
Slave_IO_Running	从服务器IO线程运行状态。YES表示正常
Slave_SQL_Running	从服务器SQL线程运行状态。YES表示正常
Exec_Master_Log_Pos	表示同步到主服务器的二进制日志偏移量位置。

查看SQL线程延迟的方法：

Master_Log_File = Relay_Master_Log_File 并且 Read_Master_Log_Pos - Exec_Master_Log_Pos = 0

#表示没有延迟

查看IO线程延迟的方法：
在主服务器上查看二进制状态，如：

> SHOW master status\G

*************************** 1. row ***************************
             File: my3306_binlog.000071
         Position: 386
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: e4382832-949d-11e8-97ba-080027793430:1-154
1 row in set (0.00 sec)


#用Position 减去从服务器上 Master_Log_File 值，就可得知IO线程的延时。上述没有延时。

4. 主从复制故障处理

从库上出现写入数据，把自增id占用：1062错误
从库上出现少数据，delete，update操作时，找不到相应记录。错误号：1032

4.1 错误号1062处理

模拟故障


#1. 在master上创建表
create table z1(id int not null,uname varchar(32),primary key(id));

#2. 在slave上插入数据
set sql_log_bin=0;
set global read_only=off;
insert into z1(id,uname) values(3,'python');

#3. 在master上插入数据
insert into z1(id,uname) values(2,'mysql');
insert into z1(id,uname) values(3,'java');

查看slave status

>show slave status\G

...
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
...
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table test.z1; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log my3306_binlog.000071, end_log_pos 1214

修复错误

利用pt-slave-restart 工具跳过此错误。

pt-slave-restart --error-numbers=1062
2018-09-19T15:24:02  mysqldb2-relay-bin.000019        1088 1062 

#查看slave staus

···
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
···

#说明复制已经正常，跳过了错误事务。

#但是master与slave上的这张表数据不一致，所以这种修复方法只能算暂时的。

#之后还需进行修复

通过pt-table-checksum工具查找数据不一致的表



pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=test --tables=z1 h=192.168.56.100,u=wanbin,p=mysql,P=3306


#常用参数解释：
--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
--replicate-check-only :只显示不同步的信息。
--replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。
--databases= ：指定需要被检查的数据库，多个则用逗号隔开。
--tables= ：指定需要被检查的表，多个用逗号隔开
h= ：Master的地址
u= ：用户名
p=：密码
P= ：端口

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
09-19T15:58:29      0      1        2          0       1       0   0.086 test.z1

#解释：
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=test --replicate-check-only  h=192.168.56.100,u=wanbin,p=mysql,P=3306

Checking if all tables can be checksummed ...
Starting checksum ...
Differences on mysqldb2
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
test.z1 1 -1 1   


【注意】：
1）根据测试，需要一个即能登录主库，也能登录从库的账号；
2）只能指定一个host，必须为主库的IP；
3）在检查时会向表加S锁；
4）运行之前需要从库的同步IO和SQL进程是YES状态。

使用pt-table-sync修复数据

pt-table-sync: 高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

#print修复命令

pt-table-sync --replicate=test.checksums h=192.168.56.100,u=wanbin,p=mysql,P=3306 h=192.168.56.200,u=wanbin,p=mysql,P=3306 --print

REPLACE INTO `test`.`z1`(`id`, `uname`) VALUES ('3', 'java') /*percona-toolkit src_db:test src_tbl:z1 src_dsn:P=3306,h=192.168.56.100,p=...,u=wanbin dst_db:test dst_tbl:z1 dst_dsn:P=3306,h=mysqldb2,p=...,u=wanbin lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:4374 user:root host:mysqldb1*/;

#参数解释：
--replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
--databases= : 指定执行同步的数据库。
--tables= ：指定执行同步的表，多个用逗号隔开。
--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
h= ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。
u= ：帐号。
p= ：密码。
--print ：打印，但不执行命令。
--execute ：执行命令。


#execute修复命令
pt-table-sync --replicate=test.checksums h=192.168.56.100,u=wanbin,p=mysql,P=3306 h=192.168.56.200,u=wanbin,p=mysql,P=3306 --execute


#再使用pt-table-checksum


# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=test --tables=z1 h=192.168.56.100,u=wanbin,p=mysql,P=3306
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
09-19T16:11:50      0      0        2          0       1       0   0.134 test.z1


【注意】：要是表中没有唯一索引或则主键则会报错：
Can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.

4.2 错误号1032处理

1032错误：主库更新或者删除的记录在从库上不存在。

模拟错误

1. slave上删除数据
mysql> set sql_log_bin=0;
mysql> select * from z1;
+----+-------+
| id | uname |
+----+-------+
|  2 | mysql |
|  3 | java  |
+----+-------+
2 rows in set (0.00 sec)

mysql> delete from z1 where id=3;

2. master上更新数据
mysql> update z1 set uname='python' where id=3;

3. 查看slave status
mysql> show slave status\G

...
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table test.z1; Can't find record in 'z1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log my3306_binlog.000076, end_log_pos 974253816

          Read_Master_Log_Pos: 974253847

...
          Exec_Master_Log_Pos: 974253510

...