MySQL--备份和恢复

一.备份类型

1.热备份、温备份和冷备份

1）热备份：读写操作均不受影响；

2）温备份：仅可以执行读操作；

3）冷备份：离线备份，读写操作均停止；

2.物理备份和逻辑备份

1）物理备份：复制数据文件；

特点：速度快；

2）逻辑备份：将数据导出至文本文件中；

特点：速度慢、丢失浮点数精度、方便使用文本工具直接进行处理、可移植能力强；

3.完全备份、增量备份和差异备份

1）完全备份：备份全部数据；

2）增量备份：仅备份上次完全备份或增量备份以后变化的数据；

3）差异备份：仅备份上次完全备份以来变化的数据；

二.备份工具

1.MySQL：

1）mysqldump：逻辑备份工具；

2）mysqlhotcopy：物理备份工具，温备份；

2.文件系统：

1）cp：冷备；

2）lv：逻辑卷的快照功能，几乎热备；

3.第三方工具

1）ibbackup：商业工具；

2）xtrabackup：开源工具；

三.mysqldump

1.备份单个数据库，或库中特定表

备份：mysqldump -uuser -ppasswd DB_NAME [tb1] [tb2] > file_name.sql

2.主要参数：

--compatible=name：指定导出的数据和哪种数据库或哪个版本的mysql服务器兼容，可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用几个值，用逗号将它们隔开；

--default-character-set=charset：指定导出数据时采用何种字符集，mysqldump默认使用utf8；

--master-date={0|1|2}：0表示不记录二进制日志文件及位置；1表示以CHANGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器；2表示以CHANGE MASTER TO的方式记录位置，但为注释状态；

lock-all-tables：备份前锁定所有表；

--flush-logs：执行日志flush；

如果指定库中的表类型为InnoDB，可使用--single-transaction启动热备；

备份多个库：

--all-databases：备份所有库；

--databases DB_NAME,DBNAME：备份多个库；

恢复时会自动创建库；

3.备份策略

完全备份+增量备份

4.例子

1）mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /root/test.sql    =========>完全备份

2）修改数据库后，滚动日志：FLUSH LOGS；此时使用SHOW BINARY LOGS可发现二进制日志多了一个；

3）查看/root/test.sql可发现：保存的日志到mysql-bin.000003为止

 

在/var/lib/mysql/目录下可看到有四个日志：因为更新数据库后我又手动滚动了一次日志

4）使用mysqlbinlog mysql-bin.000003 > /root/incremental.sql，将日志文件用mysqlbinlog处理后的文件保存；

5）再向数据库中插入数据；

6）模拟删除了数据库操作；

7）此时恢复完全备份：mysql -uroot -p < /root/test.sql，此时查看数据库，发现已经恢复到完全备份的时候；

8）再恢复：mysql -uroot -p < /root/incremental.sql，至此完全恢复。

5.还原：mysql -uuser -ppasswd DB_NAME < file_name.sql