Mysql8.4基于GTID主从部署以及故障修复

DBA实战

修改于 2024-09-26 18:51:17

2160

修改于 2024-09-26 18:51:17

文章被收录于专栏：DBA实战

简介

MySQL 8.4是一个稳定和高性能的关系型数据库管理系统，支持各种操作系统平台，包括Linux、Windows和macOS。部署MySQL 8.4通常包括安装、配置、优化和测试等步骤

安装步骤

MySQL 8.0 及以上版本8.4可以通过官方的 RPM 包进行安装和管理。以下是在 CentOS8 系统上部署 MySQL 8.4 的基本步骤：

下载 MySQL 8.0 RPM 包：

访问 MySQL 官方网站下载页面 (https://dev.mysql.com/downloads/repo/yum/)，选择对应你系统版本的 RPM 包。
安装下载的 RPM 包：
[root@idc-mongo55 ~]#  rpm -Uvh https://dev.mysql.com/get/mysql84-community-release-el8-1.noarch.rpm
Retrieving https://dev.mysql.com/get/mysql84-community-release-el8-1.noarch.rpm
warning: /var/tmp/rpm-tmp.N9Kyl1: Header V4 RSA/SHA256 Signature, key ID a8d3785c: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql84-community-release-el8-1  ################################# [100%]
   Warning: native mysql package from platform vendor seems to be enabled.
    Please consider to disable this before installing packages from repo.mysql.com.
    Run: yum module -y disable mysql

##禁用
yum module -y disable mysql

2. 确认 MySQL 8.4 社区版存储库已启用
在禁用原生 MySQL 模块后，确认 MySQL 8.4 社区版存储库已启用。你可以通过以下命令查看可用的存储库：yum repolist all | grep mysql
确保 mysql84-community 存储库已启用。如果没有启用，可以使用以下命令启用：
yum-config-manager --enable mysql-8.4-lts-communit

3. 安装 MySQL 8.4 社区服务器

禁用原生 MySQL 模块并确认 MySQL 8.4 社区版存储库已启用后，尝试安装 MySQL 8.4 社区服务器：

yum install mysql-community-server

4. 修改配置文件

vim /etc/my.cnf

datadir=/jesong/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
##binlog
server_id = 1
binlog_format = row
binlog_row_metadata=full
binlog_row_image=full
max_binlog_size = 512M
log_bin = /jesong/mysql/mysql-bin
skip_name_resolve = 1
 
# GTID #
gtid_mode = ON
enforce_gtid_consistency = 1
 
##innnodb
max_connections = 8000
default-time_zone = '+8:00'
default_storage_engine = InnoDB
sync_binlog = 1
innodb_buffer_pool_size = 24G
innodb_file_per_table = 1
innodb_io_capacity = 12000
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit=1
 
##cache
key_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 32M
table_open_cache = 1024
 
##slowlog
slow_query_log = 1
slow_query_log_file = mysql-slow.log
long_query_time  = 1
 
##隔离级别
transaction_isolation = READ-COMMITTED

##ohter
character-set-server = utf8mb4
mysql_native_password=on
lower_case_table_names = 1
skip_slave_start = 1

授权：
chmod -R 755 /jesong/mysql
chown -R mysql:mysql  mysql
 
6.启动mysql
输入：/bin/systemctl start mysqld.service
/bin/systemctl status mysqld.service

7.显示mysql的随机密码
输入：grep 'temporary password' /var/log/mysqld.log

8. 运行 MySQL 安装安全脚本  (也可使用第9部手动操作，二者选其一)
为了加强 MySQL 的安全性，运行 mysql_secure_installation 脚本：shell > mysql_secure_installation

根据提示完成以下操作：输入临时 root 密码
设置新的 root 密码
移除匿名用户
禁止 root 远程登录
移除测试数据库
重新加载权限表

 
9.修改密码
修改密码：ALTER USER 'root'@'localhost' IDENTIFIED BY 'tMKmf3fdggim!vfdv1';
查看密码策略：SHOW VARIABLES LIKE 'validate_password%';
 修改密码长度：set global validate_password.length=1;（长度）
修改密码等级：set global validate_password.policy=0;（等级）

创建复制账户

mysql> create user 'repl'@'10.28.24.58' IDENTIFIED  WITH mysql_native_password BY '123WQEDDSFfdsf#456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.28.29.58';

由于我的环境主节点是很早就部署的，从节点是新部署的，所以要备份下主库数据导入从库

##基于DTID部署的主从所以开启了set-gtid-purged
mysqldump -h localhost -u root -p --all-databases  --single-transaction  --source-data=2 --set-gtid-purged=ON >1.sql

##备份文件有记录要跳过的GTID
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'd2486368-1999-11ef-991a-fa163ef2f747:1-6';

从节点导入数据

mysql -uroot -p'tMKmf3fdggim!vfdv1' < 1.sql 

##执行跳过的GTID
mysql> SET @@GLOBAL.GTID_PURGED='d2486368-1999-11ef-991a-fa163ef2f747:1-6';
Query OK, 0 rows affected (0.00 sec)

如果主节点都是新部署的，就可以跳过上面的2个步骤，备份数据以及跳过GTID，直接执行下面步骤

从节点执行复制操作

#建立主从关系
mysql> change REPLICATION SOURCE TO SOURCE_host='10.28.29.55',SOURCE_user='repl',SOURCE_password='123WQEDDSFfdsf#456', SOURCE_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start replica;
mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 10.28.29.55
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000003
          Read_Source_Log_Pos: 721
               Relay_Log_File: idc-mongo58-relay-bin.000002
                Relay_Log_Pos: 422
        Relay_Source_Log_File: mysql-bin.000003
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes

如何重新建立主从关系？

##停掉主从关系
mysql> stop replica;
##重置主从配置信息
mysql> reset replica all;
##建立主从关系
mysql> change REPLICATION SOURCE TO SOURCE_host='10.28.29.55',SOURCE_user='repl',SOURCE_password='123WQEDDSFfdsf#456', SOURCE_auto_position=1;
##开启
mysql> start replica;

从节点如何跳过指定GTID?

##比如1032情况，这里只是举了例子，出现这种情况就是主节点执行了删除操作，从节点没有这条数据。一般都会在配置文件配置忽略这个错误码
   Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1032
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'd2486368-1999-11ef-991a-fa163ef2f747:13' at source log mysql-bin.000003, end_log_pos 5616. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 4930
              Relay_Log_Space: 5565
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1032
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'd2486368-1999-11ef-991a-fa163ef2f747:13' at source log mysql-bin.000003, end_log_pos 5616. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 1
                  Source_UUID: d2486368-1999-11ef-991a-fa163ef2f747
             Source_Info_File: mysql.slave_master_info

###修复步骤
mysql> stop replica;
Query OK, 0 rows affected (0.00 sec)
--- 从错误信息中获取，跳过的GTID
mysql> set session gtid_next='d2486368-1999-11ef-991a-fa163ef2f747:13';
Query OK, 0 rows affected (0.00 sec)
--- 写入空事务
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GTID_NEXT='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)
--- 开启主从同步
mysql> start replica;
Query OK, 0 rows affected (0.01 sec)
--- 故障恢复
mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 10.28.29.55
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000003
          Read_Source_Log_Pos: 5647
               Relay_Log_File: idc-mongo58-relay-bin.000003
                Relay_Log_Pos: 462
        Relay_Source_Log_File: mysql-bin.000003
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes

总结

通过以上步骤，可以完成 MySQL 主从复制环境的部署、数据导入、主从关系建立、主从关系重置以及跳过指定 GTID 的操作。这些步骤确保了主从复制的正确性和可靠性，同时也提供了解决复制过程中可能出现的问题的方法。在实际生产环境中，这些操作是维护和管理 MySQL 主从复制系统的重要手段。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-09-25，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql