「MySQL」- 使用 mysqldump 进行数据备份与恢复（迁移） @20210312

第一步、在旧主机中导出备份

备份单个数据库：

#!/bin/sh

mysqldump test > dump.sql
# 该备份文件不包含 CRETAE DATABASE 与 USE 语句，在恢复时需要选择数据库。

mysqldump --databases "<dbname>" > "<dbname>".sql
# 该备份文件包含 CRETAE DATABASE 与 USE 语句，可直接用于恢复。

备份多个数据库：

#!/bin/sh

# 备份 db1 数据库中的 db2、db3 这两张表
mysqldump db1 db2 db3 > dump.sql

# 备份 db1、db2、db3 数据库
mysqldump --databases db1 db2 db3 > dump.sql

备份所有数据库：

#!/bin/sh

# 备份所有数据库
mysqldump --all-databases > dump.sql

# 备份所有数据库，并添加 DROP DATABASE 语句
mysqldump --all-databases --add-drop-database > dump.sql

备份输出使用 gzip 压缩：

#!/bin/sh

mysqldump | gzip > outputfile.sql.gz

附加说明：
1）这里没有对函数、存储过程、视图等内容进行备份；
2）在使用 mysqldump 命令时，注意 --databases，--table 等选项的配合使用；
3）在 MySQL 8.0 前，使用 --all-databases 即可；在 MySQL 8.0 后，还要指定 --routines 和 --events 选项；

第二步、传输数据到新的主机

#!/bin/sh

scp [database name].sql [username]@[servername]:path/to/database/

第三步、在新数据库主机上的操作

#3 导入数据

#!/bin/sh

mysql -uroot -p < /path/to/newdatabase.sql
# 如果备份语句里没有 CRAETE 和 USE 语句，需要选择数据库

# 如果导出文件为 gzip 格式：
zcat /path/to/file.sql.gz | mysql -u ‘root‘ -p your_database

#4 用户迁移

#!/bin/sh

# 在新的数据库主机上创建用户。（略过……）

简化操作

#!/bin/sh

mysqldump -u root -p --databases [database name] | ssh ‘user‘@‘hostname‘ mysql -u root -p

附加说明

#1 不建议全库备份

不要全库直接备份，粒度太大，恢复困难。

如果要恢复指定的库或表：

		方法一、如果要恢复指定的库或表，那里需要先把指定的数据抽离出来：
		[[https://www.saotn.org/restore-single-mysql-table-full-mysqldump-backup-file/|Restore single MySQL table from a full mysqldump backup file]]
		[[http://www.darrenmothersele.com/blog/2012/01/10/restore-single-db-from-complete-mysql-dump/|Restoring a single database from a complete MySQL database dump]]

		方法二、当到恢复指定库或者表时，创建一个临时用户，该用户具有被恢复库或表的访问权限，然后跳过错误，导入数据。
		[[https://stackoverflow.com/questions/2342356/import-single-database-from-all-databases-dump|Import single database from --all-databases dump]]

全库备份一般都是偷懒的做法。

#2 除数据外的备份

上面的示例没有对函数、存储过程、视图等内容进行备份。

如果需要对上述对象进行备份，参考 mysqldump 手册。

#3 /*!xxxxxx statement */

What does /*!xxxxxx statement */ generated by mysqldump mean?
MySQL 5.6 Reference Manual/Language Structure/Comment Syntax

在导出文件中会看到形如/*!40000 ALTER TABLE abc DISABLE KEYS */;的语句，在其他数据库中的这些内容将被视为注释，而在MySQL中将被执行。

前面的数字是可选的，代表MySQL版本号。如果高于当前MySQL版本号，则该语句将被忽略。

#4 导出特定行

Get Insert Statement for existing row in MySQL

如果想要导出特定行，可以使用：

#!/bin/sh

mysqldump -t -u MyUserName -pMyPassword MyDatabase MyTable --where="ID = 10"

相关链接

4.5.4 mysqldump — A Database Backup Program

相关文章

「XtraBackup」- 备份数据的“预备”（Prepare）操作
「XtraBackup」- 备份实例及实例恢复
「MySQL」- 恢复数据到特定时间点

参考文献

How To Migrate a MySQL Database Between Two Servers
How do I load a sql.gz file to my database? (importing)
Compressing Mysqldump Output
1.4.1 Dumping Data in SQL Format with mysqldump
1.4.2 Reloading SQL-Format Backups
How can I suppress column header output for a single SQL statement?
MySQL command to show list of databases on server
MySQL 8.0 Reference Manual/mysqldump — A Database Backup Program