mysqldump备份7

2023-11-10 来源：哗拓教育

160518 11:00:59 14 Connect root@localhost on 14 Query /*!40100 SET @@SQL_MODE=‘‘ */ 14 Query /*!40103 SET TIME_ZONE=‘+00:00‘ */ 14 Query FLUSH /*!40101 LOCAL */ TABLES 14 Query FLUSH TABLES WITH READ LOCK 14 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 14 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 14 Query SHOW VARIABLES LIKE ‘gtid\_mode‘ 14 Query SHOW MASTER STATUS 14 Query UNLOCK TABLES 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 GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME 14 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE‘ ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 14 Query SHOW DATABASES 14 Query SHOW VARIABLES LIKE ‘ndbinfo\_version‘

其中，比较重要的有以下几点：

1. FLUSH /*!40101 LOCAL */ TABLES

Closes all open tables, forces all tables in use to be closed, and flushes the query cache.

2. FLUSH TABLES WITH READ LOCK

执行flush tables操作，并加一个全局读锁，很多童鞋可能会好奇，这两个命令貌似是重复的，为什么不在第一次执行flush tables操作的时候加上锁了，其实，这样做的原因在于可以尽量减少加锁的影响。

加上全局读锁，只允许读，不允许更新操作。

3. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

设置当前会话的事务隔离等级为RR，RR可避免不可重复读和幻读。

4. START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

获取当前数据库的快照，这个是由mysqldump中--single-transaction决定的。

这个只适用于支持事务的表，在MySQL中，只有Innodb。

注意：START TRANSACTION和START TRANSACTION WITH CONSISTENT SNAPSHOT并不一样，

START TRANSACTION WITH CONSISTENT SNAPSHOT是开启事务的一致性快照。

RC下的START TRANSACTION WITH CONSISTENT SNAPSHOT相当于RR下的START TRANSACTION。

不明白事务的童鞋可能觉得这点会比较绕，其实所谓的不可重复读和幻读可简单理解为，在同一个事务内，两次SELECT的结果并不相同。

之所以要使用START TRANSACTION WITH CONSISTENT SNAPSHOT，因为每个表的备份时间并不相同，这就要求在对第一张表进行备份的期间，对第二个表进行的操作，并不会反映到第二张表开始备份时执行的SELECT操作中。（注：mysqldump备份的底层实现即是select * from tab）。而这用START TRANSACTION就无法实现。

5. SHOW MASTER STATUS

这个是由--master-data决定的，记录了开始备份时，binlog的状态信息，包括MASTER_LOG_FILE和MASTER_LOG_POS

6. UNLOCK TABLES

释放锁。

因为我的数据库中只有以下四个库

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.03 sec)

备份的时候可以发现只备份了mysql和test，并没有备份information_schema和performance_schema。

下面来看看备份mysql和test的日志输出信息，

因日志输出信息太多，在这里，只选择test库的日志信息。test库中一共有两张表test和test1。

 14 Init DB test 14 Query SHOW CREATE DATABASE IF NOT EXISTS `test` 14 Query SAVEPOINT sp 14 Query show tables 14 Query show table status like ‘test‘ 14 Query SET SQL_QUOTE_SHOW_CREATE=1 14 Query SET SESSION character_set_results = ‘binary‘ 14 Query show create table `test` 14 Query SET SESSION character_set_results = ‘utf8‘ 14 Query show fields from `test` 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test` 14 Query SET SESSION character_set_results = ‘binary‘ 14 Query use `test` 14 Query select @@collation_database 14 Query SHOW TRIGGERS LIKE ‘test‘ 14 Query SET SESSION character_set_results = ‘utf8‘ 14 Query ROLLBACK TO SAVEPOINT sp 14 Query show table status like ‘test1‘ 14 Query SET SQL_QUOTE_SHOW_CREATE=1 14 Query SET SESSION character_set_results = ‘binary‘ 14 Query show create table `test1` 14 Query SET SESSION character_set_results = ‘utf8‘ 14 Query show fields from `test1` 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test1` 14 Query SET SESSION character_set_results = ‘binary‘ 14 Query use `test` 14 Query select @@collation_database 14 Query SHOW TRIGGERS LIKE ‘test1‘ 14 Query SET SESSION character_set_results = ‘utf8‘ 14 Query ROLLBACK TO SAVEPOINT sp 14 Query RELEASE SAVEPOINT sp 14 Query use `test` 14 Query select @@collation_database 14 Query SET SESSION character_set_results = ‘binary‘ 14 Query SHOW FUNCTION STATUS WHERE Db = ‘test‘ 14 Query SHOW CREATE FUNCTION `mycat_seq_currval` 14 Query SHOW PROCEDURE STATUS WHERE Db = ‘test‘ 14 Query SET SESSION character_set_results = ‘utf8‘ 14 Quit

