23.MySQL的备份与恢复

23.1  备份单个数据库练习多种参数使用

1. mysql 数据库自带了一个很好用的备份命令，就是 mysqldump，它的基本使用如下：
2. 语法：mysqldump –u <用户名> -p <数据库名> 备份的文件名​

范例一：

23.2  查看数据库 oldboy 的内容

mysql> use oldboy；
Database changed
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| test |
+------------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+------+-----------+-------------+
| id | age | name | shouji |
+----+------+-----------+-------------+
| 1 | NULL | oldgirl | NULL |
| 2 | NULL | 老男孩 | NULL |
| 3 | NULL | etiantian | NULL |
| 4 | 24 | zhangxuan | 13511111111 |
| 5 | 22 | huangyan | 13655555555 |
+----+------+-----------+-------------+
5 rows in set (0.05 sec)​

23.3 执行备份的命令

[root@mysql ~]# mysqldump -u root -ppcwangjixuan -S /mysqldata/3306/mysql.sock oldboy >/opt/oldboy_bak.sql​

23.4 查看备份的结果

[root@mysql ~]# grep -vE "#|*|--|^$" /opt/oldboy_bak.sql
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
 `id` int(4) NOT NULL DEFAULT '0',
 `age` tinyint(2) DEFAULT NULL,
 `name` varchar(16) DEFAULT NULL,
 `shouji` char(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `index_name_and_shouji` (`name`(6),`shouji`(8))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,NULL,'oldgirl',NULL),(2,NULL,' 老男孩
',NULL),(3,NULL,'etiantian',NULL),(4,24,'zhangxuan','13511111111'),(5,22,'huangyan','13655555
555');
UNLOCK TABLES;​

23.5 mysqldump 备份时加上-B 参数时的备份，然后比较不加-B 备份的不同

加上-B 备份的操作
[root@mysql opt]# mysqldump -u root -ppcwangjixuan -S /mysqldata/3306/mysql.sock -B
oldboy >/opt/oldboy_B_bak.sql
比较两者备份的不同
[root@mysql opt]# vimdiff oldboy_bak.sql oldboy_B_bak.sql
从下面结果看出，加上-B 后，备份的时候，会有创建数据库并 use 库的过程​

23.6 删除数据库中备份过的库 oldboy，然后将备份的数据重新导入数据库

删除数据库 oldboy
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy_gbk |
| performance_schema |
| wordpress |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database oldboy;
Query OK, 1 row affected (0.44 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy_gbk |
| performance_schema |
| wordpress |
+--------------------+
5 rows in set (0.00 sec)
用不同备份的数据分别导入查看其情况
[root@mysql ~]# mysql -u root -ppcwangjixuan -S /mysqldata/3306/mysql.sock
</opt/oldboy_bak.sql
ERROR 1046 (3D000) at line 22: No database selected 从结果看出，没加-B 参数备份的数据，是不能导进去的，需要重新创建库，并且导入的时候指定库
[root@mysql ~]# mysql -u root -ppcwangjixuan -S /mysqldata/3306/mysql.sock
</opt/oldboy_B_bak.sql 从结果看出，加了-B 参数备份的数据，可以直接导入数据库，查看
其导入结果
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy_gbk |
| performance_schema |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)
mysql> use oldboy
Database changed
mysql> select * from test;
+----+------+-----------+-------------+
| id | age | name | shouji |
+----+------+-----------+-------------+
| 1 | NULL | oldgirl | NULL |
| 2 | NULL | 老男孩 | NULL |
| 3 | NULL | etiantian | NULL |
| 4 | 24 | zhangxuan | 13511111111 |
| 5 | 22 | huangyan | 13655555555 |
+----+------+-----------+-------------+
5 rows in set (0.00 sec)​

23.7 利用 mysqldump 命令对指定的库进行压缩备份

[root@mysql ~]# mysqldump -uroot -ppcwangjixuan -S /mysqldata/3306/mysql.sock -B oldboy|gzip>/opt/oldboy_B_bak.sql.gz
[root@mysql ~]# ls -l /opt/oldboy_B_bak.sql.gz
-rw-r--r--. 1 root root 881 Jun 15 21:04 /opt/oldboy_B_bak.sql.gz​

23.8 利用 mysqldump 命令备份多个库（-B 参数后可以指定多个库）

备份多个库 oldboy 和 oldboy_gbk
[root@mysql ~]# mysqldump -uroot -ppcwangjixuan -S /mysqldata/3306/mysql.sock -B
oldboy oldboy_gbk>/opt/oldboy_and_oldboy_gbk.bak.sql
登录数据库删除 oldboy 和 oldboy_gbk
mysql> drop database oldboy;
Query OK, 1 row affected (0.00 sec)
mysql> drop database oldboy_gbk;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wordpress |
+--------------------+
4 rows in set (0.00 sec)
将备份的数据直接导入数据库查看其结果（从下面结果看出，数据恢复过来）
[root@mysql ~]# mysql -u root -ppcwangjixuan -S /mysqldata/3306/mysql.sock
</opt/oldboy_and_oldboy_gbk.bak.sql
[root@mysql ~]# mysql -u root -ppcwangjixuan -S /mysqldata/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 38
Server version: 5.5.27-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy_gbk |
| performance_schema |
| wordpress |
+--------------------+
6 rows in set (0.00 sec)​

23.9 分库备份（对 mysql、oldboy、oldboy_gbk、wordpress 库进行备份）

利用命令行分库备份
[root@mysql ~]# mysql -uroot -ppcwangjixuan -S /mysqldata/3306/mysql.sock -e "show
databases"|egrep -v "Data|inf|per"|sed -r 's#(.*)#mysqldump -uroot -ppcwangjixuan -S
/mysqldata/3306/mysql.sock -B &#g'|bash
 
[root@db01 data]# mysql -uroot -p123456  -e "show databases;"|egrep -v "Data|sche|mysql|inf"|sed -r 's#^(.*)#mysqldump -uroot -p123456 -B 1 >/data/db/1.sql.gz#g'|bash​

23.10 对一个库的多个表备份

利用命令对单个库的多个表备份

[root@mysql ~]# mysql -u root -ppcwangjixuan -S /mysqldata/3306/mysql.sock -e "use
oldboy;show tables"
+------------------+
| Tables_in_oldboy |
+------------------+
| pengchun |
| test |
| zhangxuan |
+------------------+
备份命令
[root@mysql ~]# mysql -u root -ppcwangjixuan -S /mysqldata/3306/mysql.sock -e "use
oldboy;show tables"|sed '1'd|sed -r 's#(.*)#mysqldump -u root -ppcwangjixuan -S
/mysqldata/3306/mysql.sock oldboy & >/opt/&.sql#g'|bash​

23.11  备份多个表

语法：mysqldump –u 用户名 –p 数据库名 表名 1 表名 2 >备份的文件名
操作结果：
mysql> use oldboy;
Database changed
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| pengchun |
| test |
| zhangxuan |
+------------------+
3 rows in set (0.09 sec)
[root@mysql ~]# mysqldump -u root -ppcwangjixuan -S /mysqldata/3306/mysql.sock oldboy
pengchun test zhangxuan >/opt/oldboy.$(date +%F).sql​

23.12  备份单个表

语法：mysqldump –u 用户名 –p 数据库名 表名 >备份的文件名
执行结果：
[root@mysql ~]#mysqldump –u root –ppcwangjixuan oldboy student >opt/table.sql
提示：不能加-B 参数了，因为库 oldboy 后面就是 oldboy 表了，指定-B 就表示后面的都是库​

23.13  关于 mysqldump 的参数说明

1、-B 备份多个库（添加 create 和 use 库的语句）
2、-d 只备份库表结构
3、-t 只备份数据（sql 语句形式）
4、-T 分离库表和数据不同的文件，数据是文本，非 SQL 语句
5、-A 备份数据库中所有的数据
6、--compact 去掉注释，适合调试输出，生产不使用
7、-F 刷新 binglog 日志，生成新文件，将来增量恢复从这个文件开始
8、--master-data 增加 binlog 日志文件名及对应的位置点（即 CHANGE MASTER 语句）
--master-data=1 不注释，--master-data=2 注释
9、-x –lock-all-tables 锁表，当某一时刻备份数据时需要加入此参数，以确定备份数据时是
从某一时刻开始的
10、-l –lock-tables 对读锁表
11、--single-transaction 适合 innodb 事务数据库备份
innodb 表在备份时，通常启动选项—single-transaction 来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为 REPEATABLE READ,以确保本次会话（dump）时，不会看到其他会话已经提交了的数据
12、-q 不做缓冲查询，直接导入输出​

23.14 刷新 binglog 的参数

binglog 是什么，记录数据库更新的 SQL 语句
mysqldump 用于定时对某一时刻的数据的全备，例如：00 点进行备份 bak.sql
增量备份：当有数据写入到数据库时，还会同时把更新的 SQL 语句写入到对应的文件里，这个文件就叫做 binglog 文件
10 点丢失数据需要恢复数据
1、00 点时刻备份的 bak.sql 数据还原到数据库，这个时候数据恢复到了 00 点
2、00 点-10:00 数据，就要从 binglog 里恢复
binglog 文件生效需要一个参数：log-bin
[root@mysql ~]# grep log-bin /mysqldata/3306/my.cnf
log-bin = /mysqldata/3306/mysql-bin
binglog 文件：
[root@mysql ~]# ll /mysqldata/3306/mysql-bin.*
-rw-rw----. 1 mysql mysql 1434 Jun 13 07:06 /mysqldata/3306/mysql-bin.000001
-rw-rw----. 1 mysql mysql 622 Jun 13 07:14 /mysqldata/3306/mysql-bin.000002
-rw-rw----. 1 mysql mysql 434 Jun 13 18:37 /mysqldata/3306/mysql-bin.000003
-rw-rw----. 1 mysql mysql 126 Jun 13 18:43 /mysqldata/3306/mysql-bin.000004
-rw-rw----. 1 mysql mysql 2194 Jun 14 03:39 /mysqldata/3306/mysql-bin.000005
-rw-rw----. 1 mysql mysql 483 Jun 14 03:48 /mysqldata/3306/mysql-bin.000006
-rw-rw----. 1 mysql mysql 529082 Jun 14 21:38 /mysqldata/3306/mysql-bin.000007
-rw-rw----. 1 mysql mysql 150 Jun 14 21:39 /mysqldata/3306/mysql-bin.000008
-rw-rw----. 1 mysql mysql 126 Jun 14 21:39 /mysqldata/3306/mysql-bin.000009
-rw-rw----. 1 mysql mysql 150 Jun 14 21:40 /mysqldata/3306/mysql-bin.000010
-rw-rw----. 1 mysql mysql 126 Jun 14 21:41 /mysqldata/3306/mysql-bin.000011
-rw-rw----. 1 mysql mysql 366 Jun 14 21:43 /mysqldata/3306/mysql-bin.000012
-rw-rw----. 1 mysql mysql 1032064 Jun 14 23:18 /mysqldata/3306/mysql-bin.000013
-rw-rw----. 1 mysql mysql 444 Jun 14 23:20 /mysqldata/3306/mysql-bin.000014
-rw-rw----. 1 mysql mysql 524680 Jun 15 21:41 /mysqldata/3306/mysql-bin.000015
-rw-rw----. 1 mysql mysql 495 Jun 14 23:20 /mysqldata/3306/mysql-bin.index
binglog 日志切割：确定全备和增量的临界点
-F 刷新 binglog 日志，生成新文件，将来增量恢复从这个文件开始
--master-data 在备份语句里添加 CHANGE MASTER 语句及 binglog 文件及位置点信息值为 1，为可执行的 CHANGE MASTER 语句值为 2，注释的—CHANGE MASTER 语句--master-data 除了增量恢复确定临界点外，做主从复制时作用更大

23.15 生产场景不同引擎 mysqldump 备份命令

myisam 引擎企业生产备份命令（适合所有引擎或混合引擎）

mysqldump –uroot –ppcwangjixuan –A –B –F –R –master-data=2 –x –events|gzip >/opt/all.sql.gz
提示-F 也可以不用，与—master-data 有些重复
innodb 引擎企业生产备份命令：推荐使用的
mysqldump –uroot –ppcwangjixuan –A –B –F –R --master-data=2 –events –singletransaction|gzip>/opt/all.sql.gz
提示:-F 也可以不用，与—master-data 有些重复
其中—master-data 作用：
使用—master-data=2 进行备份文件会增加如下内容，适合普通备份增量恢复
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000024',
MASTER_LOG_POS=107;
额外补充：
50G 以内的数据
1、mysqldump 逻辑备份，缺点：效率不是特别高。优点：简单、方便、可靠、迁移，适用
与数据量不是特别大的场景
超过 50G 的数据
1、 xtrabackup 物理备份工具：全量和增量物理备份方案：
2、 从库停止 SQL 线程，打包，cp
什么时候会使用 mysqldump 的数据？
1、 恢复数据到测试库
2、 人为通过 SQL 将数据删除的时候
3、 主从复制