MySQL

参考

卸载 MySQL

卸载 yum 安装的 MySQL

$ yum remove -y mysql*

# 验证卸载情况,如果存在继续删
$ rpm -qa | grep -i mysql

# 删除其他相关文件
$ rm -rf /var/lib/mysql
$ rm /etc/my.cnf

卸载 rpm 安装的 MySQL

# 查询已经安装的 mysql 包
$ rpm -qa | grep -i mysql
MySQL-test-5.5.54-1.el6.x86_64
MySQL-shared-5.5.54-1.el6.x86_64
MySQL-server-5.5.54-1.el6.x86_64
MySQL-client-5.5.54-1.el6.x86_64
MySQL-devel-5.5.54-1.el6.x86_64
MySQL-embedded-5.5.54-1.el6.x86_64
MySQL-shared-compat-5.5.54-1.el6.x86_64

# 如果有依赖卸载不了可以加参数 --nodeps 强制卸载
$ rpm -e MySQL-test-5.5.54-1.el6.x86_64 MySQL-shared-5.5.54-1.el6.x86_64 MySQL-server-5.5.54-1.el6.x86_64 ...

# 删除服务
$ chkconfig --list | grep -i mysql
$ chkconfig --del mysql
# 或者
$ systemctl disable mysqld

# 删除相关文件
$ whereis mysql 或者 find / -name mysql
$ mysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql /usr/share/mysql
$ rm -rf /usr/lib/mysql
$ rm -rf /usr/lib64/mysql
$ rm -rf /usr/include/mysql
$ rm -rf /usr/share/mysql
$ rm -rf /usr/my.cnf

安装 MySQL

rpm 包安装

建议安装 rpm 包,省时省力,只需要指定root密码,不需要额外配置什么东西就可以启动了。

$ rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm \
           mysql-community-common-5.7.22-1.el7.x86_64.rpm \
           mysql-community-libs-5.7.22-1.el7.x86_64.rpm \
           mysql-community-server-5.7.22-1.el7.x86_64.rpm

对于 Mysql5.7+ 的版本,为了加强安全性,为自动为 root 用户随机生成了一个密码,对于 RPM 安装的 Mysql,默认是/var/log/mysqld.log。并且只有在第一次启动 Mysql 才可以在日志中查看临时密码!

如果很不幸你忘记了密码,可以在/etc/my.cnf中添加skip-grant-tables,然后重启 Mysql,直接进入 Mysql 控制台,然后修改密码就可以了。

mysql> update mysql.user set authentication_string=password('123456') where user='root' and host='localhost';

然后注释掉/etc/my.cnf中的skip-grant-tables,重启就可以了。

如果修改密码的时候出现Your password does not satisfy the current policy requirements,说明你设置的密码不符合安全性规范,如果你就是想设置的简单一些,可以修改两个参数。

# validate_password_policy 的取值以及含义:
# 0: low      密码检查标准 Length
# 1: medium   密码检查标准 Length; numeric, lowercase/uppercase, and special characters
# 2: strong   密码检查标准 Length; numeric, lowercase/uppercase, and special characters; dictionary file
mysql> set global validate_password_policy=0;
# validate_password_length 参数默认为8,它有最小值的限制,最小值为4,即便你设置成1,它实际也会变成4
mysql> set global validate_password_length=4;

压缩包安装方式

$ wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz

$ tar -zxvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

$ cd /usr/local/

$ ln -s /usr/local/mysql-5.6.33-linux-glibc2.5-x86_64 mysql

$ cd mysql-5.6.33-linux-glibc2.5-x86_64

# deprecated in 5.7 
$ scripts/mysql_install_db --user=despacito
# 5.7
$ bin/mysqld --initialize --user=despacito (super user with random passwd in log) 
# 或者
$ bin/mysqld --initialize-insecure --user=despacito (no passwd) 

# 将启动脚本 mysql.server 放到 /etc/init.d 目录下,这样就可以使用 service 命令了
$ cp support-files/mysql.server /etc/init.d/mysqld
$ chmod 755 /etc/init.d/mysqld

