当前位置： 首页 > article >正文

开源数据库 - mysql - mysql-server-8.4(gtid主主同步+ keepalived热切换)部署方案

article 2024/11/14 22:03:38

前置条件

假设主从信息

mysqlhostport
主192.168.1.13306
从192.168.1.23306
vip192.168.1.3

部署流程

导出测试环境表结构与数据

使用mysqldump

./mysqldump -ulzzc -p -S /tmp/mysql3306.sock --single-transaction --database lzzc > databaseLZZCxxxx.sql

查看gtid号

head -n 40 databaseLZZCxxxx.sql

安装mysql

下载mysql-server-8.4.0.tar.xz
MySQL :: Download MySQL Community Server (Archived Versions)

将安装包放置在/usr/local/ 目录下解压，并将解压后目录改名为mysql

添加my.cnf文件

cd /usr/local/mysql/
mkdir etc
cd etc
vi my.cnf

从库

[mysqld]
default-time-zone='+08:00'
bind-address=0.0.0.0
port=3306
user=mysql
socket=/tmp/mysql3306.sock
pid_file=/mysql/data/my.pid
basedir=/usr/local/mysql
datadir=/mysql/data
#character config
character_set_server=utf8mb4
explicit_defaults_for_timestamp=true
symbolic-links=0
log-error=/mysql/logs/mysql3306.err
log_bin = /mysql/binlog/mysql-bin
relay_log = /mysql/relaylog/relay-bin
slow_query_log_file = /mysql/logs/slow.log
slow_query_log=on
server-id=6 # 两个库的server-id需要不同
binlog_format=row
#innodb settings
innodb_buffer_pool_size=4G # cache_buffer大小
#skip-grant-tables
gtid-mode=on
enforce-gtid-consistency=true
log-replica-updates=ON
lower_case_table_names=1
interactive_timeout=28800000
wait_timeout=28800000
max_connections=1000
innodb_log_group_home_dir=/mysql/data
binlog_expire_logs_seconds=2592000 # 30天时间
[mysql]
socket = /tmp/mysql3306.sock

在根目录下创建mysql目录存放mysql数据与日志等，在/mysql中创建data,binlog ,logs,relaylog目录

系统创建mysql 用户组和用户

groupadd mysql
useradd -r -g mysql mysql

给/usr/local/mysql和/mysql目录赋权

chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /mysql

初始化mysql应用

cd /usr/local/mysql/bin
./mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --initialize-insecure

等待初始化，查看/mysql/logs/mysql3306.err如果没有错误报错则继续执行

启动mysql

./mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf &

查看mysql进程

ps -ef|grep mysql

mysql创建用户赋权

主/从库

登录root

./mysql -uroot -p -S /tmp/mysql3306.sock

进入mysql命令行，修改root密码

alter user 'root'@'localhost' identified by 'xxxxxx';

创建同步用户并赋权

create user 'repl'@'%' identified by 'replpassword';
grant replication slave on *.* to "repl"@"%";
flush privileges;

从库重置gtid、导入主库数据

重置gtid

show variables like "%gtid%";

输出类似如下图（正常gtid中会在gtid_executed中显示为本机的gtid，gtid_purged为导入的主库gtid）
![[Pasted image 20241111105605.png]]

reset binary logs and gtids; # 重置gtid

导入主库数据

./mysql -uroot -p -S /tmp/mysql3306.sock <databaseLZZCxxxx.sql

再次查看gtid，此时gtid_purged应该与主库的gtid一致

开启从库同步进程

设置同步指向

change replication source to source_host="192.168.1.1",source_port=3306,source_user="repl",source_password="replpassword",source_auto_position=1,get_source_public_key=1;

开启replica进程

start replica;

查看replica状态,输出类似：（主要查看Replica_IO_Running和Replica_SQL_Running以及Replica_SQL_Running_State三个值，前两个都是yes后面一个没有警告和报错就可以）

show replica status\G;

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.1.1
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000006
          Read_Source_Log_Pos: 237
               Relay_Log_File: relay-bin.000021
                Relay_Log_Pos: 321
        Relay_Source_Log_File: mysql-bin.000006
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 237
              Relay_Log_Space: 782
              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: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 7
                  Source_UUID: 292e4a07-9e71-11ef-88a8-001a4a1601f1
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 292e4a07-9e71-11ef-88a8-001a4a1601f1:1-3
            Executed_Gtid_Set: 292e4a07-9e71-11ef-88a8-001a4a1601f1:1-3,
92c1608c-9cdb-11ef-a01e-001a4a1601fe:1-343,
eb66bcc8-9e70-11ef-858d-001a4a1601fe:1-4
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 1
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

