干货！总结运维人日常工作中使用的 MySQL 高频命令！

2024-11-19

MySQL常见操作

干货！一文总结运维人日常工作中使用的 MySQL 的高频命令！

1. 数据库管理

• 创建数据库

# 查看数据库列表
mysql> SHOW DATABASES;

# 创建一个数据库，库名以 sreyys 为例（默认字符集）
mysql> CREATE DATABASE sreyys;

# 查看创建的数据库信息(字符集)
mysql> SHOW CREATE DATABASE sreyys;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| sreyys   | CREATE DATABASE `sreyys` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 创建一个 UTF8 字符集和排序规则的库
mysql> CREATE DATABASE sreyys_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;

# 查看该 UTF8 数据库
mysql> SHOW CREATE DATABASE sreyys_utf8;
+-------------+------------------------------------------------------------------------------------------------------------+
| Database    | Create Database                                                                                            |
+-------------+------------------------------------------------------------------------------------------------------------+
| sreyys_utf8 | CREATE DATABASE `sreyys_utf8` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-------------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 创建一个 utf8mb4 字符集和排序规则的库
mysql> CREATE DATABASE sreyys_utf8mb4 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 查看该 utf8mb4 数据库
mysql> SHOW CREATE DATABASE sreyys_utf8mb4;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------+
| Database       | Create Database                                                                                                                          |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------+
| sreyys_utf8mb4 | CREATE DATABASE `sreyys_utf8mb4` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• 修改指定数据库的编码

# 修改 sreyys_utf8 编码为 utf8mb4
mysql> ALTER DATABASE sreyys_utf8 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 查看修改后的编码
mysql> SHOW CREATE DATABASE sreyys_utf8;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| Database    | Create Database                                                                                                                       |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| sreyys_utf8 | CREATE DATABASE `sreyys_utf8` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• 删除数据库

# 格式：DROP DATABASE 数据库名
mysql> DROP DATABASE sreyys_utf8mb4;

2. 数据库常见配置项

• 数据库默认密码策略

# 查看 sql
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

# 注解
validate_password.check_user_name: 不检查密码是否与用户名相同
validate_password.dictionary_file: 未指定字典文件路径
validate_password.length: 密码最小长度为8个字符
validate_password.mixed_case_count: 密码必须包含至少1个大写字母
validate_password.number_count: 密码必须包含至少1个数字
validate_password.policy: 密码复杂度策略为中等级别(MEDIUM);LOW(底)；STRONG（强）
validate_password.special_char_count: 密码必须包含至少1个特殊字符

# 注意：MySQL 5.7 和 MySQL8 不同
# 临时设置密码级别为 LOW；密码强度较低，要求密码长度至少为8个字符。
mysql> set global validate_password.policy=0;

# 临时设置密码级别为 STRONG；要求密码长度至少为8个字符，并且包含大写字母、小写字母、数字和特殊字符。
mysql> set global validate_password.policy=2;

# 永久设置
root@n9e-categraf:~# echo "validate_password.policy=0" >> /etc/mysql/mysql.conf.d/mysqld.cnf
root@n9e-categraf:~# service mysql stop
root@n9e-categraf:~# service mysql start

# 如果查询为空，MySQL8 可以执行以下 sql
# mysql> INSTALL COMPONENT 'file://component_validate_password';

• 数据库时区

mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

# 查看当前时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 11:15:28  |
+-----------+
1 row in set (0.00 sec)

# 临时修改mysql全局时区为 UTC+8
mysql> set global time_zone = '+8:00';

# 临时修改当前会话时区为 UTC+8
mysql> set time_zone = '+8:00';

# 永久修改时区为 UTC+8
root@n9e-categraf:~# echo "default-time_zone = '+8:00'" >> /etc/mysql/mysql.conf.d/mysqld.cnf
root@n9e-categraf:~# service mysql restart

# 验证
mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 19:21:58  |
+-----------+
1 row in set (0.00 sec)

• 查看连接数

# 查看 MySQL 最大连接数
# max_connections 可以负责全局最大连接数管理。
mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.01 sec)


# 查看每个用户的最大连接数
# max_user_connections 负责限制每个用户的最大连接数，设置数量不能超过max_connections。
mysql> show variables like '%max_user_connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_user_connections | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

• 修改连接数

# 临时修改全局 MySQL 最大连接数
mysql> set global max_connections=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.01 sec)

