mysql.exe代码复制_31.Mysql复制

31.Mysql复制

复制是指将主数据库的DDL和DML操作通过二进制日志传到从数据库上，然后在从数据库上对重做日志，从而使从库与主库保持同步。

Mysql支持一台主库同时向多台从库复制，从库也可以作为其他从库的主库，实现链状的复制。

Mysql复制优点：

如果主库出现问题，可以快速切换到从库提供服务；

可以在从库上执行查询操作，降低主库的访问压力；

可以在从库上执行备份操作，避免备份对主库锁表。

Mysql复制缺点：

Mysql复制为异步复制，从库与主库之间存在延时。

31.1 复制概述

MySQL复制原理：

Mysql主库在事务提交时会把数据变更作为事件记录在二进制日志文件中，sync_binlog参数控制binlog日志刷新到磁盘；

主库推送binlog中的事件到从库的中继日志relay log中，从库根据relay log重做数据变更操作。

MySQL复制的三个线程：

binlog dump线程在主库上，I/O线程在从库上，SQL线程在从库上。

当在从库上启动复制(start slave)时，首先创建I/O线程连接主库，

主库随后创建binlog dump线程读取数据库事件并发送给I/O线程，

I/O线程将获得的事件数据更新到中继日志relay log中，

SQL线程读取relay log中更新的数据库事件并应用。

MySQL复制三个线程的状态：

show processlist\G

binlog dump线程:Master has sent all binlog to slave;waiting for more updates

I/O线程connect：Waiting for master to send event

SQL线程connect：Slave has read all relay log;waiting for more updates

31.1.1 复制中的各类文件

二进制文件binlog:把所有数据修改操作以二进制的形式记录到日志中，包括DDL和DML操作，不包括DQL(select)和DCL。

中继日志文件relay log:与binlog内容相同，区别在于SQL线程执行完relay log后会自动删除relay log，从而避免relay log占用过多的磁盘空间。

master.info:记录I/O线程连接主库的参数和binlog文件名以及事件的读取进度。

Master_Host:主库IP

Master_User:主库的复制用户

Master_port:主库的Mysql端口

Master_Log_File:从库I/O线程正在读取的binlog文件名

Read_Master_Log_Pos:从库I/O线程正在读取的binlog文件内的位置

relay-log.info:记录SQL线程应用relay log的参数，包括：relay log文件名、位置，对应binlog的文件名、位置。

Relay_Log_File: SQL线程正在应用的relay log文件名

Relay_Log_Pos: SQL线程正在应用的relay log文件内的位置

Read_Master_Log_File: SQL线程正在应用的relay log对应binlog的文件名

Exec_Master_Log_Pos: SQL线程正在应用的relay log对应binlog文件内的位置

show slave status\G -- 查看master.info和relay-log.info信息

31.1.2 三种复制方式

binlog的格式由参数binlog_format定义，binlog_format的取值分为Statement、Row、Mixed 三种。

mysql>show variables like 'binlog_format';

Statement:基于SQL语句级别的binlog，每条修改数据的SQL都会保存在binlog中。基于SQL语句的复制(SBR)。

Row:基于行级别，记录每一行数据的变化，将每行数据的变化记录在binlog中。基于行的复制(RBR)。优点：数据一致性高。缺点：日志量大。

Mixed:默认采用Statement模式，某些情况下切换到Row模式。混合复制模式。

例子：查看Statement模式和Row模式的区别。

-- 查看binlog_format参数

mysql>show variables like 'binlog_format';

-- 修改binlog_format参数'STATEMENT'

myql>set [global] binlog_format='STATEMENT'|'ROW'|'MIXED';

-- 查看binlog文件名和当前位置

mysql>show master status;

mysql-bin.000012 | 10301

-- 修改数据

mysql>update scott.emp set sal=sal+1000 where deptno=10;

-- 报错1665，调整隔离级别为可重复读

mysql>show variables like '%iso%'; -- tx_isolation仅作为transaction_isolation的别名，建议使用transaction_isolation

语法：

set [global] transaction_isolation='READ-UNCOMMITTED'|'READ-COMMITTED'|'REPEATABLE-READ'|'SERIALIZABLE';

myql>set transaction_isolation='REPEATABLE-READ';

-- 查看binlog(通过binlog文件名和起始位置)

