「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://*.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


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

上一篇:MySQL长时间不登录密码过期解决办法


下一篇:mysql统计各个科目的分数、总分、平均分