差异备份
首先需要开启mysql服务器的二进制日志功能
[root@localhost ~]# vi /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
//在这里加上一下内容
server-id=2
log-bin=mysql_bin
//添加完成之后重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
进行差异备份首先需要全备
[root@localhost ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210506.sql
[root@localhost ~]# ls
all-20210506.sql
在class1里面插入修改一点数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
5 rows in set (0.00 sec)
mysql> use zdj;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql>create table class1(id int not null primary key auto_increment ,name varchar(50),age tinyint,score fioat);
Database changed
mysql> select * from class1;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 5 | tom | NULL | 67.5 |
| 6 | jerry | NULL | 77.5 |
| 7 | zhangshan | NULL | 59.9 |
| 8 | lisi | NULL | 88 |
+----+-----------+------+-------+
4 rows in set (0.00 sec)
mysql> insert class1 (name,age,score) values('yh',22,null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from class1;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 5 | tom | NULL | 67.5 |
| 6 | jerry | NULL | 77.5 |
| 7 | zhangshan | NULL | 59.9 |
| 8 | lisi | NULL | 88 |
| 9 | yh | 22 | NULL |
+----+-----------+------+-------+
5 rows in set (0.00 sec)
mysql> update class1 set score = 99 where name = 'yh';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from class1;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 5 | tom | NULL | 67.5 |
| 6 | jerry | NULL | 77.5 |
| 7 | zhangshan | NULL | 59.9 |
| 8 | lisi | NULL | 88 |
| 9 | yh | 22 | 99 |
+----+-----------+------+-------+
5 rows in set (0.00 sec)
模拟误删
mysql> drop database zdj;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
刷新创建的新的二进制日志
[root@localhost ~]# ll /opt/data
总用量 122972
-rw-r-----. 1 mysql mysql 56 5月 3 08:07 auto.cnf
-rw-------. 1 mysql mysql 1676 5月 3 08:07 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 5月 3 08:07 ca.pem
-rw-r--r--. 1 mysql mysql 1112 5月 3 08:07 client-cert.pem
-rw-------. 1 mysql mysql 1676 5月 3 08:07 client-key.pem
-rw-r-----. 1 mysql mysql 532 5月 6 06:19 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月 6 06:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月 6 06:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月 3 08:07 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月 6 06:23 ibtmp1
-rw-r-----. 1 mysql mysql 11193 5月 6 06:19 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 5月 3 08:07 mysql
-rw-r-----. 1 mysql mysql 852 5月 6 06:36 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 5月 6 06:23 mysql_bin.index
-rw-r-----. 1 mysql mysql 7 5月 6 06:19 mysql.pid
drwxr-x---. 2 mysql mysql 8192 5月 3 08:07 performance_schema
-rw-------. 1 mysql mysql 1680 5月 3 08:07 private_key.pem
-rw-r--r--. 1 mysql mysql 452 5月 3 08:07 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 5月 3 08:07 server-cert.pem
-rw-------. 1 mysql mysql 1680 5月 3 08:07 server-key.pem
drwxr-x---. 2 mysql mysql 8192 5月 3 08:07 sys
//刷新日志
[root@localhost ~]# mysqladmin -uroot flush-logs
[root@localhost ~]# ll /opt/data
总用量 122976
-rw-r-----. 1 mysql mysql 56 5月 3 08:07 auto.cnf
-rw-------. 1 mysql mysql 1676 5月 3 08:07 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 5月 3 08:07 ca.pem
-rw-r--r--. 1 mysql mysql 1112 5月 3 08:07 client-cert.pem
-rw-------. 1 mysql mysql 1676 5月 3 08:07 client-key.pem
-rw-r-----. 1 mysql mysql 532 5月 6 06:19 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月 6 06:38 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月 6 06:38 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月 3 08:07 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月 6 06:23 ibtmp1
-rw-r-----. 1 mysql mysql 11193 5月 6 06:19 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 5月 3 08:07 mysql
//得到以下两行
-rw-r-----. 1 mysql mysql 899 5月 6 06:38 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 5月 6 06:38 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 5月 6 06:38 mysql_bin.index
-rw-r-----. 1 mysql mysql 7 5月 6 06:19 mysql.pid
drwxr-x---. 2 mysql mysql 8192 5月 3 08:07 performance_schema
-rw-------. 1 mysql mysql 1680 5月 3 08:07 private_key.pem
-rw-r--r--. 1 mysql mysql 452 5月 3 08:07 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 5月 3 08:07 server-cert.pem
-rw-------. 1 mysql mysql 1680 5月 3 08:07 server-key.pem
drwxr-x---. 2 mysql mysql 8192 5月 3 08:07 sys
查看一下日志文件
[root@localhost ~]# cd /opt/data
[root@localhost data]# ls
auto.cnf ibdata1 mysql_bin.000002 public_key.pem
ca-key.pem ib_logfile0 mysql_bin.000003 server-cert.pem
ca.pem ib_logfile1 mysql_bin.index server-key.pem
client-cert.pem ibtmp1 mysql.pid sys
client-key.pem localhost.localdomain.err performance_schema
ib_buffer_pool mysql private_key.pem
[root@localhost data]# ls mysql_bin.index
mysql_bin.index
[root@localhost data]# cat mysql_bin.index
./mysql_bin.000002
./mysql_bin.000003
[root@localhost data]# ll mysql_bin*
-rw-r-----. 1 mysql mysql 899 5月 6 06:38 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 5月 6 06:38 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 5月 6 06:38 mysql_bin.index
恢复全备的内容
[root@localhost ~]# mysql -uroot < all-20210506.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
5 rows in set (0.00 sec)
mysql> use zdj;
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> show tables;
+---------------+
| Tables_in_zdj |
+---------------+
| class1 |
| student |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from class1;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 5 | tom | NULL | 67.5 |
| 6 | jerry | NULL | 77.5 |
| 7 | zhangshan | NULL | 59.9 |
| 8 | lisi | NULL | 88 |
+----+-----------+------+-------+
4 rows in set (0.00 sec)
可以看到,现在恢复的是我插入数据之前的表。
接下来看一下恢复之前的那张mysql-bin.000002
表
mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 | 4 | Format_desc | 2 | 123 | Server ver: 5.7.33-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 2 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 2 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 2 | 290 | BEGIN |
| mysql_bin.000002 | 290 | Table_map | 2 | 344 | table_id: 140 (zdj.class1) |
| mysql_bin.000002 | 344 | Write_rows | 2 | 388 | table_id: 140 flags: STMT_END_F |
| mysql_bin.000002 | 388 | Xid | 2 | 419 | COMMIT /* xid=483 */ |
| mysql_bin.000002 | 419 | Anonymous_Gtid | 2 | 484 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 484 | Query | 2 | 555 | BEGIN |
| mysql_bin.000002 | 555 | Table_map | 2 | 609 | table_id: 140 (zdj.class1) |
| mysql_bin.000002 | 609 | Update_rows | 2 | 667 | table_id: 140 flags: STMT_END_F |
| mysql_bin.000002 | 667 | Xid | 2 | 698 | COMMIT /* xid=485 */ |
| mysql_bin.000002 | 698 | Anonymous_Gtid | 2 | 763 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 763 | Query | 2 | 852 | drop database zdj |
| mysql_bin.000002 | 852 | Rotate | 2 | 899 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
恢复
[root@localhost ~]# mysqlbinlog --stop-position=763 /opt/data/mysql_bin.000002 |mysql -uroot
[root@localhost ~]# mysql
mysql> select * from class1;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 5 | tom | NULL | 67.5 |
| 6 | jerry | NULL | 77.5 |
| 7 | zhangshan | NULL | 59.9 |
| 8 | lisi | NULL | 88 |
| 9 | yh | 22 | 99 |
+----+-----------+------+-------+
5 rows in set (0.00 sec)
这里发现恢复完成之后就把删除之前的数据了。
破解密码
密码是在mysql这个数据库里的user这张表内的。
mysql> select * from mysql.user\G
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *BDCAAACD3C05E729952AC2CF78176372B9AB57DC
//这个就是加密后的密码
password_expired: N
password_last_changed: 2021-05-03 08:21:53
password_lifetime: NULL
account_locked: N
首先编辑一下配置文件
[root@localhost ~]# vim /etc/my.cnf
skip-grant-tables
//跳过授权表
重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
破解密码:
mysql> update user set authentication_string = password('ZHANGde12+heng') where Host
= 'localhost' and User = 'mysql.sys';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
再次用破解的密码登录就可以了。