语法：

show binlog events in 'binlog文件名' from 起始位置\G;

mysql>show binlog events in 'mysql-bin.000012' from 10301\G;

-- 使用mysqlbinlog工具分析binlog日志

语法

# mysqlbinlog --no-defaults binlog文件名 --start-pos=起始位置

# cd /usr/local/mysql/bin

# ./mysqlbinlog --no-defaults /data/log/mysql/mysql-bin.000012 --start-position=10301

-- 修改binlog_format参数'ROW'

myql>set binlog_format='ROW';

-- 查看binlog文件名和当前位置

mysql>show master status;

mysql-bin.000012 | 10603

-- 修改数据

mysql>update scott.emp set sal=sal-100 where deptno=10;

-- 查看binlog(通过binlog文件名和起始位置)

mysql>show binlog events in 'mysql-bin.000012' from 10603\G;

-- 使用mysqlbinlog工具分析binlog日志

# cd /usr/local/mysql/bin

# ./mysqlbinlog --no-defaults /data/log/mysql/mysql-bin.000012 --start-position=10603

-- Row复制模式时，binlog中数据以64进制显示，使用-vv --base64-output=DECODE-ROWS选项进行转换

# ./mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS /data/log/mysql/mysql-bin.000012 --start-position=10603

发现Row复制模式时，binlog中会逐行记录数据的变更，从而保证数据一致性，但记录量大。

31.1.3 复制的3种常见架构

复制的3种常见架构有：一主多从复制架构、多级复制架构、双主复制架构。

1.一主多从复制架构

适用场景：读请求压力非常大时，通过配置一主多从复制架构，主库负责写请求，多个从库(负载均衡)负责读请求，实现读写分离。

优点：满足大多数读请求压力大的业务场景。

缺点：从库较多时，主库推送binlog日志的I/O压力和网络压力较大。

2.多级复制架构

主库1推送binlog日志给主库2，主库2再推送日志给所有的从库。

优点：解决了主库推送binlog日志的I/O压力和网络压力较大的问题。

缺点：通过了两次复制，数据延时更大。

改进：在主库2上设置存储引擎为BLACKHOLE,BLACKHOLE表的数据不会写回到磁盘，仅读取relay log并生成自己的binlog再推送binlog给从库，以降低多级复制的延时问题。

注意：主库2上需要设置log_slave_updates参数，该参数含义是SQL线程执行Relay log的时，仍会记录自身的binlog，从而实现了binlog的多级传递。

3.双主复制架构

两台数据库互为主从。

优点：避免重复搭建从库，方便日常维护。

server id是每个数据库的唯一标识，binlog中会记录事件初始发生的server id，SQL线程只应用和自身server id不同的binlog日志，所以不会出现循环复制问题。

31.2 复制搭建过程

31.2.1 异步复制

异步复制指复制相关的三个线程(binlog dump、I/O、SQL)之间是相互独立的，没有依赖关系。

主库执行完commit操作，且主库写入binlog日志后即可成功返回客户端，无需等待binlog日志传送给从库。

1).确保主从库上安装了相同版本的数据。

2).在主库上创建复制用户，并授予replication slave权限。

mysql> create user 'repl'@'host' identified by 'pssword';

mysql> grant replication slave on *.* to 'repl'@'host';

3).在主库上修改参数文件，并重启数据库。

[mysqld]

server-id=1 -- 数据库的唯一标识

log-bin=/home/mysql/log/mysql-bin.log -- binlog目录及文件名，开启binlog

4).在主库上，设置读锁定有效(确保没有数据库操作，以便获得一个一致性快照)

mysql>flush tables with read lock;

5).获取主库的binlog文件名和偏移量

mysql>show master status;

6).备份主库。可以使用逻辑备份、物理-冷备份、物理-热备份。

#tar -cvf data.tar data

7).主库备份完成，解除锁定。

mysql>unlock tables;

8).恢复从库。

#tar -zvf data.tar

9).修改从库参数文件

[mysqld]

server-id=2 -- 数据库的唯一标识

10).启动从库。通过--skip-slave-start选项跳过启动复制进程

[mysql_home]# ./bin/mysqld_safe --skip-slave-start &

11).在从数据库上，配置主库信息：IP、端口、用户、密码、binlog文件名、文件内开始复制的位置

