一、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 | +--------------------+