用mysqldump备份及结合binlog日志恢复的全过程

1、查看更新备份时的数据

mysql> select * from t1;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

 

2、因为我的存储引擎是Myisam ，为了保证数据的一直我加了参数 –l 备份时不能对数据更新，如果是innodb引擎加参数 –single-transcation 即能保证数据一直性，有可以对数据更新-F 表示生成新的日志文件

# /usr/local/mysql/bin/mysqldump -uroot -p123456 -l -F data1 > data1.sql

 

3、备份后，对数据更新

mysql> insert into t1 values(4),(5),(6),(7);

mysql> select * from t1;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

|    6 |

|    7 |

+------+

 

4、

数据恢复时禁止binlog日志

# /usr/local/mysql/bin/mysql -uroot -p123456 -e "set global sql_log_bin=0"

 

# /usr/local/mysql/bin/mysql -uroot -p123456 -e "show global variables like 'sql_log_bin'\G"

*************************** 1. row ***************************

Variable_name: sql_log_bin

        Value: OFF

 

5、# /usr/local/mysql/bin/mysql -uroot -p123456 data1 < data1.sql

 mysql> select * from t1;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

 

6、

用mysqlbinglog恢复自mysqldump备份以来的binglog日志（不一定只有一个binlog日志）

/usr/local/mysql/bin/mysqlbinlog binlog.000028|/usr/local/mysql/bin/mysql -uroot -p123456  data1

 

mysql> select * from t1;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

|    6 |

|    7 |

+------+

7 rows in set (0.00 sec)

 

7、开启binglog日志功能

 

# /usr/local/mysql/bin/mysql -uroot -p123456 -e "set global sql_log_bin=1"

# /usr/local/mysql/bin/mysql -uroot -p123456 -e "show global variables like 'sql_log_bin'\G"

*************************** 1. row ***************************

Variable_name: sql_log_bin

        Value: ON

  备份情况举例说明：

周一（2010.11.15）凌晨5点进行了数据库的全备，文件名为：all_bak.sql。全备文件中记录的日志位置是：MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=364; 自全备以来产生了3个新的日志文件，分别为mysql-bin.000024，mysql-bin.000025，mysql-bin.000026。其中三个增量日志的最后写入时间分别是上午10点，下午2点与下午6点。

5.1 恢复到某一时间点（可以结合开始位置，结束时间的方式）。

晚上发现中午12点10分左右由于问题使得12点10分以后的数据错乱，需要恢复之前数据。全备文件与增量备份文件完整。

（1）故障停机后，全备数据库

（2）恢复全备文件

mysql -uroot -proot test1

（3）恢复全备以来的增量日志文件到12点10分结束

 mysqlbinlog --no-defaults mysql-bin.000023 mysql-bin.000024 mysql-bin.000025  --start-position=364 --stop-datetime='2010-11-15 12:10:00' |mysql -uroot -proot

（4）抽查数据，开服务测试

5.2 跳过某一破坏性语句的恢复

晚上时发现下午有误删除了数据库中的一些数据，知道删除的命令和大概时间（下午4点左右），此时需要恢复出误删除的那部分数据。

（1）故障停机后，全备数据库

（2）恢复全备文件

mysql -uroot -proot test1

（3）依删除命令和大概时间在mysql-bin.000026中找出删除命令的开始和结束位置号。例如：6678 与6691

（4）恢复增量日志到删除命令开始的位置号。

mysqlbinlog --no-defaults mysql-bin.000023 mysql-bin.000024 mysql-bin.000025  mysql-bin.000026 --start-position=364 --stop-position=6678 |mysql -uroot -proot

（5）从删除命令的结束位置号开始恢复剩余的数据。

mysqlbinlog --no-defaults mysql-bin.000026 --start-position=6691|mysql -uroot -proot

（6）抽查数据，开服务测试

 

5.3 基于某个数据库的增量恢复

在同一台服务器上运行了多个数据库，可以分开全备数据库，但是增量日志是统一写在一起。所以当单个数据库故障时会对单个数据库进行恢复操作，而其他的数据库不允许停机。

（1）停止故障数据库对应服务，全备数据库

（2）恢复全备文件

mysql -uroot -proot test1

（3）恢复故障数据库对应的增量日志部分。

mysqlbinlog --no-defaults mysql-bin.000023 mysql-bin.000024 mysql-bin.000025  mysql-bin.000026 --start-position=364 --database=test1 |mysql -uroot -proot

 

6 备份数据恢复测试

定期对备份数据进行恢复测试，以抽查验证数据有效性。