mysql基础笔记（1）

一、Mysql安装

1.RPM安装

1.官网下载mysql rpm包
yum下载太慢了，故使用去官网下载rpm包，速度较快，下载地址：
https://downloads.mysql.com/archives/community/
选择全家桶（包含所有文件）：RPM Bundle(mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar)

2.确认环境（mariadb、mysql如果存在删除）

注：一定要将mariaDB包和自带mysql包卸载干净，否则安装时出现各种奇妙错误，导致失败。
rpm -qa | grep -i mariadb
rpm -qa | grep -i mysql                   //查看有没有安装mysql
rpm -e MySQL-client-5.6.38-1.el7.x86_64   //如果有，卸载旧版本Mysql及相关依赖包

删除服务
chkconfig --list | grep -i mysql          //查看服务
chkconfig --del mysql                     //删除服务

删除mysql分散的文件夹
whereis mysql                             //查出相应的mysql文件夹，也可以用find / -name *mysql*         
rm -rf /use/lib/mysql                     //删除

3.安装
#root用户下，按顺序安装
yum localinstall mysql-community-common-5.7.28-1.el7.x86_64.rpm
yum localinstall mysql-community-libs-*
yum localinstall mysql-community-client-5.7.28-1.el7.x86_64.rpm 
yum localinstall mysql-community-devel-5.7.28-1.el7.x86_64.rpm 
yum localinstall mysql-community-server-5.7.28-1.el7.x86_64.rpm

4.配置文件
#然后设置字符集，连接数等相关参数
 cp /etc/my.cnf /etc/my.cnf.bak
修改 /etc/my.cnf 添加：
character_set_server=utf8
init_connect='SET NAMES utf8'

5.其他
#启动mysql
systemctl start mysqld
systemctl enable mysqld

#防火墙开放3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent

#查看初始密码
cat /var/log/mysqld.log |grep generated

#登录
mysql -uroot -p

#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Test12345@';

#设置root用户可以远程登录
mysql> grant all privileges on *.* to 'root'@'%' identified by 'Ora@123456';
mysql> flush privileges;

2.通用二进制安装

--------------------------------------------------------------------------
软件下载地址：https://downloads.mysql.com/archives/community/
Product Version:
Operating System:    Linux -Generic
OS Version:          Linux -Genrtic (glibc 2.12)(x86,64-bit)
---------
下载文件：mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
--------------------------------------------------------------------------
1.创建软件目录
[root@db01 ~]# mkdir -p /app/

2.上传软件到此目录/app下
使用ftp上传文件mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz至目录/app下,或者直接wget下载。
[root@db01 ~]# cd /app
[root@db01 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

3.解压并重命
[root@db01 ~]# tar -zxvf /app/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /app
               tar -xvf  /app/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz -C /app   #mysql8.0
[root@db01 ~]# mv /app/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz /app/mysql

4.修改环境变量
[root@db01 ~]# cat >>/etc/profile <<'EOF'

#add for mysql
export PATH=/app/mysql/bin:$PATH
EOF
[root@db01 ~]# source /etc/profile

5.建立mysql用户和组(如果已有可忽略)
[root@db01 ~]# groupadd mysql
[root@db01 ~]# useradd -g mysql mysql -s /sbin/nologin

6.创建相关目录并修改权限
[root@db01 ~]# mkdir -p /app/mysqldata
[root@db01 ~]# mkdir -p /app/mysqllog
[root@db01 ~]# mkdir -p /app/mysqlsocket
[root@db01 ~]# chown -R mysql.mysql /app/*

7.初始化数据库
[root@db01 ~]# mysqld --initialize --user=mysql --basedir=/app/mysql  --datadir=/app/mysqldata  --explicit_defaults_for_timestamp
--------------------- 最后一行创建了一个临时密码 -----------------
2020-03-18T02:48:49.144915Z 1 [Note] A temporary password is generated for root@localhost: +jdY0e6hG)#l
----------------------------------------------------------------
	新特性重要说明:
		5.7开始,MySQL加入了全新的 密码的安全机制:
		   1.初始化完成后,会生成临时密码(显示到屏幕上,并且会往日志中记一份)
		   2.密码复杂度:长度:超过12位? 复杂度:字符混乱组合
		   3.密码过期时间180天
		