mysql>change master to master_host='IP',master_port='端口',master_user='用户',master_password='密码',master_log_file='binlog文件名',master_log_pos='Position';

12).在从库上，启动slave线程。start|stop slave;

mysql>start slave;

13).在从库上，查看进程列表。检查I/O线程和SQL线程是否正常启动，或查看slave的状态。

mysql>show processlist;

mysql>show slave status;

14).测试。在主库上创建表并插入数据，在从库上检查表是否创建，数据是否正常插入。

binlog产生的时机：提交事务之后，释放锁之前。

支持事务的存储引擎(InnoDB): 执行SQL-->提交事务-->记录binlog-->释放锁

不支持事务的存储引擎(MyISAM): 执行SQL-->记录binlog-->释放锁

binlog从内存刷新到磁盘的时机通过参数sync_binlog控制：

mysql>show variables like 'sync_binlog';

sync_binlog=0 表示Mysql不控制binlog的刷盘，由操作(文件)系统控制缓存的刷新。

sync_binlog=1 表示每次事务提交，MySQL都会调用文件系统的刷新操作，将binlog更新到磁盘文件中。

sync_binlog=n 表示每n次事务提交，MySQL都会调用文件系统的刷新操作，将binlog更新到磁盘文件中。

宕机导致的数据丢失：

当主机宕机或掉电时，内存缓存中的binlog丢失，sync_binlog=1能够将损失降低到1个事务，但高频率的刷新磁盘操作会影响系统I/O，对Mysql性能产生影响。

宕机后已刷新到binlog文件但未推送给从库的数据可以通过mysqlbinlog工具手动同步到从库。

异步复制缺点：

主库和从库的数据之间存在一定的延迟，主库宕机时binlog丢失，导致主从不一致。

31.2.2 半同步复制

半同步复制指复制的binlog dump线程和I/O线程是同步的，SQL线程是独立的。

主库执行完commit操作，且主库写入binlog日志，等待一个从库也接收到binlog并成功写入中继日志后，最终成功返回客户端。

半同步复制事务提交需要等带binlog传递到从库并写入relay log后，再由从库通知主库收到binlog，才能完成。

往返时延(RTT)指从发送端发送数据到发送端收到接收端的确认，总共经历的时长。

半同步复制性能取决与主、从之间的往返时延。

mysql>show variables like '%semi-sync%';

例子1：开启半同步复制需要安装插件，且主从库使用不同的插件。

1).通过参数have_dynamic_loading判断MySQL服务器是否支持动态安装插件。

mysql>select @@have_dynamic_loading;

2).检查MySQL安装目录下是否存在插件，$MYSQL_HOME/lib/plugin/semisync_master.so和semisync_slave.so

# cd /usr/local/mysql/lib/plugin

# ls -l

3).在主库上安装插件semisync_master.so

mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';

4).在从库上安装插件semisync_slave.so

mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

5).在plugin表中查看安装的插件

mysql>select * from mysql.plugin;

下次数据库重启后会自动加载插件。

6).在主库上配置半同步参数

mysql>set global rpl_semi_sync_master_enabled=1; -- 开启主库半同步

mysql>set global rpl_semi_sync_master_timeout=30000; -- 设置超时参数为30秒，当从库故障，不能同步时，超过该参数，将自动切换为异步复制。

在从库上配置半同步参数

mysql>set global rpl_semi_sync_slave_enabled=1; -- 开启从库半同步

重启从库上的I/O线程

mysql>stop slave io_thread;

mysql>start slave io_thread;

7).查看半同步的系统参数和状态参数：

mysql>show variables like '%semi_sync%';

rpl_semi_sync_master_enabled=ON -- 开启主库半同步

rpl_semi_sync_master_timeout=30000 -- 设置超时参数为30秒

rpl_semi_sync_master_trace_level=32 -- 日志打印级别

rpl_semi_sync_master_wait_for_slave_count=1 -- 等待返回信息的slave数量

rpl_semi_sync_master_wait_no_slave=ON -- 在没有slave时，是否选择等待。

rpl_semi_sync_master_wait_point=AFTER_SYNC -- 控制等待ack的逻辑处于整个事务提交过程的哪个阶段，目前支持两种模式：AFTER_SYNC、AFTER_COMMIT。

