MySQL之奇奇怪怪的小问题(不定期更新)

1.[ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: 1872
问题背景:
从MySQL-5.6.26使用xtrabackup备份，并将数据恢复至MySQL-5.7.9，使用MySQL_upgrade以后，开启同步时报错
问题原因：
备份是由从库生成的，备份数据中，master.info文件或者是mysql.master_info里面保存了以前5.6.26的信息，新库upgrade以后格式出现了问题
问题解决：
reset slave，重新设定同步的参数

2.ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
问题背景:
从MySQL-5.6.26使用xtrabackup备份，并将数据恢复至MySQL-5.7.9，使用MySQL_upgrade以后，开启同步时报错
问题原因：
备份是由从库生成的，备份数据中，包含了auto.cnf，里面记录了其他服务器的UUID
问题解决：
删除auto.cnf，重启数据库

3.各种奇奇怪怪的lock
问题背景：
无
问题原因：
锁争用
问题解决：
参考锁争用的逻辑，详细分析具体场景
相关资料：
多出来的I为意向锁

Gap，Insert Intention Lock， Record， Next-Key


4.flush_method=O_DRIECT与Double WriteBuffer
参考官方文档的说法：如果刷新策略使用O_DRIECT，且存储使用了RAID， RAID卡也有备用电池来保护write-buffer， 那么可以关掉Double WriteBuffer，节省IO；

5.有时候会遇到一些不规范的命名或者字符， 这时候用`来包裹这些命名， 有时候会有效果~

6.insert into on duplicate key update的语句, 在发现duplicate key的时候, binlog会以update语句的形式记录下来, 并且只修改一行的时候, 会显示2row affect;

7. --sysdate-is-now, 这个参数会控制sysdate()方法的行为, 参数默认为false, 那么sysdate()会返回语句结束时的时间; 如果设置为true, 那么这个方法就会变成的那个now()的别名, 返回执行开始时的时间;


8.  div_precision_increment参数能控制小数点后显示的数字个数;

9. 在诊断MySQL5.7的内存占用异常或者OOM的时候, 可以在ps中开启完整的内存监控, 参考语句 :

点击(此处)折叠或打开

1. update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';

10. 排查等待事件的SQL, 在performance_schema下执行;

点击(此处)折叠或打开

1. SELECT COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, EVENT_NAME 
2. FROM events_waits_summary_global_by_event_name 
3. where COUNT_STAR > 0 and EVENT_NAME like 'wait/synch/%' order by SUM_TIMER_WAIT desc limit 10

11. 偶尔会遇到mysql客户端不能输入中文的情况, 这时候在系统层修改一下LANG就好, utf8, zh-CN, zh_CN.GB18030都可以试试;

12. mysql可以通过修改init_connect参数来定义用户登录时默认执行的动作, 如果里面的语句有语法问题或者执行失败, 那么连接会马上中断 ;

13. mysql重置performance_schema表的统计信息;

点击(此处)折叠或打开

1. CALL sys.ps_truncate_all_tables(FALSE)

 14.做字符集转换的时候, 可以考虑用mydumper, 直接用binary的形式保存的数据, 也可以用mysql自带的convert函数手动转;

点击(此处)折叠或打开

1. UPDATE table SET title = CONVERT(CONVERT(CONVERT(title USING latin1) USING binary) USING UTF8)

15. 有时候相对日志中的某些数据进行分组统计, 可以用shell直接完成; awk和grep -v能过滤掉一些非格式化的数据/异常等;

点击(此处)折叠或打开

1. cat log* | awk '{print $6}' | grep -v ip | sort | uniq -c

16. 手动指定alter的算法

点击(此处)折叠或打开

1. alter table case_sen ALGORITHM=INPLACE, modify name varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;

17. 更换数据库表名
直接用awk来拼接str, 把需要的SQL语句输出到std, 可以用重定std来保存这些输出

点击(此处)折叠或打开

1. mysql -sNe 'select table_name from information_schema.tables where table_schema = "test"' | awk '{print"alter table test."$1" rename test_new."$1";"}'

18. 查询外键关系, 写脚本用

点击(此处)折叠或打开

1. SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE a WHERE a.REFERENCED_TABLE_NAME is not null and a.CONSTRAINT_SCHEMA = 'test';

19. 排除部分库做dump

点击(此处)折叠或打开

1. mysql -Ne "show databases;" | grep -Ev "information_schema|mysql|performance_schema|sys" | xargs mysqldump -t --skip-add-drop-table -c --skip-extended-insert --set-gtid-purged=OFF --skip-lock-tables --databases > dump.sql

20. 排除部分库授权

点击(此处)折叠或打开

1. SELECT CONCAT("GRANT UPDATE ON YourDB.", table_name, " TO user@localhost;")
   
2. FROM information_schema.TABLES
   
3. WHERE table_schema = "YourDB" AND table_name <> "table_to_skip"

21. gdb捕捉thread信息的脚本, 重点在于gdb那一行

点击(此处)折叠或打开

1. #!/bin/bash
   
2. 
   
3. while true
   
4. do
   
5.     msg="[$(date)]"
   
6.     echo ${msg} >> /root/script/log/shell/gdb.log
   
7.     DELAY=`mysql -e "show slave status\G" | grep "Seconds" | awk '{print $2}' | head -n 1`
   
8.     if [ $DELAY -gt 10 ]; then
   
9.         MYSQLD_PID=`ps -ef | grep "mysqld " | grep -v grep | awk '{print $2}'`
   
10.         gdb -ex "set pagination 0" -ex "thread apply all bt" -batch -p $MYSQLD_PID >> /root/script/log/shell/gdb.log
    
11.     fi
    
12.     sleep 30
    
13. done

22. 可以通过ALTER TABLE TEST TABLESPACE=innodb_file_per_table把innodb表从系统表空间里面挪出来, 无需关注innodb_file_per_table是否开启;


23. 有时候使用gdb调试的时候, 会提示pthread的版本不匹配, 无法输出线程的堆栈信息, 这时候从其他没问题的debian系统中把相关的文件拷贝到目标服务器就可以了;
     原因一般是目标进程使用的pthread与系统自己的pthread不匹配, 发生于gdb调试MySQL时, debian;

点击(此处)折叠或打开

1. cp /lib/x86_64-linux-gnu/libpthread-2.19.so /home/backup

24. 批量提取mysql的业务用户及权限

点击(此处)折叠或打开

1. mysql -Ne "select concat('show create user \'',user,'\'@\'',host,'\';') from mysql.user where user not in ('server','mysqld','mysql.sys','root');" | mysql -N | sed 's/$/&;/g'
2. mysql -Ne "select concat('show grants for \'',user,'\'@\'',host,'\';') from mysql.user where user not in ('server','mysqld','mysql.sys','root');" | mysql -N | sed 's/$/&;/g'
   

25. innodb status里面的Dictionary memory allocated

这个值的大小和table_definition_cache有一定的关系, 具体计算公式应当为: 表实例大小 x  table_definition_cache, 和表的数据大小无关, 与表结构的复杂度相关, 如列的数量, 索引数量;

26. 窗口函数 in mysql-8.0

点击(此处)折叠或打开

1. SELECT ROW_NUMBER() OVER (ORDER BY birthdate) AS num,
   
2.        name, birthdate
   
3. FROM people;
   
4.  
   
5. +--------+------------------+------------+
   
6. | num | name            | birthdate  |
   
7. +--------+------------------+------------+
   
8. | 1   | Georges Danton  | 1759-10-26 |
   
9. | 2   | Herbert G Wells | 1866-09-21 |
   
10. | 3   | Jimmy Hendrix   | 1942-11-27 |
    
11. | 4   | Angela Merkel   | 1954-07-17 |
    
12. | 5   | Rigoberta Menchu| 1959-01-09 |
    
13. | 6   | Tracy Chapman   | 1964-03-30 |
    
14. +--------+------------------+------------+

27. 开启MySQL内存监控选项(持久化)

点击(此处)折叠或打开

1. performance_schema_instrument = 'memory%=counted'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29510932/viewspace-1845228/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29510932/viewspace-1845228/