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 status\G #查看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; #验证
