MySQL常用命令汇总（偏向运维管理）

基础部分

1. select @@version; ##查询当前mysql的版本.

2. show variables like 'port';##查看mysql实例的端口。

3. show variables like 'socket';##查看实例的socket数据。

4. show variables like 'datadir';##查看实例的数据路径。

5. show databases;   ##显示所有数据库名的命令 。

6. desc tablename;   ## 显示表结构和列结构的命令。

7. show processlist \g;##显示正在执行的线程。

8. explain ##查看语句的执行计划。

9. show index from table_name ##查看表的索引情况。

10. select * from statistics where table_name='xxx'\g ##查看表的统计信息。

11. select @@max_allowed_packet; ## 查询定义的packet大小。

12. show master status;##查看master状态。

      show slave status ;##查看slave状态。

13. show master logs;##查看所有的log文件，在主服务器上执行。

14. purge binary logs to 'mysql-bin3306.000003';  #mysql-bin3306.000003之前的日志被purge。

15. show warnings; ##显示最近的警告详情。

16. show variables \g; ##查看当前mysqld的所有参数，包括默认值。

17. show grants for 'username'@'hostip' \g; ##查看某一个用户的权限,请替换参数username 和 hostip。

18.  show create table tablename \g; ##查看某表的创建脚本

实例参数部分

19. show variables like 'log_slave%' \g; ##指定条件的参数设置查询，例如查询以log_slave开头的参数设置。

20. show variables like 'slow_query_log';##查看是否开启了慢查询日志；on代表开启。可以在线打开。set global slow_query_log = 1;

21. show variables like 'slow_query_log_file';## 查看慢查询日志的路径。

22. show variables like 'long_query_time'; ##查看慢查询定义的阈值，单位是秒。记录的查询是大于该值，不包括该值。

23. show variables like 'log_output'; ##查看日志的输出格式（file或table）。

24. show variables like 'log_timestamps';##查看日志的时间信息，utc时间或者system时间。

25. show variables like 'log_slow_slave_statements';##查看从服务器是否开启慢查询日志，on代表开启。

26. show variables like 'log_queries_not_using_indexes';##将没有使用索引的sql语句记录到慢查询日志中。

27. show variables like 'log_throttle_queries_not_using_indexes';##集合上面的参数一起使用，限制每分钟内，在慢查询日志中，记录没有使用
索引的次数。避免日志快速增长。

28. show variables like "default%tmp%";查看创建的临时表的存储引擎类型。

29. show variables like 'innodb_log_file_size';##查询log文件大小。

30. show variables like 'innodb_page_size'; ##查询页的大小。一旦数据库通过innodb_page_size设置完成，则后续无法更改。innodb_page_size
是针对普通表的，压缩表不受限制。

31. show variables like 'innodb_buffer_pool_size';##查看缓冲池的大小，每次读写数据都是通过buffer pool；当buffer pool中没有所需的数据
时，才去硬盘中获取。该值设置的越大越好。buffer pool 也是以页（page）为单位的，且大小和innodb_page_size一致。

32. show variables like 'innodb_buffer_pool_instances'; ##设置多少个缓冲池。设置多个instance可将热点打散，提高并发性能（建议设置成cpu
个数值）

33. show engine innodb status \g;##查看buffer pool的状态。

34. set global innodb_buffer_pool_size=2*1024*1024*1024;##在线调整innodb_buffer_pool_size。mysql 5.7之前的版本，修改该值，需要重启。

35. show variables like 'innodb_buffer_pool_dump_at_shutdown'; ##在mysql 5.6 以后，可以在停机的时候dump出buffer pool的数据，然后在
启动的时候load进buffer pool。该功能可以在mysql启动时自动预热，无需人工干预。

36. show variables like 'innodb_buffer_pool_dump_pct';##dumpd 百分比，是每个buffer pool文件，而不是整体。

37. show variables like 'innodb_buffer_pool_load_at_startup';## 启动时加载dump的文件，恢复到buffer pool中。dump的越多，启动的越慢。

38. select * from innodb_lock_waits;##查看锁的信息，在数据库sys下执行。

39. show variables like 'transaction_isolation'; ##查看隔离级别

40. set transaction_isolation='read-committed'; ##设置隔离级别。

41.show variables like 'innodb_print_all_deadlocks';##设置为on，表示将死锁信息打印到err_log中。

42.show variables like "%innodb_flush_log_at_timeout%";##master thread 每秒刷新redo的buffer到logfile。5.7版本可以设置刷新间隔时间，
默认是1秒。

