mysql双节点部署_keeplived + mysql双主复制部署 --原创

环境：

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_repobaseurl=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 forkeepalived

global_defs {

notification_email {

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server192.168.200.1smtp_connect_timeout30router_id LVS_DEVEL

vrrp_skip_check_adv_addr

# vrrp_strict

vrrp_garp_interval0vrrp_gna_interval0}

vrrp_instance VI_1 {

state BACKUP

interface ens160

virtual_router_id51priority100advert_int1authentication {

auth_type PASS

auth_pass1111}

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 forkeepalived

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.1smtp_connect_timeout 30router_id LVS_DEVEL

vrrp_skip_check_adv_addr

# vrrp_strict

vrrp_garp_interval 0vrrp_gna_interval 0}

vrrp_instance VI_1 {

state BACKUP

interface ens160

virtual_router_id 51priority 100advert_int 1authentication {

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