基于mysqld_multi实现MySQL 5.7.24多实例多进程配置

MySQL多实例的原理

mysql多实例,简单理解就是在一台服务器上,mysql服务开启多个不同的端口(如3306、3307、3308)运行多个服务进程。这些 mysql 服务进程通过不同的 socket来监听不同的数据端口,进而互不干涉的提供各自的服务。

在同一台服务器上,mysql 多实例会去共用一套 mysql 应用程序,因此你在部署 mysql 的时候只需要部署一次mysql程序即可,无需多次部署。但是,mysql多实例之间会各自使用不同的 my.cnf配置文件、启动程序和数据文件。在提供服务方面,mysql多实例在逻辑上看起来是各自独立,互不干涉的,并且多个实例之间是根据配置文件的设定值,来获取相关服务器的硬件资源。

优点如下:

有效利用服务器资源
当单个服务器资源过剩时,可以充分利用剩余的资源来提供更多的服务
节约服务器资源
当公司资金紧张,但数据库又需要数据库之间各自提供服务时,并且还想使用主从同步等技术,此时多实例就再好不过了
方便后期架构扩展
当公司的某个项目才启动时,启动初期并不一定有很大的用户量,因此可以先用一组物理数据库服务器,在上面部署多个实例,方便后续架构扩展、迁移

缺点如下:
资源互相抢占问题
当某个服务实例并发很高或者有慢查询时,整个实例会消耗更多的内存、CPU和IO资源,这将导致服务器上的其它实例提供服务的质量下降
mysql 多实例在生产环境下的应用场景!

当一个公司业务访问量不太大,又想节俭成本,并且还希望不同业务的数据库服务能够各自尽量独立,提供服务能够互相不受影响。另外还需要应用主从同步等技术来提供数据库备份或读写分离服务,以及方便后期业务量增大时,数据库架构的扩展和迁移。此时,Mysql 多实例就再好不过了。比如,我们可以通过在 3 台服务器部署 6-9 个实例,然后交叉做主从同步备份及读写分离,来实现 6-9 台服务器才能够达到的效果

公司业务访问量不是太大的时候,服务器的资源基本都是过剩状态。此时就很适合 mysql 多实例的应用。如果对 SQL语句优化做的比较好,mysql 多实例是一个很值得去使用的技术。即使后期业务并发很大,只要合理分配好系统资源,也不会有太大的问题

为了规避 mysql 对 SMP 架构不支持的缺陷,我们可以使用 mysql 多实例绑定处理器的办法(NUMA处理器必须支持,不过现在大部分处理器都支持的)将不同的数据库分配到不同的实例上提供数据服务;

传统游戏行业的 MMO/MMORPG以及Web Game,会将每个服都对应一个数据库,而且可能经常要做很多数据查询和数据订正工作。此时,为了减少维护而出错的概率,我们也可以采用多实例的部署方式,按区的概念来分配数据库。

Mysql多实例实现的3种方式

1、基于多配置文件

通过使用多个配置文件来启动不同的进程,以此来实现多实例。

优点:逻辑简单,配置简单

缺点:管理起来不方便

2、基于mysqld_multi
通过官方自带的 mysqld_multi 工具,使用单独配置文件来实现多实例

优点: 便于集中管理管理

缺点: 不方便针对每个实例配置进行定制

3、基于IM
使用 MySQL 实例管理器(MYSQLMANAGER),这个方法好像比较好不过也有点复杂

优点:便于集中管理

缺点:耦合度高。IM一挂,实例全挂/
不方便针对每个实例配置进行定制

MySQL本身就可以通过多实例方式运行,只要修改启动脚本和配置文件,把端口、basedir、datadir 文件夹分开后,多个实例的运行就会互不影响。但是这种方式操作起来太过繁杂,所以MySQL官方提供了一个mysqld_multi 的程序来辅助实现多实例操作。

一、创建并初始化数据目录
几个实例要分开运行,必然要把数据库文件放到不同目录中,所以第一步是要建立各个实例的数据目录,这里假设我们要运行三个实例,端口分别是3306,3307,3308,为了方便维护,我们把数据文件夹也按照端口号来命名:

[root@localhost ~]# mkdir -p /data/mysql/{3306,3307,3308}
[root@localhost ~]# chown mysql:mysql /data/mysql/{3306,3307,3308}
[root@localhost ~]# ls -ld /data/mysql/{3306,3307,3308}
drwxr-xr-x 2 mysql mysql 6 12月  7 16:37 /data/mysql/3306
drwxr-xr-x 2 mysql mysql 6 12月  7 16:37 /data/mysql/3307
drwxr-xr-x 2 mysql mysql 6 12月  7 16:37 /data/mysql/3308

通过配置文件指定并初始化数据目录

[root@localhost ~]# cp /etc/my.cnf /data/mysql/3308.cnf
[root@localhost ~]# vim /data/mysql/3308.cnf
[mysqld]
port=3308
datadir=/data/mysql/3308/
socket=/tmp/mysql3308.sock
symbolic-links=0

[mysqld_safe]
log-error=/data/mysql/3308.log
pid-file=/usr/local/mysql/data/3308.pid

[client]
port=3308
socket=/tmp/mysql3308.sock

[root@localhost ~]# mysqld --defaults-file=/data/mysql/3308.cnf --initialize-insecure --user=mysql

初始化完成之后,后续的配置不需要3308.cnf文件,实例的参数会在my.cnf中集中配置。注意,data目录在初始化前,必须为空,不然初始化是会报错

重复上面两个步骤,把3307、3306目录初始化好。

[root@localhost ~]# cat /data/mysql/3306.cnf
[mysqld]
port=3306
datadir=/data/mysql/3306/
socket=/tmp/mysql3306.sock
symbolic-links=0

[mysqld_safe]
log-error=/data/mysql/3306.log
pid-file=/usr/local/mysql/data/3306.pid

[client]
port=3306
socket=/tmp/mysql3306.sock
[root@localhost ~]# cat /data/mysql/3307.cnf
[mysqld]
port=3307
datadir=/data/mysql/3307/
socket=/tmp/mysql3307.sock
symbolic-links=0

[mysqld_safe]
log-error=/data/mysql/3307.log
pid-file=/usr/local/mysql/data/3307.pid

[client]
port=3307
socket=/tmp/mysql3307.sock
[root@localhost ~]# mysqld --defaults-file=/data/mysql/3307.cnf --initialize-insecure --user=mysql
[root@localhost ~]# mysqld --defaults-file=/data/mysql/3306.cnf --initialize-insecure --user=mysql

二、配置my.cnf文件集中管理多个实例