43. show variables like 'binlog_format';##查看binlog的类型。statement 记录sql语句；row 记录sql语句操作的那些行（行的变化）；mixed 混
合statement 和 row 格式（不推荐）。

mha 常用命令 

44. masterha_check_ssh --conf=/etc/masterha/app1.conf    ##检查mha集群ssh配置。

45. masterha_check_repl --conf=/etc/masterha/app1.conf   ##检查整个集群的复制状况。

46. masterha_check_status --conf=/etc/masterha/app1.conf ##检查mha manager的状态：如果正常，会显示"ping_ok"，否则会显示"not_running" ，这代表mha监控没有开启。

47. nohup masterha_manager --conf=/etc/masterha/app1.conf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 & ###监控进程通过nohup管理，可以通过jobs查看后台进程。

48. show slave hosts;##在master节点上执行，查看slave节点数据。

50. change master to master_host='172.xxx.xxx.xxx',master_user='replname',master_password='pwd',master_log_file='mysql-bin.000001',master_log_pos=154; ##change master 示例

51. 若在slave机器上对数据库进行修改或者删除，会导致主从的不一致，需对slave机器设置为read_only = 1 ，让slave提供只读操作。

   注意： read_only 仅仅对没有super权限的用户有效（即 mysql.user表的super_priv字段为y），一般给app 的权限是不需要super权限的。参数super_read_only 可以将有super权限的用户也设置为只读，且该参数设置为on 后， read_only 也跟着自动设置为on。

52. show variables like "server_uuid";## 查看uuid。 gtid（g lobal t ransaction id entifier） 全局事物id。gtid = server_uuid +
transaction_id 其中 server_uuid 是全局唯一的，transaction_id 是自增的。

53. show variables like "%gtid%";##查看gtid相关数据及配置

54.从服务器跳过一个错误的事务
    步骤1: 关闭复制
    stop slave;
    步骤2 ： 设置 gtid_next 为回放失败的gtid
    set session gtid_next='4e659069-3cd8-11e5-9a49-001c4270714e:1'; #在session里设置gtid_next，即跳过这个gtid
    步骤3 : 执行一个空的事物，让回放失败的gtid对应到这个空的事物
    begin;
    commit;
    步骤4 : 还原gtid_next为automatic
    set session gtid_next = automatic; #把gtid_next设置回来
    步骤5: 开启复制
    start slave;

55. 通过gtid的复制都是没有指定master_log_file和master_log_pos的，所以通过gtid复制都是从最先开始的事务开始，除非在自己的binlog里面有执行过之前的记录，才会继续后面的执行。slave如何跳过purge的部分，而不是在最先开始的事务执行。
     步骤1：在主上执行，查看被purge的gtid
     show global variables like 'gtid_purged';

     以下步骤在从上执行，跳过这个gtid：
     步骤2
    stop slave;
    步骤3
    reset master;
    步骤4
    start slave;

其他部分

56. show binlog events in 'mysql-bin.000008'; ##查看指定binlog中的内容。

57. flush binary logs;#刷新日志，并且会产生一个新的日志文件。

58. show variables like "binlog_rows_query_log_events";##设置为on，可以在row格式下，看到sql的信息。

60. show variables like "binlog_cache_size";##binlog默认写入到binlog_cache中，系统默认是32k，当有一个大的事务时（几百兆），内存中显然
放不下那么多binlog，所以会记录到磁盘上。

61. show global status like 'binlog_cache_disk_use';##记录了使用临时文件写二进制日志的次数。注意：写日志本来就停满的，如果cache写不下，
再写入磁盘，然后再写binlog，就是写入2次磁盘，会更慢。如果参数binlog_cache_disk_use次数很多，就要看一下binlog_cache_size设置是否太小，
或者事务本身是否太大。

62.xtrabackup 只能备份innodb存储引擎表（用的较少）；innobackupex可以备份其他存储引擎（含innodb）。innobackupex在xtrabackup的基础上做
了包装，可以兼容各种存储引擎。

63 .mysqldump重要参数 --all-databases ：备份所有的数据库;--databases db1 [db2 db3] ：备份指定的数据库;--single-transaction ： 在一个
事物中导出，确保产生一致性的备份，当前只对innodb支持;--master-data ： 备份的时候dump出change master 信息（file 和 pos），可供主从复制
的时候使用， 默认值为1,当值设置为2 的时候，也会dump出信息，但是会被注释掉 。

64. show variables like '%slave_parallel_workers%';##从数据库用来还原的并发线程数。