使用mysqldump完整备份和增量备份MySQL及模拟故障恢复测试

环境准备

安装MySQL数据库服务器

参考:
企业之Mysql备份恢复:http://502245466.blog.51cto.com/7559397/1291913

创建测试数据库与表并插入数据

[root@localhost ~]# mysql -uroot -proot@pass
mysql> create database allen;
mysql> use allen;
mysql> create table tab1 (ID tinyint unsigned not null primary key auto_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M');
mysql> insert into tab1 (Name,Age,Gender) values ('Zhang WuJi',26,'M'),('Zhao Min',24,'F');

使用mysqldump备份工具进行备份与恢复测试

1、使用Mysqldump对”allen”数据库做备份并且滚动二进制日志,记录日志位置

[root@localhost ~]# mysqldump -uroot -proot@pass --lock-all-tables --routines --events --triggers --master-data=2 --flush-logs --databases allen > /backup/allen_`date +%F`.sql
注释:
-u                        #指定用户名
-p                        #指定用户密码
-h                        #指定主机地址
-A|--all-databases        #备份所有数据库
--databases               #备份指定数据库
--single-transcation      #基于此项可以实现对InnoDB表做热备份,但不需要使用
--lock-all-tables         #执行备份时为所有表请求加锁
-E|--events               #备份事件调度器代码
--opt                     #同时启动各种高级选项
-R|--routines             #备份存储过程和存储函数
--flush-logs              #备份之前刷新日志
--triggers                #备份触发器
--master-data=2           #该选项将会记录binlog的日志位置与文件名并追加到文件中,如果为1将会输出CHANGE MASTER命令,主从下有用
######更多选项请参考man手册

2、备份二进制日志文件并查看已备份的数据文件

[root@localhost ~]# cp /binlog/mysql-bin.00000* /backup/
[root@localhost ~]# ls /backup/
allen_2013-09-06.sql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.000004

3、在”allen”数据库中新添加数据来模拟做增量备份,并查看当前日志文件及所处位置

mysql> create table tab2 (ID tinyint unsigned not null primary key auto_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M');
mysql> insert into tab2 (Name,Age,Gender) values ('Liang ShanBo',26,'M'),('Zhu YingTai',24,'F');
mysql> show master status; #查看二进制日志文件所处位置
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     1289 |              |                  |
+------------------+----------+--------------+------------------+

4、做增量备份,基于上一次完全备份到目前日志所处的位置

######查看数据库备份的二进制日志
[root@localhost ~]# less /backup/allen_2013-09-06.sql
######从下面可以看出二进制日志文件及完全备份后日志所处位置"107"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=107;
######基于二进制日志做增量备份,
[root@localhost ~]# mysqlbinlog --start-position=107 /binlog/mysql-bin.000004 > /backup/allen_incremental.sql
注释:
--start-position     #指定从哪个位置开始导出二进制日志,包含当前开始位置,即“>=”
--stop-position      #指定到哪个位置结束;如果到最后可以不用指定,不包含当前结束位置,即“< ”
--start-datetime     #指定从哪个时间开始 时间格式:"2013-09-06 14:50:26"(该值格式应符合DATETIME或TIMESTAMP数据类型。),包含当前开始位置,即“>=”
--stop-datetime      #指定到哪个时间结束,不包含当前结束位置,即“< ”

5、向"allen"数据库新添加两条记录,然后模拟误操作把数据库删除了,怎么恢复到当前数据

mysql> insert into tab2 (Name,Age,Gender) values ('Zhu BaJie',26,'M'),('Sun WuKong',24,'F');
mysql> drop database allen;
mysql> show master status; #查看二进制文件及所处位置
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     1644 |              |                  |
+------------------+----------+--------------+------------------+

6、恢复数据到当前数据,需要恢复完整备份+增量备份+增量备份后删除之前的二进制文件

######查看二进制文件,下面可以看出删除操作是在"1561"时做的
[root@localhost ~]# mysqlbinlog  /binlog/mysql-bin.000004
# at 1561
#130906  5:31:04 server id 1  end_log_pos 1644  Query   thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1378416664/*!*/;
drop database allen
######导出删除之前至增量备份后的二进制日志数据
[root@localhost ~]# mysqlbinlog --start-position=1289 --stop-position=1561 /binlog/mysql-bin.000004  > /backup/allen_1561.sql
[root@localhost ~]# mysql -uroot -proot@pass
mysql> set global sql_log_bin=0;            #关闭二进制日志记录,因为恢复数据对我们来说记录没有意义
mysql> source /backup/allen_2013-09-06.sql  #恢复完整备份
mysql> source /backup/allen_incremental.sql #恢复增量备份
mysql> source /backup/allen_1561.sql        #恢复增量后至删除前的数据
mysql> set global sql_log_bin=1;            #开启二进制日志记录
mysql> use allen;                           #切换到"allen"数据库
mysql> show tables;                         #查看数据表已经恢复
+-----------------+
| Tables_in_allen |
+-----------------+
| tab1            |
| tab2            |
+-----------------+
mysql> select * from tab1;                  #查看表内数据也已经完全恢复
+----+------------+------+--------+
| ID | Name       | Age  | Gender |
+----+------------+------+--------+
|  1 | Zhang WuJi |   26 | M      |
|  2 | Zhao Min   |   24 | F      |
+----+------------+------+--------+
mysql> select * from tab2;
+----+--------------+------+--------+
| ID | Name         | Age  | Gender |
+----+--------------+------+--------+
|  1 | Liang ShanBo |   26 | M      |
|  2 | Zhu YingTai  |   24 | F      |
|  3 | Zhu BaJie    |   26 | M      |
|  4 | Sun WuKong   |   24 | F      |
+----+--------------+------+--------+

注释:如果在生产环境中需要做数据库完全备份,如果数据库整个系统崩溃,需要另外找一台服务器安装好Mysql服务,初始化并启动,然后使用备份文件与二进制文件进行恢复,所以一定要保存好备份文件与二进制文件

附录:
1、使mysqldump来实现mysql的备份与还原,只能应用于那些读写量不大的小企业,因为备份过程太慢了,会造成精度的丢失。

扩展阅读:
mysqldump命令详解及几个主要选项探究:http://www.3mu.me/mysqldump%e5%91%bd%e4%bb%a4%e8%af%a6%e8%a7%a3%e5%8f%8a%e5%87%a0%e4%b8%aa%e4%b8%bb%e8%a6%81%e9%80%89%e9%a1%b9%e6%8e%a2%e7%a9%b6/

Shell结合crontab实现MySQL的全量,增量备份:http://www.3mu.me/shell%e7%bb%93%e5%90%88crontab%e5%ae%9e%e7%8e%b0mysql%e7%9a%84%e5%85%a8%e9%87%8f%e5%a2%9e%e9%87%8f%e5%a4%87%e4%bb%bd/

在线备份MySQL及远程copy到远程备份服务器shell脚本:http://blog.chinaunix.net/uid-25723371-id-3241683.html

参考资料:
企业之Mysql备份恢复:http://502245466.blog.51cto.com/7559397/1291913

发表评论?

0 条评论。

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据