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

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 157,298评论 4赞 360
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 66,701评论 1赞 290
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 107,078评论 0赞 237
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,687评论 0赞 202
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,018评论 3赞 286
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,410评论 1赞 211
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,729评论 2赞 310
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,412评论 0赞 194
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,124评论 1赞 239
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,379评论 2赞 242
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,903评论 1赞 257
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,268评论 2赞 251
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 32,894评论 3赞 233
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,014评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,770评论 0赞 192
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,435评论 2赞 269
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,312评论 2赞 260

推荐阅读更多精彩内容