从上述输出可以看出：

1. 备份的核心是SELECT /*!40001 SQL_NO_CACHE */ * FROM `test1`语句。

该语句会查询到表test1的所有数据，在备份文件中会生成相应的insert语句。

其中SQL_NO_CACHE的作用是查询的结果并不会缓存到查询缓存中。

2. SHOW CREATE DATABASE IF NOT EXISTS `test`，show create table `test1`

生成创库语句和创表语句。

3. SHOW TRIGGERS LIKE ‘test1‘

可以看出，如果不加-R参数，默认是会备份触发器的。

4. SHOW FUNCTION STATUS WHERE Db = ‘test‘

SHOW CREATE FUNCTION `mycat_seq_currval`

SHOW PROCEDURE STATUS WHERE Db = ‘test‘

用于备份存储过程和函数。

5. 设置SAVEPOINT，然后备份完每个表后再回滚到该SAVEPOINT。

不知道为什么要这么设置，感觉这样完全没必要，

因为前面通过START TRANSACTION WITH CONSISTENT SNAPSHOT开启的事务只能通过commit或者rollback来结束，而不是ROLLBACK TO SAVEPOINT sp。

PS：这样做不会阻塞在备份期间对已经备份表的ddl操作，具体可见下面的“后续补充”第三点。

总结：

1. mysqldump的本质是通过select * from tab来获取表的数据的。

2. START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */必须放到FLUSH TABLES WITH READ LOCK和UNLOCK TABLES之间，放到之前会造成START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */和FLUSH TABLES WITH READ LOCK之间执行的DML语句丢失，放到之后，会造成从库重复插入数据。

3. mysqldump只适合放到业务低峰期做，如果备份的过程中数据操作很频繁，会造成Undo表空间越来越大，undo表空间默认是放到共享表空间中的，而ibdata的特性是一旦增大，就不会收缩。

4. mysqldump的效率还是比较低下，START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */只能等到所有表备份完后才结束，其实效率比较高的做法是备份完一张表就提交一次，这样可尽快释放Undo表空间快照占用的空间。但这样做，就无法实现对所有表的一致性备份。

参考：

http://tencentdba.com/blog/mysqldump-backup-principle/

后续补充：

最近翻了下源码，msyqldump对应的是client/mysqldump.c。里面的注释还是有一定的参考意义，一并附上。

1. 关于FLUSH TABLE和FLUSH TABLES WITH READ LOCK的

 /* We do first a FLUSH TABLES. If a long update is running, the FLUSH TABLES will wait but will not stall the whole mysqld, and when the long update is done the FLUSH TABLES WITH READ LOCK will start and succeed quickly. So, FLUSH TABLES is to lower the probability of a stage where both mysqldump and most client connections are stalled. Of course, if a second long update starts between the two FLUSHes, we have that bad stall. */

2. 为什么备份结束时没有执行commit操作

 /* No reason to explicitely COMMIT the transaction, neither to explicitely UNLOCK TABLES: these will be automatically be done by the server when we disconnect now. Saves some code here, some network trips, adds nothing to server. */

3. 关于ROLLBACK TO SAVEPOINT的

/** ROLLBACK TO SAVEPOINT in --single-transaction mode to release metadata lock on table which was already dumped. This allows to avoid blocking concurrent DDL on this table without sacrificing correctness, as we won‘t access table second time and dumps created by --single-transaction mode have validity point at the start of transaction anyway. Note that this doesn‘t make --single-transaction mode with concurrent DDL safe in general case. It just improves situation for people for whom it might be working. */

上面的意思是不阻塞并发的DDL操作。

下面具体来测试一下：

第一种情况：

会话1发起事务，并查询test表的值，然后会话2进行添加列操作，该操作被hang住。

技术分享

第二种情况：

会话1发起事务，然后会话2进行添加列操作，发现该操作成功。

技术分享

第三种情况：

模仿mysqldump的备份原理，设置断点。

