# 附录C: MySQL常用命令

MySQL版本：5.5.19

## 系统命令

1.  启动MySQL

    ```
    mysqladmin start
    /ect/init.d/mysql start
    ```
2.  重启MySQL

    ```
     mysqladmin restart
     /ect/init.d/mysql restart
    ```
3.  关闭MySQL

    ```
     mysqladmin shutdown
     /ect/init.d/mysql  shutdown
    ```
4.  连接本机上的MySQL

    进入目录`mysql\bin`，键入命令`mysql -uroot -p`，回车后提示输入密码。使用`exit`退出MySQL。
5.  修改MySQL密码

    ```
    mysqladmin -u用户名 -p旧密码 password 新密码
    ```

    或进入MySQL命令行后设置

    ```
    set password for root=password("root");
    ```
6.  增加新用户

    ```
    grant select on 数据库.* to 用户名@登录主机 identified by "密码";
    ```

    示例：增加一个用户test密码为123，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。以root用户连入MySQL，然后键入以下命令：

    ```
    grant select,insert,update,delete on *.* to test@"% " identified by "123";
    ```
7.  刷新MySQL的系统权限相关表

    ```
    flush privileges
    ```

    新设置用户或更改密码后需刷新MySQL的系统权限相关表
8.  查看MySQL支持的存储引擎

    ```
    show engines
    ```
9.  查看MySQL当前的默认存储引擎

    ```
    show variables like '%storage_engine%';
    ```
10. 查看Mysql服务器上的版本

    ```
    select version();
    ```
11. 查看数据库连接情况

    ```
    show variables like '%max_connections%’; #查看最大连接数设置
    set global max_connections = 200; #设置最大连接数

    select * from information_schema.processlist where db=''; #查看进程/连接列表，可指定数据库。和show processlist一样
    ```
12. 查看死锁信息

    ```
    show engine innodb status; #LATEST DETECTED DEADLOCK这一栏即死锁信息
    ```
13. 开启慢查询日志

    ```
    show variables like '%slow%’; //查看慢查询日志配置
    set global slow_query_log='ON'; //开启慢查询
    set global long_query_time=4;//这只慢查询语句的耗时阈值
    ```
14. 查询结果输出到文件

    ```
    [select query]  into outfile '[filePath]';

    pager cat > [filePath]; #所有查询结果都自动写入指定文件中，并前后覆盖

    mysql -h [host] -u [user] -p [password] -P [port] -e "[query]"  > [filePath]
    ```

## 数据操作

首先登录到MySQL中，有关操作都是在MySQL的提示符下进行，而且每个命令以分号结束。

1.  显示数据库列表

    ```
     show databases;
    ```
2.  显示库中的数据表

    ```
    show tables;
    ```
3.  显示数据表的结构

    ```
     describe 表名;
    ```
4.  建库

    ```
    create database 库名;

    create database db_name default character set utf8 collate utf8_general_ci;
    ```

    create database 的语法：

    ```
     create {database | schema} [if not exists] db_name  [create_specification  [, create_specification  ] ...] create_specification  :  [default] character set charset_name  | [default] collate collation_name 
    ```
5.  建表

    ```
     create table 表名(字段设定列表)；
    ```
6.  删库和删表

    ```
    drop database 库名;
    drop table 表名；
    ```
7.  将表中记录清空

    ```
    delete from 表名;
    truncate table 表名; #不同于delete, 不用扫描全表
    ```
8.  显示表中的记录：

    ```
    select * from 表名;
    ```
9.  添加列

    ```
    alter table 数据表名 add 新列名 新列类型 default 0 comment;
    ```
10. 修改列名

    ```
    alter table 数据表名 change 原列名 新列名 新列类型;
    ```
11. 修改列

    ```
    alter table 表名 modify column 列名 类型;
    ```
12. 删除列

    ```
    alter table 表名 drop column 列名;
    ```
13. 修改表名

    ```
    rename table 旧表名 to 新表名;
    ```
14. 添加索引

    ```
    alter table table_name add index index_name (column_list);
    alter table table_name add unique index_name (column_list);
    alter table table_name add primary key (column_list);
    ```
