mysql进阶

二进制格式mysql安装

#下载二进制格式的mysql软件包
[root@wnz ~]# cd /usr/src/
[root@wnz src]# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
--2020-05-28 00:25:08--  https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 23.36.249.17
Connecting to cdn.mysql.com (cdn.mysql.com)|23.36.249.17|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 660017902 (629M) [application/x-tar-gz]
Saving to: ‘mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz’

100%[===============================>] 660,017,902 1.27MB/s   in 9m 38s 

2020-05-28 00:34:53 (1.09 MB/s) - ‘mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz’ saved [660017902/660017902]


#创建用户和组
[root@wnz src]# groupadd -r mysql
[root@wnz src]# useradd -M -s /sbin/nologin -g mysql mysql


#解压mysql二进制文件
[root@wnz src]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@wnz ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.30-linux-glibc2.12-x86_64  share
备注：必须解压到/usr/local目录下


#创建mysql软连接
[root@wnz local]# ln -sv mysql-5.7.30-linux-glibc2.12-x86_64/ mysql
‘mysql’ -> ‘mysql-5.7.30-linux-glibc2.12-x86_64/’
[root@wnz local]# ll
total 0
drwxr-xr-x. 2 root root   6 Apr 11  2018 bin
drwxr-xr-x. 2 root root   6 Apr 11  2018 etc
drwxr-xr-x. 2 root root   6 Apr 11  2018 games
drwxr-xr-x. 2 root root   6 Apr 11  2018 include
drwxr-xr-x. 2 root root   6 Apr 11  2018 lib
drwxr-xr-x. 2 root root   6 Apr 11  2018 lib64
drwxr-xr-x. 2 root root   6 Apr 11  2018 libexec
lrwxrwxrwx. 1 root root  36 May 28 00:49 mysql -> mysql-5.7.30-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 May 28 00:47 mysql-5.7.30-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Apr 11  2018 sbin
drwxr-xr-x. 5 root root  49 May 26 00:45 share
drwxr-xr-x. 2 root root   6 Apr 11  2018 src


#修改目录/usr/local/mysql的属主属组
[root@wnz local]# chown -R mysql.mysql mysql*
[root@wnz local]# ll
total 0
drwxr-xr-x. 2 root  root    6 Apr 11  2018 bin
drwxr-xr-x. 2 root  root    6 Apr 11  2018 etc
drwxr-xr-x. 2 root  root    6 Apr 11  2018 games
drwxr-xr-x. 2 root  root    6 Apr 11  2018 include
drwxr-xr-x. 2 root  root    6 Apr 11  2018 lib
drwxr-xr-x. 2 root  root    6 Apr 11  2018 lib64
drwxr-xr-x. 2 root  root    6 Apr 11  2018 libexec
lrwxrwxrwx. 1 mysql mysql  36 May 28 00:49 mysql -> mysql-5.7.30-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 May 28 00:47 mysql-5.7.30-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 Apr 11  2018 sbin
drwxr-xr-x. 5 root  root   49 May 26 00:45 share
drwxr-xr-x. 2 root  root    6 Apr 11  2018 src


#把执行文件bin放入 $PATH全局变量
[root@wnz ~]# vim /etc/profile.d/mysql.sh
[root@wnz ~]# cat /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@wnz ~]# . /etc/profile.d/mysql.sh
[root@wnz ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@wnz ~]# which mysql
/usr/local/mysql/bin/mysql