# mysql 配置文件
$ cp support-files/my-default.cnf /etc/my.cnf

# 修改启动脚本
$ vim /etc/init.d/mysqld
# mysql 安装目录
basedir=/usr/local/mysql/
# 数据存储目录
datadir=/usr/local/mysql/data/

开启binlog日志

$ vim my.inf
# 开启 binlog
log_bin=ON
# binlog 日志基本文件名
log_bin_basename=/var/lib/mysql/mysql-bin
# binlog 的索引文件,用于管理所有 binlog 文件的目录
log_bin_index=/var/lib/mysql/mysql-bin.index

# 查看binlog是否开启成功
mysql> show variables like '%log_bin%'
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /var/lib/mysql/mysql-bin        |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index  |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+

成功开启binlog后,查看binlog文件索引信息

$ cat /var/lib/mysql/mysql-bin.index
/var/lib/mysql/mysql-bin.000001
/var/lib/mysql/mysql-bin.000002
/var/lib/mysql/mysql-bin.000003
/var/lib/mysql/mysql-bin.000004

启动/关闭 mysql 服务

# 查看命令
$ service mysql | service mysqld
# 支持的操作 {start|stop|restart|reload|force-reload|status}
# 例如启动服务,实际上执行的是 ./support-files/mysql.server start
$ service mysql start

# 或者使用 systemctl <option> mysqld

创建用户与分配权限

# 查看 mysql 详细用法
$ bin/mysql --help 

# root用户登录 
# 使用 -h 指定远程 mysql 服务器的 hostname,默认 localhost
$ mysql -u root -p

# 第一次登录必须修改生成的初始密码
# === 方式1 ===
$ bin/mysqladmin -u root -p password 新密码
# === 方式2 ===
mysql> SET PASSWORD = PASSWORD('your new password');
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
mysql> flush privileges;

# 创建一个新数据库实例,在 mysql shell 中`;`是必须的
mysql> create database testDB;

# 创建一个新用户 test,并将 testDB 的所有权限赋给它。localhost 指只有本机可以登录访问
mysql> grant all privileges on testDB.* to test@localhost identified by '1234';
# "%" 表示除了本机之外的所有主机可以登录访问,要想本机和所有其他主机都可以就同时执行这两条命令
mysql> grant all privileges on testDB.* to test@"%" identified by '1234';

# 创建用户也可以直接操作系统用户表 mysql.user
mysql> insert into mysql.user(Host, User, Password) values("localhost", "test", password("1234"));

# 赋予某些权限,和所有数据库
mysql> select, insert, update, delete, create, drop on *.* to test@localhost identified by '1234';

# Note:8.0 语法
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '你的密码';
mysql> GRANT ALL ON *.* TO 'root'@'%';
# 修改密码
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';

# 记得刷新系统权限表
mysql> flush privileges; 

其他命令

# 删除用户
mysql> Delete FROM mysql.user Where User='test' and Host='localhost';

# 修改用户密码
mysql> update mysql.user set password=password('新密码') where User="test" and Host="localhost";
mysql> flush privileges;

# 列出所有数据库
mysql> show databases;

# 切换数据库
mysql> use '数据库名';

# 列出所有表
mysql> show tables;

# 显示表结构
mysql> describe 表名;

# 删除数据库和表
mysql> drop database 数据库名;
mysql> drop table 数据表名;

忘记 mysql 用户名密码

# 首先关闭MySQL实例
$ bin/mysqld stop | service mysqld stop

# 安全模式启动MySQL
$ bin/mysqld_safe --skip-grant-tables &

# 进入MySQL Command Line
$ bin/mysql

# 进来之后,就可以修改密码了
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
mysql> flush privileges;

数据导出/导入

数据导出

# 查看基本使用方法
$ mysqldump

# 查看详细的使用方法
$ mysqldump --help 

# 导出表
$ mysqldump -uroot -p dbname table1 table2 > /root/data.sql

# 导出指定数据库
$ mysqldump -uroot -p --databases dbname1 dbname2 dbname3 > /root/data.sql

# 导出所有数据库
$ mysqldump -uroot -p --all-databases > /root/all_databases_data.sql