15. 删除索引

    ```
    drop index index_name on talbe_name
    alter table table_name drop index index_name
    alter table table_name drop primary key
    ```
16. 查看索引

    ```
    show index from tblname;
    show keys from tblname;
    ```
17. 复制表结构及数据到新表

    ```
    create table 新表 select * from 旧表;
    ```
18. 只复制表结构到新表

    ```
    create table 新表 select * from 旧表 where 1=2;
    create table 新表 like 旧表;
    ```
19. 复制旧表的数据到新表（假设两个表结构一样）

    ```
    insert into 新表 select * from 旧表;
    ```
20. 复制旧表的数据到新表（假设两个表结构不一样）

    ```
    insert into 新表(字段1,字段2,.......) select 字段1,字段2,...... from 旧表
    ```
21. 设置表的自增主键起始值

    ```
    alter table table_name AUTO_INCREMENT = 10000;
    ```

## 数据的导入导出

1.  文本数据转到数据库中

    文本数据应符合的格式：字段数据之间用tab键隔开，null值用空格字符来代替。例如：

    ```
    1	name	test	2017-1-1
    ```

    数据传入命令

    ```
     load data local infile "文件名" into table 表名;
    ```
2.  导出数据库和表

    将数据库news中的所有表备份到news.sql文件，news.sql是一个文本文件，文件名任取。

    ```
    mysqldump --opt news > news.sql  
    ```

    将数据库news中的author表和article表备份到author.article.sql文件，author.article.sql是一个文本文件，文件名任取。

    ```
     mysqldump --opt news author article > author.article.sql
    ```

    将数据库dbl和db2备份到news.sql文件，news.sql是一个文本文件，文件名任取。

    ```
     mysqldump --databases db1 db2 > news.sql
    ```

    把host上的以用户user、密码pass的数据库dbname导入到文件file.dump中

    ```
    mysqldump -h host -u user -p pass --databases dbname > file.dump
    ```

    将所有数据库备份到all-databases.sql文件，all-databases.sql是一个文本文件，文件名任取。

    ```
    mysqldump --all-databases > all-databases.sql
    ```
3.  导入数据

    导入数据库:

    ```
    mysql < all-databases.sql
    ```

    在mysql命令行导入表:

    ```
    source news.sql;
    ```

## 编码操作

*   查看数据库编码

    ```
    show create database db_name;
    ```
*   查看数据表编码，包括表使用的数据库引擎

    ```
    show create table tbl_name;
    ```
*   查看字段编码

    ```
    show full columns from tbl_name;
    ```
*   改变整个MySQL的编码, 启动MySQL的时候，mysqld\_safe命令行加入

    ```
    --default-character-set=gbk;
    ```
*   改变某个库的编码，在MySQL提示符后输入命令

    ```
    alter database db_name default character set gbk;
    ```
*   把表默认的字符集和所有字符列（char,varchar,text）改为新的字符集

    ```
    alter table tbl_name convert to character set character_name [collate ...];
    ```

    示例如下：

    ```
    alter table logtest convert to character set utf8 collate utf8_general_ci;
    	
    alter table table_name convert to character set utf8mb4 collate utf8mb4_bin; #使得数据库支持emoji
    ```
*   修改表的默认字符集

    ```
    alter table tbl_name default character set character_name [collate...];
    ```
*   修改字段的字符集

    ```
    alter table tbl_name change c_name c_name character set character_name [collate ...];
    ```

## 数据库元信息查询

information\_schema数据库中保存了各个数据库以及表的元信息，主要包括：

* schemata表：提供了当前MySQL实例中所有数据库的信息。是`show databases`的结果来源。
* tables表：提供了关于数据库中的表的信息，包括视图。详细表述了某个表属于哪个schema、表的类型、表使用的引擎以及创建时间等信息。是`show tables from [schemaName]`和`show table status from [schemaName] like '[tableName]'`的结果来源。
* columns表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是`show columns from [tableName]`的结果来源。
* statistics表：提供了关于表索引的信息。是`show index from [tableName]`的结果来源。
* user\_privileges表：给出了关于用户权限的信息。该信息源自mysql.user授权表。
* schema\_privileges表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。
* table\_privileges表：给出了关于表权限的信息。该信息源自mysql.tables\_priv授权表。
* column\_privileges表：给出了关于列权限的信息。该信息源自mysql.columns\_priv授权表。
* table\_constraints表：描述了存在约束的表，以及表的约束类型。

