mysql数据库备份管理,MySQL管理之数据备份及恢复

一次完整的备份恢复过程

首先，我们的备份一定要有备份策略，根据环境定义最适合自己的方案

这里我们的备份策略如下：

·每周一次完整备份

·每天做一次增量备份

接下来我们完全使用MySQLdump以及二进制日志文件方式来实现

完全备份则使用mysqldump

增量备份则使用二进制日志(因为我们无从得知增量的具体数据内容，所以最好的办法就是备份当前产生的二进制文件的内容)

如果确保二进制日志文件是没有问题的不用去做增量也可以，但为了保险起见要每天复制二进制日志文件或让二进制日志文件每天回滚一次，再将回滚日志备份至其他主机

例子：假如服务器在备份后某个时间段崩溃那么如何恢复：

完全备份+昨天的增量备份+今天的凌晨到目前时间服务器尚且未备份为增量的事件(二进制日志文件)

模拟操作

首先确保目前所使用的存储引擎为innodb

mysql> select @@global.default_storage_engine;

+---------------------------------+

|@@global.default_storage_engine |

+---------------------------------+

| InnoDB |

+---------------------------------+

1 row in set (0.00sec)

可以看到，存储引擎是innodb所以可以使用热备(innodb可使用single-transaction参数进行热备)

或者先对其回滚一次二进制日志，但是我们这里没有锁表，因为是没有锁表的热备所以flush了日志意义也不大，最好将其事件记录下来：

创建备份目录，让其备份的文件按时间归档

[root@test ~]#mkdir -p /backup/$(date +%F)

使用single-transaction参数进行热备

master-data=2表示该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。

[root@test ~]# mysqldump -uroot --single-transaction--master-data=2--databases wpdb > /backup/$(date +%F)/backup_$(date+%F).sql

备份完成，之后数据库一直很稳定，我们又对数据库进行了其他操作：

mysql> createtable tb3(id INT);

Query OK, 0 rowsaffected (0.10 sec)

mysql> insert into tb3 values (1),(2),(3);

Query OK, 3 rowsaffected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select *from tb3;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00sec)

数据库依旧没有出现问题，到了晚上要开始做增量备份了：

增量备份要从上次完全备份之后到当前时间

因为我们不知道具体时间和事件的位置，所以最简单的方式就是查看当前哪个二进制文件和所在的位置

mysql> showmaster status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 3383| | |

+------------------+----------+--------------+------------------+

1 row in set (0.00sec)

如上可以看到，所使用的二进制文件是000003 而位置所在处是3383,我们记下这个数值，再去读完整备份的文件

找到关键字所在行

[root@test ~]# grep-i 'change master to'/backup/2014-04-02/backup_2014-04-02.sql

-- CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=3104;

我们从MASTER_LOG_POS=3104这个位置一直到3383才算结束

[root@test ~]#mysqlbinlog --start-position=3104--stop-position=3383/mydata/data/mysql-bin.000003 >/backup/2014-04-02/increment_$(date +%F).sql

增量备份完成

数据恢复

备份完成，此后我们又对数据库进行了操作：

mysql> insertinto tb3 values (4),(6);

