高可用mysql集群搭建（mysql5.6+keepalived）

川长思鸟来 2022-08-21 14:57 169阅读 0赞

#     #

1、方案在一定程度上保证主库的高可用，在一台主库down掉之后，可以在极短的时间内切换到另一台从库上（尽可能减少主库宕机对业务造成的影响）

（1）      一台主库（master）提供服务，只负责数据的写入

（2）      一台数据库服务器资源做master主库的从库，之间做主从同步

（3）      两台主库之间做高可用，采用keepalived，master同时也作为keepalived的主

（4）      程序调用使用高可用的keepalived VIP的地址

（5）      所有提供服务的从服务器与master进行主从同步

图1-1 mysql集群系统框架图

# 2、主从复制详细搭建过程 #

在集群中的所有服务器上搭建mysql服务，一般采用的是源码包安装。

MySQL-VIP：192.168.237.200

MySQL-version: mysql-5.5.36

MySQL-master：192.168.237.127

MySQL-slave01：192.168.237.128

MySQL-slave02：192.168.237.128

Keepalived版本：1.1.20

## 4、安装依赖包 ##

yum-y install cmake make gcc gcc-c++  ncurses-devel bisonopenssl-devel

## 5、所需要的用户/组 ##

groupadd mysql

useradd -gmysql -r mysql

## 6、源码包 ##

wgethttp://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz

## 7、安装所需的目录 ##

mkdir /data/mydata/\{data,tmp,logs,mysqllog\}

cd /data/mydata/mysqllogs

mkdir logs

修改权限：

chgrp -R mysql /data/mydata/data&& chown -R mysql /data/mydata/data

chgrp -R mysql /data/mydata/logs&& chown -R mysql /data/mydata/logs

chgrp -R mysql /data/mydata/tmp&& chown -R mysql /data/mydata/tmp

chgrp -R mysql /data/mydata/mysqllog&& chown -R mysql /data/mydata/mysqllog

tar xf mysql-5.5.36.tar.gz

cd mysql-5.5.36

cmake \\

\-DCMAKE\_INSTALL\_PREFIX=/usr/local/mysql \\

\-DMYSQL\_DATADIR=/data/mydata/data \\

\-DSYSCINFDIR=/etc \\

\-DWITH\_INNOBASE\_STORAGE\_ENGINE=1 \\

\-DWITH\_ARCHIVE\_STORAGE\_ENGINE=1 \\

\-DWITH\_BLACKHOLE\_STORAGE\_ENGINE=1 \\

\-DWITH\_READLINE=1 \\

\-DWITH\_SSL=system \\

\-DWITH\_ZLIB=system \\

\-DWITH\_LIBWARP=0 \\

\-DWITH\_UNIX\_ADDR=/tmp/mysql.sock \\

\-DDEFAULT\_CHARASET=uft8 \\

\-DDEFAULT\_COLLATTON=utf9\_general\_ci

make && make install

## 9、提供启动脚本 ##

cp support-files/mysql.server /etc/rc.d/init.d/mysqld

## 10、主库提供配置文件 ##

\[client\]

port = 3306

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

default-character-set=utf-8

\[mysqld\]

server-id = 1

port = 3306

user = mysql

basedir = /usr/local/mysql

datadir = /data/mydata/data

tmpdir = /data/mydata/tmp

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

skip-external-locking

skip-name-resolve

default-storage-engine = INNODB

character-set-server = utf8

wait-timeout = 100

connect\_timeout = 20

interactive\_timeout = 100

back\_log = 300

myisam\_recover

event\_scheduler = on

log-bin=/data/mydata/logs/mysql-bin

binlog\_format = row

max\_binlog\_size = 64M

binlog\_cache\_size = 1M

slave-net-timeout = 10

skip-slave-start

slow\_query\_log  = 1

long\_query\_time = 1

slow\_query\_log\_file = /data/mydata/mysqllog/logs/mysql.slow

log-error= /data/mydata/mysqllog/logs/error.log

max\_connections = 1000

max\_user\_connections = 1000

max\_connect\_errors = 10000

key\_buffer\_size =32M     \#以MyISAM为主的服务器,要调大此值

max\_allowed\_packet = 64M

table\_cache = 4096

table\_open\_cache = 4096

table\_definition\_cache = 4096

sort\_buffer\_size =512K

read\_buffer\_size =512K

read\_rnd\_buffer\_size = 512K

join\_buffer\_size =512K

tmp\_table\_size = 64M

max\_heap\_table\_size = 64M

query\_cache\_type = 0

query\_cache\_size = 0

bulk\_insert\_buffer\_size = 16M

thread\_cache\_size = 64

thread\_concurrency =16       \#CPU核数\*2

thread\_stack = 256K

innodb\_data\_home\_dir = /data/mydata/data

innodb\_log\_group\_home\_dir= /data/mydata/mysqllog/logs

innodb\_data\_file\_path = ibdata1:1G:autoextend

innodb\_buffer\_pool\_size = 16G

innodb\_buffer\_pool\_instances =4

innodb\_additional\_mem\_pool\_size = 16M

innodb\_log\_file\_size = 512M

