MySQL问题处理记录
mysql> select command, count(*) from information_schema.processlist group by command; +---------+----------+ | command | count(*) | +---------+----------+ | Daemon | 1 | | Query | 1 | | Sleep | 30 | +---------+----------+
1.测试服务器直接kill -9关闭数据库后，数据库无法启动，日志报错： Could not open unix socket lock file /tmp/mysql.sock.lock
去/tmp目录下查看有mysql.sock.lock文件，但是没有权限打开。
-rw-------. 1 root root 6 Mar 18 22:29 mysql.sock.lock
解决： chown -R mysql. mysql.sock.lock
启动正常。
2.记一次主从不同步，重新构建主从，持续报错主键重复，
原因不详，有尝试注入一个空事物，来解决，但是持续报错，然后再找了一台服务器构建主从，发现没有问题了，怀疑这台服务器有问题，便重启了服务器，再次构建主从问题解决
一般处理这样的问题，先看错误日志，如果检查不出来配置问题，那就再搭一个新的重库。
主键矛盾的问题，解决办法：1.删掉重复的。
2.跳过重复的，执行后面的。
基于gtid跳过的方式：
1）. stop slave;
2).set gtid_next='Retrieved_Gtid_Set'
Retrieved_Gtid_Set值从show slave status\G获取
3). begin;
4).commit;
5).set gtid_next='automatic'
6).start slave;
3.mysqldump备份
/usr/local/mysql/bin/mysqldump -uroot -p'1qaz@WSX' -h$ip --single-transaction --triggers --set-gtid-purged=ON --routines --events --master-data=2 -A |gzip > $path/redmine_master/all_${ttime}.sql.gz
1. --single-transaction：
可以支持innodb存储引擎热备功能，对innodb可以不锁表进行热备，对于非innodb热备进行锁表
2.--triggers：备份触发器
3.--routines ：备份存储过程和自定义函数
4.--events：导出事件
5.--master-data：该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。
官网介绍：
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
6.--set-gtid-purged=ON 备份恢复到其他库，从库恢复，记录GTID，不记录binlog，构建主从架构必须用on，使用此参数会自动purge 指定的GTID不执行，如下：1-12356的范围的GTID不执行
此参数的执行不会记录binlog，因此适合导入从库
--set-gtid-purged=OFF备份恢复到本地数据库，主库恢复，导入的记录会记录binlog，记录GTID
5.基于gtid构建的主从，从库重新构建主从关系的方法：
1.reset master;
2.reset slave;
3.change master to .............
6.https://i.cnblogs.com/posts/edit;postId=12522697
innodb_max_dirty_pages_pct 通过设置该值为0可以让内存的数据尽快刷新到磁盘，通过，查看show engine innodb status 的modified db pages值很小了说明，就已经刷新完了
7.给普通用户授权可以创建存储函数
root登录后，执行以下命令：
GRANT SUPER ON *.* TO 'public'@'%';
flush privileges;
8.主从不同步
显示：1032报错
Last_SQL_Error: Could not execute Delete_rows event on table mirror.mirror; Can't find record in 'mirror', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 116977
原因：主库再执行delete语句发现从库已经没有这行记录了，但是我真的不知道为何我的架构会出这样的问题
解决：
set global slave_exec_mode='IDEMPOTENT'; ##忽略delete和key矛盾的记录继续往下走，默认为STRICT模式
9.查看语句执行各个环节消耗的情况
set profiling = 1
update t set name where id = 1
SELECT STATE,
SUM(DURATION) as total_R,
ROUND(100 * SUM(DURATION) /
(SELECT SUM(DURATION)
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 1),
2) as pct_r,
count(*) as calls,
sum(DURATION) / count(*) as "R/Call"
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 1
GROUP BY STATE
ORDER BY Total_R DESC;
+--------------------------------+----------+----------+-------+--------------+
| STATE | total_R | pct_r | calls | R/Call |
+--------------------------------+----------+----------+-------+--------------+
| query end | 1.003655 | 35641.16 | 2 | 0.5018275000 |
| updating | 0.001037 | 36.83 | 1 | 0.0010370000 |
| starting | 0.000100 | 3.55 | 1 | 0.0001000000 |
| freeing items | 0.000084 | 2.98 | 1 | 0.0000840000 |
| Opening tables | 0.000078 | 2.77 | 1 | 0.0000780000 |
| System lock | 0.000068 | 2.41 | 1 | 0.0000680000 |
| init | 0.000042 | 1.49 | 1 | 0.0000420000 |
| Waiting for semi-sync ACK from | 0.000042 | 1.49 | 1 | 0.0000420000 |
| end | 0.000041 | 1.46 | 1 | 0.0000410000 |
| cleaning up | 0.000035 | 1.24 | 1 | 0.0000350000 |
| closing tables | 0.000027 | 0.96 | 1 | 0.0000270000 |
| checking permissions | 0.000020 | 0.71 | 1 | 0.0000200000 |
+--------------------------------+----------+----------+-------+--------------+
10.在刪除user前，先确认用户是否使用的查寻命令，
SELECT SUBSTRING_INDEX(host,'1',1) as ip,count(*) ,user,HOST from information_schema.processlist list group by ip,user;
11. 查看mysql內部字符集
通过数据字典查询
gselect * from information_schema.character_sets where description like '%Unicode%' and character_set_name like 'utf8%';
show character set where description llike 'utf8%';%' and charset；
查看默认字符集，default为'yes'
show collation where charset like '%utf8mb4%';
查看正在使用的字符集：
show variables like 'char%';
12.processlist统计
1）按照command类型统计客户端数量
mysql> select command, count(*) from information_schema.processlist group by command; +---------+----------+ | command | count(*) | +---------+----------+ | Daemon | 1 | | Query | 1 | | Sleep | 30 | +---------+----------+
2）连接IP排序
mysql> select ip, count(*) as num from ( select SUBSTR(host, 1, INSTR(host, ':') - 1) as ip, db , command, info from information_schema.processlist ) as a group by ip
-> order by num desc;
+-------------+-----+
| ip | num |
+-------------+-----+
| 10.2.100.70 | 9 |
| 10.2.86.64 | 8 |
| 10.2.83.122 | 7 |
| 10.2.100.60 | 7 |
| | 2 |
+-------------+-----+
5 rows in set (0.00 sec)
3） 查询Sleep连接
mysql> select ip, command, count(*) as num from ( select SUBSTR(host, 1, INSTR(host, ':') - 1) as ip, db , command, info from information_schema.processlist ) as a where command = 'Sleep'group by ip, command order by num desc; +-------------+---------+-----+ | ip | command | num | +-------------+---------+-----+ | 10.2.100.70 | Sleep | 9 | | 10.2.86.64 | Sleep | 8 | | 10.2.100.60 | Sleep | 7 | | 10.2.83.122 | Sleep | 7 | +-------------+---------+-----+
4）kill连接,找出超过5分钟的线程
mysql> select concat("kill ",id,";") from information_schema.PROCESSLIST where host not in ('localhost','127.0.0.1') and
Command != 'Sleep' and Time > 300 order by Time desc;
+------------------------+
| concat("kill ",id,";") |
+------------------------+
| kill 249045; |
| kill 249044; |
| kill 249019; |
| kill 246865; |
| kill 246866; |
| kill 246867; |
| kill 246872; |
| kill 246873; |
| kill 246871; |
| kill 246870; |
| kill 246868; |
| kill 248916; |
| kill 248931; |
| kill 248974; |
| kill 248918; |
| kill 248977; |
| kill 248924; |
+------------------------+
5）杀掉指定用户的的连接
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt'; //输出到指定文件，并
source /tmp/a.txt
+------------------------+ | concat('KILL ',id,';') | +------------------------+ | KILL 249041; | | KILL 249043; | | KILL 249045; | | KILL 249046; | | KILL 249040; | | KILL 249042; | | KILL 249044; | +------------------------+
