MySQL在Linux系统下通过开启MySQL的bin-log来恢复数据

第一步

需要在my.conf中开启log-bin这个配置是最主要的

[mysqld]
datadir=/usr/local/tools/data/mysql
socket=/usr/local/tools/data/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql_bin  
#SERVER-id=33081
transaction_isolation=REPEATABLE-READ
binlog_format=MIXED

第二步
重启MySQL

然后在MySQL里面输入命令

mysql> show variables like '%bin%';

会查看到  如果log_bin = on的话就是开启了，off的话就是没有开启，需要配置开启

+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | MIXED                |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)

然后查看log_bin的文件存储的地方命令

mysql> show variables like '%datadir%';

结果

+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| datadir       | /usr/local/tools/data/mysql/ |
+---------------+------------------------------+
1 row in set (0.00 sec)

通过命令查看有多少个日志

mysql> show master logs;

结果

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql_bin.000001 |       126 |
| mysql_bin.000002 |       126 |
| mysql_bin.000003 |      1787 |
+------------------+-----------+
3 rows in set (0.00 sec)

通过这个命令可以查看log_bin里面的操作内容

mysql> show binlog events in 'mysql_bin.000003';

结果

+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql_bin.000001 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.12-log, Binlog ver: 4 |
| mysql_bin.000001 | 107 | Stop        |         1 |         126 |                                       |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

这就是操作的内容。

第三步

可以设置这些日志的存活时间

mysql> set global expire_logs_days=7;

结果

mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.00 sec)

第四步
这一步就是恢复数据了
可以通过第二步分析日志  可以查看到误操作的内容及其起始位置

需要到mysql的bin目录下去执行

[root@JD bin]# ./mysqlbinlog /usr/local/tools/data/mysql/mysql_bin.000003 --start-pos=1130 --stop-pos=1362 | ./mysql -uroot -ppassword

需要恢复数据--start-pos就是开始的位置 --stop-pos就是结束的位置。