mysql备份要执行空表语句_MySQLDump 数据库全量备份工具备份操作的执行过程详解 | IT工程师的生活足迹...

MySQL 官方提供的数据库逻辑备份的工具MySQLdump，可以将数据库对象定义和表数据转储到文件中(sql,csv,xml)。

一、概述

1.1、优点和缺点

逻辑备份工具；将数据库备份为通用形式的SQL文件。对于InnoDB表可以在线备份。

单线程，数据量大的时候，备份时间长。

备份时会查询所有的数据，这可能会把内存中的热点数据刷掉。

1.2、InnoDB存储引擎常用参数

--single-transaction --master-data=2 --triggers --routines --events test

二、备份详细过程

MySQLdump 工具对 MySQL 数据库备份过程可以分为：备份前，备份中和备份后；三个阶段。

2.1、备份前

数据备份前的准备工作

1、MySQLdump 工具连接MySQL服务端

3 Connectroot@localhost on

2、修改当前SESSION的SQL模式为空；避免对备份产生影响

3 Query/*!40100 SET @@SQL_MODE='' */

3 Query/*!40103 SET TIME_ZONE='+00:00' */

3、强制刷新表

关闭所有打开的表，强制所有正在使用的表被关闭，并刷新查询缓存和准备语句缓存。

Query FLUSH /*!40101 LOCAL */ TABLES

4、对整个实例加全局读锁；如果已经存在表锁将阻塞加全局读锁语句

Closes all open tables and locks all tables for all databases with a global read lock.

Query FLUSH TABLES WITH READ LOCK

5、设置当前SESSION隔离级别为RR

Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

6、开启一个一致性快照事务

Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

7、查看是否开启GTID；如果开启GTID则查看当前的事务GTID集合

Query SHOW VARIABLES LIKE 'gtid\_mode'

Query SELECT @@GLOBAL.GTID_EXECUTED

8、查看当前二进制日志位置

Query SHOW MASTER STATUS

9、释放全局读锁

Query UNLOCK TABLES

10、获取数据库中的表和其他信息

3 QuerySELECT 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 AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME

3 QuerySELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

3 QuerySHOW VARIABLES LIKE 'ndbinfo\_version'

Init DB test

11、在一个数据库开始备份之前，设置一个保存点(回滚点)’

Query SAVEPOINT sp

12、查看库下有哪些表’

Query show tables

2.2、开始备份数据库表

对数据库中的表进行备份

1、查看这个表的状态’

Query show table status like 'country'

2、给每个表的每个字段加个反引号

Query SET SQL_QUOTE_SHOW_CREATE=1

3、表结构的备份都是binary格式

Query SET SESSION character_set_results = 'binary'

4、查看这个表的定义语句

Query show create table `country`

5、修改session的数据结果返回字符集，备份数据需要使用数据原本的字符集，这里是utf8

Query SET SESSION character_set_results = 'utf8'

6、查看这个表的字段信息

Query show fields from `country`

7、查询表中的数据，结合show fields from `country`的字段信息生成insert into语句

3 QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `country`

3 QuerySET SESSION character_set_results = 'binary'

8、查询此连接使用的字符编码，并设置(查询是否有触发器)

3 Queryuse `test`

3 Queryselect @@collation_database

3 QuerySHOW TRIGGERS LIKE 'country'

3 QuerySET SESSION character_set_results = 'utf8'

9、country 数据表备份完成，回滚到sp保存点。

3 QueryROLLBACK TO SAVEPOINT sp

10、备份其他数据表、相同的过程

3 Queryshow table status like 'name'

3 QuerySET SQL_QUOTE_SHOW_CREATE=1

3 QuerySET SESSION character_set_results = 'binary'

3 Queryshow create table `name`

3 QuerySET SESSION character_set_results = 'utf8'

3 Queryshow fields from `name`

3 QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `name`

3 QuerySET SESSION character_set_results = 'binary'

3 Queryuse `test`

3 Queryselect @@collation_database

3 QuerySHOW TRIGGERS LIKE 'name'

3 QuerySET SESSION character_set_results = 'utf8'

3 QueryROLLBACK TO SAVEPOINT sp

2.3、数据表备份完成

1、整个test库备份完成之后，释放该保存点

Query RELEASE SAVEPOINT sp

2、查看是否有相关的events

Query show events

3、查询test数据库的编码、存储函数、存储过程

3 Queryuse `test`

3 Queryselect @@collation_database

3 QuerySET SESSION character_set_results = 'binary'

3 QuerySHOW FUNCTION STATUS WHERE Db = 'test'

3 QuerySHOW PROCEDURE STATUS WHERE Db = 'test'

3 QuerySET SESSION character_set_results = 'utf8'

4、备份结束，退出连接

Quit