MySQL数据库(五)mysqldump备份与恢复

3.2 mysql备份与恢复

3.2.1 备份的概念与分类

1、概念

为防止文件、数据丢失或损坏等可能出现的意外情况，将电子计算机存储设备中的数据复制到磁带等大容量存储设备中,从而在原文中独立出来单独贮存的程序或文件副本; 如果系统的硬件或存储媒体发生故障，“备份”工具可以帮助您保护数据免受意外的损失。

mysql数据备份其实就是通过SQL语句的形式将数据DUMP出来，以文件的形式保存，而且导出的文件还是可编辑的，这和Oracle数据库的rman备份还是很不一样的，mysql更像是一种逻辑备份从库中抽取SQL语句，这就包括建库，连库，建表，插入等就像是将我们之前的操作再通过SQL语句重做一次。

2、分类

一般的备份可分为：

（1）系统备份：指的是用户操作系统因磁盘损伤或损坏，计算机病毒或人为误删除等原因造成的系统文件丢失，从而造成计算机操作系统不能正常引导，因此使用系统备份，将操作系统事先贮存起来，用于故障后的后备支援。

（2）数据备份：指的是用户将数据包括文件，数据库，应用程序等贮存起来，用于数据恢复时使用。

备份更专业地可分为：

i. 全量备份：完全备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝

ii. 增量备份：增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件

iii. 差异备份：差异备份是指在一次全备份后到进行差异备份的这段时间内，对那些增加或者修改文件的备份

3.2.2 备份工具：mysqldump、mydumper、xtrabackup

3.2.2.1 使用mysqldump工具备份与恢复

1.命令介绍：

mysqldump是mysql提供的一个基于命令行的mysql数据备份工具，提供了丰富的参数选择，用于各种需求形式的备份，如单库备份，多库备份，单表与多表备份，全库备份，备份表结构，备份表数据等。

2.备份还原语法格式：

以下为备份还原，lol数据库和hero表为演练。

（1）、单库备份及还原

备份
mysqldump -uroot -p123456 lol >/opt/backup/lol.sql
注意：此操作只备份其中的表（包括创建表的语句和数据）。
?
还原
mysql -uroot -p123456 -e 'create database lol;'
mysql -uroot -p123456 lol </opt/backup/lol.sql

（2）、多库备份及还原

备份
mysqldump -uroot -p123456 -B 库1 库2 库3 >/opt/backup/mysql_bak_db.sql
?
还原
mysql -uroot -p123456 </opt/backup/mysql_bak_db.sql

注意：多个库之间用空格分隔

（3）、单表备份及还原

备份
mysqldump -uroot -p123456 lol hero>/opt/backup/hero.sql
?
还原
mysql -uroot -p123456 lol </opt/backup/hero.sql

（4）、多表备份及还原

备份
mysqldump -uroot -p123456 库名 表1 表2>/opt/backup/mysql_bak_db.sql
?
还原
mysql -uroot -p123456 库名 </opt/backup/mysql_bak_db.sql

（5）、全库备份

mysqldump -uroot -p123456 -A >/opt/backup/mysql_bak_db.sql
或者
mysqldump -uroot –p123456 --all-databases >/opt/backup/mysql_bak_db.sql

3.常用参数解析：

（1）、-B等同于--databases

如果想一次备份多个库需要添加B参数，B参数会在备份数据中添加create database和use语句

（2）、-F 在备份之前会先刷新日志，可以看到二进制文件前滚产生新的二进制文件。 （3）、--master-data 有二个值1或者2，等于1会在备份数据中增加如下语句：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000040',MASTER_LOG_POS=4543;

等于2会在备份数据中增加如下语句：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000040',MASTER_LOG_POS=4543;

唯一区别就是有没有被“--”注释掉，如果备份的数据用于slave，等于1即可，此时从库就知道应该从哪个地方开始读二进制日志，如果仅用于备份标识当前二进制是哪一个和位置点等于2合适。

（4）、-x

锁表，备份的时候锁表来保证数据一致性。

（5）、-d

只备份表结构不备份数据。

（6）、-A等同于--all-databases

备份所有数据库

（7）、-X等同于--xml

导出为xml文件

（8）、--single-transaction Innodb引擎保证数据一致性的参数，使用此参数后会话的安全隔离级别会被置为repeatble-read，此时其它会话提交的数据是不可视的，从而保证数据的一致性。 更多参数请参考help

4.还原备份

方法一：

#备份lol数据库
[root@ c6s02 ~]# mysqldump -uroot -p123456 -B lol >lol.sql
?
?
#先删除lol数据库
[root@ c6s02 ~]# mysql -uroot -p123456
?
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lol                |
| mysql              |
| performance_schema |
| test               |
| wg                 |
+--------------------+
6 rows in set (0.00 sec)
?
mysql> drop database lol;
Query OK, 3 rows affected (0.03 sec)
?
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| wg                 |
+--------------------+
5 rows in set (0.00 sec)
?
mysql> \q
?
#测试恢复并查看
[root@ c6s02 ~]# mysql -uroot -p123456 <lol.sql
Warning: Using a password on the command line interface can be insecure.
?
[root@ c6s02 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lol                |
| mysql              |
| performance_schema |
| test               |
| wg                 |
+--------------------+
6 rows in set (0.00 sec)
?
mysql> select * from lol.hero;
+----+--------+--------+-----------+--------+
| id | 角色   | 职业   | 攻击力    | 血量   |
+----+--------+--------+-----------+--------+
|  1 | 蛮王   | 战士   |       200 | NULL   |
|  2 | 狗头   | 战士   |       100 | NULL   |
|  3 | 剑圣   | 战士   |       300 | NULL   |
+----+--------+--------+-----------+--------+
3 rows in set (0.00 sec)

方法二：source方法

[root@ c6s02 ~]# mysql -uroot -p123456
mysql> source /root/lol.sql