mysql之备份和恢复

（一）备份

1、备份的种类

（1）完全备份：将整个数据库完整的进行备份

（2）增量备份：在完全备份的基础上，对后续新增的内容进行备份

2、备份的需求

（1）在生产环境中，数据的安全至关重要，任何数据的丢失都可能产生严重的后果

（2）数据丢失的原因：程序操作、运算错误、磁盘故障、不可预期的事件、认为操作等等

3、备份的方法

（1）冷备份：关机备份，停止mysql服务，然后进行备份

（2）热备份：开机备份，无需关闭mysql服务，然后进行备份

（3）物理备份：对数据库系统的物理文件（数据文件、日志文件）进行备份

（4）逻辑备份：对数据库的逻辑组件进行备份（表结构），以sql语句的形式，把库、表结构、表数据进行备份保存（直接在数据库系统中删除所有文件，逻辑备份无法恢复）

（二）物理备份

1、物理备份：采用完全备份，对整个数据库进行完整的打包备份

2、优点：操作简单（打包、解包）

3、缺点：数据库文件占用量很大，占用空间大，备份和恢复的时间很长，而且需要暂停数据库

4、物理冷备份（关机备份）

打包备份最好关闭服务，避免有新的数据进入、被覆盖，也可能导致恢复失败

（1）创建两个数据库、建表、写入数据

5、远程复制库

（三）热备份中的逻辑备份

1、mysql自带的工具：mysqldump（逻辑恢复数据库）

（1）mysqldump：操作方便简单，但是只能基于逻辑上的表结构和表数据恢复，物理删除之后，再用逻辑恢复会报错；也可以作为数据迁移

（2）优点：较物理备份，相对来说占的空间要小得多

（3）缺点：占用空间大

2、命令

备份单个库：mysqldump -u root -p123 --databases test > /opt/test.sql

备份多个库：mysqldump -u root -p123 --databases test test1> /opt/test.sql

备份所有库：mysqldump -u root -p123 --all-databases > /opt/test.sql

3、实验

（1）物理删除数据库，会报错

（2）对多个库一次性备份

（3）备份所有的库

4、只恢复数据表

（1）单个表

（2）多个表

5、mysql的命令

（1）mysql连接执行之后即退出

-e：指定连接mysql之后，执行完命令之后，自动退出

（四）增量备份

1、mysqldump也支持增量备份

1. 增量备份：没有重复数据，备份量小，时间短
2. mysqldump增量备份恢复表数据期间，表会锁定
3. 缺点：备份时锁表，必然会影响业务（超过10G大小，耗时会比较长，导致服务不可用）
4. mysql提供的二进制日志间接的视线增量备份

2、mysql二进制日志记录格式有三种

（1）STATEMENT：基于sql语句

特点：记录修改的sql语句，高并发情况下，记录sql语句时候的顺序可能会出错，恢复数据时可能会导致丢失和误差，效率比较高

（2）ROW：基于行

特点：精准记录每一行的数据，准确率高，但是恢复时的效率低

（3）MIXED：混合模式，既可以根据sql语句，也可以根据行

特点：在正常情况下使用STATEMENT，一旦发生高并发，会智能自动切换ROW行

3、二进制文件怎么来（修改配置文件）

4、位置恢复（基于位置点进行恢复）：

（1）从某一个点开始，恢复到最后

格式：mysqlbinlog --no-defaults --start-position=’位置点’ 文件名 | mysql -u root -p

（2）从开头一直恢复到某个位置

格式：mysqlbinlog --no-defaults --stop-position=’位置点’ 文件名 | mysql -u root -p

（3）从指定点到指定结束点（位置范围）

格式：mysqlbinlog --no-defaults --start-position=’位置点’ --stop-position=’位置点’ 文件名 | mysql -u root -p

（4）实验

5、时间恢复（基于时间点进行恢复）

（1）从某个事件点开始，恢复到最后

格式：mysqlbinlog --no-defaults --start-datetime=’位置点’ 文件名 | mysql -u root -p

（2）从开头一直恢复到某个时间点

格式：mysqlbinlog --no-defaults --stop-datetime=’位置点’ 文件名 | mysql -u root -p

（3）从指定时间点到指定结束时间点（时间范围）

格式：mysqlbinlog --no-defaults --start-datetime=’位置点’ --stop-datetime=’位置点’ 文件名 | mysql -u root -p

（4）实验

6、总结

（1）在生产中，通过binlog进行增量恢复是非常好用的方法，只需要对binlog文件进行备份，随时可以进行备份和恢复

（五）配置文件

1、MySQL 的日志默认保存位置为 /usr/local/mysql/data

2、错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

（1）log-error=/usr/local/mysql/data/mysql_error.log

3、通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的

（1）general_log=ON

（2）general_log_file=/usr/local/mysql/data/mysql_general.log

4、二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启

（1）log-bin=mysql-bin

（2）log_bin=mysql-bin

5、慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的

（1）slow_query_log=ON

（2）slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

（3）long_query_time=5（设置慢查询时间，超过5秒记录）

6、查看通用查询日志是否开启：show variables like 'general%';

7、查看二进制日志是否开启：show variables like 'log_bin%';

8、查看慢查询日功能是否开启：show variables like '%slow%';

9、 查看慢查询时间设置：show variables like 'long_query_time';

10、在数据库中设置开启慢查询的方法：set global slow_query_log=ON;