#建立数据存放目录
[root@wnz ~]# mkdir /opt/mysqldata
[root@wnz ~]# chown -R mysql.mysql /opt/mysqldata/
[root@wnz ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 May 28 01:39 mysqldata


#初始化数据库
[root@wnz ~]# mysqld --initialize --user=mysql --datadir=/opt/mysqldata/
2020-05-27T17:45:05.001586Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-27T17:45:07.520167Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-05-27T17:45:09.031550Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-05-27T17:45:09.802478Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: cf36f152-a041-11ea-be71-000c299fb654.
2020-05-27T17:45:09.803881Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-05-27T17:45:10.231182Z 0 [Warning] CA certificate ca.pem is self signed.
2020-05-27T17:45:10.618140Z 1 [Note] A temporary password is generated for root@localhost: aXsflUXii6:a
//请注意，这个命令的最后会生成一个临时密码，此处密码是aXsflUXii6:a
//再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到


#生成配置文件
[root@wnz ~]# > /etc/my.cnf
[root@wnz ~]# vim /etc/my.cnf
[root@wnz ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysqldata
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysqldata/mysql.pid
user = mysql
skip-name-resolve


#配置服务启动脚本
[root@wnz ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@wnz ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@wnz ~]# sed -ri 's#^(datadir=).*#\1/opt/mysqldata#g' /etc/init.d/mysqld


#启动mysql
[root@wnz ~]# service mysqld start
Starting MySQL.Logging to '/opt/mysqldata/wnz.err'.
 SUCCESS! 
[root@wnz ~]# ss -antl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128      *:22                   *:*                  
LISTEN      0      100    127.0.0.1:25                   *:*                  
LISTEN      0      80        [::]:3306                  [::]:*                  
LISTEN      0      128       [::]:22                    [::]:*                  
LISTEN      0      100      [::1]:25                    [::]:*                  
[root@wnz ~]# service mysqld status
 SUCCESS! MySQL running (10872)
[root@wnz ~]# chkconfig mysqld on    //设置开机自启动
[root@wnz ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off
netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off



#修改密码，使用临时密码登录
[root@wnz ~]# mysql -uroot -p'aXsflUXii6:a'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30

Copyright (c) 2000, 2020, 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> set password = password('wang123!');   //设置新密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql配置文件

mysql的配置文件为/etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

mysql常用配置文件参数：

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求

mysql数据库备份与恢复

数据库常用备份方案

• 全量备份

全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
实际应用中就是用一盘磁带对整个系统进行全量备份，包括其中的系统和所有数据。这种备份方式最大的好处就是只要用一盘磁带，就可以恢复丢失的数据。因此大大加快了系统或数据的恢复时间。然而它的不足之处在于，各个全备份磁带中的备份数据存在大量的重复信息；另外，由于每次需要备份的数据量相当大，因此备份所需时间较长。

• 增量备份

增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。
这种备份方式最显著的优点就是：没有重复的备份数据，因此备份的数据量不大，备份所需的时间很短。但增量备份的数据恢复是比较麻烦的。您必须具有上一次全备份和所有增量备份磁带（一旦丢失或损坏其中的一盘磁带，就会造成恢复的失败），并且它们必须沿着从全量备份到依次增量备份的时间顺序逐个反推恢复，因此这就极大地延长了恢复时间。

• 差异备份

差异备份是指在一次全备份后到进行差异备份的这段时间内，对那些增加或者修改文件的备份。
差异备份在避免了另外两种备份策略缺陷的同时，又具备了它们各自的优点。首先，它具有了增量备份需要时间短、节省磁盘空间的优势；其次，它又具有了全量备份恢复所需磁带少、恢复时间短的特点。系统管理员只需要两盘磁带，即全备份磁带与灾难发生前一天的差异备份磁带，就可以将系统恢复。

mysql备份工具mysqldump

#语法：
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
#常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

#备份整个数据库(全备)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE school;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)
[root@wnz ~]# ls
anaconda-ks.cfg
[root@wnz ~]# mysqldump -uroot -pwang123! --all-databases > all-202006142242.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wnz ~]# ls
all-202006142242.sql  anaconda-ks.cfg
[root@wnz ~]# file all-202006142242.sql 
all-202006142242.sql: UTF-8 Unicode text, with very long lines
[root@wnz ~]# less all-202006142242.sql   //可查看其内容


#备份school库的student表和teacher表
[root@wnz ~]# mysqldump -uroot -pwang123! school student teacher > table-202006142245.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wnz ~]# ls
all-202006142242.sql  anaconda-ks.cfg  table-202006142245.sql


#备份school库
[root@wnz ~]# mysqldump -uroot -pwang123! --databases school > school-202006142248.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wnz ~]# ls
all-202006142242.sql  school-202006142248.sql
anaconda-ks.cfg       table-202006142245.sql

mysql数据恢复

#模拟误删除school数据库
mysql> drop database school;
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)



#恢复school数据库
[root@wnz ~]# mysql -uroot -pwang123! < all-202006142242.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


#恢复school库的student表和teacher表
mysql> use school;
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> source table-202006142245.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)

差异备份与恢复

#开启MySQL服务器的二进制日志功能
[root@wnz ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysqldata
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysqldata/mysql.pid
user = mysql
skip-name-resolve

server-id=1      //设置服务器标识符
log-bin=mysql_bin    //开启二进制日志功能
[root@wnz ~]# service mysqld restart   //重启服务
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 




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

mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chensshou   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.01 sec)

mysql> select * from teacher;
+----+----------+------+--------+
| id | name     | age  | salary |
+----+----------+------+--------+
|  1 | xiaowang |   20 |  10000 |
|  2 | xiaonie  |   23 |  15000 |
|  3 | xiaozhou |   25 |  20000 |
|  4 | natasha  |   28 |  10000 |
+----+----------+------+--------+
4 rows in set (0.00 sec)


#对数据库进行完全备份
[root@wnz ~]#  mysqldump -uroot -pwang123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202006142330.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wnz ~]# ls
all-202006142330.sql  anaconda-ks.cfg



#增加新内容
mysql> use school;
Database changed
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chensshou   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

mysql> update student set age = 100 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |  100 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chensshou   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)