innodb\_log\_buffer\_size = 32M

innodb\_log\_files\_in\_group = 3

innodb\_flush\_log\_at\_trx\_commit = 2

innodb\_lock\_wait\_timeout = 10

innodb\_sync\_spin\_loops = 40

innodb\_max\_dirty\_pages\_pct = 90

innodb\_support\_xa = 1

innodb\_thread\_concurrency = 0

innodb\_thread\_sleep\_delay = 500

innodb\_file\_io\_threads = 4

innodb\_concurrency\_tickets = 1000

log\_bin\_trust\_function\_creators = 1

innodb\_flush\_method = O\_DIRECT

innodb\_file\_per\_table          \#是否采用单表单空间

innodb\_write\_io\_threads = 8

innodb\_read\_io\_threads = 8

innodb\_io\_capacity = 1000

innodb\_file\_format =Barracuda    \#不开启单表单空间,此选项无效

innodb\_purge\_threads = 1

innodb\_purge\_batch\_size = 32

innodb\_old\_blocks\_pct = 75

innodb\_change\_buffering = all

transaction\_isolation = READ-COMMITTED

\[mysqldump\]

quick

max\_allowed\_packet = 32M

\[mysql\]

no-auto-rehash

\[myisamchk\]

key\_buffer\_size = 64M

sort\_buffer\_size = 256M

read\_buffer = 2M

write\_buffer = 2M

\[mysqlhotcopy\]

interactive-timeout

\[mysqld\_safe\]

open-files-limit = 10240

## 3.8  为salve提供配置文件 ##

slave的配置文件仅需在master上稍作修改

server-id = 20

log\_slave\_updates = 1 \#添加(将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启)

replicate-same-server-id=0 \#添加(防止MySQL循环更新)

relay\_log\_recovery = 1   \#添加(MySQLrelay\_log的自动修复功能)

## 3.9  为从库提供配置文件 ##

\[client\]

port = 3306

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

default-character-set = utf8

\[mysqld\]

server-id = 2

port = 3306

user = mysql

basedir = /usr/local/mysql

datadir = /data/mydata/data

tmpdir  = /data/mydata/tmp

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

skip-external-locking

skip-name-resolve

default-storage-engine = INNODB

character-set-server = utf8

wait-timeout = 100

connect\_timeout = 20

interactive\_timeout = 100

back\_log = 300

myisam\_recover

event\_scheduler = on

log-bin=/data/mydata/logs/mysql-bin

binlog\_format = row

max\_binlog\_size = 64M

binlog\_cache\_size = 1M

slave-net-timeout = 10

relay\_log\_recovery = 1

slow\_query\_log = 1

long\_query\_time = 1

slow\_query\_log\_file= /data/mydata/mysqllog/logs/mysql.slow

log-error= /data/mydata/mysqllog/logs/error.log

max\_connections = 500

max\_user\_connections = 500

max\_connect\_errors = 10000

key\_buffer\_size =32M     \#以MyISAM为主的服务器,要调大此值

max\_allowed\_packet = 64M

table\_cache = 2048

table\_open\_cache = 2048

table\_definition\_cache = 2048

sort\_buffer\_size =128K

read\_buffer\_size =128K

read\_rnd\_buffer\_size = 128K

join\_buffer\_size =128K

tmp\_table\_size =16M

max\_heap\_table\_size = 16M

query\_cache\_type = 0

query\_cache\_size = 0

bulk\_insert\_buffer\_size = 16M

thread\_cache\_size = 64

thread\_concurrency =4       \#CPU核数\*2

thread\_stack = 128K

innodb\_data\_home\_dir= /data/mydata/data

innodb\_log\_group\_home\_dir= /data/mydata/mysqllog/logs

innodb\_data\_file\_path =ibdata1:1G:autoextend

innodb\_buffer\_pool\_size = 2G

innodb\_buffer\_pool\_instances =4

innodb\_additional\_mem\_pool\_size = 4M

innodb\_log\_file\_size = 512M

innodb\_log\_buffer\_size = 16M

innodb\_log\_files\_in\_group = 3

innodb\_flush\_log\_at\_trx\_commit = 2

innodb\_lock\_wait\_timeout = 10

innodb\_sync\_spin\_loops = 40

innodb\_max\_dirty\_pages\_pct = 90

innodb\_support\_xa = 1

innodb\_thread\_concurrency = 0

innodb\_thread\_sleep\_delay = 500

innodb\_file\_io\_threads = 4

innodb\_concurrency\_tickets = 1000

log\_bin\_trust\_function\_creators = 1

innodb\_flush\_method =O\_DIRECT

innodb\_file\_per\_table                \#是否采用单表单空间

innodb\_write\_io\_threads = 8

innodb\_read\_io\_threads = 8

innodb\_io\_capacity = 1000

innodb\_file\_format =Barracuda     \#不开启单表单空间,此选项无效

innodb\_purge\_threads = 1

innodb\_purge\_batch\_size = 32

innodb\_old\_blocks\_pct = 75

innodb\_change\_buffering = all

transaction\_isolation = READ-COMMITTED

\[mysqldump\]

quick

