溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文主要給大家介紹使用mysqldump實現對mysql備份實踐，文章內容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下使用mysqldump實現對mysql備份實踐吧。
作用：便于分析mysqldump工作原理
方法：mysql> set global genaral_log=ON;
root@localhost:mysql3306.sock [db1]>show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE `tb1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@localhost:mysql3306.sock [db1]>show create table tb2\G *************************** 1. row *************************** Table: tb2 Create Table: CREATE TABLE `tb2` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
備份命令：
mysqldump -S /tmp/mysql3306.sock -p db1 tb2 >myisam_date +%Y%m%d.sql
輸出日志：
2018-05-14T15:05:30.582509Z 6 Connect root@localhost on using Socket 2018-05-14T15:05:30.582877Z 6 Query /*!40100 SET @@SQL_MODE='' */ 2018-05-14T15:05:30.583141Z 6 Query /*!40103 SET TIME_ZONE='+00:00' */ 2018-05-14T15:05:30.583461Z 6 Query SHOW VARIABLES LIKE 'gtid\_mode' 2018-05-14T15:05:30.585270Z 6 Query SELECT @@GLOBAL.GTID_EXECUTED 2018-05-14T15:05:30.589611Z 6 Init DB db1 2018-05-14T15:05:30.589894Z 6 Query SHOW TABLES LIKE 'tb2' 2018-05-14T15:05:30.590262Z 6 Query LOCK TABLES `tb2` READ /*!32311 LOCAL */ 2018-05-14T15:05:30.590555Z 6 Query show table status like 'tb2' 2018-05-14T15:05:30.591038Z 6 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-05-14T15:05:30.591307Z 6 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:05:30.591604Z 6 Query show create table `tb2` 2018-05-14T15:05:30.591884Z 6 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:05:30.592215Z 6 Query show fields from `tb2` 2018-05-14T15:05:30.592808Z 6 Query show fields from `tb2` 2018-05-14T15:05:30.593334Z 6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 2018-05-14T15:05:30.593655Z 6 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:05:30.593876Z 6 Query use `db1` 2018-05-14T15:05:30.594140Z 6 Query select @@collation_database 2018-05-14T15:05:30.594413Z 6 Query SHOW TRIGGERS LIKE 'tb2' 2018-05-14T15:05:30.594903Z 6 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:05:30.595076Z 6 Query UNLOCK TABLES 2018-05-14T15:05:30.597973Z 6 Quit
小結：可以看出不加任何參數，自動加上了LOCK TABLES READ LOCAL鎖，該鎖不會阻止讀，也不會阻止新的數據插入。所以不加參數的mysqldump是非常不安全的。
備份命令：
mysqldump -S /tmp/mysql3306.sock -p --lock-tables db1 tb2 >myisam_date +%Y%m%d.sql
輸出日志：
2018-05-14T15:31:46.319694Z 9 Connect root@localhost on using Socket 2018-05-14T15:31:46.320016Z 9 Query /*!40100 SET @@SQL_MODE='' */ 2018-05-14T15:31:46.320281Z 9 Query /*!40103 SET TIME_ZONE='+00:00' */ 2018-05-14T15:31:46.320559Z 9 Query SHOW VARIABLES LIKE 'gtid\_mode' 2018-05-14T15:31:46.322433Z 9 Query SELECT @@GLOBAL.GTID_EXECUTED 2018-05-14T15:31:46.326421Z 9 Init DB db1 2018-05-14T15:31:46.326694Z 9 Query SHOW TABLES LIKE 'tb2' 2018-05-14T15:31:46.327091Z 9 Query LOCK TABLES `tb2` READ /*!32311 LOCAL */ 2018-05-14T15:31:46.327369Z 9 Query show table status like 'tb2' 2018-05-14T15:31:46.327850Z 9 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-05-14T15:31:46.328099Z 9 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:31:46.328335Z 9 Query show create table `tb2` 2018-05-14T15:31:46.328589Z 9 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:31:46.328868Z 9 Query show fields from `tb2` 2018-05-14T15:31:46.329402Z 9 Query show fields from `tb2` 2018-05-14T15:31:46.329892Z 9 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 2018-05-14T15:31:46.330246Z 9 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:31:46.330484Z 9 Query use `db1` 2018-05-14T15:31:46.330669Z 9 Query select @@collation_database 2018-05-14T15:31:46.331026Z 9 Query SHOW TRIGGERS LIKE 'tb2' 2018-05-14T15:31:46.331545Z 9 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:31:46.331807Z 9 Query UNLOCK TABLES 2018-05-14T15:31:47.492837Z 9 Quit
小結：跟默認不加參數是一樣的，自動加了LOCK TABLES READ LOCAL鎖，不會阻止讀，也不會阻止寫。
備份命令：
mysqldump -S /tmp/mysql3306.sock -p --lock-all-tables db1 tb2 >myisam_date +%Y%m%d.sql
輸出日志：
2018-05-14T15:37:59.045469Z 10 Connect root@localhost on using Socket 2018-05-14T15:37:59.045824Z 10 Query /*!40100 SET @@SQL_MODE='' */ 2018-05-14T15:37:59.046086Z 10 Query /*!40103 SET TIME_ZONE='+00:00' */ 2018-05-14T15:37:59.046388Z 10 Query FLUSH TABLES 2018-05-14T15:37:59.106269Z 10 Query FLUSH TABLES WITH READ LOCK 2018-05-14T15:37:59.106572Z 10 Query SHOW VARIABLES LIKE 'gtid\_mode' 2018-05-14T15:37:59.108617Z 10 Query SELECT @@GLOBAL.GTID_EXECUTED 2018-05-14T15:37:59.112768Z 10 Init DB db1 2018-05-14T15:37:59.113058Z 10 Query SHOW TABLES LIKE 'tb2' 2018-05-14T15:37:59.113474Z 10 Query show table status like 'tb2' 2018-05-14T15:37:59.113876Z 10 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-05-14T15:37:59.114136Z 10 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:37:59.114369Z 10 Query show create table `tb2` 2018-05-14T15:37:59.114619Z 10 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:37:59.114849Z 10 Query show fields from `tb2` 2018-05-14T15:37:59.115364Z 10 Query show fields from `tb2` 2018-05-14T15:37:59.115843Z 10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 2018-05-14T15:37:59.116209Z 10 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:37:59.116390Z 10 Query use `db1` 2018-05-14T15:37:59.116691Z 10 Query select @@collation_database 2018-05-14T15:37:59.116929Z 10 Query SHOW TRIGGERS LIKE 'tb2' 2018-05-14T15:37:59.117396Z 10 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:37:59.119640Z 10 Quit
小結：可以發現執行了flush tables（關閉所有已打開的表），它請求發起一個全局的讀鎖（FLUSH TABLES WITH READ LOCK）會阻止對所有表的寫入操作，以此來確保數據的一致性。備份完成后，該會話斷開，會自動解鎖。
備份命令：
mysqldump -S /tmp/mysql3306.sock -p --lock-all-tables --master-data=2 db1 tb2 >myisam_date +%Y%m%d.sql
輸出日志：
2018-05-14T15:45:58.822719Z 11 Connect root@localhost on using Socket 2018-05-14T15:45:58.822835Z 11 Query /*!40100 SET @@SQL_MODE='' */ 2018-05-14T15:45:58.822906Z 11 Query /*!40103 SET TIME_ZONE='+00:00' */ 2018-05-14T15:45:58.822991Z 11 Query FLUSH /*!40101 LOCAL */ TABLES 2018-05-14T15:45:58.823137Z 11 Query FLUSH TABLES WITH READ LOCK 2018-05-14T15:45:58.823206Z 11 Query SHOW VARIABLES LIKE 'gtid\_mode' 2018-05-14T15:45:58.825232Z 11 Query SELECT @@GLOBAL.GTID_EXECUTED 2018-05-14T15:45:58.825364Z 11 Query SHOW MASTER STATUS 2018-05-14T15:45:58.828646Z 11 Init DB db1 2018-05-14T15:45:58.828739Z 11 Query SHOW TABLES LIKE 'tb2' 2018-05-14T15:45:58.828987Z 11 Query show table status like 'tb2' 2018-05-14T15:45:58.829176Z 11 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-05-14T15:45:58.829238Z 11 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:45:58.829293Z 11 Query show create table `tb2` 2018-05-14T15:45:58.829371Z 11 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:45:58.829440Z 11 Query show fields from `tb2` 2018-05-14T15:45:58.829775Z 11 Query show fields from `tb2` 2018-05-14T15:45:58.830036Z 11 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 2018-05-14T15:45:58.830214Z 11 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:45:58.830274Z 11 Query use `db1` 2018-05-14T15:45:58.830331Z 11 Query select @@collation_database 2018-05-14T15:45:58.830402Z 11 Query SHOW TRIGGERS LIKE 'tb2' 2018-05-14T15:45:58.830691Z 11 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:45:58.833762Z 11 Quit
小結：可以發現沒什么變化，只是多執行了SELECT @@GLOBAL.GTID_EXECUTED和SHOW MASTER STATUS，前者用于記錄執行過的事務GTID值，后者用于記錄二進制日志執行到的文件名和位置點信息。--master-data=2在做主從的時候非常實用。
備份命令：
mysqldump -S /tmp/mysql3306.sock -p --lock-all-tables --master-data=2 --flush-logs db1 tb2 >myisam_date +%Y%m%d.sql
輸出日志：
2018-05-14T15:58:12.896794Z 12 Connect root@localhost on using Socket 2018-05-14T15:58:12.896917Z 12 Query /*!40100 SET @@SQL_MODE='' */ 2018-05-14T15:58:12.896988Z 12 Query /*!40103 SET TIME_ZONE='+00:00' */ 2018-05-14T15:58:12.897076Z 12 Query FLUSH /*!40101 LOCAL */ TABLES 2018-05-14T15:58:12.897164Z 12 Query FLUSH TABLES WITH READ LOCK 2018-05-14T15:58:12.897224Z 12 Refresh /usr/local/mysql/bin/mysqld, Version: 5.7.22-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql3306.sock Time Id Command Argument 2018-05-14T15:58:14.310803Z 12 Query SHOW VARIABLES LIKE 'gtid\_mode' 2018-05-14T15:58:14.312359Z 12 Query SELECT @@GLOBAL.GTID_EXECUTED 2018-05-14T15:58:14.312486Z 12 Query SHOW MASTER STATUS 2018-05-14T15:58:14.314876Z 12 Init DB db1 2018-05-14T15:58:14.314961Z 12 Query SHOW TABLES LIKE 'tb2' 2018-05-14T15:58:14.315082Z 12 Query show table status like 'tb2' 2018-05-14T15:58:14.315218Z 12 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-05-14T15:58:14.315264Z 12 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:58:14.315305Z 12 Query show create table `tb2` 2018-05-14T15:58:14.315360Z 12 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:58:14.315416Z 12 Query show fields from `tb2` 2018-05-14T15:58:14.315672Z 12 Query show fields from `tb2` 2018-05-14T15:58:14.315914Z 12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 2018-05-14T15:58:14.316027Z 12 Query SET SESSION character_set_results = 'binary' 2018-05-14T15:58:14.316073Z 12 Query use `db1` 2018-05-14T15:58:14.316134Z 12 Query select @@collation_database 2018-05-14T15:58:14.316190Z 12 Query SHOW TRIGGERS LIKE 'tb2' 2018-05-14T15:58:14.316407Z 12 Query SET SESSION character_set_results = 'utf8' 2018-05-14T15:58:14.318938Z 12 Quit
小結：可以看出多加了一個參數--flush-logs日志里面并沒有明顯變化，但是該參數會刷新binlog，重新產生一個新的binlog文件。
總所周知，innodb實現了mvcc，即多版本并發控制
備份命令：
mysqldump -S /tmp/mysql3306.sock -p --single-transaction db1 tb1 >innodb_date +%Y%m%d.sql
輸出日志：
2018-05-14T16:16:34.757675Z 14 Connect root@localhost on using Socket 2018-05-14T16:16:34.757788Z 14 Query /*!40100 SET @@SQL_MODE='' */ 2018-05-14T16:16:34.757858Z 14 Query /*!40103 SET TIME_ZONE='+00:00' */ 2018-05-14T16:16:34.758005Z 14 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2018-05-14T16:16:34.758056Z 14 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 2018-05-14T16:16:34.758134Z 14 Query SHOW VARIABLES LIKE 'gtid\_mode' 2018-05-14T16:16:34.759847Z 14 Query SELECT @@GLOBAL.GTID_EXECUTED 2018-05-14T16:16:34.760130Z 14 Query UNLOCK TABLES 2018-05-14T16:16:34.763394Z 14 Init DB db1 2018-05-14T16:16:34.763471Z 14 Query SHOW TABLES LIKE 'tb1' 2018-05-14T16:16:34.763674Z 14 Query SAVEPOINT sp 2018-05-14T16:16:34.763760Z 14 Query show table status like 'tb1' 2018-05-14T16:16:34.764038Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-05-14T16:16:34.764098Z 14 Query SET SESSION character_set_results = 'binary' 2018-05-14T16:16:34.764175Z 14 Query show create table `tb1` 2018-05-14T16:16:34.764273Z 14 Query SET SESSION character_set_results = 'utf8' 2018-05-14T16:16:34.764365Z 14 Query show fields from `tb1` 2018-05-14T16:16:34.764705Z 14 Query show fields from `tb1` 2018-05-14T16:16:34.764973Z 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1` 2018-05-14T16:16:34.765118Z 14 Query SET SESSION character_set_results = 'binary' 2018-05-14T16:16:34.765175Z 14 Query use `db1` 2018-05-14T16:16:34.765296Z 14 Query select @@collation_database 2018-05-14T16:16:34.765377Z 14 Query SHOW TRIGGERS LIKE 'tb1' 2018-05-14T16:16:34.765658Z 14 Query SET SESSION character_set_results = 'utf8' 2018-05-14T16:16:34.765715Z 14 Query ROLLBACK TO SAVEPOINT sp 2018-05-14T16:16:34.765762Z 14 Query RELEASE SAVEPOINT sp 2018-05-14T16:16:37.208932Z 14 Quit
小結：innodb表在備份時，通常啟用選項--single-transaction來保證備份的一致性，實際上他的工作原理是設置本次會話的隔離級別為RR，然后啟動一個快照，實現一致性非鎖定讀。
備份命令：
mysqldump -S /tmp/mysql3306.sock -p --single-transaction --master-data=2 db1 tb1 >innodb_date +%Y%m%d.sql
輸出日志：
2018-05-14T16:28:28.118691Z 15 Connect root@localhost on using Socket 2018-05-14T16:28:28.118805Z 15 Query /*!40100 SET @@SQL_MODE='' */ 2018-05-14T16:28:28.118927Z 15 Query /*!40103 SET TIME_ZONE='+00:00' */ 2018-05-14T16:28:28.119040Z 15 Query FLUSH /*!40101 LOCAL */ TABLES 2018-05-14T16:28:28.119135Z 15 Query FLUSH TABLES WITH READ LOCK 2018-05-14T16:28:28.119189Z 15 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2018-05-14T16:28:28.119231Z 15 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 2018-05-14T16:28:28.119298Z 15 Query SHOW VARIABLES LIKE 'gtid\_mode' 2018-05-14T16:28:28.120900Z 15 Query SELECT @@GLOBAL.GTID_EXECUTED 2018-05-14T16:28:28.121275Z 15 Query SHOW MASTER STATUS 2018-05-14T16:28:28.121342Z 15 Query UNLOCK TABLES 2018-05-14T16:28:28.124607Z 15 Init DB db1 2018-05-14T16:28:28.124689Z 15 Query SHOW TABLES LIKE 'tb1' 2018-05-14T16:28:28.124829Z 15 Query SAVEPOINT sp 2018-05-14T16:28:28.124901Z 15 Query show table status like 'tb1' 2018-05-14T16:28:28.125151Z 15 Query SET SQL_QUOTE_SHOW_CREATE=1 2018-05-14T16:28:28.125211Z 15 Query SET SESSION character_set_results = 'binary' 2018-05-14T16:28:28.125264Z 15 Query show create table `tb1` 2018-05-14T16:28:28.125339Z 15 Query SET SESSION character_set_results = 'utf8' 2018-05-14T16:28:28.125408Z 15 Query show fields from `tb1` 2018-05-14T16:28:28.125832Z 15 Query show fields from `tb1` 2018-05-14T16:28:28.126198Z 15 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1` 2018-05-14T16:28:28.126337Z 15 Query SET SESSION character_set_results = 'binary' 2018-05-14T16:28:28.126417Z 15 Query use `db1` 2018-05-14T16:28:28.126475Z 15 Query select @@collation_database 2018-05-14T16:28:28.126546Z 15 Query SHOW TRIGGERS LIKE 'tb1' 2018-05-14T16:28:28.126889Z 15 Query SET SESSION character_set_results = 'utf8' 2018-05-14T16:28:28.126949Z 15 Query ROLLBACK TO SAVEPOINT sp 2018-05-14T16:28:28.127017Z 15 Query RELEASE SAVEPOINT sp 2018-05-14T16:28:28.129902Z 15 Quit
小結：由于增加了選型--master-data，因此看見提交了一個快速的全局讀鎖，目的是為了使整個實例進入短暫一致性狀態，以便--single-transaction能夠得到整個實例的一致性數據，同時記錄該狀態下的二進制日志偏移量（文件名和位置）。
備份myisam表的參數推薦如下：
mysqldump --lock-all-tables --master-data=2 --flush-logs db table >instence_port_db_table__`date +%Y%m%d`.sql
備份innodb表的參數推薦如下：
mysqldump --single-transaction --master-data=2 --flush-logs db table >instence_port_db_table_`date +%Y%m%d`.sql
看完以上關于使用mysqldump實現對mysql備份實踐，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