数据导入

$ mysql -uroot -p

mysql> source /root/all_databases_data.sql

如果有多个 sql 文件需要导入数据库,可以新建一个文件all.sql

$ vim all.sql
# 添加下面的内容
source /root/data1.sql
source /root/data2.sql

# 然后去 mysql 控制台执行
mysql> source /root/all.sql

导出表结构到 Excel

SELECT 
    COLUMN_NAME 列名,
    COLUMN_TYPE 列类型,
    DATA_TYPE 数据类型,
    CHARACTER_MAXIMUM_LENGTH 数据长度,
    IS_NULLABLE 是否为空,
    COLUMN_KEY 键类型,
    COLUMN_DEFAULT 默认值,
    COLUMN_COMMENT 备注
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'your_db_name'
AND
    TABLE_NAME = 'your_table_name'

异常记录

corruption in the InnoDB tablespace

这种错误一般都是由于断电(power breakdown)导致的。

$ less /var/log/mysqld.log

2019-05-22 21:59:55 0x7fad69ffb700  InnoDB: Assertion failure in thread 140382784435968 in file fut0lst.ic line 85
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
13:59:55 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=1000
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 405567 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fad500008c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fad69ffadb0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf0702b]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x7b93a1]
/lib64/libpthread.so.0(+0xf5d0)[0x7fad8b55b5d0]
/lib64/libc.so.6(gsignal+0x37)[0x7fad89f44207]
/lib64/libc.so.6(abort+0x148)[0x7fad89f458f8]
/usr/sbin/mysqld[0x789704]
/usr/sbin/mysqld[0x78922c]
/usr/sbin/mysqld[0x10a0815]
/usr/sbin/mysqld[0x10a2918]
/usr/sbin/mysqld(_Z9trx_purgemmb+0x3c9)[0x10a5119]
/usr/sbin/mysqld(srv_purge_coordinator_thread+0xded)[0x107d72d]
/lib64/libpthread.so.0(+0x7dd5)[0x7fad8b553dd5]
/lib64/libc.so.6(clone+0x6d)[0x7fad8a00bead]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

看错误日志是InnoDB tablespace文件损坏,直接导致mysql崩溃后启动不了了。看下日志中的线索https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html,可以在/etc/my.cnf中配置参数innodb_force_recovery为大于0的值以强制启动 InnoDB Storage Engine 以便将实际的数据文件 dump 出来,我这里改成1也没有成功启动,文档中介绍必要情况(比如还是起不来)可以调大该参数的值,最大是6(只读模式)。

# 备份原始数据库文件,不知道原始数据存在哪可以查看 /etc/my.cnf 的配置项 datadir=/data/mysql
$ mv /data/mysql /data/mysql_bak20190522

# 修改参数以强制启动 mysql innodb
$ vim /etc/my.cnf
[mysqld]
innodb_force_recovery = 6
$ systemctl start mysqld

# 导出所有数据
$ mysqldump -uroot -p --all-databases > /data/all_databases_data.sql

# 重装 mysql
$ rpm -qa | grep mysql
mysql-community-common-5.7.24-1.el7.x86_64
mysql-community-libs-compat-5.7.24-1.el7.x86_64
mysql-community-libs-5.7.24-1.el7.x86_64
mysql-community-server-5.7.24-1.el7.x86_64
mysql-community-client-5.7.24-1.el7.x86_64
$ rpm -e --nodeps mysql-community-common-5.7.24-1.el7.x86_64 ...
$ rpm -ivh mysql-community-common-5.7.24-1.el7.x86_64 ...

# 查看最近生成的临时密码
$ cat /var/log/mysqld.log | grep "temporary password" 
H+:*lcgPw3SD

$ mysql -uroot -pH+:*lcgPw3SD
# 修改初始密码
mysql> set password = password('your new password');
# 设置密码永不过期
mysql> alter user 'root'@'localhost' password expire never;
# 开启远程节点访问权限
mysql> grant all privileges on *.* to [email protected]"%" identified by 'your new password';
# 刷入操作
mysql> flush privileges;
# 导入数据
mysql> source /data/all_databases_data.sql