rpl_semi_sync_slave_enabled=ON -- 开启从库半同步

rpl_semi_sync_slave_trace_level=32 -- 从库日志级别

参数详细解释：

rpl_semi_sync_master_enabled=ON -- 开启主库半同步

控制是否打开半同步复制，这是一个即时生效的参数, 举个例子，如果master因为种种原因无法接受到slave返回的ack信息，导致事务在提交阶段阻塞，手动关闭此参数即可生效。

此参数还有另外的作用就是，在slave丢失，事务等待超时后，半同步状态会自动关闭，但是此参数依然是on的状态，此时当半同步slave恢复后，内部状态会自动切换到半同步模式，其实也有一种手动的方式，就是重新设置此参数为on。

rpl_semi_sync_master_timeout=30000 -- 设置超时参数为30秒，当从库故障，不能同步时，超过该参数，将自动切换为异步复制。

等待ack的超时时间，可以动态设置，但是对于处于等待状态的事务无效，

如果master因为种种原因无法接受到slave返回的ack信息，处于等待状态，

此时如果为了让事务成功提交，将此参数设置为0是不生效的。必须调整rpl_semi_sync_master_enabled参数.

rpl_semi_sync_master_trace_level=32 -- 日志打印参数，默认即可

1 = general level (for example, time function failures)

16 = detail level (more verbose information)

32 = net wait level (more information about network waits)

64 = function level (information about function entry and exit)

rpl_semi_sync_master_wait_for_slave_count=1 -- 必须等待多少个slave的ack信息，才可以提交

设置为1表示1个slave返回接收到binlog后即可提交事务

rpl_semi_sync_master_wait_no_slave=ON -- 在没有slave时，是否选择等待。

rpl_semi_sync_master_wait_point=AFTER_SYNC -- 控制等待ack的逻辑处于整个事务提交过程的哪个阶段，目前支持两种模式：AFTER_SYNC、AFTER_COMMIT。

AFTER_COMMIT流程：master write binlog -> slave sync binlog -> master commit -> salve ack -> master return result。

AFTER_SYNC流程：master write binlog -> slave sync binlog -> salve ack -> master commit -> master return result。

AFTER_COMMIT缺点：

会出现脏读的情况，也就是在事务提交之后，slave 确认之前，客户端A还没有获得结果返回，但是客户端B能够读取A提交的结果；

主库在commit后宕机，如果从库还没有收到binlog,则可能会出现数据丢失的问题。

AFTER_SYNC优缺点：

保证所有客户端查询到的结果都是相同的；

保证 slave 不会丢数据；

master 宕机虽然不会导致数据丢失，但有一种情况可能会出现，那就是 salve 的数据比 master 多。即salve ack成功，master commit失败，主库事务回退，从库正常提交。

rpl_semi_sync_slave_enabled=ON -- 开启从库半同步

rpl_semi_sync_slave_trace_level=32 -- 从库日志级别

mysql>show status like '%semi_sync%';

rpl_semi_sync_master_clients=0 -- 客户端数量

rpl_semi_sync_master_net_avg_wait_time=0 -- 网络平均等待时间

rpl_semi_sync_master_net_wait_time=0 -- 网络等待时间

rpl_semi_sync_master_net_waits=0 -- 网络等待次数

rpl_semi_sync_master_no_times=0 --

rpl_semi_sync_master_no_tx=0 -- 异步复制事务量(半同步模式下，从库没有及时响应的事务量，即半同步模式自动转异步模式下的事务量)

rpl_semi_sync_master_status=ON -- 主库半同步状态，ON为打开，OFF为关闭

rpl_semi_sync_master_timefunc_failures=0 --

rpl_semi_sync_master_tx_avg_wait_time=0 -- 事务平均等待时间，单位：

rpl_semi_sync_master_tx_wait_time=0 -- 事务等待时间，单位：

rpl_semi_sync_master_tx_waits=0 -- 事务等待次数

rpl_semi_sync_master_wait_pos_backtraverse=0 --

rpl_semi_sync_master_wait_sessions=0 -- 等待的会话数

rpl_semi_sync_master_yes_tx=0 -- 半同步复制事务量

Rpl_semi_sync_slave_status=OFF -- 从库半同步状态，当为OFF时需要重启从库的I/O线程，stop|start slave io_thread;

