# 附录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后做了改善，直到收集信息才会激活。