# 永久生效 写入到 [mysqld] 下
root@n9e-categraf:~# echo "max_connections=1000" >> /etc/mysql/mysql.conf.d/mysqld.cnf
root@n9e-categraf:~# systemctl restart mysql
mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)

3. 用户管理

创建用户

MySQL创建用户和授权分为两步操作，先创建，在授权。

# 格式 CREATE USER 用户名@'授权来源地址，% 为全部' IDENTIFIED BY '密码';
mysql> CREATE USER sreyys@'%' IDENTIFIED BY 'sreyys@666666';

# 例如指定允许 114.114.114.114 访问
mysql> CREATE USER sreyys@'114.114.114.114' IDENTIFIED BY 'sreyys@666666';

# 例如指定允许 192.168.0.0/16 网段访问
mysql> CREATE USER sreyys@'192.168.%.%' IDENTIFIED BY 'sreyys@666666';

• 新增授权

# 格式 GRANT 权限（ALL 为全部） ON 数据库名.数据表名(*.* 代表全部) TO 用户名@'授权来源地址'
mysql> GRANT ALL ON *.* TO sreyys@'%';

# 刷新MySQL的系统权限相关表
mysql> FLUSH PRIVILEGES;

# 例如授权 sreyys@'%' 对 sreyys 库为只读
mysql> GRANT SELECT ON sreyys.* TO sreyys@'%';

# 刷新MySQL的系统权限相关表
mysql> FLUSH PRIVILEGES;

# 查看该用户对 sreyys 数据库的权限
mysql> SHOW GRANTS FOR sreyys@'%';
+--------------------------------------------+
| Grants for sreyys@%                        |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `sreyys`@`%`         |
| GRANT SELECT ON `sreyys`.* TO `sreyys`@`%` |
+--------------------------------------------+
2 rows in set (0.00 sec)

• 新增一个具有主从同步的用户权限

# 创建用户
mysql> CREATE USER sync@'%' IDENTIFIED BY 'sreyys@666666';

# 授权 （canal，dts，dms，主从同步等需要该权限）
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE,SHOW VIEW,SELECT,CREATE ON *.* TO sync@'%';

# 刷新权限
mysql> FLUSH PRIVILEGES;

# 查看该用户权限
mysql> SHOW GRANTS FOR sync@'%';
+---------------------------------------------------------------------------------------------+
| Grants for sync@%                                                                           |
+---------------------------------------------------------------------------------------------+
| GRANT SELECT, CREATE, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO `sync`@`%` |
+---------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

• 移除授权

# 移除授权和添加授权差不多，把 GRANT 换成 REVOKE，把 to 换成 FROM

# 回收 sreyys@'%' 用户对 sreyys 数据库的只读权限
mysql> REVOKE SELECT ON sreyys.* FROM sreyys@'%';

# 刷新MySQL的系统权限相关表
mysql> FLUSH PRIVILEGES;

# 查看权限，对比上图 SELECT 权限已被回收
mysql> SHOW GRANTS FOR sreyys@'%';
+------------------------------------+
| Grants for sreyys@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO `sreyys`@`%` |
+------------------------------------+
1 row in set (0.00 sec)

# 也可以只回收某一权限，如该用户同时拥有 SELECT,INSERT,UPDATE,DELETE 我们可以只回收 DELETE
# 如 授权 sreyys@'%' 用户对 sreyys 数据库 拥有 SELECT,INSERT,UPDATE,DELETE 权限
mysql> GRANT SELECT,INSERT,UPDATE,DELETE on sreyys.* to sreyys@'%';

# 刷新MySQL的系统权限相关表
mysql> FLUSH PRIVILEGES;

# 查看权限
mysql> SHOW GRANTS FOR sreyys@'%';
+--------------------------------------------------------------------+
| Grants for sreyys@%                                                |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sreyys`@`%`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `sreyys`.* TO `sreyys`@`%` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

# 回收 DELETE 权限
mysql> REVOKE DELETE ON sreyys.* FROM sreyys@'%';
# 刷新MySQL的系统权限相关表
mysql> FLUSH PRIVILEGES;


# 查看回收后剩余权限
mysql> SHOW GRANTS FOR sreyys@'%';
+------------------------------------------------------------+
| Grants for sreyys@%                                        |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sreyys`@`%`                         |
| GRANT SELECT, INSERT, UPDATE ON `sreyys`.* TO `sreyys`@`%` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

