【MySQL】关于MySQL的mysqldump备份与还原

一、mysqldump的简介

mysqldump是MySQL原生自带的逻辑备份，单线程工具。

mysqldump 相当于一个MySQL客户端工具，你的服务器在远程，mysqldump在本地，没有任何问题，也就意味着二者可以在不同的主机上。

mysqldump 可实现完全备份和部分备份，但是还原一个库时，这个库得事先存在，就是说在还原前，先得CREATE DATABASE。

适用场景：一次性dump备份的数据量在10G以下

 

对本机执行备份(通过本地socket连接，速度较快)

#  mysqldump -u root -p"MySQL@123"   --single-transaction --routines --triggers --events  test > test.sql
#  mysqldump -u root  -h localhost   -p"MySQL@123"   --single-transaction --routines --triggers --events  test > test.sql

 

对远端服务器执行备份(通过TCP协议连接，速度一般)

# mysqldump -u root  -h xx.xx.xx.xx   -p"MySQL@123"   --single-transaction --routines --triggers --events  test > test.sql

# mysqldump  --help

###############################################
-h 主机

-P端口

-u用户名

-p密码 （注意不能有空格）

--ignore-table   忽略表不到出 举例  ----ignore-table=dataname.table1 --ignore-table=dataname.table2

-d 结构(--no-data:不导出任何数据，只导出数据库表结构)

-t 数据(--no-create-info:只导出数据，而不添加CREATE TABLE 语句)

-n (--no-create-db:只导出数据，而不添加CREATE DATABASE 语句）

-R (--routines:导出存储过程以及自定义函数)

-E (--events:导出事件)

--triggers (默认导出触发器，使用--skip-triggers屏蔽导出)

-B (--databases:导出数据库列表，单个库时可省略）

--tables 表列表（单个表时可省略）

①同时导出结构以及数据时可同时省略-d和-t
②同时 不 导出结构和数据可使用-ntd
③只导出存储过程和函数可使用-R -ntd
④导出所有(结构&数据&存储过程&函数&事件&触发器)使用-R -E(相当于①，省略了-d -t;触发器默认导出)
⑤只导出结构&函数&事件&触发器使用 -R -E -d
#####################################################

 

二、备份与还原

备份单个库

#  mysqldump -u root -p"MySQL@123"   --single-transaction --routines --triggers  --events  test > test.sql 

还原单个库(恢复时如果目标库不存在，需要事先手动创建)

#  mysql -u root -p"MySQL@123"   -e  "create database if not exists test default character set utf8 collate utf8_general_ci;"

#   mysql -u root -p"MySQL@123"  -D  test  <   test.sql

 

备份单个库的某张表（registry库中的role表）

# mysqldump -u root -p"MySQL@123"  --single-transaction --routines   --triggers  --events registry role > registry_role.sql

#   mysql -u root -p"MySQL@123"  -D   registry   <  registry_role.sql

可以看出，指定数据库进行备份的这种方式，它只是备份这个库里的东西，它以为这个库是事先存在的，所以恢复时是不会自动创建库的。

 

备份多个库

#  mysqldump -u root -p"MySQL@123" --single-transaction --routines --triggers  --events   --databases  A  B   > AB.sql

还原多个库(恢复时，无需事先创建库)

#  mysql -u root -p"MySQL@123"   <  AB.sql 

 

MySQL的全库备份

# mysqldump -u root -p"MySQL@123" --single-transaction --routines --triggers  --events --all-databases > all.sql  

默认情况下，来自具有GTID的服务器的部分转储将包括所有事务的GTID，甚至是那些更改了数据库的已抑制部分的事务。

如果您不想恢复GTID，请传递 --set-gtid-purged = OFF， 要进行完整转储，请传递 --all-databases  --triggers --routines --events。

 

导出单个库中的所有表结构

# mysqldump -u root -p"MySQL@123"  --single-transaction --routines   --triggers  --events  --no-data  registry  > registry_frm.sql

导出单个库中的单个表结构

# mysqldump -u root -p"MySQL@123"  --single-transaction --routines   --triggers  --events  --no-data  registry role  > registry.role_frm.sql

导出多个库中的所有表结构

# mysqldump -u root -p"MySQL@123"  --single-transaction --routines   --triggers  --events  --no-data   --databases  A  B   > AB_frm.sql

三、参考

MySQL的全库备份

https://www.jianshu.com/p/bdda49562cf9

MySQL 备份和恢复机制

https://juejin.im/entry/5a0aa2026fb9a045132a369f

基于mysqldump做备份恢复

https://jkzhao.github.io/2018/04/21/%E5%9F%BA%E4%BA%8Emysqldump%E5%81%9A%E5%A4%87%E4%BB%BD%E6%81%A2%E5%A4%8D

MySQL存储引擎对比

https://jkzhao.github.io/2018/04/08/MySQL%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E5%AF%B9%E6%AF%94