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 > 脚本名.sql
3.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-Kdisable 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-pluginEnable/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-lengthTCP/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-PMySQL连接端口--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-pathRSA公钥目录--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.sql
3.4 备份全库(仅结构)
mysqldump -uroot -p123456 -A -d > /backup/all_metadata.sql
或者
mysqldump -uroot -p123456 --all-databases --no-data > /backup/all_metadata.sql
3.5 备份全库(仅数据)
mysqldump -uroot -p123456 --all-databases --no-create-info > /backup/all_data.sql
或者
mysqldump -uroot -p123456 -A -t > /backup/all_data.sql
3.6 备份单个数据库(结构和数据)
mysqldump -uroot -p123456 zs > /backup/zs.sql
3.7 备份单个数据库(仅结构)
mysqldump -uroot -p123456 zs -d > /backup/zs_metadata.sql
或者
mysqldump -uroot -p123456 zs --no-data > /backup/zs_metadata.sql
3.8 备份单个数据库(仅数据)
mysqldump -uroot -p123456 zs -t > /backup/zs_data.sql
或者
mysqldump -uroot -p123456 zs --no-create-info > /backup/zs_data.sql
3.9 备份多个指定数据库(多个数据库以空格间隔)
mysqldump -uroot -p123456 --databases db1 db2 > /backup/somedbs.sql
或者
mysqldump -uroot -p123456 -B db1 db2 > /backup/somedbs.sql
3.10 备份多张表
mysqldump -uroot -p123456 -B zs --tables aa z1 > /backup/aa_z1.sql
或者
mysqldump -uroot -p123456 zs aa z1 > /backup/aa_z1.sql
3.11 排除某些表
导出DB中其余表结构和数据
mysqldump -uroot -p123456 zs --ignore-table=zs.aa --ignore-table=zs.t1 > /backup/ignoe.sql
3.12 指定where条件
导出表的部分数据(多张表都需有where条件后的字段)
mysqldump -uroot -p123456 zs z1 t1 -w 'a<=4' > /backup/where.sql
3.13 压缩备份
mysqldump -uroot -p123456 -B zs python --single-transaction --master-data=2 --triggers | gzip > /backup/zs_python.sql.tar.gz
3.14 压缩备份至远程服务器
mysqldump -uroot -p123456 -B zs python --single-transaction --master-data=2 --triggers -E -R | gzip | ssh [email protected] 'cat > /tmp/zs_python.sql.tar.gz'
3.13 一致性导出
导出的时候为了一致性，一般加上参数--single-transaction，可以在配置文件中加上该参数
[mysqldump]
single-transaction
3.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.sql
4.2 source命令恢复
#进入mysql使用source命令
source /backup/zs_python.sql
4.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 -p123456
4.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
版权归原作者 鱼跃龙门Smile 所有， 如有侵权，请联系我们删除。