max\_allowed\_packet = 32M

\[mysql\]

no-auto-rehash

\[myisamchk\]

key\_buffer\_size = 64M

sort\_buffer\_size = 256M

read\_buffer = 2M

write\_buffer = 2M

\[mysqlhotcopy\]

interactive-timeout

\[mysqld\_safe\]

open-files-limit = 10240

## 3.10     初始化MySQL ##

/usr/local/mysql/scripts/mysql\_install\_db --user=mysql--datadir=/data/mydata/data/ --basedir=/usr/local/mysql

## 3.11     为启动脚本赋予可执行权限并启动MySQL ##

chmod +x /etc/rc.d/init.d/mysqld

/etc/init.d/mysqld start

## 3.12     映射命令路径 ##

ln -s /usr/local/mysql/bin/mysql/usr/bin/mysql

## 3.13     关闭防火墙 ##

关闭命令：  service iptables stop

永久关闭防火墙：chkconfig iptables off

两个命令同时运行，运行完成后查看防火墙关闭状态

service iptables status

## 3.14     配置master ##

### 3.12.1 添加同步账户 ###

mysql> grant replication slaveon \*.\* to 'repl'@'192.168.216.%' idetified by 'repl';

mysql> flush privileges;

### 3.12.2查看主库状态 ###

mysql> show master status;

## 3.15     配置slave ##

### 3.13.1配置slave为master的从 ###

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

flush privileges;

\#若是线上有数据需要先导入数据

mysql> CHANGE MASTER TO

->MASTER\_HOST='192.168.237.128',

->MASTER\_PORT=3306,

->MASTER\_USER='repl',

->MASTER\_PASSWORD='123456',

->MASTER\_LOG\_FILE='mysql-bin.000009',

->MASTER\_LOG\_POS=652;

Query OK, 0 rows affected (0.03sec)

mysql> start slave;

mysql> show slave status \\G

Slave\_IO\_Running:Yes   \#确保为yes

Slave\_SQL\_Running:Yes  \#确保为yes

如果显示

Slave\_IO\_State: Waiting to reconnect aftera failed registration on master

### 3.13.2 配置slave的同步用户 ###

mysql> grant replication slave on \*.\* to 'repl’@’192.168.237.%' identifiedby '123456';

mysql> flush privileges;

### 3.13.3 查看slave的状态 ###

mysql> showmaster status;

## 3.16     从库根据配置slave的从即可。 ##

# 4        Keepalived架构图 #

图1-2keepalived架构图

# 5        keepalived安装及配置 #

**1.    **

**2.    **

**3.    **

**4.    **

**5.    **

## 5.1.     安装keepalived ##

tar zxvf keepalived-1.1.20.tar.gz

cd keepalived-1.1.20

yum install popt-devel,

./configure--prefix=/usr/local/keepalived--with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86\_64/

make && make install

## 5.2.    配置 ##

我们自己在新建一个配置文件，默认情况下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

virtual\_router\_id 51

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

advert\_int 1

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

authentication \{

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

192.168.237.200

virtual\_server 192.168.237.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.237.127 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服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP

## 5.3.    启动keepalived ##

/usr/local/keepalived/sbin/keepalived –D

ps -aux | grep keepalived

## 5.4.    主库keepalived测试 ##

找一台局域网服务器，然后去ping  MySQL的VIP，这时候MySQL的VIP是可以ping的通的停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本.

## 5.5.    从库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

## 5.6.    配置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.237.200 3306 \{

delay\_loop 2

lb\_algo wrr

lb\_kind DR

persistence\_timeout 60

protocol TCP

real\_server 192.168.237.128 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

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>#!/bin/sh &nbsp;</p> <p>pkill&nbsp;keepalived &nbsp;</p> </td> 
  </tr> 
 </tbody> 
</table>

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

## 5.8.    启动keepalived   ##

/usr/local/keepalived/sbin/keepalived –D

ps -aux | grep keepalived

## 5.9.    MySQL远程登录测试 ##

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

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

MySQL**>** flush privileges;

## 5.10. 使用客户端登录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**>**

## 5.11. keepalived故障转移测试 ##

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

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

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

## 5.12. MySQL故障转移测试 ##

在192.168.237.127上关闭MySQL服务，看VIP是否会切换到192.168.237.128上，开启192.168.237.127上的MySQL和keepalived，然后关闭192.168.237.128上的MySQL，看VIP是否会切换到192.168.237.127上。

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

高可用mysql集群搭建（mysql5.6+keepalived）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关搭建高可用mysql集群PXC

相关 Docker 搭建mysql pxc集群 + haproxy + keepalived 高可用

相关 mysql-mmm搭建,搭建mysql-mmm高可用MySQL集群

相关 docker swarm集群搭建高可用mysql主备

相关 pxs MySQL_Mysql高开用集群搭建

相关高可用mysql集群搭建（mysql5.6+keepalived）

相关 MySQL主主复制+Keepalived 打造高可用MySQL集群

相关搭建高可用mysql集群PXC

相关搭建：LVS+Keepalived高可用Web服务群集环境

相关 keepalived+Haproxy搭建高可用Web群集