差异备份&恢复&破解密码

原创 于 2021-05-07 16:14:43 发布 · 105 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql

差异备份&恢复&破解密码

差异备份

首先开启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! 




创建数据库yh，在yh里创建一个表class1，
往class1表中插入数据。

```cpp
mysql> create database yh;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yh                 |
+--------------------+
5 rows in set (0.00 sec)

mysql> use yh;
Database changed

mysql> create table class1 (id int not null primary key auto_increment,name varchar (50),age tinyint,score float);
Query OK, 0 rows affected (0.05 sec)

mysql> insert class1 (name,age,score) values ('tom',null,50),('jerry',null,60),('xiaoyu',null,89),('wangwu',null,78);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from class1;
+----+--------+------+-------+
| id | name   | age  | score |
+----+--------+------+-------+
|  1 | tom    | NULL |    50 |
|  2 | jerry  | NULL |    60 |
|  3 | xiaoyu | NULL |    89 |
|  4 | wangwu | NULL |    78 |
+----+--------+------+-------+
4 rows in set (0.00 sec)


[root@localhost ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210506.sql    进行差异备份需要先全备
[root@localhost ~]# ls
公共  图片  音乐              hh                                          yy
模板  文档  桌面              mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
视频  下载  all-20210506.sql  yh


mysql> insert class1 (name,age,score) value ('lisi',20,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from class1;
+----+--------+------+-------+
| id | name   | age  | score |
+----+--------+------+-------+
|  1 | tom    | NULL |    50 |
|  2 | jerry  | NULL |    60 |
|  3 | xiaoyu | NULL |    89 |
|  4 | wangwu | NULL |    78 |
|  5 | lisi   |   20 |  NULL |
+----+--------+------+-------+
5 rows in set (0.00 sec)

mysql> update class1 set score = 80 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from class1;
+----+--------+------+-------+
| id | name   | age  | score |
+----+--------+------+-------+
|  1 | tom    | NULL |    50 |
|  2 | jerry  | NULL |    60 |
|  3 | xiaoyu | NULL |    89 |
|  4 | wangwu | NULL |    78 |
|  5 | lisi   |   20 |    80 |
+----+--------+------+-------+
5 rows in set (0.01 sec)

模拟误删

mysql> drop database yh;
Query OK, 1 row 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
总用量 122980
-rw-r-----. 1 mysql mysql       56 5月   7 14:14 auto.cnf
-rw-------. 1 mysql mysql     1680 5月   7 14:14 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 5月   7 14:14 ca.pem
-rw-r--r--. 1 mysql mysql     1112 5月   7 14:14 client-cert.pem
-rw-------. 1 mysql mysql     1676 5月   7 14:14 client-key.pem
-rw-r-----. 1 mysql mysql      301 5月   7 14:37 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月   7 15:03 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月   7 15:03 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月   7 14:14 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月   7 14:38 ibtmp1
-rw-r-----. 1 mysql mysql    18988 5月   7 14:37 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 5月   7 14:14 mysql
-rw-r-----. 1 mysql mysql     1559 5月   7 15:03 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 5月   7 14:38 mysql_bin.index
-rw-r-----. 1 mysql mysql        7 5月   7 14:37 mysql.pid
drwxr-x---. 2 mysql mysql     8192 5月   7 14:14 performance_schema
-rw-------. 1 mysql mysql     1676 5月   7 14:14 private_key.pem
-rw-r--r--. 1 mysql mysql      452 5月   7 14:14 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 5月   7 14:14 server-cert.pem
-rw-------. 1 mysql mysql     1680 5月   7 14:14 server-key.pem
drwxr-x---. 2 mysql mysql     8192 5月   7 14:14 sys



[root@localhost ~]# mysqladmin -uroot flush-logs   //刷新日志
[root@localhost ~]# ll /opt/data
总用量 122984
-rw-r-----. 1 mysql mysql       56 5月   7 14:14 auto.cnf
-rw-------. 1 mysql mysql     1680 5月   7 14:14 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 5月   7 14:14 ca.pem
-rw-r--r--. 1 mysql mysql     1112 5月   7 14:14 client-cert.pem
-rw-------. 1 mysql mysql     1676 5月   7 14:14 client-key.pem
-rw-r-----. 1 mysql mysql      301 5月   7 14:37 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月   7 15:03 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月   7 15:03 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月   7 14:14 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月   7 14:38 ibtmp1
-rw-r-----. 1 mysql mysql    18988 5月   7 14:37 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 5月   7 14:14 mysql
-rw-r-----. 1 mysql mysql     1606 5月   7 15:05 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 5月   7 15:05 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 5月   7 15:05 mysql_bin.index
-rw-r-----. 1 mysql mysql        7 5月   7 14:37 mysql.pid
drwxr-x---. 2 mysql mysql     8192 5月   7 14:14 performance_schema
-rw-------. 1 mysql mysql     1676 5月   7 14:14 private_key.pem
-rw-r--r--. 1 mysql mysql      452 5月   7 14:14 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 5月   7 14:14 server-cert.pem
-rw-------. 1 mysql mysql     1680 5月   7 14:14 server-key.pem
drwxr-x---. 2 mysql mysql     8192 5月   7 14:14 sys

查看日志文件

[root@localhost ~]# cd /opt/data
[root@localhost data]# ls
auto.cnf         ibdata1                    mysql_bin.000003    public_key.pem
ca-key.pem       ib_logfile0                mysql_bin.000004    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.000003
./mysql_bin.000004

[root@localhost data]# ll mysql_bin*
-rw-r-----. 1 mysql mysql 1606 5月   7 15:05 mysql_bin.000003
-rw-r-----. 1 mysql mysql  154 5月   7 15:05 mysql_bin.000004
-rw-r-----. 1 mysql mysql   38 5月   7 15:05 mysql_bin.index

恢复全备的内容

[root@localhost ~]# mysql -uroot < all-20210506.sql 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yh                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use yh;
Database changed

mysql> select * from class1;
+----+--------+------+-------+
| id | name   | age  | score |
+----+--------+------+-------+
|  1 | tom    | NULL |    50 |
|  2 | jerry  | NULL |    60 |
|  3 | xiaoyu | NULL |    89 |
|  4 | wangwu | NULL |    78 |
|  5 | lisi   | NULL |    80 |
+----+--------+------+-------+
5 rows in set (0.01 sec)
上面是恢复的插入数据之前的表

再看 恢复之前的那张mysql_bin.000003 表

mysql> show binlog events in 'mysql_bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |   4 | Format_desc    |         2 |         123 | Server ver: 5.7.33-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids |         2 |         154 |                                       |
| mysql_bin.000003 | 154 | Anonymous_Gtid |         2 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 219 | Query          |         2 |         289 | BEGIN                                 |
| mysql_bin.000003 | 289 | Table_map      |         2 |         342 | table_id: 173 (yh.class1)             |
| mysql_bin.000003 | 342 | Write_rows     |         2 |         388 | table_id: 173 flags: STMT_END_F       |
| mysql_bin.000003 | 388 | Xid            |         2 |         419 | COMMIT /* xid=917 */                  |
| mysql_bin.000003 | 419 | Anonymous_Gtid |         2 |         484 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 484 | Query          |         2 |         554 | BEGIN                                 |
| mysql_bin.000003 | 554 | Table_map      |         2 |         607 | table_id: 173 (yh.class1)             |
| mysql_bin.000003 | 607 | Update_rows    |         2 |         669 | table_id: 173 flags: STMT_END_F       |
| mysql_bin.000003 | 669 | Xid            |         2 |         700 | COMMIT /* xid=919 */                  |
| mysql_bin.000003 | 700 | Anonymous_Gtid |         2 |         765 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 765 | Query          |         2 |         851 | drop database yh                      |
| mysql_bin.000003 | 851 | Rotate         |         2 |         898 | mysql_bin.000004;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

恢复

[root@localhost ~]# mysqlbinlog --stop-position=765 /opt/data/mysql_bin.000003 |mysql -uroot

mysql> use yh;
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 * from class1;
+----+--------+------+-------+
| id | name   | age  | score |
+----+--------+------+-------+
|  1 | tom    | NULL |    50 |
|  2 | jerry  | NULL |    60 |
|  3 | xiaoyu | NULL |    89 |
|  4 | wangwu | NULL |    78 |
|  5 | lisi   |   20 |    80 |
+----+--------+------+-------+
5 rows in set (0.00 sec)
恢复完成后就有了删除之前的数据

破解密码

登录mysql的密码在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-07 14:28:41
     password_lifetime: NULL
        account_locked: N
*************************** 2. row ***************************
                  Host: localhost
                  User: mysql.session
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: Y
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              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: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
      password_expired: N
 password_last_changed: 2021-05-07 14:14:39
     password_lifetime: NULL
        account_locked: Y
*************************** 3. row ***************************
                  Host: localhost
                  User: mysql.sys
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              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: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
      password_expired: N
 password_last_changed: 2021-05-07 14:14:39
     password_lifetime: NULL
        account_locked: Y
3 rows in set (0.00 sec)

编辑配置文件

[root@localhost ~]# vim /etc/my.cnf

添加以下内容
skip-grant-tables   //跳过授权表

重启服务

[root@localhost ~]# vim /etc/my.cnf
[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 = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

在用破解的密码登录就可以了。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值