8.编辑参数文库my.cnf
[root@db01 ~]# vim /app/mysql/my.cnf
-------------  编辑文件内容如下 -----------------
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/app/mysqldata
server_id=6
port=3306
socket=/app/mysqlsocket/mysql.sock

[client]
port=3306
socket=/MySQL/my3306/run/mysql.sock

[mysql]
socket=/app/mysqlsocket/mysql.sock


-------------------------------------------------

更详细的参数文件内容如下：
-------------------------------------------------------------------------------
[mysqld]
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir=/u01/MySQL5.7                              #介质目录
datadir=/MySQL/my3306/data                         #数据目录
port=3306                                          #端口
pid-file = /MySQL/my3306/data/mysql.pid            #进程id 
user = mysql                                       #启动用户
socket=/MySQL/my3306/run/mysql.sock                #sock文件地址
bind-address = 0.0.0.0                             #绑定ip 这里表示绑定所有ip
server-id = 1                                      #用于复制环境钟标识实例,这个在复制环境里唯一
character-set-server = utf8                        #服务端默认字符集,很重要,错误设置会出现乱码
max_connections = 1000                             #允许客户端并发连接的最大数量
max_connect_errors = 6000                          #如果客户端尝试连接的错误数量超过这个参数设置的值，则服务器不再接受新的客户端连接。
open_files_limit = 65535                           #操作系统允许MySQL服务打开的文件数量。
table_open_cache = 128                             #所有线程能打开的表的数量
max_allowed_packet = 4M                            #网络传输时单个数据包的大小。
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30
log_error = /MySQL/my3306/data/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /MySQL/my3306/data/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
#lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800

[client]
port=3306
socket=/MySQL/my3306/run/mysql.sock

[mysql]
socket=/MySQL/my3306/run/mysql.sock

-------------------------------------------------------------------------------

9.复制my.inf及mysql.server
[root@db01 ~]# cp /app/mysql/my.cnf /etc/my.cnf
[root@db01 ~]# cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld

10.编辑 /etc/init.d/mysqld
将 "basedir="、"datadir=" 修改为 "basedir=/app/mysql"、"datadir=/app/mysqldata"
[root@db01 ~]# sed -i "s/^basedir=*/basedir=/app/mysql/" /etc/init.d/mysqld
[root@db01 ~]# sed -i "s/^datadir=*/datadir=/app/mysqldata/" /etc/init.d/mysqld


11.启动数据库
[root@db01 ~]# service mysqld start

12.连接数据库并修改密码
[root@db01 ~]# mysql -uroot -p
mysql> set PASSWORD=PASSWORD('root');
mysql> flush privileges;

13.配置mysql开机启动
[root@db01 ~]# chkconfig mysqld on
[root@db01 ~]# chkconfig

14.配置使用systemd管理mysql
--------------------------------------------------------------
注意：将原来模式启动mysqld先关闭，然后再用systemd管理。使用其中一种。 
--------------------------------------------------------------
[root@db01 ~]# cat >>/etc/systemd/system/mysqld.service <<'EOF'
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

[root@db01 ~]# systemctl systemctl daemon-reload    #重新加载systemctl
[root@db01 ~]# systemctl start mysqld
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# systemctl status mysqld
[root@db01 ~]# systemctl restart mysqld

3.多实例安装

1.准备多个目录
[root@db01 ~]# mkdir -p /app/mysqldata/330{7,8,9}/data

2.准备配置文件
[root@db01 ~]# cat > /app/mysqldata/3307/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/app/mysqldata/3307/data
socket=/app/mysqldata/3307/mysql.sock
log_error=/app/mysqldata/3307/mysql.log
port=3307
server_id=7
log_bin=/app/mysqldata/3307/mysql-bin

[client]
port=3307
socket=/app/mysqldata/3307/mysql.sock
EOF

