mysql进阶


文章目录



1. 二进制格式mysql安装

//安装依赖包
[root@mysql ~]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel
Installing:
cmake x86_64 2.8.12.2-2.el7 myrepo 7.0 M
mariadb-devel x86_64 1:5.5.56-2.el7 myrepo 752 k
ncurses-devel x86_64 5.9-13.20130511.el7 myrepo 713 k
Installing for dependencies:
libarchive x86_64 3.1.2-10.el7_2 myrepo 319 k

Transaction Summary
================================================================================================
Install 3 Packages (+1 Dependent package)

Total download size: 8.8 M
.....
Installed:
cmake.x86_64 0:2.8.12.2-2.el7 mariadb-devel.x86_64 1:5.5.56-2.el7
ncurses-devel.x86_64 0:5.9-13.20130511.el7

Dependency Installed:
libarchive.x86_64 0:3.1.2-10.el7_2

Complete!


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


//下载二进制格式的mysql软件包
[root@mysql ~]# cd /usr/src/
[root@mysql 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]



//解压软件至/usr/local/
[root@mysql src]# ls
debug kernels mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@mysql src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@mysql ~]# ls /usr/local/
bin games lib libexec sbin src
etc include lib64 mysql-5.7.22-linux-glibc2.12-x86_64 share
[root@mysql ~]# cd /usr/local/
[root@mysql local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
‘mysql’ -> ‘mysql-5.7.22-linux-glibc2.12-x86_64/’
[root@mysql 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@mysql ~]# chown -R mysql.mysql /usr/local/mysql
[root@mysql ~]# 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@mysql ~]# ls /usr/local/mysql
bin COPYING docs include lib man README share support-files
[root@mysql ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@mysql ~]# . /etc/profile.d/mysql.sh
[root@mysql ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin



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



//初始化数据库
[root@mysql ~]# /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
//再次注意,这个密码是随机的,你的不会跟我一样,一定要记住这个密码,因为一会登录时会用到


//配置mysql
[root@mysql ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
‘/usr/local/include/mysql’ -> ‘/usr/local/mysql/include/’
[root@mysql ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@mysql ~]# ldconfig -v
ldconfig: Can't stat /libx32: No such file or directory
ldconfig: Path `/usr/lib' given more than once
ldconfig: Path `/usr/lib64' given more than once
ldconfig: Can't stat /usr/libx32: No such file or directory
/usr/lib64/mysql:
libmysqlclient.so.18 -> libmysqlclient_r.so
/usr/local/mysql/lib:
libmysqlclient.so.20 -> libmysqlclient.so.20.3.9
......
/lib/sse2: (hwcap: 0x0000000004000000)
/lib64/sse2: (hwcap: 0x0000000004000000)
/lib64/tls: (hwcap: 0x8000000000000000)
[root@mysql ~]# ldconfig -p |grep mysql
libmysqlclient.so.20 (libc6,x86-64) => /usr/local/mysql/lib/libmysqlclient.so.20
libmysqlclient.so.18 (libc6,x86-64) => /usr/lib64/mysql/libmysqlclient.so.18
libmysqlclient.so (libc6,x86-64) => /usr/lib64/mysql/libmysqlclient.so
libmysqlclient.so (libc6,x86-64) => /usr/local/mysql/lib/libmysqlclient.so


//生成配置文件
[root@mysql ~]# 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
EOF


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



//启动mysql
[root@mysql ~]# service mysqld start
Starting MySQL.. SUCCESS!
[root@mysql ~]# 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@mysql ~]# 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('1');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye

2. 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将无法正常处理连接请求

3. 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 |
| zjy |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zjy;
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_zjy |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)




//备份全部的库
[root@mysql ~]# mysqldump -uroot -p1 --all-databases > all-20190818.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ls
all-20190818.sql mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
anaconda-ks.cfg



//备份zjy库的student表
[root@mysql ~]# mysqldump -uroot -p1 zjy student > table-20190818.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ls
all-20190818.sql mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
anaconda-ks.cfg table-20190818.sql



//备份zjy这个库
[root@mysql ~]# mysql -uroot -p1 --database zjy > 201908181.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ls
201908181.sql anaconda-ks.cfg
20190818.sql mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
all-20190818.sql table-20190818.sql



//误删除了zjy库
mysql> drop database zjy;
Query OK, 1 row affected (0.22 sec)

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

3.3 mysql数据恢复

//恢复zjy数据库
[root@mysql ~]# mysql -uroot -p1 < all-20190818.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# mysql -uroot -p1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zjy |
+--------------------+







//恢复zjy里的student表
mysql> use zjy;
Database changed



//先要进zjy库里才可以恢复
mysql> source table-20190818.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)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_zjy |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)




//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zjy |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database zjy;
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@mysql ~]# mysql -uroot -p1 < all-20190818.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# mysql -uroot -p1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zjy |
+--------------------+

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@mysql ~]# mysql -uroot -p1
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 16
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zjy |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from zjy;
+---------------+
| Tables_in_zjy |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)

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




//完全备份
[root@mysql ~]# mysqldump -uroot -p1 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs >all-20190818.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ll
total 628712
-rw-r--r--. 1 root root 1251 Aug 19 00:09 all-20190818.sql
-rw-------. 1 root root 1525 Mar 22 03:01 anaconda-ks.cfg
-rw-r--r--. 1 root root 643790848 Aug 7 23:11 mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz




//增加新内容
mysql> create table A(id int,age int);
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> desc A;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
### 3.4.2. mysql差异备份恢复
mysql> insert into A(id,age)values(1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into A(id,age)values(2,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from A;
+------+------+
| id | age |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)

3.4.2. mysql差异备份恢复

模拟误删数据

mysql> drop database zjy;
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@mysql ~]# ll /opt/data/
total 123160
-rw-r-----. 1 mysql mysql 36886 Aug 11 02:12 128.err
-rw-r-----. 1 mysql mysql 56 Aug 7 23:20 auto.cnf
-rw-r-----. 1 mysql mysql 1224 Aug 19 00:32 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Aug 19 00:32 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug 19 00:32 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 7 23:20 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug 19 00:32 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Aug 19 00:05 mysql
-rw-r-----. 1 mysql mysql 154 Aug 19 00:32 mysql_bin.000001
-rw-r-----. 1 mysql mysql 19 Aug 19 00:32 mysql_bin.index
-rw-r-----. 1 mysql mysql 196174 Aug 19 00:32 mysql.err
-rw-r-----. 1 mysql mysql 5 Aug 19 00:32 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Aug 7 23:20 performance_schema
drwxr-x---. 2 mysql mysql 8192 Aug 7 23:20 sys

//刷新创建新的二进制日志
[root@mysql ~]# mysqladmin -uroot -p1 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# 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@mysql ~]# 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 zjy

//检查误删数据库的位置在什么地方
[root@mysql ~]# mysql -uroot -p1
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 (zjy.student) |
| 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 (zjy.student) |
| 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 | 791 | Query | 1 | 898 | drop database zjy | //此处就是删除数据库的位置,对应的pos位置是791
| mysql_bin.000001 | 898 | Rotate | 1 | 945 | mysql_bin.000002;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

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