• 权限说明

列举 MySQL 内置的名称和含义，方便大家在授权和移除权限时查阅。

1. SELECT：允许用户读取数据库中的数据。
2. INSERT：允许用户向数据库中插入新数据。
3. UPDATE：允许用户更新数据库中的数据。
4. DELETE：允许用户删除数据库中的数据。
5. CREATE：允许用户创建新数据库或表格。
6. DROP：允许用户删除数据库或表格。
7. ALTER：允许用户修改数据库的结构。
8. INDEX：允许用户创建或删除索引。
9. REFERENCES：允许用户定义外键关系。
10. CREATE TEMPORARY TABLES：允许用户创建临时表格。
11. EXECUTE：允许用户执行存储过程或函数。
12. LOCK TABLES：允许用户锁定表格，防止其他用户对其进行修改。
13. CREATE VIEW：允许用户创建视图。
14. SHOW VIEW：允许用户查看视图的定义。
15. CREATE ROUTINE：允许用户创建存储过程或函数。
16. ALTER ROUTINE：允许用户修改存储过程或函数。
17. EVENT：允许用户创建、修改或删除事件调度。
18. TRIGGER：允许用户创建触发器。

修改用户密码

# 修改指定用户的密码，如 sreyys@'%' 的密码修改为 sreyys@new_mima
mysql> alter user sreyys@'%' identified by 'sreyys@new_mima';

# 刷新MySQL的系统权限相关表
mysql> FLUSH PRIVILEGES;

# 方法2，修改当前用户的密码
mysql> alter user user() identified by 'sreyys@new_mima';
mysql> FLUSH PRIVILEGES;

• 删除用户


# 删除指定用户 sreyys@'%'，删除后后权限会被回收
mysql> DROP USER sreyys@'%';

# 刷新MySQL的系统权限相关表
mysql> FLUSH PRIVILEGES;

4. 备份与恢复

4.1 备份

备份指定数据库

# 格式：mysqldump -u 用户名 -p 密码 -h MySQL服务器地址 数据库名 > 输出到指定文件中
# --triggers：该参数代表导出触发器。该选项默认启用，用--skip-triggers禁用它。
# --routines：导出存储过程以及自定义函数。
# --single-transaction：使用单个事务来确保一致性快照。这意味着在导出期间，将启动一个事务并保持其打开状态，以确保导出的数据是从同一时间点的一致性视图中提取的。，可以确保在导出数据时使用一致性快照，而不会阻塞写操作或导致数据不一致。（可以理解为导出过程中不锁表）
# --skip-add-drop-table：使用该参数会使导出的 sql 文件中不具备删除表的命令，这个选项通常用于防止意外删除表格的情况，特别是在生产环境中为了避免数据丢失而设置。默认导出的 sql 文件中会先删除表，在创建表，在插入数据。
# 详细 mysqldump 参数：https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

# 导出 mysql 数据库的全部数据，不锁表导出
ubuntu@mysql:~$ mysqldump -u root -p -h 127.0.0.1 mysql --triggers --routines --single-transaction > mysql.sql

备份指定数据表

# 导出指定数据库下的指定表，如 mysql 库下的 user 表
ubuntu@n9e-categraf:~$ mysqldump -uroot -p --databases mysql --tables user --single-transaction > user.sql

# 导出多个表，如导出 user 表 和 plugin 表
ubuntu@n9e-categraf:~$ mysqldump -uroot -p --databases mysql --tables user plugin --single-transaction > table2.sql

仅备份数据结构（不备份数据）

使用 -d 参数只备份数据结构

# 导出指定数据库的数据结构，库名  mysql
ubuntu@n9e-categraf:~$ mysqldump -u root -p -h 127.0.0.1 mysql --triggers --routines --single-transaction -d > mysql.sql

# 导出指定表的数据结构，库名 mysql，表名 user，plugin
ubuntu@n9e-categraf:~$ mysqldump -uroot -p --databases mysql --tables user plugin --single-transaction -d > table_jiegou.sql

4.2 恢复

导入数据比较简单，通常使用 mysql 命令直接导入，或使用 source 进行导入。注意：一般情况下使用 mysqldump 命令导出的 sql 文件默认会先删除表，在创建表，在插入数据。

