2017-04-22

DB / mysql

18 分钟读完 (大约 2760 个字) 0次访问

170422-MySQL常用运维命令汇总

MySQL常用命令汇总

MySQL常用命令汇总

一、基础部分

查询当前mysql的版本

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.41-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.41                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.41-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

查看mysql实例的端口

mysql> select @@port;
+--------+
| @@port |
+--------+
|   4306 |
+--------+
1 row in set (0.00 sec)

mysql> show variables like '%port%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_support_xa   | ON    |
| large_files_support | ON    |
| port                | 4306  |
| report_host         |       |
| report_password     |       |
| report_port         | 4306  |
| report_user         |       |
+---------------------+-------+
7 rows in set (0.00 sec)

查看实例的socket数据

mysql> select @@socket;
+---------------------------+
| @@socket                  |
+---------------------------+
| /var/lib/mysql/mysql.sock |
+---------------------------+
1 row in set (0.01 sec)

查看实例的数据路径

mysql>  show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

显示所有数据库名的命令
1
show databases;

显示表结构和列结构的命令。

mysql> desc mysql.general_log;
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                | Null | Key | Default           | Extra                       |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| event_time   | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host    | mediumtext          | NO   |     | NULL              |                             |
| thread_id    | bigint(21) unsigned | NO   |     | NULL              |                             |
| server_id    | int(10) unsigned    | NO   |     | NULL              |                             |
| command_type | varchar(64)         | NO   |     | NULL              |                             |
| argument     | mediumtext          | NO   |     | NULL              |                             |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

显示正在执行的线程。

mysql> show processlist \G
*************************** 1. row ***************************
    Id: 2772
  User: root
  Host: 122.225.201.180:7797
    db: NULL
Command: Query
  Time: 0
  State: init
  Info: show processlist
1 row in set (0.02 sec)

查看语句的执行计划

mysql> explain select * from user_info_0 where user_name ='laosan.xin' \G
*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
        table: user_info_0
        type: ALL
possible_keys: idx_user_name
          key: NULL
      key_len: NULL
          ref: NULL
        rows: 6
        Extra: Using where
1 row in set (0.00 sec)

查看表的索引情况

mysql> show index from user_info_0;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_info_0 |          0 | PRIMARY       |            1 | user_id     | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| user_info_0 |          1 | idx_user_name |            1 | user_name   | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

查看统计信息

mysql> select * from information_schema.STATISTICS where table_name ='USER_INFO_0' \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: user_0
  TABLE_NAME: user_info_0
  NON_UNIQUE: 0
INDEX_SCHEMA: user_0
  INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 1
  COLUMN_NAME: user_id
    COLLATION: A
  CARDINALITY: 6
    SUB_PART: NULL
      PACKED: NULL
    NULLABLE:
  INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: user_0
  TABLE_NAME: user_info_0
  NON_UNIQUE: 1
INDEX_SCHEMA: user_0
  INDEX_NAME: idx_user_name
SEQ_IN_INDEX: 1
  COLUMN_NAME: user_name
    COLLATION: A
  CARDINALITY: 2
    SUB_PART: NULL
      PACKED: NULL
    NULLABLE: YES
  INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
*************************** 3. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: user_1
  TABLE_NAME: user_info_0
  NON_UNIQUE: 0
INDEX_SCHEMA: user_1
  INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 1
  COLUMN_NAME: user_id
    COLLATION: A
  CARDINALITY: 0
    SUB_PART: NULL
      PACKED: NULL
    NULLABLE:
  INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
3 rows in set (0.01 sec)

查询定义的packet大小

mysql> select @@max_allowed_packet/1024;
+---------------------------+
| @@max_allowed_packet/1024 |
+---------------------------+
|                 4096.0000 |
+---------------------------+
1 row in set (0.00 sec)

查看master状态。可以查看最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点

mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000034 |   307552 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.06 sec)

mysql>  show slave status;
Empty set (0.01 sec)

