mysql主从复制
主从复制慨述
构建大型，高性能应用程序的基础
主服务器复制负责更新，且将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环
从服务器主动连接主库，通知主服务器从日志中读取的最后一次更新的位置（第一次的话就是开始位置）
从服务器接受从那时起发生的任何更新，任何封锁并等待主库通知新的更新
注意：任何数据更新都必须在主服务器上进行，主能读写，从只能读
使用主从的好处
- 提高数据库读的性能，主库专注写操作，从可以多个，分散读操作
- 提高数据安全，在从库做备份不会影响主库相关数据
能解决的问题
- 数据分布(data distribution)
- 负载均衡(load balancing)
- 备份(backups)
- 高可用性和容错行 High availability and failover，故障切换
mysql支持的复制方法
- statement based replication(SBR) 基于SQL语句的复制: 主服务器上执行的sql,在从服务器上同样执行,5.6默认使用SBR,
- row based replication(RBR) 基于行的复制: 把改变的内容复制过去,而不是把命令在从服务器上执行一遍,从5.0开始支持
- mixed based replication(MBR) 混合型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确复制时,就采用基于行的复制
- 基于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
主从复制工作流程
- slave端的SLAVE_IO_RUNNING进程连接上master端，提交主库二进制日志文件名及指定的位置(POS)
- master端负责复制的IO进程根据文件名及起始位置读取起始位置及以后的数据，发送给slave端的IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在master端的binary log文件的名称以及在binary log中的位置
- slave端的IO线程收到信息（二进制日志事件，binary log events）后，将接收到的日志内容依次写入到save端的relay log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的bin-log的文件名及位置记录到master-info(也可能是mysql.slave_master_info表)文件中
- 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.%
主从复制配置
有很多种配置主从同步的方法，可以总结为如下的步骤：
- 在master端开启二进制日志和配置独立的ID
- 在每一个slave端配置一个唯一的ID，创建一个用来专门复制master端数据的账号
- 在开始复制前，在master端上记录二进制文件位置信息
- 如果在开始复制之前，master端中已经有数据，就必须先建一个数据快照
- 配置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
网友评论