MySQL 如何使用离线模式维护服务器

离线模式

作为 DBA,最常见的任务之一就是批量处理 MySQL 服务的启停或其他一些活动。在停止 MySQL 服务前,我们可能需要检查是否有活动连接;如果有,我们可能需要把它们全部杀死。通常,我们使用 pt-kill 杀死应用连接或使用 SELECT 语句查询准备杀死语句。例如:

pt-kill --host=192.168.11.11 --user=percona -p --sentinel /tmp/pt-kill.sentinel2 --pid /tmp/pt-kill.pid --victims all --match-command 'Query' --ignore-user 'pmm|rdsadmin|system_user|percona' --busy-time 10 --verbose --print --kill

select concat('kill ',id,';') from information_schema.processlist where user='app_user';

MySQL 有一个名为 offline_mode 的变量来将服务器设置为维护模式。设置此选项后,它会立即断开所有不具有 SYSTEM_VARIABLES_ADMIN 和 CONNECTION_ADMIN 权限的客户端连接,并且不允许新连接,除非用户拥有这些权限。如果您手动终止连接或使用 pt-kill,则无法避免新连接的创建。但通过使用这种模式,我们可以避免新的连接。这是一个全局动态变量,我们可以在服务器运行时设置此模式。

要启用 offline_mode,用户帐户必须具有 SYSTEM_VARIABLES_ADMIN* 权限和 CONNECTION_ADMIN 权限(或已弃用的 SUPER 权限,它涵盖了这两个权限)。CONNECTION_ADMIN 从 MySQL 8.0.31 开始是必需的,并建议在所有版本中使用,以防止意外锁定。让我们测试一下。

要对此进行测试,请创建一个新用户 app_user,该用户仅具有 DDL/DML 权限。

mysql> create user app_user identified by 'App@!234TEst';
Query OK, 0 rows affected (0.20 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP , REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE,CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE on *.* to app_user;
Query OK, 0 rows affected (0.00 sec)

使用 aap_user 用户启动 sysbench 工具。

[root@centos12 vagrant]# sysbench /usr/share/sysbench/oltp_read_write.lua --threads=10 --time=100  --mysql-db=sysbench --mysql-user=app_user --mysql-password='App@!234TEst' run

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Initializing random number generator from current time
Initializing worker threads...
mysql> show processlist;
+----+-----------------+---------------------+----------+-------------+------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+---------------------+----------+-------------+------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 2151 | Waiting for next activation | NULL | 2151034 | 0 | 0 |
| 9 | bhuvan | 192.168.33.11:50642 | NULL | Binlog Dump | 2102 | Source has sent all binlog to replica; waiting for more updates | NULL | 2102317 | 0 | 0 |
| 14 | bhuvan | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 |
| 20 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 11 | 0 | 0 |
| 21 | app_user | localhost | sysbench | Execute | 0 | updating | DELETE FROM sbtest1 WHERE id=5000 | 6 | 0 | 0 |
| 23 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 8 | 0 | 0 |
| 24 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 18 | 0 | 0 |
| 25 | app_user | localhost | sysbench | Execute | 0 | updating | UPDATE sbtest1 SET c='99153469917-25523144931-18125321038-96151238215-88445737418-14906501975-136014 | 13 | 0 | 0 |
| 27 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 7 | 0 | 0 |
| 28 | app_user | localhost | sysbench | Execute | 0 | statistics | SELECT c FROM sbtest1 WHERE id=5003 | 0 | 0 | 0 |
| 29 | app_user | localhost | sysbench | Execute | 0 | updating | UPDATE sbtest1 SET c='84180675456-88426921120-90373546373-84823361786-77912396694-08592771856-912331 | 13 | 0 | 0 |
+----+-----------------+---------------------+----------+-------------+------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
13 rows in set (0.00 sec)

当 sysbench 运行时,设置 offline_mode=ON,来自 sysbech with Sysbench”) 的所有连接都将被终止。您将在 sysbench 中看到错误。

mysql> select @@offline_mode;
+----------------+
| @@offline_mode |
+----------------+
| 0 |
+----------------+
1 row in set (0.15 sec)
mysql> set global offline_mode=1;
Query OK, 0 rows affected (0.15 sec)
mysql> show processlist;
+----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 2178 | Waiting for next activation | NULL | 2178008 | 0 | 0 |
| 9 | bhuvan | 192.168.33.11:50642 | NULL | Binlog Dump | 2129 | Source has sent all binlog to replica; waiting for more updates | NULL | 2129291 | 0 | 0 |
| 14 | bhuvan | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 |
+----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+
3 rows in set (0.01 sec)

