mysql进阶管理-优快云博客

本文链接：https://blog.youkuaiyun.com/LiamYoko/article/details/106121379

mysql进阶

一. 二进制格式mysql安装
二. mysql配置文件
三. mysql数据库备份与恢复

一. 二进制格式mysql安装

//下载二进制格式的mysql软件包
[root@localhost ~]# cd /usr/src/
[root@localhost src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
--2018-08-13 23:56:27--  https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz [following]
......
Saving to: ‘mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz’

100%[=====================================>] 643,790,848 2.46MB/s   in 4m 20s

2018-08-14 00:00:50 (2.36 MB/s) - ‘mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz’saved [643790848/643790848]


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


//解压软件至/usr/local/
[root@localhost src]# ls
debug  kernels  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.22-linux-glibc2.12-x86_64  share
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
‘mysql’ -> ‘mysql-5.7.22-linux-glibc2.12-x86_64/’
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root   6 Mar 10  2016 bin
drwxr-xr-x. 2 root root   6 Mar 10  2016 etc
drwxr-xr-x. 2 root root   6 Mar 10  2016 games
drwxr-xr-x. 2 root root   6 Mar 10  2016 include
drwxr-xr-x. 2 root root   6 Mar 10  2016 lib
drwxr-xr-x. 2 root root   6 Mar 10  2016 lib64
drwxr-xr-x. 2 root root   6 Mar 10  2016 libexec
lrwxrwxrwx  1 root root  36 Aug 14 16:00 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x  9 root root 129 Aug 14 00:16 mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Mar 10  2016 sbin
drwxr-xr-x. 5 root root  49 Jun 13 19:03 share
drwxr-xr-x. 2 root root   6 Mar 10  2016 src


//修改目录/usr/local/mysql的属主属组
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql
[root@localhost ~]# ll /usr/local/mysql -d
lrwxrwxrwx 1 mysql mysql 36 Aug 14 16:00 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/



//添加环境变量
[root@localhost ~]# ls /usr/local/mysql
bin  COPYING  docs  include  lib  man  README  share  support-files
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# . /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin



//建立数据存放目录
[root@localhost mysql]# mkdir /opt/data
[root@localhost mysql]# chown -R mysql.mysql /opt/data/
[root@localhost mysql]# ll /opt/
total 0
drwxr-xr-x 2 mysql mysql 6 Aug 14 16:54 data



//初始化数据库
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2018-08-15T07:57:46.168380Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-15T07:57:50.542516Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-15T07:57:50.927286Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-15T07:57:51.071260Z 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: e8600890-a060-11e8-b1a2-000c294c50b4.
2018-08-15T07:57:51.074566Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-15T07:57:51.078089Z 1 [Note] A temporary password is generatedfor root@localhost: jtBzkkb=r5ik
//请注意，这个命令的最后会生成一个临时密码，此处密码是jtBzkkb=r5ik
//再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到





//生成配置文件
[root@localhost ~]# cat > /etc/my.cnf <<EOF
> [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
> EOF

[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 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld




//启动mysql
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
 SUCCESS! 
[root@localhost ~]# ps -ef|grep mysql
root       1521      1  0 01:58 pts/0    00:00:00 /bin/sh /usr/local/mysql/binmysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql      1699   1521  0 01:58 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root       1734   1301  0 01:59 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# 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      128        :::22                     :::*
LISTEN      0      100       ::1:25                     :::*
LISTEN      0      80         :::3306                   :::* 
 
 

//修改密码
//使用临时密码登录
[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
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> 

//设置新密码
mysql> set password = password('wangqing123!');
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数据库备份与恢复

3.1 数据库常用备份方案

数据库备份方案：

全量备份
增量备份
差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

3.2 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 |
| sys                |
| wangqingge         |
+--------------------+
5 rows in set (0.00 sec)

mysql> use wangqingge;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_wangqingge |
+----------------------+
| chenshuo             |
| guohui               |
| wangqing             |
+----------------------+
3 rows in set (0.00 sec)

[root@localhost ~]# ls
anaconda-ks.cfg
[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-201808131500.sql
Enter password:
[root@localhost ~]# ls
all-201808131500.sql  anaconda-ks.cfg




//备份wangqingge库的wangqing表和guohui表
[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 wangqingge wangqing guohui > table-201808131500.sql
Enter password:
[root@localhost ~]# ls
all-201808131500.sql  anaconda-ks.cfg  table-201808131500.sql




//备份wangqingge库
[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --databases wangqingge > wq-201808131500.sql
Enter password:
[root@localhost ~]# ls
all-201808131500.sql  table-201808131500.sql
anaconda-ks.cfg       wq-201808131500.sql



//模拟误删wangqingge数据库
mysql> drop database wangqingge;
Query OK, 3 rows affected (0.02 sec)

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

3.3 mysql数据恢复

//恢复wangqingge数据库
[root@localhost ~]# ls
all-201808131500.sql  table-201808131500.sql
anaconda-ks.cfg       wq-201808131500.sql
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 < all-201808131500.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wangqingge         |
+--------------------+






//恢复wangqingge数据库的wangqing表和guohui表
mysql> use wangqingge;
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-201808131500.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)

mysql> show tables;
+----------------------+
| Tables_in_wangqingge |
+----------------------+
| chenshuo             |
| guohui               |
| wangqing             |
+----------------------+
3 rows in set (0.00 sec)






//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wangqingge         |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database wangqingge;
Query OK, 3 rows affected (0.01 sec)

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

//恢复整个数据库
[root@localhost ~]# ls
all-201808131500.sql  table-201808131500.sql
anaconda-ks.cfg       wq-201808131500.sql
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 < all-201808131500.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wangqingge         |
+--------------------+

3.4 差异备份与恢复

3.4.1. mysql差异备份

开启MySQL服务器的二进制日志功能
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve

server-id=1         //设置服务器标识符
log-bin=mysql_bin    //开启二进制日志功能

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
对数据库进行完全备份
[root@localhost ~]# mysql -uroot -pwangqing123!
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-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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wangqing           |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from wangqing;
+--------------------+
| Tables_in_wangqing |
+--------------------+
| runtime            |
| student            |
+--------------------+
2 rows in set (0.01 sec)

mysql> select * from wangqing.runtime;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | tom   |   10 |
|    2 | jerry |   30 |
+------+-------+------+
2 rows in set (0.01 sec)

mysql> select * from wangqing.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 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)



//完全备份
[root@localhost ~]# mysqldump -uroot -pwangqing123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902211406.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll
总用量 792
-rw-r--r--. 1 root root 803946 2月  21 13:47 all-201902211406.sql
-rw-------. 1 root root   1259 1月   7 16:39 anaconda-ks.cfg



//增加新内容
[root@localhost ~]# mysql -uroot -pwangqing123!
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 4
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 wangqing;
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> insert into runtime values(3,'hehe',20),(4,'xixi',50);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from runtime;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | tom   |   10 |
|    2 | jerry |   30 |
|    3 | hehe  |   20 |
|    4 | xixi  |   50 |
+------+-------+------+
4 rows in set (0.00 sec)

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

mysql> select * from runtime;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | tom   |   10 |
|    2 | jerry |   30 |
|    3 | hehe  |   40 |
|    4 | xixi  |   50 |
+------+-------+------+
4 rows in set (0.00 sec)

3.4.2. mysql差异备份恢复

模拟误删数据
[root@localhost ~]# mysql -uroot -pwangqing123! -e 'drop database wangqing;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pwangqing123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
//由上可以看到wangqing这个数据库已被删除
刷新创建新的二进制日志
[root@localhost ~]# ll /opt/data/
总用量 188548
-rw-r-----. 1 mysql mysql       56 2月  20 10:11 auto.cnf
-rw-r-----. 1 mysql mysql      996 2月  21 14:54 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 2月  21 16:09 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月  21 16:09 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月  20 10:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月  21 14:54 ibtmp1
-rw-r-----. 1 mysql mysql     8304 2月  21 08:45 linux-node1.com.err
-rw-r-----. 1 mysql mysql    74620 2月  21 14:54 localhost.err
drwxr-x---. 2 mysql mysql     4096 2月  21 16:03 mysql
-rw-r-----. 1 mysql mysql      154 2月  21 16:09 mysql_bin.000001
-rw-r-----. 1 mysql mysql       19 2月  21 16:09 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 2月  20 10:11 performance_schema
drwxr-x---. 2 mysql mysql     8192 2月  20 10:11 sys

//刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -pwangqing123! flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
总用量 188552
-rw-r-----. 1 mysql mysql       56 2月  20 10:11 auto.cnf
-rw-r-----. 1 mysql mysql      996 2月  21 14:54 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 2月  21 16:17 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月  21 16:18 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月  20 10:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月  21 14:54 ibtmp1
-rw-r-----. 1 mysql mysql     8304 2月  21 08:45 linux-node1.com.err
-rw-r-----. 1 mysql mysql    74620 2月  21 14:54 localhost.err
drwxr-x---. 2 mysql mysql     4096 2月  21 16:16 mysql
-rw-r-----. 1 mysql mysql      945 2月  21 16:18 mysql_bin.000001
-rw-r-----. 1 mysql mysql      154 2月  21 16:18 mysql_bin.000002
-rw-r-----. 1 mysql mysql       38 2月  21 16:18 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 2月  20 10:11 performance_schema
drwxr-x---. 2 mysql mysql     8192 2月  20 10:11 sys
恢复完全备份
[root@localhost ~]# mysql -uroot -pwangqing123! < all-201902211406.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pwangqing123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wangqing           |
+--------------------+
[root@localhost ~]# mysql -uroot -pwangqing123! -e 'show tables from wangqing;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Tables_in_wangqing |
+--------------------+
| runtime            |
| student            |
+--------------------+
[root@localhost ~]# mysql -uroot -pwangqing123! -e 'select * from wangqing.runtime;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | tom   |   10 |
|    2 | jerry |   30 |
+------+-------+------+
[root@localhost ~]# mysql -uroot -pwangqing123! -e 'select * from wangqing.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| 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 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
恢复差异备份
[root@localhost ~]# ll /opt/data/
总用量 189572
-rw-r-----. 1 mysql mysql       56 2月  20 10:11 auto.cnf
-rw-r-----. 1 mysql mysql      996 2月  21 14:54 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 2月  21 16:20 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月  21 16:20 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月  20 10:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月  21 14:54 ibtmp1
-rw-r-----. 1 mysql mysql     8304 2月  21 08:45 linux-node1.com.err
-rw-r-----. 1 mysql mysql    74620 2月  21 14:54 localhost.err
drwxr-x---. 2 mysql mysql     4096 2月  21 16:20 mysql
-rw-r-----. 1 mysql mysql      945 2月  21 16:18 mysql_bin.000001
-rw-r-----. 1 mysql mysql   786443 2月  21 16:20 mysql_bin.000002
-rw-r-----. 1 mysql mysql       38 2月  21 16:18 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 2月  20 10:11 performance_schema
drwxr-x---. 2 mysql mysql     8192 2月  20 10:11 sys
drwxr-x---. 2 mysql mysql       96 2月  21 16:20 wangqing

//检查误删数据库的位置在什么地方
[root@localhost ~]# mysql -uroot -pwangqing123!
mysql> show binlog events in 'mysql_bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql_bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 219 | Query          |         1 |         295 | BEGIN                                 |
| mysql_bin.000001 | 295 | Table_map      |         1 |         353 | table_id: 330 (wangqing.runtime)      |
| mysql_bin.000001 | 353 | Write_rows     |         1 |         410 | table_id: 330 flags: STMT_END_F       |
| mysql_bin.000001 | 410 | Xid            |         1 |         441 | COMMIT /* xid=2628 */                 |
| mysql_bin.000001 | 441 | Anonymous_Gtid |         1 |         506 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 506 | Query          |         1 |         582 | BEGIN                                 |
| mysql_bin.000001 | 582 | Table_map      |         1 |         640 | table_id: 330 (wangqing.runtime)      |
| mysql_bin.000001 | 640 | Update_rows    |         1 |         698 | table_id: 330 flags: STMT_END_F       |
| mysql_bin.000001 | 698 | Xid            |         1 |         729 | COMMIT /* xid=2630 */                 |
| mysql_bin.000001 | 729 | Anonymous_Gtid |         1 |         794 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 794 | Query          |         1 |         898 | drop database wangqing                |     //此处就是删除数据库的位置，对应的pos位置是794
| mysql_bin.000001 | 898 | Rotate         |         1 |         945 | mysql_bin.000002;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=794 /opt/data/mysql_bin.000001 |mysql -uroot -pwangqing123!
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pwangqing123! -e 'select * from wangqing.runtime;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | tom   |   10 |
|    2 | jerry |   30 |
|    3 | hehe  |   40 |
|    4 | xixi  |   50 |
+------+-------+------+