溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本文主要給大家簡單講講mysqldump備份時如何保證數(shù)據(jù)的一致，相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望mysqldump備份時如何保證數(shù)據(jù)的一致這篇文章可以給大家?guī)硪恍嶋H幫助。
mysqldump
運行mysqldump需一定的權(quán)限。如，備份表的最低權(quán)限為select，備份觸發(fā)器需show triggers權(quán)限。
（1）備份結(jié)果文件命令規(guī)范：dbname_port_$(date +%Y%m%d).bak
（2）gzip,tar是單線程壓縮軟件，只能用到一個cpu，效率比較低，備份的時候不建議壓縮，如果空間實在不足，可以考錄備份完成之后使用pigz多線程壓縮軟件；
（3）mysqldump是單線程工作，效率比較低，如果mysqldump備份需要很長時間，可以考慮使用mydumper支持多線程并發(fā)導(dǎo)出；
備份的參數(shù)
mysql>create table t1(c1 int,c2 varchar(10)); mysql>insert into t1 values(1,'aaa'),(2,'bbb'),(3,'ccc'); 例1： #指定備份單個庫testdb: [root@Darren2 tmp]# mysqldump -uroot -p147258 testdb > /tmp/testdb1.bak [root@Darren2 tmp]# vim testdb1.bak SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; --當(dāng)還原的時候不記錄binlog日志 SET @@GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914'; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `t1` WRITE; --還原表的時候不允許其他會話讀寫t1表 INSERT INTO `t1` VALUES (1,'aaa'),(2,'bbb'),(3,'ccc'); UNLOCK TABLES; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN; 在本數(shù)據(jù)庫上還原： [root@Darren2 tmp]# mysql -uroot -p147258 < testdb1.bak ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 實質(zhì)報錯： root@localhost [testdb]>SET @@GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914'; ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 如果直接還原會報錯，因為我開啟了gtid_mode,此時可以show master status看一下executed_gtid_set參數(shù)不為空，需要在備份文件testdb1.bak中把 “SET @@GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914';”注釋掉能繼續(xù)還原 如果希望能夠傳寫入binlog,把還原也同步到從庫，需要注釋掉SET @@SESSION.SQL_LOG_BIN= 0; root@localhost [testdb]>show master status; +------------------+----------+--------------+------------------+----------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------------+ | mysql-bin.000004 | 6392 | | | 83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10931 | +------------------+----------+--------------+------------------+----------------------------------------------+ 還原命令： [root@Darren2 tmp]# mysql -uroot -p147258 testdb < testdb1.bak #必須指定testdb庫,否則報錯找不到，如果還原testdb庫之前被刪除了，也會報錯： [root@Darren2 tmp]# mysql -uroot -p147258 testdb <testdb1.bak mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1049 (42000): Unknown database 'testdb' 例2： 同時備份多個庫-B,并且增加了create database testdb和use testdb的功能 [root@Darren2 tmp]# mysqldump -uroot -p147258 -B testdb > testdb2.bak [root@Darren2 tmp]# vim testdb2.bak SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; SET @@GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10931'; CREATE DATABASE `testdb` ; USE `testdb`; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `t1` WRITE; INSERT INTO `t1` VALUES (1,'aaa'),(2,'bbb'),(3,'ccc'); UNLOCK TABLES; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN; 還原：無需指定testdb庫 [root@Darren2 tmp]# mysql -uroot -p147258 < testdb2.bak #同時備份多個庫： [root@Darren2 tmp]# mysqldump -uroot -p147258 -B testdb mysql > testdbmysql.bak 還原多個庫： [root@Darren2 tmp]# mysql -uroot -p147258 < testdbmysql.bak 例3： 壓縮備份 [root@Darren2 logs]# mysqldump -uroot -p147258 testdb | gzip > testdbgzip.bak.gz 還原： [root@Darren2 tmp]# gunzip < testdbgzip.bak.gz | mysql -uroot -p147258 例4： 只備份庫中的表 格式：mysqldump -uroot -p147258 庫名 表名1 表名2 ... >備份文件名 注意：不能加-B 參數(shù)，否則就是備份多個庫了 #備份單個表： [root@Darren2 tmp]# mysqldump -uroot -p147258 testdb t1 > testdbt1.bak #備份多個表： [root@Darren2 tmp]# mysqldump -uroot -p147258 testdb t1 t2 > testdbt1t2.bak 例5： --master-data，指定日志文件從哪個位置開始，不用切割binlog日志了 [root@www ~]# mysqldump -uroot -p147258 --master-data=1 testdb CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=107; [root@www opt]# mysqldump -uroot -p147258 --master-data=2 testdb -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=107; --master-data=1時，備份文件中change master 是sql語句，在主從同步時用到 --master-data=2時，備份文件中--change master被注釋掉，還原時不具有sql語句作用 例6：其它參數(shù) --single-transaction 適合innodb事物數(shù)據(jù)庫備份，原理是設(shè)定本次會話級別是repeatable read,保證本次會話備份時，不會看到其他已經(jīng)提交的會話，保證數(shù)據(jù)一致性 -A, --all-databases Dump all the databases. This will be same as --databases -F, --flush-logs 即刷新binlog
總結(jié)：
innodb引擎 :一般生產(chǎn)全備使用的方法
[root@Darren2 tmp]# mysqldump -uroot -p147258 -A -B --master-data=2 --events --single-transaction > /tmp/all_$(date +%Y%m%d).bak
備份的全過程
下面mysqldump備份的過程：
root@localhost [testdb]>set global general_log=1;
[root@Darren1 data]# cat /dev/null > general.log
[root@Darren1 ~]# mysqldump -uroot -p147258 --single-transaction --master-data=2 testdb >testdb1_$(date +%Y%m%d)
[root@Darren1 data]# cat general.log
2016-12-21T15:31:00.474824Z 14 Connect root@localhost on using Socket
2016-12-21T15:31:00.475031Z 14 Query /*!40100 SET @@SQL_MODE='' */
2016-12-21T15:31:00.475194Z 14 Query /*!40103 SET TIME_ZONE='+00:00' */
2016-12-21T15:31:00.475282Z 14 Query FLUSH /*!40101 LOCAL */ TABLES --刷表，為了防止有表的DDL操作，如果備份的時候有表的DDL操作，flush tables一直處于等待狀態(tài)，直到DDL動作結(jié)束，才執(zhí)行flush tables
2016-12-21T15:31:00.475598Z 14 Query FLUSH TABLES WITH READ LOCK --全局鎖表，所有會話不能對表進行任何DML和DDL操作，讓數(shù)據(jù)處于一致性狀態(tài)
2016-12-21T15:31:00.475661Z 14 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ --設(shè)置隔離界別為RR
2016-12-21T15:31:00.475728Z 14 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ --開始事物
2016-12-21T15:31:00.475805Z 14 Query SHOW VARIABLES LIKE 'gtid\_mode' --查看GTID狀態(tài)
2016-12-21T15:31:00.478393Z 14 Query SHOW MASTER STATUS --查看master
2016-12-21T15:31:00.478487Z 14 Query UNLOCK TABLES --解鎖
2016-12-21T15:31:00.478625Z 14 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 ('testdb'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2016-12-21T15:31:00.480360Z 14 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 ('testdb')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2016-12-21T15:31:00.481072Z 14 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2016-12-21T15:31:00.483114Z 14 Init DB testdb
2016-12-21T15:31:00.483193Z 14 Query SAVEPOINT sp --創(chuàng)建事物的回滾點，如果下面一旦出錯，可以回滾到回滾點之前的狀態(tài)；
2016-12-21T15:31:00.483262Z 14 Query show tables
2016-12-21T15:31:00.483459Z 14 Query show table status like 't1'
2016-12-21T15:31:00.483711Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1
2016-12-21T15:31:00.483782Z 14 Query SET SESSION character_set_results = 'binary'
2016-12-21T15:31:00.483844Z 14 Query show create table `t1`
2016-12-21T15:31:00.483927Z 14 Query SET SESSION character_set_results = 'utf8'
2016-12-21T15:31:00.483998Z 14 Query show fields from `t1`
2016-12-21T15:31:00.484307Z 14 Query show fields from `t1`
2016-12-21T15:31:00.484551Z 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2016-12-21T15:31:00.484758Z 14 Query SET SESSION character_set_results = 'binary'
2016-12-21T15:31:00.484828Z 14 Query use `testdb`
2016-12-21T15:31:00.484888Z 14 Query select @@collation_database
2016-12-21T15:31:00.484962Z 14 Query SHOW TRIGGERS LIKE 't1'
2016-12-21T15:31:00.485199Z 14 Query SET SESSION character_set_results = 'utf8'
2016-12-21T15:31:00.485255Z 14 Query ROLLBACK TO SAVEPOINT sp --回到回滾點
2016-12-21T15:31:00.485315Z 14 Query show table status like 't2'
2016-12-21T15:31:00.485464Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1
2016-12-21T15:31:00.485515Z 14 Query SET SESSION character_set_results = 'binary'
2016-12-21T15:31:00.485567Z 14 Query show create table `t2`
2016-12-21T15:31:00.485635Z 14 Query SET SESSION character_set_results = 'utf8'
2016-12-21T15:31:00.485744Z 14 Query show fields from `t2`
2016-12-21T15:31:00.485968Z 14 Query show fields from `t2`
2016-12-21T15:31:00.486185Z 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
2016-12-21T15:31:00.486298Z 14 Query SET SESSION character_set_results = 'binary'
2016-12-21T15:31:00.486351Z 14 Query use `testdb`
2016-12-21T15:31:00.486407Z 14 Query select @@collation_database
2016-12-21T15:31:00.486468Z 14 Query SHOW TRIGGERS LIKE 't2'
2016-12-21T15:31:00.486792Z 14 Query SET SESSION character_set_results = 'utf8'
2016-12-21T15:31:00.486887Z 14 Query ROLLBACK TO SAVEPOINT sp --回到回滾點
2016-12-21T15:31:00.486943Z 14 Query RELEASE SAVEPOINT sp --釋放回滾點
2016-12-21T15:31:00.513639Z 14 Quit --退出總結(jié)：mysqldump的過程：
flush tables; flush table with read lock; set tx_isolation='repeatable-read'; start transaction; GTID_MODE; show master stauts; unlock tables; SAVEPOINT sp show create table `t1` SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` SHOW TRIGGERS LIKE 't1' ROLLBACK TO SAVEPOINT sp RELEASE SAVEPOINT sp
注意事項：
在使用mysqldump備份表的時候，如果對備份的表進行DDL操作可能使備份失敗，因為DDL不在事物的框架中，mysql8.0以后可能會把DDL放在事物框架中；
mysqldump備份時如何保證數(shù)據(jù)的一致就先給大家講到這里，對于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