[root@db01 ~]# cat > /app/mysqldata/3308/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/app/mysqldata/3308/data
socket=/app/mysqldata/3308/mysql.sock
log_error=/app/mysqldata/3308/mysql.log
port=3308
server_id=8
log_bin=/app/mysqldata/3308/mysql-bin

[client]
port=3308
socket=/app/mysqldata/3308/mysql.sock
EOF

[root@db01 ~]# cat > /app/mysqldata/3309/my.cnf <<EOF
[mysqld]
basedir=/app/mysqldata/mysql
datadir=/app/mysqldata/3309/data
socket=/app/mysqldata/3309/mysql.sock
log_error=/app/mysqldata/3309/mysql.log
port=3309
server_id=9
log_bin=/app/mysqldata/3309/mysql-bin

[client]
port=3309
socket=/app/mysqldata/3309/mysql.sock
EOF

3.初始化三套数据
[root@db01 ~]# mv /etc/my.cnf /etc/my.cnf.bak               #删除/etc目录下的my.cnf

[root@db01 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/app/mysqldata/3307/data --basedir=/app/mysql

[root@db01 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/app/mysqldata/3308/data --basedir=/app/mysql

[root@db01 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/app/mysqldata/3309/data --basedir=/app/mysql

4. systemd管理多实例
[root@db01 ~]# cd /etc/systemd/system
[root@db01 ~]# cp mysqld.service mysqld3307.service
[root@db01 ~]# cp mysqld.service mysqld3308.service
[root@db01 ~]# cp mysqld.service mysqld3309.service
如果没有带或者：
[root@db01 ~]# cp mysqld.service mysqld3307.service
[root@db01 ~]# vim mysqld3307.service
ExecStart=/app/mysql/bin/mysqld  --defaults-file=/app/mysqldata/3307/my.cnf
[root@db01 ~]# vim mysqld3308.service
ExecStart=/app/mysql/bin/mysqld  --defaults-file=/app/mysqldata/3308/my.cnf
[root@db01 ~]# vim mysqld3309.service
ExecStart=/app/mysql/bin/mysqld  --defaults-file=/app/mysqldata/3309/my.cnf

5.授权
[root@db01 ~]# chown -R mysql.mysql /app/mysqldata/*

6.启动
[root@db01 ~]# systemctl start mysqld3307.service
[root@db01 ~]# systemctl start mysqld3308.service
[root@db01 ~]# systemctl start mysqld3309.service

7.验证多实例
[root@db01 ~]# netstat -lnp|grep 330
[root@db01 ~]# mysql -S /app/mysqldata/3307/mysql.sock -e "select @@server_id"
[root@db01 ~]# mysql -S /app/mysqldata/3308/mysql.sock -e "select @@server_id"
[root@db01 ~]# mysql -S /app/mysqldata/3309/mysql.sock -e "select @@server_id"

alt

二、Mysql常用命令

1.常用命令

1.忘记密码

[root@db01 ~]# mysqld --skip-grant-tables&
mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> flush privileges;
mysql> shutdown       #关闭mysql，或者使用：[root@db01 ~]# pkill mysqld
[root@db01 ~]# service mysqld start		#二选一
[root@db01 ~]# systemctl start mysqld	#二选一

3.常用命令---系统

[root@db01 ~]# mysqladmin -uroot -p password 123		#修改密码， 不建议使用，可能泄露密码。

#初始化数据库：
mysqld --intialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql 
mysqld --intialize --user=mysql --basedir=/app/mysql --datadir=/data/mysql 

#连接
[root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306
[root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;"
[root@db01 ~]# mysql -uroot -p -e "select @@socket;"
[root@db01 ~]# mysql -uroot -p <world.sql
----------  说明 ----------------------------
-u                   用户
-p                   密码
-h                   IP
-P                   端口
-S                   socket文件
-e                   免交互执行命令
<                    导入SQL脚本
---------- --- ----------------------------

#启动方式
mysql.server           --------->  mysqld_safe         -------->  mysqld
[service mysqld start]             ./bin/mysqld_safe &
------------------ 说明 --------------------------------------------
以上多种方式，都可以单独启动MySQL服务
mysqld_safe和mysqld一般是在临时维护时使用。
另外，从Centos 7系统开始，支持systemd直接调用mysqld的方式进行启动数据库
---------------------------------------------------------------------

#初始配置文件
[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
注:
默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。
但是，如果启动时加入了--defaults-file=xxxx时，以上的所有文件都不会读取.
配置文件的书写方式:
----------------------------
[标签]
配置项=xxxx

标签类型：服务端、客户端
服务器端标签：
[mysqld]
[mysqld_safe]
[server]

客户端标签：
[mysql]
[mysqldump]
[client]

配置文件的示例展示：
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\d]>
----------------------------------------

3.常用命令---mysql

#用户
select user,password,host from mysql.user;                #5.7以前
select user,authentication_string,host from mysql.user;   #5.7以后
show processlist;    								   #通过以下语句可以查看到连接线程基本情况
create user test@'10.0.0.%' identified by '123';		 #创建用户
alter user test@'10.0.0.%' identified by '456';			 #修改密码
drop user oldboy@'10.0.0.%';						    #删除用户

#权限
grant all on wordpress.* to wordpress@'10.0.0.%' identified  by '123';			
grant select ,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';
show grants for app@'10.0.0.%';								#查看权限
revoke  delete on app.*  from app@'10.0.0.%';				 #回收权限				

----------  说明 ----------------------------
-- 1 --
ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能
-- 2 --
*.*                  ---->管理员用户
wordpress.*          ---->开发和应用用户
wordpress.t1
-- 3 --
8.0在grant命令添加新特性
建用户和授权分开了
grant 不再支持自动创建用户了，不支持改密码
授权之前，必须要提前创建用户。
--------------------------------------------

4.show命令

----------------------------------------------------------------
show  databases;                          #查看所有数据库
show tables;                                          #查看当前库的所有表
SHOW TABLES FROM                        #查看某个指定库下的表
show create database world                #查看建库语句
show create table world.city                #查看建表语句
show grants for  root@'localhost'       #查看用户的权限信息
show  charset；                                   #查看字符集
show collation                                      #查看校对规则
show processlist;                                  #查看数据库连接情况
show index from                                 #表的索引情况
show status                                         #数据库状态查看
SHOW STATUS LIKE '%lock%';         #模糊查询数据库某些状态
SHOW VARIABLES                             #查看所有配置信息
SHOW variables LIKE '%lock%';          #查看部分配置信息
show engines                                       #查看支持的所有的存储引擎
show engine innodb statusG               #查看InnoDB引擎相关的状态信息
show binary logs                                    #列举所有的二进制日志
show master status                                 #查看数据库的日志位置信息
show binlog evnets in                             #查看二进制日志事件
show slave status G                             #查看从库状态
SHOW RELAYLOG EVENTS               #查看从库relaylog事件信息
desc  (show colums from city)               #查看表的列定义信息
------------------------------------------------------------------
http://dev.mysql.com/doc/refman/5.7/en/show.html

3.information_schema.tables视图

DESC information_schema.TABLES
TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）

#查询整个数据库中所有库和所对应的表信息
SELECT table_schema,GROUP_CONCAT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

#统计所有库下的表个数
SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema

#查询所有innodb引擎的表及所在的库
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';

#统计world数据库下每张表的磁盘空间占用
SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")  AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA='world';

#统计所有数据库的总的磁盘空间占用
SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

#生成整个数据库下的所有表的单独备份语句
模板语句：
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;

CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

#107张表，都需要执行以下2条语句
ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';

2.日志管理

1.错误日志

记录启动关闭日常运行过程中,状态信息,警告,错误
默认就是开启的:  /数据路径下/hostname.err
手工设定:
mysql> select @@log_error;
vim /etc/my.cnf
log_error=/var/log/mysql.log
log_timestamps=system
重启生效
show variables like 'log_error';

主要关注[ERROR],看上下文

2.binlog二进制日志

作用：
(1)备份恢复必须依赖二进制日志
(2)主从环境必须依赖二进制日志

binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。
DDL ：原封不动的记录当前DDL(statement语句方式)。
DCL ：原封不动的记录当前DCL(statement语句方式)。
DML ：只记录已经提交的事务DML

#DML三种记录方式
binlog_format（binlog的记录格式）参数影响
（1）statement（5.6默认）SBR(statement based replication) ：语句模式原封不动的记录当前DML。
（2）ROW(5.7 默认值) RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)
（3）mixed（混合）MBR(mixed based replication)模式  ：以上两种模式的混合

binlog配置 (5.7必须加server_id)
vim /etc/my.cnf
-------------------
server_id=6        ----->5.6中，单机可以不需要此参数              
log_bin=/data/binlog/mysql-bin
binlog_format=row
--------------------
/etc/init.d/mysqld restart  #重启数据库生效

注意：MySQL默认是没有开启二进制日志的。
基础参数查看:
开关:
mysql> select @@log_bin;
日志路径及名字
[(none)]> select @@log_bin_basename;
服务ID号:
[(none)]> select @@server_id;
二进制日志格式:
[(none)]> select @@binlog_format;
双一标准之二:
[(none)]> select @@sync_binlog;

#binlog 日志恢复
mysql> create database bindb charset utf8;
mysql> use bindb;
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> insert into t1 values(4),(5),(6);
mysql> commit;
mysql> insert into t1 values(7),(8),(9);
mysql> commit;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |     1288 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000006';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000006 |    4 | Format_desc    |         7 |         123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000006 |  123 | Previous_gtids |         7 |         154 |                                       |
| mysql-bin.000006 |  154 | Anonymous_Gtid |         7 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000006 |  219 | Query          |         7 |         329 | create database bindb charset utf8    |
| mysql-bin.000006 |  329 | Anonymous_Gtid |         7 |         394 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000006 |  394 | Query          |         7 |         493 | use `bindb`; create table t1(id int)  |
| mysql-bin.000006 |  493 | Anonymous_Gtid |         7 |         558 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000006 |  558 | Query          |         7 |         631 | BEGIN                                 |
| mysql-bin.000006 |  631 | Table_map      |         7 |         677 | table_id: 108 (bindb.t1)              |
| mysql-bin.000006 |  677 | Write_rows     |         7 |         727 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000006 |  727 | Xid            |         7 |         758 | COMMIT /* xid=22 */                   |
| mysql-bin.000006 |  758 | Anonymous_Gtid |         7 |         823 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000006 |  823 | Query          |         7 |         896 | BEGIN                                 |
| mysql-bin.000006 |  896 | Table_map      |         7 |         942 | table_id: 108 (bindb.t1)              |
| mysql-bin.000006 |  942 | Write_rows     |         7 |         992 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000006 |  992 | Xid            |         7 |        1023 | COMMIT /* xid=24 */                   |
| mysql-bin.000006 | 1023 | Anonymous_Gtid |         7 |        1088 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000006 | 1088 | Query          |         7 |        1161 | BEGIN                                 |
| mysql-bin.000006 | 1161 | Table_map      |         7 |        1207 | table_id: 108 (bindb.t1)              |
| mysql-bin.000006 | 1207 | Write_rows     |         7 |        1257 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000006 | 1257 | Xid            |         7 |        1288 | COMMIT /* xid=26 */                   |
| mysql-bin.000006 | 1288 | Anonymous_Gtid |         7 |        1353 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000006 | 1353 | Query          |         7 |        1448 | drop database bindb                   |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
23 rows in set (0.00 sec)

#起点：219  终点：1353 

mysql> select @@log_bin_basename;
+-------------------------------+
| @@log_bin_basename            |
+-------------------------------+
| /app/mysqldata/3307/mysql-bin |
+-------------------------------+
1 row in set (0.00 sec)
[root@localhost 3307]# mysqlbinlog --start-position=219 --stop-position=1353 /app/mysqldata/3307/mysql-bin.000006 >/tmp/bin.sql


mysql> set sql_log_bin=0         #临时关闭当前会话不记录binlog，不影响其他会话
mysql> source /tmp/bin.sql       #执行sql恢复数据库
mysql> set sql_log_bin=1         #再次开启记录binlog 
mysql> user bindb				#验证
mysql> select * from bindb.t1;	 #验证