ITPub博客

首页 > 数据库 > MySQL > MySQL之奇奇怪怪的小问题(不定期更新)

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

原创 MySQL 作者:wangwenan6 时间:2015-11-24 11:29:53 0 删除 编辑
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. while true
  3. do
  4.     msg="[$(date)]"
  5.     echo ${msg} >> /root/script/log/shell/gdb.log
  6.     DELAY=`mysql -e "show slave status\G" | grep "Seconds" | awk '{print $2}' | head -n 1`
  7.     if [ $DELAY -gt 10 ]; then
  8.         MYSQLD_PID=`ps -ef | grep "mysqld " | grep -v grep | awk '{print $2}'`
  9.         gdb -ex "set pagination 0" -ex "thread apply all bt" -batch -p $MYSQLD_PID >> /root/script/log/shell/gdb.log
  10.     fi
  11.     sleep 30
  12. 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-02-28

  • 博文量
    102
  • 访问量
    1351262