8).执行事务，并检查状态

mysql>update scott.emp set sal=sal+100 where empno=7788;

mysql>show status like '%semi_sync%';

例子2：测试从库故障，主库等待rpl_semi_sync_master_timeout毫秒后超时，自动转为异步复制

1).确认主库的半同步复制模式和等待时间

mysql>show variables like '%semi_sync%';

2).在从库上通过iptables模拟网络故障或直接关闭从库

# iptables -A INPUT -s 主库IP -j DROP

3).在主库上提交事务，发现事务被阻塞，30.06s后正常提交

mysql>update scott.emp set sal=sal+100 where empno=7788;

4).检查主库的半同步辅助状态， rpl_semi_sync_master_status变为OFF，rpl_semi_sync_master_no_tx增加1。

mysql>show status like '%semi_sync%';

5).半同步复制转为异步复制后，再次在主库上提交事务时，不会阻塞。

mysql>update scott.emp set sal=sal+100 where empno=7788;

例子3：测试从库故障恢复后，自动由异步复制转为半同步复制

1).取消从库上的iptables规则

# iptables -F

2).检查从库I/O线程状态，由Connecting变成Yes

#mysql -hlocalhost -uroot -proot

mysql>show slave status\G

3).检查主库半同步复制的状态参数，rpl_semi_sync_master_status由OFF变为ON

mysql>show status like '%semi_sync%';

4).检查主、从库数据值

mysql>select sal from scott.emp where empno=7788;

5).执行事务，并检查状态

mysql>update scott.emp set sal=sal+100 where empno=7788;

mysql>show status like '%semi_sync%';

31.3 主要复制启动选项

31.3.1 log_slave_updates参数

log_slave_updates参数用来配置从库上的更新操作是否写进二进制日志，默认不打开。但作为级联复制架构的Master2服务器时必须打开该参数。

mysql>show variables like 'log_slave_updates';

该参数必须与log_bin和log_bin_basename一起使用，即开启二进制日志，并指定二进制日志的目录和文件名。

mysql>show variables like 'log_bin';

mysql>show variables like 'log_bin_basename';

31.3.2 master_connect_retry参数【已删除】

master_connect_retry参数用于配置从库到主库连接丢失时，每次重连的时间间隔，默认为60s，即连接断开时，每60秒尝试一下重新连接。

该参数现已删除，功能保留在change master命令中：

mysql>change master to master_host='192.168.23.123',master_user='repl',master_password='xxxxxx',master_connect_retry=30;

并可以通过show slave status\G命令查看，列为Connect_Retry: 60。

31.3.3 read_only参数

read_only参数用来设置从库只能接受root用户的操作，从而限制应用程序等普通用户对从库的更新操作。

mysql>show variables like 'read_only'; --OFF

0).创建用户并授权

mysql> create user 'scott'@'localhost' identified by 'scott';

mysql> grant insert,update,select on scott.* to 'scott'@'localhost';

mysql> exit;

1).使用普通用户登陆并进行DML操作：ok

#mysql -hlocalhost -uscott -pscott

mysql> update scott.emp set sal=sal+100 where empno=7788;

2).使用root账户修改read_only参数为ON：

mysql>set global read_only=on;

3).使用普通用户登陆并进行DML操作：no

#mysql -hlocalhost -uscott -pscott

mysql> update scott.emp set sal=sal+100 where empno=7788;

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

4).使用root账户操作或将修改read_only参数为OFF。

31.3.4 指定复制的数据库或者表

Replicate_Do_DB:指定需要复制的数据库

Replicate_Ignore_DB:指定不复制的数据库

Replicate_Do_Table:指定需要复制的表

Replicate_Ignore_Table:指定不复制的表

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

上述参数均可以在从库的my.cnf文件中配置，但不可以通过set命令配置。

通过my.cnf文件配置需要重启Mysql服务，不停止服务时可以通过change replication filter命令动态调整(仅需要停止SQL线程)：

stop slave sql_thread;

change replication filter replicate_ignore_db=(demo);

start slave sql_thread;

https://yq.aliyun.com/articles/59268

