第13课:数据备份与恢复

各种意外情况都可能导致mysql数据损坏,比如升级数据库,SQL误操作,BUG引起的数据错误,所以mysql的备份是尤为重要的
目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相关的配置文件。MyISAM 表是保存成文件的形式,因此相对比较容易备份,上面提到的几种方法都可以使用。Innodb 所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件,或者是独立的表空间文件),相对来说比较不好备份

1.mysqldump备份恢复数据
1.1备份
我通常使用以下 SQL 来备份 MyISAM 表:
/usr/local/mysql/bin/mysqldump -uroot -p \
--default-character-set=utf8 --opt --extended-insert=false \
--triggers -R --hex-blob -x db_name > db_name.sql
使用以下 SQL 来备份 Innodb 表:
/usr/local/mysql/bin/mysqldump -uroot -p \
--default-character-set=utf8 --opt --extended-insert=false \
--triggers -R --hex-blob --single-transaction db_name > db_name.sql

另外,如果想要实现在线备份,还可以使用 --master-data 参数来实现,如下:
/usr/local/mysql/bin/mysqldump -uroot -p \
--default-character-set=utf8 --opt --master-data=1 \
--single-transaction --flush-logs db_name > db_name.sql

注意:--extended-insert 需要根据实际情况决定是否启用或关闭 ,会对数据恢复速度产生较大影响。

1.2恢复
/usr/local/mysql/bin/mysql -uroot -p db_name < db_name.sql

2.binlog日志恢复数据
发现数据损坏的时候可能忘记备份数据,但是因为打开了mysql的binlog,数据库的操作都有日志文件,我们可以通过日志来恢复数据
采用 binlog 的方法相对来说更灵活,省心省力,而且还可以支持增量备份

启用 binlog方法: 
首先,关闭 mysqld,打开 my.cnf,加入以下几行:
server-id	= 1
log-bin		= binlog
log-bin-index	= binlog.index
然后启动 mysqld 就可以了

运行过程中会产生 binlog.000001 以及 binlog.index,前面的文件是 mysqld 记录所有对数据的更新操作,后面的文件则是所有 binlog 的索引
备份出来的 binlog 文件可以用 MySQL 提供的工具 mysqlbinlog 来查看,如:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
恢复时,可以采用类似以下语句来做到:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uroot -p db_name
把 mysqlbinlog 输出的 SQL 语句直接作为输入来执行它

3.备份策略
对于中等级别业务量的系统来说,备份策略可以这么定:第一次全量备份,每天一次增量备份,每周再做一次全量备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 slave 机器上做备份

4.数据维护和灾难恢复
CHECK TABLE 或 REPAIR TABLE,检查或维护 MyISAM 表
OPTIMIZE TABLE,优化 MyISAM 表
ANALYZE TABLE,分析 MyISAM 表

Innodb 表则可以通过执行以下语句来整理碎片,提高索引速度:
ALTER TABLE tbl_name ENGINE = Innodb;
这其实是一个 NULL 操作,表面上看什么也不做,实际上重新整理碎片了

通常使用的MyISAM表可以用上面提到的恢复方法来完成。如果是索引坏了,可以用 myisamchk 工具来重建索引。而对于 Innodb 表来说,就没这么直接了,因为它把所有的表都保存在一个表空间了。不过Innodb有一个检查机制叫 模糊检查点,只要保存了日志文件,就能根据日志文件来修复错误。可以在 my.cnf 文件中,增加以下参数,让mysqld在启动时自动检查日志文件:
innodb_force_recovery	= 4

打赏  如对你有帮助,请我喝杯咖啡吧!