MySQL常用操作(2)mysql常用命令示例

mysql常用命令

查询库 show databases;

切换库 use mysql; 这条命令可以不加；号

查看库里的表 show tables;

查看表里的字段 desc tb_name;

查看建表语句 show create table tb_name\G;

查看当前用户 select user();

查看当前使用的数据库 select databsase(); 这里不是database s

创建库 create database db1;

创建表 use db1; create table t1(`id` int(4), `name` char(40));

查看当前数据库版本 select version();

查看数据库状态 show status;

查看各参数 show variables; show variables like 'max_connect%';

修改参数 set global max_connect_errors=1000;

查看队列 show processlist; show full processlist;

例：

[root@aliyun ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases; 查看所有数据库

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.00 sec)

mysql> use mysql 切换到数据库mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.00 sec)

mysql> show tables; 显示当前数据下的所有表（数据库由表组成，表由字段组成）

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

| Tables_in_mysql |

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

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

28 rows in set (0.01 sec)

mysql> desc user; 查询user表里的所有字段

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

| Field 字段名称 | Type 字段类型 | Null | Key | Default | Extra |

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

| Host | char(60) 字符格式 | NO | PRI | | |

| User | char(16) | NO | PRI | | |

| Password | char(41) | NO | | | |

| Select_priv | enum('N','Y') 字母格式 | NO | | N | |

| Insert_priv | enum('N','Y') | NO | | N | |

| Update_priv | enum('N','Y') | NO | | N | |

| Delete_priv | enum('N','Y') | NO | | N | |

| Create_priv | enum('N','Y') | NO | | N | |

| Drop_priv | enum('N','Y') | NO | | N | |

| Reload_priv | enum('N','Y') | NO | | N | |

| Shutdown_priv | enum('N','Y') | NO | | N | |

| Process_priv | enum('N','Y') | NO | | N | |

| File_priv | enum('N','Y') | NO | | N | |

| Grant_priv | enum('N','Y') | NO | | N | |

| References_priv | enum('N','Y') | NO | | N | |

| Index_priv | enum('N','Y') | NO | | N | |

| Alter_priv | enum('N','Y') | NO | | N | |

| Show_db_priv | enum('N','Y') | NO | | N | |

| Super_priv | enum('N','Y') | NO | | N | |

| Create_tmp_table_priv | enum('N','Y') | NO | | N | |

| Lock_tables_priv | enum('N','Y') | NO | | N | |

| Execute_priv | enum('N','Y') | NO | | N | |

| Repl_slave_priv | enum('N','Y') | NO | | N | |

| Repl_client_priv | enum('N','Y') | NO | | N | |

| Create_view_priv | enum('N','Y') | NO | | N | |

| Show_view_priv | enum('N','Y') | NO | | N | |

| Create_routine_priv | enum('N','Y') | NO | | N | |

| Alter_routine_priv | enum('N','Y') | NO | | N | |

| Create_user_priv | enum('N','Y') | NO | | N | |

| Event_priv | enum('N','Y') | NO | | N | |

| Trigger_priv | enum('N','Y') | NO | | N | |

| Create_tablespace_priv | enum('N','Y') | NO | | N | |

| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |

| ssl_cipher | blob | NO | | NULL | |

| x509_issuer | blob | NO | | NULL | |

| x509_subject | blob | NO | | NULL | |

| max_questions | int(11) unsigned | NO | | 0 | |

| max_updates | int(11) unsigned | NO | | 0 | |

| max_connections | int(11) unsigned | NO | | 0 | |

| max_user_connections | int(11) unsigned | NO | | 0 | |

| plugin | char(64) | YES | | mysql_native_password | |

| authentication_string | text | YES | | NULL | |

| password_expired | enum('N','Y') | NO | | N | |

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

43 rows in set (0.00 sec)

mysql> show create table user\G; 查看user表的 建表语句 并竖排显示

*************************** 1. row ***************************

Table: user

