MySQL的逻辑备份工具---mysqldump

MySQL的逻辑备份工具---mysqldump

1.mysqldump

mysqldump时MySQL提供的一个非常有用的数据库备份工具。

1.1 备份一个数据库

mysqldump命令执行时，可以将数据库被分成一个 文本文件，该文件中实际上包含多个 CREATE 和 INSERT 语句，使用这些语句可以重新创建表和插入数据。

~ 查出需要备份的表结构，在文本文件中生成一个CREATE语句

~ 将表中的所有记录转化成一条INSERT语句。

基本语法：

mysqldump –u user –h host –p 数据库的名称 [ 表名 1 [ 表名 1...]] > 备份文件名称 .sql

举例：

使用root用户备份db_game数据库：

mysqldump -uroot -p db_game > db_game.sql

若想用mysqldump备份整个实例，可以使用 --all-databases 或 -A 参数：

mysqldump -uroot -pxxxxxx -- all - databases > all_database .sql
mysqldump -uroot -pxxxxxx -A > all_database .sql 

1.2 备份部分数据库

使用 -- databases 或 - B 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定 databases 参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。

--语法如下：
mysqldump –u user –h host –p -- databases [ 数据库的名称 1 [ 数据库的名称 1...]] > 备份文件名称 .sql
mysqldump -uroot -p -- databases db_game db_game12 >two_database .sql
mysqldump -uroot -p -B db_game db_game12 > two_database .sql

1.3 备份部分表

比如，在表变更前做个备份。

--语法如下：
mysqldump –u user –h host –p 数据库的名称 [ 表名 1 [ 表名 1...]] > 备份文件名称 .sql

--举例：备份 db_game 数据库下的 book 表
mysqldump -uroot -p db_game book> book .sql

1.5 备份单表的部分数据

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 -- where 选项了。 where 后面附带需要满足的条件。

--举例：备份 student 表中 id 小于 10 的数据：
mysqldump -uroot -p db_game student -- where = "id < 10 " > student_part_id10_low_bak .sql



--内容如下所示， insert 语句只有 id 小于 10 的部分
LOCK TABLES `student` WRITE ;
/*!40000 ALTER TABLE `student` DISABLE KEYS */ ;
INSERT INTO `student` VALUES ( 1 , 100002 , 'JugxTY' , 157 , 280 ),( 2 , 100003 , 'QyUcCJ' , 251 , 277 ),
( 3 , 100004 , 'lATUPp' , 80 , 404 ),( 4 , 100005 , 'BmFsXI' , 240 , 171 ),( 5 , 100006 , 'mkpSwJ' , 388 , 476 ),
( 6 , 100007 , 'ujMgwN' , 259 , 124 ),( 7 , 100008 , 'HBJTqX' , 429 , 168 ),( 8 , 100009 , 'dvQSQA' , 61 , 504 ),
( 9 , 100010 , 'HljpVJ' , 234 , 185 );

1.6 排除某些表的备份

如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项 --ignore-table 可以完成这个功能。

mysqldump -uroot -p db_game -- ignore - table =db_game .student > no_stu_bak .sql
通过如下指定判定文件中没有 student 
表结构：
grep "student" no_stu_bak.sql

1.7 只备份结构或只备份数据

只备份结构的话可以使用 -- no - data 简写为 - d 选项；

只备份数据可以使用 -- no - create - info 简写为 -t 选项。

只备份结构
mysqldump -uroot -p 123 --no-data > all_data_bak .sql
只备份数据
mysqldump -uroot -p 123 --no-create-info > all_create_info_bak .sql

1.8 备份中包含存储过程、函数、事件

mysqldump 备份默认是不包含存储过程，自定义函数及事件的。可以使用 -- routines 或 - R 选项来备份存储过程及函数，使用 -- events 或 - E 参数来备份事件。

举例：备份整个 db_game 库，包含存储过程及事件:

--使用下面的 SQL 可以查看当前库有哪些存储过程或者函数
SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA FROM
--下面备份 db_game 库的数据，函数以及存储过程。
mysqldump -uroot -p -R -E --databases db_game > fun_db_game_bak .sql

1.9 mysqldump常用选项

mysqldump其他常用选项如下：

--add-drop-database ：在每个 CREATE DATABASE 语句前添加 DROP DATABASE 语句。
--add-drop-tables ：在每个 CREATE TABLE 语句前添加 DROP TABLE 语句。
--add-locking ：用 LOCK TABLES 和 UNLOCK TABLES 语句引用每个表转储。重载转储文件时插入得更快。
--all-database , -A ：转储所有数据库中的所有表。与使用 -- database 选项相同，在命令行中命名所有数据库。
--comment [= 0 | 1 ] ：如果设置为 0 ，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。 
--skip-comments 与 --comments= 0 的结果相同。
默认值为 1 ，即包括额外信息。