使用 mysql 命令导入

# 创建数据库(utf8_mb4)
mysql> CREATE DATABASE sreyys DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 导入备份的 sql 文件 到 sreyys
ubuntu@n9e-categraf:~$ mysql -h127.0.0.1 -uroot -p sreyys < /home/ubuntu/240316.sql

• 使用 source 命令导入

# 切换到指定数据库中，如切换到 sreyys 库中
mysql> use sreyys;

# 导入 sql
mysql> source /home/ubuntu/240316.sql

• 查看指定数据表的创建和更新时间

mysql> SELECT
    `TABLE_NAME`, `CREATE_TIME`, `UPDATE_TIME`  
FROM  
    `information_schema`.`TABLES`  
WHERE  
    `information_schema`.`TABLES`.`TABLE_SCHEMA` = '数据库名'  
AND
    `information_schema`.`TABLES`.`TABLE_NAME` = '数据表名';

5. 磁盘占用相关

• 查询 MySQL 占用磁盘大小

mysql> use information_schema;

mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as 总容量 from TABLES;
+-----------+
| 总容量    |
+-----------+
| 1602.98MB |
+-----------+
1 row in set (0.14 sec)

• 查看所有数据库中的表大小

# 无需替换参数，直接执行，会显示所有表的信息
mysql> select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

• 查看各数据库占用的磁盘大小

# 无需替换参数，会全部显示
mysql> select
TABLE_SCHEMA, 
concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc;

查看指定数据库中的表占用磁盘大小

# 查询所有表，只需要修改 table_schema 为自己的数据库名称
mysql> select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='sreyys'
order by data_length desc, index_length desc;
+-----------+-----------------------------+-----------+------------------+------------------+
| 数据库    | 表名                        | 记录数    | 数据容量(MB)     | 索引容量(MB)     |
+-----------+-----------------------------+-----------+------------------+------------------+
| sreyys    | segment              |  12456008 |          1511.00 |           645.87 |
| sreyys    | data_packets         |   1092757 |            63.57 |            43.07 |
+-----------+-----------------------------+-----------+------------------+------------------+
2 rows in set (0.00 sec)

6. Binlog 相关

• 查看 binlog 开启状态

# 全部显示：mysql> show variables like 'log_%';
mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)

• binlog 模式

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

# 临时生效
mysql> SET GLOBAL binlog_format = 'ROW';

# 永久生效
root@n9e-categraf:~# echo "binlog_format = ROW" >> /etc/mysql/mysql.conf.d/mysqld.cnf
root@n9e-categraf:~# systemctl restart mysql

• 查看 binlog 保存时间

MySQL 5.7 和 MySQL 8 参数不同。

# MySQL 5.7
mysql> show variables like 'expire_logs_days';
# 设置 binlog 保存时间
mysql> set global expire_logs_days = 7;

#########################################################################

# MySQL 8 （单位：秒）
mysql> SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
1 row in set (0.01 sec)

# 临时设置保存时间为 3600 秒
mysql> SET GLOBAL binlog_expire_logs_seconds = 3600;

# 永久设置
root@n9e-categraf:~# echo "binlog_expire_logs_seconds = 3600" >> /etc/mysql/mysql.conf.d/mysqld.cnf
# 重启数据库并验证
root@n9e-categraf:~# systemctl restart mysql
mysql> SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| binlog_expire_logs_seconds | 3600  |
+----------------------------+-------+
1 row in set (0.00 sec)

• 查看 binlog 日志

# 查看当前正在写入的binlog
mysql> show master status\G;
*************************** 1. row ***************************
             File: binlog.000011
         Position: 3797
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000011 |     3797 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


# 查看 binlog
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000006 |      1580 | No        |
| binlog.000007 |       180 | No        |
| binlog.000008 |       180 | No        |
| binlog.000009 |       180 | No        |
| binlog.000010 |       180 | No        |
| binlog.000011 |       157 | No        |
+---------------+-----------+-----------+
6 rows in set (0.00 sec)

# 查看指定 binlog 内容
mysql> show binlog events in 'binlog.000011';

• 清理 binlog 日志

# 查看 binlog 列表
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000006 |      1580 | No        |
| binlog.000007 |       180 | No        |
| binlog.000008 |       180 | No        |
| binlog.000009 |       180 | No        |
| binlog.000010 |       180 | No        |
| binlog.000011 |       157 | No        |
+---------------+-----------+-----------+
6 rows in set (0.00 sec)

