当前位置:Gxlcms > 数据库问题 > keeplived + mysql双主复制部署 --原创

keeplived + mysql双主复制部署 --原创

时间:2021-07-01 10:21:17 帮助过:5人阅读

master 1: 192.168.100.10  oracle  linux 7.4  mysql 5.7.1
master 2: 192.168.100.11 oracle  linux 7.4  mysql 5.7.1
keepalived VIP 192.168.100.12    

 

 

 

 

配置本地yum仓库

vim /etc/yum.repo.d/public-yum-ol7.repo

[yum_repo] name=yum_repo baseurl=file:///share/repo gpgcheck=0 enabled=1

 

 

安装mysql

检查是否已经安装

rpm -qa | grep mysql

 

 

1.mysql安装包安装顺序 安装包有依赖关系必须按照顺序安装

  1. mysql-community-common-5.7.21-1.el7.x86_64.rpm
  2. mysql-community-libs-5.7.21-1.el7.x86_64.rpm
  3. mysql-community-client-5.7.21-1.el7.x86_64.rpm
  4. mysql-community-server-5.7.21-1.el7.x86_64.rpm

2.初始化mysql 在初始化完成后会生成一个过期的root密码,登录mysql后需要立刻更改,否则无法使用任何命令

rpm -qa | grep mysql

 

3.启动mysql

systemctl start mysqld

 

4.查看mysql root密码

cat /var/log/mysqld.log | grep password

 

5.mysql安全设置 (可选项,可以重置root密码)
  

mysql_secure_installation

 

6.登录mysql
  

mysql -u root -p

 

7.修改root密码
  

alter user root@% identified by Len#qwer;

 

8.设置root账户永不过期 (可选项)
  

alter user root@% password expire never;

 

 

 

配置双主

修改mysql配置文件c

master1节点

vim /etc/my.cnf

[mysqld]    
log-bin=mysql-bin     #开启二进制日志
server-id=1       #设置server-id 主备库必须不同
log_slave_updates=1

 

 

master2节点

vim /etc/my.cnf

[mysqld]    
log-bin=mysql-bin        #开启二进制日志
server-id=2          #设置server-id 主备库必须不同
log_slave_updates=1

 

 

2.重启mysql,
master1节点

systemctl restart mysqld

 

master2节点

systemctl restart mysqld

 

 

 

创建同步用户
master1节点

CREATE USER repl@192.168.100.11 IDENTIFIED BY Phjj#qwer;
GRANT REPLICATION SLAVE ON *.* TO repl@192.168.100.11;

 


master2节点

CREATE USER repl@192.168.100.10 IDENTIFIED BY Phjj#qwer;
GRANT REPLICATION SLAVE ON *.* TO repl@192.168.100.10;

 

查看两个节点的binlog文件名称和post位置,用于配置复制

 

master1节点

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      619 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

master2节点

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1175 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

 

master1节点

start slave;

 

master2节点

start slave;

 

配置复制

master1节点

CHANGE MASTER TO
MASTER_HOST=192.168.100.11,
MASTER_USER=repl,
MASTER_PASSWORD=Phjj#qwer,
MASTER_LOG_FILE=mysql-bin.000002,
MASTER_LOG_POS=1175;

 

master2节点

CHANGE MASTER TO
MASTER_HOST=192.168.100.10,
MASTER_USER=repl,
MASTER_PASSWORD=Phjj#qwer,
MASTER_LOG_FILE=mysql-bin.000004,
MASTER_LOG_POS=619;

 

master1启动复制

start slave;

 

master2启动复制

start slave;

 

查看复制状态 Slave_IO_Running,Slave_SQL_Running 状态必须为running才为正常

mysql> show slave status\G;


Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 

安装Keepalived

yum install -y keepalived

 

 

配置keepalived

master1节点

vim  /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
# vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}

vrrp_instance VI_1 {
state BACKUP
interface ens160
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.100.12/24
}
}

virtual_server 192.168.100.12 3306 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP


real_server 192.168.100.10 3306 {
weight 3
notify_down /etc/keepalived/kill_keepalived.sh

TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306

}
}
}

 

master2节点

vim  /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc    
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
# vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}

vrrp_instance VI_1 {
state BACKUP
interface ens160
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.100.12/24
}
}

virtual_server 192.168.100.12 3306 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP


real_server 192.168.100.11 3306 {
weight 3
notify_down /etc/keepalived/kill_keepalived.sh

TCP_CHECK { 
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306

}
}
}

 

配置关闭keepalived脚本

vim /etc/keepalived/kill_keepalived.sh

#!/bin/bash
pkill keepalived

 

添加执行权限给 关闭keepalived脚本

chmod +x /etc/keepalived/kill_keepalived.sh

 

查看keepalived VIP地址

ip addr 

 

 

UUID相同导致mysql无法进行复制

故障现象

vim /var/log/mysqld.log

2018-02-12T02:13:14.499315Z 7 [Note] Slave SQL thread for channel ‘‘ initialized, starting replication in log mysql-bin.000001 at position 619, relay log ./ajiamysql1-relay-bin.000001 position: 4
2018-02-12T02:13:14.499839Z 6 [Note] Slave I/O thread for channel ‘‘: connected to master repl@192.168.100.11:3306,replication started in log mysql-bin.000001 at position 619
2018-02-12T02:13:14.501900Z 6 [ERROR] Slave I/O for channel ‘‘: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593

 

故障原因:直接复制已经安装好mysql的虚拟机导致mysql  UUID相同无法进行复制连接    注意:UUID和server_id一样不能相同

 

故障解决:  修改mysql UUID

 

[root@ajiamysql1 mysql]# vim /var/lib/mysql/auto.cnf

[auto]
server-uuid=6fe77f0a-eae1-11e7-805e-005056b309100

 

keeplived + mysql双主复制部署 --原创

标签:mys   ide   set   out   algo   notify   完成   x86   pass   

人气教程排行