MySQL学习【第13篇】MySQL的备份和恢复

一、MySQL备份基础

1、备份的原因？

运维工作的核心简单概括就两件事：
1）第一个是保护公司的数据；                                       #备份就是为了恢复。
2）第二个是让网站能7*24小时提供服务(用户体验好)；#尽量减少数据的丢失（公司的损失）

2、备份的类型

冷备份：停服务，停机备份

这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

温备份：不停服务，锁表，其他用户无法写入数据

这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

热备份：不停服务，不锁表，其他用户可以继续写入数据

这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。

3、备份的方式

1）逻辑备份：基于SQL语句的备份： 

1. binlog

2. into outfile
mysql> select * from world.city into outfile '/tmp/world_city.data';

3. mysqldump

4. replication

2）物理备份：基于数据文件的备份

Xtrabackup（percona公司）

4、备份的策略

1）全量备份 full

2）增量备份 increamental

5、备份的工具

mysqldump（逻辑）：mysql原生自带很好用的逻辑备份工具

mysqlbinlog（逻辑）：实现binlog备份的原生态命令

xtrabackup（物理）：precona公司开发的性能很高的物理备份工具

二、mysqldump的备份和恢复

1、常用参数

1）连接服务端参数(基本参数)：-u -p -h -P -S

[root@db02 ~]# mysql -uroot -p123 -e "show databases;"
-u：指定用户
-p：指定密码
-h：指定主机
-P：指定端口
-S：指定sock
-e：指定SQL(在命令行)
--protocol=name：指定连接方式(无意义，了解即可)

2）-A,，--all-databases：全库备份

[root@db01 ~]# mysqldump -uroot -p123 -A > /backup/full.sql

3）不加参数：单库、单表多表备份

[root@db01 ~]# mysqldump -uroot -poldboy123 db1 > /backup/db1.sql
[root@mysql-db01 backup]# mysqldump -uroot -poldboy123 world city > /backup/city.sql

4）-B：指定库备份

[root@db01 ~]# mysqldump -uroot -p123 -B db1 > /backup/db1.sql
[root@db01 ~]# mysqldump -uroot -p123 -B db1 db2 > /backup/db1_db2.sql

5）-F：flush logs在备份时自动刷新binlog（不怎么常用）

[root@db01 backup]# mysqldump -uroot -p123 -A -R –triggers -F > /backup/full_2.sql

6）--master-data=2：备份时加入change master语句0没有1不注释2注释

[root@db01 backup]# mysqldump -uroot -p123 --master-data=2 >/backup/full.sql

7）-d：仅表结构

8）-t：仅数据

2、备份额外扩展项

[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers > /backup/full_2.s/3、ql

-R, --routines：备份存储过程和函数数据
--triggers：备份触发器数据

3、特殊参数

1）-x：锁表备份（myisam温备份）
2）--single-transaction：快照备份

[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction>/backup/full.sql

 4、mysqldump的恢复

#先不记录二进制日志
mysql> set sql_log_bin=0;
#库内恢复操作
mysql> source /backup/full.sql
#库外恢复操作
[root@db01 ~]# mysql -uroot -p123 < /backup/full.sql

 注意事项：

• mysqldump在备份和恢复时都需要MySQL实例启动为前提
• 一般数据量级100G以内，大约15-30分钟可以恢复（PB、EB就需要考虑别的方式）
• mysqldump是以覆盖的形式恢复数据的