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

  CREATE BY JENKINSBOT
原文链接:「MySQL」- 使用 mysqldump 进行数据备份与恢复(迁移)
文章分类:「MySQL_and_MariaDB:8.Backup,_Recovery_and_Migration:1.Migration_with_mysqldump」
文章标识:「a71282a1」

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

备份单个数据库:

#!/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 不建议全库备份

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

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

方法一、如果要恢复指定的库或表,那里需要先把指定的数据抽离出来:


Restore single MySQL table from a full mysqldump backup file


Restoring a single database from a complete MySQL database 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

参考文献




Backlinks: 00.INDEX