MySQL備份工具，支持各種參數選項，使用不同的選項極有可能影響備份處理過程。本文使用我們常規認為合理的備份參數，測試/驗證是否存在容易忽視的坑
# 常規備份參數
# mysqldump
shell> mysqldump --single-transaction --master-data=2 -B replcrash >dbname_dump_serverid_`date +%Y%m%d`.sql
# mysqlpump
shell> mysqlpump --single-transaction -B replcrash >dbname_pump_serverid_`date +%Y%m%d`.sql
# mydumper
shell> mydumper -B replcrash -o /data/backup/mydumper
# XtraBackup
# backup
shell> innobackupex [--defaults-file=MY.CNF] BACKUP-ROOT-DIR
# apply-log
shell> innobackupex --apply-log [--defaults-file=MY.CNF] BACKUP-DIR
# copy-back
shell> innobackupex --copy-back [--defaults-file=MY.CNF] BACKUP-DIR
常規備份參數
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
一、mysqldump
1.1、DML操作對備份的影響
創建兩張測試表
# 創建兩張測試表(192.168.85.132,3306)usereplcrash;create tablepy_user_innodb(
uidint not nullauto_increment,
namevarchar(32),
add_timedatetime default current_timestamp,
server_idvarchar(10),primary key(uid),key(name)
)engine=innodb;create tablepy_user_myisam(
uidint not nullauto_increment,
namevarchar(32),
add_timedatetime default current_timestamp,
server_idvarchar(10),primary key(uid),key(name)
)engine=myisam;
View Code
運行下面的腳本持續往測試表中寫入數據
#!/user/bin/python
importstringimportrandomimportMySQLdbimporttime
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)
);"""
whileTrue:
r_name= ‘‘.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(random.randint(20,30)))printr_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`.sql
View Code
使用備份文件搭建復制
# 還原實例清空GTID信息
mydba@192.168.85.132,3308 [replcrash]>reset master;
# 還原數據
[root@ZST1 backup]# mysql-h127.0.0.1 -P3308 -uroot -p replcrash
# 搭建復制
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.000183Read_Master_Log_Pos:1541377Relay_Log_File: relay-bin.000002Relay_Log_Pos:741Relay_Master_Log_File: mysql-bin.000183Slave_IO_Running: Yes
Slave_SQL_Running: No
Exec_Master_Log_Pos:1042768Last_SQL_Errno:1062Last_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 1043062Retrieved_Gtid_Set: 8ab82362-9c37-11e7-a858-000c29c1025c:251874-253268Executed_Gtid_Set: 8ab82362-9c37-11e7-a858-000c29c1025c:1-251874Auto_Position:1
View 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]# morereplcrash_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-timerecovery 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*/TABLES2018-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 10Query FLUSH TABLES WITH READ LOCK2018-01-02T01:05:07.702344Z 9Query commit2018-01-02T01:05:07.702411Z 10Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2018-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 10Query SELECT @@GLOBAL.GTID_EXECUTED2018-01-02T01:05:07.713179Z 10Query SHOW MASTER STATUS2018-01-02T01:05:07.725821Z 10Query UNLOCK TABLES2018-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 9Query commit2018-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_NAME2018-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 9Query commit2018-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 9Query commit2018-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_NAME2018-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 9Query commit2018-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 9Query commit2018-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 9Query commit2018-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 10Init DB replcrash2018-01-02T01:05:07.780249Z 10Query SAVEPOINT sp2018-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 9Query commit2018-01-02T01:05:07.781776Z 10Query show tables2018-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 9Query commit2018-01-02T01:05:07.788774Z 10Query 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 10Query show fields from `py_user`2018-01-02T01:05:07.790423Z 10Query 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 10Query use `replcrash`2018-01-02T01:05:07.791778Z 10 Query select@@collation_database2018-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 10Query ROLLBACK TO SAVEPOINT sp2018-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 10Query 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 10Query show fields from `py_user_innodb`2018-01-02T01:05:07.793607Z 10Query 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 9Query commit2018-01-02T01:05:07.798108Z 10 Query SET SESSION character_set_results = ‘binary‘
2018-01-02T01:05:07.798205Z 10Query use `replcrash`2018-01-02T01:05:07.798303Z 10 Query select@@collation_database2018-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 10Query ROLLBACK TO SAVEPOINT sp2018-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 10Query 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 10Query show fields from `py_user_myisam`2018-01-02T01:05:07.800098Z 10Query 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/*!*/;
# at1042059#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‘/*!*/;
# at1042124#180102 9:05:07 server id 1323306 end_log_pos 1042209 CRC32 0x5df266e4 Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1514855107/*!*/;
BEGIN/*!*/;
# at1042209#180102 9:05:07 server id 1323306 end_log_pos 1042279 CRC32 0xc1d41c5f Table_map: `replcrash`.`py_user_myisam` mapped to number 254# at1042279#180102 9:05:07 server id 1323306 end_log_pos 1042355 CRC32 0x27badc02 Write_rows: table id 254flags: 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*/# at1042355#180102 9:05:07 server id 1323306 end_log_pos 1042441 CRC32 0x67285443 Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1514855107/*!*/;
COMMIT/*!*/;
# at1042441
==================== mysqldump備份文件獲取的binlog pos ====================
==================== 備份文件還原后，從庫GTID_PURGED位置 ====================#180102 9:05:07 server id 1323306 end_log_pos 1042506 CRC32 0xf77ede80 GTID last_committed=2918 sequence_number=2919 rbr_only=yes/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= ‘8ab82362-9c37-11e7-a858-000c29c1025c:251874‘/*!*/;
# at1042506#180102 9:05:07 server id 1323306 end_log_pos 1042591 CRC32 0x506a2875 Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1514855107/*!*/;
BEGIN/*!*/;
# at1042591#180102 9:05:07 server id 1323306 end_log_pos 1042661 CRC32 0x90b154e8 Table_map: `replcrash`.`py_user_innodb` mapped to number 255# at1042661#180102 9:05:07 server id 1323306 end_log_pos 1042737 CRC32 0x1d693238 Write_rows: table id 255flags: STMT_END_F
### INSERT INTO `replcrash`.`py_user_innodb`
### 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*/# at1042737#180102 9:05:07 server id 1323306 end_log_pos 1042768 CRC32 0x87864022 Xid = 4420COMMIT/*!*/;
# at1042768
==================== 啟動復制后，py_user_innodb寫入uid=331記錄，成功 ====================#180102 9:05:07 server id 1323306 end_log_pos 1042833 CRC32 0xe492578a GTID last_committed=2919 sequence_number=2920 rbr_only=yes/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= ‘8ab82362-9c37-11e7-a858-000c29c1025c:251875‘/*!*/;
# at1042833#180102 9:05:07 server id 1323306 end_log_pos 1042918 CRC32 0xf08c4165 Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1514855107/*!*/;
BEGIN/*!*/;
# at1042918#180102 9:05:07 server id 1323306 end_log_pos 1042988 CRC32 0xf16731d6 Table_map: `replcrash`.`py_user_myisam` mapped to number 257# at1042988#180102 9:05:07 server id 1323306 end_log_pos 1043062 CRC32 0x128aec5e Write_rows: table id 257flags: STMT_END_F
### INSERT INTO `replcrash`.`py_user_myisam`
### SET
### @1=332 /*INT meta=0 nullable=0 is_null=0*/### @2=‘X1LME9HO5V7WXNOKBVZE‘ /*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*/# at1043062#180102 9:05:07 server id 1323306 end_log_pos 1043148 CRC32 0x5cc8cc30 Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1514855107/*!*/;
COMMIT/*!*/;
# at1043148
==================== 啟動復制后，py_user_myisam寫入uid=332記錄，失敗 ====================
==================== 從庫py_user_myisam表已存在332記錄，sql_thread停止 ====================#180102 9:05:07 server id 1323306 end_log_pos 1043213 CRC32 0xceb1ce4d GTID last_committed=2920 sequence_number=2921 rbr_only=yes/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= ‘8ab82362-9c37-11e7-a858-000c29c1025c:251876‘/*!*/;
# at1043213#180102 9:05:07 server id 1323306 end_log_pos 1043298 CRC32 0x38591b71 Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1514855107/*!*/;
BEGIN/*!*/;
# at1043298#180102 9:05:07 server id 1323306 end_log_pos 1043368 CRC32 0xf80c2ae9 Table_map: `replcrash`.`py_user_innodb` mapped to number 258# at1043368#180102 9:05:07 server id 1323306 end_log_pos 1043442 CRC32 0x0bf4ae26 Write_rows: table id 258flags: STMT_END_F
### INSERT INTO `replcrash`.`py_user_innodb`
### SET
### @1=332 /*INT meta=0 nullable=0 is_null=0*/### @2=‘X1LME9HO5V7WXNOKBVZE‘ /*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*/# at1043442#180102 9:05:07 server id 1323306 end_log_pos 1043473 CRC32 0x1b75f9e0 Xid = 4431COMMIT/*!*/;
# at1043473...
View Code
二進志日志顯示，按照py_user_myisam->py_user_innodb->py_user_myisam這樣的順序往表中寫入數據。
使用備份文件搭建的從庫，py_user_myisam表已包含UNLOCK TABLES之后的9條數據，但在備份文件中的@@GLOBAL.GTID_PURGED卻是UNLOCK TABLES時刻的位置。因此在啟動復制后，第一條操作py_user_innodb表成功，第二條操作py_user_myisam表失敗，從庫報主鍵沖突錯誤
那么應該如何修復這個錯誤呢?
# 刪除從庫py_user_myisam表大于等于沖突key的記錄
mydba@192.168.85.132,3308 [replcrash]> delete from py_user_myisam where uid>=332;
# 重新啟動sql_thread
mydba@192.168.85.132,3308 [replcrash]> start slave sql_thread;
View Code
總得來說就是只有innodb才會提供一致性備份！！！
1.2、DDL操作對備份的影響
建議先閱讀后續章節，理解各備份過程后再返回閱讀DDL操作對備份的影響
運行下面的腳本持續DDL操作
#!/user/bin/python
importstringimportrandomimportMySQLdbimporttime
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)
);"""counter= 1
while counter<=100:
addcol= ‘col‘ +str(counter)printaddcol
cursor=conn.cursor()#DDL
cursor.execute("alter table py_user_innodb add %s int;" %addcol)
cursor.execute("alter table py_user_innodb drop column %s;" %addcol)
conn.commit()
counter+= 1time.sleep(0.0001)
Python DDL
邏輯備份數據庫
# 清空general_log
[root@ZST1 logs]#cat /dev/null > /data/mysql/mysql3306/data/mysql-general.log
# mysqldump
[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`.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don not want to restore GTIDs, pass--set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
mysqldump: Error1412: Table definition has changed, please retry transaction when dumping table `py_user_innodb` at row: 0# mysqlpump
[root@ZST1 backup]# mysqlpump-h127.0.0.1 -P3306 -uroot -p --single-transaction --add-drop-table --exclude-databases=mysql,sakila,backupdb -A >/data/backup/replcrash_pump_1323306_`date +%Y%m%d`.sql
Enter password:
mysqlpump: [ERROR] (1412) Table definition has changed, please retry transaction
Dump process encountered error and will not continue.
Dump progress:0/3 tables, 250/2431rows
[root@ZST1 backup]#
# mydumper
[root@ZST1 mydumper]# mydumper-h 127.0.0.1 -P 3306 -u root -p mysql5719 --trx-consistency-only -v 3 -t 2 -o /data/backup/mydumper
..** Message: Thread 2 dumping data for`replcrash`.`py_user_innodb`** Message: Thread 1 dumping data for`replcrash`.`py_user_myisam`** (mydumper:5179): CRITICAL **: Could not read data from replcrash.py_user_innodb: Table definition has changed, please retry transaction**Message: Empty table replcrash.py_user_innodb
...
View Code
mydumper需要使用-v 3 顯示詳細信息，本身是不會報錯的！！！如果想更容易再現錯誤，最好是備份的數據表較大、較多，適當降低并行線程數(-t)，開啟--trx-consistency-only，讓其盡早解鎖
生成的備份文件中，py_user_innodb表只有結構，沒有數據
根據備份邏輯，在UNLOCK TABLES解除FTWRL到SELECT /*!40001 SQL_NO_CACHE */ * FROM `tbname`之間如果有DDL操作，就會造成上述錯誤
使用了with consistent snapshot子句開啟一致性快照事務之后，如果一旦表結構定義發生改變，事務將無法對該表執行查詢
使用WITH CONSISTENT SNAPSHOT子句，會話1顯式開啟一個事務之后先不執行查詢，會話B使用DDL語句添加一個字段
會話1
會話2
修改隔離級別為RR
mydba@192.168.85.132,3306 [replcrash]> set tx_isolation=‘repeatable-read‘;
Query OK, 0 rows affected (0.00 sec)
修改隔離級別為RR
mydba@192.168.85.132,3306 [replcrash]> set tx_isolation=‘repeatable-read‘;
Query OK, 0 rows affected (0.00 sec)
顯式開啟一個事務，先不執行查詢
mydba@192.168.85.132,3306 [replcrash]> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
執行DDL語句添加字段，執行成功
mydba@192.168.85.132,3306 [replcrash]> alter table py_user_innodb add col1 int;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
執行查詢，報表定義已經改變的錯誤
mydba@192.168.85.132,3306 [replcrash]> select * from py_user_innodb;
ERROR 1412 (HY000): Table definition has changed, please retry transaction
物理備份數據庫
[root@ZST1 backup]# innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf -S /tmp/mysql3306.sock -uroot -pmysql5719 /data/backup/full/
180104 15:29:31 [01] Copying ./sakila/payment.ibd to /data/backup/full/2018-01-04_15-29-21/sakila/payment.ibd180104 15:29:32 [01] ...doneInnoDB: Last flushed lsn:19337348200 load_index lsn 19337354022InnoDB: An optimized (without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
PXB will not be able take a consistent backup. Retry the backup operation180104 15:29:32 [01] Copying ./replcrash/py_user.ibd to /data/backup/full/2018-01-04_15-29-21/replcrash/py_user.ibd180104 15:29:32 [01] ...done
180104 15:29:32 [01] Copying ./replcrash/py_user_innodb.ibd to /data/backup/full/2018-01-04_15-29-21/replcrash/py_user_innodb.ibd180104 15:29:32 [01] ...done
View Code
只要在備份期間(實際是備份InnoDB表期間，因為備份non-InnoDB表期間會加FTWRL只讀鎖，阻塞DDL、DML操作)執行DDL操作，innobackupex就會報錯退出。原因就是DDL操作不會記錄到redo log，PXB will not be able take a consistent backup.
因此備份期間要避免執行不記錄事務日志的操作(ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE)
二、mysqlpump
2.1、備份過程
借助前面已開啟general_log，來查看mysqlpump執行過程
MySQL 5.7.11起解決了--single-transaction和--default-parallelism的互斥問題
# 清空general_log
[root@ZST1 logs]#cat /dev/null > /data/mysql/mysql3306/data/mysql-general.log
# 備份replcrash數據庫，一致性備份(源碼限制只有備份整個實例時才能返回GTID信息，因此這里使用-A，然后使用--exclude-databases排除不需要備份的db)
[root@ZST1 backup]# mysqlpump-h127.0.0.1 -P3306 -uroot -p --single-transaction --add-drop-table --exclude-databases=mysql,sakila,backupdb -A >/data/backup/replcrash_pump_1323306_`date +%Y%m%d`.sql
默認mysqlpump使用一個隊列兩個線程
[root@ZST1 data]# vim/data/mysql/mysql3306/data/mysql-general.log
...4Time Id Command Argument5 2018-01-03T01:24:06.623704Z 14 Connect root@localhost on using TCP/IP6 2018-01-03T01:24:06.631266Z 14Query FLUSH TABLES WITH READ LOCK7 2018-01-03T01:24:06.667093Z 14Query SHOW WARNINGS8 2018-01-03T01:24:06.667310Z 14Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ9 2018-01-03T01:24:06.667520Z 14Query SHOW WARNINGS10 2018-01-03T01:24:06.667647Z 14Query START TRANSACTION WITH CONSISTENT SNAPSHOT11 2018-01-03T01:24:06.667792Z 14Query SHOW WARNINGS12 2018-01-03T01:24:06.679491Z 15 Connect root@localhost on using TCP/IP13 2018-01-03T01:24:06.683019Z 15Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ14 2018-01-03T01:24:06.684865Z 15Query SHOW WARNINGS15 2018-01-03T01:24:06.685015Z 15Query START TRANSACTION WITH CONSISTENT SNAPSHOT16 2018-01-03T01:24:06.685114Z 15Query SHOW WARNINGS17 2018-01-03T01:24:06.686057Z 16 Connect root@localhost on using TCP/IP18 2018-01-03T01:24:06.688856Z 16Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ19 2018-01-03T01:24:06.716231Z 16Query SHOW WARNINGS20 2018-01-03T01:24:06.716447Z 16Query START TRANSACTION WITH CONSISTENT SNAPSHOT21 2018-01-03T01:24:06.716558Z 16Query SHOW WARNINGS22 2018-01-03T01:24:06.716701Z 14Query UNLOCK TABLES23 2018-01-03T01:24:06.716857Z 14Query SHOW WARNINGS
...各種SHOW...1176 2018-01-03T01:24:07.344380Z 16Query SHOW CREATE DATABASE IF NOT EXISTS `replcrash`1177 2018-01-03T01:24:07.344468Z 16Query SHOW WARNINGS1178 2018-01-03T01:24:07.344565Z 16Query SHOW TABLE STATUS FROM `replcrash`1179 2018-01-03T01:24:07.380209Z 16Query SHOW WARNINGS1180 2018-01-03T01:24:07.380416Z 16Query SHOW COLUMNS IN `py_user` FROM `replcrash`1181 2018-01-03T01:24:07.381223Z 16Query SHOW WARNINGS1182 2018-01-03T01:24:07.381408Z 16Query SHOW CREATE TABLE `replcrash`.`py_user`1183 2018-01-03T01:24:07.381614Z 16Query SHOW WARNINGS1184 2018-01-03T01:24:07.381950Z 16 Query SHOW TRIGGERS FROM `replcrash` LIKE ‘py_user‘
1185 2018-01-03T01:24:07.382575Z 16Query SHOW WARNINGS1186 2018-01-03T01:24:07.382764Z 16Query SHOW COLUMNS IN `py_user_innodb` FROM `replcrash`1187 2018-01-03T01:24:07.383125Z 14 Query SET SQL_QUOTE_SHOW_CREATE= 1
1188 2018-01-03T01:24:07.383334Z 14Query SHOW WARNINGS1189 2018-01-03T01:24:07.383617Z 14 Query SET TIME_ZONE=‘+00:00‘
1190 2018-01-03T01:24:07.384037Z 14Query SHOW WARNINGS1191 2018-01-03T01:24:07.385106Z 15 Query SELECT `COLUMN_NAME`, `EXTRA` FROM `INFORMATION_SCHEMA`.`COLUMNS`WHERE TABLE_SCHEMA =‘replcrash‘ AND TABLE_NAME =‘py_user‘
1192 2018-01-03T01:24:07.386099Z 15Query SHOW WARNINGS1193 2018-01-03T01:24:07.386347Z 15Query SELECT SQL_NO_CACHE `uid`,`name`,`add_time`,`server_id` FROM `replcrash`.`py_user`1194 2018-01-03T01:24:07.387102Z 15Query SHOW WARNINGS1195 2018-01-03T01:24:07.387644Z 16Query SHOW WARNINGS1196 2018-01-03T01:24:07.387997Z 16Query SHOW CREATE TABLE `replcrash`.`py_user_innodb`1197 2018-01-03T01:24:07.388216Z 16Query SHOW WARNINGS1198 2018-01-03T01:24:07.388487Z 16 Query SHOW TRIGGERS FROM `replcrash` LIKE ‘py_user_innodb‘
1199 2018-01-03T01:24:07.389053Z 14 Query SELECT `COLUMN_NAME`, `EXTRA` FROM `INFORMATION_SCHEMA`.`COLUMNS`WHERE TABLE_SCHEMA =‘replcrash‘ AND TABLE_NAME =‘py_user_innodb‘
1200 2018-01-03T01:24:07.390054Z 14Query SHOW WARNINGS1201 2018-01-03T01:24:07.390293Z 14Query SELECT SQL_NO_CACHE `uid`,`name`,`add_time`,`server_id` FROM `replcrash`.`py_user_innodb`1202 2018-01-03T01:24:07.391566Z 16Query SHOW WARNINGS1203 2018-01-03T01:24:07.391776Z 16Query SHOW COLUMNS IN `py_user_myisam` FROM `replcrash`1204 2018-01-03T01:24:07.392559Z 16Query SHOW WARNINGS1205 2018-01-03T01:24:07.392747Z 16Query SHOW CREATE TABLE `replcrash`.`py_user_myisam`1206 2018-01-03T01:24:07.393065Z 16Query SHOW WARNINGS1207 2018-01-03T01:24:07.393336Z 16 Query SHOW TRIGGERS FROM `replcrash` LIKE ‘py_user_myisam‘
1208 2018-01-03T01:24:07.394146Z 16Query SHOW WARNINGS1209 2018-01-03T01:24:07.394371Z 16 Query SHOW FUNCTION STATUS WHERE db = ‘replcrash‘
1210 2018-01-03T01:24:07.396083Z 15 Query SELECT `COLUMN_NAME`, `EXTRA` FROM `INFORMATION_SCHEMA`.`COLUMNS`WHERE TABLE_SCHEMA =‘replcrash‘ AND TABLE_NAME =‘py_user_myisam‘
1211 2018-01-03T01:24:07.399053Z 15Query SHOW WARNINGS1212 2018-01-03T01:24:07.399425Z 15Query SELECT SQL_NO_CACHE `uid`,`name`,`add_time`,`server_id` FROM `replcrash`.`py_user_myisam`1213 2018-01-03T01:24:07.405719Z 16Query SHOW WARNINGS1214 2018-01-03T01:24:07.405915Z 16 Query SHOW PROCEDURE STATUS WHERE db = ‘replcrash‘
1215 2018-01-03T01:24:07.412340Z 14Query SHOW WARNINGS1216 2018-01-03T01:24:07.414496Z 15Query SHOW WARNINGS1217 2018-01-03T01:24:07.416755Z 16Query SHOW WARNINGS1218 2018-01-03T01:24:07.417261Z 16Query SHOW EVENTS FROM `replcrash`1219 2018-01-03T01:24:07.417884Z 16 Query SHOW WARNINGS
View Code
即使備份一個db，general_log中也會出現很多其他庫的內容(⊙_⊙)
mysqlpump備份過程(--single-transaction)：
對于建立的第一個連接，執行FLUSH TABLES WITH READ LOCK，加上只讀鎖；對于其他連接(包含第一個連接)，設置RR隔離級別，并開啟一致性快照讀START TRANSACTION WITH CONSISTENT SNAPSHOT；當所有連接(--default-parallelism設置多少個線程)都建立好后，再執行解鎖UNLOCK TABLES；最后通過SELECT colname ?FROM `tbname`備份數據
general_log中沒有看到SHOW MASTER STATUS，猜測它類似于mydumper，也是在主線程FLUSH TABLES WITH READ LOCK后，就去獲取GTID信息。
因此mysqlpump也會遇到在mysqldump中的坑
mysqlpump并行備份的部分參數需要在單線程模式才能應用，實際使用中建議先測試，對比輸出結果分辨各參數的作用
2.2、--single-transaction
? --single-transaction
This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.
When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump/mysqlpump to retrieve the table contents to obtain incorrect contents or fail.
? START TRANSACTION
The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table.
The WITH CONSISTENT SNAPSHOT modifier does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits a consistent read. The only isolation level that permits a consistent read is REPEATABLE READ.
該參數將事務隔離級別設置成Repeatable Read，并在dump之前發送start transaction語句給服務端。這只對事務表(比如innodb)很有用，因為在發出start transaction時，保證了在不阻塞任何應用下的一致性狀態。對myisam和memory等非事務表，還是會改變狀態的，當使用此參的時候要確保沒有其他連接在使用ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等語句，否則會出現不正確的內容或者失敗。在mysql5.7.11之前，--default-parallelism大于1的時候和此參數互斥，必須使用--default-parallelism=0。5.7.11之后解決了--single-transaction和--default-parallelism的互斥問題
三、mydumper
3.1、備份過程
mydumper備份過程
1、連接目標數據庫
2、通過show processlist來判斷是否有長查詢，根據參數long-query-guard和kill-long-queries決定退出或殺掉長查詢
3、主線程flush tables with read lock；start transaction with consistent snapshot
4、主線程讀取當前時間點的二進制日志文件名和日志寫入的位置并記錄在metadata文件中，以供即時點恢復使用
5、創建dump線程，缺省為4個
6、確定候選表，根據類別分別插入non_innodb_table、innodb_tables以及table_schemas鏈表
7、將候選表通過g_async_queue_push加入任務隊列(隊列最后元素是thread shutdown)，由dump線程從隊列中讀取表信息并執行數據導出
8、備份完non_innodb_table后立即unlock tables解鎖，以減少鎖定時間
9、等待dump InnoDB tables完成
從備份邏輯中可以看出，mydumper需要在備份完非事務表之后才解鎖，這就保證了事務表和非事務表的一致性備份。默認情況下，DML操作不會影響mydumper備份的一致性
3.2、--trx-consistency-only
這個參數退化為僅保證事務表的一致性備份，在備份非事務表前如果有數據寫入就會導致數據與metadata不一致，有點類似前面的mysqldump、mysqlpump的味道
運行前面的PythonDML腳本，一直往py_user_myisam、py_user_innodb表中寫入數據，測試--trx-consistency-only選項的效果
# 清空general_log
[root@ZST1 logs]#cat /dev/null > /data/mysql/mysql3306/data/mysql-general.log
# 備份replcrash庫(僅事務表一致性備份)
[root@ZST1 mydumper]# mydumper-h 127.0.0.1 -P 3306 -u root -p mysql5719 --trx-consistency-only -v 3 -B replcrash -o /data/backup/mydumper** (mydumper:2808): WARNING **: Using trx_consistency_only, binlog coordinates will not be accurate ifyou are writing to non transactional tables.**Message: Connected to a MySQL server** Message: Started dump at: 2018-01-04 09:34:53
**Message: Written master status** Message: Thread 1 connected using MySQL connection ID 50
** Message: Thread 2 connected using MySQL connection ID 51
** Message: Thread 3 connected using MySQL connection ID 52
** Message: Thread 4 connected using MySQL connection ID 53
**Message: Transactions started, unlocking tables** Message: Thread 1 dumping data for`replcrash`.`py_user`** Message: Thread 2 dumping data for`replcrash`.`py_user_innodb`** Message: Thread 2 dumping data for`replcrash`.`py_user_myisam`** Message: Thread 1 dumping schema for`replcrash`.`py_user`** Message: Thread 3 dumping schema for`replcrash`.`py_user_innodb`** Message: Thread 1 dumping schema for`replcrash`.`py_user_myisam`** Message: Thread 3shutting down** Message: Thread 1shutting down** Message: Thread 2shutting down** Message: Thread 4shutting down** Message: Finished dump at: 2018-01-04 09:34:53# 查看metadata
[root@ZST1 mydumper]#catmetadata
Started dump at:2018-01-04 09:34:53SHOW MASTER STATUS:
Log: mysql-bin.000185Pos:2452139GTID:8ab82362-9c37-11e7-a858-000c29c1025c:1-272303Finished dump at:2018-01-04 09:34:53[root@ZST1 mydumper]#
# 還原replcrash庫
[root@ZST1 mydumper]# myloader-h 127.0.0.1 -P 3308 -u root -p mysql5719 -o -B replcrash -d /data/backup/mydumper
myloader不會產生binlog，也不會應用metadata中的GTID
# 設置GTID_PURGED
mydba@192.168.85.132,3308 [replcrash]>reset master;
mydba@192.168.85.132,3308 [replcrash]> SET @@GLOBAL.GTID_PURGED=‘8ab82362-9c37-11e7-a858-000c29c1025c:1-272303‘;
# 搭建復制
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.000185Read_Master_Log_Pos:2758886Relay_Log_File: relay-bin.000002Relay_Log_Pos:414Relay_Master_Log_File: mysql-bin.000185Slave_IO_Running: Yes
Slave_SQL_Running: No
Exec_Master_Log_Pos:2452139Last_SQL_Errno:1062Last_SQL_Error: Could not execute Write_rows event on table replcrash.py_user_myisam; Duplicate entry‘783‘ for key ‘PRIMARY‘, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event‘‘s master log mysql-bin.000185, end_log_pos 2452443Retrieved_Gtid_Set: 8ab82362-9c37-11e7-a858-000c29c1025c:272304-273161Executed_Gtid_Set: 8ab82362-9c37-11e7-a858-000c29c1025c:1-272303Auto_Position:1# 大于等于沖突key的數據
mydba@192.168.85.132,3308 [replcrash]> select * from replcrash.py_user_myisam where uid>=783;+-----+--------------------------------+---------------------+-----------+
| uid | name | add_time | server_id |
+-----+--------------------------------+---------------------+-----------+
| 783 | MLGU22VB26RHNNYAY6IPPUJX9A74EM | 2018-01-04 09:34:53 | 1323306 |
+-----+--------------------------------+---------------------+-----------+
1 row in set (0.06sec)
# mydumper產生的general-log
[root@ZST1 data]# vim/data/mysql/mysql3306/data/mysql-general.log
...2018-01-04T01:34:53.769877Z 49 Query SET SESSION net_write_timeout = 2147483
2018-01-04T01:34:53.770302Z 49Query SHOW PROCESSLIST2018-01-04T01:34:53.770886Z 49Query FLUSH TABLES WITH READ LOCK2018-01-04T01:34:53.771236Z 49 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT*/
2018-01-04T01:34:53.771430Z 49 Query /*!40101 SET NAMES binary*/
2018-01-04T01:34:53.772084Z 49Query SHOW MASTER STATUS2018-01-04T01:34:53.772331Z 49Query SHOW SLAVE STATUS2018-01-04T01:34:53.773386Z 48 Query insert into py_user_myisam(name,add_time,server_id) values(‘MLGU22VB26RHNNYAY6IPPUJX9A74EM‘,now(),@@server_id)2018-01-04T01:34:53.773981Z 50 Connect root@localhost on using TCP/IP2018-01-04T01:34:53.788273Z 50 Query SET SESSION wait_timeout = 2147483
2018-01-04T01:34:53.788874Z 50Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2018-01-04T01:34:53.788972Z 50 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT*/
2018-01-04T01:34:53.789092Z 50 Query /*!40103 SET TIME_ZONE=‘+00:00‘*/
2018-01-04T01:34:53.789201Z 50 Query /*!40101 SET NAMES binary*/
2018-01-04T01:34:53.790091Z 51 Connect root@localhost on using TCP/IP2018-01-04T01:34:53.790315Z 51 Query SET SESSION wait_timeout = 2147483
2018-01-04T01:34:53.791912Z 51Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2018-01-04T01:34:53.792095Z 51 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT*/
2018-01-04T01:34:53.792307Z 51 Query /*!40103 SET TIME_ZONE=‘+00:00‘*/
2018-01-04T01:34:53.792418Z 51 Query /*!40101 SET NAMES binary*/
2018-01-04T01:34:53.793381Z 52 Connect root@localhost on using TCP/IP2018-01-04T01:34:53.794631Z 52 Query SET SESSION wait_timeout = 2147483
2018-01-04T01:34:53.796301Z 52Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2018-01-04T01:34:53.796404Z 52 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT*/
2018-01-04T01:34:53.796538Z 52 Query /*!40103 SET TIME_ZONE=‘+00:00‘*/
2018-01-04T01:34:53.796641Z 52 Query /*!40101 SET NAMES binary*/
2018-01-04T01:34:53.797414Z 53 Connect root@localhost on using TCP/IP2018-01-04T01:34:53.798420Z 53 Query SET SESSION wait_timeout = 2147483
2018-01-04T01:34:53.799316Z 53Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2018-01-04T01:34:53.799425Z 53 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT*/
2018-01-04T01:34:53.800345Z 53 Query /*!40103 SET TIME_ZONE=‘+00:00‘*/
2018-01-04T01:34:53.800449Z 53 Query /*!40101 SET NAMES binary*/
2018-01-04T01:34:53.800661Z 49 Query UNLOCK TABLES /*trx-only*/
2018-01-04T01:34:53.801868Z 49Init DB replcrash2018-01-04T01:34:53.802332Z 49Query SHOW TABLE STATUS2018-01-04T01:34:53.804759Z 50 Query SELECT /*!40001 SQL_NO_CACHE*/ *FROM `replcrash`.`py_user`2018-01-04T01:34:53.805137Z 51 Query SELECT /*!40001 SQL_NO_CACHE*/ *FROM `replcrash`.`py_user_innodb`2018-01-04T01:34:53.807164Z 51 Query SELECT /*!40001 SQL_NO_CACHE*/ *FROM `replcrash`.`py_user_myisam`2018-01-04T01:34:53.808786Z 48 Query insert into py_user_innodb(name,add_time,server_id) values(‘MLGU22VB26RHNNYAY6IPPUJX9A74EM‘,now(),@@server_id)2018-01-04T01:34:53.809449Z 49Query SHOW CREATE DATABASE `replcrash`2018-01-04T01:34:53.810326Z 52Query SHOW CREATE TABLE `replcrash`.`py_user_innodb`2018-01-04T01:34:53.810638Z 50Query SHOW CREATE TABLE `replcrash`.`py_user`2018-01-04T01:34:53.811307Z 52Quit2018-01-04T01:34:53.811506Z 50Query SHOW CREATE TABLE `replcrash`.`py_user_myisam`2018-01-04T01:34:53.812272Z 50Quit2018-01-04T01:34:53.812512Z 48 Query commit
View Code
48是python持續寫入的線程、49是主線程、50~53是dump線程
所有連接建立后，主線程立即解鎖UNLOCK TABLES /* trx-only */。主線程SHOW MASTER STATUS的后面緊接著python寫入nsert into py_user_myisam，實際這個寫入操作應該是在主線程UNLOCK TABLES之后，dump線程備份py_user_myisam數據之前。這就導致備份的數據與metadata不一致，因此不推薦使用--trx-consistency-only選項
3.3、邏輯備份差異
mysqldump：只支持單線程工作，這就使得它無法迅速的備份數據
mysqlpump：并行的最小粒度是單個數據庫對象，對于每張表的導出只能是單個線程的
mydumper：支持對單表多個線程備份，參數-r
多線程操作提升空間受限于磁盤的IO能力，在使用前做好磁盤IO的評估
四、XtraBackup
4.1、備份過程
innobackupex全備過程
1、start xtrabackup_log
2、copy .ibd、ibdata1
3、FLUSH TABLES WITH READ LOCK
4、copy .frm、.MYD、.MYI、misc files
5、SHOW MASTER STATUS
6、UNLOCK TABLES
7、stop and copy xtrabackup_log
備份開始時首先會開啟一個后臺檢測進程，從當前checkpoint位置開始拷貝redo log，同時持續檢測redo log，一旦發現redo中有新的日志寫入，立刻將日志記入后臺日志文件xtrabackup_log中。之后拷貝innodb的數據文件和系統表空間文件ibdata1，待拷貝結束后，執行flush tables with read lock操作，拷貝.frm，MYI，MYD，等文件，并且在這一時刻獲得binlog的位置，最后會發出unlock tables，把表設置為可讀可寫狀態，最終停止xtrabackup_log。
InnoDB表copy [.ibd、ibdata]+[redo log]借助InnoDB Crash Recovery機制保證一致性；non-InnoDB表使用FTWRL加只讀鎖后backup non-InnoDB tables and files。DML操作不會影響innobackupex備份的一致性
4.2、總結
mysqldump
mysqlpump
mydumper
innobackupex
FTWRL被阻塞
存在互斥鎖，會被阻塞
存在互斥鎖，會被阻塞
存在互斥鎖，會被阻塞
存在互斥鎖，會被阻塞
報錯，表定義變更
報錯，表定義變更
不報錯，-v 3查看
報錯，DDL操作沒寫redo log
DML操作影響數據一致性
non_InnoDB表不一致
non_InnoDB表不一致
默認一致，--trx-consistency-only會導致不一致
一致
其他
沒有指定my.cnf導致備份一個錯誤的實例
對生產庫的DDL操作、大事務、或者長時間鎖表的操作，一定要避開備份時間(?ω?)
五、參考文檔
)
)
; 很牛逼)
)
)