# 重启 mysql
# 理论上不需要重装 mysql 只要找到损坏的文件/表删掉就可以了,当然这也取决于你坏的到底是什么文件,我们这里坏的比较彻底,并且
# 里面存的是各个集群的元数据信息,数据量也不是很大,因此这个方式是最无脑也是最快的恢复的方式了。

mysql 默认innodb_force_recovery = 0,normal startup without forced recovery,当指定为较大的值时会包含所有更小值的功能,比如3包含了1和2的所有功能。如果能够使用innodb_force_recovery = 3或更低的值来转储表,只有损坏的单个页面上的某些数据会丢失,相对安全。4或更高就比较危险了,因为数据文件可能会永久损坏。6被认为是激烈的,因为数据库页面处于过时状态,这反过来可能会在B树和其他数据库结构中引入更多损坏。作为一种安全措施,当innodb_force_recovery大于0时,InnoDB 会阻止INSERTUPDATEDELETE操作。如果innodb_force_recovery设置为4或更高,InnoDB 会以只读模式启动。

死锁排查

mysql> SHOW ENGINE INNODB STATUS;
LATEST DETECTED DEADLOCK
------------------------
2020-06-28 14:31:21 0x7f8e40181700
*** (1) TRANSACTION:
TRANSACTION 618925, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 63 lock struct(s), heap size 8400, 8 row lock(s)
MySQL thread id 115798, OS thread handle 140248671397632, query id 10039891 10.53.5.59 dlink updating
UPDATE download_task_file  SET file_status='Running'  
 
 WHERE  task_id='8793c54cfb5940d09fdd19f70e25859a'
 AND task_item_id='f7588a983397426ab9c662e0c2a81cff'
 AND cluster='hadoop'
 AND hdfs_file_path='/data/datum/raw/others/20190617_遗留物体采集_part5_huangshiyao/part-r-00410.avro'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 211 page no 1045 n bits 152 index GEN_CLUST_INDEX of table `dlink`.`download_task_file` trx id 618925 lock_mode X locks rec but not gap waiting
Record lock, heap no 54 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 6; hex 000000010b2c; asc      ,;;
 1: len 6; hex 00000009576f; asc     Wo;;
 2: len 7; hex 26000003440975; asc &   D u;;
 3: len 30; hex 383739336335346366623539343064303966646431396637306532353835; asc 8793c54cfb5940d09fdd19f70e2585; (total 32 bytes);
 4: len 6; hex 6861646f6f70; asc hadoop;;
 5: len 30; hex 2f646174612f646174756d2f7261772f6f74686572732f32303139303631; asc /data/datum/raw/others/2019061; (total 77 bytes);
 6: len 7; hex 53756363656564; asc Succeed;;
 7: len 30; hex 383864616232393038343835346164633933636561653461346465383965; asc 88dab29084854adc93ceae4a4de89e; (total 32 bytes);

*** (2) TRANSACTION:
TRANSACTION 618924, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 3591
mysql tables in use 3, locked 3
30 lock struct(s), heap size 3520, 3 row lock(s)
MySQL thread id 115788, OS thread handle 140248937404160, query id 10039890 10.53.5.59 dlink updating
UPDATE download_task_file  SET file_status='Running'  
 
 WHERE  task_id='8793c54cfb5940d09fdd19f70e25859a'
 AND task_item_id='88dab29084854adc93ceae4a4de89e9b'
 AND cluster='hadoop'
 AND hdfs_file_path='/data/datum/raw/others/20190616_遗留物体采集_part4_huangshiyao/part-r-00510.avro'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 211 page no 1045 n bits 152 index GEN_CLUST_INDEX of table `dlink`.`download_task_file` trx id 618924 lock_mode X locks rec but not gap