查看所有的log文件，在主服务器上执行。(即查看所有binlog日志列表)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1035 |
| mysql-bin.000002 |      6893 |
| mysql-bin.000003 |      4974 |
| mysql-bin.000004 |    342638 |
| mysql-bin.000005 |   9331243 |
| mysql-bin.000006 |    183793 |
| mysql-bin.000007 |  44107008 |
| mysql-bin.000008 |  52031853 |
| mysql-bin.000009 |    195507 |
| mysql-bin.000010 |       143 |
| mysql-bin.000011 |       143 |
| mysql-bin.000012 |  15651673 |
| mysql-bin.000013 |     36417 |
| mysql-bin.000014 |   7796717 |
| mysql-bin.000015 |     61890 |
| mysql-bin.000016 |  21469800 |
| mysql-bin.000017 | 116846922 |
| mysql-bin.000018 |   2543535 |
| mysql-bin.000019 |    559869 |
| mysql-bin.000020 |    269689 |
| mysql-bin.000021 |    149803 |
| mysql-bin.000022 |       143 |
| mysql-bin.000023 |    826333 |
| mysql-bin.000024 |   3073827 |
| mysql-bin.000025 |    160419 |
| mysql-bin.000026 |      4824 |
| mysql-bin.000027 |     13055 |
| mysql-bin.000028 |     50982 |
| mysql-bin.000029 |    408431 |
| mysql-bin.000030 |     17465 |
| mysql-bin.000031 |    518054 |
| mysql-bin.000032 |    122495 |
| mysql-bin.000033 |  82363196 |
| mysql-bin.000034 |    307552 |
+------------------+-----------+
34 rows in set (0.00 sec)

purge日志

1	purge binary logs to 'mysql-bin3306.000003';

显示最近的警告详情

mysql> show warnings;
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Error | 1381 | You are not using binary logging |
+-------+------+----------------------------------+
1 row in set (0.00 sec)

查看当前mysqld的所有参数，包括默认值。
1
show variables \G;

查看某一个用户的权限，

mysql> show grants for 'weixin'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for weixin@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'weixin'@'%' IDENTIFIED BY PASSWORD '*406C968ECC27E2FDC6E93CFF1053E3F3E4B0BE55' |
| GRANT ALL PRIVILEGES ON `weixin`.* TO 'weixin'@'%'                                                    |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

查看某表的创建脚本

mysql> show create table user_info_0 \G
*************************** 1. row ***************************
      Table: user_info_0
