使用mysqldump实现对mysql备份实践
本文主要给大家介绍使用MySQLdump实现对mysql备份实践，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下使用mysqldump实现对mysql备份实践吧。
创新互联公司专注为客户提供全方位的互联网综合服务，包含不限于成都网站设计、成都网站制作、华龙网络推广、成都小程序开发、华龙网络营销、华龙企业策划、华龙品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联公司为所有大学生创业者提供华龙建站搭建服务，24小时服务热线：13518219792，官方网址：www.cdcxhl.com
打开查询日志
作用：便于分析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备份实践，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。
文章标题：使用mysqldump实现对mysql备份实践
网址分享：http://azwzsj.com/article/pesodo.html