Record lock, heap no 54 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 6; hex 000000010b2c; asc      ,;;
 1: len 6; hex 00000009576f; asc     Wo;;
 2: len 7; hex 26000003440975; asc &   D u;;
 3: len 30; hex 383739336335346366623539343064303966646431396637306532353835; asc 8793c54cfb5940d09fdd19f70e2585; (total 32 bytes);
 4: len 6; hex 6861646f6f70; asc hadoop;;
 5: len 30; hex 2f646174612f646174756d2f7261772f6f74686572732f32303139303631; asc /data/datum/raw/others/2019061; (total 77 bytes);
 6: len 7; hex 53756363656564; asc Succeed;;
 7: len 30; hex 383864616232393038343835346164633933636561653461346465383965; asc 88dab29084854adc93ceae4a4de89e; (total 32 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 211 page no 752 n bits 432 index download_task_file_fk of table `dlink`.`download_task_file` trx id 618924 lock_mode X locks rec but not gap waiting
Record lock, heap no 113 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 383739336335346366623539343064303966646431396637306532353835; asc 8793c54cfb5940d09fdd19f70e2585; (total 32 bytes);
 1: len 6; hex 000000010b2c; asc      ,;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 620395
Purge done for trx's n:o < 620395 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421725670569840, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670568016, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670567104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670568928, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670574400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670573488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670572576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670571664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670570752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670566192, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670565280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670564368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670563456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670562544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670560720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670561632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670558896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670557984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670555248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670553424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670559808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670556160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670619088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670557072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421725670554336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
2901 OS file reads, 1557620 OS file writes, 367881 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 10.80 writes/s, 3.13 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 20 merges
merged operations:
 insert 2, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 16 buffer(s)
Hash table size 276707, node heap has 9 buffer(s)
Hash table size 276707, node heap has 7 buffer(s)
Hash table size 276707, node heap has 13 buffer(s)
Hash table size 276707, node heap has 15 buffer(s)
Hash table size 276707, node heap has 9 buffer(s)
Hash table size 276707, node heap has 8 buffer(s)
Hash table size 276707, node heap has 72 buffer(s)
242.78 hash searches/s, 5.80 non-hash searches/s
---
LOG
---
Log sequence number 3221726781
Log flushed up to   3221726781
Pages flushed up to 3221726781
Last checkpoint at  3221726772
0 pending log flushes, 0 pending chkp writes
433982 log i/o's done, 1.93 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1099431936
Dictionary memory allocated 829744
Buffer pool size   65536
Free buffers       8192
Database pages     57195
Old database pages 21032
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 59088, not young 108859
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2869, created 118629, written 1027166
0.00 reads/s, 0.00 creates/s, 8.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 57195, unzip_LRU len: 0
I/O sum[728]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   16384
Free buffers       2048
Database pages     14304
Old database pages 5260
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14890, not young 65743
0.00 youngs/s, 0.00 non-youngs/s
Pages read 794, created 29668, written 205939
0.00 reads/s, 0.00 creates/s, 5.40 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14304, unzip_LRU len: 0
I/O sum[182]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   16384
Free buffers       2048
Database pages     14296
Old database pages 5257
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14495, not young 33000
0.00 youngs/s, 0.00 non-youngs/s
Pages read 650, created 29466, written 164276
0.00 reads/s, 0.00 creates/s, 1.20 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14296, unzip_LRU len: 0
I/O sum[182]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   16384
Free buffers       2048
Database pages     14299
Old database pages 5258
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14992, not young 3059
0.00 youngs/s, 0.00 non-youngs/s
Pages read 701, created 29711, written 457094
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14299, unzip_LRU len: 0
I/O sum[182]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   16384
Free buffers       2048
Database pages     14296
Old database pages 5257
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14711, not young 7057
0.00 youngs/s, 0.00 non-youngs/s
Pages read 724, created 29784, written 199857
0.00 reads/s, 0.00 creates/s, 1.40 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14296, unzip_LRU len: 0
I/O sum[182]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=30614, Main thread ID=140248851928832, state: sleeping
Number of rows inserted 6241674, updated 408291, deleted 1615041, read 1893514361
0.00 inserts/s, 1.00 updates/s, 0.00 deletes/s, 4261.05 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

打赏一个呗

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码支持
扫码打赏,一毛也是爱

打开支付宝扫一扫,即可进行扫码打赏哦