mysql 主从复制

最新推荐文章于 2021-08-16 10:07:28 发布

ciqiyuan9775

最新推荐文章于 2021-08-16 10:07:28 发布

阅读量137

点赞数

一.主从复制原理

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
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
无法在外键的表中插入或者更新参考主键没有的数据

(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线程执行完成之后，再执行当前的日志

点击(此处)折叠或打开

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，则表示为同组提交的事务，可以进行并行回放。

2.并行复制相关参数
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/

ciqiyuan9775

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 主从复制

一.主从复制原理1.主从同步过程-slave发送请求在slave节点start slave后，会向master进行连接，并且请求从binlog的指定位置开始同步。-Master接受请求并发送数据maste...
复制链接

扫一扫