--compact ：产生少量输出。该选项禁用注释并启用 --skip-add-drop-tables 、 --no-set-names 、--skip- disable-keys 和--skip-add-locking 选项。
--compatible=name ：产生与其他数据库系统或旧的 MySQL 服务器更兼容的输出，值可以为 ansi 、 MySQL323 、
MySQL40 、 postgresql 、 oracle 、 mssql 、 db2 、 maxdb 、 no_key_options 、 no_table_options 或者no_field_options 。
--complete_insert, -c ：使用包括列名的完整的 INSERT 语句。
--debug[=debug_options], - #[debug_options] ：写调试日志。 -- delete ， -D ：导入文本文件前清空表。
-- default - character - set = charset ：使用 charsets 默认字符集。如果没有指定，就使用 utf8 。
-- delete -- master - logs ：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用--master-data 。
-- extended - insert ， -e ：使用包括几个 VALUES 列表的多行 INSERT 语法。这样使得转储文件更小，重载文件时可以加速插入。
-- flush - logs ， -F ：开始转储前刷新 MySQL 服务器日志文件。该选项要求 RELOAD 权限。
-- force ， -f ：在表转储过程中，即使出现 SQL 错误也继续。
-- lock - all - tables ， -x ：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关闭 --single- transaction 和 -- lock - tables 。
-- lock - tables ， -l ：开始转储前锁定所有表。用 READ LOCAL 锁定表以允许并行插入 MyISAM 表。对于事务表（例如 InnoDB 和 BDB ）， --single- transaction 是一个更好的选项，因为它根本不需要锁定表。
-- no - create -db ， -n ：该选项禁用 CREATE DATABASE /*!32312 IF NOT EXIST*/ db_name 语句，如果给出 -database 或 -- all - database 选项，就包含到输出中。
-- no - create -info ， -t ：只导出数据，而不添加 CREATE TABLE 语句。
-- no - data ， -d ：不写表的任何行信息，只转储表的结构。
--opt ：该选项是速记，它可以快速进行转储操作并产生一个能很快装入 MySQL 服务器的转储文件。该选项默认开启，但可以用 --skip-opt 禁用。
-- password [= password ] ， -p[ password ] ：当连接服务器时使用的密码。
-port=port_num ， -P port_num ：用于连接的 TCP/IP 端口号。
--protocol={TCP|SOCKET|PIPE|MEMORY} ：使用的连接协议。
-- replace ， -r –replace 和 -- ignore ：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定 --
replace ，新行替换有相同的唯一键值的已有行；如果指定 -- ignore ，复制已有的唯一键值的输入行被跳过。如果不
指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
--silent ， -s ：沉默模式。只有出现错误时才输出。
--socket=path ， -S path ：当连接 localhost 时使用的套接字文件（为默认主机）。
-- user =user_name ， -u user_name ：当连接服务器时 MySQL 使用的用户名。
--verbose ， -v ：冗长模式，打印出程序操作的详细信息。
--xml ， -X ：产生 XML 输出。

2.MySQL命令恢复数据

2.1 单库备份中恢复单库

使用 root 用户，将之前练习中备份的 db_game.sql 文件中的备份导入数据库中，命令如下：

如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示

mysql -uroot -p < db_game .sql

否则需要指定数据库名称，如下所示

mysql -uroot -p db_game4< db_game .sql

2.2 全量备份恢复

如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：

mysql –u root –p < all.sql
mysql -uroot -pxxxxxx < all.sql

执行完后， MySQL 数据库中就已经恢复了 all.sql 文件中的所有数据库。

2.3 从全量备份中恢复单库

可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。

--举例：
sed -n '/^-- Current Database: `db_game`/,/^-- Current Database: `/p' all_database .sql

db_game .sql分离完成后我们再导入 db_game.sql 即可恢复单个库

2.4 从单库备份中恢复单表

这个需求还是比较常见的。

比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。

举例：我们有 db_game 整库的备份，但是由于 class 表误操作，需要单独恢复出这张表。

cat db_game .sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' >class_structure .sql
cat db_game .sql | grep -- ignore - case 'insert into `class`' > class_data .sql

用 shell 语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复

use db_game;
mysql> source class_structure .sql ;
Query OK, 0 rows affected, 1 warning ( 0.00 sec)
mysql> source class_data .sql ;
Query OK, 1 row affected ( 0.01 sec)