linux mysql恢复数据_Mysql数据恢复和备份

使用binlog日志恢复数据

1、首先要确保log日志选项是开启的

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /usr/local/mysql/log/log       |
| log_bin_index                   | /usr/local/mysql/log/log.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

在my.cnf主配置文件中直接添加三行：
log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
三个参数来指定，
第一个参数是打开binlog日志
第二个参数是binlog日志的基本文件名，后面会追加标识来表示每一个文件
第三个参数指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录

当然也有一种简单的配置，一个参数就可以搞定：

[root@bogon mysql]# vim /etc/my.cnf
[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
#expire_log_days = 3
log_bin=/usr/local/mysql/log/log

#开启之后不要忘记重启服务[root@bogon log]# /etc/init.d/mysqld restart。
#不同版本的Mysql或者Mysql集群会有稍微的差异，详见下文链接。

开启binlog日志

2、日志存储的位置

[root@bogon log]# pwd
/usr/local/mysql/log
[root@bogon log]# ll
total 64
-rw-rw----. 1 mysql mysql  1646 Dec 14 15:00 log.000001
-rw-rw----. 1 mysql mysql   532 Dec 14 15:07 log.000002
-rw-rw----. 1 mysql mysql  1021 Dec 14 15:36 log.000003
-rw-rw----. 1 mysql mysql   161 Dec 14 15:36 log.000004
-rw-rw----. 1 mysql mysql   161 Dec 14 15:37 log.000005
-rw-rw----. 1 mysql mysql   806 Dec 14 16:00 log.000006
-rw-rw----. 1 mysql mysql  1474 Dec 14 16:03 log.000007
-rw-rw----. 1 mysql mysql 20219 Dec 14 16:36 log.000008
-rw-rw----. 1 mysql mysql 12024 Dec 14 17:35 log.000009
-rw-rw----. 1 mysql mysql   288 Dec 14 16:36 log.index

3、清空数据（模拟误删除）

3、确定恢复的起点（这里模拟数据表已经被全部删除，所以从log.000001开始恢复）

[root@bogon log]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 46
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show binlog events in 'log.000001';
+------------+------+-------------+-----------+-------------+---------------------------------------------+
| Log_name   | Pos  | Event_type  | Server_id | End_log_pos | Info                                        |
+------------+------+-------------+-----------+-------------+---------------------------------------------+
| log.000001 |    4 | Format_desc |         1 |         120 | Server ver: 5.6.40-log, Binlog ver: 4       |
| log.000001 |  120 | Query       |         1 |         199 | BEGIN                                       |
| log.000001 |  199 | Query       |         1 |         303 | use `test`; insert into t1 values('8','7')  |
| log.000001 |  303 | Xid         |         1 |         334 | COMMIT /* xid=10 */                         |
| log.000001 |  334 | Query       |         1 |         413 | BEGIN                                       |
| log.000001 |  413 | Query       |         1 |         517 | use `test`; insert into t1 values('9','7')  |
| log.000001 |  517 | Xid         |         1 |         548 | COMMIT /* xid=20 */                         |
| log.000001 |  548 | Query       |         1 |         627 | BEGIN                                       |
| log.000001 |  627 | Query       |         1 |         732 | use `test`; insert into t1 values('10','7') |
| log.000001 |  732 | Xid         |         1 |         763 | COMMIT /* xid=30 */                         |
| log.000001 |  763 | Query       |         1 |         842 | BEGIN                                       |
| log.000001 |  842 | Query       |         1 |         947 | use `test`; insert into t1 values('11','7') |
| log.000001 |  947 | Xid         |         1 |         978 | COMMIT /* xid=40 */                         |
| log.000001 |  978 | Query       |         1 |        1057 | BEGIN                                       |
| log.000001 | 1057 | Query       |         1 |        1162 | use `test`; insert into t1 values('12','7') |
| log.000001 | 1162 | Xid         |         1 |        1193 | COMMIT /* xid=50 */                         |
| log.000001 | 1193 | Query       |         1 |        1272 | BEGIN                                       |
| log.000001 | 1272 | Query       |         1 |        1377 | use `test`; insert into t1 values('13','7') |
| log.000001 | 1377 | Xid         |         1 |        1408 | COMMIT /* xid=60 */                         |
| log.000001 | 1408 | Query       |         1 |        1487 | BEGIN                                       |
| log.000001 | 1487 | Query       |         1 |        1592 | use `test`; insert into t1 values('14','7') |
| log.000001 | 1592 | Xid         |         1 |        1623 | COMMIT /* xid=70 */                         |
| log.000001 | 1623 | Stop        |         1 |        1646 |                                             |
+------------+------+-------------+-----------+-------------+---------------------------------------------+
23 rows in set (0.00 sec)

4、执行恢复

[root@bogon log]# mysqlbinlog log.000001 --start-position 120 --stop-position 1646 | mysql -uroot -p

5、验证恢复结果

6、按需执行日志恢复

[root@bogon log]# mysqlbinlog log.000002 --start-position 120 --stop-position 1021 | mysql -uroot -p
[root@bogon log]# mysqlbinlog log.000003 --start-position 120 --stop-position 806 | mysql -uroot -p
......
注意使用mysql> show binlog events in 'log.000001';命令查看日志文件的细节，及时过滤掉误操作的语句。

7、及时备份数据库！！！

[root@bogon mysql]# mysqldump -uroot -p test | gzip > ./test_back.sql.gz
[root@bogon mysql]# mysqldump -uroot -p test > test.sql