复制过滤的判断逻辑顺序自上而下为：replicate-do-db -> replicate-ignore-db -> replicate-do-table -> replicate-ignore-table -> replicate-wild-do-table -> replicate-wild-ignore-table -> replicate-do-table or replicate-wild-do-table

replicate-do-db -> replicate-ignore-db两个步骤称为DB level，后续步骤称为able level。

binlog-format=statement时，DB level以use DB为判断依据，而与实际db_name.tab_name无关，这可能导致部分binlog不能被复制到relay log中。

binlog-format=rows时，DB level以实际db_name.tab_name为判断依据，binlog按过滤设置被复制到relay log中，不会丢失。

binlog-format='MIXED'(即DDL语句时binlog-format=statement，DML语句时binlog-format=rows)时，可能导致部分DDL语句的binlog不能被复制到relay log中，级联导致部分DML语句的binlog虽然被复制到relay log中但执行时报错。

简单的来说，在DB level 中，当binlog-format=statement 时，SQL不允许跨库；当binlog-format=rows 时，SQL允许跨库。

在table level中，无论binlog-format是statement还是rows，SQL都可以跨库。

总的流程走向：先判断DB-level，如果DB-level 判断完成后需要exit，则退出。如果DB-level判断完成后，没有exit，则再判断Table-level。

在DB-level中，如果有replicate-do-db，则判断replicate-do-db，将不会走到replicate-ignore-db这层。 如果判断replicate-do-db符合条件，则判断table-level。 如果不符合，则exit。

在DB-level中，如果没有replicate-do-db，但是有replicate-ignore-db。 流程则是：符合replicate-ignore-db规则，则exit，不符合，则走到table-level层继续判断。

在Table-level中，判断逻辑顺序自上而下为：replicate-do-table -> replicate-ignore-table -> replicate-wild-do-table -> replicate-wild-ignore-table

在Table-level中, 从第一个阶段(replicate-do-table)开始，如果符合replicate-do-table判断规则，则exit。如果不符合，则跳到下一层(replicate-ignore-table)。

然后以此内推，直到最后一层(replicate-wild-ignore-table)都不符合，则最后判断是否有(replicate-do-table or replicate-wild-do-table)，如果有，则ignore & exit。如果没有，则execute & exit。

31.3.5 slave_skip_errors

从库SQL线程在执行relay log时可能发生SQL错误，默认情况下，从库SQL线程会停止执行等待用户处理。

slave_skip_errors参数的作用是告知SQL线程遇到下面编号的错误可以直接跳过，减少从库SQL线程的停止。

查看slave_skip_errors参数

mysql>show variables like 'slave_skip_errors';

修改slave_skip_errors参数：在my.cnf文件中配置

[mysqld]

slave_skip_errors=1053,1062,1146

slave_skip_errors参数不能动态设置，否则报1238：

mysql>set slave_skip_errors='1053,1062,1146';

ERROR 1238 (HY000): Variable 'slave_skip_errors' is a read only variable

注意：从库SQL线程报错需要人工分析报错原因并给出解决方案，直接跳过出错SQL可能导致数据不一致，不建议启用。

31.4 日常管理维护

31.4.1 查看主、从库状态

mysql>show master status\G -- 查看主库状态

File: mysql-bin.000012 -- 当前的binlog文件

Position: 14880 -- 当前binlog文件内的偏移量

Binlog_Do_DB: -- 复制的库

Binlog_Ignore_DB: mysql,sys,information_schema,performance_schema -- 忽略的库

Executed_Gtid_Set: -- GTID即全局事务ID(global transaction identifier)，GTID实际上是由UUID+TID组成的。

其中UUID是一个MySQL实例的唯一标识。

TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行(不会重复执行同一个事务，并且会补全没有执行的事务)。

下面是一个GTID的具体形式：4e659069-3cd8-11e5-9a49-001c4270714e:1-77

mysql>show slave status\G -- 查看从库状态

Slave_IO_State: Waiting for master to send event -- 从库I/O线程状态

Master_Host: 172.22.5.202 -- 主库IP

Master_User: repl -- 主库的复制用户

Master_Port: 3306 -- 主库端口

Connect_Retry: 60 -- 当复制连接断开后每60秒尝试连接一次

Master_Log_File: mysql-bin.000005 -- 主库binlog文件名

Read_Master_Log_Pos: 14881 -- 主库binlog的偏移量

