利用Keepalived构建MySQL主主复制

一时失言乱红尘 2022-08-19 15:06 231阅读 0赞

<table style="margin-bottom:10px; border-collapse:collapse; color:rgb(0,0,0); font-family:sans-serif; font-size:15.555556297302246px"> 
 <tbody> 
 <tr> 
 <td style="padding:5px 7px; border:0.5pt solid rgb(221,221,221); font-size:14px; word-wrap:break-word; line-height:1.6"> 主机名 </td> 
 <td style="padding:5px 7px; border-width:0.5pt 0.5pt 0.5pt medium; border-style:solid solid solid none; border-color:rgb(221,221,221); font-size:14px; word-wrap:break-word; line-height:1.6"> IP地址 </td> 
 <td style="padding:5px 7px; border-width:0.5pt 0.5pt 0.5pt medium; border-style:solid solid solid none; border-color:rgb(221,221,221); font-size:14px; word-wrap:break-word; line-height:1.6"> 虚拟ip（VIP） </td> 
 </tr> 
 <tr> 
 <td style="padding:5px 7px; border-width:medium 0.5pt 0.5pt; border-style:none solid solid; border-color:rgb(221,221,221); font-size:14px; word-wrap:break-word; line-height:1.6"> Mysql01 </td> 
 <td style="padding:5px 7px; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-color:rgb(221,221,221); font-size:14px; word-wrap:break-word; line-height:1.6"> 192.168.47.149 </td> 
 <td style="padding:5px 7px; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-color:rgb(221,221,221); font-size:14px; word-wrap:break-word; line-height:1.6"> 192.168.47.140 </td> 
 </tr> 
 <tr> 
 <td style="padding:5px 7px; border-width:medium 0.5pt 0.5pt; border-style:none solid solid; border-color:rgb(221,221,221); font-size:14px; word-wrap:break-word; line-height:1.6"> Mysql02 </td> 
 <td style="padding:5px 7px; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-color:rgb(221,221,221); font-size:14px; word-wrap:break-word; line-height:1.6"> 192.168.47.148 </td> 
 <td style="padding:5px 7px; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-color:rgb(221,221,221); font-size:14px; word-wrap:break-word; line-height:1.6"> 192.168.47.140 </td> 
 </tr> 
 </tbody> 
</table>

**两台数据库上面设置相应的host文件**

vim /etc/hosts  
127.0.0.1 Mysql01  
localhost Mysql01  
192.168.47.149 Mysql01  
192.168.47.148 Mysql02

Keepalived的安装

** 3.1 \#yum install keepalived –y**

![141106083015331.png][]

**                                            **** ****3.2 Mysql01进行参数文件的修改**

**cat /etc/keepalived/keepalived.conf**

\#! Configuration File for keepalived