例如，可通过tables查询某个数据表的创建时间:

```
select create_time from tables where table_schema='数据库名' and table_name='表名';
```

## 数据库性能信息查询

performance\_schema数据库用于收集数据库服务器性能参数，其中所有表的存储引擎为performance\_schema。此功能MySQL5.5是默认关闭的，从5.6版本后变为默认开启。此功能开关如下：

```
[mysqld]
performance_schema=ON/OFF
```

其中的常用数据表如下：

* setup\_actors：配置用户纬度的监控，默认监控所有用户。
* setup\_consumers：配置events的消费者类型，即收集的events写入到哪些统计表中。
* setup\_objects：配置监控对象，默认对mysql，performance\_schema和information\_schema中的表都不监控，而其它DB的所有表都监控。
* setup\_timers：配置每种类型指令的统计时间单位。MICROSECOND表示统计单位是微妙，CYCLE表示统计单位是时钟周期，时间度量与CPU的主频有关，NANOSECOND表示统计单位是纳秒。但无论采用哪种度量单位，最终统计表中统计的时间都会转换到皮秒。（1秒＝1000000000000皮秒）
* file\_instances：文件实例, 记录了系统中打开了文件的对象，包括ibdata文件，redo文件，binlog文件，用户的表文件等。
* rwlock\_instances： 读写锁同步对象实例，记录了系统中使用读写锁对象的所有记录，其中name为 wait/synch/rwlock/\*。
* socket\_instances：活跃会话对象实例，记录了thread\_id,socket\_id,ip和port，其它表可以通过thread\_id与socket\_instance进行关联，获取IP-PORT信息，能够与应用对接起来。
*
  * socket\_summary\_by\_instance、socket\_summary\_by\_event\_name：socket聚合统计表。
* events\_waits\_current：记录了当前线程等待的事件。
* events\_waits\_history：记录了每个线程最近等待的10个事件。
* events\_waits\_history\_long：记录了最近所有线程产生的10000个事件。
* events\_stages\_current：记录了当前线程所处的执行阶段。同events\_waits\_current一样，events\_stages\_history、events\_stages\_history\_long是历史记录。
* events\_statements\_current：通过 thread\_id+event\_id可以唯一确定一条记录。Statments表只记录最顶层的请求，SQL语句或是COMMAND，每条语句一行。event\_name形式为statement/sql/_，或statement/com/_。同events\_waits\_current一样，events\_statements\_history、events\_statements\_history\_long是历史记录。
* events\_waits\_summary\_by\_thread\_by\_event\_name：按每个线程和事件来统计，thread\_id+event\_name唯一确定一条记录。包括总的等待时间、最小等待时间、平均等待时间以及最大等待时间。
* table\_lock\_waits\_summary\_by\_table：聚合了表锁等待事件，包括internal lock 和 external lock。
* table\_io\_waits\_summary\_by\_table：根据wait/io/table/sql/handler，聚合每个表的I/O操作（逻辑IO纬度）。
* users：记录用户连接数信息。
* hosts：记录了主机连接数信息。
* accounts：记录了用户主机连接数信息。
* threads：监视服务端的当前运行的线程。

几个常用应用示例如下：

*   统计哪个SQL执行最多

    ```
    SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR desc LIMIT 1\G
    ```
*   哪个SQL平均响应时间最长

    ```
    SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT desc LIMIT 1\G
    ```
*   哪个索引没有使用过

    ```
    SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
    ```

需要注意的是，MySQL5.5开启此功能即使没有数据可收集，也会有性能损失，慎重开启。MySQL5.6后做了改善，直到收集信息才会激活。
