# 附录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后做了改善，直到收集信息才会激活。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://rowkey-books.gitbook.io/pragmatic-java-engineer/mysql-usage.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.