目录
1. 备份类型
2. 逻辑备份VS物理备份
3. mysqldump工具
3.1 备份命令格式
3.2 备份选项
3.3 备份全库(结构和数据)
3.4 备份全库(仅结构)
3.5 备份全库(仅数据)
3.6 备份单个数据库(结构和数据)
3.7 备份单个数据库(仅结构)
3.8 备份单个数据库(仅数据)
3.9 备份多个指定数据库(多个数据库以空格间隔)
3.10 备份多张表
3.11 排除某些表
3.12 指定where条件
3.13 压缩备份
3.14 压缩备份至远程服务器
3.13 一致性导出
3.14 备份原理
3.15 详细过程解释
3.16 原理总结
4.恢复
4.1 mysql命令恢复
4.2 source命令恢复
4.3 备份并用gzip压缩
4.4 备份并用bzip压缩
1. 备份类型
2. 逻辑备份VS物理备份
3. mysqldump工具
mysqldump是MySQL自带的逻辑备份工具，连接MySQL数据库，生成一组SQL语句，可以执行这些语句来重现原始数据库对象定义和表数据。mysqldump命令还可以生成CSV、其他分隔文本或XML格式的输出。它是单线程备份恢复。
3.1 备份命令格式
mysqldump [options] db_name [tbl_name ...] > 脚本名.sql
mysqldump [options] --databases db_name ... > 脚本名.sql
mysqldump [options] --all-databases > 脚本名.sql3.2 备份选项
| 选项 | 简写 | 描述 |
| --add-drop-database | 在每个CREATE DATABASE语句之前添加DROP DATABASE语句 | |
| --add-drop-table | 在每个CREATE TABLE语句之前添加DROP TABLE语句 | |
| --add-drop-trigger | 在每个CREATE TRIGGER语句之前添加DROP TRIGGER声明 | |
| --add-locks | 在每张导出的表前后用LOCK TABLES和UNLOCK TABLES语句包裹 | |
| --all-databases | -A | 导出所有数据库的所有表 |
| --allow-keywords | 允许创建的列名是关键字 | |
| --apply-slave-statements | 在CHANGE MASTER语句之前包括STOP SLAVE，在输出结束时包括START SLAVE | |
| --bind-address | 使用指定的网络接口连接到MySQL Server | |
| --character-sets-dir | 安装字符集的目录 | |
| --comments | -i | 将注释添加到dump file |
| --compact | 提供较少的详细输出（对调试有用）。禁用结构注释和页眉/页脚构造,相当于一个参数包，其中包含了--skip-add-drop-table，--skip-add-locks，--skip-comments，--skip-disable-keys，--skip-set-charset,以产生更紧促的输出 | |
| --compatible | 与其他数据库兼容，或与旧版本MySQL兼容，值为ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options，多个值用逗号分隔 | |
| --complete-insert | -c | 在CREATE TABLE语句中包括所有MySQL特定的表选项 |
| --compress | -C | 在客户端和服务器之间启用压缩传递所有信息 |
| --create-options | -a | 在CREATE TABLE语句中包括所有MySQL特性选项(默认为打开状态,可以使用--skip-create-options禁用) |
| --databases | -B | 指定要备份的数据库 |
| --debug | -# | 写入调试日志 |
| --debug-check | 程序退出时打印调试信息 | |
| --debug-info | 程序退出时打印调试信息,内存和CPU统计信息 | |
| --default-auth | 使用的密码验证插件 | |
| --default-character-set | 指定默认字符集 | |
| --defaults-extra-file | 除了读取常用选项文件外，还读取命名选项文件 | |
| --defaults-file | 只读取命名选项文件 | |
| --defaults-group-suffix | 组后缀 | |
| --delete-master-logs | 在复制源服务器上，执行dump(备份)操作后删除二进制日志，自动启用 | |
| --disable-keys | -K | disable and enable keys包围INSERT语句 |
| --dump-date | 输出结尾显示dump完成时间，默认为on；使用--skip dump date可禁用 | |
| --dump-slave | 该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，在命令前增加说明信息。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0 | |
| --enable-cleartext-plugin | Enable/disable明文身份验证插件 | |
| --events | -E | 导出events |
| --extended-insert | -e | 使用多行INSERT语法 |
| --fields-enclosed-by | 指定包裹字段的字符 | |
| --fields-escaped-by | 指定转义字符 | |
| --fields-optionally-enclosed-by | 指定包裹字段的字符，对纯数字无影响不加字符包裹 | |
| --fields-terminated-by | 指定字段之间的字符 | |
| --flush-logs | -F | 导出之前刷新日志，假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。 |
| --flush-privileges | 在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。 | |
| --force | -f | 遇到sql报错继续执行 |
| --get-server-public-key | 从服务器请求RSA公钥 | |
| --help | -? | 查看帮助 |
| --hex-blob | 使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB | |
| --host | -h | 要导出的MySQL服务器ip |
| --ignore-error | 指定忽略的错误，错误号以逗号分隔 | |
| --ignore-table | 不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=db.t1 --ignore-table=db.t2 | |
| --include-master-host-port | 使用了--dump-slave时在CHANGE MASTER语句中加入MASTER_HOST和MASTER_PORT。 | |
| --insert-ignore | 用INSERT IGNORE 而非INSERT ，以忽略主键或唯一键的重复数据。 | |
| --lines-terminated-by | 指定换行符 | |
| --lock-all-tables | -x | 提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭–single-transaction 和–lock-tables 选项 |
| --lock-tables | -l | 导出之前锁定所有表 |
| --log-error | 附加警告和错误信息到给定文件 | |
| --login-path | 从.mylogin.cnf登录文件读取登录参数。可通过mysql_config_editor创建登录文件。 | |
| --master-data | 该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。（在主执行） | |
| --max-allowed-packet | 发送或接收服务器的最大数据包长度 | |
| --net-buffer-length | TCP/IP及socket通讯的buffer大小 | |
| --no-autocommit | 在INSERT前后添加set autocommit=0和commit | |
| --no-create-db | -n | 若使用了--all-databases或--databases，则不在输出中添加CREATE DATABASE |
| --no-create-info | -t | 不使用CREATE TABLE 重建备份的表 |
| --no-data | -d | 只导出表结构，不导出表数据 |
| --no-defaults | 不读取选项文件 | |
| --no-set-names | -N | 相当于 --skip-set-charset |
| --no-tablespaces | -y | 不导出任何表空间信息，不在输出中使用CREATE LOG FILE和CREATE TABLESPACE |
| --opt | --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset,和--disable-keys几个选项合起来的简写形式。 | |
| --order-by-primary | 将备份的表中的行按主键排序或者第一个唯一键排序 | |
| --password | -p | 连接MySQL用户的密码 |
| --pipe | 在Windows上通过命名管道连接server | |
| --plugin-dir | 客户端插件的目录，用于兼容不同的插件版本 | |
| --port | -P | MySQL连接端口 |
| --print-defaults | 输出默认选项 | |
| --protocol | 连接MySQL使用的协议 | |
| --quick | -q | 备份时逐行读取表而非一次全部读取行后缓冲在内存中。在备份大表时有用。不缓冲查询，直接导出到标准输出 |
| --quote-names | -Q | 用反引号（`）引用表和列名。（默认为on;使用--skip-quote-names禁用。）若使用了ANSI_QUOTES则用“””包围。特别的，可能需要在—compatible后开启该选项 |
| --replace | 用REPLACE替代INSERT INTO.（REPLACE的运行与INSERT很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。 | |
| --result-file | -r | 将结果输出到指定的文件 |
| --routines | -R | 导出routines（存储过程和函数） |
| --secure-auth | 如果客户端使用旧的（4.1.1之前的）协议，则拒绝客户端连接到服务器。MySQL 5.7.5 该参数已弃用。始终为TRUE，不能修改 | |
| --server-public-key-path | RSA公钥目录 | |
| --set-charset | 将“SET NAMES default_character_SET”添加到输出中。（默认为on；使用–skip-set-charset关闭） | |
| --set-gtid-purged | 确定是否在结果中添加SET @@GLOBAL.GTID_PURGED。若为ON但在server中没有开启GTID则发生错误。若OFF则什么都不做。若AUTO则server中开启GTID那么添加上述语句，反之不加。 | |
| --shared-memory-base-name | 共享内存连接的共享内存名称（仅限Windows） | |
| --single-transaction | 在备份前设置事务隔离级别为REPEATABLE READ并向server发送START TRANSACTION语句。仅对事务型表如InnoDB有用。与--ock-tables互斥。对于大文件备份--single-transaction与--quick结合使用。 | |
| --skip-add-drop-table | 禁用--add-drop-table， 在CREATE TABLE之前不添加DROP TABLE语句 | |
| --skip-add-locks | 禁用--add-locks | |
| --skip-comments | 禁用--comments | |
| --skip-compact | 禁用--compact | |
| --skip-disable-keys | 禁用--disable-keys | |
| --skip-extended-insert | 禁用--extended-insert | |
| --skip-mysql-schema | 导出时不删除schema，默认要删除schema，在MySQL 5.7.36中增加此参数 | |
| --skip-opt | 禁用--skip-opt | |
| --skip-quick | 禁用--quick | |
| --skip-quote-names | 禁用--quote-names | |
| --skip-set-charset | 禁用--set-charset | |
| --skip-triggers | 不导出triggers | |
| --skip-tz-utc | 禁用-- tz-utc | |
| --socket | -S | 连接本机server所使用的socket |
| --ssl | 使用加密连接 | |
| --ssl-ca | 包含受信任SSL认证列表的文件 | |
| --ssl-capath | 包含受信任SSL认证列表文件的目录 | |
| --ssl-cert | 包含X.509证书的文件 | |
| --ssl-cipher | 一系列用于SSL加密的所允许的密码 | |
| --ssl-crl | 包含废弃证书的列表 | |
| --ssl-crlpath | 包含废弃证书列表的目录 | |
| --ssl-key | 包含X.509密钥的文件 | |
| --ssl-mode | 与服务器连接所需的安全状态 | |
| --ssl-verify-server-cert | 已废弃. 使用--ssl-mode=VERIFY_IDENTITY 替代 | |
| --tab | -T | 为给定的每个表创建以制表符分隔的文本文件路径。 创建.sql和.txt文件，.sql文件为表结构，.txt为表数据。注意：这只适用如果mysqldump在与mysqld相同的机器上运行服务器。 |
| --tables | 覆盖--databases (-B)参数，指定需要导出的表名 | |
| --tls-version | 用于加密连接的允许TLS协议 | |
| --triggers | 导出表trigger | |
| --tz-utc | 在导出顶部设置时区TIME_ZONE='+00:00' ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。(默认开启，用--skip-tz-utc禁用) | |
| --user | -u | 连接MySQL的用户 |
| --verbose | -v | 输出多种平台信息,详细信息 |
| --version | -V | 显示mysqldump版本信息并退出 |
| --where | -w | 仅导出与where条件中匹配的行。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来 |
| --xml | -X | 导出XML格式 |
3.3 备份全库(结构和数据)
mysqldump -uroot -p123456 --all-databases > /backup/all_db.sql
或者
mysqldump -uroot -p123456 -A > /backup/all_db.sql3.4 备份全库(仅结构)
mysqldump -uroot -p123456 -A -d > /backup/all_metadata.sql
或者
mysqldump -uroot -p123456 --all-databases --no-data > /backup/all_metadata.sql3.5 备份全库(仅数据)
mysqldump -uroot -p123456 --all-databases --no-create-info > /backup/all_data.sql
或者
mysqldump -uroot -p123456 -A -t > /backup/all_data.sql3.6 备份单个数据库(结构和数据)
mysqldump -uroot -p123456 zs > /backup/zs.sql3.7 备份单个数据库(仅结构)
mysqldump -uroot -p123456 zs -d > /backup/zs_metadata.sql
或者
mysqldump -uroot -p123456 zs --no-data > /backup/zs_metadata.sql3.8 备份单个数据库(仅数据)
mysqldump -uroot -p123456 zs -t > /backup/zs_data.sql
或者
mysqldump -uroot -p123456 zs --no-create-info > /backup/zs_data.sql3.9 备份多个指定数据库(多个数据库以空格间隔)
mysqldump -uroot -p123456 --databases db1 db2 > /backup/somedbs.sql
或者
mysqldump -uroot -p123456 -B db1 db2 > /backup/somedbs.sql3.10 备份多张表
mysqldump -uroot -p123456 -B zs --tables aa z1 > /backup/aa_z1.sql
或者
mysqldump -uroot -p123456 zs aa z1 > /backup/aa_z1.sql3.11 排除某些表
导出DB中其余表结构和数据
mysqldump -uroot -p123456 zs --ignore-table=zs.aa --ignore-table=zs.t1 > /backup/ignoe.sql3.12 指定where条件
导出表的部分数据(多张表都需有where条件后的字段)
mysqldump -uroot -p123456 zs z1 t1 -w 'a<=4' > /backup/where.sql3.13 压缩备份
mysqldump -uroot -p123456 -B zs python --single-transaction --master-data=2 --triggers | gzip > /backup/zs_python.sql.tar.gz3.14 压缩备份至远程服务器
mysqldump -uroot -p123456 -B zs python --single-transaction --master-data=2 --triggers -E -R | gzip | ssh root@192.168.26.101 'cat > /tmp/zs_python.sql.tar.gz'3.13 一致性导出
导出的时候为了一致性，一般加上参数--single-transaction，可以在配置文件中加上该参数
[mysqldump]
single-transaction3.14 备份原理
版本为MySQL 5.7.44
#设置general_log日志输出到表，然后执行mysqldump导出
set global log_output = 'TABLE'
set global general_log = 1;
#mysqldump执行完毕后关闭general_log
set global general_log = 0;#执行语句
mysqldump -uroot -p123456 -B zs python --single-transaction --triggers -E -R > /backup/zs_python.sql查看mysql.general_log表日志
+-----------+--------------+------------------------------------------------------------------------------------------------------+
| thread_id | command_type | left(argument,100) |
+-----------+--------------+------------------------------------------------------------------------------------------------------+
| 23 | Connect | root@localhost on using Socket |
| 23 | Query | /*!40100 SET @@SQL_MODE='' */ |
| 23 | Query | /*!40103 SET TIME_ZONE='+00:00' */ |
| 23 | Query | SHOW VARIABLES LIKE 'gtid_mode' |
| 23 | Query | FLUSH /*!40101 LOCAL */ TABLES |
| 23 | Query | FLUSH TABLES WITH READ LOCK |
| 23 | Query | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| 23 | Query | START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ |
| 23 | Query | SHOW MASTER STATUS |
| 23 | Query | UNLOCK TABLES |
| 23 | Query | SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC |
| 23 | Query | SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR |
| 23 | Query | SHOW VARIABLES LIKE 'ndbinfo\_version' |
| 23 | Init DB | zs |
| 23 | Query | SHOW CREATE DATABASE IF NOT EXISTS `zs` |
| 23 | Query | SAVEPOINT sp |
| 23 | Query | show tables |
| 23 | Query | show table status like 'aa' |
| 23 | Query | SET SQL_QUOTE_SHOW_CREATE=1 |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | show create table `aa` |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | show fields from `aa` |
| 23 | Query | show fields from `aa` |
| 23 | Query | SELECT /*!40001 SQL_NO_CACHE */ * FROM `aa` |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | use `zs` |
| 23 | Query | select @@collation_database |
| 23 | Query | SHOW TRIGGERS LIKE 'aa' |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | ROLLBACK TO SAVEPOINT sp |
| 23 | Query | show table status like 't1' |
| 23 | Query | SET SQL_QUOTE_SHOW_CREATE=1 |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | show create table `t1` |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | show fields from `t1` |
| 23 | Query | show fields from `t1` |
| 23 | Query | SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | use `zs` |
| 23 | Query | select @@collation_database |
| 23 | Query | SHOW TRIGGERS LIKE 't1' |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | ROLLBACK TO SAVEPOINT sp |
| 23 | Query | show table status like 'z1' |
| 23 | Query | SET SQL_QUOTE_SHOW_CREATE=1 |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | show create table `z1` |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | show fields from `z1` |
| 23 | Query | show fields from `z1` |
| 23 | Query | SELECT /*!40001 SQL_NO_CACHE */ * FROM `z1` |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | use `zs` |
| 23 | Query | select @@collation_database |
| 23 | Query | SHOW TRIGGERS LIKE 'z1' |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | ROLLBACK TO SAVEPOINT sp |
| 23 | Query | RELEASE SAVEPOINT sp |
| 23 | Query | show events |
| 23 | Query | use `zs` |
| 23 | Query | select @@collation_database |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | SHOW FUNCTION STATUS WHERE Db = 'zs' |
| 23 | Query | SHOW PROCEDURE STATUS WHERE Db = 'zs' |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Init DB | python |
| 23 | Query | SHOW CREATE DATABASE IF NOT EXISTS `python` |
| 23 | Query | SAVEPOINT sp |
| 23 | Query | show tables |
| 23 | Query | show table status like 'Orders' |
| 23 | Query | SET SQL_QUOTE_SHOW_CREATE=1 |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | show create table `Orders` |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | show fields from `Orders` |
| 23 | Query | show fields from `Orders` |
| 23 | Query | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Orders` |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | use `python` |
| 23 | Query | select @@collation_database |
| 23 | Query | SHOW TRIGGERS LIKE 'Orders' |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | ROLLBACK TO SAVEPOINT sp |
| 23 | Query | show table status like 't' |
| 23 | Query | SET SQL_QUOTE_SHOW_CREATE=1 |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | show create table `t` |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | show fields from `t` |
| 23 | Query | show fields from `t` |
| 23 | Query | SELECT /*!40001 SQL_NO_CACHE */ * FROM `t` |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | use `python` |
| 23 | Query | select @@collation_database |
| 23 | Query | SHOW TRIGGERS LIKE 't' |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | ROLLBACK TO SAVEPOINT sp |
| 23 | Query | show table status like 'y' |
| 23 | Query | SET SQL_QUOTE_SHOW_CREATE=1 |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | show create table `y` |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | show fields from `y` |
| 23 | Query | show fields from `y` |
| 23 | Query | SELECT /*!40001 SQL_NO_CACHE */ * FROM `y` |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | use `python` |
| 23 | Query | select @@collation_database |
| 23 | Query | SHOW TRIGGERS LIKE 'y' |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | ROLLBACK TO SAVEPOINT sp |
| 23 | Query | show table status like 'z' |
| 23 | Query | SET SQL_QUOTE_SHOW_CREATE=1 |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | show create table `z` |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | show fields from `z` |
| 23 | Query | show fields from `z` |
| 23 | Query | SELECT /*!40001 SQL_NO_CACHE */ * FROM `z` |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | use `python` |
| 23 | Query | select @@collation_database |
| 23 | Query | SHOW TRIGGERS LIKE 'z' |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Query | ROLLBACK TO SAVEPOINT sp |
| 23 | Query | RELEASE SAVEPOINT sp |
| 23 | Query | show events |
| 23 | Query | use `python` |
| 23 | Query | select @@collation_database |
| 23 | Query | SET SESSION character_set_results = 'binary' |
| 23 | Query | SHOW FUNCTION STATUS WHERE Db = 'python' |
| 23 | Query | SHOW PROCEDURE STATUS WHERE Db = 'python' |
| 23 | Query | SET SESSION character_set_results = 'utf8' |
| 23 | Quit | |
+-----------+--------------+------------------------------------------------------------------------------------------------------+
3.15 详细过程解释
#1.MySQLdump 工具连接MySQL服务端
root@localhost on using Socket
#2.修改当前SESSION的SQL模式为空；避免对备份产生影响
/*!40100 SET @@SQL_MODE='' */
#3.设置time_zone为+00:00
/*!40103 SET TIME_ZONE='+00:00' */
#4.查询是否开启GTID
SHOW VARIABLES LIKE 'gtid_mode'
#5.刷新表，减少FTWRL锁的等待时间
FLUSH /*!40101 LOCAL */ TABLES
#6.因为开启了--master-data=2，这时就需要flush tables with read lock锁住全库为只读，
记录当时的master_log_file和master_log_pos点
这里有一个疑问？
执行flush tables操作，并加一个全局读锁，那么以上两个命令貌似是重复的，
为什么不在第一次执行flush tables操作的时候加上锁呢？
是为了避免较长的事务操作造成FLUSH TABLES WITH READ LOCK操作迟迟得不到
锁，但同时又阻塞了其它客户端操作，减少FTWRL锁的等待时间。
FLUSH TABLES WITH READ LOCK
#7.--single-transaction参数的作用，设置事务的隔离级别为可重复读，
即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，
也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，
对该dump线程的数据并无影响，然而这个还不够，还需要看下一条
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
#8.获取当前数据库的快照，这个是由mysqldump中--single-transaction决定的。
# WITH CONSISTENT SNAPSHOT能够保证在事务开启的时候，第一次查询的结果就是
事务开始时的数据A，即使这时其他线程将其数据修改为B，查的结果依然是A。简而言之，就是开启事务并对所有表执行了一次SELECT操作，这样可保证备份时，
在任意时间点执行select * from table得到的数据和
执行START TRANSACTION WITH CONSISTENT SNAPSHOT时的数据一致。
【注意】，WITH CONSISTENT SNAPSHOT只在RR隔离级别下有效
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
#9.这个是由--master-data决定的，记录了开始备份时，binlog的状态信息，
包括MASTER_LOG_FILE和MASTER_LOG_POS
SHOW MASTER STATUS
#10.释放FLUSH TABLES WITH READ LOCK锁
UNLOCK TABLES
#11.获取undo文件信息
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 AND LOGFILE_GROUP_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 ('zs','python'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
#12.获取数据文件信息
SELECT 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 ('zs','python')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
#13.查看ndb版本信息
SHOW VARIABLES LIKE 'ndbinfo\_version'
#14.开始导出zs数据库
zs
#15.查看创建zs数据库的语句加上IF NOT EXISTS子句
SHOW CREATE DATABASE IF NOT EXISTS `zs`
#16.在一个事务中，sp检查点为了释放表元数据锁
SAVEPOINT sp
#17.获取数据库表信息
show tables
#18.获取表的状态信息
show table status like 'aa'
#19.sql_quote_show_create，有两个值（1，0），默认是1，表示表名和列名会用``包着的
SET SQL_QUOTE_SHOW_CREATE=1
#20.设置字符集为binary,为了更好的备份表结构，将字符集先设置成binary，避免出错
SET SESSION character_set_results = 'binary'
#21.获取表创建语句
show create table `aa`
#22.设置字符集为utf8,开始备份表数据时将字符集设置为数据库的字符集。
SET SESSION character_set_results = 'utf8'
#23.获取表的字段信息
show fields from `aa`
show fields from `aa`
#24.查询表数据,结合show fields from `country`的字段信息生成insert into语句，开始导出表
SELECT /*!40001 SQL_NO_CACHE */ * FROM `aa`
#25.设置字符集为binary
SET SESSION character_set_results = 'binary'
use `zs`
#查看zs数据库字符集编码
select @@collation_database
#26.查询触发器，导出触发器
SHOW TRIGGERS LIKE 'aa'
SET SESSION character_set_results = 'utf8'
#27.回滚至sp检查点，释放表aa的元数据锁，使其他操作可以作用在表aa，比如创建索引等等
ROLLBACK TO SAVEPOINT sp
#28.以下是重复操作步骤18~27，对其他表进行导出
show table status like 't1'
SET SQL_QUOTE_SHOW_CREATE=1
SET SESSION character_set_results = 'binary'
show create table `t1`
SET SESSION character_set_results = 'utf8'
show fields from `t1`
show fields from `t1`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
SET SESSION character_set_results = 'binary'
use `zs`
select @@collation_database
SHOW TRIGGERS LIKE 't1'
SET SESSION character_set_results = 'utf8'
ROLLBACK TO SAVEPOINT sp
show table status like 'z1'
SET SQL_QUOTE_SHOW_CREATE=1
SET SESSION character_set_results = 'binary'
show create table `z1`
SET SESSION character_set_results = 'utf8'
show fields from `z1`
show fields from `z1`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `z1`
SET SESSION character_set_results = 'binary'
use `zs`
#查看zs数据库编码
select @@collation_database
SHOW TRIGGERS LIKE 'z1'
SET SESSION character_set_results = 'utf8'
ROLLBACK TO SAVEPOINT sp
#29.导出完一个数据库的表和触发器会释放检查点
RELEASE SAVEPOINT sp
#30.完成一个数据库所有表的备份之后，最后备份的数据库中的事件、函数、存储过程
show events
use `zs`
select @@collation_database
SET SESSION character_set_results = 'binary'
SHOW FUNCTION STATUS WHERE Db = 'zs'
SHOW PROCEDURE STATUS WHERE Db = 'zs'
SET SESSION character_set_results = 'utf8'
#31.开始导出python数据库，重复15~30步骤
python
SHOW CREATE DATABASE IF NOT EXISTS `python`
SAVEPOINT sp
show tables
show table status like 'Orders'
SET SQL_QUOTE_SHOW_CREATE=1
SET SESSION character_set_results = 'binary'
show create table `Orders`
SET SESSION character_set_results = 'utf8'
show fields from `Orders`
show fields from `Orders`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `Orders`
SET SESSION character_set_results = 'binary'
use `python`
select @@collation_database
SHOW TRIGGERS LIKE 'Orders'
SET SESSION character_set_results = 'utf8'
ROLLBACK TO SAVEPOINT sp
show table status like 't'
SET SQL_QUOTE_SHOW_CREATE=1
SET SESSION character_set_results = 'binary'
show create table `t`
SET SESSION character_set_results = 'utf8'
show fields from `t`
show fields from `t`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`
SET SESSION character_set_results = 'binary'
use `python`
select @@collation_database
SHOW TRIGGERS LIKE 't'
SET SESSION character_set_results = 'utf8'
ROLLBACK TO SAVEPOINT sp
show table status like 'y'
SET SQL_QUOTE_SHOW_CREATE=1
SET SESSION character_set_results = 'binary'
show create table `y`
SET SESSION character_set_results = 'utf8'
show fields from `y`
show fields from `y`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `y`
SET SESSION character_set_results = 'binary'
use `python`
select @@collation_database
SHOW TRIGGERS LIKE 'y'
SET SESSION character_set_results = 'utf8'
ROLLBACK TO SAVEPOINT sp
show table status like 'z'
SET SQL_QUOTE_SHOW_CREATE=1
SET SESSION character_set_results = 'binary'
show create table `z`
SET SESSION character_set_results = 'utf8'
show fields from `z`
show fields from `z`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `z`
SET SESSION character_set_results = 'binary'
use `python`
select @@collation_database
SHOW TRIGGERS LIKE 'z'
SET SESSION character_set_results = 'utf8'
ROLLBACK TO SAVEPOINT sp
RELEASE SAVEPOINT sp
show events
use `python`
select @@collation_database
SET SESSION character_set_results = 'binary'
SHOW FUNCTION STATUS WHERE Db = 'python'
SHOW PROCEDURE STATUS WHERE Db = 'python'
SET SESSION character_set_results = 'utf8'
#32.备份结束，退出连接
quit
3.16 原理总结
通过以上的日志分析，可以总结下mysqldump备份的主要流程：
一开始设置SQL_MODE为空;
设置TIME_ZONE为+00:00;
查看是否开启了GTID；
执行FLUSH TABLES关闭实例中所有的表；
执行语句FLUSH TABLES WITH READ LOCK获取全局表的读锁，保证表一致性；
设置会话级别事务的隔离级别为REPEATABLE READ，保证事务期间数据的一致性；
执行语句START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */创建一个一致性事务快照；
获取当前状态下的binlog文件及位置信息(如有指定选项 --master-data)；
执行UNLOCK TABLES释放全局表读锁；
开始备份第一个数据库数据，为事务创建一个检查点，备份完一张表之后，备份触发器，然后还原至检查点再接着备份下一张表，直至该数据库所有的表备份完成，释放检查点，备份第一个数据库事件、函数、存储过程，接着备份下一个数据库数据重复操作，直至所有数据库数据备份完成；
当备份完最后一个数据库数据后，退出并中止事务。4.恢复
4.1 mysql命令恢复
mysql -uroot -p123456 < zs_python.sql4.2 source命令恢复
#进入mysql使用source命令
source /backup/zs_python.sql4.3 备份并用gzip压缩
mysqldump -uroot -p123456 -B zs python --single-transaction --master-data=2 --triggers -R -E > | gzip > outputfile.sql.gz从gzip备份恢复：
gunzip < outputfile.sql.gz | mysql -uroot -p1234564.4 备份并用bzip压缩
mysqldump -uroot -p123456 -B zs python --single-transaction --master-data=2 --triggers -R -E | bzip2 > outputfile.sql.bz2从bzip2备份恢复:
bunzip2 < outputfile.sql.bz2 | mysql -uroot -p123456