Query OK, 2 rowsaffected (0.20 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> insertinto students values ('test1','4','30','2 class');

Query OK, 1 rowaffected (0.00 sec)

mysql> insertinto students values ('test2','5','12','2 class');

Query OK, 1 rowaffected (0.05 sec)

然后将数据库删除

mysql> dropdatabase wpdb;

Query OK, 2 rowsaffected (0.20 sec)

恢复数据思路：

1.恢复完整备份,首先我们的数据库已经被删除，所以只能将其恢复到最近的完整备份的模样

2.恢复增量备份，由于恢复到之前的完整备份的环境，从上一次完整备份到当前时间的数据是不存在的，不过好在每天在做增量备份，使其增量备份恢复到最近时间的数据

3.从二进制日志文件里导入删除之前的数据，因为当前时间距离增量备份的时间前后还有时间，所以就算恢复了增量备份到当前时间还有那么几个小时的数据不存在，所以只能使用二进制日志来恢复

备份的时候一定要记下备份时起的位置号，因为二进制日志文件是唯一救命稻草

我们刚才使用showmaster status;所看到，其二进制位置为3383

[root@test ~]#mysqlbinlog --start-position=3383 /mydata/data/mysql-bin.000003

在最下面看到，在位置3999有我们刚才所误操作的语句

# at3999

#14040220:48:13 server id 1 end_log_pos 4080 Query thread_id=4 exec_time=0 error_code=0

SETTIMESTAMP=1396442893/*!*/;

dropdatabase wpdb

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* addedby mysqlbinlog */;

/*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

记下这个3999这个位置，使用position参数进行恢复二进制文件

#stop-position表示到某个位置之前

[root@test ~]#mysqlbinlog --start-position=3383 --stop-position=3999/mydata/data/mysql-bin.000003 > /backup/2014-04-02/wpdb_source_`date+%F`.sql

查看文件可以看到我们已经恢复到2465之前的位置了

[root@test ~]# tail/backup/2014-04-02/wpdb_source_`date +%F`.sql

insert intostudents values ('test2','5','12','2 class')

/*!*/;

# at 3972

#140402 20:46:40server id 1 end_log_pos 3999 Xid = 313

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* addedby mysqlbinlog */;

/*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

恢复：

使其mysql离线，全局锁表或断开前端网络都可以

关闭bin_log功能，使其不记录二进制日志文件

mysql> setsql_log_bin=0;

Query OK, 0 rowsaffected (0.04 sec)

恢复完整备份

mysql> source /backup/2014-04-02/backup_2014-04-02.sql

恢复增量备份

mysql> source/backup/2014-04-02/increment_2014-04-02.sql

导入二进制日志文件(修改后)

mysql> source/backup/2014-04-02/wpdb_source_2014-04-02.sql

验证：

mysql> showdatabases;

+--------------------+

| Database |

+--------------------+

|information_schema |

| mydb |

| mysql |

|performance_schema |

| test |

| wpdb |

+--------------------+

6 rows in set (0.07sec)

mysql> select *from students;

+-------+------+------+---------+

| name | id | Age | class |

+-------+------+------+---------+

| bob | 1| 40 | 1 class |

| jerry | 3 | 33 | 2 class |

| test | 3| 30 | 2 class |

| test1 | 4 | 30 | 2 class |

| test2 | 5 | 12 | 2 class |

| tom | 2| 20 | 1 class |

+-------+------+------+---------+

6 rows in set (0.00sec)

确保无误，开启binlog记录功能：

mysql> setsql_log_bin=1;

Query OK, 0 rowsaffected (0.00 sec)

通常我们在生产环境都是备份的全部数据库，不然的话从二进制导出的数据可能不仅是一个数据库，还必须手动去过滤，会耽误不少时间

[root@localhost ~]#mysqldump -uroot --single-transaction --master-data=2 --all-databases > /tmp/backup_`date+%F`.sql

而且在备份的时候还需要注意：

二进制日志文件备份不需要先导出，可以先使其回滚一次在将其备份二进制文件之后再统一导出为sql并按需恢复

总结

1.无论怎么去备份都需要事先考虑以下几个问题

·容忍地丢失多长时间的数据

·恢复必须要在多长时间完成

·是否需要持续提供服务

·需要恢复什么

2.MyISAM表是不支持热备的，Innodb是可以支持热备但需要专用的工具

3.备份的本身依赖于:

完全+增量 或完全+差异

4.数据恢复步骤

完全+增量|差异+二进制日志

5.一定不要将二进制日志文件与其他文件放在同一目录下以及同一磁盘上

6.要有完整的备份策略

7.从备份中恢复需要的操作：

·停止mysql服务(冷备和快照的恢复必须停止

·记录服务的配置和文件权限

·复制备份文件至数据目录

·按需调整配置

·按需改变文件权限

·尝试启动服务，但是要限制访问权限

对于二进制文件的恢复：

·装载逻辑备份

·检查或重放二进制日志

·检测数据还原正常完成