如果您在 offline_mode=1 时尝试使用 app_user 连接数据库,它将不允许连接并收到一条错误消息,表明服务器当前处于离线模式。这个 offline_mode 不会影响复制。可以看到上面的 processlist 日志,当我们设置 offline_mode=1 时,复制线程并没有断开。要禁用 offline_mode,请将值设置为 0。

mysql> set global offline_mode=0;
Query OK, 0 rows affected (0.00 sec)

结论

offline_mode 是将服务器置于维护模式的一个不错的选择。只需确保应用程序用户没有管理员权限,只有管理员有。我们可以在以下情况下使用 offline_mode

  • 在将数据库服务器取出进行维护或其相关活动之前,请在配置文件中进行更改并保持此模式,直到所有活动完成。
  • 在从服务器进行备份时,我们可以设置这个 offline_mode,以避免服务器的负载,使备份速度更快。
  • 如果由于大量查询而导致副本上出现大量复制,我们可以设置此模式,直到复制与主服务器同步。
  • 当您想立即终止所有应用程序连接时。

文章来源:https://www.cnaaa.net,转载请注明出处:https://www.cnaaa.net/archives/9882

(0)
凯影的头像凯影
上一篇 2023年10月20日 下午4:41
下一篇 2023年10月23日 下午5:18

相关推荐

  • 如何清理windows server 2008 R2 中winsxs文件夹

    最近发现公司的一台服务器C盘只剩5G左右的空间,经过仔细查看发现C:\windows\winsxs 目录占了20多G的容量。此文件夹是更新系统补丁产生的不能直接删除,所以只能通过cleanmgr.exe工具进行清理C盘。 以下方法亲自验证清理成功: 一、在Windows Server 2008 R2,右击C盘,查看属性,发现界面中没有清理C盘的工具按钮,那如…

    2023年4月3日
    25800
  • 如何在 Ubuntu 22.04 上启用 BBR?

    由于购买的VPS或者主机数据中心都在国外,经常会发生网络拥堵的情况,我们可以通过启用BBR来优化。 在Ubuntu系统上启用BBR可以提升网络连接速度,优化网络拥堵情况。 BBR代表瓶颈带宽,RTT是拥塞控制系统。 您可以在 Linux 桌面上启用 TCP BBR 以改善整体网上冲浪体验。 默认情况下,Linux 使用 Reno 和 CUBIC 拥塞控制算法…

    2024年3月12日
    13500
  • nginx配置负载均衡–实战项目(适用于轮询、加权轮询、ip_hash)

    两台服务器: 业务需求:有两台服务器,一台供于线上使用(172.16.10.10),一台供于研发使用(172.16.10.20) 如果线上的服务器挂了,需要将所有请求转移到研发的机器上,以确保线上服务正常使用; 正常时间还是线上使用线上的服务器,研发用研发的服务器;只有在线上服务器挂了的时候才会使用备用的研发服务器; 需求实现:使用nginx负载均衡来实现;…

    2023年12月25日
    8100
  • CDN 加速 – 隐藏真实 IP – 复活 IP

    CDN 一词相信很多朋友都不会陌生,网上也经常会看到相关报道。或许大部分人都知道 CDN 加速可以提升网站的打开速度及用户下载资源的速度,而同时也有不少朋友还不清楚 CDN 是什么?有什么用途?它是如何实现加速的呢?下面为大家整理了一些通俗易懂的知识点。 CDN 是什么 CDN 的全称:CONTENT DELIVERY NETWORK,即 内容分发…

    2023年11月9日
    9500
  • 查看调整cpu频率及模式

    使用cpufrequtils查看调整cpu频率及模式 cpufrequtils是一个查看和修改CPU频率GHz的工具有些物理服务器使用默认频率进行运行,这时可以使用该工具进行就该CPU的核心频率 安装: 使用: cpufreq-info 查看当前cpu状态 参数: 参数 值 说明 -c CPU序号 查看所指定cpu状态 -f 查看cpu当前频率 -l 查看c…

    2023年11月3日
    23700

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

在线咨询: QQ交谈

邮件:712342017@qq.com

工作时间:周一至周五,8:30-17:30,节假日休息

关注微信