一.主从复制原理
![](//img.blog.itpub.net/blog/attachment/201804/21/15412087_1524299788B24I.png?x-oss-process=style/bb)
1.主从同步过程
-slave发送请求
在slave节点start slave后,会向master进行连接,并且请求从binlog的指定位置开始同步。
-Master接受请求并发送数据
master收到收到slave的IO线程的同步请求后,master的IO线程开始根据请求信息读取指定日志位置的日志信息,返回给slave端的io线程,这个 信息包括了日志内容,binlog的文件名称和位置。
-slave写入relay log
slave的IO线程接收到master的消息后,开始将这个消息写入到relay_log的末尾,同时把master的位置信息写入到master.info中,下次同步 会读取master.info的位置信息。
-Slave执行解析后的sql
slave的sql线程检测到relay log中新增的内容后,会进行相关的日志解析,生成对应的语句,并且在slave执行这些语句。 最终的实质是,在slave端执行了和master同样的sql语句
二.环境配置信息
os:linux redhat 7.0 64bit
mysql1(主) 109.115.12.40
mysql2(从) 109.115.12.41
mysql3(从) 109.115.12.42
三.主-从复制配置
1.修改配置文件
-必要参数
server-id=12403306 (主从环境中id唯一)
log-bin=/mysql/mysql3306/mysql3306/mysql-bin
relay_log=/mysql/mysql3306/mysql3306/mysql-relay (备库生效)
-过滤
replicate-wild-ignore-table=mysql.% (备库生效,过滤mysql库下所有表)
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
不要在主库上使用binlog-do-db,或binlog-ignore-db选项,也不要在从库上使用replicate-do-db 或 replicate-ignore-db选项,因为这样可能产生跨库更新失败问题。
推荐从库上使用replicate-wild-do-table 和replicate-wild-ignore-table 两个选项来解决复制过滤问题。
-其他
log_slave_updates = 1
用来配置从服务器上的更新操作是否写入binlog
read_only
read-only选项:对所有的非临时表进行只读控制。但是有两种特殊情况
(1).对replication threads例外,以保证slave能够正常的进行replication。
(2).对于拥有super权限的用户,可以ignore这个选项。
master_verify_checksum
主要用于复制事件校验。当一个event被写入binary log(二进制日志)的时候,checksum也同时写入binary log,然后在event通过网络传输到从服务器(slave)之后,再在从服务器中对其进行验证并写入从服务器的relay log。由于每一步都记录了event和checksum,所以我们可以很快地找出问题所在。
2.同步数据
(1).主库执行全量备份
innobackupex --defaults-file=/etc/my5.7_3306.cnf --user=root --password=root --no-timestamp /mysql/backup/`date +%H-%M`.dbname
(2).将备份拷贝至从库
(3).在从库恢复备份
-关闭数据库
/usr/local/mysql/bin/mysqladmin -S /tmp/mysql3306.sock shutdown -p
-移除datadir
-恢复
innobackupex --defaults-file=/etc/my5.7_3306.cnf --user=root --apply-log /mysql/backup/11-21.dbname
innobackupex --defaults-file=/etc/my5.7_3306.cnf --user=root --copy-back /mysql/backup/11-21.dbname
-开启数据库
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my5.7_3306.cnf --user=mysql &
(4).查看从库进度
cat xtrabackup_info
binlog_pos = filename 'mysql-bin.000004', position '154'
3.在主库中创建复制专用用户
4.查看主库状态
5.在从库上指定同步主库的IP,用户,密码,binlog文件,以及同步开始位置
6.查看从库状态
7.启动slave
start slave; (在从库执行)
stop slave;
Reset slave all;
8.复制相关信息说明
Mysql复制参数说明:
Slave_IO_state:显示当前IO线程的状态,一般情况下就是显示等待主服务器发送二进制日志。
Master_log_file:显示当前同步的主服务器的二进制日志。
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:表示同步到主服务器的二进制日志的偏移量位置。
9.线上主从复制注意事项
注意事项:
线上主从同步的过程中,需要注意的是,在主库如果进行同步的过程中吗,如果主库含有MySIAM表结构,那么会造成主从不一致的现象。这个现象的原因可能是来自于MySIAM不支持事务,因此在同步过程中造成了相关的错误,避免这个问题的方法是对主库的MySIAM表修改存储引擎为INNODB存储引擎(这点可能需要结合开发进行操作,一般的MySaim操作引擎都是一些比较旧的实例,因为目前大多数实例,或者在开发过程中都是使用innodb存储引擎)
10.保证主从一致性
innodb_flush_log_at_trx_commit=1
sync_binlog=1
sync_master_info=1 #每间隔多少事务刷新master.info,如果是table(innodb)设置无效,每个事务都会更新
sync_relay_log_info=1 #每间隔多少事务刷新relay-log.info,如果是table(innodb)设置无效,每个事务都会更新
sync_relay_log=10000 #默认为10000,即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新,交由OS的cache控制
master_info_repository=TABLE #记录主库binlog的信息,可以设置FILE(master.info)或者TABLE(mysql.slave_master_info)
relay_log_info_repository=TABLE #记录备库relaylog的信息,可以设置FILE(relay-log.info)或者TABLE(mysql.slave_relay_log_info)
四.常见问题
1. IO线程出问题
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
问题排查:在主库上
[root@master mysql3306]# cat mysql-bin.index
/home/mysql3306/mysql3306/mysql-bin.000001
发现binlog文件名不正确,需要重新设置。
处理办法
stop slave;
reset slave all;
change master to master_host='192.168.3.10',master_user='rep',master_password='rep', master_log_file='mysql-bin.000001',master_log_pos=154,MASTER_PORT=3306;
start slave;
2.SQL线程问题
从库插入数据:
mysql> insert into wwj.t3 values(6,60);
Query OK, 1 row affected (0.01 sec)
主库插入数据:
mysql> insert into wwj.t3 values(6,60);
Query OK, 1 row affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.203
Master_User: REPL_USER
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 407
Relay_Log_File: mysql-relay.000005
Relay_Log_Pos: 273
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 376. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 942
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 376. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12033306
Master_UUID: 04bcef95-3a1b-11e8-9795-000c29720fc6
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180419 23:28:08
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
查看error log, 根据错误日志发现从库已经有 pk = 6的数据
2018-04-19T23:28:08.716025+08:00 5 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 376; Could not execute Write_rows event on table wwj.t3; Duplicate entry '6' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 376, Error_code: 1062
在从库上执行跳过该insert语句
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
问题解决!
(2). ERROR 1032
从库删除一条数据,主库更新该条删除的数据,发生报错
从库:
mysql> delete from wwj.t3 where idt3=3;
主库:
mysql> update wwj.t3 set idt4=300 where idt3=3;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.203
Master_User: REPL_USER
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 670
Relay_Log_File: mysql-relay.000006
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 639. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0
Exec_Master_Log_Pos: 407
Relay_Log_Space: 1158
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 639. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12033306
Master_UUID: 04bcef95-3a1b-11e8-9795-000c29720fc6
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180419 23:40:07
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
查看错误日志:
mysql> select * from performance_schema.replication_applier_status_by_worker limit 1\G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: ANONYMOUS
LAST_ERROR_NUMBER: 1032
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 639; Could not execute Update_rows event on table wwj.t3; Can't find record in 't3', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 639
LAST_ERROR_TIMESTAMP: 2018-04-19 23:40:07
查看主库binlog
/usr/local/mysql/bin/mysqlbinlog -vv /home/mysql3306/mysql3306/mysql-bin.000005 > /tmp/haha.sql
BINLOG '
WrjYWhManbcALQAAAEkCAAAAANwAAAAAAAEAA3d3agACdDMAAgMDAABxhKzO
WrjYWh8anbcANgAAAH8CAAAAANwAAAAAAAEAAgAC///8AwAAAB4AAAD8AwAAACwBAACjBgG0
'/*!*/;
### UPDATE `wwj`.`t3`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=30 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=300 /* INT meta=0 nullable=0 is_null=0 */
# at 639
在从库重新插入数据
insert into wwj.t3 values(3,30);
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
问题解决!
(3).ERROR 1452
无法在外键的表中插入或者更新参考主键没有的数据
(4).将从库设置为readonly
建议大家线上打开这两个参数,防止主从不一致的产生。
set global read_only=1
只读参数,需要大家注意的对于拥有super权限的用户,该参数不起作用
set global super_read_only=1(5.7新参数)
如果想对拥有super权限用户只读,使用super_read_only,开启该参数后,read_only会自动开启。
3.主从延迟问题
(1).从库同步延迟的现象
- show slave status显示参数Seconds_Behind_Master不为0,这个数值可能会很大
- show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大,说明bin-log在从库上没有及时同步,所以近期执行的bin-log和当前IO线程所读的bin-log相差很大
- MySQL的从库数据目录下存在大量mysql-relay-log日志,该日志同步完成之后就会被系统自动删除,存在大量日志,说明主从同步延迟很厉害
(2).产生原因
当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能承受的范围,那么延时就产生了,当然还有就是可能与slave的大型query语句产生了锁等待。
首要原因:数据库在业务上读写压力太大,CPU计算负荷大,网卡负荷大,硬盘随机IO太高
次要原因:读写binlog带来的性能影响,网络传输延迟。
(3).解决方案
- 更换从库更快的硬盘
- 网络,网卡,更换带宽更大的网卡
mysql考虑 从库作为备份数据库来说
- 增加从库的innodb_buffer_pool_size,可以缓存更多数据防止由于转换造成的IO压力
- 增加innodb_log_file_size和innodb_log_files_in_group,减少buffer落盘
- 修改参数innodb_flush_method,提高写入性能(SSD强烈推荐使用)
- 从库binlog关闭(如果可以) log_slave_updates关闭
- 修改innodb_flush_log_at_trx_commit为0或者2
- 修改master_info_repository和relay_log_info_repository为TABLE,防止直接落盘压力
- 升级5.7
五. MySQL 5.7并发复制
mysql5.5只支持一个SQL线程复制
mysql5.6只支持不同库之间的并行复制
mysql5.7支持同库表之间的并行复制
1.并发访问原理
多线程的思路就是把sql_thread 变成分发线程,然后由一组worker_thread来负责执行。
SQL线程作用
(1).判断事务是否可以并行,若判断可以并行执行,那么选择worker线程执行事务的二进制日志
(2).若判断不可以并行执行,如该操作是DDL,亦或者是事务跨schema操作,则等待所有的worker线程执行完成之后,再执行当前的日志
2.并行复制相关参数
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
![](http://img.blog.itpub.net/blog/attachment/201804/21/15412087_1524299788B24I.png?x-oss-process=style/bb)
1.主从同步过程
-slave发送请求
在slave节点start slave后,会向master进行连接,并且请求从binlog的指定位置开始同步。
-Master接受请求并发送数据
master收到收到slave的IO线程的同步请求后,master的IO线程开始根据请求信息读取指定日志位置的日志信息,返回给slave端的io线程,这个 信息包括了日志内容,binlog的文件名称和位置。
-slave写入relay log
slave的IO线程接收到master的消息后,开始将这个消息写入到relay_log的末尾,同时把master的位置信息写入到master.info中,下次同步 会读取master.info的位置信息。
-Slave执行解析后的sql
slave的sql线程检测到relay log中新增的内容后,会进行相关的日志解析,生成对应的语句,并且在slave执行这些语句。 最终的实质是,在slave端执行了和master同样的sql语句
二.环境配置信息
os:linux redhat 7.0 64bit
mysql1(主) 109.115.12.40
mysql2(从) 109.115.12.41
mysql3(从) 109.115.12.42
三.主-从复制配置
1.修改配置文件
-必要参数
server-id=12403306 (主从环境中id唯一)
log-bin=/mysql/mysql3306/mysql3306/mysql-bin
relay_log=/mysql/mysql3306/mysql3306/mysql-relay (备库生效)
-过滤
replicate-wild-ignore-table=mysql.% (备库生效,过滤mysql库下所有表)
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
不要在主库上使用binlog-do-db,或binlog-ignore-db选项,也不要在从库上使用replicate-do-db 或 replicate-ignore-db选项,因为这样可能产生跨库更新失败问题。
推荐从库上使用replicate-wild-do-table 和replicate-wild-ignore-table 两个选项来解决复制过滤问题。
-其他
log_slave_updates = 1
用来配置从服务器上的更新操作是否写入binlog
Skip-slave-start=1 当slave数据库启动的时候,不会自动开启复制
read-only选项:对所有的非临时表进行只读控制。但是有两种特殊情况
(1).对replication threads例外,以保证slave能够正常的进行replication。
(2).对于拥有super权限的用户,可以ignore这个选项。
master_verify_checksum
主要用于复制事件校验。当一个event被写入binary log(二进制日志)的时候,checksum也同时写入binary log,然后在event通过网络传输到从服务器(slave)之后,再在从服务器中对其进行验证并写入从服务器的relay log。由于每一步都记录了event和checksum,所以我们可以很快地找出问题所在。
2.同步数据
(1).主库执行全量备份
innobackupex --defaults-file=/etc/my5.7_3306.cnf --user=root --password=root --no-timestamp /mysql/backup/`date +%H-%M`.dbname
(2).将备份拷贝至从库
(3).在从库恢复备份
-关闭数据库
/usr/local/mysql/bin/mysqladmin -S /tmp/mysql3306.sock shutdown -p
-移除datadir
-恢复
innobackupex --defaults-file=/etc/my5.7_3306.cnf --user=root --apply-log /mysql/backup/11-21.dbname
innobackupex --defaults-file=/etc/my5.7_3306.cnf --user=root --copy-back /mysql/backup/11-21.dbname
-开启数据库
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my5.7_3306.cnf --user=mysql &
(4).查看从库进度
cat xtrabackup_info
binlog_pos = filename 'mysql-bin.000004', position '154'
3.在主库中创建复制专用用户
点击(此处)折叠或打开
- GRANT REPLICATION SLAVE ON *.* TO 'REPL_USER'@'109.115.12.41' IDENTIFIED BY 'repl1234';
- flush privilege;
- mysql> select user,host from mysql.user;
- +---------------+---------------+
- | user | host |
- +---------------+---------------+
- | root | % |
- | REPL_USER | 109.115.12.41 |
- | mysql.session | localhost |
- | mysql.sys | localhost |
- | root | localhost |
- +---------------+---------------+
4.查看主库状态
点击(此处)折叠或打开
- mysql> show master status;
- +------------------+----------+--------------+------------------+-------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +------------------+----------+--------------+------------------+-------------------+
- | mysql-bin.000004 | 606 | | | |
- +------------------+----------+--------------+------------------+-------------------+
5.在从库上指定同步主库的IP,用户,密码,binlog文件,以及同步开始位置
点击(此处)折叠或打开
- CHANGE MASTER TO MASTER_HOST='109.115.12.40',MASTER_USER='REPL_USER',MASTER_PASSWORD='repl1234',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=154
6.查看从库状态
点击(此处)折叠或打开
- mysql> show slave status\G;
- *************************** 1. row ***************************
- Slave_IO_State:
- Master_Host: 109.115.12.40
- Master_User: REPL_USER
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000004
- Read_Master_Log_Pos: 154
- Relay_Log_File: mysql-relay.000001
- Relay_Log_Pos: 4
- Relay_Master_Log_File: mysql-bin.000004
- Slave_IO_Running: No
- Slave_SQL_Running: No
- 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: 154
- Relay_Log_Space: 154
- 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: NULL
- 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: 0
- Master_UUID:
- Master_Info_File: mysql.slave_master_info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State:
- 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:
- Auto_Position: 0
- Replicate_Rewrite_DB:
- Channel_Name:
- Master_TLS_Version:
- 1 row in set (0.00 sec)
7.启动slave
start slave; (在从库执行)
stop slave;
Reset slave all;
8.复制相关信息说明
Mysql复制参数说明:
Slave_IO_state:显示当前IO线程的状态,一般情况下就是显示等待主服务器发送二进制日志。
Master_log_file:显示当前同步的主服务器的二进制日志。
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:表示同步到主服务器的二进制日志的偏移量位置。
9.线上主从复制注意事项
注意事项:
线上主从同步的过程中,需要注意的是,在主库如果进行同步的过程中吗,如果主库含有MySIAM表结构,那么会造成主从不一致的现象。这个现象的原因可能是来自于MySIAM不支持事务,因此在同步过程中造成了相关的错误,避免这个问题的方法是对主库的MySIAM表修改存储引擎为INNODB存储引擎(这点可能需要结合开发进行操作,一般的MySaim操作引擎都是一些比较旧的实例,因为目前大多数实例,或者在开发过程中都是使用innodb存储引擎)
10.保证主从一致性
innodb_flush_log_at_trx_commit=1
sync_binlog=1
sync_master_info=1 #每间隔多少事务刷新master.info,如果是table(innodb)设置无效,每个事务都会更新
sync_relay_log_info=1 #每间隔多少事务刷新relay-log.info,如果是table(innodb)设置无效,每个事务都会更新
sync_relay_log=10000 #默认为10000,即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新,交由OS的cache控制
master_info_repository=TABLE #记录主库binlog的信息,可以设置FILE(master.info)或者TABLE(mysql.slave_master_info)
relay_log_info_repository=TABLE #记录备库relaylog的信息,可以设置FILE(relay-log.info)或者TABLE(mysql.slave_relay_log_info)
四.常见问题
1. IO线程出问题
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
问题排查:在主库上
[root@master mysql3306]# cat mysql-bin.index
/home/mysql3306/mysql3306/mysql-bin.000001
发现binlog文件名不正确,需要重新设置。
处理办法
stop slave;
reset slave all;
change master to master_host='192.168.3.10',master_user='rep',master_password='rep', master_log_file='mysql-bin.000001',master_log_pos=154,MASTER_PORT=3306;
start slave;
2.SQL线程问题
(1).ERROR 1062
主从数据库不一致的时,slave已经有该条记录,但是我们又在master上插入了同一条记录,此时就会报错.
mysql> insert into wwj.t3 values(6,60);
Query OK, 1 row affected (0.01 sec)
主库插入数据:
mysql> insert into wwj.t3 values(6,60);
Query OK, 1 row affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.203
Master_User: REPL_USER
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 407
Relay_Log_File: mysql-relay.000005
Relay_Log_Pos: 273
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 376. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 942
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 376. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12033306
Master_UUID: 04bcef95-3a1b-11e8-9795-000c29720fc6
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180419 23:28:08
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
查看error log, 根据错误日志发现从库已经有 pk = 6的数据
2018-04-19T23:28:08.716025+08:00 5 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 376; Could not execute Write_rows event on table wwj.t3; Duplicate entry '6' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 376, Error_code: 1062
在从库上执行跳过该insert语句
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
问题解决!
(2). ERROR 1032
从库删除一条数据,主库更新该条删除的数据,发生报错
从库:
mysql> delete from wwj.t3 where idt3=3;
主库:
mysql> update wwj.t3 set idt4=300 where idt3=3;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.203
Master_User: REPL_USER
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 670
Relay_Log_File: mysql-relay.000006
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 639. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0
Exec_Master_Log_Pos: 407
Relay_Log_Space: 1158
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 639. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12033306
Master_UUID: 04bcef95-3a1b-11e8-9795-000c29720fc6
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180419 23:40:07
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
查看错误日志:
mysql> select * from performance_schema.replication_applier_status_by_worker limit 1\G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: ANONYMOUS
LAST_ERROR_NUMBER: 1032
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000005, end_log_pos 639; Could not execute Update_rows event on table wwj.t3; Can't find record in 't3', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 639
LAST_ERROR_TIMESTAMP: 2018-04-19 23:40:07
查看主库binlog
/usr/local/mysql/bin/mysqlbinlog -vv /home/mysql3306/mysql3306/mysql-bin.000005 > /tmp/haha.sql
BINLOG '
WrjYWhManbcALQAAAEkCAAAAANwAAAAAAAEAA3d3agACdDMAAgMDAABxhKzO
WrjYWh8anbcANgAAAH8CAAAAANwAAAAAAAEAAgAC///8AwAAAB4AAAD8AwAAACwBAACjBgG0
'/*!*/;
### UPDATE `wwj`.`t3`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=30 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=300 /* INT meta=0 nullable=0 is_null=0 */
# at 639
在从库重新插入数据
insert into wwj.t3 values(3,30);
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
问题解决!
(3).ERROR 1452
无法在外键的表中插入或者更新参考主键没有的数据
建议大家线上打开这两个参数,防止主从不一致的产生。
set global read_only=1
只读参数,需要大家注意的对于拥有super权限的用户,该参数不起作用
set global super_read_only=1(5.7新参数)
如果想对拥有super权限用户只读,使用super_read_only,开启该参数后,read_only会自动开启。
3.主从延迟问题
(1).从库同步延迟的现象
- show slave status显示参数Seconds_Behind_Master不为0,这个数值可能会很大
- show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大,说明bin-log在从库上没有及时同步,所以近期执行的bin-log和当前IO线程所读的bin-log相差很大
- MySQL的从库数据目录下存在大量mysql-relay-log日志,该日志同步完成之后就会被系统自动删除,存在大量日志,说明主从同步延迟很厉害
(2).产生原因
当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能承受的范围,那么延时就产生了,当然还有就是可能与slave的大型query语句产生了锁等待。
首要原因:数据库在业务上读写压力太大,CPU计算负荷大,网卡负荷大,硬盘随机IO太高
次要原因:读写binlog带来的性能影响,网络传输延迟。
(3).解决方案
- 更换从库更快的硬盘
- 网络,网卡,更换带宽更大的网卡
mysql考虑 从库作为备份数据库来说
- 增加从库的innodb_buffer_pool_size,可以缓存更多数据防止由于转换造成的IO压力
- 增加innodb_log_file_size和innodb_log_files_in_group,减少buffer落盘
- 修改参数innodb_flush_method,提高写入性能(SSD强烈推荐使用)
- 从库binlog关闭(如果可以) log_slave_updates关闭
- 修改innodb_flush_log_at_trx_commit为0或者2
- 修改master_info_repository和relay_log_info_repository为TABLE,防止直接落盘压力
- 升级5.7
五. MySQL 5.7并发复制
mysql5.5只支持一个SQL线程复制
mysql5.6只支持不同库之间的并行复制
mysql5.7支持同库表之间的并行复制
1.并发访问原理
多线程的思路就是把sql_thread 变成分发线程,然后由一组worker_thread来负责执行。
![](http://img.blog.itpub.net/blog/attachment/201804/24/15412087_1524574722a08Q.png?x-oss-process=style/bb)
SQL线程作用
(1).判断事务是否可以并行,若判断可以并行执行,那么选择worker线程执行事务的二进制日志
(2).若判断不可以并行执行,如该操作是DDL,亦或者是事务跨schema操作,则等待所有的worker线程执行完成之后,再执行当前的日志
点击(此处)折叠或打开
- mysqlbinlog mysql-bin.0000006 | grep last_committed
- #150520 14:23:11 server id 88 end_log_pos 259 CRC32 0x4ead9ad6 GTID last_committed=0 sequence_number=1
- #150520 14:23:11 server id 88 end_log_pos 1483 CRC32 0xdf94bc85 GTID last_committed=0 sequence_number=2
- #150520 14:23:11 server id 88 end_log_pos 2708 CRC32 0x0914697b GTID last_committed=0 sequence_number=3
- #150520 14:23:11 server id 88 end_log_pos 3934 CRC32 0xd9cb4a43 GTID last_committed=0 sequence_number=4
- #150520 14:23:11 server id 88 end_log_pos 5159 CRC32 0x06a6f531 GTID last_committed=0 sequence_number=5
- #150520 14:23:11 server id 88 end_log_pos 6386 CRC32 0xd6cae930 GTID last_committed=0 sequence_number=6
- #150520 14:23:11 server id 88 end_log_pos 7610 CRC32 0xa1ea531c GTID last_committed=6 sequence_number=7
- #事务如果具有相同的last_committed,则表示为同组提交的事务,可以进行并行回放。
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15412087/viewspace-2153117/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15412087/viewspace-2153117/