mysql主从复制

mysql主从复制

主从复制慨述

构建大型，高性能应用程序的基础
主服务器复制负责更新，且将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环
从服务器主动连接主库，通知主服务器从日志中读取的最后一次更新的位置（第一次的话就是开始位置）
从服务器接受从那时起发生的任何更新，任何封锁并等待主库通知新的更新
注意：任何数据更新都必须在主服务器上进行，主能读写，从只能读

使用主从的好处

1. 提高数据库读的性能，主库专注写操作，从可以多个，分散读操作
2. 提高数据安全，在从库做备份不会影响主库相关数据

能解决的问题

1. 数据分布(data distribution)
2. 负载均衡(load balancing)
3. 备份(backups)
4. 高可用性和容错行 High availability and failover，故障切换

mysql支持的复制方法

1. statement based replication(SBR) 基于SQL语句的复制: 主服务器上执行的sql,在从服务器上同样执行,5.6默认使用SBR,
2. row based replication(RBR) 基于行的复制: 把改变的内容复制过去,而不是把命令在从服务器上执行一遍,从5.0开始支持
3. mixed based replication(MBR) 混合型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确复制时,就采用基于行的复制
4. 基于global transaction identifiers(GTIDs)来进行事务复制 5.6.5以后版本支持

mysql是异步复制，mysql cluster是同步复制。
mysql配置文件设置复制方法的变量为binlog_farmat,值为:

• STATEMENT
• ROW
• MIXED

mysql主从复制模式：半同步模式（接近同步），异步模式(可以配置延迟时间)，同步模式(延时小一点)
mysql主从架构：master --> slave, master --> slave --> slave, master <--> master

几种复制的特点

SBR的特点

二进制日志记录的是sql语句
优点：

• 历史悠久
• 日志文件更小
• 记录了所有的语句，可以用来日后审计
  缺点:
• 使用如下函数的语句不能被正确地复制：load_file(); uuid(), uuid_short(); user(); found_rows(); sysdate(); get_lock(); is_free_lock(); is_used_lock(); master_pos_wait(); rand(); release_lock(); sleep(); version();
• 在日志中出现如下警告信息的不能正确复制：[Warning] Statement is not safe to log in statement format.
• 或者在客户端中出现show warnings
• Insert … select语句会执行大量的行级锁表
• Update语句会执行大量的行级锁表来扫描整个表

RBR的特点

主服务器把表的行变化作为事件写入二进制日志
优点:

• 所有的数据变化都是被复制,这是最安全的复制方式
• 更少的行级锁
  缺点:
• 日志会很大
• 不能通过查看日志来审计执行过的sql语句，不过可以通过mysqlbinlog --base64-output=decode-rows --verbose来查看数据的 变动

MBR

即使用SBR，又使用RBR，默认使用SBR

主从复制工作流程

1. slave端的SLAVE_IO_RUNNING进程连接上master端，提交主库二进制日志文件名及指定的位置(POS)
2. master端负责复制的IO进程根据文件名及起始位置读取起始位置及以后的数据，发送给slave端的IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在master端的binary log文件的名称以及在binary log中的位置
3. slave端的IO线程收到信息（二进制日志事件，binary log events）后，将接收到的日志内容依次写入到save端的relay log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的bin-log的文件名及位置记录到master-info(也可能是mysql.slave_master_info表)文件中
4. slave端的SLAVE_SQL_RUNNING进程检测到relay log 中新增加的内容后，会马上解析改log文件的内容,slave端重做中继日志中的事件，将改变反映它自己的数据。

下图描述了复制的过程：

Paste_Image.png

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。
多级主从：

Paste_Image.png

主从复制参数

server-id = 11 #主从集群中不能重复,唯一ID
log_bin = mysql-bin #启用二进制日志

binlog_format = row #配置二进制日志格式

log_timestamps = SYSTEM #主要是控制 error log、genera log，等等记录日志的显示时间参数

slave-parallel-workers=16 # 从库启用多线程同步，默认为0,并行执行被禁止
slave-parallel-type=LOGICAL_CLOCK # 针对slave-parallel-workers参数，多进程同步时，默认是每个库同一时间只能有一个work进程来进行工作，如果只有一个库，设置LOGICAL_CLOCK来解除这个限制，默认是DATABASE

log_slave_updates=true #从库从主库更新操作也记录从的binlog日志（主 --> 从 --> 从）连级复制时中间的从库必须启用
binlog_checksum=CRC32 #binlog做认证
master_verify_checksum=1
slave_sql_verify_checksum=1
binlog_rows_query_log_events=1 #在RBS模式中，binlog日志及relay log日志中记录具体行的变化内容，也会记录原始的sql语句（注释掉）

master_info_repository=TABLE #是否将主状态和连接信息记录到 FILE（master.info）或a TABLE （mysql.slave_master_info）

relay_log_info_repository=TABLE #取决于服务器的 relay_log_info_repository 设置（FILE或 TABLE）

如下三个参数控制mysql.slave_master_info与mysql.slave_relay_log_info表的刷新频率
sync_master_info=1 #若master-info-repository为FILE，当设置为0，则每次sync_master_info事件都会刷新到磁盘，默认为10000次刷新到磁盘；若master-info-repository为TABLE，当设置为0，则表不做任何更新，设置为1，则每次事件会更新表 #默认为10000
sync_relay_log = 1 #默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制。
sync_relay_log_info = 1 #若relay_log_info_repository为FILE，当设置为0，交由OS刷新磁盘，默认为10000次刷新到磁盘；若relay_log_info_repository为TABLE，且为INNODB存储，则无论为任何值，则都每次evnet都会更新表。

