注意:建议在系统上线前做好主从热备配置,否则后期做主从热备需要保证主库与从库表结构一直,这就需要与用户协调时间把生产机停机做主从热备,踩坑:我们就是上线3年多后才做的主从热备,停机时间大概要4-5小时;要保证一次配置成功。
1.系统服务器停服,保证主库数据不再变化;(预计时长3分钟)
2.对192.168.6.11进行快照备份;(预计时长2分钟)
3.导出主库数据,导入从库;(预计时长150分钟)
4.配置主数据库信息;(预计时长5分钟)
a)vim /etc/hostname
VIP-DATABASE
b)vim /etc/hosts
192.168.6.11 m VIP-DATABASE
192.168.6.13 s VIP-DATA-BACKUP
c)vim /etc/selinux/config
修改前:SELINUX=enforcing
修改后:SELINUX=permissive
d)重启服务器reboot
e)查看开放的端口列表,保证3306端口开放
firewall-cmd --zone=public --list-ports
开放防火墙端口3306
firewall-cmd --zone=public --add-port=3306/tcp --permanent
注意:开放端口后要重启防火墙生效
重启防火墙
systemctl restart firewalld
f)vim /etc/my.cnf
[mysqld]
log-bin=/var/log/mysql/master-bin // 启用二进制日志;
server-id= 20240001 //设置一个在当前集群中惟一的server-id(主/从服务器的ID不能重复);
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
cd /var/log
mkdir mysql
chown -R mysql:mysql mysql/
##设置mariadb开机启动,并启动mariadb
systemctl enable mariadb.service
g)mysql -u root -p
ect_DsrmSS@123
#创建一个有复制权限(REPLICATION SLAVE, REPLICATION CLIENT)账号
grant replication client,replication slave on *.* to 'repluser'@'192.168.6.13' identified by 'ect_DsrmSS@123';
#设置完成一定要刷新,不然不生效
flush privileges
MariaDB [(none)]> exit
Bye
[root@m ~]# vim /etc/my.cnf
[mysqld]
log-bin=/var/log/mysql/master-bin
server-id= 20240001
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
sync_binlog=1 //#同步二进制日志,事务提交则马上将内存中的二进制日志同步到磁盘
binlog-do-db=ectsz
binlog-do-db=dl_srm
h)systemctl restart mariadb.service
i)mysql -u root -p
ect_DsrmSS@123
#查看主库状态
show master status \G;
5.配置从数据库信息;(预计时长5分钟)
slave节点配置 /安装mariadb-server之前的步骤操作说明见master节点配置
a)vim /etc/hostname
VIP-DATA-BACKUP
b)vim /etc/hosts
192.168.6.11 m VIP-DATABASE
192.168.6.13 s VIP-DATA-BACKUP
c)vim /etc/selinux/config
修改后:SELINUX=permissive
d)重启服务器reboot
e)查看开放的端口列表,保证3306端口开放
firewall-cmd --zone=public --list-ports
开放防火墙端口3306
firewall-cmd --zone=public --add-port=3306/tcp --permanent
注意:开放端口后要重启防火墙生效
重启防火墙
systemctl restart firewalld
f)vim /etc/my.cnf
[mysqld]
lower_case_table_names = 1 #设置表名不区分大小写
relay-log = relay-bin # 启用中继日志;
server-id = 20240002
read-only = on #关闭从服务器写入功能
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
replicate-do-db=ectsz
replicate-do-db=dl_srm
g)mysql -u root -p
#CHANGE MASTER TO MASTER_HOST='192.168.6.11',MASTER_USER='repluser',MASTER_PASSWORD='gzt041057';
change master to master_host='192.168.6.11',master_user='repluser',master_password='ect_DsrmSS@123',master_port=3306,master_log_file='master-bin.000010',master_log_pos=23420;
show slave status \G;
start slave;
stop slave;#先停掉slave
set global sql_slave_skip_counter=1; #跳过错误步数,后面步数可变
start slave; #在启动slave
show slave status\G;#查看同步状态
reset slave;
6.验证主从热备情况及重启SRM系统、标签管理系统;(预计时长15分钟)