mysql进阶

1. 二进制格式mysql安装

//下载二进制格式的mysql软件包
[root@yh ~]# cd /usr/src/
[root@yh 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@yh ~]# ls /usr/src/
debug  kernels  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz 
[root@yh ~]# cd /usr/src/
[root@yh src]# groupadd -r mysql
[root@yh src]# useradd -M -s /sbin/nologin -g mysql mysql



//解压软件至/usr/local/
[root@yh src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@yh src]# cd /usr/local/
[root@yh local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.22-linux-glibc2.12-x86_64  sbin  share  src
[root@yh local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.22-linux-glibc2.12-x86_64/"
[root@yh local]# ll
总用量 0
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. 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 root root  36 4月  29 12:02 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 4月  29 12:01 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 3月  14 18:39 share
drwxr-xr-x. 2 root root   6 3月  10 2016 src


//修改目录/usr/local/mysql的属主属组
[root@yh local]# chown -R mysql.mysql /usr/local/mysql
[root@yh local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 4月  29 12:02 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/


//添加环境变量
[root@yh local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@yh local]# . /etc/profile.d/mysql.sh
[root@yh local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin



//建立数据存放目录
[root@yh mysql]# mkdir /opt/data
[root@yh mysql]# chown -R mysql.mysql /opt/data/
[root@yh mysql]# ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql   6 4月  29 12:04 data



//初始化数据库
[root@yh ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2019-04-29T04:05:32.038502Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-04-29T04:05:32.499955Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-04-29T04:05:32.613609Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-04-29T04:05:32.669108Z 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: 089a6629-6a34-11e9-b210-000c295c6114.
2019-04-29T04:05:32.672769Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-04-29T04:05:32.673879Z 1 [Note] A temporary password is generated for root@localhost: rFpVEXawu9#:
//请注意，这个命令的最后会生成一个临时密码，此处密码是 rFpVEXawu9
//再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到





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




//启动mysql
[root@yh ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/opt/data/yh.err'.
 SUCCESS! 
[root@yh ~]# ps -ef |grep mysql
root       2816      1  0 12:07 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql      2994   2816  3 12:07 pts/1    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=yh.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root       3024   2380  0 12:07 pts/1    00:00:00 grep --color=auto mysql
[root@yh ~]# 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@yh ~]# /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> set password = password('yh123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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


mysql> use yh;
Database changed
mysql> show tables;
+--------------+
| Tables_in_yh |
+--------------+
| student      |
+--------------+


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



//备份yh库的student表
[root@yh ~]#  mysqldump -uroot -p -h127.0.0.1 yh student > table-201904291400.sql
Enter password: 
[root@yh ~]# ls
all-201904291356.sql  anaconda-ks.cfg   table-201904291400.sql





//备份yh库
[root@yh ~]# mysqldump -uroot -p -h127.0.0.1 --databases yh > yh-201904291402.sql
Enter password: 
[root@yh ~]# ls
all-201904291356.sql  anaconda-ks.cfg   table-201904291400.sql  yh-201904291402.sql




//模拟误删yh数据库
mysql> drop database yh;
Query OK, 1 row affected (0.05 sec)

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

3.3 xtrabackup备份命令

Xtrabackup

• percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具

特点：

• 备份还原过程快速、可靠
• 备份过程不会打断正在执行的事务
• 能够基于压缩等功能节约磁盘空间和流量
• 自动实现备份检验
• 开源，免费

Xtrabackup2.2版之前包括4个可执行文件：

• Innobackupex: perl 脚本
• Xtrabackup: C/C++ 编译的二进制
• Xbcrypt:加解密
• Xbstream:支持并发写的流文件格式

xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 mysqld server 没有交互；innobackupex 脚本用来备份非InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和 mysqld server 发送命令进行交互，如加读锁（FTWRL）、获取位点（SHOW SLAVESTATUS）等。即innobackupex是在xtrabackup 之上做了一层封装实现的。
虽然目前一般不用 MyISAM 表，只是mysql 库下的系统表是MyISAM 的，因此备份基本都通过 innobackupex 命令进行

xtrabackup的特性：

• 使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：
• xtrabackup_checkpoints：备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
• xtrabackup_binlog_info：mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置
• xtrabackup_binlog_pos_innodb：二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当position
• xtrabackup_binary：备份中用到的xtrabackup的可执行文件
• backup-my.cnf：备份命令用到的配置选项信息在使用innobackupex进行备份时，还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录；innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据

xtrabackup用法

备份：innobackupex [option] BACKUP-ROOT-DIR
选项说明：
--user：该选项表示备份账号
--password：该选项表示备份的密码
--host：该选项表示备份数据库的地址
--databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置
--incremental：该选项表示创建一个增量备份，需要指定--incremental-
basedir
--incremental-basedir：该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录，与--incremental同时使用
--incremental-dir：该选项表示增量备份的目录
--include=name：指定表名，格式：databasename.tablename

Prepare：innobackupex --apply-log [option] BACKUP-DIR
选项说明：
--apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory：该选项表示和--apply-log选项一起使用，prepare 备份的时候，xtrabackup做crash recovery分配的内存大小，单位字节。也可(1MB,1M,1G,1GB)，推荐1G
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置
-export：表示开启可导出单独的表之后再导入其他Mysql中
--redo-only：这个选项在prepare base full backup，往其中merge增量备份时候使用

还原：innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
选项说明：
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本

注意事项:

• 1.datadir目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则–copy-backup选项不会覆盖
• 2.在restore之前,必须shutdown MySQL实例，你不能将一个运行中的实例restore到datadir目录中
• 3.由于文件属性会被保留，大部分情况下你需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户chown -R mysql:mysql /data/mysql以上需要在用户调用innobackupex之前完成–force-non-empty-directories：指定该参数时候，使得innobackupex --copy-back或–move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果–copy-back和–move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败!

3.4 mysql数据恢复

//恢复yh数据库
[root@yh ~]# ls
all-201904291356.sql  anaconda-ks.cfg  table-201904291400.sql  yh-201904291402.sql

[root@yh ~]# mysql -uroot -p -h127.0.0.1 <  yh-201904291402.sql
Enter password:
[root@yh ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yh        |
+--------------------+






//恢复yh数据库的student表
mysql> use yh;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source table-201904291400.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_yh |
+--------------+
| student      |
+--------------+





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

mysql> drop database yh;
Query OK, 1 rows affected (0.01 sec)

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

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

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 -pyh123456
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                |
| yh          |
+--------------------+
5 rows in set (0.00 sec)

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

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



//完全备份
[root@localhost ~]# mysqldump -uroot -pyh123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201904291400.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-201904291400.sql
-rw-------. 1 root root   1259 1月   7 16:39 anaconda-ks.cfg



//增加新内容
[root@localhost ~]# mysql -uroot -pyh123456
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 yh;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into student values(3,'hehe',20),(4,'xixi',50);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

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

mysql> select * from student;
+------+-------+------+
| 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 -pyh123456 -e 'drop database yh;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pyh123456 -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 -pyh123456 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 -pyh123456 < all-201904291400.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pyh123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yh          |
+--------------------+
[root@localhost ~]# mysql -uroot -pyh123456 -e 'show tables from yh;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Tables_in_wangqing |
+--------------------+
| student            |
+--------------------+
[root@localhost ~]# mysql -uroot -pyh123456 -e 'select * from yh.studdent;'
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 |
+------+-------+------+

恢复差异备份

[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 -pyh123456
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 -pyh123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pyh123456 -e 'select * from yh.student;'
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 |
+------+-------+------+