#模拟误删数据
mysql> drop database school;
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@wnz ~]# ll /opt/mysqldata/
total 122976
-rw-r-----. 1 mysql mysql       56 May 28 01:45 auto.cnf
-rw-------. 1 mysql mysql     1676 May 28 01:45 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 May 28 01:45 ca.pem
-rw-r--r--. 1 mysql mysql     1112 May 28 01:45 client-cert.pem
-rw-------. 1 mysql mysql     1680 May 28 01:45 client-key.pem
-rw-r-----. 1 mysql mysql      820 Jun 14 23:45 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jun 15 00:12 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jun 15 00:12 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 May 28 01:45 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jun 15 00:23 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jun 14 23:15 mysql
-rw-r-----. 1 mysql mysql      609 Jun 15 00:12 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 Jun 14 23:59 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jun 14 23:45 mysql.pid
drwxr-x---. 2 mysql mysql     8192 May 28 01:45 performance_schema
-rw-------. 1 mysql mysql     1680 May 28 01:45 private_key.pem
-rw-r--r--. 1 mysql mysql      452 May 28 01:45 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 May 28 01:45 server-cert.pem
-rw-------. 1 mysql mysql     1676 May 28 01:45 server-key.pem
drwxr-x---. 2 mysql mysql     8192 May 28 01:45 sys
-rw-r-----. 1 mysql mysql    15317 Jun 14 23:45 wnz.err
[root@wnz ~]# mysqldump -uroot -pwang123! flush-logs
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wnz ~]# ll /opt/mysqldata/
total 122976
-rw-r-----. 1 mysql mysql       56 May 28 01:45 auto.cnf
-rw-------. 1 mysql mysql     1676 May 28 01:45 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 May 28 01:45 ca.pem
-rw-r--r--. 1 mysql mysql     1112 May 28 01:45 client-cert.pem
-rw-------. 1 mysql mysql     1680 May 28 01:45 client-key.pem
-rw-r-----. 1 mysql mysql      820 Jun 14 23:45 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jun 15 00:12 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jun 15 00:12 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 May 28 01:45 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jun 15 00:23 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jun 14 23:15 mysql
-rw-r-----. 1 mysql mysql      609 Jun 15 00:12 mysql_bin.000002
-rw-r-----. 1 mysql mysql      178 Jun 15 00:12 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 Jun 14 23:59 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jun 14 23:45 mysql.pid
drwxr-x---. 2 mysql mysql     8192 May 28 01:45 performance_schema
-rw-------. 1 mysql mysql     1680 May 28 01:45 private_key.pem
-rw-r--r--. 1 mysql mysql      452 May 28 01:45 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 May 28 01:45 server-cert.pem
-rw-------. 1 mysql mysql     1676 May 28 01:45 server-key.pem
drwxr-x---. 2 mysql mysql     8192 May 28 01:45 sys
-rw-r-----. 1 mysql mysql    15317 Jun 14 23:45 wnz.err


#首先恢复完全备份
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |    //目前这里的age还不是修改后的
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chensshou   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.01 sec)



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    |         1 |         123 | Server ver: 5.7.30-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |         1 |         293 | BEGIN                                 |
| mysql_bin.000002 | 293 | Table_map      |         1 |         349 | table_id: 152 (school.student)        |
| mysql_bin.000002 | 349 | Update_rows    |         1 |         415 | table_id: 152 flags: STMT_END_F       |
| mysql_bin.000002 | 415 | Xid            |         1 |         446 | COMMIT /* xid=927 */                  |
| mysql_bin.000002 | 446 | Anonymous_Gtid |         1 |         511 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 511 | Query          |         1 |         609 | drop database school                  |
| mysql_bin.000002 | 609 | Rotate         |         1 |         658 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
9 rows in set (0.00 sec)
此处删除数据库的位置，对应的pos位置是511



#使用mysqlbinlog恢复差异备份
[root@wnz ~]# mysqlbinlog --stop-position=511 /opt/mysqldata/mysql_bin.000002 |mysql -uroot -pwang123!
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |  100 |   //此处已恢复成修改后
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chensshou   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

MySQL密码破解

密码破解步骤：
1.改配置文件，在配置文件中加入skip-grant-tables
2.重启数据库
3.登录数据库（此时不需要密码）
4.修改密码
5.还原配置文件
6.重启数据库

[root@wnz ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysqldata
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysqldata/mysql.pid
user = mysql
skip-name-resolve

skip-grant-tables      //跳过授权表


[root@wnz ~]# service mysqld restart      //重启数据库
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 


[root@wnz ~]# mysql              //无需密码即可登录
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> 

mysql> flush privileges;     //刷新
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';  //修改密码
Query OK, 0 rows affected (0.00 sec)

[root@wnz ~]# vim /etc/my.cnf     //还原配置文件
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysqldata
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysqldata/mysql.pid
user = mysql
skip-name-resolve
[root@wnz ~]# service mysqld restart     //重启数据库
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 


[root@wnz ~]# mysql -uroot -p123456    //用修改的密码登录成功
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>