开源数据库 - mysql - mysql-server-8.4(gtid主主同步+ keepalived热切换)
前置条件假设主从信息
mysqlhostport主192.168.1.13306从192.168.1.23306vip192.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
从库
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天时间
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）
![]
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
剩余工作
自行测试主主同步、热切换
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]