# 清理 binlog.000011 之前的所有 binlog
mysql> purge master logs to 'binlog.000011';
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000011 |      3797 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

刷新 binlog 列表

# 刷新 binlog 日志
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000011 |      3797 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> flush logs; 
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000011 |      3841 | No        |
| binlog.000012 |       157 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

• 临时关闭 binlog 记录

在MySQL运维工作中，当我们想在主库上执行一些操作但是并不希望该操作被复制到从库上时，可以临时将binlog关闭掉，等操作完了再开启。

# 临时关闭 binlog 记录（一定要注意修改的仅是会话级的参数，不能加global，否则主从数据会不一致）
mysql> set sql_log_bin=0; 

# 执行变更
mysql> alter table test1 drop index idx_id add index (id,username);

# 临时开启 binlog 记录
mysql> set sql_log_bin=1;

7. 进程管理

• 正在执行的进程

# 查看当前正在运行的进程，可使用 mysql> show full processlist\G 进行格式化输出
mysql> show full processlist;
+----+-----------------+-----------+--------+---------+------+------------------------+-------------------------------------+
| Id | User            | Host      | db     | Command | Time | State                  | Info                                |
+----+-----------------+-----------+--------+---------+------+------------------------+-------------------------------------+
|  5 | event_scheduler | localhost | NULL   | Daemon  |  188 | Waiting on empty queue | NULL                                |
|  8 | root            | localhost | sreyys | Query   |    0 | init                   | show full processlist               |
|  9 | root            | localhost | NULL   | Query   |   33 | User sleep             | select *,sleep(100) from mysql.user |
+----+-----------------+-----------+--------+---------+------+------------------------+-------------------------------------+
3 rows in set (0.00 sec)

# 可以看到 Id 为 9 的 sql 执行了 33 秒，如果不想让其执行，可以使用 kill 命令杀掉该进程。
mysql> kill 9;
Query OK, 0 rows affected (0.00 sec)

# 验证
mysql> show full processlist;
+----+-----------------+-----------+--------+---------+------+------------------------+-------------------------------------+
| Id | User            | Host      | db     | Command | Time | State                  | Info                                |
+----+-----------------+-----------+--------+---------+------+------------------------+-------------------------------------+
|  5 | event_scheduler | localhost | NULL   | Daemon  |  282 | Waiting on empty queue | NULL                                |
|  8 | root            | localhost | sreyys | Query   |    0 | init                   | show full processlist               |
| 11 | root            | localhost | NULL   | Query   |   17 | User sleep             | select *,sleep(100) from mysql.user |
+----+-----------------+-----------+--------+---------+------+------------------------+-------------------------------------+
3 rows in set (0.00 sec)

8. 监视用户详情

• 查看正在运行的连接数信息

mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 15    |        # 表示当前缓存的线程数，这些线程可以被重复使用而不需要重新创建
| Threads_connected | 23    |        # 这个数值指的是打开的连接数
| Threads_created   | 4546  |        # 表示自启动MySQL服务器以来创建的线程数。
| Threads_running   | 5     |        # 表示当前正在运行的线程数，这个数值一般远低于connected数值
+-------------------+-------+
4 rows in set (0.00 sec)

• 查看活跃用户

mysql> select USER , count(*) from information_schema.processlist group by USER;
+-----------------+----------+
| USER            | count(*) |
+-----------------+----------+
| root            |        3 |
| event_scheduler |        1 |
+-----------------+----------+
2 rows in set (0.01 sec)

• 查看活跃 IP

mysql> select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
+-----------+----------+
| ip        | count(*) |
+-----------+----------+
| localhost |        4 |
+-----------+----------+
1 row in set (0.01 sec)

• 查看当前连接时间最长的链接

mysql>  select host,user,time,state,info from information_schema.processlist order by time desc limit 10;

DevOps运维技术栈专注于分享运维、Devops、网络安全以及SRE云原生相关知识，内容包括但不限于自动化运维、云架构、云监控、云安全、AI、AWS以及攻防渗透等。内容多以原创为主，旨在于编写高质量文章。

阅读剩余

作者：admin

链接：https://www.linux66.cn/937.html

文章版权归作者所有，未经允许请勿转载。

THE END