mysql idle_MySQL常用指令

1.查看MySQL版本等基本信息：

root@server1:~# /usr/bin/mysqladmin version

2.查看数据库：

root@server1:~# /usr/bin/mysqlshow

3.查看某个数据库，获取tables：

root@server1:~# /usr/bin/mysqlshow mysql

Database: mysql

+---------------------------+

| Tables |

+---------------------------+

| columns_priv |

| db |

| event |

| func |

| general_log |

4.查看系统环境变量：

mysql> show variables like 'datadir';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| datadir | /var/lib/mysql/ |

+---------------+-----------------+

或者：

mysql> SELECT @@sql_mode;

+------------------------+

| @@sql_mode |

+------------------------+

| NO_ENGINE_SUBSTITUTION |

+------------------------+

或者：

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

5.修改系统环境变量参数：

SET的语法：

SET variable = expr [, variable = expr] ...

variable: {

user_var_name

| param_name

| local_var_name

| {GLOBAL | @@GLOBAL.} system_var_name

| {PERSIST | @@PERSIST.} system_var_name

| {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name

| [SESSION | @@SESSION. | @@] system_var_name

}

例如：

SET PERSIST max_connections = 1000;

SET @@PERSIST.max_connections = 1000;

+-------------------------------------+

mysql> SET GLOBAL max_connections=8192;

Query OK, 0 rows affected (0.01 sec)

6.查看用户的授权：

例如：mysql> show grants for root@127.0.0.1;

+-------------------------------------------------------------------------------------------------------------+

| Grants for root@127.0.0.1 |

+--------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*4B366F1B8A4D4328F21B5EEABD65395CCB2997B4' WITH GRANT OPTION |

+-------------------------------------------------------------------------------------------------------------+

例如：mysql> show grants for 'debian-sys-maint'@'localhost';

例如：mysql> SHOW GRANTS FOR 'nova';

+-----------------------------------------------------------------------------------------------------+

| Grants for nova@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'nova'@'%' IDENTIFIED BY PASSWORD '*8EF67695CB6B5CC9269ED99A48DCBDEE16B86EF2' |

| GRANT ALL PRIVILEGES ON `nova`.* TO 'nova'@'%' |

+-----------------------------------------------------------------------------------------------------+

7.查看MySQL的运行的process：

root@server1:~# /usr/bin/mysqladmin processlist

或者

mysql> SHOW PROCESSLIST;

+--------+-------------+-----------------------+----------+---------+--------+----------------+----------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+-------------+-----------------------+----------+---------+--------+--------------+------------------+

| 1 | system user | | NULL | Sleep | 628684 | wsrep aborter idle | NULL |

| 2 | system user | | NULL | Sleep | 626580 | committed 22409221 | NULL |

| 3 | system user | | NULL | Sleep | 626579 | committed 22409235 | NULL |

.....................

| 1500 | zabbix | fd00::c0a8:341d:42388 | zabbix | Sleep | 17 | | NULL |

8.查看当前用户：

mysql> SELECT CURRENT_USER();

9.MySQL抓包：

root@server1:~# tcpdump -l -i eth1 -w - src or dst port 3306 | strings

10.SELECT指令攻击：

SELECT * FROM table WHERE ID=234 OR 1=1；将搜索出所有的记录。

通过Setting the SQL Mode的方法来解决：

SET GLOBAL sql_mode = 'modes';

SET SESSION sql_mode = 'modes';

或者innodb_strict_mode=‘ON'。

11.连接数据库：

shell> mysql --user=finley --password db_name

shell> mysql -u finley -p db_name

或者带上password:(不推荐Specifying a password on the command line should be considered insecure)

shell>mysql --user=finley --password=password db_name

shell>mysql -u finley -ppassword db_name

12.查看使用的socket：

root@server1:~# netstat -ln | grep mysql

unix 2 [ ACC ] STREAM LISTENING 70412 /var/run/mysqld/mysqld.sock

13.修改用户密码：

CLI>mysqladmin -u user_name -h host_name password "password" (不安全)

或者：

mysql>ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

14.查看当前整体状态：

mysql> status

或者mysql> \s

--------------

mysql Ver 14.14 Distrib 5.6.37, for debian-linux-gnu (x86_64) using EditLine wrapper

Connection id: 452202

Current database:

Current user: root@ad00::b0a6:342e

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.6.37-0~u14.04+mos0 (Ubuntu), wsrep_25.19

Protocol version: 10

Connection: 192.168.64.36 via TCP/IP

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

TCP port: 3306

Uptime: 8 days 22 hours 54 min 33 sec

Threads: 464 Questions: 350036576 Slow queries: 0 Opens: 590 Flush tables: 1 Open tables: 583 Queries per second avg: 452.434

--------------

15.查看数据库运行状态：

mysql> show global status;

mysql> show global status like 'Max_used_connections';

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| Max_used_connections | 524   |

+----------------------+-------+

1 row in set (0.00 sec)

16.数据库备份：

shell>mysqldump --all-databases > dump.sql

shell>mysqldump --databases db1 db2 db3 > dump.sql

17. 最大连接数：

mysql 的最大连接数：max_connections

服务器响应的最大连接数：Max_used_connections

mysql 服务器最大连接数值的设置范围比较理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在 10% 以上，如果在 10% 以下，说明 mysql 服务器最大连接上限值设置过高。

Max_used_connections / max_connections * 100% > 10%