MySQL 管理分析工具

mysql_analytics.txt

慢查询优化：

mysql> show variables like "%long%";   //查看一下默认为慢查询的时间10秒  
mysql> show variables like "%slow%";   //查看慢查询是否enable
mysql> set slow_query_log='ON';        //加上global，不然会报错的。  
mysql> set global slow_query_log='ON'; //启用慢查询  

[my.cnf]
long_query_time = 2  
log-slow-queries = /usr/local/mysql/mysql-slow.log 

工具: mysqldumpslow 

ref:https://zhuanlan.zhihu.com/p/25648377
ref:http://blog.51yip.com/mysql/972.html

备份和恢复工具binlog：
mysql> show variables like 'log_%'; 
    +----------------------------------------+---------------------------------------+
    | Variable_name                          | Value                                 |
    +----------------------------------------+---------------------------------------+
    | log_bin                                | ON                                    | ------> ON表示已经开启binlog日志
    | log_bin_basename                       | /usr/local/mysql/data/mysql-bin       |
    | log_bin_index                          | /usr/local/mysql/data/mysql-bin.index |
    | log_bin_trust_function_creators        | OFF                                   |
    | log_bin_use_v1_row_events              | OFF                                   |
    | log_error                              | /usr/local/mysql/data/martin.err      |
    | log_output                             | FILE                                  |
    | log_queries_not_using_indexes          | OFF                                   |
    | log_slave_updates                      | OFF                                   |
    | log_slow_admin_statements              | OFF                                   |
    | log_slow_slave_statements              | OFF                                   |
    | log_throttle_queries_not_using_indexes | 0                                     |
    | log_warnings                           | 1                                     |
    +----------------------------------------+---------------------------------------+
常用binlog日志操作命令
    1.查看所有binlog日志列表
      mysql> show master logs;

    2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
      mysql> show master status;

    3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件
      mysql> flush logs;
      注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

    4.重置(清空)所有binlog日志
      mysql> reset master;

      A.查询第一个(最早)的binlog日志：
        mysql> show binlog events\G; 
    
      B.指定查询 mysql-bin.000021 这个文件：
        mysql> show binlog events in 'mysql-bin.000021'\G;

      C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
        mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

      D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

      E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;
        
        
3.此刻杯具了，别慌！先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；
    
      备份一下最后一个binlog日志文件：
      # ll /usr/local/mysql/data | grep mysql-bin
      # cp -v /usr/local/mysql/data/mysql-bin.000023 /root/
      mysql> flush logs;
      mysql> show master status;
        # /usr/local/mysql/bin/mysqlbinlog  /usr/local/mysql/data/mysql-bin.000023

      方式二：登录服务器，并查看(推荐)：
        mysql> show binlog events in 'mysql-bin.000023';
        以下为末尾片段：
        +------------------+------+------------+-----------+-------------+------------------------------------------------------------+
        | Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                                                       |
        +------------------+------+------------+-----------+-------------+------------------------------------------------------------+
        | mysql-bin.000023 |  922 | Xid        |         1 |         953 | COMMIT /* xid=3820 */                                      |
        | mysql-bin.000023 |  953 | Query      |         1 |        1038 | BEGIN                                                      |
        | mysql-bin.000023 | 1038 | Query      |         1 |        1164 | use `zyyshop`; update zyyshop.tt set name='李四' where id=4|
        | mysql-bin.000023 | 1164 | Xid        |         1 |        1195 | COMMIT /* xid=3822 */                                      |
        | mysql-bin.000023 | 1195 | Query      |         1 |        1280 | BEGIN                                                      |
        | mysql-bin.000023 | 1280 | Query      |         1 |        1406 | use `zyyshop`; update zyyshop.tt set name='小二' where id=2|
        | mysql-bin.000023 | 1406 | Xid        |         1 |        1437 | COMMIT /* xid=3823 */                                      |
        | mysql-bin.000023 | 1437 | Query      |         1 |        1538 | drop database zyyshop                                      |
        +------------------+------+------------+-----------+-------------+------------------------------------------------------------+
    5.现在把凌晨备份的数据恢复：
      
      # /usr/local/mysql/bin/mysql -uroot -p123456 -v < /root/BAK.zyyshop.sql;

      注: 至此截至当日凌晨(4:00)前的备份数据都恢复了。
          但今天一整天(4:00--18:00)的数据肿么办呢？就得从前文提到的 mysql-bin.000023 新日志做文章了......

    6.从binlog日志恢复数据
      
      恢复语法格式： 必须恢复指定内容。
      # mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名        

ref:https://www.cnblogs.com/martinzhang/p/3454358.html

查看数据库和表大小:

SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名';
SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='table_name' ; 查表里的记录条数

查数据库中各个表的更新时间
select TABLE_NAME,UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='数据库名';

ref:http://like-eagle.iteye.com/blog/1125165

show status where variable_name = 'Threads_connected';
show processlist;

看mysql线程数，和线程具体状态。

pager grep -v Sleep; ### 这个可以设置mysql命令输出后的管道处理命令。 默认是标准输出，这个改成了 grep -v Sleep.

在阿里云数据库上pager命令不起作用。
#select version() 查看mysql版本 ;