*MySQL备份与恢复(7)mysql进程状态*(重要)
一、查看mysql状态
在生产环境中,这里会有许多进程 show processlist;
[root@localhost bak]# mysql -uroot -pdubin -e "show processlist;" +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 86 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ [root@localhost bak]# mysql -uroot -pdubin -e "show full processlist;" +----+------+-----------+------+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+-----------------------+ | 87 | root | localhost | NULL | Query | 0 | NULL | show full processlist | +----+------+-----------+------+---------+------+-------+-----------------------+ [root@localhost bak]# mysql -uroot -pdubin -e "show full processlist;"|egrep -v "Sleep" Id User Host db Command Time State Info 88 root localhost NULL Query 0 NULL show full processlist [root@localhost bak]#
mysql sleep 过多的问题
查看mysql的所有参数配置,一般在 /etc/my.cnf 有的,这里都有 show variables;
[root@localhost bak]# mysql -uroot -pdubin -e "show variables;"|grep log_bin log_bin ON log_bin_trust_function_creators OFF sql_log_bin ON [root@localhost bak]# grep log-bin /etc/my.cnf log-bin=mysqlbin_oldboy
查看mysql的状态,也就是计数器 show global status;(你每天做的事情,这里边都有)
[root@localhost bak]# mysql -uroot -pdubin -e "show global status;"|grep sel Com_insert_select 0 Com_replace_select 0 Com_select 490 [root@localhost bak]# mysql -uroot -pdubin -e "select * from oldboy.student;" [root@localhost bak]# mysql -uroot -pdubin -e "show global status;"|grep sel Com_insert_select 0 Com_replace_select 0 Com_select 493
mysql> show global status like '%inser%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Com_insert | 32 | | Com_insert_select | 0 | | Delayed_insert_threads | 0 | | Innodb_rows_inserted | 193 | | Qcache_inserts | 0 | +------------------------+-------+ 5 rows in set (0.00 sec) mysql> insert into test1 values(2); ERROR 1046 (3D000): No database selected mysql> use oldboy Database changed mysql> insert into test1 values(3); Query OK, 1 row affected (0.11 sec) mysql> insert into test1 values(2); Query OK, 1 row affected (0.00 sec) mysql> show global status like '%inser%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Com_insert | 34 | | Com_insert_select | 0 | | Delayed_insert_threads | 0 | | Innodb_rows_inserted | 195 | | Qcache_inserts | 0 | +------------------------+-------+ 5 rows in set (0.00 sec)