注意，DDL操作发起的时间是在执行了select * from test之后，如果是在之前，根据上面第二种情况的测试，是可以进行DDL操作的。

此时，如果不执行ROLLBACK TO SAVEPOINT sp，DDL操作会一直hang下去，执行了该操作后，DDL操作可以继续执行了。

由此可见，ROLLBACK TO SAVEPOINT确实可以提高DDL的并发性。

但还有一点需要注意，如果DDL操作是发生在select * from test之前，正如第二种情况所演示的，DDL操作会成功，此时，查看test表的数据会报以下错误：

root@test 04:32:49 > select * from test;ERROR 1412 (HY000): Table definition has changed, please retry transaction

对应mysqldump，会报如下错误：

mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `test` at row: 0

技术分享

mysqldump备份7

标签：

小编还为您整理了以下内容，可能对您也有帮助：

mysqldump 如何备份以及如何解决锁表的问题

mysqlmp是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。如果给mysqlmp进行备份，从库上停止复制的sql线程然后mysqlmp，这个是个很好的选择，因为停止复制就没有写，就不用担心锁表的问题。下面提供两只备份方法：

一、MyISAM引擎备份
1. 由于MyISAM引擎为表级锁，因此，在备份时需要防止在备份期间数据写入而导致不一致，
2. 所以，在备份时使用--lock-all-tables加上读锁

mysqlmp -A -F -B --lock-all-tables |gzip >/data/backup/$(date +%F).tar.gz
3. 特别提示：有关MyISAM和InnoDB引擎的差别和在工作中如何选择，在前面已经详细讲解过了，这里就不在讲了。

二、 InnoDB引擎备份
1. InnoDB引擎为行锁，因此，备份时可以不对数据库加锁的操作，可以加选项--single-transaction进行备份：
mysqlmp -A -F -B --single-transaction |gzip >/data/backup/$(date +%F).tar.gz

2. 特别注意：
--single-transaction仅适用于InnoDB引擎。

--master-data=2
会将当前mysql用到的binlog文件的日志名称和位置记录下来然后搜索change master就行了
mysqlmp -uroot -p'passwd' -B ctp1 --lock-all-tables|gzip >/home/mysql/ctp1.$(date +%F).tar.gz
--no--data 仅仅mp数据库结构创建脚本通过--no-create-info 去掉mp文件中创建表结构的命令。

mysqldump 如何备份以及如何解决锁表的问题

mysql如何备份数据

1.1、 mysqlmp命令备份数据
在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqlmp,我们可以通过命令行直接实现数据库内容的导出mp,首先我们简单了解一下mysqlmp命令用法:
#MySQLmp常用
mysqlmp -u root -p --databases 数据库1 数据库2 > xxx.sql
1.2、 mysqlmp常用操作实例
1.备份全部数据库的数据和结构
mysqlmp -uroot -p123456 -A > /data/mysqlDump/mydb.sql
2.备份全部数据库的结构（加 -d 参数）
mysqlmp -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql
3.备份全部数据库的数据(加 -t 参数)
mysqlmp -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql
4.备份单个数据库的数据和结构(,数据库名mydb)
mysqlmp -uroot-p123456 mydb > /data/mysqlDump/mydb.sql
5.备份单个数据库的结构
mysqlmp -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql
6.备份单个数据库的数据
mysqlmp -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql
7.备份多个表的数据和结构（数据，结构的单独备份方法与上同）
mysqlmp -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql
8.一次备份多个数据库
mysqlmp -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

mysql如何备份数据

如何备份整个mysql数据库

1、首先打开mysql数据库软件进入软件主界面。

2、然后再左侧树里打开自己的的数据库。

3、然后需要点击需要备份的数据库名。

4、如图所示为打开数据库后界面。

5、然后需要点击转储sql文件选项。

6、然后需要打开选择存储文件路径并选择保存。

7、点击保存即可在路径备份好格式为sql的数据库文件。

如何在64位Windows7上用命令备份MySQL数据库

1.备份命令:

mysqlmp -u用户名 -p密码 -h服务器IP 数据库名 > 备份文件名

例如:

mysqlmp -uroot -p123456 -h192.168.1.2 discuz > /data/backup/discuz.sql

2.还原命令:

mysql -u用户名 -p密码 -h服务器IP 数据库名 < 备份文件名

例如:

mysql -uroot -p123456 -h192.168.1.2 discuz < /data/backup/discuz.sql

显示全文

全部栏目

mysqldump备份7