relay_log_recovery=ON #当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的

binlog-row-image=minimal #针对row模式，参数默认值是FULL，为minimal：binlog记录的就只是影响后的行

server-id = ID
log_bin = mysql-bin
binlog_format = row
log_timestamps = SYSTEM
slave_parallel_workers=16
slave_parallel_type=LOGICAL_CLOCK
log_slave_updates=true
binlog_rows_query_log_events=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_master_info=1
sync_relay_log = 1
sync_relay_log_info = 1
relay_log_recovery=ON
binlog_row_image=minimal

复制过滤

两种思路，一种是在主库上过滤，一种是从库上过滤

Paste_Image.png

主库上面仅向二进制日志记录有特定相关数据库写操作；
问题：即时点还原将无法全面恢复

• binlog_do_db=
• binlog_ignore_db=

从库的SQL_THREAD仅在中继日志中读取特定数据相关的语句并应用到本地
问题： 会造成网络带宽及磁盘IO浪费

replicate_do_db=
replicate_ignre_db=

replicate_db_table=
replicate_ignore_table=

replicate_wild_do_table=
replicate_wild_ignore_table=

实例：

replicate-wild-do-table=xxx_db.%
replicate-wild-do-table=yxxx_db.%

replicate-wild-ignore-table =mysql.%
replicate-wild-ignore-table =sys.%
replicate-wild-ignore-table =information_schema.%
replicate-wild-ignore-table =performance_schema.%

主从复制配置

有很多种配置主从同步的方法，可以总结为如下的步骤：

1. 在master端开启二进制日志和配置独立的ID
2. 在每一个slave端配置一个唯一的ID，创建一个用来专门复制master端数据的账号
3. 在开始复制前，在master端上记录二进制文件位置信息
4. 如果在开始复制之前，master端中已经有数据，就必须先建一个数据快照
5. 配置slave端要连接的master端的IP地址和登陆授权，二进制日志名称及位置

在master端建主从复制专用用户
CREATE USER 'userrepl'@'%' IDENTIFIED BY 'userpasswd';
GRANT REPLICATION SLAVE ON . TO 'userrepl'@'%';

基本配置参数
log-bin=mysql-bin
server-id=1 (每个主机的ID唯一)

slave-parallel-workers=16
slave-parallel-type=LOGICAL_CLOCK
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
binlog-row-image=minimal

传统方式

从上配置连接主库的信息
CHANGE MASTER TO
-> MASTER_HOST='Master_IP',
-> MASTER_USER='userrepl',
-> MASTER_PASSWORD='userpasswd',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=xxx;

GTID方式

gtid事物复制 5.6版本出现 主和从都开启还需要添加旧版本的配置
gtid_mode = on
enforce-gtid-consistency=true

change master to
-> master_host='192.168.1.102',
-> master_user='repuser',
-> master_password='reppasswd',
-> master_port=3306,
-> master_auto_position = 1,
-> master_delay=30; 配置同步延时

半同步方式

主节点：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.05 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout=2000;
Query OK, 0 rows affected (0.00 sec)

从节点：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.05 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

如果从服务器已经启动，还需要重启IO_THREAD;

主从复制状态

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xx.xx.xx
Master_User: xxrepl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000061
Read_Master_Log_Pos: 48850622
Relay_Log_File: r430db02-relay-bin.000180
Relay_Log_Pos: 48850835
Relay_Master_Log_File: mysql-bin.000061
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: y_db.%,yi_db.%,b_db.%,u_cms.%
Replicate_Wild_Ignore_Table: mysql.%,sys.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 48850622
Relay_Log_Space: 48851092
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: 11
Master_UUID: a614d748-1cd6-11e6-a130-14187760f283
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: 35e7d774-b8d1-11e5-948c-00163e0c1173:1-766221
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

GTID

MySQL [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xx.xx.xx
Master_User: xxrepl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 12844624
Relay_Log_File: vm10-20-10-6-relay-bin.000002
Relay_Log_Pos: 12844837
Relay_Master_Log_File: mysql-bin.000001
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: mysql.%,sys.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12844624
Relay_Log_Space: 12845051
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: 68
Master_UUID: d5197ac0-5996-11e7-8f1d-fa163e7c7d02
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: d5197ac0-5996-11e7-8f1d-fa163e7c7d02:1-9459
Executed_Gtid_Set: d5197ac0-5996-11e7-8f1d-fa163e7c7d02:1-9459
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

复制的监控及维护

清理日志：PURGE
复制监控:

• SHOW MASTER STATUS
• SHOW BINLOG EVENTS
• SHOW BINARY LOGS
• SHOW SLAVE STATUS

多源复制slave启动和停止
启动所有
START SLAVEthread_types;
启动一个
start slave for channel "master50";
停止
STOP SLAVEthread_types;
stop slave for channel "master50";
查看
show slave status\G
show slave status for channel "master50"\G
重置
RESET SLAVE;
RESET SLAVE FOR CHANNELchannel;

如何判断slave是否落后于master
Seconds_Behind_Master: 0

如何确定主从节点数据是否一致？
通过表自身的CHECKSUM检查
使用percona-tools中pt-table-checksum

数据不一致的修复方法：重复复制；

参考资料：
http://wangwei007.blog.51cto.com/68019/965575
http://blog.csdn.net/hguisu/article/details/7325124
http://www.cnblogs.com/kylinlin/p/5258719.html
https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html