时间：2021-07-01 10:21:17 帮助过：10人阅读
| ROLE | HOSTNAME | BASEDIR | DATADIR | IP | PORT |
| Master | ZST1 | /usr/local/mysql | /data/mysql/mysql3306/data | 192.168.85.132 | 3306 |
| Slave | ZST1 | /usr/local/mysql | /data/mysql/mysql3308/data | 192.168.85.132 | 3308 |
官方社区版MySQL 5.7.19 基于Row+Position搭建的一主一从异步复制结构：Master->{Slave}。每次使用备份文件还原数据库后，重新搭建这个复制结构
备份工具版本：mysqldump、mysqlpump是MySQL 5.7.19中自带的；mydumper version 0.9.3、innobackupex version 2.4.8
创建两张测试表
# 创建两张测试表(192.168.85.132,3306) use replcrash; create table py_user_innodb( uid int not null auto_increment, name varchar(32), add_time datetime default current_timestamp, server_id varchar(10), primary key(uid), key(name) )engine=innodb; create table py_user_myisam( uid int not null auto_increment, name varchar(32), add_time datetime default current_timestamp, server_id varchar(10), primary key(uid), key(name) )engine=myisam;View Code
运行下面的脚本持续往测试表中写入数据
#!/user/bin/python import string import random import MySQLdb import time conn = MySQLdb.connect(host=‘192.168.85.132‘, port=3306, user=‘mydba‘, passwd=‘mysql5719‘, db=‘replcrash‘) """ create table py_user( uid int not null auto_increment, name varchar(32), add_time datetime default current_timestamp, server_id varchar(10), primary key(uid), key(name) ); """ while True: r_name = ‘‘.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(random.randint(20,30))) print r_name cursor = conn.cursor() cursor.execute("insert into py_user_myisam(name,add_time,server_id) values(‘%s‘,now(),@@server_id);" % str(r_name)) cursor.execute("insert into py_user_innodb(name,add_time,server_id) values(‘%s‘,now(),@@server_id);" % str(r_name)) conn.commit() time.sleep(0.001)Python DML
开启general_log，用来查看mysqldump执行过程
# 开启general_log mydba@192.168.85.132,3306 [replcrash]> set global general_log_file=‘/data/mysql/mysql3306/data/mysql-general.log‘; mydba@192.168.85.132,3306 [replcrash]> set global general_log=1; # 清空general_log [root@ZST1 logs]# cat /dev/null > /data/mysql/mysql3306/data/mysql-general.log # 备份replcrash数据库 [root@ZST1 backup]# mysqldump -h127.0.0.1 -P3306 -uroot -p --single-transaction --master-data=2 replcrash >/data/backup/replcrash_dump_1323306_`date +%Y%m%d`.sqlView Code
使用备份文件搭建复制
# 还原实例清空GTID信息 mydba@192.168.85.132,3308 [replcrash]> reset master; # 还原数据 [root@ZST1 backup]# mysql -h127.0.0.1 -P3308 -uroot -p replcrash </data/backup/replcrash_dump_1323306_`date +%Y%m%d`.sql # 搭建复制 mydba@192.168.85.132,3308 [replcrash]> change master to master_host=‘192.168.85.132‘, master_port=3306, master_user=‘repl‘, master_password=‘repl‘, master_auto_position=1; # 启动复制，查看复制状态 mydba@192.168.85.132,3308 [replcrash]> start slave; mydba@192.168.85.132,3308 [replcrash]> show slave status\G *************************** 1. row *************************** Master_Log_File: mysql-bin.000183 Read_Master_Log_Pos: 1541377 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 741 Relay_Master_Log_File: mysql-bin.000183 Slave_IO_Running: Yes Slave_SQL_Running: No Exec_Master_Log_Pos: 1042768 Last_SQL_Errno: 1062 Last_SQL_Error: Could not execute Write_rows event on table replcrash.py_user_myisam; Duplicate entry ‘332‘ for key ‘PRIMARY‘, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event‘‘s master log mysql-bin.000183, end_log_pos 1043062 Retrieved_Gtid_Set: 8ab82362-9c37-11e7-a858-000c29c1025c:251874-253268 Executed_Gtid_Set: 8ab82362-9c37-11e7-a858-000c29c1025c:1-251874 Auto_Position: 1View Code
从上面的结果中可以看到，主键冲突了，在从库查询一下这个表中大于等于冲突key的数据
# 查询从库出错表大于等于冲突key的数据 mydba@192.168.85.132,3308 [replcrash]> select * from replcrash.py_user_myisam where uid>=332; +-----+--------------------------------+---------------------+-----------+ | uid | name | add_time | server_id | +-----+--------------------------------+---------------------+-----------+ | 332 | X1LME9HO5V7WXNOKBVZE | 2018-01-02 09:05:07 | 1323306 | | 333 | 2PBFQ7KS4BPIJ27G88EYXWEDSX5 | 2018-01-02 09:05:07 | 1323306 | | 334 | E85Y2SS9UD0FZG4YGCNTRSWA8L | 2018-01-02 09:05:07 | 1323306 | | 335 | Y2TQOEVJ58NN7EREL4WRZ | 2018-01-02 09:05:07 | 1323306 | | 336 | O0MEATAXYIAE2V2IZG96YVQ56WEUHF | 2018-01-02 09:05:07 | 1323306 | | 337 | A6QKRWEXHRGUA3V2CH61VXUNBVA3H2 | 2018-01-02 09:05:07 | 1323306 | | 338 | NYCSI1HS61BN6QAVVYTZSC | 2018-01-02 09:05:07 | 1323306 | | 339 | 7CFC1JQPIQGNC97MDTT8ZIMIZL7D | 2018-01-02 09:05:07 | 1323306 | | 340 | GA78AR4Z12WQTEAM41JB | 2018-01-02 09:05:07 | 1323306 | +-----+--------------------------------+---------------------+-----------+ 9 rows in set (0.08 sec)View Code
我们查看mysqldump备份文件获取的binlog pos
[root@ZST1 backup]# more replcrash_dump_1323306_20180102.sql -- GTID state at the beginning of the backup SET @@GLOBAL.GTID_PURGED=‘8ab82362-9c37-11e7-a858-000c29c1025c:1-251873‘; -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000183‘, MASTER_LOG_POS=1042441; [root@ZST1 backup]#View Code
这里的pos信息是mysqldump通过SHOW MASTER STATUS获取。查看mysqldump得到的general-log;
[root@ZST1 data]# vim /data/mysql/mysql3306/data/mysql-general.log ... 2018-01-02T01:05:07.693104Z 10 Query FLUSH /*!40101 LOCAL */ TABLES 2018-01-02T01:05:07.694738Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘7ATZSNFNIBW5DZNMNZYBMV‘,now(),@@server_id) 2018-01-02T01:05:07.701616Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘7ATZSNFNIBW5DZNMNZYBMV‘,now(),@@server_id) 2018-01-02T01:05:07.702139Z 10 Query FLUSH TABLES WITH READ LOCK 2018-01-02T01:05:07.702344Z 9 Query commit 2018-01-02T01:05:07.702411Z 10 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2018-01-02T01:05:07.702597Z 10 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 2018-01-02T01:05:07.702721Z 10 Query SHOW VARIABLES LIKE ‘gtid\_mode‘ 2018-01-02T01:05:07.713019Z 10 Query SELECT @@GLOBAL.GTID_EXECUTED 2018-01-02T01:05:07.713179Z 10 Query SHOW MASTER STATUS 2018-01-02T01:05:07.725821Z 10 Query UNLOCK TABLES 2018-01-02T01:05:07.732125Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘X1LME9HO5V7WXNOKBVZE‘,now(),@@server_id) 2018-01-02T01:05:07.733237Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘X1LME9HO5V7WXNOKBVZE‘,now(),@@server_id) 2018-01-02T01:05:07.734240Z 9 Query commit 2018-01-02T01:05:07.740508Z 10 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘UNDO LOG‘ AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE‘ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘replcrash‘))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 2018-01-02T01:05:07.741895Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘2PBFQ7KS4BPIJ27G88EYXWEDSX5‘,now(),@@server_id) 2018-01-02T01:05:07.742720Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘2PBFQ7KS4BPIJ27G88EYXWEDSX5‘,now(),@@server_id) 2018-01-02T01:05:07.743257Z 9 Query commit 2018-01-02T01:05:07.749840Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘E85Y2SS9UD0FZG4YGCNTRSWA8L‘,now(),@@server_id) 2018-01-02T01:05:07.750588Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘E85Y2SS9UD0FZG4YGCNTRSWA8L‘,now(),@@server_id) 2018-01-02T01:05:07.750989Z 9 Query commit 2018-01-02T01:05:07.754180Z 10 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE‘ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘replcrash‘)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 2018-01-02T01:05:07.756229Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘Y2TQOEVJ58NN7EREL4WRZ‘,now(),@@server_id) 2018-01-02T01:05:07.757030Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘Y2TQOEVJ58NN7EREL4WRZ‘,now(),@@server_id) 2018-01-02T01:05:07.757598Z 9 Query commit 2018-01-02T01:05:07.763629Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘O0MEATAXYIAE2V2IZG96YVQ56WEUHF‘,now(),@@server_id) 2018-01-02T01:05:07.764626Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘O0MEATAXYIAE2V2IZG96YVQ56WEUHF‘,now(),@@server_id) 2018-01-02T01:05:07.765654Z 9 Query commit 2018-01-02T01:05:07.766769Z 10 Query SHOW VARIABLES LIKE ‘ndbinfo\_version‘ 2018-01-02T01:05:07.773997Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘A6QKRWEXHRGUA3V2CH61VXUNBVA3H2‘,now(),@@server_id) 2018-01-02T01:05:07.774757Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘A6QKRWEXHRGUA3V2CH61VXUNBVA3H2‘,now(),@@server_id) 2018-01-02T01:05:07.775198Z 9 Query commit 2018-01-02T01:05:07.779582Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘NYCSI1HS61BN6QAVVYTZSC‘,now(),@@server_id) 2018-01-02T01:05:07.780174Z 10 Init DB replcrash 2018-01-02T01:05:07.780249Z 10 Query SAVEPOINT sp 2018-01-02T01:05:07.780913Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘NYCSI1HS61BN6QAVVYTZSC‘,now(),@@server_id) 2018-01-02T01:05:07.781387Z 9 Query commit 2018-01-02T01:05:07.781776Z 10 Query show tables 2018-01-02T01:05:07.782078Z 10 Query show table status like ‘py\_user‘ 2018-01-02T01:05:07.782400Z 10 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-01-02T01:05:07.782513Z 10 Query SET SESSION character_set_results = ‘binary‘ 2018-01-02T01:05:07.787051Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘7CFC1JQPIQGNC97MDTT8ZIMIZL7D‘,now(),@@server_id) 2018-01-02T01:05:07.787810Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘7CFC1JQPIQGNC97MDTT8ZIMIZL7D‘,now(),@@server_id) 2018-01-02T01:05:07.788502Z 9 Query commit 2018-01-02T01:05:07.788774Z 10 Query show create table `py_user` 2018-01-02T01:05:07.789570Z 10 Query SET SESSION character_set_results = ‘utf8‘ 2018-01-02T01:05:07.789725Z 10 Query show fields from `py_user` 2018-01-02T01:05:07.790423Z 10 Query show fields from `py_user` 2018-01-02T01:05:07.791163Z 10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `py_user` 2018-01-02T01:05:07.791447Z 10 Query SET SESSION character_set_results = ‘binary‘ 2018-01-02T01:05:07.791648Z 10 Query use `replcrash` 2018-01-02T01:05:07.791778Z 10 Query select @@collation_database 2018-01-02T01:05:07.791929Z 10 Query SHOW TRIGGERS LIKE ‘py\_user‘ 2018-01-02T01:05:07.792383Z 10 Query SET SESSION character_set_results = ‘utf8‘ 2018-01-02T01:05:07.792492Z 10 Query ROLLBACK TO SAVEPOINT sp 2018-01-02T01:05:07.792651Z 10 Query show table status like ‘py\_user\_innodb‘ 2018-01-02T01:05:07.792874Z 10 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-01-02T01:05:07.792948Z 10 Query SET SESSION character_set_results = ‘binary‘ 2018-01-02T01:05:07.793024Z 10 Query show create table `py_user_innodb` 2018-01-02T01:05:07.793131Z 10 Query SET SESSION character_set_results = ‘utf8‘ 2018-01-02T01:05:07.793220Z 10 Query show fields from `py_user_innodb` 2018-01-02T01:05:07.793607Z 10 Query show fields from `py_user_innodb` 2018-01-02T01:05:07.793985Z 10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `py_user_innodb` 2018-01-02T01:05:07.794435Z 9 Query insert into py_user_myisam(name,add_time,server_id) values(‘GA78AR4Z12WQTEAM41JB‘,now(),@@server_id) 2018-01-02T01:05:07.795204Z 9 Query insert into py_user_innodb(name,add_time,server_id) values(‘GA78AR4Z12WQTEAM41JB‘,now(),@@server_id) 2018-01-02T01:05:07.795688Z 9 Query commit 2018-01-02T01:05:07.798108Z 10 Query SET SESSION character_set_results = ‘binary‘ 2018-01-02T01:05:07.798205Z 10 Query use `replcrash` 2018-01-02T01:05:07.798303Z 10 Query select @@collation_database 2018-01-02T01:05:07.798408Z 10 Query SHOW TRIGGERS LIKE ‘py\_user\_innodb‘ 2018-01-02T01:05:07.798884Z 10 Query SET SESSION character_set_results = ‘utf8‘ 2018-01-02T01:05:07.798965Z 10 Query ROLLBACK TO SAVEPOINT sp 2018-01-02T01:05:07.799049Z 10 Query show table status like ‘py\_user\_myisam‘ 2018-01-02T01:05:07.799271Z 10 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-01-02T01:05:07.799344Z 10 Query SET SESSION character_set_results = ‘binary‘ 2018-01-02T01:05:07.799420Z 10 Query show create table `py_user_myisam` 2018-01-02T01:05:07.799554Z 10 Query SET SESSION character_set_results = ‘utf8‘ 2018-01-02T01:05:07.799661Z 10 Query show fields from `py_user_myisam` 2018-01-02T01:05:07.800098Z 10 Query show fields from `py_user_myisam` 2018-01-02T01:05:07.800418Z 10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `py_user_myisam`View Code
mysqldump备份过程(--single-transaction --master-data)：
会话先执行FTWRL(实例只读)，然后设置RR隔离级别->START TRANSACTION WITH CONSISTENT SNAPSHOT;->SHOW MASTER STATUS;->UNLOCK TABLES;->SELECT /*!40001 SQL_NO_CACHE */ * FROM `tbname`;
在UNLOCK TABLES解锁后其他事务就可以进行写入操作。general-log中我们可看到 UNLOCK TABLES 到 SELECT /*!40001 SQL_NO_CACHE */ * FROM `py_user_myisam` 之间往py_user_myisam、py_user_innodb各写入9条数据
SELECT /*!40001 SQL_NO_CACHE */ * FROM `py_user_innodb`;读取的是START TRANSACTION WITH CONSISTENT SNAPSHOT建立时的数据
SELECT /*!40001 SQL_NO_CACHE */ * FROM `py_user_myisam`;读取的是最新的数据
再来查看上述过程期间binary log记录
[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000183 |more ... COMMIT/*!*/; # at 1042059 #180102 9:05:07 server id 1323306 end_log_pos 1042124 CRC32 0x221cda50 GTID last_committed=2917 sequence_number=2918 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= ‘8ab82362-9c37-11e7-a858-000c29c1025c:251873‘/*!*/; # at 1042124 #180102 9:05:07 server id 1323306 end_log_pos 1042209 CRC32 0x5df266e4 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1514855107/*!*/; BEGIN /*!*/; # at 1042209 #180102 9:05:07 server id 1323306 end_log_pos 1042279 CRC32 0xc1d41c5f Table_map: `replcrash`.`py_user_myisam` mapped to number 254 # at 1042279 #180102 9:05:07 server id 1323306 end_log_pos 1042355 CRC32 0x27badc02 Write_rows: table id 254 flags: STMT_END_F ### INSERT INTO `replcrash`.`py_user_myisam` ### SET ### @1=331 /* INT meta=0 nullable=0 is_null=0 */ ### @2=‘7ATZSNFNIBW5DZNMNZYBMV‘ /* VARSTRING(96) meta=96 nullable=1 is_null=0 */ ### @3=‘2018-01-02 09:05:07‘ /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @4=‘1323306‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ # at 1042355 #180102 9:05:07 server id 1323306 end_log_pos 1042441 CRC32 0x67285443 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1514855107/*!*/; COMMIT /*!*/; # at 1042441 ==================== mysqldump备份文件获取的binlog pos ==================== ==================== 备份文件还原后，从库GTID_PURGED位置 ==================== #180102 9:05:07 server id 1323306 end_log_pos