[root@localhost ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@localhost ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin

[mysqld1]
port=3306
socket=/tmp/mysql3306.sock
datadir=/data/mysql/3306/
skip-external-locking
log-bin=/data/mysql/3306/mysql-bin
server-id=3306
user=mysql

[mysqld2]
port=3307
socket=/tmp/mysql3307.sock
datadir=/data/mysql/3307/
skip-external-locking
log-bin=/data/mysql/3307/mysql-bin
server-id=3307
user=mysql

[mysqld3]
port=3308
socket=/tmp/mysql3308.sock
datadir=/data/mysql/3308/
skip-external-locking
log-bin=/data/mysql/3308/mysql-bin
server-id=3308
user=mysql

[mysql]
no-auto-rehash

mysqld_multi的配置文件和一般MySQL配置不同,没有[mysqld]段,取而代之的是[mysqld1]、[mysqld2]等配置段,每个配置段代表一个MySQL实例。

三、管理多个MySQL实例
1,启动多个MySQL实例
启动多个实例时需要一个启动脚本,这个脚本一般在/usr/local/mysql/support-files目录下的mysqld_multi.server文件

[root@localhost ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

启动多个MySQL实例

[root@localhost ~]# /etc/init.d/mysqld_multi start 1-3

启动三个MySQL实例,注意这里的数字和my.cnf中的[mysqldN]对应,1-3就是启动[mysqld1]、[mysqld2]、[mysqld3]配置段的MySQL实例。

[root@localhost ~]# netstat -lnpt | grep -E "3306|3307|3308"
tcp6       0      0 :::3306                 :::*                    LISTEN      2744/mysqld
tcp6       0      0 :::3307                 :::*                    LISTEN      65502/mysqld
tcp6       0      0 :::3308                 :::*                    LISTEN      65499/mysqld

查看端口,看MySQL有没有正常启动,如果没有启动或报错,一般报错详细日志存在各个实例data目录下的主机名.err文件中,打开此文件查找错误原因,逐步排错就可以了。

2,关闭多个MySQL实例

[root@localhost ~]# /etc/init.d/mysqld_multi stop 1-3
[root@localhost ~]# netstat -lnpt | grep -E "3306|3307|3308"

也可以使用以下命令:

killall -u mysql 或者 kill -9 the-mysql-pid

四、root账户管理
前面初始化数据库时用的是–initialize-insecure参数,所以我们初始化的数据库,root账户是没有密码的,要先改密码为123456:

mysqladmin -uroot password '123456' -S /tmp/mysql3306.sock
mysqladmin -uroot password '123456' -S /tmp/mysql3307.sock
mysqladmin -uroot password '123456' -S /tmp/mysql3308.sock

或者:

mysqladmin -uroot password '123456' -P3306 -h127.0.0.1
mysqladmin -uroot password '123456' -P3307 -h127.0.0.1
mysqladmin -uroot password '123456' -P3308 -h127.0.0.1

五、连接mysql多实例:

[root@localhost ~]# mysql -u root -p123456 -S /tmp/mysql3306.sock
[root@localhost ~]# mysql -u root -p123456 -S /tmp/mysql3307.sock
[root@localhost ~]# mysql -u root -p123456 -S /tmp/mysql3308.sock

mysqldump 数据库备份程序

mysqldump 数据库备份程序
mysqldump客户端工具执行逻辑备份,生成一组SQL语句可以用来执行重新创建原数据库对象定义和表数据。它dumps一个或多个MySQL数据库的备份或者传输到另外的服务器。mysqldump命令也可以生成CSV或,其它文本或XML格式的输出

mysqldump的优点是在还原之前它可以方便与灵活的查看或者编辑输出。可以克隆数据库来给开发人员和DBA使用,或者提供测试环境。它并不是一种用来备份大量数据的快速或可扩展的方案。使用大数据大小,即使备份操作花费的时间合理,但还原数据可能非常缓慢因为重放SQL语句调用磁盘I/O来执行插入操作,索引创建等等。

对于大规模的备份和还原,物理备份更合适,可以以它们的原有格式来复制数据文件来快速完成还原操作:
.如果你的表主要是InnoDB表,或者如果你有一种混合的InnoDB与MyISAM表,可以考虑MySQL企业级备份产品的mysqlbackup命令。它为InnoDB备份提供了最好的性能并且破坏性最小,它也可以从MyISAM和其它存储引擎中备份数据表,并且它提供了许多不同的选项来适应不同的备份场景。

mysqldump可以逐行检索和dump表的内容或者它可以在dump它之前从一个表与内存的缓存中来检索整个内容。如果dump一个大表从内存中的缓存中检索可能是一个问题。为了逐行dump表,使用–quick选项(或–opt,它启用–quick)–opt选项(因此–quick)缺省被启用,因此启用内存缓存,使用–skip-quick选项.

如果使用一个最近版本的mysqldump来生成一个dump被加载到一个旧版本的MySQL服务器中,使用–skip-opt选项来代替–extended-insert选项。

有三种常用方式来使用mysqldump命令来完成对一个或多个表,一个或多个数据库,或整个MySQL服务器来进行dump:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

为了dump整个数据库,在db_name后面不要接任何表名,或者使用–database或–all-databases选项。

mysqldump支持以下选项,它可以在命令行或在选项文件中的[mysqldump]与[client]组中进行指定。

mysqldump选项:
–add-drop-database 在每个create database语句之前加上drop database语句

–add-drop-table 在每个create table语句之前加上drop table语句

–add-drop-trigger 在每个create trigger语句之前加上drop table语句

–add-locks 使用lock tables与unlock tables语句来包围每个表的dump

–all-databases dump所有数据库中的所有表

–allow-keywords 允许创建关键字列名

–apply-slave-statements 在change master语句之前包含stop slave,在结束输出之前包含start slave

–bind-address 使用特定的网络接口来连接MySQL服务器

–character-sets-dir 安装字符集的目录

–comments 添加注释到dump文件中

–compact 产生更紧凑的输出

–compatible 产生的输出将与其它的数据库系统或者旧版本的MySQL服务器更兼容

–complete-insert 使用完整的insert语句包括列名

–compress 对在客户端和服务器之间发送的所有信息进行压缩

–create-options 在create table语句中包含所有的MySQL特定的表选项

–databases 将所有的命名参数解释为数据库名

–debug 写调试日志

–debug-check 当程序退出时打印调试信息

–debug-info 当程序退出时打印调试信息,内存与CPU统计信息

–default-auth 要使用的身份认证插件

–default-character-set 指定的缺省字符集

–default-extra-file 除了常用的选项文件外还要读取的选项文件

–default-file 只读指定的选项文件

–default-group-suffix 选项组后缀值

–delete-master-logs 在一个主复制服务器上,在执行dump操作后删除binary日志文件

–disable-keys 对于每个表,围绕insert语句使用语句来禁用与启用关键字

–dump-date 如果–comments被指定,包含dump日期作为”Dump completed on”注释

–dump-slave 包含change master语句来列出slave’s master相关的二进制日志

–enable-cleartext-plugin 使用cleartext身份认证插件

–events 来自转储数据库的转储事件

–extended-insert 使用多行插入语法

–fields-enclosed-by 这个选项与–tab选项一起使用并且与load data infile相关子句有相同的意思

–fields-escaped-by 这个选项与–tab选项一起使用并且与load data infile相关子句有相同的意思

–fields-optionally-enclosed-by 这个选项与–tab选项一起使用并且与load data infile相关子句有相同的意思。

–fields-terminated-by 这个选项与–tab选项一起使用并且与load data infile相关子句有相同的意思

–flush-logs 在开始dump之前清空MySQL服务器的日志文件

–flush-privileges 在dump MySQL数据库后发布一个flush privileges语句。

–force 在一个表被dump时即使出现错误也会继续

–help 显示帮助信息并退出

–hex-blob 使用十六进制表示法来dump二进制列

–host 要连接的主机(IP地址或主机名)

–ignore-error 忽略特定的错误信息

–ignore-table 不dump指定的表

–include-master-host-port 在使用–dump-slave生成的change master语句中包含master_host/master_port选项

–insert-ignore 写insert ignore而不是insert语句

–lines-terminated-by 这个选项与–tab选项一起使用并且有与load data infile相关子句相同的意思

–lock-all-tables 跨所有数据库锁定所有表

–lock-tables 在dump他们之前锁定所有表

–log-error 将警告与错误追加到指定文件

–master-data 输出所写入的二进制日志文件名与位置

–max_allowed_packet 发送到到服务器或从服务器接收到的最大包长度

-net_buffer_length TCP/IP与socket通信的缓冲大小

–no-autocommit 对于每个dump表的insert语句包含了set autocommit=0与commit语句

–no-create-db 不写create database语句

–no-create-info 对于重建的每个dump表不写create table语句

–no-data 不dump表的内容

–no-defaults 不读取选项文件

–no-set-names 与–skip-set-charset相同

–no-tablespaces 在输出中不写入任何create logfile group或create tablespace语句

–opt 对于–add-drop-table –add-locks –create-options –disable-kyes –extended-insert –lock-tables –quick –sset-charset的速记法

–order-by-primary 通过主键或它的第一个唯一索引排序来dump每个表的行记录

–password 当连接服务器时使用的密码

–pipe 在Windows上,使用命名管道连接服务器

–plugin-dir 插件被安装的目录

–port 用于连接的TCP/IP端口号

–print-defaults 打印缺省选项

–protocol 使用的连接协议

–quick 从服务器中一次一行地检索数据

–quote-names 引号内的标识符

–replace 写replace语句而不是insert语句

–result-file 指示输出到指定文件

–routines 从被dump的数据库中dump存储程序(过程与函数)

–secure-auth 不使用旧格式发送密码到服务器

–set-charset 添加set names default_character_set到输出中

–set-gtid-purged 是否向输出添加set @@global.gtid_purged

–shared-memory-base-name 用于共享内存连接的共享内存名

–single-transaction 在从服务器dump数据之前执行begin sql语句

–skip-add-drop-table 在每个create table语句之前不添加drop table语句

–skip-add-locks 不添加锁

–skip-comments 不添加注释到dump文件

–skip-compact 不产生更多紧凑输出

–skip-disable-keys 不禁用关键字

–skip-extended-insert 关闭扩展插入

–skip-opt 关闭–opt选项集

–skip-quick 不从服务器一次一行地检索表的行

–skip-quote-names 不要引用标识符

–skip-set-charset 不写set names语句

–skip-triggers 不dump触发器

–skip-tz-utc 关闭tz-utc

–socket 对于本地连接,使用的Unix socket文件

–ssl 启用加密连接

–ssl-ca 包含可信SSL CA列表的文件路径

–ssl-capath 包含PEM格式可信SSL CA认证的目录路径

–ssl-cert 包含PEM格式X509认证的文件路径

–ssl-cipher 用于连接加密的允许密码列表

–ssl-crl 包含证书撤销列表的文件路径

–ssl-crlpath 包含证书撤销列表文件的目录的路径

–ssl-key 包含PEM格式的X509 钥匙的文件路径

–ssl-mode 连接服务器的安全状态

–ssl-verify-server-cert 根据连接到服务器时使用的主机名验证服务器证书公共名称值

–tab 生成用制表符分隔的数据文件

–tables 覆盖–database或-B选项

–tls-version 对于加密连接允许的连接协议

–triggers 对于每个被dump的表dump触发器

–tz-utc 添加set time_zone=’+00:00’到dump文件

–user 连接服务器时所使用的用户名

–verbose 详细模式

–version 显示版本信息并退出

–where 通过指定where条件只dump所选择的行

–xml 生成XML输出

1、备份命令
格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql

例如: mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database cmdb > /data/backup/cmdb.sql

2、备份压缩
导出的数据有可能比较大,不好备份到远程,这时候就需要进行压缩
格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 | gzip > 文件名.sql.gz

例如: mysqldump -h192.168.1.100 -p 3306 -uroot -ppassword --database cmdb | gzip > /data/backup/cmdb.sql.gz

3、备份同个库多个表
格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 表1 表2 …. > 文件名.sql

例如 mysqldump -h192.168.1.100 -p3306 -uroot -ppassword cmdb t1 t2 > /data/backup/cmdb_t1_t2.sql

4、同时备份多个库
格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –databases 数据库名1 数据库名2 数据库名3 > 文件名.sql

例如:mysqldump -h192.168.1.100 -uroot -ppassword --databases cmdb bbs blog > /data/backup/mutil_db.sql

5、备份实例上所有的数据库
格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –all-databases > 文件名.sql

例如:mysqldump -h192.168.1.100 -p3306 -uroot -ppassword --all-databases > /data/backup/all_db.sql

6、备份数据出带删除数据库或者表的sql备份
格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –add-drop-table –add-drop-database 数据库名 > 文件名.sql

例如:mysqldump -uroot -ppassword --add-drop-table --add-drop-database cmdb > /data/backup/all_db.sql

7、备份数据库结构,不备份数据
格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 –no-data 数据库名1 数据库名2 数据库名3 > 文件名.sql

例如:mysqldump --no-data –databases db1 db2 cmdb > /data/backup/structure.sql

mysqlimport 数据导入程序

mysqlimport 数据导入程序
mysqlimport客户端提供了一个命令行接口来执行load data infile SQL语句。大多数的mysqlimport直接与load data infile语法的子句相关。

其使用语法为:

shell>mysqlimport [options] db_name textfile1 [textfile2 ...]


mysql -e 'create table imptest(id int,n varchar(30))'  mysql


[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 -e 'create table imptest(id int,n varchar(30))'  mysql
mysql: [Warning] Using a password on the command line interface can be insecure.

[mysql@localhost ~]$ vi imptest.txt
100 Max Sydow
101 Count Dracula

[mysql@localhost ~]$ od -c imptest.txt
0000000   1   0   0       M   a   x       S   y   d   o   w  \n   1   0
0000020   1       C   o   u   n   t       D   r   a   c   u   l   a  \n
0000040

[mysql@localhost ~]$ mysqlimport --local mysql imptest.txt -uroot -pxxzx7817600  --fields-terminated-by="|"
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
mysql.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0


[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 -e 'SELECT * FROM imptest' mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+---------------+
| id   | n             |
+------+---------------+
|  100 | Max Sydow     |
|  101 | Count Dracula |
+------+---------------+

[mysql@localhost ~]$ mysqlimport --help
mysqlimport  Ver 3.7 Distrib 5.7.26, for Linux (x86_64)
Copyright (c) 2000, 2019, 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.

Loads tables from text files in various formats.  The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and
read the text file directly. In other cases the client will open the text
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.

Usage: mysqlimport [OPTIONS] database textfile...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /mysqlsoft/mysql/etc/my.cnf /mysqlsoft/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqlimport client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.
  --bind-address=name IP address to bind to.
  --character-sets-dir=name
                      Directory for character set files.
  --default-character-set=name
                      Set the default character set.
  -c, --columns=name  Use only these columns to import the data to. Give the
                      column names in a comma separated list. This is same as
                      giving columns to LOAD DATA INFILE.
  -C, --compress      Use compression in server/client protocol.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       This is a non-debug version. Catch this and exit.
  --debug-info        This is a non-debug version. Catch this and exit.
  --default-auth=name Default authentication client-side plugin to use.
  -d, --delete        First delete all rows from table.
  --enable-cleartext-plugin
                      Enable/disable the clear text authentication plugin.
  --fields-terminated-by=name
                      Fields in the input file are terminated by the given
                      string.
  --fields-enclosed-by=name
                      Fields in the import file are enclosed by the given
                      character.
  --fields-optionally-enclosed-by=name
                      Fields in the input file are optionally enclosed by the
                      given character.
  --fields-escaped-by=name
                      Fields in the input file are escaped by the given
                      character.
  -f, --force         Continue even if we get an SQL error.
  -?, --help          Displays this help and exits.
  -h, --host=name     Connect to host.
  -i, --ignore        If duplicate unique key was found, keep old row.
  --ignore-lines=#    Ignore first n lines of data infile.
  --lines-terminated-by=name
                      Lines in the input file are terminated by the given
                      string.
  -L, --local         Read all files through the client.
  -l, --lock-tables   Lock all tables for write (this disables threads).
  --low-priority      Use LOW_PRIORITY when updating the table.
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  --plugin-dir=name   Directory for client-side plugins.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -r, --replace       If duplicate unique key was found, replace old row.
  --secure-auth       Refuse client connecting to server if it uses old
                      (pre-4.1.1) protocol. Deprecated. Always TRUE
  -s, --silent        Be more silent.
  -S, --socket=name   The socket file to use for connection.
  --ssl-mode=name     SSL connection mode.
  --ssl               Deprecated. Use --ssl-mode instead.
                      (Defaults to on; use --skip-ssl to disable.)
  --ssl-verify-server-cert
                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
  --ssl-ca=name       CA file in PEM format.
  --ssl-capath=name   CA directory.
  --ssl-cert=name     X509 cert in PEM format.
  --ssl-cipher=name   SSL cipher to use.
  --ssl-key=name      X509 key in PEM format.
  --ssl-crl=name      Certificate revocation list.
  --ssl-crlpath=name  Certificate revocation list path.
  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1
  --use-threads=#     Load files in parallel. The argument is the number of
                      threads to use for loading data.
  -u, --user=name     User for login if not current user.
  -v, --verbose       Print info about the various stages.
  -V, --version       Output version information and exit.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
bind-address                      (No default value)
character-sets-dir                (No default value)
default-character-set             auto
columns                           (No default value)
compress                          FALSE
default-auth                      (No default value)
delete                            FALSE
enable-cleartext-plugin           FALSE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
force                             FALSE
host                              (No default value)
ignore                            FALSE
ignore-lines                      0
lines-terminated-by               (No default value)
local                             FALSE
lock-tables                       FALSE
low-priority                      FALSE
plugin-dir                        (No default value)
port                              0
replace                           FALSE
secure-auth                       TRUE
silent                            FALSE
socket                            (No default value)
ssl                               TRUE
ssl-verify-server-cert            FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)
tls-version                       (No default value)
use-threads                       0
user                              (No default value)
verbose                           FALSE

mysqlpump 数据库备份程序

mysqlpump工具执行逻辑备份,生成一组SQL语句集可以被用来执行重建源数据库对象定义和表数据。它将dump一个或多个MySQL数据库备份或者传输到另一个MySQL服务器。

mysqlpump有以下功能:
.并行处理数据库和数据库对象,提高dump速度。

.更好地控制那个数据库和数据库对象(表,存储过程,用户账号)将被dump。

.创建压缩输出

.进程指示器

.对于dump文件重新加载,通过在行数据插入后通过添加索引可以为InnoDB表快速的创建第二索引

缺省情况下,mysqlpump会dump所有数据库。为了显性指定其行为,使用–all-databases选项

shell>mysqlpump --all-databases

为了dump单个数据库,或数据库中特定的表,在命令行中指定数据库名,紧接着指定表名:

shell>mysqlpump db_name

shell>mysqlpump db_name tab_name1 tab_name2 ...

为了将所有的命名参数作为数据库名,使用–databases选项

shell>mysqlpump --databases db_name1 db_name2 ...

缺省情况下,mysqlpump不会dump用户账号定义,即使你dump了mysql系统数据库包含了授权表。为了dump授权表内容以逻辑格式的形式create user和grant语句输出,使用–users选项并压制所有数据库的dump:

shell>mysqlpump --exclude-databases=%  --users

上面命令中的%是一个通配符对于–exclude-databases选项来匹配所有数据库的名字.

为了重加一个dump文件,执行它所包含的语句。例如,使用mysql工具

[mysql@localhost ~]$ mysqlpump --host=192.168.1.250 --port=33306 -uroot -p123456 mysql imptest >imptest_dump.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 1/1 tables, 0/2 rows
Dump completed in 1259 milliseconds
[mysql@localhost ~]$ ls -lrt
总用量 3532
drwxrwxr-x. 2 mysql mysql       6 9月   6 13:03 perl5
-rw-r--r--. 1 mysql mysql 3605196 10月 21 17:27 backup.sql
-rw-r--r--. 1 mysql mysql      32 10月 28 11:46 imptest.txt
-rw-r--r--. 1 mysql mysql    1253 10月 29 14:49 imptest_dump.sql
[mysql@localhost ~]$ cat imptest_dump.sql
-- Dump created by MySQL pump utility, version: 5.7.26, Linux (x86_64)
-- Dump start time: Tue Oct 29 14:49:10 2019
-- Server version: 5.7.26

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `mysql`.`imptest` (
`id` int(11) DEFAULT NULL,
`n` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
INSERT INTO `mysql`.`imptest` VALUES (100,"Max Sydow"),(101,"Count Dracula");
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Tue Oct 29 14:49:11 2019


[mysql@localhost ~]$ mysql --host=192.168.1.250 --port=33306 -uroot -p123456 mysql 

mysqlslap 负载模拟客户端

mysqlslap是一种诊断程序用来模拟MySQL服务器的客户端负载并报告每个阶段的时间。它模拟多个客户端同时访问MySQL服务。

mysqlslap语法如下:

Usage: mysqlslap [OPTIONS]

它有许多选项可以使用:

有些选项比如–create或–query能够让你指这一个字符串包含一个SQL语句或一个文件来包含SQL语句。如果你指定一个文件,缺省情况是它必须每行包含一个SQL语句(也就是说隐式语句分隔符是换行符)。使用–delimiter选项来指定不同的分隔符,它能让你指定一个语句跨越多行或者将多个语句放入一行。在文件中不能包含注释,mysqlslap不能解析它们。

mysqlslap运行分为三个阶段
1.创建方案,表和可选的任何存储过程或用于测试的数据。这个阶段使用单个客房端连接。

2.运行负载测试,这个阶段可以使用多个客户端连接。

3.清除(断开连接,如果指定删除表)。这个阶段使用单个客户端连接。

例如:
假设你创建和查询SQL语句,而且想使用50个客户端来执行查询并且每个语句执行200次(在单行中输入命令):

mysqlslap  --delimiter=";" --create="create table a(b int);insert into a values(11)" --query="select * from a"  --concurrency=50 --iterations=200

执行结果如下:

[mysql@localhost ~]$ mysqlslap -h192.168.1.250 -P33306 -uroot -p123456 mysql --delimiter=";" --create="create table a(b int);insert into a values(11)" --query="select * from a"  --concurrency=50 --iterations=200
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.268 seconds
        Minimum number of seconds to run all queries: 0.257 seconds
        Maximum number of seconds to run all queries: 0.370 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

让mysqlslap来使用一个有两个INT列和三个varchar列的表来构建查询SQL语句。使用五个客户端每个客户端查询20次。不创建表或不插入数据(使用之前的测试方案和数据):

mysqlslap --concurrency=5 --iterations=20 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql

执行结果如下:

[mysql@localhost ~]$ mysqlslap -h192.168.1.250 -P33306 -uroot -p123456 mysql --concurrency=5 --iterations=20 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.247 seconds
        Minimum number of seconds to run all queries: 0.182 seconds
        Maximum number of seconds to run all queries: 0.361 seconds
        Number of clients running queries: 5
        Average number of queries per client: 0

为了告诉程序从指定的文件中加载创建,插入和查询的SQL语句,这里给–create选项指定create.sql文件,它包含了由分号隔开的多个创建表的语句和多个插入语句。–query选项指定的query.sql文件包含由分号隔开的多个查询语句。运行所有加载的语句,然后使用五个客户端运行查询文件中的所有查询(每个运行五次):

mysqlslap --concurrency=5 --iterations=5 --query=query.sql --create=create.sql --delimiter=";"

执行结果如下:

[mysql@localhost ~]$ mysqlslap -h192.168.1.250 -P33306 -uroot -p123456 mysql --concurrency=5 --iterations=5 --query=query.sql --create=create.sql --delimiter=";"
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.035 seconds
        Minimum number of seconds to run all queries: 0.034 seconds
        Maximum number of seconds to run all queries: 0.038 seconds
        Number of clients running queries: 5
        Average number of queries per client: 5

mysqlslap支持以下选项,可以在命令行指定或在选项文件中通过[mysqlslap]和[client]组来指定

[mysql@localhost ~]$ mysqlslap --help
mysqlslap  Ver 1.0 Distrib 5.7.26, for Linux (x86_64)
Copyright (c) 2005, 2019, 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.

Run a query multiple times against the server.

Usage: mysqlslap [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /mysqlsoft/mysql/etc/my.cnf /mysqlsoft/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqlslap client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.
  -?, --help          Display this help and exit.
  -a, --auto-generate-sql
                      Generate SQL where not supplied by file or command line.
  --auto-generate-sql-add-autoincrement
                      Add an AUTO_INCREMENT column to auto-generated tables.
  --auto-generate-sql-execute-number=#
                      Set this number to generate a set number of queries to
                      run.
  --auto-generate-sql-guid-primary
                      Add GUID based primary keys to auto-generated tables.
  --auto-generate-sql-load-type=name
                      Specify test load type: mixed, update, write, key, or
                      read; default is mixed.
  --auto-generate-sql-secondary-indexes=#
                      Number of secondary indexes to add to auto-generated
                      tables.
  --auto-generate-sql-unique-query-number=#
                      Number of unique queries to generate for automatic tests.
  --auto-generate-sql-unique-write-number=#
                      Number of unique queries to generate for
                      auto-generate-sql-write-number.
  --auto-generate-sql-write-number=#
                      Number of row inserts to perform for each thread (default
                      is 100).
  --commit=#          Commit records every X number of statements.
  -C, --compress      Use compression in server/client protocol.
  -c, --concurrency=name
                      Number of clients to simulate for query to run.
  --create=name       File or string to use create tables.
  --create-schema=name
                      Schema to run tests in.
  --csv[=name]        Generate CSV output to named file or to stdout if no file
                      is named.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       This is a non-debug version. Catch this and exit.
  -T, --debug-info    This is a non-debug version. Catch this and exit.
  --default-auth=name Default authentication client-side plugin to use.
  -F, --delimiter=name
                      Delimiter to use in SQL statements supplied in file or
                      command line.
  --detach=#          Detach (close and reopen) connections after X number of
                      requests.
  --enable-cleartext-plugin
                      Enable/disable the clear text authentication plugin.
  -e, --engine=name   Storage engine to use for creating the table.
  -h, --host=name     Connect to host.
  -i, --iterations=#  Number of times to run the tests.
  --no-drop           Do not drop the schema after the test.
  -x, --number-char-cols=name
                      Number of VARCHAR columns to create in table if
                      specifying --auto-generate-sql.
  -y, --number-int-cols=name
                      Number of INT columns to create in table if specifying
                      --auto-generate-sql.
  --number-of-queries=#
                      Limit each client to this number of queries (this is not
                      exact).
  --only-print        Do not connect to the databases, but instead print out
                      what would have been done.
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  --plugin-dir=name   Directory for client-side plugins.
  -P, --port=#        Port number to use for connection.
  --post-query=name   Query to run or file containing query to execute after
                      tests have completed.
  --post-system=name  system() string to execute after tests have completed.
  --pre-query=name    Query to run or file containing query to execute before
                      running tests.
  --pre-system=name   system() string to execute before running tests.
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -q, --query=name    Query to run or file containing query to run.
  --secure-auth       Refuse client connecting to server if it uses old
                      (pre-4.1.1) protocol. Deprecated. Always TRUE
  -s, --silent        Run program in silent mode - no output.
  -S, --socket=name   The socket file to use for connection.
  --sql-mode=name     Specify sql-mode to run mysqlslap tool.
  --ssl-mode=name     SSL connection mode.
  --ssl               Deprecated. Use --ssl-mode instead.
                      (Defaults to on; use --skip-ssl to disable.)
  --ssl-verify-server-cert
                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
  --ssl-ca=name       CA file in PEM format.
  --ssl-capath=name   CA directory.
  --ssl-cert=name     X509 cert in PEM format.
  --ssl-cipher=name   SSL cipher to use.
  --ssl-key=name      X509 key in PEM format.
  --ssl-crl=name      Certificate revocation list.
  --ssl-crlpath=name  Certificate revocation list path.
  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1
  -u, --user=name     User for login if not current user.
  -v, --verbose       More verbose output; you can use this multiple times to
                      get even more verbose output.
  -V, --version       Output version information and exit.

mysqlbinlog 处理二进制日志文件的工具

mysqlbinlog 处理二进制日志文件的工具
服务器的二进制日志文件由用来描述修改数据库内容的事件组成。服务器以二进制方式来写这些文件。为了以文本格式来显示这些内容,可以使用mysqlbinlog工具。也可以使用mysqlbinlog来显示在复制环境中由从从slave服务器所写入中relay日志文件中的内容,因为其格式与二进制日志文件格式一样。

mysqlbinlog的使用语法如下:

Usage: mysqlbinlog [options] log-files

下面的命令用来显示名为binlog.000001的二进制日志文件的内容:

[mysql@localhost ~]$ mysqlbinlog  /mysqldata/mysql/binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191115 15:39:01 server id 1  end_log_pos 123 CRC32 0x2d9d7b4f  Start: binlog v 4, server v 5.7.26-log created 191115 15:39:01 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
FVbOXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAVVs5dEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AU97nS0=
'/*!*/;
# at 123
#191115 15:39:01 server id 1  end_log_pos 154 CRC32 0x42dcd61c  Previous-GTIDs
# [empty]
# at 154
#191115 15:51:15 server id 1  end_log_pos 219 CRC32 0x5bc0b021  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#191115 15:51:15 server id 1  end_log_pos 308 CRC32 0x7261eacb  Query   thread_id=2     exec_time=0     error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1573804275/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C gb2312 *//*!*/;
SET @@session.character_set_client=24,@@session.collation_connection=24,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
truncate table person
/*!*/;
# at 308
#191115 15:51:38 server id 1  end_log_pos 373 CRC32 0x6d2e39aa  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 373
#191115 15:51:38 server id 1  end_log_pos 454 CRC32 0x7871c2ea  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
BEGIN
/*!*/;
# at 454
# at 486
#191115 15:51:38 server id 1  end_log_pos 486 CRC32 0xb746cd30  Intvar
SET INSERT_ID=1/*!*/;
#191115 15:51:38 server id 1  end_log_pos 654 CRC32 0x0e926042  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
insert into person(last_name,first_name,birth,death) values('yong','jing','1985-02-28',null)
/*!*/;
# at 654
#191115 15:51:38 server id 1  end_log_pos 736 CRC32 0xc5450308  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
COMMIT
/*!*/;
# at 736
#191115 15:51:45 server id 1  end_log_pos 801 CRC32 0xc2c892b8  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 801
#191115 15:51:45 server id 1  end_log_pos 882 CRC32 0x51a9cd5c  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
BEGIN
/*!*/;
# at 882
# at 914
#191115 15:51:45 server id 1  end_log_pos 914 CRC32 0x40a98fae  Intvar
SET INSERT_ID=2/*!*/;
#191115 15:51:45 server id 1  end_log_pos 1082 CRC32 0x3396c40d         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
insert into person(last_name,first_name,birth,death) values('yan','huang','1990-08-25',null)
/*!*/;
# at 1082
#191115 15:51:45 server id 1  end_log_pos 1164 CRC32 0xf6f6efad         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
COMMIT
/*!*/;
# at 1164
#191115 15:51:53 server id 1  end_log_pos 1229 CRC32 0x55b50dbe         Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1229
#191115 15:51:53 server id 1  end_log_pos 1310 CRC32 0xd0f6a335         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
BEGIN
/*!*/;
# at 1310
# at 1342
#191115 15:51:53 server id 1  end_log_pos 1342 CRC32 0xfad94baf         Intvar
SET INSERT_ID=3/*!*/;
#191115 15:51:53 server id 1  end_log_pos 1508 CRC32 0x26c5b3bb         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
insert into person(last_name,first_name,birth,death) values('yali','ye','1994-12-23',null)
/*!*/;
# at 1508
#191115 15:51:53 server id 1  end_log_pos 1590 CRC32 0xbb6a2b4c         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

上面输出了binlog.000001二进制日志文件中所包含的内容。对于基于语句的日志,事件信息包括SQL语句,执行语句的服务器ID,语句被执行的时间戳,执行时间等。对于基于行记录的日志,事件信息指示行的改变而不是SQL语句。

# at 486
#191115 15:51:38 server id 1  end_log_pos 486 CRC32 0xb746cd30  Intvar SET INSERT_ID=1/*!*/;
#191115 15:51:38 server id 1  end_log_pos 654 CRC32 0x0e926042  Query   thread_id=2
exec_time=0     error_code=0 SET TIMESTAMP=1573804298/*!*/;

第一行,at后面的数字指示事件在二进制日志文件中的偏移量或开始位置。

第二行是以日期和时间开始指示语句开始执行的时间。对于复制来说,这个时间戳将传播到从属服务器。server id是事件起源服务器的server_id值。end_log_pos指示下一个事件开始的位置(它是当前事件的终止位置+1)。thread_id那个线程来执行这个事件。exec_time是在主服务器上执行事件所花费的时间。在从属服务器上,它是从属服务器上执行结束时间减去主服务器上的执行开始时间的差值。这种差值可以作为一种指示来表示复制进程落后于主服务器多长时间。error_code指示执行事件的结果。零意味着没有出现错误。

mysqlbinglog的输出可以用来重新执行日志文件中的语句(例如,通过使用mysql工具)。这在服务器崩溃时用来恢复是很有用的。

正常来说,使用mysqlbinlog直接读取二进制日志文件并应用它们到本地MySQL服务器。它也可以通过使用–read-from-remote-server选项来从远程服务器上读取二进制日志文件。为了读取远程二进制日志文件,连接参数选项可以被指定用来指示如何连接服务器。这些选项有–host,–password,–port,–protocol,–socket和–user,除非使用了–read-from-remote-server选项否则它们会被忽略。

当对一个大的二进制日志文件执行mysqlbinlog时,要小心注意文件系统是否有足够的空间来存储结果文件。为了配置目录给mysqlbinlog临时使用存储文件,使用TMPDIR环境变量。

使用mysqlbinlog备份二进制日志文件
缺省情况下,mysqlbinlog读取二进制日志文件并以文本格式来显示它的内容。这能让你更容易使用文件来检查发生的事件和重新执行它们(例如,通过使用输出作为mysql的输入)。mysqlbinlog可以直接从本地文件系统中读取日志文件或者使用–read-from-remote-server选项来连接远程服务器并从远程服务器上读取二进制日志文件。

mysqlbinlog以文本格式将内容输出到标准输出,或者如果指定了–result-file=file_name选项会将内容写入文件。

mysqlbinlog可以读了二进制日志文件并将其包含的内容以二进制格式而不是文本格式写入新文件。这种能力可以让你以原来的格式来备份二进制日志文件。mysqlbinlog可以生成静态备份,在备份一组日志文件时当备份完最后的文件时而停止。它也可以生成一种连续(live)备份,当备份到最后的日志文件时仍然保持对服务器的连接并当生成新的事件时继续复制新的事件。在连续备份操作时,mysqlbinlog会运行到连接中断为止(比如,服务器退出)或mysqlbinlog被强制中断为止。当连接中断,mysqlbinlog不会进行等待并重新进行连接,不像从属复制服务器那样。为了在服务器重启之后继续一个live备份,必须重新启动mysqlbinlog。

二进制日志文件备份要求在调用mysqlbinlog时最少要使用两个选项:
.–read-from-remote-server(或-R)选项来告诉mysqlbinlog连接到一个服务器并读取它的二进制日志文件(这类似于一个从属复制服务器连接到它的主服务器).

.–raw选项告诉mysqlbinlog以原始(二进制)格式输出,而不是文本格式。

与–read-from-remote-server一起通常还指定其它选项:–host指示服务器运行在哪里,并且可能需要指定连接选项–user和password。

与–raw联合使用的几个其它选项:
.–stop-never:在读取到最后日志文件后保持对服务器的连接并继续读取新的事件。

.–stop-never-slave-server-id=id:当–stop-never被使用时mysqlbinlog报告的服务器ID,缺省值65535。这可以避免与从属服务器或其它的mysqlbinlog进程的ID冲突。

.–result-file:输出文件名的前缀

为了使用mysqlbinlog来备份服务器的二进制日志文件,你必须指定在服务器上真实存在的文件名。如果你不知道文件名,连接到服务器并使用show binary logs语句来查看当前的日志文件名。

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2530 |
+---------------+-----------+
1 row in set (0.00 sec)

使用这些信息可以使用mysqlbinlog来备份二进制日志文件到当前目录:
为了对binlog.000130到binlog.000132的日志文件进行静态备份,使用以下命令:

mysqlbinlog --read-from-remote-server --host=host_name --raw binlog.000130 binlog.000131 binlog.000132


mysqlbinlog --read-from-remote-server --host=host_name --raw --to-last-log binlog.000130

第一个命令显式指定每个文件名。第二个只指定了第一个日志文件并使用了–to-last-log来读取到最后一个日志文件。在这些命令之间的差异是在mysqlbinlog到达binlog.000132的末尾之前如果服务器打开了binlog.000133文件,第一个命令将不会读取,但第二个命令会读取。

为了进行live备份mysqlbinlog从binlog.000130开始备份现有的日志文件,然后保持对服务器的连接来复制生成的新事件:

mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never binlog.000130

使用–stop-never选项,不需要指定–to-last-log来读取最后的日志文件因为这个选项是隐含的

输出文件名
在没有使用–raw选项时,mysqlbinlog会生成文本格式的输出,如果指定–result-file选项,指定将所有输出写入一个文件中。使用–raw选项时,mysqlbinlog会将服务器的每个日志文件转换成一个二进制输出文件。缺省情况下,mysqlbinlog会在当前目录中生成与源日志文件同名的文件。为了修改输出文件名,使用–result-file选项。与–raw联合使用,–result-file选项值将作为前缀来命名输出文件名。

现在对远程服务器中的binlog.000001日志文件进行备份

[mysql@localhost ~]$ mysqlbinlog --read-from-remote-server --host=192.168.1.250 --raw binlog.000001 --result-file=jy_
[mysql@localhost ~]$ ls -lrt
-rw-r-----. 1 mysql mysql    2530 11月 22 10:24 jy_binlog.000001

可以看到备份的日志文件为以jy_为前缀,其文件名为jy_binlog.000001

使用mysqldump+mysqlbinlog执行备份与还原操作
下面将介绍一个简单的例子显示如何使用mysqldump与mysqlbinlog一起来备份MySQL服务器的数据和二进制日志文件以及在数据丢失时如何使用备份来还原数据。

现在主机上的MySQL服务器的第一个二进制日志文件为binlog.000001

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2530 |
+---------------+-----------+
1 row in set (0.01 sec)

使用mysqlbinlog来对二进制日志文件执行连续备份:

[mysql@localhost ~]$ mysqlbinlog --read-from-remote-server --host=192.168.1.250 --raw  --stop-never binlog.000001

[mysql@localhost ~]$ ls -lrt

-rw-r-----. 1 mysql mysql    2530 11月 22 10:38 binlog.000001

创建了一个名为t的测试表并插入了三行记录

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

使用mysqldump来创建一个dump文件作为对MySQL服务器的数据快照。使用–all-databases,–events和–routines来备份所有的数据,–master-data=2用来指示在dump文件中包括当前的二进制日志文件。

[mysql@localhost ~]$ mysqldump --host=192.168.1.250 --port=3306 -uroot -pxxzx7817600 --all-databases --events --routines --master-data=2> dump_mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ ls -lrt
-rw-r--r--. 1 mysql mysql 3290497 11月 22 10:51 dump_mysql.sql

现在删除mysql库中的表t

mysql> drop table t;
Query OK, 0 rows affected (0.18 sec)

mysql> desc t;
ERROR 1146 (42S02): Table 'mysql.t' doesn't exist

现在假设mysql库中的表t丢失了,使用最近的dump文件来还原数据:

[mysql@localhost ~]$ mysql --host=192.168.1.250 --port=3306 -uroot -pxxzx7817600  mysql 

还原数据后mysql.t表就恢复了

mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| date  | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

现在向mysql.t表中插入一条记录并删除这条记录,然后使用备份的二进制日志文件来重新执行事件来恢复这条记录

mysql> insert into t value(4,'wenyao',NULL);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> delete from t where id=4;
Query OK, 1 row affected (0.13 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.01 sec)

我们需要找到插入这条记录在日志文件中的开始与结束的位置

# at 3306211
#191122 11:04:34 server id 1  end_log_pos 3306323 CRC32 0x88f89864      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574391874/*!*/;
insert into t value(4,'wenyao',NULL)
/*!*/;
# at 3306323
#191122 11:04:34 server id 1  end_log_pos 3306354 CRC32 0x966500de      Xid = 1041
COMMIT/*!*/;
# at 3306354
#191122 11:07:26 server id 1  end_log_pos 3306419 CRC32 0x1f3e6e28      Anonymous_GTID  last_committed=160      sequence_number=161     rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3306419
#191122 11:07:26 server id 1  end_log_pos 3306500 CRC32 0x883ecef4      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574392046/*!*/;
BEGIN
/*!*/;
# at 3306500
#191122 11:07:26 server id 1  end_log_pos 3306600 CRC32 0xecae0a57      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574392046/*!*/;
delete from t where id=4

从上面的日志文件内容可以看到插入的开始位置为3306211,结束位置为3306323

现在当前备份的二进制日志文件名为binlog.000001,重新执行事件的命令如下:

[mysql@localhost ~]$ mysqlbinlog --start-position=3306211 --stop-position=3306323 binlog.000001 | mysql --host=192.168.1.250 --port=3306 -uroot -pxxzx7817600  mysql
mysql: [Warning] Using a password on the command line interface can be insecure.

现在检查mysql.t表中的记录,可以看到被删除的这条记录恢复了。

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

设置msyqlbinlog 服务器ID
在使用--read-from-remote-server选项来调用mysqlbinlog时,mysqlbinlog会连接到一个MySQL服务器,指定了一个服务器ID来标识它并且从该服务器获取所需要的二进制日志文件。可以使用mysqlbinlog以以下几种方式来从服务器中获取日志文件:
.对文件集指定显式的名字。对每个文件,mysqlbinlog会执行连接操作并执行binlog dump命令。服务器会发送文件并断开连接。每个文件都有一个连接。

.指定开始文件与--to-last-log选项,mysqlbinlog会执行连接并对所有的日志文件执行binlog dump命令。服务器会发送所有日志文件并断开连接

.指定开始文件与--stop-never选项(隐式实现--to-last-log选项的功能),mysqlbinlog会执行连接并对所有日志文件执行binlog dump命令。服务器会发送所有日志文件,但在发送最后一个日志文件后不会断开与服务器的连接。

只有使用--read-from-remote-server选项时,mysqlbinlog使用一个为0的server ID进行连接,它将告诉服务器在发送所请求的日志文件后断开连接。

使用--read-from-remote-server与--stop-never选项时,mysqlbinlog将使用一个非0的server ID进行连接,因此在最后的日志文件发送之后服务器不会断开连接。缺省的server ID为65535,但这个可以通过使用
--stop-never-slave-server-id选项来修改。

因此,对于使用前两种方式来获取日志文件人,因为mysqlbinlog指定的server ID为0,所有服务器会断开连接,如果--stop-never选项被指定因为mysqlbinlog指定一个非0的server ID,所以服务器将不会断开连接。

MySQL Binary Log

binary log包含用来描述数据库改变比如表创建操作或表数据改变的事件信息。除非使用基于行的日志,它还可能包含潜在可能发生改变的语句(例如,delete没有匹配的行记录)。binary log也包含关于每个语句更新数据所花费的时间。binary log有两个重要的目的:
.用于复制,主复制服务器上的binary log提供了被发往从服务器的数据改变的记录。主服务器将binary log中包含的事件发送给它的从服务器,从服务器执行这些事件使用相同的数据改变应用到从服务器。

.用于需要使用binary log来执行的特定恢复操作。在一个备份还原之后,在binary log中的事件记录了在备份之后所发生的改变,使用binary log来重新执行这些改变。这些事件从备份时间点起记录了数据库的更新。

当启用binary log后会使用服务器的性能稍稍有所下降。然而,binary log的好处就是能让你配置复制并且对于恢复操作来说比起这稍稍的性能下降更重要。

binary log通常对于意外停机具有弹性因为只有完成的事务会被记录或读回。写入binary log的语句中的密码由服务器重写,不会以明文形式出现。

为了启用binary log,使用–log-bin[=base_name]选项来启动服务器。如果base_name没有指定,默认名字是pid-file选项的值(它的默认名字是主机名)后面跟着-bin。如果给定了基本名,则服务器将文件写入数据目录中,除非给定的基本名包含一个前导绝对路径名以指定另一个目录。建议您显式地指定一个基本名称,而不是使用默认的主机名。

如果在日志名称中提供扩展名(例如,——log-bin=base_name.extension),扩展名将被静默删除并忽略。

mysqld将数字扩展追加到二进制日志基名以生成二进制日志文件名。每次服务器创建一个新的日志文件时,这个数字都会增加,从而创建一个有序的文件序列。服务器每次启动或刷新日志时,都会在这个系列中创建一个新文件。当当前日志的大小达到max_binlog_size时,服务器还会自动创建一个新的二进制日志文件。如果使用大事务因为一个事务是以一个片段写入文件而不会跨文件写入所以binary log文件可能会超过max_binlog_size的大小。

为了保持对那些被使用的binary log文件的跟踪,mysqld也会创建一个binary log索引文件它包含所有被使用的binary log文件。默认情况下这里使用基本名作为binary 日志文件名并带有’.index’扩展名。你可以使用–log-bin-index[=file_name]选项来改变binary log索引文件名。在mysqld正在运行时不能手动编辑这个文件。

术语”binary log file”通常表示包含数据库事件的单独编号的日志文件。术语句”binary log”集体表示一组binary log文件和索引文件。

有super权限的客户端可以通过使用set sql_log_bin=0来对它的语句禁用binary log。

默认情况下,服务器会记录事件的长度以及事件本身,并使用它来验证事件是否被正确写入。也可以通过设置binlog_checksum系统变量让服务器对事件写checksums。当从binary log加读时,主服务器默认会使用事件长度,但如果启用了master_verify_checksum系统变量也会使用checksums。从服务器IO线和也会验证从主服务器所接收到的事件。如果启用了slave_sql_verify_checksum系统变量并可用可以使用从SQL线程使用chechsums。

服务器评估–binlog-do-db和–binlog-ignore-db选项的方式与–replicate-do-db和–replicate-ignore-db选项。

一个复制从服务器默认情况下不会将从复制主服务器所接收到的任何数据修改写入它的binary log。为了记录这些修改,可以在启动从服务器时使用–log-slave-updates选项外加–log-bin选项。在链式复制中,当一个从属节点也充当其他从属节点的主节点时,将执行此操作。

可以使用reset master语句或purge binary logs来删除所有binary log文件。

如果你将使用复制,直到你确定没有从服务器仍然需要使用它们之前你将不应该删除旧的binary log文件。例如如果你的从服务器同步没有落后三天,你可以在主服务器上执行mysqladmin flush-logs并且删除任何超过三天的日志。可以手动删了日志文件,但最好使用purgebinary logs,它能为你安全地更新binary log索引文件。

在一个语句或事务完成后但在任何锁被释放或任何提交完成前会立即写入binary log。这可以确保日志以提交顺序被记录。

对非事务性表的更新在执行后立即存储在二进制日志中。

在未提交事务中,对事务表比如InnoDB表的所有更新(update,delete或insert)会被缓存直到服务器收到commit语句为止。在这时,mysqld会在commit执行之前将整个事务写入binary log。

对于非事务表的修改不能被回滚。如果一个被回滚的事务包含对非事务表的修改,整个事务在最后用ROLLBACK语句记录,以确保复制了对这些表的修改。

当处理事务的线程开始时,它将为binlog_cache_size分配一个buffer来缓存语句。如果语句大于这个buffer,线程会打开一个临时文件来存储事务。当线程结束时临时文件会被删除。

binlog_cache_use状态变量显示了使用这个缓冲区(可能还有一个临时文件)来存储语句的事务数。binlog_cache_disk_use状态变量显示了这些事务中有多少事务实际上使用了临时文件。这两个变量可以被用来调整binlog_cache_size的大小使其足够大避免使用临时文件。

mysql> show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| binlog_cache_size | 4194304 |
+-------------------+---------+
1 row in set (0.00 sec)

max_binlog_cache_size系统变量(默认值为4GB,这也是最大值)可以被用来限制用于缓存多语句事务的总大小。如果事务大于这么多字节,则会失败并回滚。最小值是4096。

如果将使用binary log和基于行日志,并发插入比如create … select或insert … select语句会被转换为正常插入。这样做是为了确保您可以通过在备份操作期间应用日志来重新创建表的精确副本。如果使用基于语句的日志记录,则将原始语句写入日志。

默认情况下每次写入(sync_binlog=1)时binary log会被同步到磁盘。如果sync_binlog没有启用,操作系统或机器(不仅仅是MySQL服务器)崩溃, 二进制日志的最后一条语句可能会丢失。为了防止这个问题,启用sync_binlog系统变量来在每N个提交组之后同步二进制日志到磁盘。sync_binlog最安全的值是1(默认值),但这也是最慢的。

例如,如果您使用InnoDB表,MySQL服务器处理一个COMMIT语句,它会按顺序将许多准备好的事务写到二进制日志中,同步二进制日志,然后将这个事务提交到InnoDB中。如果服务器在这两个操作之间崩溃,事务会在重启时由InnoDB回滚,但仍然存在于二进制日志中。假设——innodb_support_xa设置为1 为默认值,这个问题已经解决了,。虽然这个选项与InnoDB中XA事务的支持有关,但它也确保了二进制日志和InnoDB数据文件是同步的。为了提供更大程度的安全性,MySQL服务器还应该配置为在提交事务之前将二进制日志和InnoDB日志同步到磁盘。默认情况下,InnoDB日志是同步的,sync_binlog=1可用于同步二进制日志。这个选项的效果是,在崩溃后重新启动时,在执行事务回滚之后,MySQL服务器扫描最新的二进制日志文件来收集事务xid值,并计算二进制日志文件中的最后一个有效位置。然后,MySQL服务器告诉InnoDB完成所有已成功写入二进制日志的事务,并将二进制日志截断到最后一个有效位置。这确保了二进制日志反映了InnoDB表的准确数据,因此从数据库与主数据库保持同步,因为它没有收到一条回滚的语句。

如果MySQL服务器在崩溃恢复时发现二进制日志比应该的短,那么它至少缺少一个成功提交的InnoDB事务。如果sync_binlog=1和磁盘/文件系统在被请求时执行实际的同步(有些没有),则不会发生这种情况,因此服务器将打印一条错误消息The binary log file_name is shorter than its expected size。在这种情况下,这个二进制日志不正确,应该从主数据的新快照重新启动复制。

以下系统变量的会话值被写入二进制日志,并在解析二进制日志时由复制从属服务器执行:

.sql_mode(除了NO_DIR_IN_CREATE模式不被复制)
.foreign_key_checks
.unique_checks
.character_set_client
.collation_connection
.collation_database
.collation_server
.sql_auto_is_null

binary log格式
服务器使用几种日志格式来记录二进制日志中的信息。所使用的确切格式取决于所使用的MySQL版本。有三种日志格式:
.MySQL中的复制功能最初是基于从主到从的SQL语句传播。这称为基于语句的日志记录。通过使用——binlog-format=STATEMENT启动服务器,可以使用这种格式。
.在基于行的日志记录中,主进程将事件写入二进制日志,以指示各个表行是如何受到影响的。因此,表总是使用主键来确保有效地标识行,这一点很重要。通过使用——binlogformat=ROW启动服务器,可以使它使用基于行的日志记录。
.还有第三个选项:混合日志记录。对于混合日志记录,默认使用基于语句的日志记录,但是在某些情况下,日志记录模式会自动切换到基于行,如下所述。通过使用选项——binlogformat=MIXED启动mysqld,可以使MySQL显式地使用混合日志记录好坏参半。日志格式也可以由所使用的存储引擎设置或限制。这有助于消除在使用不同存储引擎的主从之间复制某些语句时出现的问题。

对于基于语句的复制,复制不确定语句可能会有问题。在决定给定的语句对于基于语句的复制是否安全时,MySQL决定是否可以保证使用基于语句的日志来复制语句。如果MySQL不能做到这一点,它会将该语句标记为可能不可靠,并发出警告。

Statement may not be safe to log in statement format.

可以使用MySQL的基于行的复制来避免这些问题。

设置Binary log格式
可以在启动MySQL服务器时使用–binlog-format=type来显式选项binary log格式,type支持以下取值:
.STATEMENT基于语句记录日志
.ROW基于行记录记录日志
.MIXED使用混合格式记录日志

日志格式也可以在运行时切换。设置binlog_format系统变量的全局值,以指定更改之后连接的客户端的日志格式

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| MIXED           |
+-----------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)


[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, 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> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)


mysql> SET GLOBAL binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| MIXED           |
+-----------------+
1 row in set (0.00 sec)

[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, 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> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)


mysql> SET GLOBAL binlog_format = 'MIXED';
Query OK, 0 rows affected (0.00 sec)

[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, 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> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| MIXED           |
+-----------------+
1 row in set (0.00 sec)

单个客户端通过设置binlog_format会话值可以控制自己语句的日志格式:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

每个MySQL服务器都可以设置自己的并且只有自己的二进制日志格式(无论binlog_format是用全局还是会话范围设置的)。这意味着更改复制主服务器上的日志格式并不会导致从服务器更改日志格式以匹配。(使用语句模式时,不复制binlog_format系统变量;当使用混合或行日志记录模式时,它被复制,但被从服务器忽略)。在复制正在进行时更改主服务器上的二进制日志格式,或者不更改从服务器上的日志格式,都可能导致复制失败,出现错误比如:

Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT= STATEMENT.'

为了修改全局或会话级binlog_format值,必须要有super权限。客户端可能希望在每个会话的基础上设置二进制日志记录的原因有几个:
.对数据库进行许多小更改的会话可能希望使用基于行的日志记录
.执行与WHERE子句中的许多行匹配的更新的会话可能希望使用基于语句的日志记录,因为记录几个语句比记录许多行更有效
.有些语句需要在主服务器上执行大量时间,但是只修改了几行。因此,使用基于行的日志记录来复制它们可能是有益的

在出现以下情况时您无法在运行时切换复制格式时:
.从存储的函数或触发器中
.如果启用了NDB存储引擎
.如果会话当前处于基于行的复制模式,并且有打开的临时表

尝试在这些情况中切换格式会导致错误.

如果当前正在使用InnoDB表并且事务隔离级别为read committed或read uncommitted时,只能使用基于行的日志记录。它可以修改日志格式为基于语句的日志,但是在运行时这样做会导致很快就会出错因为InnoDB不能再执行插入。

当存在任何临时表时,不建议在运行时切换复制格式,因为仅在使用基于语句的复制时才记录临时表,而使用基于行的复制时则不记录临时表。对于混合复制,通常会记录临时表;除非使用用户定义函数(udf)和UUID()函数。

将二进制日志格式设置为ROW后,使用基于行的格式将许多更改写入二进制日志。但是,有些更改仍然使用基于语句的格式。例如包括所有DDL(数据定义语言)语句,如CREATE TABLE、ALTER TABLE或DROP TABLE。

对于能够进行基于行的复制的服务器,可以使用——binlog-row-event-max-size选项。行以块的形式存储到二进制日志中,块的大小(以字节为单位)不超过此选项的值。该值必须是256的倍数。默认值是8192。

在使用基于语句的日志记录进行复制时,如果语句设计为数据修改是不确定的,则主从上的数据可能会有所不同;也就是说,它由查询优化器决定。通常,即使在复制之外,这也不是一个好的实践。

Mixed Binary Logging Format
当以混合日志格式运行时,服务器在以下条件下自动从基于语句的日志切换到基于行的日志:
.当一个函数包含UUID()。
.当一个或多个表使用AUTO_INCREMENT列被更新并且有一个触发器或存储函数被调用时,像其它不安全语句一样,如果binlog_format=STATEMENT会生成警告。
.当视图主体需要基于行的复制时,创建视图的语句也会使用它。例如,当创建视图的语句使用UUID()函数时,就会发生这种情况
.当涉及到对UDF的调用时
.如果一个语句是按行记录的,并且执行该语句的会话有任何临时表,则按行记录将用于所有后续语句(访问临时表的语句除外),直到该会话使用的所有临时表都被删除为止。不管是否实际记录了任何临时表,his都是正确的。无法使用基于行的格式记录临时表;因此,一旦使用了基于行的日志记录,使用该表的所有后续语句都是不安全的。服务器通过将会话期间执行的所有语句视为不安全的,直到会话不再持有任何临时表,从而近似于这种情况。
.使用FOUND_ROWS()或ROW_COUNT()时
.当使用USER()、CURRENT_USER()或CURRENT_USER时
.当一个语句引用一个或多个系统变量时

例外:以下系统变量在与会话范围(仅)一起使用时,不会导致日志格式切换

 auto_increment_increment
 auto_increment_offset
 character_set_client
 character_set_connection
 character_set_database
 character_set_server
 collation_connection
 collation_database
 collation_server
 foreign_key_checks
 identity
 last_insert_id
 lc_time_names
 pseudo_thread_id
 sql_auto_is_null
 time_zone
 timestamp
 unique_checks

.当其中一个表是mysql数据库中的日志表时
.使用LOAD_FILE()函数时

注意:
如果您试图使用基于语句的日志记录来执行应该使用基于行的日志记录的语句,则会生成一个警告。该警告在客户机中(在SHOW WARNINGS的输出中)和通过mysqld错误日志显示。每次执行这样的语句时,都会向SHOW WARNINGS表添加一个警告。但是,只有为每个客户端会话生成警告的第一个语句被写入错误日志,以防止日志泛滥。

除了上面的决策之外,各个引擎还可以确定在更新表中的信息时使用的日志格式。单个引擎的日志功能可以定义如下:
.如果一个引擎支持基于行的日志记录,则该引擎被认为是支持行日志记录的。
.如果一个引擎支持基于语句的日志记录,那么该引擎就被称为支持语句日志记录

给定的存储引擎可以支持日志格式中的一种或两种。下表列出了每个引擎支持的格式:

存储引擎                           支持基于行日志记录                支持基于语句日志记录
ARCHIVE                            Yes                               Yes
BLACKHOLE                          Yes                               Yes
CSV                                Yes                               Yes
EXAMPLE                            Yes                               NO
FEDERATED                          Yes                               Yes
HEAP                               Yes                               Yes
InnoDB                             Yes                               当事务隔离级别为REPEATABLE READ或SERIALIZABLE时为Yes
                                                                     否则为No
MyISAM                             Yes                               Yes
MERGE                              Yes                               Yes
NDB                                Yes                               No

要记录语句和使用的日志模式是根据语句的类型(安全的、不安全的或二进制注入的)、二进制日志格式(语句、行或混合的)和存储引擎的日志功能(语句支持、行支持、两者都支持或两者都不支持)来确定的。(二进制注入指的是记录必须使用行格式记录的更改。)

语句可能被记录,也可能没有警告;失败的语句不会被记录,但会在日志中生成错误。这在下面的决策表中显示,其中SLC表示“支持语句日志记录”,RLC表示“支持行日志记录”。

当确定产生一个警告时,就会产生一个标准的MySQL警告(可以使用SHOW WARNINGS)。信息也被写入mysqld错误日志。对于每个客户端连接的每个错误实例,只记录一个错误,以防止日志泛滥。日志消息包括尝试的SQL语句。

如果从服务器启动时使用log_error_verbosity设置来显示警告,从服务器会打印消息到错误日志中来提供状态信息,比如例如二进制日志和中继日志坐标,它在何处开始工作,何时切换到另一个中继日志,何时在断开连接后重新连接,对于基于语句的日志记录不安全的语句,等等。

改变mysql数据库表的日志格式
mysql数据库授权表中的内容可以直接(使用insert或delete)或间接(使用grant或create user)修改。影响mysql数据库表的语句会使用以下规则写入binary log:
.根据设置的binlog_format系统变量对mysql数据库表进行数据修改的维护语句直接被记录。这与语句有关比如insert,update,delete,replace,do,load data infile,select和truncate table。

.对mysql数据库进行修改的语句会间接地作为语句被记录而不管binlog_format的设置。这与语句有关比如grant,revoke,set password,rename user,create(除了create table … select之外的所有形式),alter(所有形式)和drop(所有形式)。

create table … select它是由数据定义和数据维护语句组成的。create table部分使用语句格式被记录而select部分根据binlog_format设置情况来记录。

MySQL 通用查询日志

通用查询日志是mysqld所生成的记录。当客户端连接或断开时和从客户端接收到每个SQL语句时服务器会写信息到通用查询日志。当怀疑客户端有错误并且想了解客户端发送给mysqld的信息时通用查询日志非常有用。

当一个客户端连接时显示的每一行也包括using connection_type来指示建立连接的协议。connection_type是TCP/IP(不使用SSL的TCP/IP连接),SSL/TLS(使用SSL的TCP/IP连接),Socket(Unix socket file连接),Named Pipe(Windows命名管道连接),或Shared Memory(Windows共享内存连接)。

mysqld以它所接收到的语句顺序将它们写入通用查询日志,这可能不同于语句被执行的顺序。这个日志顺序与二进制日志顺序相反,语句会在执行之后但在任何锁被释放之前被写入通用查询日志。另外查询日志可能包含那些只查询数据但从没被写入二进制日志的语句。

当在一个复制主服务器上使用基于语句的二进制日志时,通过从服务器所接收到的语句会被写入每个从服务器的查询日志中。如果客户端使用mysqlbinlog工具读取事件并将它们传递给服务器,那么语句会被写入主服务器的查询日志中。

然而当使用基于行记录的二进制日志时,更新会被当作行改变被发送而不是SQL语句,并且当binlog_format为ROW时这些语句从来不会被写入查询日志。当这个变量设置为MIXED时依赖于语句的使用,指定的更新也可能不会被写入查询日志。

默认情况下,通用查询日志是被禁用的。为了显式指定初始化通用查询日志状态,使用–general_log[={0|1}]。没有参数或者参数为1,–general_log启用日志。当这个参数为0,这个选项将禁用日志。为了指定一个日志文件名,使用–general_log_file=file_name。为了指定日志目录,使用–log-output。

如果对通用查询日志文件没有指定文件名,默认名字为host_name.log。除非指定一个绝对路径来指定不同的目录否则服务器将在数据目录中创建日志文件名。

为了在运行时禁用或启用通用查询日志或改变日志文件名,使用全局general_log和general_log_file系统变量。设置general_log为0(或OFF)来禁用日志或1(或ON)来启用日志。设置general_log_file指定日志文件名。如果一个日志文件已经被opne,将会关闭它并打开新文件。

当通用查询日志被启用时,服务器将输出写入到由–log-output选项或log_output系统变量所指定的任何目录。如果启用日志,服务器将打开日志文件并将启动信息写入日志文件。然而除非FILE日志目录被选定否则更多的查询日志不会写入日志文件。如果日志目录为NONE,即使通用查询日志被启用服务器也不会将查询写入日志文件。如果日志目录值不包括FILE,设置日志文件名不会影响日志记录。

服务器重启和日志刷新不会导致生成新的通用查询日志文件(虽然刷新会关闭和重新打开日志文件)。为了重命名文件并创建新文件,使用以下命令:

shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> mv host_name-old.log backup-directory

在Windows上,使用rename而不是mv

通过禁用日志也可以在运行时重命名通用查询日志

set global general_log='OFF';

当禁用日志后,在外部重命名日志文件,例如,通过命令行。然后再次启用日志:

set global general_log='ON';

这种方法可以在任何平台上工作并且不需要重启服务器。

对于当前连接会话级变量sql_log_off可以被设置为ON或OFF来启用或禁用通用查询日志。

写入通用查询日志的语句中的密码由服务器重写,不会以明文形式出现。密码重写对于通用查询日志来说是可以禁上的通过在服务器启动时使用–log-raw选项。这个选项对于诊断来说非常有用,为了查看服务器所接收到的精确语句文本,但由于安全原因不建议在生产环境中使用。

密码重写的一个含义是,不能被解析的语句(例如,由于语法错误)不会被写入通用查询日志,因为它们不能被认为是没有密码的。需要记录所有语句(包括有错误的语句)的用例应该使用——log-raw选项,记住这也可以绕过密码重写。

只有在预期使用纯文本密码时才会发生密码重写。对于具有期望密码散列值的语法的语句,不发生重写。如果为这种语法错误地提供了纯文本密码,则按给定的方式记录密码,而不进行重写。例如,如下所示记录下的语句,因为需要密码散列值

CREATE USER 'user1'@'localhost' IDENTIFIED BY PASSWORD 'not-so-secret';

log_timestamps系统变量控制首写入通用查询日志中信息的时区(对慢查询日志文件和错误日志也一样)。它不会影响写入日志表中的通用查询日志和慢查询日志信息的时区,但从这些表中检索行记录使用convert_tz()或设置会话变量time_zone系统变量可以将本地系统时区转换为任何你所期待的时区。

MySQL Slow Query log(慢查询日志)

慢查询日志由执行时间超过long_query_time秒且至少有min_examined_row_limit行被检查的SQL语句组成。long_query_time的最小值与默认值为0和10。它也可以指定为微秒。对于写入文件的日志,时间信息会被记录并且包含微秒部分。对于写入日志表的记录,只有整数时间被记录,微秒部分被忽略。

默认情况下,管理语句不会被记录,也不会记录不使用索引进行查询的语句。可以使用log_slow_admin_statements和log_queries_not_using_indexes来改变这种行为。

获取初始锁的时间不计算为执行时间。mysqld会在语句执行完成和所有锁被释放完成后将语句写入慢查询日志,因此日志顺序可能与语句的执行顺序不一致。

默认情况下,慢查询日志是被禁用的。为了显式指定初始化慢查询日志状态,使用–slow_query_log={0|1}。不使用参数值或参数值为1时,–slow_query_log启用日志。使用参数值为0时,禁用日志。为了指定慢查询日志文件名,使用–slow_query_log_file=file_name。为了指定日志目录,使用–log-output。

mysql> show variables like 'slow_query%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | OFF                                 |
| slow_query_log_file | /mysqldata/mysql/localhost-slow.log |
+---------------------+-------------------------------------+
2 rows in set (0.01 sec)


mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.01 sec)

如果没有为慢查询日志文件指定文件名,默认文件名为host_name-slow.log。除非使用绝对路私名指定不同的目录否则将在数据目录下创建默认文件。

为了禁用或启用慢查询日志或在运行时改变日志文件名,使用全局的slow_query_log和slow_query_log_file系统变量。设置slow_query_log为0(或OFF)来禁用日志,设置为1(或ON)来启用日志。设置slow_query_log_file来指定日志文件名。如果一个日志文件已经打开 ,它将关闭再打开新文件。

当慢查询日志被启用时,服务器将输出写入到由–log-output选项或log_output系统变量所指定的任何目录中。如果启用了日志,服务器打开日志文件并将启动信息写入文件中。然而,除非FILE日志目录被选择否则查询的进一步日志不会记录在文件中。如果目录为NONE,即使慢查询日志被启用也不会记录查询。如果日志目录不包含FILE,设置日志文件名不会影响日志功能。

如果使用–log-short-format选项,服务器会写少量信息到慢查询日志中。

为了在写入到慢查询日志中的语句中包含慢速管理语句,使用log_slow_admin_statements系统变量。管理语句包括alter table,
analyze table,check table,create index, drop index,optimizer table和repair table。

为了将不使用索引的语句写入慢查询日志中,可以启用log_queries_not_using_indexes系统变量。当这样的查询被写入时,慢查询日志可能增长很快。通过设置log_throttle_queries_not_using_indexes系统变量来对这些查询设置速率限制是有可能的。默认情况下,这个变量为0,这意味着没有限制。正值对不使用索引的查询的日志记录施加了每分钟的限制。第一个这样的查询打开一个60秒的窗口,在这个窗口内,服务器将查询记录到给定的限制,然后抑制其他查询。如果在窗口结束时存在被抑制的查询,服务器将记录一个摘要,指出有多少查询以及在这些查询中花费的累计时间。当服务器记录下一个不使用索引的查询时,下一个60秒窗口开始。

服务器按照以下顺序使用控制参数来决定是否向慢速查询日志写入查询:
1.查询必须不是管理语句或者log_slow_admin_statements必须被启用。
2.查询必须至少花了long_query_time所设定的秒数或者启用log_queries_not_using_indexes和没有使用索引的查询
3.查询必须至少检查了min_examined_row_limit所设置的行数。
4.根据log_throttle_queries_not_using_indexes设置必须不能被禁止。

log_timestamps系统变量控制着写入慢查询日志(通用查询日志和错误日志也一样)中信息所包含的时间戳中的时区信息。它不会影响写入通用查询日志和写入日志表中慢查询日志信息中的时区信息,但从这些日志表中检索数据通过使用convert_tz()或通过设置time_zone系统变量可以从本地系统时区转换成你所期待的任何时区。

所有日志行都包含一个时间戳。

服务器不会将由查询缓存处理的查询写入慢速查询日志,也不会将由于表只有零行或一行而无法从索引中获益的查询写入慢速查询日志。

默认情况下,复制从服务器不会将复制的查询写入慢速查询日志。为了改变这种行为,可以使用log_slow_slave_statements系统变量。

写入慢速查询日志的语句中的密码由服务器重写,不会以明文形式出现。

慢速查询日志可用于查找执行时间较长的查询,因此可以进行优化。然而,检查一个长的慢查询日志可能成为一项困难的任务。为了简化这一过程,可以使用mysqldumpslow命令处理一个慢速查询日志文件,以总结日志中出现的查询

MySQl 配置InnoDB持久化的优化器统计信息

配置InnoDB的优化器统计信息
介绍如何为InnoDB表配置持久化和非持久化的优化器统计信息。

持久性优化器统计数据将被持久保存可以跨跃服务器的重启,从而实现更大的计划稳定性和更一致的查询性能。持久性优化器统计数据还提供了控制和灵活性以及这些额外的好处:
.您可以使用innodb_stats_auto_recalc配置选项来控制是否在对表进行重大更改后自动更新统计信息

.您可以使用STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句以及CREATE TABLE和ALTER TABLE语句为单个表配置优化器统计信息

.您可以在mysql.innodb_table_stats和mysql.innodb_index_stats表中查询优化器的统计数据

mysql> select * from mysql.innodb_table_stats;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: mysql

+---------------+-------------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name        | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+-------------------+---------------------+--------+----------------------+--------------------------+
| cs            | address           | 2021-06-03 16:17:22 |      3 |                    1 |                        0 |
| cs            | articles          | 2021-01-12 16:39:07 |      2 |                    1 |                        0 |
| cs            | bit_test          | 2021-06-18 16:28:55 |      2 |                    1 |                        0 |
| cs            | individual        | 2021-05-27 17:20:38 |      8 |                    1 |                        0 |
| cs            | person            | 2021-06-03 16:13:47 |      2 |                    1 |                        0 |
| cs            | person1           | 2021-05-27 17:02:34 |      3 |                    1 |                        0 |
| cs            | rewrite_rules     | 2020-07-15 17:34:15 |      3 |                    1 |                        0 |
| cs            | sequence          | 2021-01-18 10:45:15 |      0 |                    1 |                        0 |
| cs            | t                 | 2021-09-13 22:27:49 |      0 |                    1 |                        0 |
| cs            | t1                | 2021-07-06 16:00:24 |      2 |                    1 |                        0 |
| cs            | test              | 2021-06-23 15:47:12 |      0 |                    1 |                        0 |
| cs            | test2             | 2021-06-23 15:57:48 |      2 |                    1 |                        0 |
| d1            | T1                | 2021-08-06 17:16:37 |      0 |                    1 |                        0 |
| d1            | child             | 2021-08-17 16:27:43 |      2 |                    1 |                        0 |
| d1            | t                 | 2021-08-17 17:30:55 |      0 |                    1 |                        0 |
| d1            | t1                | 2021-08-09 16:18:51 |      0 |                    1 |                        0 |
| mysql         | articles          | 2021-01-08 15:21:02 |      8 |                    1 |                        1 |
| mysql         | child             | 2021-03-01 11:39:44 |      0 |                    1 |                        1 |
| mysql         | client_firms#P#r0 | 2021-03-01 10:13:09 |      0 |                    1 |                        0 |
| mysql         | client_firms#P#r1 | 2021-03-01 10:13:09 |      0 |                    1 |                        0 |
| mysql         | client_firms#P#r2 | 2021-03-01 10:13:09 |      0 |                    1 |                        0 |
| mysql         | client_firms#P#r3 | 2021-03-01 10:13:09 |      0 |                    1 |                        0 |
| mysql         | count             | 2020-01-06 10:45:24 |      0 |                    1 |                        0 |
| mysql         | cs                | 2020-04-02 18:58:57 |      0 |                    1 |                        0 |
| mysql         | customer          | 2021-03-01 11:43:54 |      0 |                    1 |                        0 |
| mysql         | gtid_executed     | 2019-06-17 14:28:37 |      0 |                    1 |                        0 |
| mysql         | imptest           | 2019-10-28 11:47:04 |      2 |                    1 |                        0 |
| mysql         | jemp              | 2021-04-26 08:12:27 |      4 |                    1 |                        1 |
| mysql         | lc#P#p0           | 2021-03-01 10:20:18 |      0 |                    1 |                        0 |
| mysql         | lc#P#p1           | 2021-03-01 10:20:18 |      0 |                    1 |                        0 |
| mysql         | lc#P#p2           | 2021-03-01 10:20:18 |      0 |                    1 |                        0 |
| mysql         | lc#P#p3           | 2021-03-01 10:20:18 |      0 |                    1 |                        0 |
| mysql         | my_stopwords      | 2021-01-08 16:42:36 |      0 |                    1 |                        0 |
| mysql         | new_table         | 2021-01-13 16:53:36 |      0 |                    1 |                        0 |
| mysql         | opening_lines     | 2021-01-08 16:46:10 |      8 |                    1 |                        1 |
| mysql         | parent            | 2021-03-01 11:39:33 |      0 |                    1 |                        0 |
| mysql         | product           | 2021-03-01 11:43:50 |      0 |                    1 |                        0 |
| mysql         | product_order     | 2021-03-01 11:44:23 |      0 |                    1 |                        2 |
| mysql         | rc#P#p0           | 2021-02-26 11:21:58 |      0 |                    1 |                        0 |
| mysql         | rc#P#p1           | 2021-02-26 11:21:58 |      0 |                    1 |                        0 |
| mysql         | rc#P#p2           | 2021-02-26 11:21:58 |      0 |                    1 |                        0 |
| mysql         | rc#P#p3           | 2021-02-26 11:21:58 |      0 |                    1 |                        0 |
| mysql         | rc#P#p4           | 2021-02-26 11:21:58 |      0 |                    1 |                        0 |
| mysql         | sales             | 2021-01-20 17:00:50 |      0 |                    1 |                        0 |
| mysql         | t                 | 2021-04-26 15:37:14 |      0 |                    1 |                        0 |
| mysql         | t1                | 2021-04-26 11:04:17 |      0 |                    1 |                        0 |
| mysql         | t2                | 2021-04-26 11:05:46 |      0 |                    1 |                        0 |
| mysql         | t3                | 2021-04-26 11:05:58 |      0 |                    1 |                        0 |
| mysql         | t4                | 2021-04-26 11:11:41 |      0 |                    1 |                        0 |
| mysql         | t5                | 2020-10-10 16:24:57 |      0 |                    1 |                        0 |
| mysql         | test              | 2020-01-07 10:56:04 |      0 |                    1 |                        0 |
| mysql         | total             | 2021-02-19 15:22:54 |      0 |                    1 |                        0 |
| mysql         | triangle          | 2021-03-01 14:57:31 |      3 |                    1 |                        0 |
| query_rewrite | rewrite_rules     | 2020-07-15 16:36:38 |      3 |                    1 |                        0 |
| sys           | sys_config        | 2019-06-17 14:28:41 |      6 |                    1 |                        0 |
+---------------+-------------------+---------------------+--------+----------------------+--------------------------+
55 rows in set (0.04 sec)

mysql> select * from mysql.innodb_index_stats;
+---------------+-------------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name        | index_name       | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+-------------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| cs            | address           | PRIMARY          | 2021-06-03 16:17:22 | n_diff_pfx01 |          3 |           1 | address_id                        |
| cs            | address           | PRIMARY          | 2021-06-03 16:17:22 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | address           | PRIMARY          | 2021-06-03 16:17:22 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | articles          | GEN_CLUST_INDEX  | 2021-01-12 16:39:07 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |
| cs            | articles          | GEN_CLUST_INDEX  | 2021-01-12 16:39:07 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | articles          | GEN_CLUST_INDEX  | 2021-01-12 16:39:07 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | bit_test          | GEN_CLUST_INDEX  | 2021-06-18 16:28:55 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |
| cs            | bit_test          | GEN_CLUST_INDEX  | 2021-06-18 16:28:55 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | bit_test          | GEN_CLUST_INDEX  | 2021-06-18 16:28:55 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | individual        | PRIMARY          | 2021-05-27 17:20:38 | n_diff_pfx01 |          8 |           1 | individual_id                     |
| cs            | individual        | PRIMARY          | 2021-05-27 17:20:38 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | individual        | PRIMARY          | 2021-05-27 17:20:38 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | person            | PRIMARY          | 2021-06-03 16:13:47 | n_diff_pfx01 |          2 |           1 | person_id                         |
| cs            | person            | PRIMARY          | 2021-06-03 16:13:47 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | person            | PRIMARY          | 2021-06-03 16:13:47 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | person1           | PRIMARY          | 2021-05-27 17:02:34 | n_diff_pfx01 |          3 |           1 | person_id                         |
| cs            | person1           | PRIMARY          | 2021-05-27 17:02:34 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | person1           | PRIMARY          | 2021-05-27 17:02:34 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | rewrite_rules     | PRIMARY          | 2020-07-15 17:34:15 | n_diff_pfx01 |          3 |           1 | id                                |
| cs            | rewrite_rules     | PRIMARY          | 2020-07-15 17:34:15 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | rewrite_rules     | PRIMARY          | 2020-07-15 17:34:15 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | sequence          | GEN_CLUST_INDEX  | 2021-01-18 10:45:15 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| cs            | sequence          | GEN_CLUST_INDEX  | 2021-01-18 10:45:15 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | sequence          | GEN_CLUST_INDEX  | 2021-01-18 10:45:15 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | t                 | GEN_CLUST_INDEX  | 2021-09-13 22:27:49 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| cs            | t                 | GEN_CLUST_INDEX  | 2021-09-13 22:27:49 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | t                 | GEN_CLUST_INDEX  | 2021-09-13 22:27:49 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | t1                | GEN_CLUST_INDEX  | 2021-07-06 16:00:24 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |
| cs            | t1                | GEN_CLUST_INDEX  | 2021-07-06 16:00:24 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | t1                | GEN_CLUST_INDEX  | 2021-07-06 16:00:24 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | test              | PRIMARY          | 2021-06-23 15:47:12 | n_diff_pfx01 |          0 |           1 | id                                |
| cs            | test              | PRIMARY          | 2021-06-23 15:47:12 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | test              | PRIMARY          | 2021-06-23 15:47:12 | size         |          1 |        NULL | Number of pages in the index      |
| cs            | test2             | PRIMARY          | 2021-06-23 15:57:48 | n_diff_pfx01 |          1 |           1 | id                                |
| cs            | test2             | PRIMARY          | 2021-06-23 15:57:48 | n_diff_pfx02 |          2 |           1 | id,ts                             |
| cs            | test2             | PRIMARY          | 2021-06-23 15:57:48 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| cs            | test2             | PRIMARY          | 2021-06-23 15:57:48 | size         |          1 |        NULL | Number of pages in the index      |
| d1            | T1                | GEN_CLUST_INDEX  | 2021-08-06 17:16:37 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| d1            | T1                | GEN_CLUST_INDEX  | 2021-08-06 17:16:37 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| d1            | T1                | GEN_CLUST_INDEX  | 2021-08-06 17:16:37 | size         |          1 |        NULL | Number of pages in the index      |
| d1            | child             | PRIMARY          | 2021-08-17 16:27:43 | n_diff_pfx01 |          2 |           1 | id                                |
| d1            | child             | PRIMARY          | 2021-08-17 16:27:43 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| d1            | child             | PRIMARY          | 2021-08-17 16:27:43 | size         |          1 |        NULL | Number of pages in the index      |
| d1            | t                 | GEN_CLUST_INDEX  | 2021-08-17 17:30:55 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| d1            | t                 | GEN_CLUST_INDEX  | 2021-08-17 17:30:55 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| d1            | t                 | GEN_CLUST_INDEX  | 2021-08-17 17:30:55 | size         |          1 |        NULL | Number of pages in the index      |
| d1            | t1                | GEN_CLUST_INDEX  | 2021-08-09 16:18:51 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| d1            | t1                | GEN_CLUST_INDEX  | 2021-08-09 16:18:51 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| d1            | t1                | GEN_CLUST_INDEX  | 2021-08-09 16:18:51 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | articles          | FTS_DOC_ID_INDEX | 2021-01-08 15:21:02 | n_diff_pfx01 |          8 |           1 | FTS_DOC_ID                        |
| mysql         | articles          | FTS_DOC_ID_INDEX | 2021-01-08 15:21:02 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | articles          | FTS_DOC_ID_INDEX | 2021-01-08 15:21:02 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | articles          | PRIMARY          | 2021-01-08 15:21:02 | n_diff_pfx01 |          8 |           1 | id                                |
| mysql         | articles          | PRIMARY          | 2021-01-08 15:21:02 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | articles          | PRIMARY          | 2021-01-08 15:21:02 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | child             | GEN_CLUST_INDEX  | 2021-03-01 11:39:44 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | child             | GEN_CLUST_INDEX  | 2021-03-01 11:39:44 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | child             | GEN_CLUST_INDEX  | 2021-03-01 11:39:44 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | child             | par_ind          | 2021-03-01 11:39:44 | n_diff_pfx01 |          0 |           1 | parent_id                         |
| mysql         | child             | par_ind          | 2021-03-01 11:39:44 | n_diff_pfx02 |          0 |           1 | parent_id,DB_ROW_ID               |
| mysql         | child             | par_ind          | 2021-03-01 11:39:44 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | child             | par_ind          | 2021-03-01 11:39:44 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | client_firms#P#r0 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | client_firms#P#r0 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | client_firms#P#r0 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | client_firms#P#r1 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | client_firms#P#r1 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | client_firms#P#r1 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | client_firms#P#r2 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | client_firms#P#r2 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | client_firms#P#r2 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | client_firms#P#r3 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | client_firms#P#r3 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | client_firms#P#r3 | GEN_CLUST_INDEX  | 2021-03-01 10:13:09 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | count             | GEN_CLUST_INDEX  | 2020-01-06 10:45:24 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | count             | GEN_CLUST_INDEX  | 2020-01-06 10:45:24 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | count             | GEN_CLUST_INDEX  | 2020-01-06 10:45:24 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | cs                | GEN_CLUST_INDEX  | 2020-04-02 18:58:57 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | cs                | GEN_CLUST_INDEX  | 2020-04-02 18:58:57 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | cs                | GEN_CLUST_INDEX  | 2020-04-02 18:58:57 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | customer          | PRIMARY          | 2021-03-01 11:43:54 | n_diff_pfx01 |          0 |           1 | id                                |
| mysql         | customer          | PRIMARY          | 2021-03-01 11:43:54 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | customer          | PRIMARY          | 2021-03-01 11:43:54 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | gtid_executed     | PRIMARY          | 2019-06-17 14:28:37 | n_diff_pfx01 |          0 |           1 | source_uuid                       |
| mysql         | gtid_executed     | PRIMARY          | 2019-06-17 14:28:37 | n_diff_pfx02 |          0 |           1 | source_uuid,interval_start        |
| mysql         | gtid_executed     | PRIMARY          | 2019-06-17 14:28:37 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | gtid_executed     | PRIMARY          | 2019-06-17 14:28:37 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | imptest           | GEN_CLUST_INDEX  | 2019-10-28 11:47:04 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |
| mysql         | imptest           | GEN_CLUST_INDEX  | 2019-10-28 11:47:04 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | imptest           | GEN_CLUST_INDEX  | 2019-10-28 11:47:04 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | jemp              | GEN_CLUST_INDEX  | 2021-04-26 08:12:27 | n_diff_pfx01 |          4 |           1 | DB_ROW_ID                         |
| mysql         | jemp              | GEN_CLUST_INDEX  | 2021-04-26 08:12:27 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | jemp              | GEN_CLUST_INDEX  | 2021-04-26 08:12:27 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | jemp              | i                | 2021-04-26 08:12:27 | n_diff_pfx01 |          4 |           1 | g                                 |
| mysql         | jemp              | i                | 2021-04-26 08:12:27 | n_diff_pfx02 |          4 |           1 | g,DB_ROW_ID                       |
| mysql         | jemp              | i                | 2021-04-26 08:12:27 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | jemp              | i                | 2021-04-26 08:12:27 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | lc#P#p0           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | lc#P#p0           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | lc#P#p0           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | lc#P#p1           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | lc#P#p1           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | lc#P#p1           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | lc#P#p2           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | lc#P#p2           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | lc#P#p2           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | lc#P#p3           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | lc#P#p3           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | lc#P#p3           | GEN_CLUST_INDEX  | 2021-03-01 10:20:18 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | my_stopwords      | GEN_CLUST_INDEX  | 2021-01-08 16:42:36 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | my_stopwords      | GEN_CLUST_INDEX  | 2021-01-08 16:42:36 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | my_stopwords      | GEN_CLUST_INDEX  | 2021-01-08 16:42:36 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | new_table         | GEN_CLUST_INDEX  | 2021-01-13 16:53:36 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | new_table         | GEN_CLUST_INDEX  | 2021-01-13 16:53:36 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | new_table         | GEN_CLUST_INDEX  | 2021-01-13 16:53:36 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | opening_lines     | FTS_DOC_ID_INDEX | 2021-01-08 16:46:10 | n_diff_pfx01 |          8 |           1 | FTS_DOC_ID                        |
| mysql         | opening_lines     | FTS_DOC_ID_INDEX | 2021-01-08 16:46:10 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | opening_lines     | FTS_DOC_ID_INDEX | 2021-01-08 16:46:10 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | opening_lines     | PRIMARY          | 2021-01-08 16:46:10 | n_diff_pfx01 |          8 |           1 | id                                |
| mysql         | opening_lines     | PRIMARY          | 2021-01-08 16:46:10 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | opening_lines     | PRIMARY          | 2021-01-08 16:46:10 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | parent            | PRIMARY          | 2021-03-01 11:39:33 | n_diff_pfx01 |          0 |           1 | id                                |
| mysql         | parent            | PRIMARY          | 2021-03-01 11:39:33 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | parent            | PRIMARY          | 2021-03-01 11:39:33 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | product           | PRIMARY          | 2021-03-01 11:43:50 | n_diff_pfx01 |          0 |           1 | category                          |
| mysql         | product           | PRIMARY          | 2021-03-01 11:43:50 | n_diff_pfx02 |          0 |           1 | category,id                       |
| mysql         | product           | PRIMARY          | 2021-03-01 11:43:50 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | product           | PRIMARY          | 2021-03-01 11:43:50 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | product_order     | PRIMARY          | 2021-03-01 11:44:23 | n_diff_pfx01 |          0 |           1 | no                                |
| mysql         | product_order     | PRIMARY          | 2021-03-01 11:44:23 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | product_order     | PRIMARY          | 2021-03-01 11:44:23 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | product_order     | customer_id      | 2021-03-01 11:44:23 | n_diff_pfx01 |          0 |           1 | customer_id                       |
| mysql         | product_order     | customer_id      | 2021-03-01 11:44:23 | n_diff_pfx02 |          0 |           1 | customer_id,no                    |
| mysql         | product_order     | customer_id      | 2021-03-01 11:44:23 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | product_order     | customer_id      | 2021-03-01 11:44:23 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | product_order     | product_category | 2021-03-01 11:44:23 | n_diff_pfx01 |          0 |           1 | product_category                  |
| mysql         | product_order     | product_category | 2021-03-01 11:44:23 | n_diff_pfx02 |          0 |           1 | product_category,product_id       |
| mysql         | product_order     | product_category | 2021-03-01 11:44:23 | n_diff_pfx03 |          0 |           1 | product_category,product_id,no    |
| mysql         | product_order     | product_category | 2021-03-01 11:44:23 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | product_order     | product_category | 2021-03-01 11:44:23 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | rc#P#p0           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | rc#P#p0           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | rc#P#p0           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | rc#P#p1           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | rc#P#p1           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | rc#P#p1           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | rc#P#p2           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | rc#P#p2           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | rc#P#p2           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | rc#P#p3           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | rc#P#p3           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | rc#P#p3           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | rc#P#p4           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | rc#P#p4           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | rc#P#p4           | GEN_CLUST_INDEX  | 2021-02-26 11:21:58 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | sales             | GEN_CLUST_INDEX  | 2021-01-20 17:00:50 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | sales             | GEN_CLUST_INDEX  | 2021-01-20 17:00:50 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | sales             | GEN_CLUST_INDEX  | 2021-01-20 17:00:50 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | t                 | GEN_CLUST_INDEX  | 2021-04-26 15:37:14 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | t                 | GEN_CLUST_INDEX  | 2021-04-26 15:37:14 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t                 | GEN_CLUST_INDEX  | 2021-04-26 15:37:14 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | t1                | PRIMARY          | 2021-04-26 11:04:17 | n_diff_pfx01 |          0 |           1 | c1                                |
| mysql         | t1                | PRIMARY          | 2021-04-26 11:04:17 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t1                | PRIMARY          | 2021-04-26 11:04:17 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | t2                | PRIMARY          | 2021-04-26 11:05:46 | n_diff_pfx01 |          0 |           1 | c1                                |
| mysql         | t2                | PRIMARY          | 2021-04-26 11:05:46 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t2                | PRIMARY          | 2021-04-26 11:05:46 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | t3                | PRIMARY          | 2021-04-26 11:05:58 | n_diff_pfx01 |          0 |           1 | c1                                |
| mysql         | t3                | PRIMARY          | 2021-04-26 11:05:58 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t3                | PRIMARY          | 2021-04-26 11:05:58 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | t4                | PRIMARY          | 2021-04-26 11:11:41 | n_diff_pfx01 |          0 |           1 | c1                                |
| mysql         | t4                | PRIMARY          | 2021-04-26 11:11:41 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t4                | PRIMARY          | 2021-04-26 11:11:41 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | t5                | GEN_CLUST_INDEX  | 2020-10-10 16:24:57 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | t5                | GEN_CLUST_INDEX  | 2020-10-10 16:24:57 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t5                | GEN_CLUST_INDEX  | 2020-10-10 16:24:57 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | test              | GEN_CLUST_INDEX  | 2020-01-07 10:56:04 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | test              | GEN_CLUST_INDEX  | 2020-01-07 10:56:04 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | test              | GEN_CLUST_INDEX  | 2020-01-07 10:56:04 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | total             | GEN_CLUST_INDEX  | 2021-02-19 15:22:54 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| mysql         | total             | GEN_CLUST_INDEX  | 2021-02-19 15:22:54 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | total             | GEN_CLUST_INDEX  | 2021-02-19 15:22:54 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | triangle          | GEN_CLUST_INDEX  | 2021-03-01 14:57:31 | n_diff_pfx01 |          3 |           1 | DB_ROW_ID                         |
| mysql         | triangle          | GEN_CLUST_INDEX  | 2021-03-01 14:57:31 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | triangle          | GEN_CLUST_INDEX  | 2021-03-01 14:57:31 | size         |          1 |        NULL | Number of pages in the index      |
| query_rewrite | rewrite_rules     | PRIMARY          | 2020-07-15 16:36:38 | n_diff_pfx01 |          3 |           1 | id                                |
| query_rewrite | rewrite_rules     | PRIMARY          | 2020-07-15 16:36:38 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| query_rewrite | rewrite_rules     | PRIMARY          | 2020-07-15 16:36:38 | size         |          1 |        NULL | Number of pages in the index      |
| sys           | sys_config        | PRIMARY          | 2019-06-17 14:28:41 | n_diff_pfx01 |          6 |           1 | variable                          |
| sys           | sys_config        | PRIMARY          | 2019-06-17 14:28:41 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| sys           | sys_config        | PRIMARY          | 2019-06-17 14:28:41 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+-------------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
191 rows in set (0.00 sec)

.可以查看mysql.innodb_table_stats和mysql.innodb_index_stats表的last_update列查看统计信息最后一次更新的时间。

.您可以手动修改mysql.innodb_table_stats和mysql.innodb_index_stats表强制执行特定的查询优化计划或在不修改数据库的情况下测试可选计划。

默认情况下,持久化优化器统计特性是启用的(innodb_stats_persistent=ON)。

非持久优化器统计信息在每次服务器重启时和一些其他操作之后被清除,并在下一个表访问时重新计算。因此,在重新计算统计信息时可能会产生不同的估计,导致执行计划中的不同选择和查询性能的变化

本节还提供了有关估计ANALYZE TABLE复杂度的信息,这在试图在准确的统计数据和ANALYZE TABLE执行时间之间取得平衡时可能很有用。

配置持久优化器统计信息参数
持久性优化器统计信息特性将统计信息存储到磁盘,并在服务器重启时保持这些统计信息的持久性,从而提高了计划的稳定性,这样优化器就更有可能在每次给定查询时做出一致的选择。

当innodb_stats_persistent=ON或使用STATS_PERSISTENT=1创建或修改单个表时,优化器统计信息被持久化到磁盘。innodb_stats_persistent默认启用。

以前,在每次服务器重启和一些其他操作之后,都会清除优化器统计信息,并在下一个表访问时重新计算。因此,在重新计算统计信息时可能会产生不同的估计,导致查询执行计划中的不同选择,从而导致查询性能的变化。

持久性统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中。

要恢复使用非持久优化器统计信息,可以使用ALTER TABLE tbl_name STATS_PERSISTENT=0语句修改表。

为持久优化器统计信息配置自动统计信息计算
innodb_stats_auto_recalc配置选项在默认情况下是启用的,它决定是否在表发生重大更改(超过10%的行)时自动计算统计数据。您还可以在CREATE TABLE或ALTER TABLE语句中使用STATS_AUTO_RECALC子句为单个表配置自动统计信息重新计算。innodb_stats_auto_recalc默认启用。

mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.01 sec)

由于自动统计信息重新计算的异步特性(发生在后台),即使启用了innodb_stats_auto_recalc,当DML操作影响一个表的10%以上时,统计数据可能不会立即重新计算。在某些情况下,统计信息的重新计算可能会延迟几秒钟。如果在更改表的重要部分后需要立即更新统计信息,则运行analyze table来启动同步(前台)统计信息的重新计算。

如果innodb_stats_auto_recalc被禁用,那么在对索引的列进行大量更改之后,为每个适用的表发出ANALYZE TABLE语句,以确保优化器统计数据的准确性。您可以在将代表性数据加载到表中之后,在设置脚本中运行此语句,并在DML操作显著改变了索引列的内容之后定期运行它,或者在活动较少的时候调度运行它。当一个新的索引被添加到一个现有的表时,索引统计信息被计算并添加到innodb_index_stats表中,而不管innodb_stats_auto_recalc的值是多少。

要确保在创建新索引时收集统计信息,可以启用innodb_stats_auto_recalc选项,也可以在启用持久统计模式时,在创建每个新索引后运行ANALYZE TABLE。

为个别表配置优化器统计信息参数
innodb_stats_persistent,innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全局配置选项。要覆盖这些系统范围的设置,并为各个表配置优化器统计信息参数,可以在CREATE TABLE或ALTER TABLE语句中定义STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句来实现。
.STATS_PERSISTENT指定InnoDB表是否启用持久化统计信息。默认值导致表的持久统计信息设置由innodb_stats_persistent配置选项决定。值1启用表的持久统计,而值0关闭此特性。在通过CREATE TABLE或ALTER TABLE语句启用持久统计信息后,在将代表性数据加载到表中之后,发出ANALYZE TABLE语句来计算统计信息

.STATS_AUTO_RECALC指定是否自动重新计算InnoDB表的持久统计信息。默认值导致表的持久统计信息设置由innodb_stats_auto_recalc配置选项决定。当表中10%的数据发生变化时,值1将导致重新计算统计数据。0可以防止对该表进行自动重新计算;使用此设置,在对表进行实质性更改后,发出一条ANALYZE TABLE语句来重新计算统计数据。

.STATS_SAMPLE_PAGES指定在估计索引列的基数和其他统计信息(例如由ANALYZE TABLE计算的统计信息)时要抽样的索引页数。

三个子句都在下面的CREATE TABLE示例中指定:

mysql> CREATE TABLE t1 (
    ->  id  int(8) NOT NULL auto_increment,
    ->  data  varchar(255),
    ->  date  datetime,
    -> PRIMARY KEY ( id ),
    -> INDEX  DATE_IX  ( date )
    -> ) ENGINE=InnoDB,
    -> STATS_PERSISTENT=1,
    -> STATS_AUTO_RECALC=1,
    -> STATS_SAMPLE_PAGES=25;
Query OK, 0 rows affected (0.09 sec)

配置InnoDB优化器统计信息的采样页面数
MySQL查询优化器使用关于键分布的估计统计信息,根据索引的相对选择性为执行计划选择索引。像ANALYZE TABLE这样的操作会导致InnoDB从表上的每个索引中随机取样页,以估计索引的基数。(这种技术被称为随机潜水。)

为了控制统计信息估计的质量(从而为查询优化器提供更好的信息),可以使用参数innodb_stats_persistent_sample_pages更改采样页面的数量,这个参数可以在运行时设置

Innodb_stats_persistent_sample_pages的默认值是20。作为一般指导原则,当遇到以下问题时,请考虑修改此参数:
1.统计数据不够准确,优化器会选择次优计划,如EXPLAIN输出所示。通过比较索引的实际基数(在索引列上运行SELECT DISTINCT返回)和mysql.innodb_index_stats持久化统计表提供的估计,可以检查统计信息的准确性。

如果确定统计信息不够准确,则应该增加innodb_stats_persistent_sample_pages的值,直到统计信息估计足够准确。然而,过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE运行缓慢

2.ANALYZE TABLE太慢。在这种情况下,innodb_stats_persistent_sample_pages应该减少,直到ANALYZE TABLE的执行时间是可接受的。然而,过多地降低该值可能会导致第一个问题:不准确的统计数据和不够理想的查询执行计划

如果无法在精确的统计数据和ANALYZE TABLE执行时间之间取得平衡,那么可以考虑减少表中索引列的数量,或者限制分区的数量,以降低ANALYZE TABLE的复杂性。表主键中的列数也需要考虑,因为主键列被附加到每个非唯一索引。

在持久性统计信息计算中包括删除标记的记录
默认情况下,InnoDB在计算统计信息读取未提交的数据。在一个未提交事务从表中删除行的情况下,InnoDB在计算行估计和索引统计时,会排除被标记删除的记录,这可能会导致使用READ UNCOMMITTED以外的事务隔离级别并发操作表的其他事务的执行计划不是最优的。为了避免这种情况,可以启用innodb_stats_include_delete_marked,以确保在计算持久优化器统计数据时,InnoDB包含有标记删除的记录。

当innodb_stats_include_delete_marked被启用后,analyze table在计算统计信息时会考虑被标记为删除的记录。

innodb_stats_include_delete_marked是一个全局设置会影响所有的innodb表,并且它只应用于持久性优化器统计。

innodb_stats_include_delete_marked是在MySQL 5.7.16中引入的。

InnoDB持久化统计信息表
持久统计特性依赖于mysql数据库的内部管理表innodb_table_stats和innodb_index_stats。这些表在所有安装、升级和从源构建过程中自动设置。

mysql> desc innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field                    | Type                | Null | Key | Default           | Extra                       |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name            | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name               | varchar(199)        | NO   | PRI | NULL              |                             |
| last_update              | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| n_rows                   | bigint(20) unsigned | NO   |     | NULL              |                             |
| clustered_index_size     | bigint(20) unsigned | NO   |     | NULL              |                             |
| sum_of_other_index_sizes | bigint(20) unsigned | NO   |     | NULL              |                             |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)



mysql> desc innodb_index_stats;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                | Null | Key | Default           | Extra                       |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name    | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name       | varchar(199)        | NO   | PRI | NULL              |                             |
| index_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| last_update      | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| stat_name        | varchar(64)         | NO   | PRI | NULL              |                             |
| stat_value       | bigint(20) unsigned | NO   |     | NULL              |                             |
| sample_size      | bigint(20) unsigned | YES  |     | NULL              |                             |
| stat_description | varchar(1024)       | NO   |     | NULL              |                             |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

innodb_table_stats和innodb_index_stats表都包含一个last_update列,显示InnoDB上次更新索引统计信息的时间,如下例所示:

mysql> select * from innodb_table_stats \G
*************************** 1. row ***************************
           database_name: cs
              table_name: address
             last_update: 2021-06-03 16:17:22
                  n_rows: 3
    clustered_index_size: 1
sum_of_other_index_sizes: 0

mysql> select * from innodb_index_stats where table_name='address' \G
*************************** 1. row ***************************
   database_name: cs
      table_name: address
      index_name: PRIMARY
     last_update: 2021-06-03 16:17:22
       stat_name: n_diff_pfx01
      stat_value: 3
     sample_size: 1
stat_description: address_id
*************************** 2. row ***************************
   database_name: cs
      table_name: address
      index_name: PRIMARY
     last_update: 2021-06-03 16:17:22
       stat_name: n_leaf_pages
      stat_value: 1
     sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 3. row ***************************
   database_name: cs
      table_name: address
      index_name: PRIMARY
     last_update: 2021-06-03 16:17:22
       stat_name: size
      stat_value: 1
     sample_size: NULL
stat_description: Number of pages in the index
3 rows in set (0.00 sec)

innodb_table_stats和innodb_index_stats是普通表,可以手动更新。手动更新统计信息的能力使得在不修改数据库的情况下强制执行特定的查询优化计划或测试备选计划成为可能。如果您手动更新统计信息,请执行FLUSH TABLE tbl_name命令让MySQL重新加载更新后的统计信息。

持久性统计信息被认为是本地信息,因为它们与服务器实例相关。因此,当自动统计信息重新计算发生时,innodb_table_stats和innodb_index_stats表不会被复制。如果您运行ANALYZE TABLE来启动统计信息的同步重新计算,那么这个语句将被复制(
除非您抑制了对它的日志记录),并在复制从服务器上进行重新计算。

InnoDB持久化统计信息表示例
innodb_table_stats表每个表包含一行。下面的例子演示了收集到的数据。

表t1包含一个主索引(列a、b)二级索引(列c、d)和唯一索引(列e、f):

mysql> CREATE TABLE t1 (
    -> a INT, b INT, c INT, d INT, e INT, f INT,
    -> PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)

插入五行样本数据后,表如下所示:

mysql> insert into t1 values(1,1,10,11,100,101),(1,2,10,11,200,102),(1,3,10,11,100,103),(1,4,10,12,200,104),(1,5,10,12,100,105);
Query OK, 5 rows affected (0.12 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+---+---+------+------+------+------+
| a | b | c    | d    | e    | f    |
+---+---+------+------+------+------+
| 1 | 1 |   10 |   11 |  100 |  101 |
| 1 | 2 |   10 |   11 |  200 |  102 |
| 1 | 3 |   10 |   11 |  100 |  103 |
| 1 | 4 |   10 |   12 |  200 |  104 |
| 1 | 5 |   10 |   12 |  100 |  105 |
+---+---+------+------+------+------+
5 rows in set (0.00 sec)

要立即更新统计信息,运行ANALYZE TABLE(如果启用了innodb_stats_auto_recalc,假设改变的表行达到10%的阈值,统计信息会在几秒钟内自动更新)

mysql> analyze table t1;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| mysql.t1 | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.03 sec)


mysql> select * from innodb_table_stats where table_name='t1' and database_name='mysql';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| mysql         | t1         | 2022-02-17 14:52:13 |      5 |                    1 |                        2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.01 sec)

表t1的表统计信息显示InnoDB最后更新表统计信息的时间为(2022-02-17 14:52:13),表中的行记录数为5,集簇索引大小为1个索引页,其它索引大小为2个索引页。

mysql> select * from innodb_table_stats where table_name='t1' and database_name='mysql' \G
*************************** 1. row ***************************
           database_name: mysql
              table_name: t1
             last_update: 2022-02-17 14:52:13
                  n_rows: 5
    clustered_index_size: 1
sum_of_other_index_sizes: 2
1 row in set (0.01 sec)

innodb_index_stats表包含每个索引的多行。innodb_index_stats表中的每一行都提供了与特定索引统计相关的数据,在stat_name列中显示命名,在stat_description列中显示描述。例如:

mysql> select * from innodb_index_stats where table_name='t1' and database_name='mysql';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| mysql         | t1         | PRIMARY    | 2022-02-17 14:52:13 | n_diff_pfx01 |          1 |           1 | a                                 |
| mysql         | t1         | PRIMARY    | 2022-02-17 14:52:13 | n_diff_pfx02 |          5 |           1 | a,b                               |
| mysql         | t1         | PRIMARY    | 2022-02-17 14:52:13 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t1         | PRIMARY    | 2022-02-17 14:52:13 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | t1         | i1         | 2022-02-17 14:52:13 | n_diff_pfx01 |          1 |           1 | c                                 |
| mysql         | t1         | i1         | 2022-02-17 14:52:13 | n_diff_pfx02 |          2 |           1 | c,d                               |
| mysql         | t1         | i1         | 2022-02-17 14:52:13 | n_diff_pfx03 |          2 |           1 | c,d,a                             |
| mysql         | t1         | i1         | 2022-02-17 14:52:13 | n_diff_pfx04 |          5 |           1 | c,d,a,b                           |
| mysql         | t1         | i1         | 2022-02-17 14:52:13 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t1         | i1         | 2022-02-17 14:52:13 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | t1         | i2uniq     | 2022-02-17 14:52:13 | n_diff_pfx01 |          2 |           1 | e                                 |
| mysql         | t1         | i2uniq     | 2022-02-17 14:52:13 | n_diff_pfx02 |          5 |           1 | e,f                               |
| mysql         | t1         | i2uniq     | 2022-02-17 14:52:13 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t1         | i2uniq     | 2022-02-17 14:52:13 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
14 rows in set (0.01 sec)

stat_name列显示了以下类型的统计信息:
.size: 当tat_name=size时, stat_value列显示索引中的总页数。

.n_leaf_pages: 当stat_name=n_leaf_pages时, stat_value列显示索引中叶页的数量。

.n_diff_pfxNN: 当stat_name=n_diff_pfx01时,stat_value列显示索引中第一列的distinct值的数量。当stat_name=n_diff_pfx02时,stat_value列显示索引中前两列的distinct值的数量。另外,stat_name=n_diff_pfxNN,stat_description列显示了被计数的索引列的逗号分隔列表。

为了进一步说明n_diff_pfxNN统计数据所提供的基数数据,考虑t1表示例。如下所示,用一个主索引(列a、b)、一个辅助索引(列c、d)和一个唯一索引(列e、f)创建了t1表。

mysql> CREATE TABLE t1 (
    -> a INT, b INT, c INT, d INT, e INT, f INT,
    -> PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)


插入五行样本数据后,表如下所示:

mysql> insert into t1 values(1,1,10,11,100,101),(1,2,10,11,200,102),(1,3,10,11,100,103),(1,4,10,12,200,104),(1,5,10,12,100,105);
Query OK, 5 rows affected (0.12 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+---+---+------+------+------+------+
| a | b | c    | d    | e    | f    |
+---+---+------+------+------+------+
| 1 | 1 |   10 |   11 |  100 |  101 |
| 1 | 2 |   10 |   11 |  200 |  102 |
| 1 | 3 |   10 |   11 |  100 |  103 |
| 1 | 4 |   10 |   12 |  200 |  104 |
| 1 | 5 |   10 |   12 |  100 |  105 |
+---+---+------+------+------+------+
5 rows in set (0.00 sec)

当查询index_name,stat_name,stat_value和stat_description且where条件为stat_name like ‘n_diff%’,返回结果如下:

mysql> select index_name,stat_name,stat_value,stat_description from innodb_index_stats where table_name='t1' and
    -> database_name='mysql' and stat_name like 'n_diff%';
+------------+--------------+------------+------------------+
| index_name | stat_name    | stat_value | stat_description |
+------------+--------------+------------+------------------+
| PRIMARY    | n_diff_pfx01 |          1 | a                |
| PRIMARY    | n_diff_pfx02 |          5 | a,b              |
| i1         | n_diff_pfx01 |          1 | c                |
| i1         | n_diff_pfx02 |          2 | c,d              |
| i1         | n_diff_pfx03 |          2 | c,d,a            |
| i1         | n_diff_pfx04 |          5 | c,d,a,b          |
| i2uniq     | n_diff_pfx01 |          2 | e                |
| i2uniq     | n_diff_pfx02 |          5 | e,f              |
+------------+--------------+------------+------------------+
8 rows in set (0.00 sec)

对于primary索引,这里有两个n_diff%行。行数等于索引中的列数。

注意:对于非唯一索引 ,InnoDB会附加主键索引的列到非唯一索引中。

.当index_name=PRIMARY和stat_name=n_diff_pfx01时,stat_value值为1,这说明索引中第一个列(a)包含一个distinct值。列a的distinct值可以通过查看表t1中的列a的值来进行确认,只有单个distinct值1。计数列(a)显示在结果集的stat_description列中。

.当index_name=PRIMAY和stat_name=n_diff_pfx02时,stat_value值为5,这说明索引中前两列包含五个distinct值。列a和b的distinct值可以通过查看表t1中的列a和b的值来进行确认,有五个distinct值(1,1),(1,2),(1,3),(1,4),(1,5)。计数列(a,)显示在结果集的stat_description列中。

对于二级索引(i1),有4个n_diff%行。二级索引只定义了两个列(c,d),但是二级索引有四个n_diff%行,因为InnoDB将所有非唯一的索引都以主键作为后缀。因此,二级索引列(c,d)和主键列(a,b)有4个n_diff%行,而不是2个。
.当index_name=i1和stat_name=n_diff_pfx01时,stat_value的值为1,这说明索引中第一列(c)包含一个distinct值。列c的distinct值可以通过查看表t1中的列c的数据来进行确认。计数列c在stat_description列中显示。

.当index_name=i1和stat_name=n_diff_pfx02时,stat_value的值为2,这说明索引中前两列(c,d)包含两个distinct值。列c和d的distinct值可以通过查看表t1中的列c和d的数据来进行确认。计数列(c,d)在stat_description列中显示

.当index_name=i1和stat_name=n_diff_pfx03,stat_value的值为2,这说明索引中前三列(c,d,a)包含两个distinct值。列c,d和a的distinct值可以通过查看表t1中列c,d和a的数据来进行确认,有两个distinct值(10,11,1)和(10,12,1)。计数列(c,d,a)在stat_desciption列中显示

.当index_name=i1和stat_name=n_diff_pfx04,stat_value的值为5,这说明索引中四列(c,d,a,b)包含五个distinct值。列c,d,a和b的distinct值可以通过查看表t1中列c,d,a和b的数据来进行确认,有五个distinct值(10,11,1,1)和(10,11,1,2),(10,11,1,3),(10,12,1,4)和(10,12,1,5)。计数列(c,d,a,b)在stat_description列中显示

对于唯一索引(i2uniq),有两个n_diff%行。
.当index_name=i2uniq和stat_name=n_diff_pfx01时,stat_value值为2,这说明索引中第一列(e)包含两个distinct值。列e的distinct值可以通过查看表t1的列e的数据来进行确认,有两个distinct值(100)和(200)。计数列e在stat_description列中显示。

.当index_name=i2uniq和stat_name=n_diff_pfx02时,stat_value值为5,这说明索引中两列(e,f)包含五个distinct值。列e和f的distinct值可以通过查看表t1的列e和f的数据来进行确认,有五个distinct值(100,101),(200,102),(100,103),(200,104)和(100,105)。计数列(e,f)在stat_description列中显示。

使用innodb_index_stats表获取索引大小
表、分区或子分区的索引大小可以使用innodb_index_stats表来检索。在下面的例子中,检索表t1的索引大小。

mysql> select sum(stat_value) pages,index_name,sum(stat_value)*@@innodb_page_size size
    -> from mysql.innodb_index_stats where table_name='t1' and database_name='mysql' and stat_name='size' group by index_name;

+-------+------------+-------+
| pages | index_name | size  |
+-------+------------+-------+
|     1 | PRIMARY    | 16384 |
|     1 | i1         | 16384 |
|     1 | i2uniq     | 16384 |
+-------+------------+-------+
3 rows in set (0.04 sec)

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

对于分区或子分区,可以使用带有修改后的WHERE子句的相同查询来检索索引大小。例如,下面的查询检索表t1的分区的索引大小

mysql> select sum(stat_value) pages,index_name,sum(stat_value)*@@innodb_page_size size
    -> from mysql.innodb_index_stats where table_name like't1#P%' and database_name='mysql' and stat_name='size' group by index_name;
Empty set (0.01 sec)