MySQL命令、参数总结

一、MySQL常用命令

1、show status

查看当前会话的数据库状态信息

[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'show status;;'
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 0           |
| Aborted_connects                         | 0           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 2           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 137         |
| Bytes_sent                               | 183         |
...

2、show global status

查看整个数据库运行的状态信息，进行分析、监控

[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'show global status;'
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 0           |
| Aborted_connects                         | 0           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 2           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 6378        |
| Bytes_sent                               | 17508       |
| Com_admin_commands                       | 0           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
...

3、show processlist

查看正在执行的SQL（不完整）语句

[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'show processlist;;'
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 11 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

4、show full processlist

查看正在执行的完整的SQL语句

[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'show full processlist;;'
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 10 | root | localhost | NULL | Query   |    0 | NULL  | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+

5、show variables

MySQL当前会话的参数信息

[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'show variables;'

　　+---------------------------------------------------+--
　　| Variable_name | Value
　　+---------------------------------------------------+--

|　auto_increment_increment | 1
| auto_increment_offset | 1

...

6、show glabal variables

全局参数信息

[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'show global variables;'

7、修改全局变量

客户端中修改
配置文件中修改

上面客户端和配置文件同时修改后，重启服务后依旧生效。

-- 客户端中修改
mysql> set global key_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.01 sec)

-- 配置文件/etc/my.cnf修改
[mysqld]
port            = 3306
...
key_buffer_size = 32M
...

然后重启MySQL服务，查看该变量的大小。

mysql> show global variables like 'key_buffer%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set (0.00 sec)

二、MySQL常用参数

1、-e

通过此参数可实现非交互式对话：

[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crm_db             |
| crm_db_1           |
| mysql              |
| performance_schema |
+--------------------+