MySQL production environment common commands

Number of connections to MySQL

1. View the current number of MySQL connections

[[email protected] ~]# mysqladmin-Uroot-p123456 statusuptime:34070Threads:1Questions:8678Slow Queries:0Opens:910Flush tables: +  OpenTables4Queries per secondavg:0.254
Therads Number of open connections

Mysql> Show status like '%thread% ';
+------------------------------------------+-------+
| variable_name | Value |
+------------------------------------------+-------+
| Delayed_insert_threads | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| threads_cached | 0 |
| threads_connected | 1 |
| threads_created | 4 | #创建的连接数
| threads_running | 1 | #打开的连接数
+------------------------------------------+-------+
8 rows in Set (0.00 sec

2. View the maximum number of connections allowed by MySQL

Mysql>Show variables like '%max_connections%';+-----------------+-------+|Variable_name|Value|+-----------------+-------+|Max_connections| 151   |+-----------------+-------+1Rowinch Set(0.00Sec

3.mysql Connections Too many solutions

Http://www.cnblogs.com/lazyball/p/7424243.html

Talk about MySQL's slow query

The reason for the slow query is generally because the conditions of the select design are more, such as sub-query, such as the database has been inserted in the card where the other statements can not be run to cause blocking. Development is not omnipotent, but also to write some of the SQL statement hanging Days, a glance to see the end.

1. See what processes are running in MySQL

Mysql>Show processlist; #默认一般显示100行 plus full display of multiple+----+------+-----------+------+---------+------+-------+------------------+|Id| User |Host|Db|Command|Time|State|Info|+----+------+-----------+------+---------+------+-------+------------------+|  4 |Root|localhost| NULL |Sleep|  882 |       | NULL             ||  6 |Root|localhost| NULL |Query|    0 | NULL  |Show Processlist|+----+------+-----------+------+---------+------+-------+------------------+2Rowsinch Set(0.00sec) MySQL>Show Fullprocesslist;+----+------+-----------+------+---------+------+-------+-----------------------+|Id| User |Host|Db|Command|Time|State|Info|+----+------+-----------+------+---------+------+-------+-----------------------+|  4 |Root|localhost| NULL |Sleep|  894 |       | NULL                  ||  6 |Root|localhost| NULL |Query|    0 | NULL  |Show FullProcesslist|+----+------+-----------+------+---------+------+-------+-----------------------+2Rowsinch Set(0.00Sec

[Email protected] ~]# mysqladmin-uroot-p processlist
Enter Password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 4 | Root |    localhost | | Sleep |       1238 |                  | |
| 7 | Root |    localhost | | Query |       0 | | Show Processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Parameter explanation:
ID #ID标识 useful when you want to kill a statement
Use #当前连接用户
Host #显示这个连接从哪个ip的哪个端口上发出
DB #数据库名
Command #连接状态, usually sleep (sleep), query, connection (connect)
Time #连接持续时间, unit is seconds
State #显示当前sql语句的状态
Info #显示这个sql语句

# #慢查询发生的时候, state is generally watting for table level lock watch time to see if it runs long, emergency kill, do not urgently find development, write what thing (I know how to optimize
Words, looking for development to show off)

2. Emergency batch kill MySQL slow query script

Let's just say what you wrote.

#!/Bin/Bash forIdinch' Mysqladmin-Uxxx-H192.168.1.1 -P'xxxx'Processlist|grep Query|Grep'Paigoushop' |Grep'Sending Data'  |Awk'{print $}'' Do mysqladmin-Uxxx-H192.168.1.1 -P'XXX'  Kill${id} Done
#grep有点多 to pinpoint the SQL you want to kill

This is online, I have not tried to put it here in case I use it later

#!/Bin/Bash forIinch' MySQL-Bse'show full processlist;' |Grep-ISelect |Grep-I "Waiting for Table  LevelLock|Awk'{print $}'' do MySQL-Bse "Kill$i " done

3. Turn on slow query logging

Always feel that the above method is not very good, more passive, for some people do not understand, but also scold operation, I come to learn: "No, what is it, Xiao Yang, you This database is too slow, how to make, hurriedly to me check." ”

Say it is tears, then don't say. Speak with the facts, take out the SQL statements, and let the development take a good look at what is written.

MySQL production environment common commands