Relay_Log_File: mysql-relay-bin.000018 -- 从库relay log的文件名

Relay_Log_Pos: 320 -- 从库relay log的偏移量

Relay_Master_Log_File: mysql-bin.000005 -- 从库执行到与主库binlog对应的文件名

Slave_IO_Running: Yes -- 从库I/O线程运行状态

Slave_SQL_Running: Yes -- 从库SQL线程运行状态

Replicate_Do_DB: -- 复制的库

Replicate_Ignore_DB: -- 忽略的库

Replicate_Do_Table: -- 复制的表

Replicate_Ignore_Table: -- 忽略的表

Replicate_Wild_Do_Table: -- 复制的Wild表

Replicate_Wild_Ignore_Table: -- 忽略的Wild表

Last_Errno: 0 -- SQL线程报错的错误编号

Last_Error: -- SQL线程报错的错误信息

Skip_Counter: 0 -- 跳过错误的数量

Exec_Master_Log_Pos: 14881 -- 从库r执行到与主库binlog对应的偏移量

Relay_Log_Space: 693

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 -- I/O线程报错的错误编号

Last_IO_Error: -- I/O线程报错的错误信息

Last_SQL_Errno: 0 -- SQL线程报错的错误编号

Last_SQL_Error: -- SQL线程报错的错误信息

Replicate_Ignore_Server_Ids: -- 复制忽略的MySQL服务器

Master_Server_Id: 2 -- 主库MySQL服务器ID

Master_UUID: f40a8fd0-17e2-11e9-ae19-fa163edf67bd -- 主库MySQL服务器UUID

Master_Info_File: /data/mysql/master.info -- 主库的master.info

SQL_Delay: 0 -- SQL延迟

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates -- 从库SQL线程状态

Master_Retry_Count: 86400 -- 连接主库的次数

Master_Bind:

Last_IO_Error_Timestamp: -- I/O线程报错的时刻

Last_SQL_Error_Timestamp: -- SQL线程报错的时刻

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

31.4.2 主从库同步维护

主从库差距：由主库的File、Position与从库的Master_Log_File、Read_Master_Log_Pos和Relay_Master_Log_File、Exec_Master_Log_Pos中可以看出。

缩小主从库差距的方法有：

1).阻塞主库,强制主从同步

mysql>flush tables with read lock; -- 给主库加读锁，禁止主库的所有更新操作

mysql>show master status; -- 查看主库的binglog和偏移量

mysql>select master_pos_wait('mysql-bin.000012','14880'); --在从库执行，返回0表示从库与主库同步，返回-1表示超时退出

mysql>unlock tables; --在主库执行，释放锁

2).增加从库SQL线程

待处理

3).percona-toolkit工具监控

https://www.cnblogs.com/kevingrace/p/6261091.html

待处理

31.4.3 从库复制出错的处理

启、停slave，包括I/O线程和SQL线程：

mysql>start slave;

mysql>stop slave;

启、停I/O线程：

mysql>start slave io_thread;

mysql>stop slave io_thread;

启、停SQL线程：

mysql>start slave sql_thread;

mysql>stop slave sql_thread;

跳过出错的该条SQL语句：

mysql>set global sql_slave_skip_counter=1;

当更新语句包含auto_increment或last_insert_id()时，该语句包含了2个事件，需要跳过sql_slave_skip_counter=2：

mysql>set global sql_slave_skip_counter=2;

从库复制出错会导致SQL进程停止。

一般的处理方法：先停止slave，再跳过该条错误SQL，接着开启slave，最后在binlog中查看出错的SQL并解决报错手动更新。

mysql>stop slave;

mysql>set global sql_slave_skip_counter=1;

mysql>start slave;

# cd /usr/local/mysql/bin

# ./mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS /data/log/mysql/mysql-bin.000012 --start-position=10603

31.4.4 log event entry execeeded max_allowed_packet错误的处理

原因：大文本记录超过了参数max_allowed_packet的设置，导致无法通过网络进行传输。

解决：在主库上增加参数max_allowed_packet的大小。

mysql>show variables like 'max_allowed_packet';

mysql>set max_allowed_packet=33554432; -- 最大设置为32MB

31.4.5 多主复制时的自增长变量冲突问题

