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的语法：
https://dev.mysql.com/doc/refman/8.0/en/set-variable.html
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%
http://www.cnblogs.com/yaohong/archive/2017/10/16/7679270.html