ä½è 详ç»åæäºä¸ä¸ª mysqldump æ建å¤å¶å¤±è´¥çé®é¢åæè¿ç¨åæ¹è¿å»ºè®®ã
ä½è ï¼æå¯å¼º
ç±å¯ç DBA å¢éæåï¼çæ MySQLï¼TiDBï¼OceanBase çæ°æ®åºãç¸ä¿¡æç»æ对çäºæ å好ä¸ç¹ï¼ä¼æä¸ä¸æ ·çæ¶è·ã
æ¬ææ¥æºï¼ååæ稿
- ç±å¯çå¼æºç¤¾åºåºåï¼ååå 容æªç»ææä¸å¾éæ使ç¨ï¼è½¬è½½è¯·èç³»å°ç¼å¹¶æ³¨ææ¥æºã
æ éç°è±¡
æ客æ·åé¦ï¼ä½¿ç¨ mysqldump æ建ä»åºï¼å¯å¨å¤å¶åï¼å¤å¶æ¥éï¼Could not execute Write_rows event on table xxx; Duplicate entry 'xxx' for key 'PRIMARY'ã
客æ·ä½¿ç¨çå½ä»¤ï¼çèµ·æ¥æ²¡å¥é®é¢ï¼ã
-- 主åºå¤ä»½
shell> mysqldump -uroot -pxxx --master-data=2 --single-transaction -A --routines --events --triggers >/tmp/xxx.sql
-- ä»æå¡å¨è¿åå¤ä»½å¹¶å¯å¨å¤å¶
mysql>reset master;
mysql>reset slave all;
mysql>source /tmp/xxx.sql ;
mysql>change master to master_host='xxx',master_port=3306,master_user='xxx',master_password='xxx',master_auto_position=1;
mysql>start slave;
é®é¢ææ¥
æ¥çå¤å¶æ¥é表ç表ç»æï¼åç°è¡¨çåå¨å¼æ为 MyISAM å¼æãæ ¹æ®å®¢æ·åé¦ï¼è¡¨è®¿é®æ¯è¾é¢ç¹ï¼mysqldump --single-transaction é项ï¼åªè½ä¿è¯ InnoDB å¼æ表å¤ä»½çä¸è´æ§ï¼æ æ³ä¿è¯ MyISAM å¼æ表å¤ä»½çä¸è´æ§ï¼é®é¢å¯è½å°±åºå¨è¿ã
é®é¢è§£å³
ä¿®æ¹è¡¨çåå¨å¼æ为 InnoDB åï¼éæ°å¤ä»½æ¢å¤ï¼å¯ä»¥æ£å¸¸æ建ä»åºã
é®é¢å¤ç°
ä¸é¢æ们æ¥å¤ç°ä¸ä¸è¯¥é®é¢ã
ç¯å¢ä¿¡æ¯
æä½ç³»ç» | CentOS Linux release 7.5.1804 (Core) |
çæ¬ | MySQL 5.7.25 |
ä¸»åº | 10.186.60.187 |
ä»åº | 10.186.60.37 |
ä¸»ä» | å¼å¯ GTID |
æä½æ¥éª¤
å¨ä¸»åºï¼ä½¿ç¨ Sysbench é ä¸å¼ 1000w æ°æ®ç InnoDB å¼æç表 testdb_innodb.sbtest1ï¼é 1000w æ°æ®ä¸»è¦ç®çæ¯è®©å¤ä»½ InnoDB å¼æ表çæ¶é´æé¿ï¼ã
shell> sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=10.186.60.187 --mysql-port=3307 --mysql-user=root \
--mysql-password=1 --mysql-db=testdb_innodb --oltp-table-size=10000000 --oltp-tables-count=1 --threads=4 --report-interval=3 prepare
-- 表ç»æå¦ä¸
mysql> show create table testdb_innodb.sbtest1;
CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
-- 表æ»è¡æ°å¦ä¸ï¼
mysql> select count(*) from testdb_innodb.sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
å¨ä¸»åºï¼é ä¸å¼ MyISAM å¼æç表 testdb_myisam.sbtest2ã
-- 表ç»æå¦ä¸ï¼
mysql> CREATE TABLE testdb_myisam.`sbtest2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=myisam AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
å¨ä¸»åºï¼å¼å§ mysqldump é»è¾å¤ä»½ï¼å¹¶å¨æ§è¡å¤ä»½ testdb_innodb.sbtest1 æé´ï¼å¤ä»½ç顺åºï¼å å¤ä»½ testdb_innodb åºï¼ï¼å¾ testdb_myisam.sbtest2 表æå ¥ä¸æ¡æ°æ®ã
-- æ§è¡ mysqldump å¤ä»½
shell> /data/mysql/base/5.7.25/bin/mysqldump -h10.186.60.187 -P3307 -uroot -p1 --master-data=2 --single-transaction -A --routines --events --triggers >/tmp/dump.sql
-- æ§è¡å¤ä»½ testdb_innodb.sbtest1 æé´ï¼å¾ testdb_myisam.sbtest2 表æå
¥ä¸æ¡æ°æ®
mysql> insert into testdb_myisam.`sbtest2`(k,c,pad) values(2,'myisam','myisam');
-- éè¿ MySQL general_log è§å¯å¤ä»½æ
åµ
2023-07-11T16:15:50.900581+08:00 2692 Connect [email protected] on using TCP/IP
2023-07-11T16:15:50.901124+08:00 2692 Query /*!40100 SET @@SQL_MODE='' */
2023-07-11T16:15:50.901529+08:00 2692 Query /*!40103 SET TIME_ZONE='+00:00' */
2023-07-11T16:15:50.901743+08:00 2692 Query FLUSH /*!40101 LOCAL */ TABLES
2023-07-11T16:15:50.938083+08:00 2692 Query FLUSH TABLES WITH READ LOCK
2023-07-11T16:15:50.938281+08:00 2692 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2023-07-11T16:15:50.938410+08:00 2692 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2023-07-11T16:15:50.938678+08:00 2692 Query SHOW VARIABLES LIKE 'gtid\_mode'
2023-07-11T16:15:50.980335+08:00 2692 Query SELECT @@GLOBAL.GTID_EXECUTED
2023-07-11T16:15:50.980566+08:00 2692 Query SHOW MASTER STATUS
2023-07-11T16:15:50.980758+08:00 2692 Query UNLOCK TABLES
...ç¥
2023-07-11T16:15:51.541911+08:00 2692 Init DB testdb_innodb
2023-07-11T16:15:51.542012+08:00 2692 Query SHOW CREATE DATABASE IF NOT EXISTS `testdb_innodb`
2023-07-11T16:15:51.542139+08:00 2692 Query SAVEPOINT sp
2023-07-11T16:15:51.542224+08:00 2692 Query show tables
2023-07-11T16:15:51.542405+08:00 2692 Query show table status like 'sbtest1'
2023-07-11T16:15:51.543353+08:00 2692 Query SET SQL_QUOTE_SHOW_CREATE=1
2023-07-11T16:15:51.543467+08:00 2692 Query SET SESSION character_set_results = 'binary'
2023-07-11T16:15:51.543548+08:00 2692 Query show create table `sbtest1`
2023-07-11T16:15:51.543729+08:00 2692 Query SET SESSION character_set_results = 'utf8'
2023-07-11T16:15:51.543837+08:00 2692 Query show fields from `sbtest1`
2023-07-11T16:15:51.544172+08:00 2692 Query show fields from `sbtest1`
2023-07-11T16:15:51.544477+08:00 2692 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`
2023-07-11T16:15:57.603435+08:00 2683 Query insert into testdb_myisam.`sbtest2`(k,c,pad) values(2,'myisam','myisam')
2023-07-11T16:16:27.456357+08:00 2692 Query SET SESSION character_set_results = 'binary'
2023-07-11T16:16:27.471239+08:00 2692 Query use `testdb_innodb`
2023-07-11T16:16:27.471589+08:00 2692 Query select @@collation_database
2023-07-11T16:16:27.472065+08:00 2692 Query SHOW TRIGGERS LIKE 'sbtest1'
2023-07-11T16:16:27.506025+08:00 2692 Query SET SESSION character_set_results = 'utf8'
2023-07-11T16:16:27.506225+08:00 2692 Query ROLLBACK TO SAVEPOINT sp
2023-07-11T16:16:27.506383+08:00 2692 Query RELEASE SAVEPOINT sp
2023-07-11T16:16:27.506538+08:00 2692 Query show events
2023-07-11T16:16:27.507226+08:00 2692 Query use `testdb_innodb`
2023-07-11T16:16:27.507346+08:00 2692 Query select @@collation_database
2023-07-11T16:16:27.507457+08:00 2692 Query SET SESSION character_set_results = 'binary'
2023-07-11T16:16:27.507629+08:00 2692 Query SHOW FUNCTION STATUS WHERE Db = 'testdb_innodb'
2023-07-11T16:16:27.621194+08:00 2692 Query SHOW PROCEDURE STATUS WHERE Db = 'testdb_innodb'
2023-07-11T16:16:27.622726+08:00 2692 Query SET SESSION character_set_results = 'utf8'
2023-07-11T16:16:27.622900+08:00 2692 Init DB testdb_myisam
2023-07-11T16:16:27.623005+08:00 2692 Query SHOW CREATE DATABASE IF NOT EXISTS `testdb_myisam`
2023-07-11T16:16:27.623102+08:00 2692 Query SAVEPOINT sp
2023-07-11T16:16:27.623211+08:00 2692 Query show tables
2023-07-11T16:16:27.623566+08:00 2692 Query show table status like 'sbtest2'
2023-07-11T16:16:27.624197+08:00 2692 Query SET SQL_QUOTE_SHOW_CREATE=1
2023-07-11T16:16:27.624314+08:00 2692 Query SET SESSION character_set_results = 'binary'
2023-07-11T16:16:27.624401+08:00 2692 Query show create table `sbtest2`
2023-07-11T16:16:27.624518+08:00 2692 Query SET SESSION character_set_results = 'utf8'
2023-07-11T16:16:27.624605+08:00 2692 Query show fields from `sbtest2`
2023-07-11T16:16:27.625027+08:00 2692 Query show fields from `sbtest2`
2023-07-11T16:16:27.625391+08:00 2692 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest2`
2023-07-11T16:16:27.636073+08:00 2692 Query SET SESSION character_set_results = 'binary'
2023-07-11T16:16:27.636213+08:00 2692 Query use `testdb_myisam`
2023-07-11T16:16:27.636317+08:00 2692 Query select @@collation_database
2023-07-11T16:16:27.636429+08:00 2692 Query SHOW TRIGGERS LIKE 'sbtest2'
2023-07-11T16:16:27.636923+08:00 2692 Query SET SESSION character_set_results = 'utf8'
2023-07-11T16:16:27.637034+08:00 2692 Query ROLLBACK TO SAVEPOINT sp
2023-07-11T16:16:27.637116+08:00 2692 Query RELEASE SAVEPOINT sp
2023-07-11T16:16:27.637195+08:00 2692 Query show events
2023-07-11T16:16:27.637517+08:00 2692 Query use `testdb_myisam`
2023-07-11T16:16:27.637631+08:00 2692 Query select @@collation_database
2023-07-11T16:16:27.637741+08:00 2692 Query SET SESSION character_set_results = 'binary'
2023-07-11T16:16:27.637839+08:00 2692 Query SHOW FUNCTION STATUS WHERE Db = 'testdb_myisam'
2023-07-11T16:16:27.639206+08:00 2692 Query SHOW PROCEDURE STATUS WHERE Db = 'testdb_myisam'
2023-07-11T16:16:27.640377+08:00 2692 Query SET SESSION character_set_results = 'utf8'
2023-07-11T16:16:27.663274+08:00 2692 Quit
å¨ä»æå¡å¨ï¼ä½¿ç¨ä¸è¿° mysqldump é»è¾å¤ä»½æ件æ§è¡æ¢å¤ï¼æ建ä»åºã
-- ä»åºæ¥çæ°æ®åº
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
-- æ¸
空ä»åº binlog å gtid ä¿¡æ¯
mysql> reset master;
-- æ¥ç确认
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
-- æ§è¡ mysqldump é»è¾å¤ä»½æ件æ¢å¤
mysql> source /tmp/dump.sql;
-- 建ç«å¤å¶,并å¯å¨å¤å¶
mysql> change master to MASTER_HOST='10.186.60.187',MASTER_PORT=3307,master_user='repl',master_password='1',MASTER_AUTO_POSITION=1;
mysql> start slave;
-- æ¥çå¤å¶ç¶æ
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.186.60.187
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 190088135
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000015
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 '19112042-1f97-11ee-bf09-02000aba3cbb:3747' at master log mysql-bin.000015, end_log_pos 190087781. 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: 190087413
Relay_Log_Space: 1339
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 '19112042-1f97-11ee-bf09-02000aba3cbb:3747' at master log mysql-bin.000015, end_log_pos 190087781. 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: 629181509
Master_UUID: 19112042-1f97-11ee-bf09-02000aba3cbb
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: 230711 17:03:01
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 19112042-1f97-11ee-bf09-02000aba3cbb:3747-3748
Executed_Gtid_Set: 19112042-1f97-11ee-bf09-02000aba3cbb:1-3746
Auto_Position: 1
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\G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 19112042-1f97-11ee-bf09-02000aba3cbb:3747
LAST_ERROR_NUMBER: 1062
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '19112042-1f97-11ee-bf09-02000aba3cbb:3747' at master log mysql-bin.000015, end_log_pos 190087781; Could not execute Write_rows event on table testdb_myisam.sbtest2; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 190087781
LAST_ERROR_TIMESTAMP: 2023-07-11 17:03:01
åçåæ
- å½ mysqldump å¼å§å¤ä»½ï¼å¹¶è·åä¸è´æ§ä½ç¹åï¼UNLOCK TABLES åï¼è®°ä¸º T1 æ¶å»ã
- å¤ä»½ InnoDB 表å®æï¼å设å å¤ä»½ InnoDB 表ï¼ï¼è®°ä¸º T2 æ¶å»ã
- å¤ä»½ MyISAM å¼æ表å®æï¼è®°ä¸º T3 æ¶å»ã
- å¨ T1 å T2 ä¹é´ï¼å¦æ MyISAM å¼æ表æ INSERT æä½ï¼ä¼æ binlog 产çï¼mysqldump ä¹ä¼æ T1 å° T2 ä¹é´å¯¹ MyISAM å¼æ表ç INSERT æ°æ®å¤ä»½ä¸æ¥ã
- è¿æ ·å°±äº§çäºï¼å¯å¨å¤å¶åï¼ç±äº SQL 线ç¨ä¼åæ¾ T1 å° T2 æé´ç binlogï¼èè¿é¨åæ°æ®å·²ç»å¨å¤ä»½æ件éäºï¼å¹¶æ¢å¤å°ä»åºäºï¼å¯¼è´ SQL 线ç¨åæ¾æ¥éå¤é®çé®é¢ã
- 使ç¨è¯¥é项æ¶ï¼mysqldump --single-transaction è·åä¸è´æ§å¤ä»½åªéç¨äº InnoDB å¼æï¼å¯¹äº InnoDB å¼æ表çå¤ä»½ï¼è·åçæ¯ T1 æ¶å»çå¿«ç §ï¼å¯¹äºé InnoDB å¼æ表çå¤ä»½ï¼è·åçæ¯å½åææ°æ°æ®ã
æ¹è¿å»ºè®®
- æä¸å¡åºçé InnoDB å¼æ表ï¼ä¿®æ¹ä¸º InnoDBï¼éæ°å¤ä»½åæ建ä»åºï¼ ä¿®æ¹è¡¨çåå¨å¼æå¼éè¾å¤§ï¼éè¦èèæ¹åå¨å¼æ对å¨çº¿ä¸å¡çå½±åï¼éå表å¯ä»¥æ¹ä¸º InnoDB å¼æçæ åµï¼ã
- æ¹ç¨ Xtrabackup å¤ä»½å·¥å ·ãå¦æé InnoDB ç表æ¯è¾å¤§ï¼å¤ä»½ MyISAM å¼ææé´ï¼ å¤ä»½çº¿ç¨ææå®ä¾çå ¨å±è¯»éï¼FLUSH TABLES WITH READ LOCKï¼æ¶é´å°å¢å ï¼å°å½±åæ°æ®åºå¯ç¨æ§ï¼éæ©ä¸å¡ä½å³°æ¶æ§è¡ãï¼éåçæ¶é´å æ æ³ä¿®æ¹è¡¨åå¨å¼æçæ åµï¼ã
æ´å¤ææ¯æç« ï¼è¯·è®¿é®ï¼https://opensource.actionsky.com/