网站建设资讯

NEWS

网站建设资讯

使用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://njwzjz.com/article/pesodo.html