*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)