用日志进行MYSQL数据库 实时恢复

1 设置二进制日志

#vi /etc/my.cnf

在[mysqld]之下添加如下内容

log-bin=/var/lib/mysql/bing.log

备注 bing.log 路径必须在mysql数据目录（ /var/lib/mysql/ ）之下

2 检查二进制日志是否生效

[root@localhost mysql]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@localhost mysql]# mysql -uroot -proot

 

mysql> show variables like 'log_%';
+---------------------------------+---------------------+
| Variable_name                   | Value               |
+---------------------------------+---------------------+
| log_bin                               | ON                   |
| log_bin_trust_function_creators | OFF                 |
| log_bin_trust_routine_creators  | OFF                 |
| log_error                                     | /var/log/mysqld.log |
| log_output                                  | FILE                |
| log_queries_not_using_indexes | OFF                 |
| log_slave_updates               | OFF                 |
| log_slow_queries                | OFF                 |
| log_warnings                    | 1                   |
+---------------------------------+---------------------+
3 查看BINLOG 的名字是什么

mysql> show binary logs;
+-------------+-----------+
| Log_name    | File_size |
+-------------+-----------+
| bing.000001 |       144 |
| bing.000002 |       125 |
| bing.000003 |       106 |
+-------------+-----------+
4 查看二进制日志文件

[root@localhost mysql]# mysqlbinlog bing.000003

 

5 在进行恢复数据前 执行以下命令 --保证是唯一可以访问该数据库服务器的人

#/usr/bin/mysqld_safe --user=mysql   --skip-networking

======================参考资料=============================

http://wenku.baidu.com/view/807f3b916bec0975f465e247.html

========================================================