mysql进阶

1.二进制格式mysql安装

1.下载mysql的二进制tar.gz压缩包并解压到/usr/src下面

[root@localhost ~]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# ls
apache.sh  a.sh  certificate.sh  haha  httpds.sh  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz  sf  yum.sh
[root@localhost ~]# tar -xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/lcoal

2.创建系统用户mysql

[root@localhost local]# groupadd -r -g 306 mysql
[root@localhost local]# useradd -r  -M -s /sbin/nologin -u 306 -g 306 mysql
[root@localhost ~]# id mysql
uid=1001(mysql) gid=306(mysql) 组=306(mysql)

3.给解压缩的mysql文件创建软连接并将其宿主和宿组改为mysql

[root@localhost ~]# cd /usr/local
[root@localhost local]# ln -s mysql-5.7.22-linux-glibc2.12-x86_64 mysql
[root@localhost local]# chown -R  mysql.mysql mysql*
[root@localhost local]# ll
总用量 0
drwxr-xr-x.  6 root  root   58 11月 13 10:33 apr
drwxr-xr-x.  5 root  root   43 11月 13 10:33 apr-util
drwxr-xr-x.  2 root  root    6 3月  10 2016 bin
drwxr-xr-x.  2 root  root    6 3月  10 2016 etc
drwxr-xr-x.  2 root  root    6 3月  10 2016 games
drwxr-xr-x. 13 root  root  152 11月 13 10:51 httpd
drwxr-xr-x.  2 root  root    6 3月  10 2016 include
drwxr-xr-x.  2 root  root    6 3月  10 2016 lib
drwxr-xr-x.  2 root  root    6 3月  10 2016 lib64
drwxr-xr-x.  2 root  root    6 3月  10 2016 libexec
lrwxrwxrwx.  1 mysql mysql  35 12月 13 09:15 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x.  9 mysql mysql 129 12月 13 09:14 mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x.  2 root  root    6 3月  10 2016 sbin
drwxr-xr-x.  5 root  root   49 10月 13 23:26 share
drwxr-xr-x.  2 root  root    6 3月  10 2016 src```

4.设置环境变量,让其用mysql就可以执行、usr/src/mysql/bin里的命令

[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/src/mysql/bin:/usr/local/httpd/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

5.创建数据存放目录/opt/data

[root@localhost ~]# mkdir /opt/data

6.初始化数据库，并将密码暂时放在/opt/pass下面

[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
2019-12-12T10:36:20.062467Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-12-12T10:36:20.062617Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2019-12-12T10:36:20.471107Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-12-12T10:36:20.563699Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-12-12T10:36:20.774205Z 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: 3c888bf4-1ccb-11ea-90ee-000c2931840c.
2019-12-12T10:36:20.798243Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-12-12T10:36:20.822238Z 1 [Note] A temporary password is generated for root@localhost: Tr.cEZh+I81u
[root@localhost ~]# echo 'Tr.cEZh+I81u' > /opt/pass

7.生成配置文件，先将原来/etc/my.cnf备份，然后重新写个my.cnf配置文件,并创建pid-file的存放目录

[root@localhost ~]# mv /etc/my.cnf{,-origin}
[root@localhost ~]# vim /etc/my.cnf
[root@localhost src]#  cat /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-resolv
[root@localhost support-files]# cd /opt/data
[root@localhost data]# mkdir mysql.pid
[root@localhost data]# ls
auto.cnf        ibdata1      ib_logfile1                mysql      performance_schema
ib_buffer_pool  ib_logfile0  localhost.localdomain.err  mysql.pid  sys

8.复制启动服务脚本到/etc/init.d/mysqld下面并将其下面的basedir=/usr/src/mysql
datadir=/opt/data（mysql安装位置以及数据库存放位置）

[root@localhost ~]# cd /usr/local/mysql/support-files/
[root@localhost support-files]# cp mysql.server /etc/init.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld

# description: A very fast and reliable SQL database engine.

# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Should-Start: ypbind nscd ldap ntpd xntpd
# Required-Stop: $local_fs $network $remote_fs
# Default-Start:  2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO

# If you install MySQL on some other places than /usr/local/mysql, then you
# have to do one of the following things for this script to work:
#
# - Run this script from within the MySQL installation directory
# - Create a /etc/my.cnf file with the following information:
#   [mysqld]
#   basedir=<path-to-mysql-installation-directory>
# - Add the above to any other configuration file (for example ~/.my.ini)
#   and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir variable
#   below.
#
# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.

# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.

basedir=/usr/local/mysql
datadir=/opt/data

9.启动mysql服务，3306端口已经开启

[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
 SUCCESS! 
[root@localhost ~]# ss -antl
State       Recv-Q Send-Q            Local Address:Port                           Peer Address:Port              
LISTEN      0      128                           *:111                                       *:*                  
LISTEN      0      128                           *:22                                        *:*                  
LISTEN      0      100                   127.0.0.1:25                                        *:*                  
LISTEN      0      25                            *:514                                       *:*                  
LISTEN      0      128                          :::111                                      :::*                  
LISTEN      0      128                          :::80                                       :::*                  
LISTEN      0      128                          :::22                                       :::*                  
LISTEN      0      100                         ::1:25                                       :::*                  
LISTEN      0      25                           :::514                                      :::*                  
LISTEN      0      80                           :::3306                                     :::*            

10.输入初始化密码然后进入数据库修改密码

[root@localhost ~]# cat pass
y#y2U3B5dBgr
[root@localhost ~]# mysql -uroot -p'y#y2U3B5dBgr'
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.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> set password=password(123456);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123456)' at line 1
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

2.mysql配置文件

1.免密码登录，将用户密码写到.my.cnf文件里

[root@localhost ~]# vim .my.cnf
[root@localhost ~]# cat .my.cnf
[client]
user=root
password=123456
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 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> quit
Bye

2.破解密码，在/etc/my.cnf加上skip-grant-tables

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /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

skip-grant-tables

3.然后就可以免密码登录了，然后修改密码为123

[root@localhost ~]# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 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> 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)
mysql> update user set authentication_string=password('123') where User='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> quit
Bye

4.然后在my.cnf的配置文件将skip-grant-tables删除，然后重启服务就可以用新密码进去

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /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
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost ~]# mysql -uroot -p123
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.22 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> quit
Bye

3.修改免密登录密码改为修改后的密码。

[root@localhost ~]# vim .my.cnf 
[root@localhost ~]# cat .my.cnf
[client]
user=root
password=123
[root@localhost ~]# mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.22 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> 

4.创建数据库和表格

[root@localhost ~]# mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.22 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> create database school;
Query OK, 1 row affected (0.01 sec)

mysql> use school;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50) not null,age tinyi,score vamysql> create table student(id int not null primary key auto_increment,name varchar(50) not null,age tinyint,score varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
| score | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> insert student(name,age,score) values('tom',20,30),('mafei',10,60),('haha',11,33);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------+------+-------+
| id | name  | age  | score |
+----+-------+------+-------+
|  1 | tom   |   20 | 30    |
|  2 | mafei |   10 | 60    |
|  3 | haha  |   11 | 33    |
+----+-------+------+-------+
3 rows in set (0.00 sec)

mysql> quit
Bye

4.将school的所有表格备份到root下面的school.sql，将shcool下面的student表格备份

[root@localhost ~]# mysqldump -uroot -p123 school>school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
ocalhost ~]# mysqldump -uroot -p123  school student>student.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
apache.sh  school.sql  student.sql

5.将整个数据库备份到root下面，将数据库school备份到root下面

[root@localhost ~]# mysqldump -uroot -p123  --all-databases > all-20191213.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20191213.sql  apache.sh  school.sql  student.sql
[root@localhost ~]# mysqldump -uroot -p123  --databases  school > school-20191213.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

6.删除数据库school然后备份

发现数据库school已经删除

[root@localhost ~]# mysql -uroot -p123 -e 'drop database school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

然后备份数据库，查看数据库school和数据库里的表格和内容都在已经恢复了

[root@localhost ~]# mysql -uroot -p123 < school-20191213.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
[root@localhost ~]# mysql -uroot -p123 -e 'show tables from school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
[root@localhost ~]# mysql -uroot -p123 -e 'select * from school.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+-------+
| id | name  | age  | score |
+----+-------+------+-------+
|  1 | tom   |   20 | 30    |
|  2 | mafei |   10 | 60    |
|  3 | haha  |   11 | 33    |
+----+-------+------+-------+

7.差异备份

1.先开启MySQL服务器的二进制日志功能，在/etc/my.cnf下面加上二进制日志功能，然后/opt/data就会多出日志功能

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /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 = 1
log-bin = mysql_bin
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost ~]# ls /opt/data
auto.cnf        ibdata1      ib_logfile1  localhost.localdomain.err  mysql_bin.000001  mysql.pid           school
ib_buffer_pool  ib_logfile0  ibtmp1       mysql                      mysql_bin.index   performance_schema  sys

2.然后全量备份一次，然后/opt/data下面的mysql_bin.000001就会变成2，日志记录已经更新

[root@localhost ~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20191213.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /opt/data
auto.cnf        ibdata1      ib_logfile1  localhost.localdomain.err  mysql_bin.000002  mysql.pid           school
ib_buffer_pool  ib_logfile0  ibtmp1       mysql                      mysql_bin.index   performance_schema  sys

3.进入表格修改表格内容，然后将数据库误删

[root@localhost ~]# mysql -uroot -p123
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 3
Server version: 5.7.22-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> 
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> select * from student;
+----+-------+------+-------+
| id | name  | age  | score |
+----+-------+------+-------+
|  1 | tom   |   20 | 30    |
|  2 | mafei |   10 | 60    |
|  3 | haha  |   11 | 33    |
+----+-------+------+-------+
3 rows in set (0.00 sec)

mysql> insert student(name,age,score) values('zhangfei',9,20);
Query OK, 1 row affected (0.00 sec)

mysql> update student set age =20 where name = 'mafei';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+----------+------+-------+
| id | name     | age  | score |
+----+----------+------+-------+
|  1 | tom      |   20 | 30    |
|  2 | mafei    |   20 | 60    |
|  3 | haha     |   11 | 33    |
|  4 | zhangfei |    9 | 20    |
+----+----------+------+-------+
4 rows in set (0.00 sec)

mysql> drop database school;
Query OK, 1 row affected (0.01 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

4. 然后刷新日志功能，就会多出一个日志文件

[root@localhost ~]# ll /opt/data
总用量 122956
-rw-r-----. 1 mysql mysql       56 12月 13 10:19 auto.cnf
-rw-r-----. 1 mysql mysql      725 12月 13 11:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 12月 13 12:10 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月 13 12:10 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月 13 10:19 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 12月 13 12:02 ibtmp1
-rw-r-----. 1 mysql mysql    28158 12月 13 11:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 12月 13 11:45 mysql
-rw-r-----. 1 mysql mysql      894 12月 13 12:10 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 12月 13 12:02 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 12月 13 11:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 12月 13 10:19 performance_schema
drwxr-x---. 2 mysql mysql     8192 12月 13 10:19 sys
[root@localhost ~]# mysqladmin -uroot -p123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data
总用量 122960
-rw-r-----. 1 mysql mysql       56 12月 13 10:19 auto.cnf
-rw-r-----. 1 mysql mysql      725 12月 13 11:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 12月 13 12:10 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月 13 12:10 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月 13 10:19 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 12月 13 12:02 ibtmp1
-rw-r-----. 1 mysql mysql    28158 12月 13 11:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 12月 13 11:45 mysql
-rw-r-----. 1 mysql mysql      941 12月 13 12:29 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 12月 13 12:29 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 12月 13 12:29 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 12月 13 11:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 12月 13 10:19 performance_schema
drwxr-x---. 2 mysql mysql     8192 12月 13 10:19 sys

5.然后首先全量备份

[root@localhost ~]# mysql -uroot < all-20191213.sql 
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
[root@localhost ~]# mysql -e 'select * from school.student;'
+----+-------+------+-------+
| id | name  | age  | score |
+----+-------+------+-------+
|  1 | tom   |   20 | 30    |
|  2 | mafei |   10 | 60    |
|  3 | haha  |   11 | 33    |
+----+-------+------+-------+

6.然后进数据库查看日志信息，找出删除数据库的前一步是什么

[root@localhost ~]# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.22-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 '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.22-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 |         352 | table_id: 139 (school.student)        |
| mysql_bin.000002 | 352 | Write_rows     |         1 |         405 | table_id: 139 flags: STMT_END_F       |
| mysql_bin.000002 | 405 | Xid            |         1 |         436 | COMMIT /* xid=454 */                  |
| mysql_bin.000002 | 436 | Anonymous_Gtid |         1 |         501 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 501 | Query          |         1 |         575 | BEGIN                                 |
| mysql_bin.000002 | 575 | Table_map      |         1 |         634 | table_id: 139 (school.student)        |
| mysql_bin.000002 | 634 | Update_rows    |         1 |         700 | table_id: 139 flags: STMT_END_F       |
| mysql_bin.000002 | 700 | Xid            |         1 |         731 | COMMIT /* xid=456 */                  |
| mysql_bin.000002 | 731 | Anonymous_Gtid |         1 |         796 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 796 | Query          |         1 |         894 | drop database school                  |
| mysql_bin.000002 | 894 | Rotate         |         1 |         941 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

mysql> show binlog events in 'mysql_bin.000002'\G;
*************************** 1. row ***************************
   Log_name: mysql_bin.000002
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.22-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql_bin.000002
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql_bin.000002
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: mysql_bin.000002
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 293
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql_bin.000002
        Pos: 293
 Event_type: Table_map
  Server_id: 1
End_log_pos: 352
       Info: table_id: 139 (school.student)
*************************** 6. row ***************************
   Log_name: mysql_bin.000002
        Pos: 352
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 405
       Info: table_id: 139 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: mysql_bin.000002
        Pos: 405
 Event_type: Xid
  Server_id: 1
End_log_pos: 436
       Info: COMMIT /* xid=454 */
*************************** 8. row ***************************
   Log_name: mysql_bin.000002
        Pos: 436
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 501
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 9. row ***************************
   Log_name: mysql_bin.000002
        Pos: 501
 Event_type: Query
  Server_id: 1
End_log_pos: 575
       Info: BEGIN
*************************** 10. row ***************************
   Log_name: mysql_bin.000002
        Pos: 575
 Event_type: Table_map
  Server_id: 1
End_log_pos: 634
       Info: table_id: 139 (school.student)
*************************** 11. row ***************************
   Log_name: mysql_bin.000002
        Pos: 634
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 700
       Info: table_id: 139 flags: STMT_END_F
*************************** 12. row ***************************
   Log_name: mysql_bin.000002
        Pos: 700
 Event_type: Xid
  Server_id: 1
End_log_pos: 731
       Info: COMMIT /* xid=456 */
*************************** 13. row ***************************
   Log_name: mysql_bin.000002
        Pos: 731
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 796
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 14. row ***************************
   Log_name: mysql_bin.000002
        Pos: 796
 Event_type: Query
  Server_id: 1
End_log_pos: 894
       Info: drop database school
*************************** 15. row ***************************
   Log_name: mysql_bin.000002
        Pos: 894
 Event_type: Rotate
  Server_id: 1
End_log_pos: 941
       Info: mysql_bin.000003;pos=4
15 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> quit
Bye

7.然后差量备份，数据库已经恢复到删除数据库之前的内容

[root@localhost ~]# mysqlbinlog --stop-position=796 /opt/data/mysql_bin.000002|mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.22-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> 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> select * from student;
+----+----------+------+-------+
| id | name     | age  | score |
+----+----------+------+-------+
|  1 | tom      |   20 | 30    |
|  2 | mafei    |   20 | 60    |
|  3 | haha     |   11 | 33    |
|  4 | zhangfei |    9 | 20    |
+----+----------+------+-------+
4 rows in set (0.00 sec)