使用mysqldump实现对mysql备份实践

发布时间：2020-05-27 13:45:37 作者：三月
来源：网络阅读：248

本文主要给大家介绍使用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)

1、不加参数备份myisam引擎的表

备份命令：
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是非常不安全的。

2、--lock tables

备份命令：
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锁，不会阻止读，也不会阻止写。

3、--lock-all-tables

备份命令：
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）会阻止对所有表的写入操作，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁。

4、--lock-all-tables --master-data=2

备份命令：
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在做主从的时候非常实用。

5、--lock-all-tables --master-data=2 --flush-logs

备份命令：
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文件。

6、使用--single-transaction备份innodb引擎表

总所周知，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，然后启动一个快照，实现一致性非锁定读。

7、--single-transaction --master-data=2

备份命令：
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备份实践，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息，可以持续关注我们的行业资讯栏目的。