global\_defs \{

router\_id Mysql01 \#修改为自己的主机名

**\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#第一部分\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#**

vrrp\_instance VI\_1 \{

state BACKUP            \#都修改成BACKUP

interface eth0

virtual\_router\_id 60    \#默认51 主从都修改为60

priority 100            \#优先级（1-254之间），另一台改为90，备用节点必须比主节点优先级低。

advert\_int 1

nopreempt                \#不抢占资源，意思就是它活了之后也不会再把主抢回来

authentication \{

\#设置验证信息，两个节点必须一致

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

192.168.47.140

**\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#第二部分\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#**

virtual\_server 192.168.47.140 3306 \{

delay\_loop 6

lb\_algo wrr            \#LVS算法

lb\_kind DR                \#LVS模式

nat\_mask 255.255.255.0

persistence\_timeout 50 \#会话保持时间

protocol TCP

real\_server 192.168.47.149 3306 \{

weight 1

notify\_down /usr/local/script/mysql.sh \#检测到服务down后执行的脚本

TCP\_CHECK \{

connect\_timeout 10    \#连接超时时间

nb\_get\_retry    3    \#重连次数

connect\_port 3306    \#健康检查端口

**                                          **** ****3.3 Mysql02进行参数文件的修改**

cat /etc/keepalived/keepalived.conf

\#! Configuration File for keepalived

global\_defs \{

router\_id Mysql02 \#修改为自己的主机名

**\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#第一部分\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#**

vrrp\_instance VI\_1 \{

state BACKUP \#都修改成BACKUP

interface eth0

virtual\_router\_id 60 \#默认51 主从都修改为60

priority 80 \#在mysql-ha1上LVS上修改成100

advert\_int 1

authentication \{

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

192.168.47.140

**\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#第二部分\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#**

virtual\_server 192.168.47.140 3306 \{

delay\_loop 6

lb\_algo wrr

lb\_kind DR

nat\_mask 255.255.255.0

persistence\_timeout 50

protocol TCP

real\_server 192.168.47.140 3306 \{

weight 1

notify\_down /usr/local/script/mysql.sh \#进行数据库状态的判断

TCP\_CHECK \{

connect\_timeout 10

nb\_get\_retry 3

connect\_port 3306

参数说明：

virtual\_ipaddress：虚拟IP地址（VIP）

interface ：网卡设备名称

virtual\_server VIP：及Mysql服务端口

real\_server ：真实服务器地址

**3.4 分别在两台mysql数据库上面创建以下脚本，用于判断数据库的状态**

vim /usr/local/script/mysql.sh

\#!/bin/bash

/etc/init.d/keepalived stop

/etc/init.d/keepalived start

3.5 keepalived的常用脚本

启动keepalived服务：service keepalived start

查看keepalived服务：service keepalived status

停止keepalived服务：service keepalived stop

3.6 进行测试

a) 使用PS命令检测服务运行状态

Ps -ef|grep keepalived

Ps -ef|grep mysql

b) 查看系统日志，确定keepalived运行正确

less /var/log/message

tail -f /var/log/messages

c) ip a查看keepalived的运行情况

![141106083015332.png][]

**注意事项：先启动mysql后启动keepalived。**

MySQL-VIP：192.168.1.90

MySQL-master1：192.168.1.91

MySQL-master2：192.168.1.92

OS版本：CentOS 5.4

MySQL版本：5.0.89

Keepalived版本：1.1.20

**一、MySQL master-master配置**

\[root@localhost ~\]\# mysql -uroot

Welcome to the MySQL monitor.  Commands end with ; or \\g.

Your MySQL connection id is 4

Server version: 5.0.77 Source distribution

Type 'help;' or '\\h' for help. Type '\\c' toclear the buffer.

mysql> GRANT ALL PRIVILEGES ON \*.\* TO root@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> use mysql;

Reading table information for completion oftable and column names

You can turn off this feature to get aquicker startup with -A

Database changed

mysql> update user setPassword=password('bee') where user='root';

Query OK, 4 rows affected (0.01 sec)

Rows matched: 4  Changed: 4 Warnings: 0

mysql> show  master status;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| MySQL-bin.000001 |      328 |              |                  |

\+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

**1、修改MySQL配置文件**

\[mysqld\]

log-bin=MySQL-bin

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

server-id = 2

replicate-same-server-id = 0

auto-increment-increment = 2

auto-increment-offset = 2

replicate-ignore-db=test

replicate-ignore-db=mysql

slave-skip-errors=all

\# Default to using old password format forcompatibility with mysql 3.x

\# clients (those using the mysqlclient10compatibility package).

old\_passwords=1

\# Disabling symbolic-links is recommended toprevent assorted security risks;

\# to do so, uncomment this line:

\# symbolic-links=0

\[replication\]

master-host=192.168.1.91

master-user=replication

master-password=replication

master-port=3306

master-connect-retry=60

\[mysqld\_safe\]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

**两台MySQL均如要开启binlog日志功能**，

**开启方法：在MySQL配置文件\[MySQLd\]段中加上log-bin=MySQL-bin选项**

两台MySQL的server-ID不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可

**2、将192.168.1.201设为192.168.1.202的主服务器**

在192.168.1.201上新建授权用户

MySQL**>** **grant replication slave on \*.\* to 'replication'@'%' identified by 'replication'; **

Query OK, 0 rows affected (0.00 sec)

MySQL**>** **show master status;  **

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| MySQL-bin.000003 |      374 |              |                  |

\+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

**在192.168.1.202上将192.168.1.201设为自己的主服务器**

MySQL**>** change master to master\_host='192.168.1.201',master\_user='replication',master\_password='replication',master\_log\_file='MySQL-bin.000003',master\_log\_pos=374;

Query OK, 0 rows affected (0.05 sec)

MySQL**>** start slave;

Query OK, 0 rows affected (0.00 sec)

MySQL**>** show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 192.168.1.201

Master\_User: replication

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: MySQL-bin.000003

Read\_Master\_Log\_Pos: 374

Relay\_Log\_File: MySQL-master2-relay-bin.000002

Relay\_Log\_Pos: 235

Relay\_Master\_Log\_File: MySQL-bin.000003

Slave\_IO\_Running: Yes

Slave\_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\_Master\_Log\_Pos: 374

Relay\_Log\_Space: 235

Until\_Condition: None

Until\_Log\_File:

Until\_Log\_Pos: 0

Master\_SSL\_Allowed: No

Master\_SSL\_CA\_File:

Master\_SSL\_CA\_Path:

Master\_SSL\_Cert:

Master\_SSL\_Cipher:

Master\_SSL\_Key:

Seconds\_Behind\_Master: 0

1 row in set (0.00 sec)

**3、将192.168.1.202设为192.168.1.201的主服务器**

在192.168.1.202上新建授权用户

MySQL> **grant** replication slave **on** \*.\* **to** 'replication'@'%' identified **by** 'replication';

Query OK, 0 **rows** affected (0.00 sec)

MySQL> show master status;

\+\------------------+----------+--------------+------------------+

\+\------------------+----------+--------------+------------------+

| MySQL-bin.000003 |      374 |              |                  |

\+\------------------+----------+--------------+------------------+

1 row in **set** (0.00 sec)

在192.168.1.201上，将192.168.1.202设为自己的主服务器

MySQL> change master **to** master\_host='192.168.1.202',master\_user='replication',master\_password='replication',master\_log\_file='MySQL-bin.000003',master\_log\_pos=374;

Query OK, 0 **rows** affected (0.05 sec)

MySQL> start slave;

Query OK, 0 **rows** affected (0.00 sec)

MySQL> show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting **for** master **to** send event

Master\_Host: 192.168.1.202

Master\_User: replication

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: MySQL-bin.000003

Read\_Master\_Log\_Pos: 374

Relay\_Log\_File: MySQL-master1-relay-bin.000002

Relay\_Log\_Pos: 235

Relay\_Master\_Log\_File: MySQL-bin.000003

Slave\_IO\_Running: Yes

Slave\_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\_Master\_Log\_Pos: 374

Relay\_Log\_Space: 235

Until\_Condition: None

Until\_Log\_File:

Until\_Log\_Pos: 0

Master\_SSL\_Allowed: **No**

Master\_SSL\_CA\_File:

Master\_SSL\_CA\_Path:

Master\_SSL\_Cert:

Master\_SSL\_Cipher:

Master\_SSL\_Key:

Seconds\_Behind\_Master: 0

1 row in **set** (0.00 sec)

4、MySQL同步测试

如上述均正确配置，现在任何一台MySQL上更新数据都会同步到另一台MySQL，MySQL同步在此不再演示

**二、keepalived安装及配置**

**1、192.168.1.201服务器上keepalived安装及配置**

安装keepalived

\#tar zxvf keepalived-1.1.20.tar.gz

\#cd keepalived-1.1.20

\#./configure \--prefix=/usr/local/keepalived \--with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686

（注意这里2.6.18-164.el5-i686版本自己到服务器的/usr/src/kernels目录下去看，写当前服务器中存在的版本）

\#make && make install

\[root@master2 ~\]\#**/usr/local/keepalived/sbin/keepalived -D**

\[root@master2 ~\]\# ps -aux|grep keepalived

Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.7/FAQ

root     4101  0.3  0.1 35828   632 ?        Ss  15:41   0:00/usr/local/keepalived/sbin/keepalived -D

root     4102  0.7  0.4 39988  1620 ?        S   15:41   0:00/usr/local/keepalived/sbin/keepalived -D

root     4103  0.5  0.3 39988  1092 ?        S   15:41   0:00/usr/local/keepalived/sbin/keepalived -D

root     4106  0.0  0.2 61136   716 pts/0    R+  15:41   0:00 grep keepalived

**配置keepalived**

ln -sv /usr/src/kernels/2.6.18-194.el5-x86\_64/ /usr/src/linux

我们自己在新建一个配置文件，**默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件**

\#mkdir /etc/keepalived

\#vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global\_defs \{

notification\_email \{

luwenju@live.cn

notification\_email\_from luwenju@live.cn

smtp\_server 127.0.0.1

smtp\_connect\_timeout 30

router\_id MySQL-ha

vrrp\_instance VI\_1 \{

state BACKUP   \#两台配置此处均是BACKUP

interface eth0  \#网卡，可使用ifconfig查看

virtual\_router\_id 51

priority 100   \#优先级，另一台改为90

advert\_int 1

nopreempt  \#不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置

authentication \{

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

192.168.1.200

virtual\_server 192.168.1.200 3306 \{

delay\_loop 2   \#每个2秒检查一次real\_server状态

lb\_algo wrr   \#LVS算法

lb\_kind DR    \#LVS模式

persistence\_timeout 60   \#会话保持时间

protocol TCP

real\_server 192.168.1.201 3306 \{

weight 3

notify\_down /usr/local/MySQL/bin/MySQL.sh  \#检测到服务down后执行的脚本

TCP\_CHECK \{

connect\_timeout 10    \#连接超时时间

nb\_get\_retry 3       \#重连次数

delay\_before\_retry 3   \#重连间隔时间

connect\_port 3306   \#健康检查端口

编写检测服务down后所要执行的脚本

\#vi /usr/local/MySQL/bin/MySQL.sh

\#!/bin/sh

pkill keepalived

\#chmod +x /usr/local/MySQL/bin/MySQL.sh

注：此脚本是上面配置文件notify\_down选项所用到的，keepalived使用notify\_down选项来检查real\_server的服务状态，当发现real\_server服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过pkillkeepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP

启动keepalived

\#/usr/local/keepalived/sbin/keepalived –D

\#ps -aux | grep keepalived

**测试**

找一台局域网PC，然后去ping  MySQL的VIP，这时候MySQL的VIP是可以ping的通的

停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本

2、192.168.1.202上keepalived安装及配置

安装keepalived

\#tar zxvf keepalived-1.1.20.tar.gz

\#cd keepalived-1.1.20

\#./configure \--prefix=/usr/local/keepalived \--with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686

\#make && make install

配置keepalived

这台配置和上面基本一样，但有三个地方不同：优先级为90、无抢占设置、real\_server为本机IP

\#mkdir /etc/keepalived

\#vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global\_defs \{

notification\_email \{

luwenju@live.cn

notification\_email\_from luwenju@live.cn

smtp\_server 127.0.0.1

smtp\_connect\_timeout 30

router\_id MySQL-ha

vrrp\_instance VI\_1 \{

state BACKUP

interface eth0

virtual\_router\_id 51

priority 90

advert\_int 1

authentication \{

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

192.168.1.200

virtual\_server 192.168.1.200 3306 \{

delay\_loop 2

lb\_algo wrr

lb\_kind DR

persistence\_timeout 60

protocol TCP

real\_server 192.168.1.202 3306 \{

weight 3

notify\_down /usr/local/MySQL/bin/MySQL.sh

TCP\_CHECK \{

connect\_timeout 10

nb\_get\_retry 3

delay\_before\_retry 3

connect\_port 3306

编写检测服务down后所要执行的脚本

\#vi /usr/local/MySQL/bin/MySQL.sh

\#!/bin/sh

pkill keepalived

\#chmod +x /usr/local/MySQL/bin/MySQL.sh

启动keepalived

\#/usr/local/keepalived/sbin/keepalived –D

\#ps -aux | grep keepalived

测试

停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本

**三、测试**

MySQL远程登录测试

我们找一台安装有MySQL客户端的windows，然后登录VIP，看是否能登录，在登录之两台MySQL服务器都要授权允许从远程登录

MySQL**>** grant all privileges on \*.\* to 'root'@'%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

MySQL**>** flush privileges;

Query OK, 0 rows affected (0.00 sec)

使用客户端登录VIP测试

C:\\MySQL\\bin**>**MySQL.exe -uroot -p123456 -h192.168.1.200 -P3306

Welcome to the MySQL monitor.  Commands end with ; or \\g.

Your MySQL connection id is 224

Server version: 5.0.89-log Source distribution

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

MySQL**>**

● keepalived故障转移测试

※在windows客户端一直去ping  VIP，然后关闭192.168.1.201上的keepalived，正常情况下VIP就会切换到192.168.1.202上面去

※开启192.168.1.201上的keepalived，关闭192.168.1.202上的keepalived，看是否能自动切换，正常情况下VIP又会属于192.168.1.201

注：keepalived切换速度还是非常块的，整个切换过程只需1-3秒

● MySQL故障转移测试

※在192.168.1.201上关闭MySQL服务，看VIP是否会切换到192.168.1.202上

※开启192.168.1.201上的MySQL和keepalived，然后关闭192.168.1.202上的MySQL，看VIP是否会切换到192.168.1.201上

下面是用windows客户端连接的MySQL的VIP，在切换时我执行了一个MySQL查询命令，从执行show databases到显示出结果时间为3-5秒（大家可以看到上面有个错误提示，不过不用担心，因为我们的keepalived切换大概为3秒左右，这3秒左右VIP是谁都不属于的）

MySQL**>** show databases;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    592

Current database: \*\*\* NONE \*\*\*

\+--------------------+

| Database           |

\+--------------------+

| information\_schema |

| MySQL              |

| test               |

\+--------------------+

3 rows in set (9.01 sec)

后话：世间万事万物，都不具备绝对的完美，就像上面的MySQL-HA一样，keepalived只能做到对3306的健康检查，但是做不到比如像MySQL复制中的slave-SQL、slave-IO进程的检查。所以要想做到一些细致的健康检查，还得需要借助额外的监控工具，比如nagios，然后用nagios实现短信、邮件报警，从而能够有效地解决问题。

mysql> GRANT REPLICATION SLAVE ON \*.\*

-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

如果你没有备份主服务器，这里是一个创建备份的快速程序。所有步骤都应该在主服务器主机上执行。

1.   发出该语句：

mysql> FLUSH TABLES WITH READ LOCK；

2.   仍然加锁时，执行该命令（或它的变体）：

shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql

3.   发出该语句并且确保记录了以后用到的输出：

mysql>SHOW MASTER STATUS；

4.   释放锁：

mysql> UNLOCK TABLES；

一个可选择的方法是，转储主服务器的SQL来代替前面步骤中的二进制复制。要这样做，你可以在主服务器上使用mysqldump --master-data，以后装载SQL转储到到你的从服务器。

1. MySQL-VIP：192.168.1.200     2. MySQL-master1：192.168.1.201     3. MySQL-master2：192.168.1.202     4.     5. OS版本：CentOS 5.4     6. MySQL版本：5.0.89     7. Keepalived版本：1.1.20

一、MySQL master-master配置

1、修改MySQL配置文件

两台MySQL均如要开启binlog日志功能，开启方法：在MySQL配置文件\[MySQLd\]段中加上log-bin=MySQL-bin选项

两台MySQL的server-ID不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可

2、将192.168.1.201设为192.168.1.202的主服务器

在192.168.1.201上新建授权用户

1. MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'replication';     2. Query OK, 0 rows affected (0.00 sec)     3.     4. MySQL> show master status;     5. +------------------+----------+--------------+------------------+     6. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |     7. +------------------+----------+--------------+------------------+     8. | MySQL-bin.000003 |      374 |              |                  |     9. +------------------+----------+--------------+------------------+    10. 1 row in set (0.00 sec)

在192.168.1.202上将192.168.1.201设为自己的主服务器

1. MySQL> change master to master_host='192.168.1.201',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374;     2. Query OK, 0 rows affected (0.05 sec)     3.     4. MySQL> start slave;     5. Query OK, 0 rows affected (0.00 sec)     6.     7. MySQL> show slave status\G     8. *************************** 1. row ***************************     9.              Slave_IO_State: Waiting for master to send event    10.                 Master_Host: 192.168.1.201    11.                 Master_User: replication    12.                 Master_Port: 3306    13.               Connect_Retry: 60    14.             Master_Log_File: MySQL-bin.000003    15.         Read_Master_Log_Pos: 374    16.              Relay_Log_File: MySQL-master2-relay-bin.000002    17.               Relay_Log_Pos: 235    18.       Relay_Master_Log_File: MySQL-bin.000003    19.            Slave_IO_Running: Yes    20.           Slave_SQL_Running: Yes    21.             Replicate_Do_DB:    22.         Replicate_Ignore_DB:    23.          Replicate_Do_Table:    24.      Replicate_Ignore_Table:    25.     Replicate_Wild_Do_Table:    26. Replicate_Wild_Ignore_Table:    27.                  Last_Errno: 0    28.                  Last_Error:    29.                Skip_Counter: 0    30.         Exec_Master_Log_Pos: 374    31.             Relay_Log_Space: 235    32.             Until_Condition: None    33.              Until_Log_File:    34.               Until_Log_Pos: 0    35.          Master_SSL_Allowed: No    36.          Master_SSL_CA_File:    37.          Master_SSL_CA_Path:    38.             Master_SSL_Cert:    39.           Master_SSL_Cipher:    40.              Master_SSL_Key:    41.       Seconds_Behind_Master: 0    42. 1 row in set (0.00 sec)

3、将192.168.1.202设为192.168.1.201的主服务器

在192.168.1.202上新建授权用户

在192.168.1.201上，将192.168.1.202设为自己的主服务器

1. MySQL> change master to master_host='192.168.1.202',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374;     2. Query OK, 0 rows affected (0.05 sec)     3.     4. MySQL> start slave;     5. Query OK, 0 rows affected (0.00 sec)     6.     7. MySQL> show slave status\G     8. *************************** 1. row ***************************     9.              Slave_IO_State: Waiting for master to send event    10.                 Master_Host: 192.168.1.202    11.                 Master_User: replication    12.                 Master_Port: 3306    13.               Connect_Retry: 60    14.             Master_Log_File: MySQL-bin.000003    15.         Read_Master_Log_Pos: 374    16.              Relay_Log_File: MySQL-master1-relay-bin.000002    17.               Relay_Log_Pos: 235    18.       Relay_Master_Log_File: MySQL-bin.000003    19.            Slave_IO_Running: Yes    20.           Slave_SQL_Running: Yes    21.             Replicate_Do_DB:    22.         Replicate_Ignore_DB:    23.          Replicate_Do_Table:    24.      Replicate_Ignore_Table:    25.     Replicate_Wild_Do_Table:    26. Replicate_Wild_Ignore_Table:    27.                  Last_Errno: 0    28.                  Last_Error:    29.                Skip_Counter: 0    30.         Exec_Master_Log_Pos: 374    31.             Relay_Log_Space: 235    32.             Until_Condition: None    33.              Until_Log_File:    34.               Until_Log_Pos: 0    35.          Master_SSL_Allowed: No  36.          Master_SSL_CA_File:    37.          Master_SSL_CA_Path:    38.             Master_SSL_Cert:    39.           Master_SSL_Cipher:    40.              Master_SSL_Key:    41.       Seconds_Behind_Master: 0    42. 1 row in set (0.00 sec)

4、MySQL同步测试

如上述均正确配置，现在任何一台MySQL上更新数据都会同步到另一台MySQL，MySQL同步在此不再演示

二、keepalived安装及配置

1、192.168.1.201服务器上keepalived安装及配置

安装keepalived

1. #tar zxvf keepalived-1.1.20.tar.gz     2. #cd keepalived-1.1.20     3. #./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686     4. #make && make install

配置keepalived

我们自己在新建一个配置文件，默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件

1. #mkdir /etc/keepalived     2. #vi /etc/keepalived/keepalived.conf     3. ! Configuration File for keepalived     4. global_defs {     5.      notification_email {     6.      luwenju@live.cn     7.      }     8.      notification_email_from luwenju@live.cn     9.      smtp_server 127.0.0.1    10.      smtp_connect_timeout 30    11.      router_id MySQL-ha    12.      }    13.    14. vrrp_instance VI_1 {    15.      state BACKUP   #两台配置此处均是BACKUP    16.      interface eth0    17.      virtual_router_id 51    18.      priority 100   #优先级，另一台改为90    19.      advert_int 1    20.      nopreempt  #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置    21.      authentication {    22.      auth_type PASS    23.      auth_pass 1111    24.      }    25.      virtual_ipaddress {    26.      192.168.1.200    27.      }    28.      }    29.    30. virtual_server 192.168.1.200 3306 {    31.      delay_loop 2   #每个2秒检查一次real_server状态    32.      lb_algo wrr   #LVS算法    33.      lb_kind DR    #LVS模式    34.      persistence_timeout 60   #会话保持时间    35.      protocol TCP    36.      real_server 192.168.1.201 3306 {    37.      weight 3    38.      notify_down /usr/local/MySQL/bin/MySQL.sh  #检测到服务down后执行的脚本    39.      TCP_CHECK {    40.      connect_timeout 10    #连接超时时间    41.      nb_get_retry 3       #重连次数    42.      delay_before_retry 3   #重连间隔时间    43.      connect_port 3306   #健康检查端口    44.      }    45.      }

编写检测服务down后所要执行的脚本

#vi /usr/local/MySQL/bin/MySQL.sh   #!/bin/sh   pkill keepalived   #chmod +x /usr/local/MySQL/bin/MySQL.sh

注：此脚本是上面配置文件notify\_down选项所用到的，keepalived使用notify\_down选项来检查real\_server的服务状态，当发现real\_server服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP

启动keepalived

1. #/usr/local/keepalived/sbin/keepalived –D     2. #ps -aux | grep keepalived

测试

找一台局域网PC，然后去ping  MySQL的VIP，这时候MySQL的VIP是可以ping的通的

停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本

2、192.168.1.202上keepalived安装及配置

安装keepalived

配置keepalived

这台配置和上面基本一样，但有三个地方不同：优先级为90、无抢占设置、real\_server为本机IP

1. #mkdir /etc/keepalived     2. #vi /etc/keepalived/keepalived.conf     3. ! Configuration File for keepalived     4. global_defs {     5.      notification_email {     6.      luwenju@live.cn     7.      }     8.      notification_email_from luwenju@live.cn     9.      smtp_server 127.0.0.1    10.      smtp_connect_timeout 30    11.      router_id MySQL-ha    12.      }    13.    14. vrrp_instance VI_1 {    15.      state BACKUP    16.      interface eth0    17.      virtual_router_id 51    18.      priority 90    19.      advert_int 1    20.      authentication {    21.      auth_type PASS    22.      auth_pass 1111    23.      }    24.      virtual_ipaddress {    25.      192.168.1.200    26.      }    27.      }    28.    29. virtual_server 192.168.1.200 3306 {    30.      delay_loop 2    31.      lb_algo wrr    32.      lb_kind DR    33.      persistence_timeout 60    34.      protocol TCP    35.      real_server 192.168.1.202 3306 {    36.      weight 3    37.      notify_down /usr/local/MySQL/bin/MySQL.sh    38.      TCP_CHECK {    39.      connect_timeout 10    40.      nb_get_retry 3    41.      delay_before_retry 3    42.      connect_port 3306    43.      }    44.      }

编写检测服务down后所要执行的脚本

1. #vi /usr/local/MySQL/bin/MySQL.sh     2. #!/bin/sh     3. pkill keepalived     4. #chmod +x /usr/local/MySQL/bin/MySQL.sh     5.     6. 启动keepalived     7. #/usr/local/keepalived/sbin/keepalived –D     8. #ps -aux | grep keepalived

测试

停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本

三、测试

MySQL远程登录测试

我们找一台安装有MySQL客户端的windows，然后登录VIP，看是否能登录，在登录之两台MySQL服务器都要授权允许从远程登录

1. MySQL> grant all privileges on *.* to 'root'@'%' identified by '123456';     2. Query OK, 0 rows affected (0.00 sec)     3.     4. MySQL> flush privileges;     5. Query OK, 0 rows affected (0.00 sec)

使用客户端登录VIP测试

1. C:\MySQL\bin>MySQL.exe -uroot -p123456 -h192.168.1.200 -P3306     2. Welcome to the MySQL monitor.  Commands end with ; or \g.     3. Your MySQL connection id is 224     4. Server version: 5.0.89-log Source distribution     5.     6. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.     7.     8. MySQL>

● keepalived故障转移测试

※在windows客户端一直去ping  VIP，然后关闭192.168.1.201上的keepalived，正常情况下VIP就会切换到192.168.1.202上面去

※开启192.168.1.201上的keepalived，关闭192.168.1.202上的keepalived，看是否能自动切换，正常情况下VIP又会属于192.168.1.201

注：keepalived切换速度还是非常块的，整个切换过程只需1-3秒

● MySQL故障转移测试

※在192.168.1.201上关闭MySQL服务，看VIP是否会切换到192.168.1.202上

※开启192.168.1.201上的MySQL和keepalived，然后关闭192.168.1.202上的MySQL，看VIP是否会切换到192.168.1.201上

[141106083015331.png]: /images/20220731/fec0bcb99bad4acdbf40958e521f54e5.png
[141106083015332.png]: /images/20220731/e13aea043f71448781c0c74396689ec7.png