转载来源 ：
mysql怎样查询表的信息！ ： https://blog.51cto.com/liuqun/2044071
MySQL数据库root账户密码忘记两种处理方法转载 ： https://blog.51cto.com/lizhenliang/1977881
SHOW COLUMNS FROM 数据表：显示数据表的属性，属性类型，主键信息，是否为NULL，默认值等其他信息。
mysql> SHOW COLUMNS FROM runoob_tbl;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| runoob_id | int(11) | NO | PRI | NULL | |
| runoob_title | varchar(255) | YES | | NULL | |
| runoob_author | varchar(255) | YES | | NULL | |
| submission_date | date | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
SHOW INDEX FROM 数据表：显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
mysql> SHOW INDEX FROM runoob_tbl;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| runoob_tbl | 0 | PRIMARY | 1 | runoob_id | A | 2 | NULL | NULL | | BTREE | | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE ‘pattern’] \G：该命令输出MySQL数据库管理系统的性能以及统计信息。
mysql> SHOW TABLE STATUS FROM RUNOOB; # 显示数据库 RUNOOB 中所有表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'; # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G; # 加上 \G，查询结果按列打印
CentOS8编译安装MySQL8发生Could not find rpcgen错误
在CentOS8下编译安装MySQL8可能会出现Could not find rpcgen错误，而CentOS8默认的yum源下不提供rpcgen的安装包。所以需要到rpcgen的GitHub仓库上找，地址如下：
https://github.com/thkukuk/rpcsvc-proto/releases
参照以下步骤完成下载及编译安装：
[root@localhost /usr/local/src]# wget https://github.com/thkukuk/rpcsvc-proto/releases/download/v1.4/rpcsvc-proto-1.4.tar.gz
[root@localhost /usr/local/src]# tar -zxvf rpcsvc-proto-1.4.tar.gz
[root@localhost /usr/local/src]# cd rpcsvc-proto-1.4/ && ./configure && make && make install
https://www.jianshu.com/p/990355f175dd
CentOS8更换yum源后出现同步仓库缓存失败的问题
问题起因：一开始CentOS 8默认的yum源是能够正常使用的，但使用如下命令安装了Development Tools之后：
yum groupinstall -y "Development Tools"
不知道为啥就开始出现同步仓库缓存失败的问题，无法正常安装一些工具包。错误提示如下：
[root@localhost /etc/yum.repos.d]# yum makecache
CentOS-8.0 - AppStream 19 B/s | 38 B 00:02
CentOS-8.0 - Base 24 B/s | 38 B 00:01
CentOS-8.0 - Extras 8.4 B/s | 38 B 00:04
同步仓库 'AppStream' 缓存失败，忽略这个 repo。
同步仓库 'BaseOS' 缓存失败，忽略这个 repo。
同步仓库 'extras' 缓存失败，忽略这个 repo。
元数据缓存已建立。
[root@localhost /etc/yum.repos.d]#
于是根据错误提示信息到网上查找相关解决方案，大部分都是说更换阿里的yum源，然后就按官方文档的说明进行了更换：
https://developer.aliyun.com/mirror/centos
本来以为已经顺利解决了，想不到还有坑，重新建立元数据缓存时依旧提示同步仓库缓存失败：
[root@mesos-master /etc/yum.repos.d]# yum makecache
CentOS-8.0 - AppStream - mirrors.aliyun.com 0.0 B/s | 0 B 00:24
CentOS-8.0 - Base - mirrors.aliyun.com 0.0 B/s | 0 B 00:32
CentOS-8.0 - Extras - mirrors.aliyun.com 0.0 B/s | 0 B 00:32
CentOS-8.0 - Epel 2.3 MB/s | 6.0 MB 00:02
WANdisco SVN Repo 1.9 2.0 kB/s | 121 kB 01:01
同步仓库 'AppStream' 缓存失败，忽略这个 repo。
同步仓库 'base' 缓存失败，忽略这个 repo。
同步仓库 'extras' 缓存失败，忽略这个 repo。
上次元数据过期检查：0:00:01 前，执行于 2020年03月23日 星期一 10时26分48秒。
元数据缓存已建立。
[root@mesos-master /etc/yum.repos.d]#
然后百思不得其解，我寻思系统默认的yum源可能因为网络的原因连不上也就算了，国内的源不应该啊，是不是配置有问题呢？结果还真是，baseurl中的 r e l e a s e v e r 和 releasever和 releasever和basearch占位符不知为何无效。故将 r e l e a s e v e r 改 为 8 ， 将 releasever改为8，将 releasever改为8，将basearch改为x86_64后问题解决。修改后的各个配置文件内容如下：
### cat CentOS-Base.repo ###
[base]
name=CentOS-8 - Base - mirrors.aliyun.com
failovermethod=priority
baseurl=https://mirrors.aliyun.com/centos/8/BaseOS/x86_64/os/
http://mirrors.aliyuncs.com/centos/8/BaseOS/x86_64/os/
http://mirrors.cloud.aliyuncs.com/centos/8/BaseOS/x86_64/os/
gpgcheck=1
gpgkey=https://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-Official
### cat CentOS-AppStream.repo ###
[AppStream]
name=CentOS-8 - AppStream - mirrors.aliyun.com
failovermethod=priority
baseurl=https://mirrors.aliyun.com/centos/8/AppStream/x86_64/os/
http://mirrors.aliyuncs.com/centos/8/AppStream/x86_64/os/
http://mirrors.cloud.aliyuncs.com/centos/8/AppStream/x86_64/os/
gpgcheck=1
gpgkey=https://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-Official
### cat CentOS-Extras.repo ###
[extras]
name=CentOS-8 - Extras - mirrors.aliyun.com
failovermethod=priority
baseurl=https://mirrors.aliyun.com/centos/8/extras/x86_64/os/
http://mirrors.aliyuncs.com/centos/8/extras/x86_64/os/
http://mirrors.cloud.aliyuncs.com/centos/8/extras/x86_64/os/
gpgcheck=1
gpgkey=https://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-Official
### cat CentOS-Epel.repo ###
[epel]
name=CentOS-$releasever - Epel
baseurl=http://mirrors.aliyun.com/epel/8/Everything/$basearch
enabled=1
gpgcheck=0
### cat CentOS-PowerTools.repo ###
[PowerTools]
name=CentOS-8 - PowerTools - mirrors.aliyun.com
failovermethod=priority
baseurl=https://mirrors.aliyun.com/centos/8/PowerTools/x86_64/os/
http://mirrors.aliyuncs.com/centos/8/PowerTools/x86_64/os/
http://mirrors.cloud.aliyuncs.com/centos/8/PowerTools/x86_64/os/
gpgcheck=1
enabled=0
gpgkey=https://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-Official
### cat CentOS-centosplus.repo ###
[centosplus]
name=CentOS-8 - Plus - mirrors.aliyun.com
failovermethod=priority
baseurl=https://mirrors.aliyun.com/centos/8/centosplus/x86_64/os/
http://mirrors.aliyuncs.com/centos/8/centosplus/x86_64/os/
http://mirrors.cloud.aliyuncs.com/centos/8/centosplus/x86_64/os/
gpgcheck=1
enabled=0
gpgkey=https://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-Official
做完以上修改后问题解决，没有再提示同步仓库缓存失败了：
[root@localhost /etc/yum.repos.d]# yum makecache
CentOS-8 - AppStream - mirrors.aliyun.com 2.1 MB/s | 6.5 MB 00:03
CentOS-8 - Base - mirrors.aliyun.com 1.6 MB/s | 5.0 MB 00:03
CentOS-8 - Extras - mirrors.aliyun.com 1.8 kB/s | 4.2 kB 00:02
CentOS-8.0 - Epel 2.2 MB/s | 6.0 MB 00:02
WANdisco SVN Repo 1.9 10 kB/s | 121 kB 00:11
元数据缓存已建立。
[root@localhost /etc/yum.repos.d]#
1、MySQL数据库root账户密码忘记两种处理方
1.停止MySQL服务
# kill `cat /var/run/mysqld/mysqld.pid`
或者
# pkill mysqld
2.创建一个密码赋值语句的文本文件
# vi mysql-init
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass!6';
在加载授权表之前重置密码。
3.使用—init-file选项启动MySQL服务
# mysqld --init-file=mysql-init --user=mysql &
4.删除文本文件，使用新密码连接MySQL
# rm -f mysql-init
# mysql -uroot -p 'MyNewPass!6'
5.停止MySQL服务并正常启动
# kill `cat /var/run/mysqld/mysqld.pid`
# systemctl start mysqld
方法2：
1.停止MySQL服务
# kill `cat /var/run/mysqld/mysqld.pid`
# pkill mysqld
2.使用–skip-grant-tables --skip-networking选项启动MySQL服务
# mysqld --skip-grant-tables --skip-networking --user=mysql
–skip-grant-tables：跳过授权表认证
–skip-networking：加了跳过授权表选项后所有的人都可以无密码登录，这是很不安全的，此选项不监听网络，防止恶意登录。
3.无密码连接MySQL
# mysql
4.重置密码
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass!6';
5.停止MySQL服务并正常启动
# kill `cat /var/run/mysqld/mysqld.pid`
# systemctl startmysqld
2、为什么MySQL不推荐使用uuid或者雪花id作为主键？
https://mp.weixin.qq.com/s/0j_Z46vpXtLriFrSDy0DZA
3、MGR修改max_binlog_cache_size参数导致异常
一、问题来源
这是一位朋友的问题，因为前期朋友设置max_binlog_cache_size为8m，后面在线进行了修改了本参数，但是结果导致整个3节点的MGR集群除了primary节点其他两个second节点均掉线。大概的日志如下：
二、使用binlog cache的大概流程
这也是我以前写过的一个过程。
- 开启读写事务。
- 执行‘DML’语句，在‘DML’语句第一次执行的时候会分配内存空间给binlog cache缓冲区。
- 执行‘DML’语句期间生成的Event不断写入到binlog cache缓冲区。
- 如果binlog cache缓冲区已经写满了，则将binlog cache缓冲区的数据写入到binlog cache临时文件，同时清空binlog cache缓冲区，这个临时文件名以ML开头。
- 事务提交，binlog cache缓冲区和binlog cache临时文件数据全部写入到binary log中进行固化，释放binlog cache缓冲区和binlog cache临时文件。但是注意此时binlog cache缓冲区的内存空间留用供下次事务使用，但是binlog cache临时文件被截断为0，保留文件描述符。其实也就是IO_CACHE结构保留，并且保留IO_CACHE中分配的内存空间和临时文件文件描述符。
- 断开连接，这个过程会释放IO_CACHE同时释放其持有的binlog cache缓冲区内存以及持有的binlog cache临时文件。
三、max_binlog_cache_size参数的作用
这部分也是我以前记录过的。
max_binlog_cache_size：修改需要使用set global进行修改，定义了binlog cache临时文件的最大容量。如果某个事务的Event总量大于了（max_binlog_cache_size+binlog_cache_size）的大小那么将会报错，如下：
ERROR 1197 (HY000): Multi-statement transaction required more than
'max_binlog_cache_size' bytes of storage; increase this mysqld variable
and try again
我们在函数_my_b_write可以看到如下代码：
if (pos_in_file+info->buffer_length > info->end_of_file) //判断binlog cache临时文件的位置加上本次需要写盘的数据大于info->end_of_file的大小则抛错
{
errno=EFBIG;
set_my_errno(EFBIG);
return info->error = -1;
}
其中info->end_of_file的大小正是来自于我们的参数max_binlog_cache_size。
四、分析问题
从second节点的报错来看，是applier线程应用的事务超过了max_binlog_cache_size设置的大小，但是朋友已经修改了其大小，并且主库并没有报这个错误。
我们知道MGR applier线程从启动MGR的那一刻开始就不会停止，类似的master-slave的sql线程也是一样，我们修改参数是通过set global修改的参数，但是实际上在对于MGR的applier线程并不会生效。
但是对于主库来讲，我们修改参数后只要重启应用重新连接那么参数就生效了，这个时候实际上primary session的max_binlog_cache_size和second applier的max_binlog_cache_size并不一致，一旦有主库做一个稍大的事务，如果这个事务的binlog大于以前设置的值，主库虽然能成功，但是备节点就会由于applier线程的max_binlog_cache_size过小而导致备节点脱离整个集群。
对于这一点我们可以通过debug MySQL的sql线程进行验证。
五、验证
这里我们使用master-slave来进行验证，我们对sql线程进行debug。如下，
当前配置
sql线程
修改参数
主库执行一个事务，从库执行
我们可以查看sql线程binlog cache的IO CACHE的信息如下：
可以看到这个值还是老值。
重启后sql线程后，主库再做一个事务观察
很明显我们刚才修改的值重启sql线程后才生效。
因此故障原因得到证明。
Enjoy MySQL 😃
4、mysql登录报错“Access denied for user ‘root’@‘localhost’ (using password: YES”的处理方法
进入MySQL时报错：
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using
password: NO) denied for user ‘root’@‘localhost’ (using password: NO)
解决方法：
1.停止MySQL服务：service mysql stop
2.终端输入：mysqld_safe --user=mysql --skip-grant-tables --skip-networking & mysql -u root mysql
3.修改新密码
mysql>UPDATE user SET Password=PASSWORD('你的新密码') where USER='root';
4.刷新MySQL的系统权限相关表
mysql> FLUSH PRIVILEGES;
5.mysql -u root -p
输入新密码
5、误删除MySQL数据库表的ibd文件怎么办
很多年前，学习oracle10g的技术时，看过盖国强老师写的一篇文章，在linux系统中，oracle数据库存活的时候，用系统命令rm删除一个数据文件，这个时候找到删除文件的fd文件句柄，就可以将删除的文件找回，当时感觉特别的神奇，竟然还有这种骚操作。
既然oracle数据库可以恢复删除的数据文件，那在MySQL数据库中能不能玩这样的骚操作呢，下面就是我做的模拟测试，一起来看看吧。
模拟测试表
[root@localhost] 10:18:14 [(none)]>use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
[root@localhost] 10:18:19 [testdb]>
[root@localhost] 10:18:20 [testdb]>show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t_test |
| test1 |
+------------------+
2 rows in set (0.00 sec)
[root@localhost] 10:18:24 [testdb]>select * from test1;
+----+-------+-------+
| id | name | name2 |
+----+-------+-------+
| 1 | test1 | test1 |
| 2 | test | test |
+----+-------+-------+
2 rows in set (0.01 sec)
查找Mysql数据库进程IP
[root@mysql ~]# cd /proc/7988/fd
[root@mysql fd]# ll
总用量 0
lr-x------ 1 mysql mysql 64 9月 18 10:18 0 -> /dev/null
lrwx------ 1 mysql mysql 64 9月 18 10:18 42 -> /data/mysql/data/3306/mysql/tables_priv.MYD
lrwx------ 1 mysql mysql 64 9月 18 10:18 43 -> /data/mysql/data/3306/mysql/columns_priv.MYI
lrwx------ 1 mysql mysql 64 9月 18 10:18 44 -> /data/mysql/data/3306/mysql/columns_priv.MYD
lrwx------ 1 mysql mysql 64 9月 18 10:18 45 -> /data/mysql/data/3306/mysql/procs_priv.MYI
lrwx------ 1 mysql mysql 64 9月 18 10:18 46 -> /data/mysql/data/3306/mysql/procs_priv.MYD
lrwx------ 1 mysql mysql 64 9月 18 10:18 47 -> /data/mysql/data/3306/mysql/servers.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:18 48 -> /data/mysql/data/3306/mysql/slave_master_info.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:18 49 -> /data/mysql/data/3306/mysql/slave_relay_log_info.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:18 5 -> /tmp/ibTITlZK (deleted)
lrwx------ 1 mysql mysql 64 9月 18 10:18 50 -> /data/mysql/data/3306/mysql/slave_worker_info.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:18 51 -> /data/mysql/data/3306/mysql/event.MYI
lrwx------ 1 mysql mysql 64 9月 18 10:18 52 -> /data/mysql/data/3306/mysql/event.MYD
lrwx------ 1 mysql mysql 64 9月 18 10:21 53 -> socket:[39748]
lrwx------ 1 mysql mysql 64 9月 18 10:21 54 -> /data/mysql/data/3306/query_rewrite/rewrite_rules.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:21 55 -> /data/mysql/data/3306/testdb/test1.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:18 6 -> /tmp/ib3ojR5h (deleted)
lrwx------ 1 mysql mysql 64 9月 18 10:18 7 -> /tmp/ib9mkncP (deleted)
lrwx------ 1 mysql mysql 64 9月 18 10:18 8 -> /tmp/ibrfgk1T (deleted)
lrwx------ 1 mysql mysql 64 9月 18 10:18 9 -> /data/mysql/log/3306/redo/ib_logfile1
模拟删除业务表ibd文件
[mysql@mysql testdb]$ ls -l
total 728
-rw-rw-r--. 1 mysql mysql 485385 Jul 14 11:11 a.log
-rw-r-----. 1 mysql mysql 67 Jul 13 17:20 db.opt
-rw-r----- 1 mysql mysql 8618 Sep 18 10:31 test1.frm
-rw-r----- 1 mysql mysql 98304 Sep 18 10:32 test1.ibd
-rw-r-----. 1 mysql mysql 8618 Sep 9 15:23 t_test.frm
-rw-r-----. 1 mysql mysql 131072 Sep 9 15:25 t_test.ibd
[mysql@mysql testdb]$
[mysql@mysql testdb]$ rm test1.ibd
[mysql@mysql testdb]$ ls -l
total 632
-rw-rw-r--. 1 mysql mysql 485385 Jul 14 11:11 a.log
-rw-r-----. 1 mysql mysql 67 Jul 13 17:20 db.opt
-rw-r----- 1 mysql mysql 8618 Sep 18 10:31 test1.frm
-rw-r-----. 1 mysql mysql 8618 Sep 9 15:23 t_test.frm
-rw-r-----. 1 mysql mysql 131072 Sep 9 15:25 t_test.ibd
如果你在生产环境，不小心做了这个操作，估计这个时候已经背后一身冷汗了。
再次查看Mysql数据库进程所用使用的文件句柄
[root@mysql fd]# ls -l
lrwx------ 1 mysql mysql 64 9月 18 10:18 46 -> /data/mysql/data/3306/mysql/procs_priv.MYD
lrwx------ 1 mysql mysql 64 9月 18 10:18 47 -> /data/mysql/data/3306/mysql/servers.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:18 48 -> /data/mysql/data/3306/mysql/slave_master_info.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:18 49 -> /data/mysql/data/3306/mysql/slave_relay_log_info.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:18 5 -> /tmp/ibTITlZK (deleted)
lrwx------ 1 mysql mysql 64 9月 18 10:18 50 -> /data/mysql/data/3306/mysql/slave_worker_info.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:18 51 -> /data/mysql/data/3306/mysql/event.MYI
lrwx------ 1 mysql mysql 64 9月 18 10:18 52 -> /data/mysql/data/3306/mysql/event.MYD
lrwx------ 1 mysql mysql 64 9月 18 10:21 53 -> socket:[39748]
lrwx------ 1 mysql mysql 64 9月 18 10:21 54 -> /data/mysql/data/3306/query_rewrite/rewrite_rules.ibd
lrwx------ 1 mysql mysql 64 9月 18 10:21 55 -> /data/mysql/data/3306/testdb/test1.ibd (deleted)
lrwx------ 1 mysql mysql 64 9月 18 10:18 6 -> /tmp/ib3ojR5h (deleted)
lrwx------ 1 mysql mysql 64 9月 18 10:18 7 -> /tmp/ib9mkncP (deleted)
lrwx------ 1 mysql mysql 64 9月 18 10:18 8 -> /tmp/ibrfgk1T (deleted)
lrwx------ 1 mysql mysql 64 9月 18 10:18 9 -> /data/mysql/log/3306/redo/ib_logfile1
[root@mysql fd]#
这个表在一定的时间范围内，还是可以进行DML操作
[root@localhost] 10:49:54 [testdb]>update test1 set name2='dsljfld' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[root@localhost] 10:50:12 [testdb]>select * from test1;
+----+-------+---------+
| id | name | name2 |
+----+-------+---------+
| 1 | test1 | test1 |
| 2 | test | dsljfld |
+----+-------+---------+
2 rows in set (0.00 sec)
[root@localhost] 10:50:36 [testdb]>insert into test1 values(3,'dsf','sfsf');
Query OK, 1 row affected (0.01 sec)
[root@localhost] 10:50:45 [testdb]>select * from test1;
+----+-------+---------+
| id | name | name2 |
+----+-------+---------+
| 1 | test1 | test1 |
| 2 | test | dsljfld |
| 3 | dsf | sfsf |
+----+-------+---------+
为了保险起见，建议将这个表进行lock操作，只能read操作
[root@localhost] 10:58:17 [testdb]>lock tables test1 read;
Query OK, 0 rows affected (0.00 sec)
[root@localhost] 10:59:07 [testdb]>
[root@localhost] 10:59:09 [testdb]>insert into test1 values(4,'esdsf','dfesfsf');
ERROR 1099 (HY000): Table 'test1' was locked with a READ lock and can't be updated
你会发现，刚才rm test1.ibd文件，在这里还可以看到，这个时候，我们要做的是，赶紧将这个文件拷贝回去。
[mysql@mysql fd]$ cp /proc/9015/fd/54 /data/mysql/data/3306/testdb/test1.ibd
[mysql@mysql testdb]$ ls -l
total 728
-rw-rw-r--. 1 mysql mysql 485385 Jul 14 11:11 a.log
-rw-r-----. 1 mysql mysql 67 Jul 13 17:20 db.opt
-rw-r----- 1 mysql mysql 8618 Sep 18 10:31 test1.frm
-rw-r----- 1 mysql mysql 98304 Sep 18 11:01 test1.ibd
-rw-r-----. 1 mysql mysql 8618 Sep 9 15:23 t_test.frm
-rw-r-----. 1 mysql mysql 131072 Sep 9 15:25 t_test.ibd
可以看到文件已经完全找回去了。这个时候，可以将表的read lock释放了
[root@localhost] 11:02:56 [testdb]>unlock tables;Query OK, 0 rows affected (0.00 sec)
[root@localhost] 11:03:25 [testdb]>insert into test1 values(4,'esdsf','dfesfsf');
Query OK, 1 row affected (0.00 sec)
此时你重启MySQL数据库都是没有任何问题的。
总结
为什么能通过这种方式，恢复rm掉的ibd文件呢，主要是因为当我们用rm命令删除的时候，Mysql数据库进程还在持有被删除的ibd文件的句柄，也就是在/proc/{mysql_pid}/pd目录下可以找到，如果你这个时候，重启了Mysql数据库实例，Mysql进程就会释放掉删除文件的句柄，你就真的访问不到被删除的文件了，这种情况下，要恢复被删除的表，就会很费时和费力了。
6、实战：一文带你解决Mysql主从复制日常错误
主从复制最常见的2种错误
第一种：主键冲突（Error_code: 1062）
第二种：记录丢失，例如update,delete操作，在从库找不到对应记录（Error_code: 1032）
下面来详细模拟一下记录丢失，处理全过程
检查主从复制是否正常
[root@localhost] 11:34:29 [testdb]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000029
Read_Master_Log_Pos: 3683
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 2207
Relay_Master_Log_File: binlog.000029
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
可以看到IO线程和SQL线程运行都是正常的。
创建测试表和记录
[root@localhost] 11:25:48 [testdb]>show create table test1\G;
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`name1` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`name2` char(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.07 sec)
insert into test1 values(1,'test1','test1');
insert into test1 values(2,'test2','test2');
insert into test1 values(3,'test3','test3');
模拟主从复制由于从库记录缺失，导致主从复制失败
第一步：在从库中删除id=2的记录
[root@localhost] 11:26:41 [testdb]>delete from test1 where id=2;
Query OK, 1 row affected (0.44 sec)
[root@localhost] 11:26:52 [testdb]>select * from test1;
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
| 1 | test1 | test1 |
| 3 | test3 | test3 |
+----+-------+-------+
2 rows in set (0.00 sec)
第二步：在主库上删除id=2的记录
[root@localhost] 11:27:11 [testdb]>delete from test1 where id=2;
Query OK, 1 row affected (0.17 sec)
[root@localhost] 11:27:51 [testdb]>select * from test1;
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
| 1 | test1 | test1 |
| 3 | test3 | test3 |
+----+-------+-------+
2 rows in set (0.00 sec)
在从库上查看主从复制情况
[root@localhost] 11:34:05 [testdb]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000029
Read_Master_Log_Pos: 3683
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 1929
Relay_Master_Log_File: binlog.000029
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table testdb.test1; Can't find record in 'test1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000029, end_log_pos 3652
Skip_Counter: 0
Exec_Master_Log_Pos: 3405
Relay_Log_Space: 2414
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows event on table testdb.test1; Can't find record in 'test1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000029, end_log_pos 3652
Replicate_Ignore_Server_Ids:
Master_Server_Id: 111213106
Master_UUID: 3ada166e-c4db-11ea-b21d-000c29cc2388
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 200904 11:33:10
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3ada166e-c4db-11ea-b21d-000c29cc2388:84830-84835
Executed_Gtid_Set: 3ada166e-c4db-11ea-b21d-000c29cc2388:1-84834,
3ada166e-c4db-11ea-b21d-000c29cc2389:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
此时主从的sql线程已经是停止状态，主从复制的数据已经不同步了。复制开始报1032错误了。
要解决1032错误，可以有以下3中方案
方案一：手工将缺失的业务记录在主库上导出，并导入到从库，然后启动从库的sql线程就可以了。慢着，大家有没有注意到一个问题，就是在主库上，到底要导出哪条记录，报错信息里并没有，但是有提示，he event’s master log binlog.000029, end_log_pos 3652，所以还需要将binlog日志里的内容解析处理，找到要操作的记录，似乎有些麻烦。不用慌，还有方案二，方案三。
方案二：Mysql数据库提供一个参数slave_skip_errors，这个参数可以跳过指定错误代码的sql语句，例如：slave_skip_errors=1032，可惜，这个参数不能在线修改，修改生效需要重启实例，是不是也太友好。
[root@localhost] 11:28:57 [testdb]>set global slave_skip_errors=1032;
ERROR 1238 (HY000): Variable 'slave_skip_errors' is a read only variable
方案三：使用percona-toolkits工具集中的pt-slave-restart工具，自动跳过主从同步指定的报错代码sql语句，此方法对mysql数据侵入性小，不必重启Mysql实例
[mysql@mysql ~]$ pt-slave-restart --user=root --password=root --socket=/data/mysql/run/3306/mysql.sock --error-numbers=1032
# A software update is available:
2020-09-04T11:32:07 S=/data/mysql/run/3306/mysql.sock,p=...,u=root mysql-relay-bin.000003 1651 1032
当跳过主从同步指定的报错代码sql语句，主从复制恢复之后，间隔64秒，会再次自动检测主从复制是否有1032错误。
其它类似的错误，都可以用以上三种方法方案处理，建议大家使用方案三。
7、MySQL数据库页损坏怎么办，innodb_force_recovery参数帮你解决问题
有些时候，你的MySQL数据库是单机，没有主从和高可用，如果宕机，或者其它异常情况，导致你的ibd文件损坏，这个时候，你的MySQL数据库实例无法启动了，而你又需要导出MySQL数据库中关键数据，这时innodb_force_recovery参数能救你一命，首先你需要了解innodb_force_recovery作用。
innodb_force_recovery默认为0，innodb_force_recovery可以设置1到6。较大的值包括较小值所有功能。例如，3包含1和2的所有功能。
设置innodb_force_recovery值等于或小于3，MySQL数据库的表是相对安全，此时仅丢失了损坏的单个页面上的某些数据。设置成4或更大的值是非常危险的，此时可能会导致页数据永久损坏。
为保护数据，InnoDB会在innodb_force_recovery大于 0 时阻止INSERT，UPDATE或DELETE操作。innodb_force_recovery设置为 4 或更大时会将InnoDB置于只读模式。
下面来介绍一下innodb_force_recovery设置各种值的作用
innodb_force_recovery=1 ( SRV_FORCE_IGNORE_CORRUPT )
此时MySQL数据库即使检测到损坏的page也可以运行。可以尝试使SELECT * FROM tab跳过损坏的索引记录和页面，可以恢复没有损坏的业务数据。
innodb_force_recovery=2 ( SRV_FORCE_NO_BACKGROUND )
阻止master thread和任何purge threads运行。如果在purge操作期间发生崩溃，则此恢复值将阻止它。
innodb_force_recovery=3 ( SRV_FORCE_NO_TRX_UNDO )
在crash recovery之后不执行事务rollbacks。
innodb_force_recovery=4 ( SRV_FORCE_NO_IBUF_MERGE )
防止insert buffer合并操作，不计算 tablestatistics。此时可能会永久损坏数据文件，需要删除并重新创建所有二级索引。
innodb_force_recovery=5 ( SRV_FORCE_NO_UNDO_LOG_SCAN )
启动数据库时不检查undo logs：InnoDB甚至将未完成的事务都视为已提交。此值可能会永久损坏数据文件。将InnoDB设置为只读。
innodb_force_recovery=6 ( SRV_FORCE_NO_LOG_REDO )
不进行与恢复有关的redo log前滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态，从而可能导致 B 树和其他数据库结构遭受更多破坏。将InnoDB设置为只读。
创建模拟表
mysql> show create table t_test\G;
*************************** 1. row ***************************
Table: t_test
Create Table: CREATE TABLE `t_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(12) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`paymont` double(10,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)
mysql> select * from t_test;
+----+---------+-------------+-------------+-----------+
| id | k | c | pad | paymont |
+----+---------+-------------+-------------+-----------+
| 1 | 4992833 | 83868641912 | 67847967377 | 200000.00 |
| 2 | 5019684 | 38014276128 | 23183251411 | NULL |
| 3 | 5042604 | 33973744704 | 38615512647 | NULL |
| 4 | 5020133 | 37002370280 | 63947013338 | NULL |
| 5 | 4998122 | 44257470806 | 34551750492 | NULL |
| 6 | 5005844 | 37216201353 | 05161542529 | NULL |
| 7 | 5013709 | 33071042495 | 91798303270 | NULL |
| 8 | 4999376 | 73754818686 | 76460662325 | NULL |
| 9 | 5272683 | 26482547570 | 30508501104 | NULL |
| 10 | 5033749 | 05677017559 | 29489382504 | NULL |
| 11 | 5495590 | 69916792160 | 87387995487 | NULL |
| 12 | 4877585 | 06636928111 | 89292458800 | NULL |
+----+---------+-------------+-------------+-----------+
12 rows in set (0.00 sec)
模拟表的页损坏
要模拟t_test表页损坏，其实很简单，可以通过vi工具，在t_test.ibd文件开始行添加N个！就可以
重启MySQL服务
模拟损坏之后，就重启MySQL数据库实例，同时看MySQL数据库错误日志
2020-09-01T12:58:25.207448Z 0 [Note] InnoDB: Loading buffer pool(s) from /u02/log/3308/iblog/ib_buffer_pool
2020-09-01T12:58:25.209575Z 0 [ERROR] InnoDB: Space ID in fsp header is 134217728, but in the page header it is 660067840.
2020-09-01T12:58:25.331762Z 0 [ERROR] [FATAL] InnoDB: Tablespace id is 48 in the data dictionary but in file ./sbtest/t_test.ibd it is 18446744073709551615!
2020-09-01 20:58:25 0x7ffb8d022700 InnoDB: Assertion failure in thread 140718379247360 in file ut0ut.cc line 942
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.
看到没，此时数据库实例已经无法正常启动了，原因是因为t_test.ibd文件头部记录的Tablespace id与数据字典不一致。怎么办
设置innodb_force_recovery
因为此时t_test.ibd文件头部记录的Tablespace id与数据字典不一致，只能在my.cnf文件中添加innodb_force_recovery=4，然后启动MySQL数据库实例
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1 |
| t_pay_test |
| t_test |
+------------------+
3 rows in set (0.00 sec)
mysql> select * from t_test;
ERROR 1812 (HY000): Tablespace is missing for table `sbtest`.`t_test`.
mysql>
mysql> drop table t_test;
Query OK, 0 rows affected (0.02 sec)
由于此时已经无法识别t_test.ibd文件了，此时只能drop掉t_test表了。
如果只是损坏了页数据，可以用下面的sql语句恢复业务数据
insert ignore into t_test_recovery select * from t_test limit 5;
insert ignore into t_test_recovery select * from t_test limit 10;
8、MGR修改max_binlog_cache_size参数导致异常
一、问题来源
这是一位朋友的问题，因为前期朋友设置max_binlog_cache_size为8m，后面在线进行了修改了本参数，但是结果导致整个3节点的MGR集群除了primary节点其他两个second节点均掉线。大概的日志如下：
二、使用binlog cache的大概流程
这也是我以前写过的一个过程。
- 开启读写事务。
- 执行‘DML’语句，在‘DML’语句第一次执行的时候会分配内存空间给binlog cache缓冲区。
- 执行‘DML’语句期间生成的Event不断写入到binlog cache缓冲区。
- 如果binlog cache缓冲区已经写满了，则将binlog cache缓冲区的数据写入到binlog cache临时文件，同时清空binlog cache缓冲区，这个临时文件名以ML开头。
- 事务提交，binlog cache缓冲区和binlog cache临时文件数据全部写入到binary log中进行固化，释放binlog cache缓冲区和binlog cache临时文件。但是注意此时binlog cache缓冲区的内存空间留用供下次事务使用，但是binlog cache临时文件被截断为0，保留文件描述符。其实也就是IO_CACHE结构保留，并且保留IO_CACHE中分配的内存空间和临时文件文件描述符。
- 断开连接，这个过程会释放IO_CACHE同时释放其持有的binlog cache缓冲区内存以及持有的binlog cache临时文件。
三、max_binlog_cache_size参数的作用
这部分也是我以前记录过的。
max_binlog_cache_size：修改需要使用set global进行修改，定义了binlog cache临时文件的最大容量。如果某个事务的Event总量大于了（max_binlog_cache_size+binlog_cache_size）的大小那么将会报错，如下：
ERROR 1197 (HY000): Multi-statement transaction required more than
'max_binlog_cache_size' bytes of storage; increase this mysqld variable
and try again
我们在函数_my_b_write可以看到如下代码：
if (pos_in_file+info->buffer_length > info->end_of_file) //判断binlog cache临时文件的位置加上本次需要写盘的数据大于info->end_of_file的大小则抛错
{
errno=EFBIG;
set_my_errno(EFBIG);
return info->error = -1;
}
其中info->end_of_file的大小正是来自于我们的参数max_binlog_cache_size。
四、分析问题
从second节点的报错来看，是applier线程应用的事务超过了max_binlog_cache_size设置的大小，但是朋友已经修改了其大小，并且主库并没有报这个错误。
我们知道MGR applier线程从启动MGR的那一刻开始就不会停止，类似的master-slave的sql线程也是一样，我们修改参数是通过set global修改的参数，但是实际上在对于MGR的applier线程并不会生效。
但是对于主库来讲，我们修改参数后只要重启应用重新连接那么参数就生效了，这个时候实际上primary session的max_binlog_cache_size和second applier的max_binlog_cache_size并不一致，一旦有主库做一个稍大的事务，如果这个事务的binlog大于以前设置的值，主库虽然能成功，但是备节点就会由于applier线程的max_binlog_cache_size过小而导致备节点脱离整个集群。
对于这一点我们可以通过debug MySQL的sql线程进行验证。
五、验证
这里我们使用master-slave来进行验证，我们对sql线程进行debug。如下，
当前配置
sql线程
修改参数
主库执行一个事务，从库执行
我们可以查看sql线程binlog cache的IO CACHE的信息如下：
可以看到这个值还是老值。
重启后sql线程后，主库再做一个事务观察
很明显我们刚才修改的值重启sql线程后才生效。
因此故障原因得到证明。
9、一条更新操作引起的MySQL主从复制异常
一、环境描述
生产环境异地机房主从数据库，数据量过百G，数据库版本社区版本5.6.25。
二、问题描述
同事根据开发提供的SQL在Master节点执行了一个大表的的全表更新操作，导致从节点Slave IO线程中断。
三、问题分析
1)相关参数
my.cnf中有两个参数设置：
expire_logs_days = 7 #binlog保留时间7天
max_binlog_size = 1G #binlog大小
2)表大小，执行SQL
Table: v_clda 5.8G
Sql: update v_clda set uploadtime ＝now(); 主库执行成功
3)主库，大事物产生的binlog
-rw-rw---- 1 mysql mysql 1.1G Mar 16 02:49 mysql-bin.000159
-rw-rw---- 1 mysql mysql 8.0G Mar 16 15:28 mysql-bin.000160
-rw-rw---- 1 mysql mysql 7.4G Mar 16 18:13 mysql-bin.000161
-rw-rw---- 1 mysql mysql 1.1G Mar 16 23:55 mysql-bin.000162
-rw-rw---- 1 mysql mysql 1.1G Mar 17 12:15 mysql-bin.000163
-rw-rw---- 1 mysql mysql 1.1G Mar 18 16:54 mysql-bin.000164
4)异地从库报错
[ERROR] Slave I/O: Unexpected master's heartbeat data: heartbeat is not compatible with local info;the event's data:og_file_name mysql-bin.000160<90>Ó°Y log_pos 121238917, Error_code: 1623
[ERROR] Slave I/O: Relay log write failure: could not queue event from master, Error_code: 1595
[Note] Slave I/O thread exiting, read up to log 'mysql-bin.000160', position 3626103968
[Note] Error reading relay log event: slave SQL thread was killed
Slave 已经无法同步数据。
一个事物只能写入一个binlog日志中，默认情况下，binlog日志达到设定值后(max_binlog_size)，会自动生成一个新的日志文件，也会根据过期参数(expire_logs_days)设置自动删除binlog日志。如果生成了一个超大的binlog日志，很可能是由于大事物引起的。
尝试从启slave线程，多次尝试后失败。
尝试跳过事物，具体方法如下：
从节点执行(基于GTID)
stop slave;
SET @@SESSION.GTID_NEXT= '498815d6-20a9-11e6-a7d6-fa163e5770cc:53'; --根据实际情况
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;
START SLAVE;
show slave status\G; 主从复制恢复正常
从节点执行更新操作，同步数据
set session sql_log_bin=0;
update v_clda set uploadtime ＝now();
在执行大事物前关闭 set session sql_log_bin=0; （默认是开启的），尤其是异地机房，网络带宽有限，而且VPN通道不是十分稳定的情况下。不允许它生成大量binlog日志。
如果像本例中，已经执行了，而且生成了大量的binlog，最终导致复制异常，可以考虑使用跳过事物的方法来解决这个问题。
最笨的方法就是重新搭建主从，由于数据量比较大，还是异地不可取。
根本解决方法还是要拆分大事物，进行批量提交操作。贺春旸老师的MySQL管理之道一书中第四章4.4节有具体的解决方法。
参考改为用存储过程，每删除10000条事务就提交一次，循环操作直至删除完毕。经过优化，行锁的范围变小了，性能也就变好了。相关代码如下：
DELIMITER
USE BIGDB
USE BIGDB
DROP PROCEDURE IF EXISTS BIG_table_delete_10k
CREATE PROCEDURE BIG_table_delete_10k(IN v_UserId INT) BEGIN del_10k:LOOP delete from BIGDB.BIGTABLE where UserId = v_UserId limit 10000; select row_count() into @count; IF @count = 0 THEN select CONCAT('BIGDB.BIGTABLE UserId = ',v_UserId,' is ',@count,' rows.') as BIGTABLE_delete_finish; LEAVE del_10k; END IF; select sleep(1); END LOOP del_10k; END
CREATE PROCEDUREBIG
table delete 1 0k(INv U serIdINT)BEGINdel 1 0k:LOOPdelete fromBIGDB.BIGTABLEwhereUserId=v U serIdlimit10000;select row c ount()into@count;IF @count=0THENselectCONCAT(
′ BIGDB.BIGTABLEUserId= ′,v U serId, ′is′ ,@count, ′ rows. ′ )asBIGTABLE delete f inish;LEAVE del 1 0k;ENDIF;select sleep(1);ENDLOOP del
1 0k;END
DELIMITER ;
10、一次诡异的磁盘IO使用率高排查
问题描述
前段时间，一个Mysql数据库服务器磁盘IO写操作非常高，经过排查，发现是mysql线程导致的，磁盘读写情况，如下图所示
从上图可以看到磁盘IO已经达到6.77M/S，mysql的版本是5.6，这个版本还没有performance_schema.threads视图的THREAD_OS_ID字段，没办法将操作系统的thread_id和mysql数据库线程id进行关联，不能精确定位是mysql那个线程产生的磁盘IO写操作。
排查过程
于是登录mysql数据库，临时开始Mysql数据库的general_log，查看日志记录，也没有发现什么特殊操作
[root@mysql ~]# ps -ef|grep -i mysqld
mysql 11650 1 0 8月29 ? 00:00:00 /bin/sh /data/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql/conf/3308/my.cnf
mysql 12694 11650 2 8月29 ? 00:59:29 /data/mysql/bin/mysqld --defaults-file=/data/mysql/mysql/conf/3308/my.cnf --basedir=/data/mysql --datadir=/data/mysql/mysql/data/3308 --plugin-dir=/data/mysql/lib/plugin --log-error=/data/mysql/mysql/log/3308/error.log --open-files-limit=65535 --pid-file=/data/mysql/mysql/run/3308/mysql.pid --socket=/data/mysql/mysql/run/3308/mysql.sock --port=3308
root 49777 49737 0 15:58 pts/0 00:00:00 grep --color=auto -i mysqld
[root@mysql ~]#
[root@mysql ~]#
[root@mysql ~]# perf top -p 12694
观察结果如下图所示
从图中可以看到，buf_calc_page_new_checksum 函数操作占用的资源最多。
buf_calc_page_new_checksum
那么buf_calc_page_new_checksum 这个函数到底是做什么的呢？
在mysql刷盘时，会调用这个函数，这个函数的作用是checksum，并写入页中。，其调用顺序如下所示
buf_flush_page–>buf_flush_write_block_low–>buf_flush_init_for_writing–>buf_calc_page_new_checksum
问题原因
从上面可以看出，是Mysql数据库在做刷脏页操作，导致磁盘的IO写操作特别高，这也是为什么在general_log看不出什么特殊操作的原因。
11、 SQLException: Lock wait timeout exceeded; try restarting transaction,please rollback!
第一个问题目前发生的原因有：
-
磁盘空间满，事务无法提交成功。(磁盘满是一个很危险的操作，会引起binlog写坏，备库无法同步进而需要恢复备库)
-
更新事务未正常提交而产生排他锁，造成其他更新事务一直获取不到该锁而事务超时。
12、条件查询卡住了，怎么重跑都通不过，怎么办，急死人了(迁移后比对实际出现)。
Truncate table过程中CTRL +C 终止了。 有分片上存在truncate 事务一直存在，进而对该表的所有操作均会超时
13、查询卡住，更新卡住…殊不知，你前面的Alter table都没成功…
DBProxy的问题不在此文讨论，查询事务没有正常提交而占据共享锁时，同样会造成alter table获取不到MDL锁，而造成一直等待。 提示为：Waiting fortable metadata lock (show processlist中可查)
8 种常见 SQL 错误用法
https://mp.weixin.qq.com/s/1mllyZyg-zcf4t6CvFeBbQ
1292 – Incorrect datetime value: ‘0000-00-00 00:00:00’ 原因与解决方法
https://www.fujieace.com/mysql/incorrect-datetime-value.html
今天我用 phpMyadmin 创建了一个"user"表，里面也插入了几十条数据，然后我想再添加一个字段“register-time"，结果给我报如下错误：
#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'register-time'
中文翻译
＃1292-错误的日期时间值：“register-time”列为“ 0000-00-00 00:00:00”
原因：
这是因为当前的MySQL模式不支持datetime为0的情况。
解决方法
解决方法也非常的简单，只需要直接修改“sql_mode”即可！以我的Mysql5.7版本为例子，具体解决方法如下：
1、在MySQL配置文件 my.ini 的 [mysqld] 下添加：
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2、重启MySQL。
14、MySQL字符集介绍及乱码解决方法
一、字符集本概念
字符集的基本概念如下 :
• 字符(Character)是指人类语言中最小的表义符号。例如’A’、’B’等
• 给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这一数值就是字符的编码(Encoding)。例如，我们给字符’A’赋予数值0，给字符’B’赋予数值1，则0就是字符’A’的编码
• 给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集(Character Set)。例如，给定字符列表为{’A’,’B’}时，{’A’=>0, ‘B’=>1}就是一个字符集
• 字符序(Collation)是指在同一字符集内字符之间的比较规则
• 确定字符序后，才能在一个字符集上定义什么是等价的字符，以及字符之间的大小关系
• 每个字符序唯一对应一种字符集，但一个字符集可以对应多种字符序，其中有一个是默认字符序(Default Collation)
• MySQL中的字符序名称遵从命名惯例：以字符序对应的字符集名称开头；以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如：在字符序“utf8_general_ci”下，字符“a”和“A”是等价的
二、常见字符集
1） ASCII
ASCII是英文American Standard Code for Information Interchange的缩写,美国标准信息交换代码是由美国国家标准学会(American National Standard Institute , ANSI )制定的，标准的单字节字符编码方案，用于基于文本的数据。是基于拉丁字母的一套电脑编码系统。它主要用于显示现代英语和其他西欧语言。它是现今最通用的 单字节编码系统，并等同于国际标准ISO/IEC 646。
ASCII 码使用指定的7 位或8 位二进制数组合来表示128 或256 种可能的字符。标准ASCII 码也叫基础ASCII码，使用7 位二进制数来表示所有的大写和小写字母，数字0 到9、标点符号， 以及在美式英语中使用的特殊控制字符。
2）GBK
GBK即汉字内码扩展规范，K为扩展的汉语拼音中“扩”字的声母。英文全称Chinese Internal Code Specification。GBK编码标准兼容GB2312，共收录汉字21003个、符号883个，并提供1894个造字码位，简、繁体字融于一库。
GB2312码是中华人民共和国国家汉字信息交换用编码，全称《信息交换用汉字编码字符集——基本集》，1980年由国家标准总局发布。基本集共收入汉字 6763个和非汉字图形字符682个，通行于中国大陆。新加坡等地也使用此编码。GBK是对GB2312-80的扩展，也就是CP936字码表 (Code Page 936)的扩展（之前CP936和GB 2312-80一模一样）。
3）latin1
Latin1是ISO-8859-1的别名，有些环境下写作Latin-1。
ISO-8859-1
ISO-8859-1编码是单字节编码，向下兼容ASCII，其编码范围是0x00-0xFF，0x00-0x7F之间完全和ASCII一致，0x80-0x9F之间是控制字符，0xA0-0xFF之间是文字符号。
ISO-8859-1收录的字符除ASCII收录的字符外，还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。欧元符号出现的比较晚，没有被收录在ISO-8859-1当中。
因为ISO-8859-1编码范围使用了单字节内的所有空间，在支持ISO-8859-1的系统中传输和存储其他任何编码的字节流都不会被抛弃。换言之，把其他任何编码的字节流当作ISO-8859-1编码看待都没有问题。这是个很重要的特性，MySQL数据库默认编码是Latin1就是利用了这个 特性。ASCII编码是一个7位的容器，ISO-8859-1编码是一个8位的容器。
4）UTF-8
UTF-8（8-bit Unicode Transformation Format）是一种针对Unicode的可变长度字符编码，又称万国码。由Ken Thompson于1992年创建。现在已经标准化为RFC 3629。UTF-8用1到4个字节编码UNICODE字符。用在网页上可以同一页面显示中文简体繁体及其它语言（如日文，韩文）
UTF-8以字节为单位对Unicode进行编码。
UTF-8的特点是对不同范围的字符使用不同长度的编码。对于0x00-0x7F之间的字符，UTF-8编码与ASCII编码完全相同。UTF-8 编码的最大长度是4个字节。
从上表可以看出，4字节模板有21个x，即可以容纳21位二进制数字。Unicod的最大码位0x10FFFF也只有21 位。
三、MySQL字符集转换过程
-
MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection
-
进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下:
- 使用每个Column的CHARACTER SET设定值；
- 如未设置Column的Character SET，则使用对应表的DEFAULT CHARACTER SET设定值
- 如Column、Table均未设地Charater SET，则使用对应数据库的DEFAULT CHARACTER SET设定值
- 如Column、Table、Database均未设地Charater SET，则使用character_set_server设定值。
- 将操作结果从内部操作字符集转换为character_set_results
四、中文乱码现象
Mysqldump导入乱码
Load data导入乱码
五、程序及MySQL字符集配置情况
出现乱码情况，首先确认写入程序使用的字符集，MySQL使用的字符集
MySQL使用的字符集情况
通过SHOW VARIABLES LIKE ‘character%’，在上面例子中通过语句获得字符集设置情况如下:
Mysqldump导入乱码原因 :
1、 首先确认mysqldump –help或查看备份文件头部信息确认默认字符集
在上例中通过mysqldump –help查看
通过备份文件查看
2、 通过 mysql –help 确认导入字符集
导入的两种情况如下
1）不修改备份文件，导入由于sql文件中存在set names utf8，则导入后字符集使用的依然是utf8,在系统默认显示字符集为gbk的情况下，则显示为乱码，此时则需要使用set names utf8 才能正常显示字符
2）修改备份文件，导入前将sql中的set names utf8 去除，在mysql默认字符集为gbk的情况下导入后字符集为gbk,此时mysql显示字符集为 gbk，两者一致，则显示正常
Load data导入乱码原因
对于load data导入乱码问题的具体解释在官方文档中有提及，请参考:
这些是无法影响到导入结果的，因此应使用Load 自身的字符集参数来进行数据导入，如下方式:
六、总结
1、 编译安装MySQL的时候指定DEFAULT_CHARSET=[charset] 与写入程序保持一致
2、 my.cnf中字符集设置与写入程序保持一致
3、 导入数据时，将导入程序与数据库的链接配置为与数据库字符集一致
4、 load data需要在语句中设置字符集参数
15、解决MySQL数据延迟跳动的问题
今天分析了另外一个关于数据库延迟跳动的问题，也算是比较典型，这个过程中也有一些分析问题的方法和技巧工参考。
首先在高可用检测中，有一套环境的检测时断时续，经过排查发现是数据库产生了延迟，在登录到从库show slave status查看，会发现Seconds_behind_master的值是不断跳动的，即从0390~39这样的频率不断跳动，让人很搓火。
查看数据库的相关日志发现竟然没有任何可以参考的日志记录，怎么分析这个问题呢，我们先来复现，于是我按照节奏抓取了3次问题出现的日志，即通过show slave status连续监测，抓取show slave status输出的结果保存下来，这样我们就得到了一个问题发生过程中的偏移量变化，而这个变化则是在SQLThread在回放过程中产生的问题。
比如下面的一段输出，我截取的是Slave端的relay log进行分析，相应的字段为Relay_Log_Pos
Slave_IO_State: Waiting for master to send event
Master_Host: xxxx
Master_User: dba_repl
Master_Port: 4306
Connect_Retry: 60
Master_Log_File: mysqlbin.000044
Read_Master_Log_Pos: 386125369
Relay_Log_File: slave-relay-bin.000066
Relay_Log_Pos: 386125580
Relay_Master_Log_File: mysqlbin.000044
所以很快得到了偏移量的变化情况：385983806 ，386062813 ，386125580
接着我使用mysqlbinlog开始分析这些日志过程中的明细，根据如下的命令可以很快得到转储的日志中相关的表有3张。
# grep INSERT relaylog_xxxx.dump |awk '{print $3 " " $4}'|sed 's/INTO//g'|sort|uniq
act_action_exec_info
act_join_desc
dic_subsidy_marketing_querylog_202008
我逐步分析了每张表的数据操作情况，得到的信息还是比较有限，继续做更进一步的分析，比如我们分析一下整个日志中的事务量大小：
# mysqlbinlog slave-relay-bin.000066 | grep "GTID$(printf '\t')last_committed" -B 1 \
> | grep -E '^# at' | awk '{print $3}' \
> | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp);tmp=$1}' \
> | sort -n -r | head -n 100
mysqlbinlog: [Warning] unknown variable 'loose-default-character-set=utf8'
5278
5268
5268
5268
5253
5253
5253
5253
5253
可以看到是5K左右，算是比较大了，而这些额外的信息从哪里获得呢，我在主库开启了general_log，这样就能够得到更细粒度的操作日志了。
进一步分析发现，整个业务使用了显示事务的方式：SET autocommit=0，整个事务中包含了几个大SQL,里面存储了很多操作日志明细，而且在事务操作过程中还基于Mybatis框架调用了多次select count(1) from xxx的操作。
经过和业务沟通也基本明确了以上问题。
以上就是MySQL数据延迟跳动的问题解决的详细内容。
16、报错 ：ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.
退出连接：
QUIT 或者 Ctrl+D
17、Mysql5.7修改用户密码报错 : Unknown Column ‘Password’ In 'Field List’解决办法
本意向修改一个用户的密码，网上搜到的命令为如下
mysql> update user set password=password(“新密码”) where user=
执行后报错 ERROR 1054(42S22) Unknown column ‘password’ in ‘field list’
错误的原因是 5.7版本下的mysql数据库下已经没有password这个字段了，password字段改成了authentication_string
所以请使用一下命令：
>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
Database changed
mysql> select User from user; #此处为查询用户命令
+-----------+
| User |
+-----------+
| ******* |
| mysql.sys |
| root |
+-----------+
3 rows in set (0.00 sec)
mysql> update user set password=password("*******") where user="*******"; #修改密码报错
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('*******') where user='*******'; #修改密码成功
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges; #立即生效
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
n>mysql -u ******* -p #以该用户登录成功.
Enter password: ********
…………………………
mysql>
18、MySQL 双主单写，主库偶尔出现大量延迟的原因
https://mp.weixin.qq.com/s/wNFmUfnmrgMlIDZ-xqNi2Q
作者：高鹏（网名八怪），《深入理解MySQL主从原理32讲》系列的作者。
系列链接：https://www.jianshu.com/nb/43148932
19、MySQL案例：一次诡异的Aborted connection错误排查
简介
前段时间，研究怎么去提升数据库安全，例如禁止执行不带条件的update操作，于是就想到了去启用sql_safe_updates参数，这个参数Mysql默认是不启用的，而且还不能加入到my.cnf配置里。因此就想到了用init_connect参数，将sql_safe_updates=1放到init_connect参数里，这样每个用户会话连接的时候，就会启用sql_safe_updates参数了。
可是用普通连接数据库之后，使用某个库之后，就会报错
mysql> use information_schema;
No connection. Trying to reconnect...
Connection id: 16
Current database: *** NONE ***
ERROR 1184 (08S01): Aborted connection 16 to db: 'unconnected' user: 'jim' host: 'localhost' (init_connect command failed)
报错分析
分析报错之前，先复盘一下操作步骤
1.创建普通用户
mysql> create user 'jim'@'%' identified by 'jim';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| jim | % |
| repl | % |
| root | % |
| tony | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
8 rows in set (0.10 sec)
2.使用root用户登录数据库，并设置init_connect参数
mysql> set global init_connect='sql_safe_updates=1';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'init_connect';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| init_connect | sql_safe_updates=1 |
+---------------+--------------------+
1 row in set (0.00 sec)
3.使用普通用户jim连接测试
root@18374a493e56:~# mysql -ujim -pjim
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.21
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use information_schema
No connection. Trying to reconnect...
Connection id: 19
Current database: *** NONE ***
ERROR 1184 (08S01): Aborted connection 19 to db: 'unconnected' user: 'jim' host: 'localhost' (init_connect command failed)
4.使用root用户连接测试
root@18374a493e56:~# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
从上面的报错信息也能很快的判断出，是由于init_connect设置不合理导致的，可是这里很奇怪的是，普通用户会报错，root用户操作没有报错。弄不清楚为什么，于是就去看官方文档，看看官方文档是怎么描述的。
init_connect参数描述
For users that have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege), the content of init_connect is not executed. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the CONNECTION_ADMIN or SUPER privilege enables them to open a connection and fix the init_connect value.
这段话的大概意思是，当用户具有CONNECTION_ADMIN，SUPER权限用户登录时，是不需要执行init_connect参数的内容的，而不具备这些权限的用户登录时，需要执行init_connect参数的内容，当init_connect参数的内容语句有问题时，就会报错了，这就解释了为什么root用户没有问题，而普通用户发生了问题。
了解报错原因之后，需要修改init_connect的内容了，init_connect里的内容复制出来，如果在mysql command命令行里执行没有问题就可以了。
5.重新设置init_connect参数值
mysql> show variables like 'init_connect';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| init_connect | sql_safe_updates=1 |
+---------------+--------------------+
1 row in set (0.01 sec)
mysql> set session sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global init_connect='set session sql_safe_updates=1';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'init_connect';
+---------------+--------------------------------+
| Variable_name | Value |
+---------------+--------------------------------+
| init_connect | set session sql_safe_updates=1 |
+---------------+--------------------------------+
1 row in set (0.00 sec)
6.使用普通用户jim再次连接测试
root@18374a493e56:~# mysql -ujim -pjim
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | ON |
+------------------+-------+
1 row in set (0.01 sec)
测试结果可以看到，已经可以正常使用Mysql数据库了，而且参数sql_safe_updates也设置正确了。
总结
总之，生产操作无小事，大家在生产上执行任何操作时，一定要在测试环境充分验证之后，了解影响范围之后，方可上线操作，如文中操作，很可能会导致一次线上故障。
20、记一次MySQL Crash处理
在MySQL 5.7的高并发场景下，有可能会触发MySQL5.7的Bug，导致宕机，临时的紧急修复方法可针对实例进行参数的修改。Bug的详细信息，请参考Bug#77588和Bug#100771
Bug的详细信息：
2020-12-10 18:12:42 7f5e54b3e700 InnoDB: Assertion failure in thread 140043124729600 in file ha_innodb.cc line 10730
InnoDB: Failing assertion: index->table->stat_initialized
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to [http://bugs.mysql.com](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.6/en/forcing-innodb-recovery.html](http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html)
InnoDB: about forcing recovery.
18:12:42 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.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
key_buffer_size=33554432
read_buffer_size=131072
max_used_connections=6139
max_threads=7000
thread_count=4112
connection_count=4109
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2810236 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0xce7b1b30
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 = 7f5e54b3de18 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8db795]
/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x664dc4]
/lib64/libpthread.so.0[0x3280a0f710]
/lib64/libc.so.6(gsignal+0x35)[0x3280632625]
/lib64/libc.so.6(abort+0x175)[0x3280633e05]
/usr/sbin/mysqld[0x9893b4]
/usr/sbin/mysqld[0x98baab]
/usr/sbin/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3e)[0x5a8a1e]
/usr/sbin/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x684)[0x76ded4]
/usr/sbin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP18Open_table_context+0x1e3)[0x69cc13]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0xc9f)[0x69f49f]
/usr/sbin/mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x4f)[0x69f5cf]
/usr/sbin/mysqld[0x6df2e1]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1553)[0x6e1c63]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x3c0)[0x6e5790]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x152b)[0x6e6d6b]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xcf)[0x6b32bf]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6b33e7]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0x960b4a]
/lib64/libpthread.so.0[0x3280a079d1]
/lib64/libc.so.6(clone+0x6d)[0x32806e88fd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f5de8f1fac0): is an invalid pointer
Connection ID (thread ID): 246446
Status: NOT_KILLED
解决方法：
set global innodb_stats_auto_recalc=off;
set global innodb_stats_persistent=off;
set global innodb_spin_wait_delay=6;
set global innodb_sync_spin_loops=30;
set global table_definition_cache=3000;
1、innodb_stats_auto_recalc
参数含义：是否自动触发更新统计信息
触发条件：
a. 表修改时，确认变化的数据是否超过10%，超过自动收集统计信息；
b. 表的索引统计信息是持久化存储；
默认值：ON
详细解读：
由于自动统计信息重新计算（发生在后台）是异步，在运行影响超过10％的表的DML操作时（即使innodb_stats_auto_recalc启用后），可能不会立即重新计算统计信息 。在某些情况下，统计重新计算可能会延迟几秒钟(10s)。如果在更改表的重要部分之后立即需要最新统计信息，请运行ANALYZE TABLE以启动统计信息的同步（前台）重新计算。
如果禁用了innodb_stats_auto_recalc，请在对索引列进行实质性更改后，通过为每个适用的表发出ANALYZE TABLE语句来确保统计信息的准确性。
在表上添加索引或者添加删除索引中的列时，将自动计算索引统计信息并将其添加到innodb_index_stats表，不受innodb_stats_auto_recalc的值影响。
2、innodb_stats_persistent
参数含义：是否启用持久化统计信息功能
默认值：ON
详细解读：
持久化统计信息功能通过将统计信息存储到磁盘并使其在服务器重新启动期间保持不变来提高执行计划的稳定性，以便优化器更有可能每次为给定查询做出一致的选择。
3、innodb_spin_wait_delay
参数含义：每次互斥锁自旋的等待的时间
默认值：6
4、innodb_sync_spin_loops
参数含义：获取互斥锁的自旋次数
默认值：30
5、table_definition_cache
参数含义：数据库实例打开表定义文件的数量，应大于实际表数量，最大值为2000
默认值：-1
关于innodb_spin_wait_delay和innodb_sync_spin_loops，涉及到InnoDB内核的一些知识点，这里做一些介绍。
InnoDB 中的mutex 和 rw_lock 在早期的版本都是通过系统提供的cas，tas 语义自己进行实现，并没有使用pthread_mutex_t，pthread_rwlock_t，这样实现的好处在于便于统计，以及为了性能考虑，还有解决早期操作系统的一些限制。
大概的原理就是，在mutex_enter 之后，在spin 的次数超过 innodb_sync_spin_loops=30 每次最多 innodb_spin_wait_delay=6如果还没有拿到Mutex，会主动yield() 这个线程，然后wait 在自己实现的wait array 进行等待。
这里每次spin 时候，等待的时候执行的是ut_delay，在ut_delay 中是执行 “pause” 指定，当innodb_spin_wait_delay = 6 的时候，在当年100MHz Pentium cpu，这个时间最大是1us。
wait array 也是InnoDB 实现的一种cond_wait 的实现，早期的MySQL 需要wait array 是因为操作系统无法提供超过100000 event，因此wait array 在用户态去进行这些event 维护，但是到了MySQL 5.0.30 以后，大部分操作系统已经能够处理100000 event，那么现在之所以还需要 wait array，主要是为了统计。
在wait array 的实现里面其实有一把大wait array mutex，是一个pthread_mutex_t，然后在wait array 里面的每一个wait cell 中，包含了os_event_t，wait 的时候调用了os_event_wait_low()，然后在 os_event_t 里面也包含了一个mutex，因此在一次wait 里面就有可能调用了两次pthread_mutex_t 的wait。
并且在os_event_t 唤醒的机制中是直接通过pthread_cond_boradcast()，当有大量线程等待在一个event 的时候, 会造成很多无谓的唤醒。
但是现有的mutex 实现会导致cpu 利用过高，差不多比使用pthread mutex 高16%，并且上下文切换也会更高。
主要的原因是：
1、因为Mutex 的唤醒在os_event 里面，os_event 实现中，如果需要执行唤醒操作，那么需要执行pthread_cond_boradcast() 操作，需要把所有等待的pthread 都唤醒，而不是只唤醒一个；
2、在wait array 的实现中，需要有一个全局的pthread_mutex_t 保护 sync array；
3、在默认的配置中，innodb_spin_wait_delay=6 是ut_delay 执行1us，innodb_sync_spin_loops=30 会执行30次，那么每次mutex 有可能都需要spin 30us；
不同场景需要的mutex 是不一样的，比如buffer pool 上面的page 的mutex 希望的就是一直spin。有些mutex 其实则是希望立刻就进入等待，只用使用这些mutex 的使用者知道接下来哪一个策略更合适。操作系统提供了futex 可能比InnoDB 自己通过wait array 的实现方式，对于通知机制而言会做的更好。
总结了现有的 mutex 实现存在的问题：
1、只有自己实现的ib_mutex_t, 并没有支持futex 的实现；
2、所有的ib_mutex_t 的行为都是一样的，通过两个变量 innodb_spin_wait_delay(控制在Test 失败以后, 最多会delay 的时间)，innodb_sync_spin_loops(控制spin 的次数)。不可以对某一个单独的ib_mutex_t 设置单独的wait + loop 次数；
3、所有的ib_mutex_t 由两个全局的变量控制，因为mutex 在尝试了innodb_sync_spin_loops 次以后，会等待在一个wait array 里面的一个wait cell 上，所有的wait cell 都会注册到一个叫wait array 的队列中进行等待；
在 InnoDB 8.0 的代码中总共实现了4种mutex 的实现方式，2种的策略：
1、TTASFutexMutex 是spin + futex 的实现，在mutex_enter 之后，会首先spin 然后在futex 进行wait；
2、TTASMutex 全spin 方式实现，在spin 的次数超过 innodb_sync_spin_loops=30 每次最多 innodb_spin_wait_delay=6us 以后，会主动yield() 这个线程，然后通过TAS(test and set 进行判断) 是否可以获得；
3、OSTrackMutex，在系统自带的mutex 上进行封装，增加统计计数等等；
4、TTASEevntMutex，InnoDB 一直使用的自己实现的Mutex；
同时在8.0 的实现中定义了两种策略，GenericPolicy和BlockMutexPolicy。这两种策略主要的区别在于在show engine innodb mutex 的时候不同的统计方式。
BlockMutexPolicy 用于统计所有buffer pool 使用的mutex，因此该Mutex 特别多。如果每一个bp 单独统计，浪费大量的内存空间，因此所有bp mutex 都在一起统计，事实上buffer pool 的rw_lock 也是一样。
GenericPolicy 用于除了buffer pool mutex 以外的其他地方。
目前InnoDB 里面都是使用 TTASEventMutex。只不过buffer pool 的mutex 使用的是 BlockMutexPolicy，而且他的mutex 使用的是 GenericPolicy，不过从目前的代码来看，也只是统计的区别而已。
但是从目前来看，不同场景使用不同的mutex，Buffer pool 使用 TTASMutex 实现，其他mutex 使用 TTASEventMutex，并且新加入的 TTASFutexMutex，也就是spin + futex 的实现方式，其实也不是默认使用的，而且wai array 的实现方式也并没有改动。
21、MySQL客户端连接登入hang住原因分析
一、问题来源
问题来自一位朋友，如下：
mysql客户端无法登陆，查看服务器负载没有发现高负载信息。通过pstack查看线程栈信息，没有发现异常信息。
二、问题诊断和解决
一般来讲出现这种情况，我们会使用pstack看看新建立的线程为在什么函数上卡住了，然后很容易就能找到原因。但是出现这个问题过后，当mysql发起连接后卡住后，使用pstack查看mysqld服务端的进程，发现根本就没有线程与之进行交互，因此mysqld怀疑监听线程是不是出了什么问题，因此对mysql客户端连接进程进行了pstack发现如下：
#0 0x00007f262e7889c0 in __connect_nocancel () from /lib64/libpthread.so.0
#1 0x0000000000435123 in inline_mysql_socket_connect (src_file=0x504490 "../../mysql-8.0.20/vio/viosocket.cc", src_line=1054, len=110, addr=0x7fffb38daa20, mysql_socket=...) at ../../mysql-8.0.20/include/mysql/psi/mysql_socket.h:647
#2 vio_socket_connect (vio=0x110d2c0, addr=addr@entry=0x7fffb38daa20, len=len@entry=110, nonblocking=<optimized out>, timeout=-1, connect_done=connect_done@entry=0x7fffb38da9ef) at ../../mysql-8.0.20/vio/viosocket.cc:1054
#3 0x0000000000422ad6 in csm_begin_connect (ctx=0x7fffb38daad0) at ../../mysql-8.0.20/sql-common/client.cc:250
#4 0x000000000041fe99 in mysql_real_connect (mysql=mysql@entry=0xaf16a0 <mysql>, host=host@entry=0x0, user=user@entry=0x10defc0 "root", passwd=passwd@entry=0x10deff0 "xsh0923", db=db@entry=0x0, port=<optimized out>, unix_socket=0x10e0860 "/data/mysql3306/tmp/mysql.sock", client_flag=66560) at ../../mysql-8.0.20/sql-common/client.cc:5600
#5 0x000000000040ddb6 in sql_real_connect (silent=0, password=0x10deff0 "xsh0923", user=0x10defc0 "root", database=0x0, host=0x0) at ../../mysql-8.0.20/client/mysql.cc:4515
#6 sql_connect (host=0x0, database=0x0, user=0x10defc0 "root", password=0x10deff0 "xsh0923", silent=<optimized out>) at ../../mysql-8.0.20/client/mysql.cc:4670
#7 0x000000000040968b in main () at ../../mysql-8.0.20/client/mysql.cc:1326
#8 0x00007f262ce3e495 in __libc_start_main () from /lib64/libc.so.6
#9 0x000000000040a4e1 in _start () at ../../mysql-8.0.20/include/my_alloc.h:86
实际上mysql卡住connect函数上面如下：
因此mysql客户端和mysqld服务端都还没有建立好连接，netstat查看如下：
unix 2 [ ACC ] STREAM LISTENING 8584712 29676/mysqld /tmp/mysqlx.sock
unix 7 [ ACC ] STREAM LISTENING 8584713 29676/mysqld /data/mysql3306/tmp/mysql.sock
unix 2 [ ] STREAM CONNECTING 0 - /data/mysql3306/tmp/mysql.sock
unix 2 [ ] STREAM CONNECTING 0 - /data/mysql3306/tmp/mysql.sock
unix 2 [ ] STREAM CONNECTING 0 - /data/mysql3306/tmp/mysql.sock
大量connectint状态的连接
然后做了strace mysqld的监听线程的操作，得到的结果如下：
正常的情况下这里应该是poll和accept然后开启新的（或者从缓存线程中拿一个）线程来处理交互信息了。但是这里我们可以清晰的看到出现了SIGSTOP信号。随即查看正mysqld线程的状态如下：
所有的线程都处于T状态下，这个状态正是由于信息SIGSTOP引起的。因此我们发一个SIGCONT信号给mysqld进程就好了如下：
kill -18 29676
三、关于信号
下面是我学习信号的时候一些笔记。
在Linux中信号是一种由内核处理的一种软中断机制，他满足简单、不能携带大量信息、并且要满足一定条件才会发送等特征。信号会经历如下过程：
- 产生–>阻塞信号集–>未决信号集–>信号递达–>信号处理方式
首先信号的产生可以有多种方式比如我们经常用的kill命令就是发起信号的一种手段如下：
[root@mgr4 8277]# kill -l
1) SIGHUP 2) SIGINT 3) SIGQUIT 4) SIGILL 5) SIGTRAP
6) SIGABRT 7) SIGBUS 8) SIGFPE 9) SIGKILL 10) SIGUSR1
11) SIGSEGV 12) SIGUSR2 13) SIGPIPE 14) SIGALRM 15) SIGTERM
16) SIGSTKFLT 17) SIGCHLD 18) SIGCONT 19) SIGSTOP 20) SIGTSTP
21) SIGTTIN 22) SIGTTOU 23) SIGURG 24) SIGXCPU 25) SIGXFSZ
26) SIGVTALRM 27) SIGPROF 28) SIGWINCH 29) SIGIO 30) SIGPWR
31) SIGSYS 34) SIGRTMIN 35) SIGRTMIN+1 36) SIGRTMIN+2 37) SIGRTMIN+3
38) SIGRTMIN+4 39) SIGRTMIN+5 40) SIGRTMIN+6 41) SIGRTMIN+7 42) SIGRTMIN+8
43) SIGRTMIN+9 44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13
48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12
53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9 56) SIGRTMAX-8 57) SIGRTMAX-7
58) SIGRTMAX-6 59) SIGRTMAX-5 60) SIGRTMAX-4 61) SIGRTMAX-3 62) SIGRTMAX-2
63) SIGRTMAX-1 64) SIGRTMAX
我们经常的按键也可以产生
- Ctrl+c 2)SIGINT
- Ctrl+\ 3)SIGQUIT
- Ctrl+z 4)SIGTSTP
当然还有很多其他触发方式比如硬件异常，raise函数，abort函数，alarm函数等等。其次是阻塞信号集，阻塞信号集能够对想除(9/19号信号以外)的信号进行屏蔽，如果屏蔽后信号自然不会到达 递达 状态，也就谈不上处理了。通过sigprocmask函数进行阻塞信号集的设置，但之前必须要设置sigset_t 集合，通过sigaddset、sigdelset、sigemptyset、sigfillset等函数设置。
未决信号集是不能被操作的，只能被获取通过sigpending函数获取，但是他和阻塞信号集一起可以控制信号的递达。
信号递达后就需要处理信号，默认的行为上面都列举了，但是信号(9/19号信号以外)是可以被捕获改变其处理方式的，我们可以自定义函数作为某个信号的处理方式，这可以通过signal函数和sigaction函数进行捕获和处理，sigaction函数相对复杂需要有一个struct sigaction的结构体变量，其中包含了sa_handler\sa_mask\sa_flags\sa_siaction 成员，这里不做解释可以自行查看Linux man page。
上面是单进程下的信号处理方式，在多线程下，线程之间公用处理方式，但是可以有不同的信号屏蔽集，在多线程下一般采用设置统一的信号屏蔽字和信号处理方式使用pthread_mask函数继承到各个线程，同时使用sigwait/sigwaitinfo等函数设置一个单独的信号处理线程来进行统一处理，MySQL就是这样处理的。MySQL实际上有一个信号处理线程，
| 36 | 1927 | sql/signal_handler | NULL | BACKGROUND | NULL | NULL |
如果需要了解可以参考如下文章： MySQL中对信号的处理(SIGTERM,SIGQUIT,SIGHUP等), http://blog.itpub.net/7728585/viewspace-2142060/
四、pstack和SIGSTOP
这里简单提一下pstack抓取线程栈的时候会触发一个SIGSTOP信号，因为pstack实际上调用的就是gdb的如下命令：
/usr/bin/gdb --quiet -nx /proc/8277/exe 8277
thread apply all bt
输出所有线程信息
gdb通常会发起SIGSTOP信号来停止进程的运行，因此我们在线上执行pstack命令的时候一定要小心，pstack可能导致你的MySQL停止运行一小会，特别是高压力线程很多负载很高的情况下，可能需要很长的时候，pstack应该作为线上重要数据库最后不得已而为之的诊断方式。
如下我们使用pstack 来获取mysqld的栈，运行一开始就关闭窗口，此时我们的mysqld已经处于停止状态如下：
因此使用pstack一定要格外小心。一般来讲从库线程不多压力不大重要性不高可以使用pstack进行信息采集。当然也可以手动发起SIGSTOP信号来达到测试效果。
五、捕获所有的信号
下面一个小代码可以捕获所有的信号供测试：
#include <signal.h>
#include <stdio.h>
#include <stdlib.h>
#include "unistd.h"
void handler(int sig)
{
printf("get signal %d\n",sig);
}
void test()
{
;
}
int main(void)
{
struct sigaction s;
int* a=NULL;
int i=0;
a=(int*)calloc(32,sizeof(int));
for(i=0;i<32;i++)
{
*(a+i) = (i+1);
}
s.sa_handler=handler;
for(i=0;i<32;i++)
{
sigaction(*(a+i),&s,NULL);
}
while(1)
{
test();
sleep(1);
}
return 0;
}
全文完。
22、
23、
24、
25、
26、
参考链接
记一次MySQL Crash处理 ：https://www.jianshu.com/p/d4fd2f4a4499
MySQL字符集介绍及乱码解决方法 : https://mp.weixin.qq.com/s/Lh6DOSoUo3EhUNWymUMHAw
误删除MySQL数据库表的ibd文件怎么办 ：https://mp.weixin.qq.com/s/qcPR3MXCf9dMg5DItxd23g
https://mp.weixin.qq.com/s/0j_Z46vpXtLriFrSDy0DZA
实战：一文带你解决Mysql主从复制日常错误: https://mp.weixin.qq.com/s/gZRkQzt7Ok_VnxFzVy_JUQ
MGR修改max_binlog_cache_size参数导致异常https://www.jianshu.com/nb/43148932
https://mp.weixin.qq.com/s/uuxtd225GnIVGzOI7S1BIQ
MySQL数据库页损坏怎么办，innodb_force_recovery参数帮你解决问题 : https://mp.weixin.qq.com/s/l6KZP4glnwwuDQxU98A0Mw
mysql登录报错“Access denied for user ‘root’@‘localhost’ (using password: YES”的处理方法 ：https://www.jianshu.com/p/d8970df7d5c0
MGR修改max_binlog_cache_size参数导致异常 ：https://mp.weixin.qq.com/s/uuxtd225GnIVGzOI7S1BIQ
几个常见而严重的 MySQL 问题分析 ：https://mp.weixin.qq.com/s/PS2YymCR4KYdvlcOnHEM-w
教你如何解决MySQL数据延迟跳动的问题 ： https://www.jianshu.com/p/9178fb3acd8b
Mysql5.7修改用户密码报错 : Unknown Column ‘Password’ In 'Field List’解决办法 ：https://mp.weixin.qq.com/s/MqkZ8UQSGIKER6mAjkJaBA
MySQL 双主单写，主库偶尔出现大量延迟的原因 ：https://mp.weixin.qq.com/s/wNFmUfnmrgMlIDZ-xqNi2Q
MySQL案例：一次诡异的Aborted connection错误排查 : https://mp.weixin.qq.com/s/LAcSlcWPzGipZv6dgldOxw
MySQL客户端连接登入hang住原因分析 ：https://mp.weixin.qq.com/s/qHcglskcvRzp2taQdNxFdQ
扫一扫
1392
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