Create Table: CREATE TABLE `user` (

`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',

`ssl_cipher` blob NOT NULL,

`x509_issuer` blob NOT NULL,

`x509_subject` blob NOT NULL,

`max_questions` int(11) unsigned NOT NULL DEFAULT '0',

`max_updates` int(11) unsigned NOT NULL DEFAULT '0',

`max_connections` int(11) unsigned NOT NULL DEFAULT '0',

`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',

`plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',

`authentication_string` text COLLATE utf8_bin,

`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

PRIMARY KEY (`Host`,`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

1 row in set (0.00 sec)

ERROR: 这里报错是因为命令中的 \ 号已经是结束符，再跟 ；号就有这个报错，不加就没有了

No query specified

mysql> select * from user\G; 查看user表的所有字段并竖排显示

*************************** 1. row ***************************

Host: localhost

User: root

Password: *D3E4F159E619834549348C890870D0E867F5E532B

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

*************************** 2. row ***************************

Host: aliyun

User: root

Password: *D3E4F159E619342945675348C890870D0E867F5E532B

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

*************************** 3. row ***************************

Host: 127.0.0.1

User: root

Password: *D3E4F159E612300989348C890870D0E867F5E532B

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

*************************** 4. row ***************************

Host: ::1

User: root

Password: *D3E4F159E619230989348C890870D0E867F5E532B

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

*************************** 5. row ***************************

Host: localhost

User:

Password:

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: NULL

password_expired: N

*************************** 6. row ***************************

Host: aliyun

User:

Password:

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: NULL

password_expired: N

6 rows in set (0.00 sec)

ERROR:

No query specified

mysql> select user(); 查看当前用户

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

| user() |

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

| root@localhost | root用户@本地

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

1 row in set (0.00 sec)

mysql> quit

Bye

[root@aliyun ~]# mysql -uroot -ptest.123 -h12.19.23.13

远程登陆数据库，不指定端口则默认3306

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(); 再次查看当前用户

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

| user() |

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

| root@aliyun | root@远程主机，IP被反解析成了hostname

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

1 row in set (0.00 sec)

mysql> quit

Bye

[root@aliyun ~]# ls -la 查看当前用户家目录

total 88

dr-xr-x---. 6 root root 4096 Jun 20 15:25 .

dr-xr-xr-x. 19 root root 4096 Jun 20 12:33 ..

-rw------- 1 root root 20410 Jun 20 15:26 .bash_history

-rw-r--r--. 1 root root 18 Dec 29 2013 .bash_logout

-rw-r--r--. 1 root root 176 Dec 29 2013 .bash_profile

-rw-r--r--. 1 root root 176 Dec 29 2013 .bashrc

drwx------ 3 root root 4096 Oct 15 2017 .cache

-rw-r--r--. 1 root root 100 Dec 29 2013 .cshrc

-rw------- 1 root root 40 Jun 11 15:32 .lesshst

-rw------- 1 root root 287 Jun 20 15:25 .mysql_history 当前用户在数据库里的命令历史记录文件

drwxr-xr-x 2 root root 4096 Oct 15 2017 .pip

drwxr----- 3 root root 4096 Jun 14 16:24 .pki

-rw-r--r-- 1 root root 64 Oct 15 2017 .pydistutils.cfg

-rw------- 1 root root 1024 Jun 13 03:43 .rnd

drwx------ 2 root root 4096 Jun 17 16:03 .ssh

-rw-r--r--. 1 root root 129 Dec 29 2013 .tcshrc

-rw------- 1 root root 8090 Jun 20 12:32 .viminfo

[root@aliyun ~]# less .mysql_history 查看文件里记录的命令历史

_HiStOrY_V2_

use\040mysql;

use\040mysql

show\040databases

quit

show\040databases\040quit\040quit;

show\040databases;

use\040mysql

show\040databases;

show\040tables;

desc\040user;

show\040create\040table\040user\134G;

select\040*\040from\040user\134G;

select\040user();

(END)

[root@aliyun ~]# mysql -uroot -ptest.123 -h12.19.23.13 -P3306

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql 切换到mysql库

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select database(); 查看当前所在库

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

| database() |

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

| mysql |

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

1 row in set (0.00 sec)

mysql> create database db1; 创建db1库

Query OK, 1 row affected (0.00 sec)

mysql> show databases; 查看所有数据库

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

| Database |

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

| information_schema |

| db1 |

| mysql |

| performance_schema |

| test |

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

5 rows in set (0.01 sec)

mysql> use db1; 切换到db1库

Database changed

mysql> create table t1(`id` int(4), `name` char(40)); 创建t1表，并定义字段，用反引号 ` ` 引起来

Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1\G; 显示t1表的建表语句

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`id` int(4) DEFAULT NULL,

`name` char(40) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 数据库引擎是Innodb，默认字符集是拉丁1

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> #create table t1(`id` int(4), `name` char(40));ENGINE=InnoDB DEFAULT CHARSET=utf8;

在mysql数据库里，命令前面加上#就标识取消执行

mysql> drop table t1; 丢弃t1表

Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建表t1，并指定数据库所用的引擎和字符集，格式为utf8，mysql常用的引擎：innodb，myisam

Query OK, 0 rows affected (0.02 sec)

mysql> show create table t1\G; 再次查看t1表的建表语句

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`id` int(4) DEFAULT NULL,

`name` char(40) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 字符集已经变更

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> select version(); 查看当前数据库版本

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

| version() |

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

| 5.6.35 |

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

1 row in set (0.00 sec)

mysql> show status; 查看数据库状态

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

| Variable_name | Value |

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

| Aborted_clients | 0 |

| Aborted_connects | 3 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Binlog_stmt_cache_disk_use | 0 |

| Binlog_stmt_cache_use | 0 |

| Bytes_received | 2208 |

| Bytes_sent | 49334 |

| Com_admin_commands | 0 |

| Com_assign_to_keycache | 0 |

| Com_alter_db | 0 |

| Com_alter_db_upgrade | 0 |

| Com_alter_event | 0 |

| Com_alter_function | 0 |

| Com_alter_procedure | 0 |

| Com_alter_server | 0 |

| Com_alter_table | 0 |

| Com_alter_tablespace | 0 |

| Com_alter_user | 0 |

| Com_analyze | 0 |

| Com_begin | 0 |

| Com_binlog | 0 |

| Com_call_procedure | 0 |

| Com_change_db | 2 |

| Com_change_master | 0 |

| Com_check | 0 |

| Com_checksum | 0 |

| Com_commit | 0 |

| Com_create_db | 1 |

| Com_create_event | 0 |

| Com_create_function | 0 |

| Com_create_index | 0 |

| Com_create_procedure | 0 |

| Com_create_server | 0 |

| Com_create_table | 9 |

| Com_create_trigger | 0 |

| Com_create_udf | 0 |

| Com_create_user | 0 |

| Com_create_view | 0 |

| Com_dealloc_sql | 0 |

| Com_delete | 0 |

| Com_delete_multi | 0 |

| Com_do | 0 |

| Com_drop_db | 0 |

| Com_drop_event | 0 |

| Com_drop_function | 0 |

| Com_drop_index | 0 |

| Com_drop_procedure | 0 |

| Com_drop_server | 0 |

| Com_drop_table | 5 |

| Com_drop_trigger | 0 |

| Com_drop_user | 0 |

| Com_drop_view | 0 |

| Com_empty_query | 0 |

| Com_execute_sql | 0 |

| Com_flush | 0 |

| Com_get_diagnostics | 0 |

| Com_grant | 0 |

| Com_ha_close | 0 |

| Com_ha_open | 0 |

| Com_ha_read | 0 |

| Com_help | 0 |

| Com_insert | 0 |

| Com_insert_select | 0 |

| Com_install_plugin | 0 |

| Com_kill | 0 |

| Com_load | 0 |

| Com_lock_tables | 0 |

| Com_optimize | 0 |

| Com_preload_keys | 0 |

| Com_prepare_sql | 0 |

| Com_purge | 0 |

| Com_purge_before_date | 0 |

| Com_release_savepoint | 0 |

| Com_rename_table | 0 |

| Com_rename_user | 0 |

| Com_repair | 0 |

| Com_replace | 0 |

| Com_replace_select | 0 |

| Com_reset | 0 |

| Com_resignal | 0 |

| Com_revoke | 0 |

| Com_revoke_all | 0 |

| Com_rollback | 0 |

| Com_rollback_to_savepoint | 0 |

| Com_savepoint | 0 |

| Com_select | 6 |

| Com_set_option | 1 |

| Com_signal | 0 |

| Com_show_binlog_events | 0 |

| Com_show_binlogs | 0 |

| Com_show_charsets | 0 |

| Com_show_collations | 0 |

| Com_show_create_db | 0 |

| Com_show_create_event | 0 |

| Com_show_create_func | 0 |

| Com_show_create_proc | 0 |

| Com_show_create_table | 4 |

| Com_show_create_trigger | 0 |

| Com_show_databases | 3 |

| Com_show_engine_logs | 0 |

| Com_show_engine_mutex | 0 |

| Com_show_engine_status | 0 |

| Com_show_events | 0 |

| Com_show_errors | 0 |

| Com_show_fields | 28 |

| Com_show_function_code | 0 |

| Com_show_function_status | 0 |

| Com_show_grants | 0 |

| Com_show_keys | 0 |

| Com_show_master_status | 0 |

| Com_show_open_tables | 0 |

| Com_show_plugins | 0 |

| Com_show_privileges | 0 |

| Com_show_procedure_code | 0 |

| Com_show_procedure_status | 0 |

| Com_show_processlist | 0 |

| Com_show_profile | 0 |

| Com_show_profiles | 0 |

| Com_show_relaylog_events | 0 |

| Com_show_slave_hosts | 0 |

| Com_show_slave_status | 0 |

| Com_show_status | 2 |

| Com_show_storage_engines | 0 |

| Com_show_table_status | 0 |

| Com_show_tables | 2 |

| Com_show_triggers | 0 |

| Com_show_variables | 4 |

| Com_show_warnings | 0 |

| Com_slave_start | 0 |

| Com_slave_stop | 0 |

| Com_stmt_close | 0 |

| Com_stmt_execute | 0 |

| Com_stmt_fetch | 0 |

| Com_stmt_prepare | 0 |

| Com_stmt_reprepare | 0 |

| Com_stmt_reset | 0 |

| Com_stmt_send_long_data | 0 |

| Com_truncate | 0 |

| Com_uninstall_plugin | 0 |

| Com_unlock_tables | 0 |

| Com_update | 0 |

| Com_update_multi | 0 |

| Com_xa_commit | 0 |

| Com_xa_end | 0 |

| Com_xa_prepare | 0 |

| Com_xa_recover | 0 |

| Com_xa_rollback | 0 |

| Com_xa_start | 0 |

| Compression | OFF |

| Connection_errors_accept | 0 |

| Connection_errors_internal | 0 |

| Connection_errors_max_connections | 0 |

| Connection_errors_peer_address | 0 |

| Connection_errors_select | 0 |

| Connection_errors_tcpwrap | 0 |

| Connections | 7 |

| Created_tmp_disk_tables | 0 |

| Created_tmp_files | 6 |

| Created_tmp_tables | 9 |

| Delayed_errors | 0 |

| Delayed_insert_threads | 0 |

| Delayed_writes | 0 |

| Flush_commands | 1 |

| Handler_commit | 0 |

| Handler_delete | 0 |

| Handler_discover | 0 |

| Handler_external_lock | 0 |

| Handler_mrr_init | 0 |

| Handler_prepare | 0 |

| Handler_read_first | 0 |

| Handler_read_key | 0 |

| Handler_read_last | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 512 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 503 |

| Innodb_buffer_pool_dump_status | not started |

| Innodb_buffer_pool_load_status | not started |

| Innodb_buffer_pool_pages_data | 223 |

| Innodb_buffer_pool_bytes_data | 3653632 |

| Innodb_buffer_pool_pages_dirty | 0 |

| Innodb_buffer_pool_bytes_dirty | 0 |

| Innodb_buffer_pool_pages_flushed | 207 |

| Innodb_buffer_pool_pages_free | 7968 |

| Innodb_buffer_pool_pages_misc | 0 |

| Innodb_buffer_pool_pages_total | 8191 |

| Innodb_buffer_pool_read_ahead_rnd | 0 |

| Innodb_buffer_pool_read_ahead | 0 |

| Innodb_buffer_pool_read_ahead_evicted | 0 |

| Innodb_buffer_pool_read_requests | 1936 |

| Innodb_buffer_pool_reads | 168 |

| Innodb_buffer_pool_wait_free | 0 |

| Innodb_buffer_pool_write_requests | 769 |

| Innodb_data_fsyncs | 122 |

| Innodb_data_pending_fsyncs | 0 |

| Innodb_data_pending_reads | 0 |

| Innodb_data_pending_writes | 0 |

| Innodb_data_read | 2822144 |

| Innodb_data_reads | 183 |

| Innodb_data_writes | 289 |

| Innodb_data_written | 6865408 |

| Innodb_dblwr_pages_written | 207 |

| Innodb_dblwr_writes | 12 |

| Innodb_have_atomic_builtins | ON |

| Innodb_log_waits | 0 |

| Innodb_log_write_requests | 130 |

| Innodb_log_writes | 39 |

| Innodb_os_log_fsyncs | 52 |

| Innodb_os_log_pending_fsyncs | 0 |

| Innodb_os_log_pending_writes | 0 |

| Innodb_os_log_written | 75776 |

| Innodb_page_size | 16384 |

| Innodb_pages_created | 56 |

| Innodb_pages_read | 167 |

| Innodb_pages_written | 207 |

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 0 |

| Innodb_row_lock_time_avg | 0 |

| Innodb_row_lock_time_max | 0 |

| Innodb_row_lock_waits | 0 |

| Innodb_rows_deleted | 0 |

| Innodb_rows_inserted | 0 |

| Innodb_rows_read | 0 |

| Innodb_rows_updated | 0 |

| Innodb_num_open_files | 9 |

| Innodb_truncated_status_writes | 0 |

| Innodb_available_undo_logs | 128 |

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 6698 |

| Key_blocks_used | 0 |

| Key_read_requests | 0 |

| Key_reads | 0 |

| Key_write_requests | 0 |

| Key_writes | 0 |

| Last_query_cost | 0.000000 |

| Last_query_partial_plans | 0 |

| Max_used_connections | 1 |

| Not_flushed_delayed_rows | 0 |

| Open_files | 46 |

| Open_streams | 0 |

| Open_table_definitions | 81 |

| Open_tables | 81 |

| Opened_files | 187 |

| Opened_table_definitions | 10 |

| Opened_tables | 4 |

| Performance_schema_accounts_lost | 0 |

| Performance_schema_cond_classes_lost | 0 |

| Performance_schema_cond_instances_lost | 0 |

| Performance_schema_digest_lost | 0 |

| Performance_schema_file_classes_lost | 0 |

| Performance_schema_file_handles_lost | 0 |

| Performance_schema_file_instances_lost | 0 |

| Performance_schema_hosts_lost | 0 |

| Performance_schema_locker_lost | 0 |

| Performance_schema_mutex_classes_lost | 0 |

| Performance_schema_mutex_instances_lost | 0 |

| Performance_schema_rwlock_classes_lost | 0 |

| Performance_schema_rwlock_instances_lost | 0 |

| Performance_schema_session_connect_attrs_lost | 0 |

| Performance_schema_socket_classes_lost | 0 |

| Performance_schema_socket_instances_lost | 0 |

| Performance_schema_stage_classes_lost | 0 |

| Performance_schema_statement_classes_lost | 0 |

| Performance_schema_table_handles_lost | 0 |

| Performance_schema_table_instances_lost | 0 |

| Performance_schema_thread_classes_lost | 0 |

| Performance_schema_thread_instances_lost | 0 |

| Performance_schema_users_lost | 0 |

| Prepared_stmt_count | 0 |

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 1031352 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 12 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

| Queries | 124 |

| Questions | 78 |

| Select_full_join | 0 |

| Select_full_range_join | 0 |

| Select_range | 0 |

| Select_range_check | 0 |

| Select_scan | 9 |

| Slave_heartbeat_period | 0.000 |

| Slave_last_heartbeat | |

| Slave_open_temp_tables | 0 |

| Slave_received_heartbeats | 0 |

| Slave_retried_transactions | 0 |

| Slave_running | OFF |

| Slow_launch_threads | 0 |

| Slow_queries | 0 |

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 0 |

| Sort_scan | 0 |

| Ssl_accept_renegotiates | 0 |

| Ssl_accepts | 0 |

| Ssl_callback_cache_hits | 0 |

| Ssl_cipher | |

| Ssl_cipher_list | |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_ctx_verify_depth | 0 |

| Ssl_ctx_verify_mode | 0 |

| Ssl_default_timeout | 0 |

| Ssl_finished_accepts | 0 |

| Ssl_finished_connects | 0 |

| Ssl_server_not_after | |

| Ssl_server_not_before | |

| Ssl_session_cache_hits | 0 |

| Ssl_session_cache_misses | 0 |

| Ssl_session_cache_mode | NONE |

| Ssl_session_cache_overflows | 0 |

| Ssl_session_cache_size | 0 |

| Ssl_session_cache_timeouts | 0 |

| Ssl_sessions_reused | 0 |

| Ssl_used_session_cache_entries | 0 |

| Ssl_verify_depth | 0 |

| Ssl_verify_mode | 0 |

| Ssl_version | |

| Table_locks_immediate | 74 |

| Table_locks_waited | 0 |

| Table_open_cache_hits | 30 |

| Table_open_cache_misses | 4 |

| Table_open_cache_overflows | 0 |

| Tc_log_max_pages_used | 0 |

| Tc_log_page_size | 0 |

| Tc_log_page_waits | 0 |

| Threads_cached | 0 |

| Threads_connected | 1 |

| Threads_created | 1 |

| Threads_running | 1 |

| Uptime | 13472 |

| Uptime_since_flush_status | 13472 |

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

341 rows in set (0.00 sec)

mysql> show variables; 查看参数变量

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

| Variable_name | Value |

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

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

| autocommit | ON |

| automatic_sp_privileges | ON |

| avoid_temporal_upgrade | OFF |

| back_log | 80 |

| basedir | /usr/local/mysql/ |

| big_tables | OFF |

| bind_address | * |

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_error_action | IGNORE_ERROR |

| binlog_format | STATEMENT |

| binlog_gtid_simple_recovery | OFF |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_row_image | FULL |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| binlogging_impossible_mode | IGNORE_ERROR |

| block_encryption_mode | aes-128-ecb |

| bulk_insert_buffer_size | 8388608 |

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

| collation_connection | utf8_general_ci |

| collation_database | latin1_swedish_ci |

| collation_server | latin1_swedish_ci |

| completion_type | NO_CHAIN |

| concurrent_insert | AUTO |

| connect_timeout | 10 |

| core_file | OFF |

| datadir | /data/mysql/ |

| date_format | %Y-%m-%d |

| datetime_format | %Y-%m-%d %H:%i:%s |

| default_storage_engine | InnoDB |

| default_tmp_storage_engine | InnoDB |

| default_week_format | 0 |

| delay_key_write | ON |

| delayed_insert_limit | 100 |

| delayed_insert_timeout | 300 |

| delayed_queue_size | 1000 |

| disconnect_on_expired_password | ON |

| div_precision_increment | 4 |

| end_markers_in_json | OFF |

| enforce_gtid_consistency | OFF |

| eq_range_index_dive_limit | 10 |

| error_count | 0 |

| event_scheduler | OFF |

| expire_logs_days | 0 |

| explicit_defaults_for_timestamp | OFF |

| external_user | |

| flush | OFF |

| flush_time | 0 |

| foreign_key_checks | ON |

| ft_boolean_syntax | + -><()~*:""&| |

| ft_max_word_len | 84 |

| ft_min_word_len | 4 |

| ft_query_expansion_limit | 20 |

| ft_stopword_file | (built-in) |

| general_log | OFF |

| general_log_file | /data/mysql/aliyun.log |

| group_concat_max_len | 1024 |

| gtid_executed | |

| gtid_mode | OFF |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | |

| have_compress | YES |

| have_crypt | YES |

| have_dynamic_loading | YES |

| have_geometry | YES |

| have_openssl | DISABLED |

| have_profiling | YES |

| have_query_cache | YES |

| have_rtree_keys | YES |

| have_ssl | DISABLED |

| have_symlink | YES |

| host_cache_size | 279 |

| hostname | aliyun |

| identity | 0 |

| ignore_builtin_innodb | OFF |

| ignore_db_dirs | |

| init_connect | |

| init_file | |

| init_slave | |

| innodb_adaptive_flushing | ON |

| innodb_adaptive_flushing_lwm | 10 |

| innodb_adaptive_hash_index | ON |

| innodb_adaptive_max_sleep_delay | 150000 |

| innodb_additional_mem_pool_size | 8388608 |

| innodb_api_bk_commit_interval | 5 |

| innodb_api_disable_rowlock | OFF |

| innodb_api_enable_binlog | OFF |

| innodb_api_enable_mdl | OFF |

| innodb_api_trx_level | 0 |

| innodb_autoextend_increment | 64 |

| innodb_autoinc_lock_mode | 1 |

| innodb_buffer_pool_dump_at_shutdown | OFF |

| innodb_buffer_pool_dump_now | OFF |

| innodb_buffer_pool_filename | ib_buffer_pool |

| innodb_buffer_pool_instances | 8 |

| innodb_buffer_pool_load_abort | OFF |

| innodb_buffer_pool_load_at_startup | OFF |

| innodb_buffer_pool_load_now | OFF |

| innodb_buffer_pool_size | 134217728 |

| innodb_change_buffer_max_size | 25 |

| innodb_change_buffering | all |

| innodb_checksum_algorithm | innodb |

| innodb_checksums | ON |

| innodb_cmp_per_index_enabled | OFF |

| innodb_commit_concurrency | 0 |

| innodb_compression_failure_threshold_pct | 5 |

| innodb_compression_level | 6 |

| innodb_compression_pad_pct_max | 50 |

| innodb_concurrency_tickets | 5000 |

| innodb_data_file_path | ibdata1:12M:autoextend |

| innodb_data_home_dir | |

| innodb_disable_sort_file_cache | OFF |

| innodb_doublewrite | ON |

| innodb_fast_shutdown | 1 |

| innodb_file_format | Antelope |

| innodb_file_format_check | ON |

| innodb_file_format_max | Antelope |

| innodb_file_per_table | ON |

| innodb_flush_log_at_timeout | 1 |

| innodb_flush_log_at_trx_commit | 1 |

| innodb_flush_method | |

| innodb_flush_neighbors | 1 |

| innodb_flushing_avg_loops | 30 |

| innodb_force_load_corrupted | OFF |

| innodb_force_recovery | 0 |

| innodb_ft_aux_table | |

| innodb_ft_cache_size | 8000000 |

| innodb_ft_enable_diag_print | OFF |

| innodb_ft_enable_stopword | ON |

| innodb_ft_max_token_size | 84 |

| innodb_ft_min_token_size | 3 |

| innodb_ft_num_word_optimize | 2000 |

| innodb_ft_result_cache_limit | 2000000000 |

| innodb_ft_server_stopword_table | |

| innodb_ft_sort_pll_degree | 2 |

| innodb_ft_total_cache_size | 640000000 |

| innodb_ft_user_stopword_table | |

| innodb_io_capacity | 200 |

| innodb_io_capacity_max | 2000 |

| innodb_large_prefix | OFF |

| innodb_lock_wait_timeout | 50 |

| innodb_locks_unsafe_for_binlog | OFF |

| innodb_log_buffer_size | 8388608 |

| innodb_log_compressed_pages | ON |

| innodb_log_file_size | 50331648 |

| innodb_log_files_in_group | 2 |

| innodb_log_group_home_dir | ./ |

| innodb_lru_scan_depth | 1024 |

| innodb_max_dirty_pages_pct | 75 |

| innodb_max_dirty_pages_pct_lwm | 0 |

| innodb_max_purge_lag | 0 |

| innodb_max_purge_lag_delay | 0 |

| innodb_mirrored_log_groups | 1 |

| innodb_monitor_disable | |

| innodb_monitor_enable | |

| innodb_monitor_reset | |

| innodb_monitor_reset_all | |

| innodb_numa_interleave | OFF |

| innodb_old_blocks_pct | 37 |

| innodb_old_blocks_time | 1000 |

| innodb_online_alter_log_max_size | 134217728 |

| innodb_open_files | 2000 |

| innodb_optimize_fulltext_only | OFF |

| innodb_page_size | 16384 |

| innodb_print_all_deadlocks | OFF |

| innodb_purge_batch_size | 300 |

| innodb_purge_threads | 1 |

| innodb_random_read_ahead | OFF |

| innodb_read_ahead_threshold | 56 |

| innodb_read_io_threads | 4 |

| innodb_read_only | OFF |

| innodb_replication_delay | 0 |

| innodb_rollback_on_timeout | OFF |

| innodb_rollback_segments | 128 |

| innodb_sort_buffer_size | 1048576 |

| innodb_spin_wait_delay | 6 |

| innodb_stats_auto_recalc | ON |

| innodb_stats_include_delete_marked | OFF |

| innodb_stats_method | nulls_equal |

| innodb_stats_on_metadata | OFF |

| innodb_stats_persistent | ON |

| innodb_stats_persistent_sample_pages | 20 |

| innodb_stats_sample_pages | 8 |

| innodb_stats_transient_sample_pages | 8 |

| innodb_status_output | OFF |

| innodb_status_output_locks | OFF |

| innodb_strict_mode | OFF |

| innodb_support_xa | ON |

| innodb_sync_array_size | 1 |

| innodb_sync_spin_loops | 30 |

| innodb_table_locks | ON |

| innodb_thread_concurrency | 0 |

| innodb_thread_sleep_delay | 10000 |

| innodb_tmpdir | |

| innodb_undo_directory | . |

| innodb_undo_logs | 128 |

| innodb_undo_tablespaces | 0 |

| innodb_use_native_aio | ON |

| innodb_use_sys_malloc | ON |

| innodb_version | 5.6.40 |

| innodb_write_io_threads | 4 |

| insert_id | 0 |

| interactive_timeout | 28800 |

| join_buffer_size | 262144 |

| keep_files_on_create | OFF |

| key_buffer_size | 8388608 |

| key_cache_age_threshold | 300 |

| key_cache_block_size | 1024 |

| key_cache_division_limit | 100 |

| large_files_support | ON |

| large_page_size | 0 |

| large_pages | OFF |

| last_insert_id | 0 |

| lc_messages | en_US |

| lc_messages_dir | /usr/local/mysql/share/ |

| lc_time_names | en_US |

| license | GPL |

| local_infile | ON |

| lock_wait_timeout | 31536000 |

| locked_in_memory | OFF |

| log_bin | OFF |

| log_bin_basename | |

| log_bin_index | |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_error | ./aliyun.err |

| log_output | FILE |

| log_queries_not_using_indexes | OFF |

| log_slave_updates | OFF |

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| log_throttle_queries_not_using_indexes | 0 |

| log_warnings | 1 |

| long_query_time | 10.000000 |

| low_priority_updates | OFF |

| lower_case_file_system | OFF |

| lower_case_table_names | 0 |

| master_info_repository | FILE |

| master_verify_checksum | OFF |

| max_allowed_packet | 4194304 |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 1073741824 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| max_connect_errors | 1000 |

| max_connections | 151 |

| max_delayed_threads | 20 |

| max_digest_length | 1024 |

| max_error_count | 64 |

| max_heap_table_size | 16777216 |

| max_insert_delayed_threads | 20 |

| max_join_size | 18446744073709551615 |

| max_length_for_sort_data | 1024 |

| max_prepared_stmt_count | 16382 |

| max_relay_log_size | 0 |

| max_seeks_for_key | 18446744073709551615 |

| max_sort_length | 1024 |

| max_sp_recursion_depth | 0 |

| max_tmp_tables | 32 |

| max_user_connections | 0 |

| max_write_lock_count | 18446744073709551615 |

| metadata_locks_cache_size | 1024 |

| metadata_locks_hash_instances | 8 |

| min_examined_row_limit | 0 |

| multi_range_count | 256 |

| myisam_data_pointer_size | 6 |

| myisam_max_sort_file_size | 9223372036853727232 |

| myisam_mmap_size | 18446744073709551615 |

| myisam_recover_options | OFF |

| myisam_repair_threads | 1 |

| myisam_sort_buffer_size | 8388608 |

| myisam_stats_method | nulls_unequal |

| myisam_use_mmap | OFF |

| net_buffer_length | 16384 |

| net_read_timeout | 30 |

| net_retry_count | 10 |

| net_write_timeout | 60 |

| new | OFF |

| old | OFF |

| old_alter_table | OFF |

| old_passwords | 0 |

| open_files_limit | 5000 |

| optimizer_prune_level | 1 |

| optimizer_search_depth | 62 |

| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |

| optimizer_trace | enabled=off,one_line=off |

| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |

| optimizer_trace_limit | 1 |

| optimizer_trace_max_mem_size | 16384 |

| optimizer_trace_offset | -1 |

| performance_schema | ON |

| performance_schema_accounts_size | 100 |

| performance_schema_digests_size | 10000 |

| performance_schema_events_stages_history_long_size | 10000 |

| performance_schema_events_stages_history_size | 10 |

| performance_schema_events_statements_history_long_size | 10000 |

| performance_schema_events_statements_history_size | 10 |

| performance_schema_events_waits_history_long_size | 10000 |

| performance_schema_events_waits_history_size | 10 |

| performance_schema_hosts_size | 100 |

| performance_schema_max_cond_classes | 80 |

| performance_schema_max_cond_instances | 3504 |

| performance_schema_max_digest_length | 1024 |

| performance_schema_max_file_classes | 50 |

| performance_schema_max_file_handles | 32768 |

| performance_schema_max_file_instances | 7693 |

| performance_schema_max_mutex_classes | 200 |

| performance_schema_max_mutex_instances | 15906 |

| performance_schema_max_rwlock_classes | 40 |

| performance_schema_max_rwlock_instances | 9102 |

| performance_schema_max_socket_classes | 10 |

| performance_schema_max_socket_instances | 322 |

| performance_schema_max_stage_classes | 150 |

| performance_schema_max_statement_classes | 168 |

| performance_schema_max_table_handles | 4000 |

| performance_schema_max_table_instances | 12500 |

| performance_schema_max_thread_classes | 50 |

| performance_schema_max_thread_instances | 402 |

| performance_schema_session_connect_attrs_size | 512 |

| performance_schema_setup_actors_size | 100 |

| performance_schema_setup_objects_size | 100 |

| performance_schema_users_size | 100 |

| pid_file | /data/mysql/aliyun.pid |

| plugin_dir | /usr/local/mysql/lib/plugin/ |

| port | 3306 |

| preload_buffer_size | 32768 |

| profiling | OFF |

| profiling_history_size | 15 |

| protocol_version | 10 |

| proxy_user | |

| pseudo_slave_mode | OFF |

| pseudo_thread_id | 6 |

| query_alloc_block_size | 8192 |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 1048576 |

| query_cache_type | OFF |

| query_cache_wlock_invalidate | OFF |

| query_prealloc_size | 8192 |

| rand_seed1 | 0 |

| rand_seed2 | 0 |

| range_alloc_block_size | 4096 |

| read_buffer_size | 131072 |

| read_only | OFF |

| read_rnd_buffer_size | 262144 |

| relay_log | |

| relay_log_basename | |

| relay_log_index | |

| relay_log_info_file | relay-log.info |

| relay_log_info_repository | FILE |

| relay_log_purge | ON |

| relay_log_recovery | OFF |

| relay_log_space_limit | 0 |

| report_host | |

| report_password | |

| report_port | 3306 |

| report_user | |

| rpl_stop_slave_timeout | 31536000 |

| secure_auth | ON |

| secure_file_priv | NULL |

| server_id | 128 |

| server_id_bits | 32 |

| server_uuid | ba14c5b2-6c9c-11e8-a1c4-00163e02ef75 |

| show_old_temporals | OFF |

| simplified_binlog_gtid_recovery | OFF |

| skip_external_locking | ON |

| skip_name_resolve | OFF |

| skip_networking | OFF |

| skip_show_database | OFF |

| slave_allow_batching | OFF |

| slave_checkpoint_group | 512 |

| slave_checkpoint_period | 300 |

| slave_compressed_protocol | OFF |

| slave_exec_mode | STRICT |

| slave_load_tmpdir | /tmp |

| slave_max_allowed_packet | 1073741824 |

| slave_net_timeout | 3600 |

| slave_parallel_workers | 0 |

| slave_pending_jobs_size_max | 16777216 |

| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |

| slave_skip_errors | OFF |

| slave_sql_verify_checksum | ON |

| slave_transaction_retries | 10 |

| slave_type_conversions | |

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file | /data/mysql/aliyun-slow.log |

| socket | /tmp/mysql.sock |

| sort_buffer_size | 262144 |

| sql_auto_is_null | OFF |

| sql_big_selects | ON |

| sql_buffer_result | OFF |

| sql_log_bin | ON |

| sql_log_off | OFF |

| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

| sql_notes | ON |

| sql_quote_show_create | ON |

| sql_safe_updates | OFF |

| sql_select_limit | 18446744073709551615 |

| sql_slave_skip_counter | 0 |

| sql_warnings | OFF |

| ssl_ca | |

| ssl_capath | |

| ssl_cert | |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | |

| storage_engine | InnoDB |

| stored_program_cache | 256 |

| sync_binlog | 0 |

| sync_frm | ON |

| sync_master_info | 10000 |

| sync_relay_log | 10000 |

| sync_relay_log_info | 10000 |

| system_time_zone | CST |

| table_definition_cache | 1400 |

| table_open_cache | 2000 |

| table_open_cache_instances | 1 |

| thread_cache_size | 9 |

| thread_concurrency | 10 |

| thread_handling | one-thread-per-connection |

| thread_stack | 262144 |

| time_format | %H:%i:%s |

| time_zone | SYSTEM |

| timed_mutexes | OFF |

| timestamp | 1529483126.544623 |

| tmp_table_size | 16777216 |

| tmpdir | /tmp |

| transaction_alloc_block_size | 8192 |

| transaction_allow_batching | OFF |

| transaction_prealloc_size | 4096 |

| tx_isolation | REPEATABLE-READ |

| tx_read_only | OFF |

| unique_checks | ON |

| updatable_views_with_limit | YES |

| version | 5.6.40 |

| version_comment | MySQL Community Server (GPL) |

| version_compile_machine | x86_64 |

| version_compile_os | linux-glibc2.12 |

| wait_timeout | 28800 |

| warning_count | 0 |

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

454 rows in set (0.00 sec)

mysql> show variables like 'max_connect%'; 查找匹配max_connect的参数变量，%是通配符

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

| Variable_name | Value |

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

| max_connect_errors | 100 |

| max_connections | 151 |

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

2 rows in set (0.01 sec)

mysql> show variables like 'slow%'; 查找匹配slow的变量，%是通配符

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

| Variable_name | Value |

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

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file | /data/mysql/aliyun-slow.log |

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

3 rows in set (0.00 sec)

mysql> set global max_connect_errors=1000; 更改变量max_connect_errors的值为1000

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connect%'; 再次查找匹配max_connect的字段

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

| Variable_name | Value |

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

| max_connect_errors | 1000 | 只在内存中生效，永久生效需要vim /etc/my.cnf，并重启MySQL

| max_connections | 151 |

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

2 rows in set (0.00 sec)

mysql> show processlist; 查看队列，查看哪些用户在连接数据库，执行的操作，有没有锁表

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

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

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

| 6 | root | aliyun:123 | db1 | Query | 0 | init | show processlist |

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

1 row in set (0.00 sec)

mysql> show full processlist; 查看完整队列

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

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

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

| 6 | root | aliyun:123 | db1 | Query | 0 | init | show full processlist |

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

1 row in set (0.01 sec)