mysql利用mysqlbinlog命令恢复误删除数据

1.编译安装MySQL

如果有老的MySQL或Maridb，则要先卸载

[root@monitor ~]# systemctl stop mysqld
[root@monitor ~]# rpm -qa | egrep -i 'mysql|maria'
[root@monitor ~]# rpm -qa | egrep -i 'mysql|maria'
mariadb-libs-5.5.56-2.el7.x86_64
# 而实际这个mariadb-libs-5.5.56-2.el7.x86_64不需要卸载没有关系。

[root@monitor ~]# rpm -e --nodeps  + 包名1 包名2 .....

# 删除老版本mysql的开发头文件和库
rm -rf /usr/lib/mysql
rm -rf /usr/include/mysql
# 注意：卸载后/var/lib/mysql中的数据及/etc/my.cnf不会删除，如果确定没用后就手工删除
rm -rf /etc/my.cnf
rm -rf /var/lib/mysql

下载安装配置

# 下载

[root@monitor ~]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.22-el7-x86_64.tar.gz -P /usr/local/    #下载慢的话通过浏览器下载后再传上去

# 解压添加用户和环境变量

[root@monitor ~]# 
mkdir -p /usr/local/mysql && \
cd /usr/local/ && \
tar -xzvf mysql-5.7.22-el7-x86_64.tar.gz && \
cp -arf mysql-5.7.22-el7-x86_64/* /usr/local/mysql/ && \
cd /usr/local/mysql/ && \
mkdir data && \
mkdir log && \
echo "export PATH=\$PATH:/usr/local/mysql/bin" >> /etc/profile && \
source /etc/profile && \
groupadd mysql && \
useradd -r -g mysql -s /bin/false mysql

# 查看目录

[root@monitor mysql]# pwd
/usr/local/mysql
[root@monitor mysql]# ll
total 36
drwxr-xr-x  2 root root   4096 Jan  4 11:07 bin
-rw-r--r--  1 7161 31415 17987 Mar  4  2018 COPYING
drwxr-xr-x  2 root root      6 Jan  4 11:08 data
drwxr-xr-x  2 root root     55 Jan  4 11:07 docs
drwxr-xr-x  3 root root   4096 Jan  4 11:07 include
drwxr-xr-x  5 root root    229 Jan  4 11:07 lib
drwxr-xr-x  2 root root      6 Jan  4 11:08 log
drwxr-xr-x  4 root root     30 Jan  4 11:07 man
-rw-r--r--  1 7161 31415  2478 Mar  4  2018 README
drwxr-xr-x 28 root root   4096 Jan  4 11:07 share
drwxr-xr-x  2 root root     90 Jan  4 11:07 support-files

# 更新配置文件

[root@monitor mysql]# 
cp -arf /etc/my.cnf /etc/my.cnf.bak`date +%F`
cat >/etc/my.cnf << EOF
[client]
socket=/usr/local/mysql/mysql.sock  
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysqld.pid
socket=/usr/local/mysql/mysql.sock
log_error=/usr/local/mysql/log/mysql.error.log
EOF

# 改为mysql用户和组

[root@monitor mysql]# 
cd /usr/local/mysql && \
chmod 750 data/ && \
chown -R mysql . && \
chgrp -R mysql .
[root@monitor mysql]# ll
total 36
drwxr-xr-x  2 mysql mysql  4096 Jan  4 11:07 bin
-rw-r--r--  1 mysql mysql 17987 Mar  4  2018 COPYING
drwxr-x---  2 mysql mysql     6 Jan  4 11:08 data
drwxr-xr-x  2 mysql mysql    55 Jan  4 11:07 docs
drwxr-xr-x  3 mysql mysql  4096 Jan  4 11:07 include
drwxr-xr-x  5 mysql mysql   229 Jan  4 11:07 lib
drwxr-xr-x  2 mysql mysql     6 Jan  4 11:08 log
drwxr-xr-x  4 mysql mysql    30 Jan  4 11:07 man
-rw-r--r--  1 mysql mysql  2478 Mar  4  2018 README
drwxr-xr-x 28 mysql mysql  4096 Jan  4 11:07 share
drwxr-xr-x  2 mysql mysql    90 Jan  4 11:07 support-files

# 初始化

[root@monitor mysql]# pwd
/usr/local/mysql
[root@monitor mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql
或 [root@monitor mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize
[root@monitor mysql]# grep 'temporary password' /usr/local/mysql/log/mysql.error.log
2019-01-04T03:11:29.386050Z 1 [Note] A temporary password is generated for root@localhost: n!rDqonId0qi

# 添加服务启动项

[root@monitor mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld && service mysqld status
ERROR! MySQL is not running
[root@monitor mysql]# chkconfig --add mysqld && chkconfig mysqld on
[root@monitor mysql]# service mysqld start
Starting MySQL. SUCCESS!

# 首次登录要更改密码，初始化时查到的初始密码为n!rDqonId0qi

[root@monitor mysql]# mysql -uroot -p
Enter password:n!rDqonId0qi
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

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> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'GoldrockMonitor01!';
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'GoldrockMonitor01!';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user,password from user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *8E058CA664EDC92380733BD602CD9A297CEE7FC4 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql>

 

2.开启MySQL binlog日志

1、登录MySQL，查看binlog日志的状态

登录MySQL后，输入show variables like '%log_bin%'；查看到binlog日志为OFF关闭状态;

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | off                             |
| log_bin_basename                |                                 |
| log_bin_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)

2、开启MySQL binlog日志

退出MySQL，使用vi编辑器修改MySQL的my.cnf配置文件

在my.cnf配置文件中添加如下图的两句

[root@localhost /]*# vim /etc/my.cnf
   
log-bin=mysql-bin  #*#开启二进制日志（默认放到data）

3.重启MySQL

查看/usr/local/mysql目录下的内容

使用命令：ls /usr/local/mysql

此时再次进入MySQL，查看binlog日志的状态。显示binlog日志为ON开启状态

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /var/mysql/data/mysql-bin       |
| log_bin_index                   | /var/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+

————————————————

3.binlog文件 恢复mysql 数据库

1、执行：FLUSH LOGS;

刷新日志是为了实验内容更直观，更容易观察到整个实验过程的内容。

我看到网上许多文章有在用REST MASTER;而未说明此命令的严重性

这条命令会删除所有日志文件，并将文件名和记录点进行重置归零，99%的情况下是用不到这条命令的

删除日志可以用PURGE MASTER LOGS...这样保险一点

2、新日志文件已经生成，先观察一下内容，有几个点需要了解


查看二进日日志文件命令：mysqlbinlog mysql-bin.000001

# at 4

# 180903 16:19:12 server id 1  end_log_pos 123 CRC32 0xe03659b3  Start: binlog v 4, server v 5.7.22-log created 180903 16:19:12

先看上边两个箭头：

# at 4（事件开始点）

# 180903 16:19:12 （代表的是时间）

server id 1（主备复制时需要为每个MYSQL数据库指定唯一的SERVER ID，我的未配置，默认是1）

end_log_pos 123（事件结束点）

再看下边两个箭头：

# at 123（事件开始点，和上边的事件结束点是对应的）

end_log_pos 154（事件结束点）

at 4 和 at 123之间的内容就是事件内容

3、模拟业务场景，建表，插入数据，最后将某个表删除；为了真实，我建了两个库，同时向不同的库写入内容，最后将其中一个库中的某个表删除。

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)

mysql> create database t1;
Query OK, 1 row affected (0.03 sec)

mysql> create database t2;
Query OK, 1 row affected (0.00 sec)

mysql> use t1;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> use t2;
Database changed
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values (3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values (4);
Query OK, 1 row affected (0.01 sec)

mysql> use t1;
Database changed
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (2);
Query OK, 1 row affected (0.01 sec)

mysql> use t2;
Database changed
mysql> insert into t2 values(20);
Query OK, 1 row affected (0.01 sec)

mysql> use t1;
Database changed
mysql> insert into t1 values(10);
Query OK, 1 row affected (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> use t2;
Database changed
mysql> insert into t2 values(222);
Query OK, 1 row affected (0.01 sec)

mysql>

建立T1、T2库，建立T1、T2表。

向T1插入数据：1、2、10

向T2插入数据：3、4、20、222

模拟场景，删除T1表，T2库T2表业务还在继续运行

现在将要通过日志将T1表进行恢复。

首先要先找到那个删除命令的日志点：

mysqlbinlog master-bin.000014|grep -5a "DROP TABLE"


看到#AT 2439 （记下这个数字）

在这个事件点执行的DROP TABLE操作。

由于日志文件内不只有T1库的日志，还有T2库的日志，一会只取T1数据库的日志

而且还只取2439日志点之前的日志，再进行重新应用

如果把2439的日志取的话，再应用时数据库会重新建库建表，插数据， 还会执行这条删表语句。

mysqlbinlog mysql-bin.000001 -d t1  --skip-gtids --stop-position=2439>test.sql

-d：参数是指定某个数据库日志

命令意思是将mysql-bin.000001日志文件内的T1数据库日志，事件点2439之前的日志，输出到test.sql

# tail test.sql

看看文件最后几行


登录数据库：

mysql> use t1;
Database changed

mysql> source test.sql

再查看表内容


这样数据就回来了。