开启主库同步进程

设置同步指向

change replication source to source_host="192.168.1.2",source_port=3306,source_user="repl",source_password="replpassword",source_auto_position=1,get_source_public_key=1;

开启replica进程

start replica;

查看replica状态,输出类似：（主要查看Replica_IO_Running和Replica_SQL_Running以及Replica_SQL_Running_State三个值，前两个都是yes后面一个没有警告和报错就可以）

show replica status\G;

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.1.1
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000006
          Read_Source_Log_Pos: 237
               Relay_Log_File: relay-bin.000021
                Relay_Log_Pos: 321
        Relay_Source_Log_File: mysql-bin.000006
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 237
              Relay_Log_Space: 782
              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: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 7
                  Source_UUID: 292e4a07-9e71-11ef-88a8-001a4a1601f1
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 292e4a07-9e71-11ef-88a8-001a4a1601f1:1-3
            Executed_Gtid_Set: 292e4a07-9e71-11ef-88a8-001a4a1601f1:1-3,
92c1608c-9cdb-11ef-a01e-001a4a1601fe:1-343,
eb66bcc8-9e70-11ef-858d-001a4a1601fe:1-4
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 1
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

设置keepalived

主备服务器安装keepalived，可以使用rpm包安装或者源码编译

主库编辑keepalived配置文件keepalived.conf

# 检查mysql服务是否存活的脚本
vrrp_script chk_mysql {
    script "/usr/bin/killall -0 mysqld"
}
# vrrp配置虚IP
vrrp_instance VI_1 {
    # 状态：MASTER  另外一台机器为BACKUP
    state MASTER
    # 绑定的网卡
    interface eth0
    # 虚拟路由id  两台机器需保持一致
    virtual_router_id 51
    # 优先级 MASTER的值要大于BACKUP
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    # 虚拟IP地址 两台keepalived需要一致
    virtual_ipaddress {
        192.168.1.3
    }
    # 检查脚本 vrrp_script的名字
    track_script {
        chk_mysql
    }
}

从库编辑keepalived配置文件keepalived.conf

# 检查mysql服务是否存活的脚本
vrrp_script chk_mysql {
    script "/usr/bin/killall -0 mysqld"
}
# vrrp配置虚IP
vrrp_instance VI_1 {
    # 状态：MASTER  另外一台机器为BACKUP
    state BACKUP
    # 绑定的网卡
    interface eth0
    # 虚拟路由id  两台机器需保持一致
    virtual_router_id 51
    # 优先级 MASTER的值要大于BACKUP
    priority 101
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    # 虚拟IP地址 两台keepalived需要一致
    virtual_ipaddress {
        192.168.1.3
    }
    # 检查脚本 vrrp_script的名字
    track_script {
        chk_mysql
    }
}

主备库开启keepalived

systemctl start keepalived

剩余工作

自行测试主主同步、热切换


http://www.kler.cn/a/390227.html

相关文章：

  • 从0开始学习Linux——文件管理
  • 俏美韵从心出发，与女性一道为健康生活贡献力量
  • DOM 规范 — MutationObserver 接口
  • 微信小程序=》基础=》常见问题=》性能总结
  • 10款PDF翻译工具的探索之旅：我的使用经历与工具特色！！
  • 少儿学习Scratch编程的好处和坏处
  • Lua进阶用法之Lua和C的接口设计
  • uniapp实现H5和微信小程序获取当前位置（腾讯地图）
  • 确定图像的熵和各向异性 Halcon entropy_gray 解析
  • Spring资源加载模块，原来XML就这，活该被注解踩在脚下 手写Spring第六篇了
  • 【vue】封装一个可随时暂停启动无需担心副作用的定时器
  • AI - 人工智能；Open WebUI；Lobe Chat；Ollama
  • git clone相关问题和bug记录
  • 本地保存mysql凭据实现免密登录mysql
  • Ubuntu 18.04 安装Fast-planner
  • Ecmascript（ES）标准
  • 【新人系列】Python 入门（九）：数据结构 - 中
  • 深入探讨Vue项目中缺少明显入口文件的原因及解决策略
  • Spring Boot框架：计算机课程与工程认证的桥梁
  • 【宝藏】浏览器端的模块化问题（1）
  • 浅谈Spring MVC
  • middleware中间件概述
  • Django博客网站上线前准备事项
  • 昇思大模型平台打卡体验活动：项目2基于MindSpore通过GPT实现情感分类
  • PHP和Python脚本的性能监测方案
  • 游戏中的设计模式及杂项