MySQL-常用命令
0.导入大文件
source filename
1.查看数据库状态
mysqladmin -h [host_name|IP] -u root -pXXX ping
>>mysqladmin -h localhost -uroot ping >>mysqld is alive
2.查看数据库当前连接等信息
直接SHELL:
mysqladmin -h127.0.0.1 -uroot -pXXX processlist
或者进入MySQL的命令行模式:
show processlist
或:
SELECT user, host, time, command, time FROM [mysql|information_schema].processlist WHERE user = 'root' and state IS NOT NULL;
如下:
mysql> show processlist; +-----+------+-----------+------+--------+-----+------+-----------------+ | Id | User | Host | db | Command| Time| State| Info | +-----+------+-----------+------+--------+-----+------+-----------------+ | 348 | root | localhost | NULL | Query | 0| NULL | show processlist| | 349 | root | localhost | NULL | Sleep | 2| | NULL | +-----+------+-----------+------+--------+-----+------+-----------------+
3.查看有多少由于客户没有正确关闭连接而死掉的连接数
SHOW GLOBAL STATUS LIKE 'aborted_clients'
4.查看当前失败连接数
SHOW GLOBAL STATUS LIKE 'aborted_connects'
5.查看最大连接数
SHOW CLOBAL VARIABLES LIKE 'max_connections' SHOW GLOBAL STATUS LIKE 'max_connections'
6.查看InnoDB死锁相关状况
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS;
SHOW OPEN TABLES;
SHOW STATUS LIKE '%lock%';
7.查看从服务器是否能跟上主服务器步伐等信息
SHOW SLAVE STATUS
8.查询系统特定资源的信息
SHOW STATUS
SHOW TABLE STATUS LIKE 'user' \G;
9.查询系统变量的名称和值
SHOW VARIABLES
# 查询 data 存储目录
SHOW VARIABLES LIKE 'datadir'
10.查询服务器所支持的权限
SHOW PRIVILEGES
11.查询服务器所支持的引擎
SHOW ENGINES
12.显示最后一个语句执行后的错误、警告和通知信息
SHOW WANNINGS
13.显示最后一个语句执行后的错误信息
SHOW ERRORS
14.显示一个用户的权限
SHOW GRANTS FOR suiyongjie@localhost
15.显示一个表的索引
SHOW INDEX FROM tname
16.设置隔离级别
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
17.复制表
目标表不存在
--以下两个方法都试只会拷贝数据，不会拷贝索引等 CREATE TABLE 目标表 SELECT * FROM 源表 LIMIT 0; 或者 SELECT * INTO 目标表 FROM 源表;
创建目标表并插入数据
CREATE TABLE 目标表 LIKE 源表; INSERT INTO 目标表(字段列表) SELECT 字段列表 FROM 源表;
18.查看空闲连接超时时间
SHOW VARIABLES LIKE '%timeout%'; #其中 interactive_timeout 表示交互连接超时时间（比如使用 MySQL 客户端连接），wait_timeout 表示非交互连接超时时间（比如 PHP ）
19.查询SQL语句执行成本
# MySQL 使用执行成本来优选执行计划
SELECT ...
SHOW STATUS LIKE 'Last_query_cost';
20.QPS、TPS、命中率、复制延时等计算
计算可以先执行一次得到 num1，隔N秒之行一次得到 num2，计算时使用 (num2 - num1) / N (1)QPS(每秒Query量) QPS = Questions(or Queries) / seconds show global status like 'Question%'; (2)TPS(每秒事务量) TPS = (Com_commit + Com_rollback) / seconds show global status like 'Com_commit'; show global status like 'Com_rollback'; (3)key Buffer 命中率 mysql>show global status like 'key%'; key_buffer_read_hits = (1-key_reads / key_read_requests) * 100% key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% (4)InnoDB Buffer命中率 show status like 'innodb_buffer_pool_read%'; innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100% (5)Query Cache命中率 show status like 'Qcache%'; Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%; (6)Table Cache状态量 show global status like 'open%'; 比较 open_tables 与 opend_tables 值 (7)Thread Cache 命中率 show global status like 'Thread%'; show global status like 'Connections'; Thread_cache_hits = (1 - Threads_created / connections ) * 100% (8)锁定状态 show global status like '%lock%'; Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重 Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的 (9)复制延时量 show slave status 查看延时时间 (10) Tmp Table 状况(临时表状况) show status like 'Create_tmp%'; Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大， 可能是排序句子过多或者是连接句子不够优化 (11) Binlog Cache 使用状况 show status like 'Binlog_cache%'; 如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小 (12) Innodb_log_waits 量 show status like 'innodb_log_waits'; Innodb_log_waits值不等于0的话，表明 innodb log buffer 因为空间不足而等待
21.查询变量配置
# 查询优化器各种开关，比如 mrr、index_merge 等
SELECT @@optimizer_switch
22.附加说明
show status 结果说明
| 列 | 含义 |
Name |
表名 |
Type |
表的类型 (ISAM，MyISAM或HEAP) |
Row_format |
行存储格式 (固定, 动态, 或压缩） |
Rows |
行数量 |
Avg_row_length |
平均行长度 |
Data_length |
数据文件的长度 |
Max_data_length |
数据文件的最大长度 |
Index_length |
索引文件的长度 |
Data_free |
已分配但未使用了字节数 |
Auto_increment |
下一个 autoincrement(自动加1）值 |
Create_time |
表被创造的时间 |
Update_time |
数据文件最后更新的时间 |
Check_time |
最后对表运行一个检查的时间 |
Create_options |
与CREATE TABLE一起使用的额外选项 |
Comment |
当创造表时，使用的注释 (或为什么MySQL不能存取表信息的一些信息)。 |
show index 结果说明：
| 列 | 含义 |
Table |
表名 |
Non_unique |
0，如果索引不能包含重复。 |
Key_name |
索引名 |
Seq_in_index |
索引中的列顺序号, 从 1 开始。 |
Column_name |
列名。 |
Collation |
列怎样在索引中被排序。在MySQL中，这可以有值A（升序) 或NULL（不排序)。 |
Cardinality |
索引中唯一值的数量。这可通过运行isamchk -a更改. |
Sub_part |
如果列只是部分被索引，索引字符的数量。NULL，如果整个键被索引。 |
show variables 结果说明：
Aborted_clients |
由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 |
Aborted_connects |
尝试已经失败的MySQL服务器的连接的次数。 |
Connections |
试图连接MySQL服务器的次数。 |
Created_tmp_tables |
当执行语句时，已经被创造了的隐含临时表的数量。 |
Delayed_insert_threads |
正在使用的延迟插入处理器线程的数量。 |
Delayed_writes |
用INSERT DELAYED写入的行数。 |
Delayed_errors |
用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 |
Flush_commands |
执行FLUSH命令的次数。 |
Handler_delete |
请求从一张表中删除行的次数。 |
Handler_read_first |
请求读入表中第一行的次数。 |
Handler_read_key |
请求数字基于键读行。 |
Handler_read_next |
请求读入基于一个键的一行的次数。 |
Handler_read_rnd |
请求读入基于一个固定位置的一行的次数。 |
Handler_update |
请求更新表中一行的次数。 |
Handler_write |
请求向表中插入一行的次数。 |
Key_blocks_used |
用于关键字缓存的块的数量。 |
Key_read_requests |
请求从缓存读入一个键值的次数。 |
Key_reads |
从磁盘物理读入一个键值的次数。 |
Key_write_requests |
请求将一个关键字块写入缓存次数。 |
Key_writes |
将一个键值块物理写入磁盘的次数。 |
Max_used_connections |
同时使用的连接的最大数目。 |
Not_flushed_key_blocks |
在键缓存中已经改变但是还没被清空到磁盘上的键块。 |
Not_flushed_delayed_rows |
在INSERT DELAY队列中等待写入的行的数量。 |
Open_tables |
打开表的数量。 |
Open_files |
打开文件的数量。 |
Open_streams |
打开流的数量(主要用于日志记载） |
Opened_tables |
已经打开的表的数量。 |
Questions |
发往服务器的查询的数量。 |
Slow_queries |
要花超过long_query_time时间的查询数量。 |
Threads_connected |
当前打开的连接的数量。 |
Threads_running |
不在睡眠的线程数量。 |
Uptime |
服务器工作了多少秒。 |
关于上面的一些注释：
- 如果
Opened_tables太大，那么你的table_cache变量可能太小。 - 如果
key_reads太大，那么你的key_cache可能太小。缓存命中率可以用key_reads/key_read_requests计算。 - 如果
Handler_read_rnd太大，那么你很可能有大量的查询需要MySQL扫描整个表或你有没正确使用键值的联结(join)。