原因：多主复制时自增是基于每个数据库的，而非全局自增，当发生切换时某个表的自增列在Master2上的自增变量有可能小于Master1上的自增变量，将导致主键冲突。

解决：通过参数auto_increment_increment和auto_increment_offset设置解决。

mysql>show variables like 'auto_increment_%';

参数auto_increment_increment是自增步长，默认是1，对于多主复制时应设置为主的个数。

参数auto_increment_offset是自增偏移量，默认是1，对于多主复制时应与server_id保持一致，server_id是集群中的Mysql服务器标识。

mysql>set auto_increment_increment=2; -- 多主复制集群中共有2个主服务器

mysql>set auto_increment_offset=1; -- 多主复制集群中的第一个主服务器(server_id=1)，生成的序号为：1，3，5，7，9，...

mysql>set auto_increment_offset=2; -- 多主复制集群中的第二个主服务器(server_id=2)，生成的序号为：2，4，6，8，10，...

某个表的主键列Master1当前序号为3，下次生成的序号为5，因为自增步长为2；当发生切换时，Master2下次生成的序号为4。

31.4.6 查看从库的复制进度

binlog中每个语句执行前都会设置时间戳。

例子：

mysql> show master status;

# cd /usr/local/mysql/bin

# ./mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS /data/log/mysql/mysql-bin.000012 --start-position=14880

SQL线程的Time属性等于SQL线程最后执行binlog事件的事件戳与当前事件的差值，单位为秒。

例子：

mysql>show processlist; -- 在从库查看SQL线程，Time=84813

mysql>update scott.emp set sal=sal+100 where empno=7788; -- 在主库执行更新SQL

mysql>show processlist; -- 再次在从库查看SQL线程，Time=8

31.4.7 如何提高复制的性能

mysql> show slave status;

属性Seconds_Behind_Master表示从库落后主库的秒数。

原因：主库写入是多线程，从库SQL线程只有一个线程，当主库多线程写入频繁时，从库的单个SQL线程不能及时执行relay log中新增的binlog，从而导致从库延时。

解决：通过参数slave_parallel_workers设置基于Schema的多线程复制，允许从库并行更新。

mysql>show variables like 'slave_parallel_workers'; -- 0 表示未启用多线程复制

mysql>show databases; -- 查看主库中的Schema，包括4个系统Schema(sys、mysql、information_schema、performance_schema)

mysql>set global slave_parallel_workers=2; -- 在从库设置为用户Schema的个数

mysql>show processlist; -- 在从库查看SQL线程发现并未增加SQL线程？？？可能是主库并发量不够吧！

另一种方案是在多级复制的框架上每个从库只复制主库的一个Schema。

31.5 切换主从库

例子：主M，从S1和S2。M故障后，将S1修改为主，将S2作为S1的从库，修改应用IP为S1的地址。

1).在S1和S2上停止I/O线程，确保SQL线程执行了relay log中的所有事件。

mysql>stop slave io_thread;

mysql>show processlist\G -- SQL线程的state必须为slave has read all relay log

2).在S1上，停止从服务，修改为主服务，创建复制用户并授权，在S1上必须配置log-bin选项，不能配置log-slave-updates选项。

mysql>stop slave;

mysql>reset master;

mysql>create user 'repl'@'S2_IP' indetified by 'password';

mysql>grant replication on *.* to 'repl'@'S2_IP';

3).在S2上，停止从服务，重新指向主服务器，再启动从服务

mysql>stop slave;

mysql>change master to master_host='S1的IP';

mysql>start slave;

4).通知应用将数据库IP更改为S1的IP地址

5).删除S1服务器上的master.info和relay-log.info文件，否则下次启动时S1仍被作为从库启动。

6).将M修复后按照S2的方法配置成从库。

7).对比M中的binlog，检查在M故障时是否有binlog数据未同步给relay log。

在异步模式下有可能存在，那这部分数据已经在M中但未在S1和S2中，该部分数据应该从M中删除还是应该同步S1、S2需要进一步手动处理。

在半同步模式下binlog数据都会同步给relay log，但存在已经同步给relay log的数据，在M故障时被回退的可能，

即M中没有的数据在S1和S2中有，且该部分数据应用已经报错给UI，应该通过binlog和redo log找到后从S1和S2中手动删除处理。

31.6 小结