MySQL常用指令

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

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

2.查看数据库：

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

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

4.查看系统环境变量：

或者：

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

或者

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