Create Table: CREATE TABLE `user_info_0` (
  `user_id` bigint(19) NOT NULL,
  `user_name` varchar(45) DEFAULT NULL,
  `account` varchar(45) NOT NULL,
  `password` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `idx_user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

二、实例参数部分

指定条件的参数设置查询，例如查询以log_slave开头的参数设置

mysql> show variables like 'log_slave%' \G
*************************** 1. row ***************************
Variable_name: log_slave_updates
        Value: OFF
1 row in set (0.02 sec)

查看是否开启了慢查询日志

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.01 sec)

ON代表开启。可以在线打开
set global slow_query_log = 1;

查看慢日志路径

mysql> show variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/laosanxin-slow.log |
+---------------------+-----------------------------------+
1 row in set (0.01 sec)

查看慢查询定义的阈值，单位是秒。记录的查询是大于该值，不包括该值。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

查看日志的输出格式（file或table）

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

查看日志的时间信息，UTC时间或者SYSTEM时间

1 2	mysql> show variables like 'log_timestamps'; Empty set (0.01 sec)

查看从服务器是否开启慢查询日志，ON代表开启。

mysql> show variables like 'log_slow_slave_statements';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_slave_statements | OFF   |
+---------------------------+-------+
1 row in set (0.01 sec)

将没有使用索引的SQL语句记录到慢查询日志中

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.01 sec)

集合上面的参数一起使用，限制每分钟内，在慢查询日志中，记录没有使用索引的次数。避免日志快速增长。

mysql> show variables like 'log_throttle_queries_not_using_indexes';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
1 row in set (0.01 sec)

查看创建的临时表的存储引擎类型

mysql> show variables like "default%tmp%";
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB |
+----------------------------+--------+
1 row in set (0.01 sec)

查询log文件大小

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.00 sec)

查询页的大小。一旦数据库通过innodb_page_size设置完成，则后续无法更改。innodb_page_size
是针对普通表的，压缩表不受限制。

mysql> select @@innodb_page_size/1024;
+-------------------------+
| @@innodb_page_size/1024 |
+-------------------------+
|                 16.0000 |
+-------------------------+
1 row in set (0.01 sec)

查看缓冲池的大小，每次读写数据都是通过buffer pool；当buffer pool中没有所需的数据时，才去硬盘中获取。该值设置的越大越好。buffer pool 也是以页（page）为单位的，且大小和innodb_page_size一致。

mysql> select @@innodb_buffer_pool_size/1024/1024;
+-------------------------------------+
| @@innodb_buffer_pool_size/1024/1024 |
+-------------------------------------+
|                        128.00000000 |
+-------------------------------------+
1 row in set (0.00 sec)

设置多少个缓冲池。设置多个instance可将热点打散，提高并发性能（建议设置成cpu个数值）

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 8     |
+------------------------------+-------+
1 row in set (0.01 sec)

查看buffer pool的状态
1
mysql> show engine innodb status \G
在线调整innodb_buffer_pool_size。MySQL 5.7之前的版本，修改该值，需要重启
1
set global innodb_buffer_pool_size=2*1024*1024*1024;
在MySQL 5.6 以后，可以在停机的时候dump出buffer pool的数据，然后在
启动的时候Load进buffer pool。该功能可以在MySQL启动时自动预热，无需人工干预。
1
show variables like 'innodb_buffer_pool_dump_at_shutdown';
dumpd 百分比，是每个buffer pool文件，而不是整体
1
show variables like 'innodb_buffer_pool_dump_pct'
启动时加载dump的文件，恢复到buffer pool中。dump的越多，启动的越慢
1
show variables like 'innodb_buffer_pool_load_at_startup';

查看锁的信息

mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+-------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id  |
+-------------------+-------------------+-----------------+-------------------+
| 12628543          | 12628543:1327:3:2 | 12628533        | 12628533:1327:3:2 |
+-------------------+-------------------+-----------------+-------------------+
1 row in set (0.00 sec)

查看隔离级别

mysql>  show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

设置隔离级别

1	set transaction_isolation='read-committed';

设置为ON，表示将死锁信息打印到err_log中

mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

master thread 每秒刷新redo的buffer到logfile。5.7版本可以设置刷新间隔时间，默认是1秒。

mysql> show variables like "%innodb_flush_log_at_timeout%";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_flush_log_at_timeout | 1     |
+-----------------------------+-------+
1 row in set (0.01 sec)

查看binlog的类型

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

statement 记录SQL语句；ROW 记录SQL语句操作的那些行（行的变化）；mixed 混合statement 和 Row 格式（不推荐）

show plugins 查看插件

mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
42 rows in set (0.00 sec)

是否支持动态加载插件

mysql> show variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.01 sec)

安装插件

1 2	mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.12 sec)

卸载插件

1 2	mysql> uninstall plugin rpl_semi_sync_master; Query OK, 0 rows affected (0.03 sec)

三、其他

3.1、MySQL的@与@@区别

@x 是用户自定义的变量 (User variables are written as @var_name)
@@x 是 global或session变量 (@@global @@session )

@@查看全局变量
1
select @@log_error;

@设置全局变量值

1
2
3

mysql> SET @t1=0, @t2=0, @t3=0;

mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

3.2、MySQl中的\g和\G

3.2.1、\g

在MySQL的sql语句后加上\g，效果等同于加上定界符，一般默认的定界符是分号;

mysql> select  * from test \g
+------------+-------------------+
| a          | b                 |
+------------+-------------------+
| 2019-12-21 | 11111111111111111 |
+------------+-------------------+
1 row in set (0.01 sec)

3.2.2、\G

在MySQL的sql语句后加上\G，表示将查询结果进行按列打印，可以使每个字段打印到单独的行。即将查到的结构旋转90度变成纵向；

使用前，单行展示，字段太多，结构难分辨

mysql> select  * from test ;
+------------+-------------------+
| a          | b                 |
+------------+-------------------+
| 2019-12-21 | 11111111111111111 |
+------------+-------------------+
1 row in set (0.02 sec)

使用后，显示效果不错，方便查询

mysql> select  * from test \G
*************************** 1. row ***************************
a: 2019-12-21
b: 11111111111111111
1 row in set (0.01 sec)

3.3、max_allowed_packet参数设置

MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。

修改方法：

修改配置文件

可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。
1
max_allowed_packet = 20M
在mysql命令行中修改

在mysql 命令行中运行
1
set global max_allowed_packet = 2*1024*1024*10

本文参考连接

# mysql