mysql高可用架构方案之一(keepalived+主主双活)

时间:2022-09-14 13:37:18
Mysql双主双活+keepalived实现高可用

 

 

 
 

 

目录

1、前言... 4

2、方案... 4

2.1、环境及软件... 4

2.2、IP规划... 4

2.3、架构图... 4

3、安装设置MYSQL半同步... 5

4、Keepalived实现MYSQL的高可用... 11

 

 

 

1、前言

最近研究了下高可用的东西,这里总结一下mysql主主双活的架构方案,整体上提高服务的高可用性,出现问题也不需要手动切换,提高整体的维护效率。确定改造的话,只需要让他们的程序中使用vip地址就可以,实现起来比较容易。

2、方案

本案例先使用两台linux做双机MASTER-MASTER高可用(后续可以考虑加入只读SLAVER,用于提高查询性能),采用MYSQL5.6.x的半同步实现数据复制和同步,使用keepalived来监控MYSQL和提供VIP及浮动。任何一台主机宕机都不会影响对外提供服务(vip可以浮动),保持服务的高可用。

注 keepalived之间使用vrrp组播方式通信使用的IP地址是192.168.150.201  

2.1、环境及软件

操作系统:RedHat 5.5

Keepalived:keepalived-1.2.12.tar.gz

MYSQL:MySQL-server- mysql-5.0.77-4.el5_4.2.x86_64.rpm   MySQL-client- mysql-5.0.77-4.el5_4.2.x86_64.rpm

 

2.2、IP规划

名称
    

IP
    

备注

VIP
    

192.168.150.201
    

由KEEPALIVED产生的虚拟IP,也是前端客户端使用的IP

MYSQL_DB_MASTER1
    

192.168.150.145
    

数据库1服务器

MYSQL_DB_MASTER2
    

192.168.150.146
    

数据库2服务器

 2.3、架构图

3、安装设置MYSQL半同步

3.1、安装MYSQL

这里选择的是使用社区版的MYSQL,请在www.mysql.com上下载对应操作系统的安装文件,这里根据官方的建议使用RPM包方式安装。也可以进行yum安装。

 

安装

如果是默认Redhat5.5安装后,

 # 使用yum 安装 yum install mysql

yum install mysql-server

……

默认安装是在/usr下面,所以my.cnf是在/usr/my.cnf,这里为了习惯用法,拷贝到/etc/目录下

# mv /usr/my.cnf /etc/

 

标准方式修改密码

# mysqladmin -u root -p oldPassword password

# New password:

# Confirm new password:



# mysqladmin -u root -p password newPassword

 

SQL直接更新方式修改密码:

 初始化root密码常见问题及处理

error: 'Access denied for user 'root'@'localhost' (usingpassword: NO)'

# /etc/init.d/mysql stop

# mysqld_safe --user=mysql --skip-grant-tables--skip-networking &

# mysql -u root mysql

mysql> UPDATE user SETPassword=PASSWORD('newpassword') where USER='root';

mysql> FLUSH PRIVILEGES;

mysql> quit

# /etc/init.d/mysql restart

# mysql -uroot -p

Enter password: <输入新设的密码newpassword>

mysql>

设置网络访问权限

 MYSQL的用户访问策略是需要指定用户从某IP可以访问那些权限。具体用法请参考grant命令。这里为了简单起见,直接设置root用户在整个局域内(192.168.150.*)都可以访问数据库。

 

# mysql -uroot -p

******

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'192.168.150.%' IDENTIFIEDBY 'password' WITH GRANT OPTION;

mysql>FLUSH PRIVILEGES;

 

这样设置后,就可以通过内网的其他机器访问数据库了。

 

修改数据文件路径

mysql安装后,默认的数据存储路径在/var/lib/mysql下。这里修改为/data/mysql

# service mysql stop

# mkdir /data/mysql

# cp -r /var/lib/mysql/* /data/mysql

# chown -R mysql:mysql /data/mysql

vi /etc/my.cnf

修改里面的参数

[mysqld]

     datadir= /data/mysql

     socket= /data/mysql/mysql.sock

     # 顺便配置下MYSQL使用mysql用户启动

     user=mysql

 保存退出

 为/data/mysql/mysql.sock建立指向/var/lib/mysql/mysql.sock的软连接

# ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock

启动MYSQL并测试

# service mysql start

# mysql -uroot -p

******

mysql>

 

最可能出现的问题(如果你是Redhat6.x或CentOS6.x):selinux 照成服务无法正常启动,报错:

Starting MySQL. ERROR! The server quit without updatingPID file (/data/mysql/xxxxx.pid).

验证问题方法:

# setenforce 0

# service mysql start

 

Starting MySQL. SUCCESS!

如果能正常启动,解决问题,就确定是selinux造成的问题了。

 

解决办法:直接禁用

vim /etc/selinux/config

修改:SELINUX=disabled

保存后,重启操作系统生效

 

如 果出现问题,一般解决问题的方式是查看MYSQL的日志文件,一般提示会比较清楚,或者根据错误信息区GOOGLE都可以找到答案。日志文件:${datadir}/${hostname}.err, 本例中修改了数据目录,所以日志文件是:/data/mysql/acooly1.err

 

OK,本节安装MYSQL完成,然后另外一台服务器的MYSQL安装完全相同(半同步的设置有小不同,那下面会说明)。请参照以上说明安装另外一台MYSQL服务(MYSQL_DB_MASTER2)。

 

3.2、设置MYSQL双向半同步

本方案中为了实现故障转移功能,在利用半同步复制能力的同时,对整体方案进行了调整如下:

1.  MYSQL_DB_MASTER1 和MYSQL_DB_MASTER2互为主备,即MYSQL_DB_MASTER1时MYSQL_DB_MASTER2的 主,MYSQL_DB_MASTER2是MYSQL_DB_MASTER1的主,通过半同步实现双向的同步复制(注意:这里不会出现冲突,因为备机只会同步不是本机server_id的bin-log日志,通过两台机器的server_id隔离需要同步的bin-log)

2.  通过Keepalived实现MYSQL服务器的监控和VIP的浮动,保证同一时间只有一台MYSQL服务器可用。

3.  该方案中,可以兼容后期的扩容,加入多台SALVE与两台MASTER异步同步实现数据的只读查询,实现读写分离,提高整体性能。

配置/etc/my.cnf

分别修改/etc/my.cnf配置,打开bin-log功能和设置server_id

vi /etc/my.cnf

 

/etc/my.conf代码  

[mysqld]

# MYSQL_DB_MASTER1设置为1,MYSQL_DB_MASTER2设置为2

server_id=1

log_bin=mysql-bin

 安 装官方文档说明,主(MYSQL_DB_MASTER1)需要设置rpl_semi_sync_master_enabled=1和rpl_semi_sync_master_timeout=1000,备(MYSQL_DB_MASTER2)需要设置 rpl_semi_sync_slave_enabled=1,这里因为是双向同步,我都没有设置,经测试,没有设置也没有关系(暂时没有深究,后续在研究下)。

 

配置MYSQL_DB_MASTER1与MYSQL_DB_MASTER2的主备同步

在MYSQL_DB_MASTER1上操作配置MYSQL_DB_MASTER1为MYSQL_DB_MASTER2的主。

Mysql_db_master1代码  

# mysql-uroot -p

Enterpassword:

......

-- 建立复制用户replication,允许内网内其他机器(访问本机所有数据库)

mysql> grant replication  slave on *.* to'repdb01'@'192.168.150.%' identified by '123456';

QueryOK, 0 rows affected (0.00 sec)

-- 查看master的bin-log状态,用户设置SLAVE同步的起点

mysql>show master status;

+------------------+----------+--------------+------------------+-------------------+

     | File             | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |

     +------------------+----------+--------------+------------------+-------------------+

     | mysql-bin.000013 |      120 |              |                  |                   |

     +------------------+----------+--------------+------------------+-------------------+

     1 row in set (0.02 sec) </span>

 这里注意记录下当前bin-log文件名称:mysql-bin.000013和位置:120,备机设置的时候需要用到。

 

 在MYSQL_DB_MASTER2上操作配置

Mysql_db_master2代码  

 

--设置SLAVE的MASTER和开始同步的文件位置

mysql>change master tomaster_host='192.168.150.145',master_user='repdb01',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120;

--启动同步

mysql>start slave;

mysql>show slave status\G;

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

               Slave_IO_State: Waiting formaster to send event

                  Master_Host:192.168.150.145

                  Master_User: repdb01

                  Master_Port: 3306

                Connect_Retry: 10

              Master_Log_File:mysql-bin.000013

          Read_Master_Log_Pos: 120

               Relay_Log_File: acooly2-relay-bin.000020

                Relay_Log_Pos: 283

        Relay_Master_Log_File:mysql-bin.000013

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

             ……

1 row in set (0.02sec)</span>

Slave_IO_Running: Yes和Slave_SQL_Running:Yes表示SLAVE已经正常启动并监听MASTER的数据发送事件,处理同步。

 

 配置MYSQL_DB_MASTER2与MYSQL_DB_MASTER1的主备同步

 所有配置操作与上一节,只是把主备关系交换。

 

OK

如果所有的配置成功,启动MASTER1和MASTER2两个数据库,分别从两端创建数据库,表和更新表数据,另外一边都会实时同步。

 也可以在两台机器中分别配置/etc/my.cnf

210.146

[root@rac2 keepalive]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Default to using old password format for compatibilitywith mysql 3.x

# clients (those using the mysqlclient10 compatibilitypackage).

old_passwords=1

 

# Disabling symbolic-links is recommended to preventassorted security risks;

# to do so, uncomment this line:

# symbolic-links=0

old_passwords=1

lower_case_table_names=1

default-character-set=utf8

default-storage-engine=innodb

max_connect_errors = 100000

#innodb_buffer_pool_size= 8G

max_connections = 500

default-character-set=utf8

 

 

server-id=2

#log-bin=mysqlbin

innodb_flush_log_at_trx_commit=1

sync_binlog=1

init_connect='SET NAMES utf8'

log-bin=mysqlbin

master-host=192.168.150.145

master-user=repdb01

master-pass=123456

master-connect-retry=60

replicate-do-db=db01

replicate-do-db=spring

master-port=3306

slave-net-timeout=60

[mysqld_safe]

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

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

 

 

[client]

default-character-set=utf8

 

210.145 上面的配置

 

[root@rac1 ~]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Default to using old password format for compatibilitywith mysql 3.x

# clients (those using the mysqlclient10 compatibilitypackage).

old_passwords=1

 

# Disabling symbolic-links is recommended to preventassorted security risks;

# to do so, uncomment this line:

# symbolic-links=0

old_passwords=1

lower_case_table_names=1

default-character-set=utf8

default-storage-engine=innodb

max_connect_errors = 100000

#innodb_buffer_pool_size= 8G

max_connections = 500

default-character-set=utf8

#binlog_format=mixed

server-id=1

master-host=192.168.150.146

master-user=repdb01

master-pass=123456

master-connect-retry=60

replicate-do-db=db01

replicate-do-db=spring

init_connect='SET NAMES utf8'

log-bin=mysqlbin

[mysqld_safe]

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

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

[client]

default-character-set=utf8

两个配置完成后重启数据库,配置完成。

4、Keepalived实现MYSQL的高可用

   前面已经完成MYSQL的双向实时同步的配置,本节使用Keepalived实现MYSQL双机的高可用,故障转移。通过VIP实现同时只有一台MYSQL提供服务。

4.1、配置KEEPALIVED

两台MYSQL服务器上的配置基本相同(除了keepalived.conf的个别配置外),具体操作配置如下:

设置keepalived的主配置文件/etc/keepalived/keepalived.conf实现本机的MYSQL的监控和VIP浮动。

安装keepalived

tar –zxvf keepalived-1.1.20.tar.gz

cd keepalived-1.1.20

./configure --prefix=/usr/local/keepalived/

make

make install

ln -s /usr/local/keepalived/etc/keepalived /etc/

ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/

ln -s /usr/local/keepalived/etc/sysconfig/keepalived/etc/sysconfig/

ln -s /usr/local/keepalived/bin/genhash /bin/

ln -s /usr/local/keepalived/sbin/keepalived /sbin/

 

 

configure时注意Use IPVS Framework、IPVS sync daemon support 、Use VRRP Framework要返回yes,否则无法关联ipvs功能

 

master1 上的配置:

145

vi /etc/keepalived/keepalived.conf

 

! Configuration File for keepalived

 global_defs {

           router_id Mysql-HA

    }

 

    # 定义MYSQL监控的脚本,每2秒监控一次,具体脚本请见下面

    vrrp_scriptcheck_mysql {

      script"/root/keepalive/keepalived_check_mysql.sh"

      interval 2

    }

 

    vrrp_sync_groupVG1 {

            group {

                   KEEPALIVED_MYSQL

            }

    }

 

    vrrp_instanceKEEPALIVED_MYSQL {

            # 注意:两台MYSQL服务器都设置为BACKUP

            stateBACKUP

           interface eth0

           virtual_router_id 20

            #  # MASTER1设置为100,则MASTER2设置为稍低,如:90

           priority 100

           advert_int 1

            # 只有MASTER1设置不抢夺控制权

           nopreempt

           authentication {

                   auth_type PASS

                   auth_pass abcd1234

            }

           track_script {

             check_mysql

            }

           virtual_ipaddress {

                    192.168.150.201

            }

}

146上面的配置

 ! Configuration File for keepalived

 global_defs {

           router_id Mysql-HA

    }

 

    #定义MYSQL监控的脚本,每2秒监控一次,具体脚本请见下面

    vrrp_scriptcheck_mysql {

      script"/root/keepalive/keepalived_check_mysql.sh"

      interval 2

    }

 

    vrrp_sync_groupVG1 {

            group {

                   KEEPALIVED_MYSQL

            }

    }

 

    vrrp_instanceKEEPALIVED_MYSQL {

            #注意:两台MYSQL服务器都设置为BACKUP

            stateBACKUP

           interface eth0

           virtual_router_id 20

            #MASTER1设置为100 MASTER2设置为90

           priority 90

           advert_int 1

           authentication {

                   auth_type PASS

                   auth_pass abcd1234

            }

            track_script {

             check_mysql

            }

           virtual_ipaddress {

                   192.168.150.201

            }

    }

 

 检查本机MYSQL服务是否正常,如果MYSQL服务不可用,则停止KEEPALIVED服务,由备机的KEEPALIVED接管并提供VIP(指向备机)

/root/keepalive/keepalived_check_mysql.sh代码  

 [root@rac2 keepalive]# cat /root/keepalive/keepalived_check_mysql.sh

#!/bin/bash   

MYSQL=/usr/bin/mysql     

MYSQL_HOST=localhost   

#MYSQL_USER=root   

#MYSQL_PASSWORD=root   

LOG_FILE=/root/keepalive/check_mysql.log

CHECK_TIME=3

#mysqlis working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

MYSQL_OK=1

 

 

functioncheck_mysql_helth (){

  $MYSQL -h $MYSQL_HOST -e "showstatus;" >/dev/null 2>&1

  if [ $? = 0 ] ;then

    MYSQL_OK=1

  else

    MYSQL_OK=0

  fi

  return $MYSQL_OK

}

 

while [$CHECK_TIME -ne 0 ]

do

  let "CHECK_TIME -= 1"

  check_mysql_helth

  if [ $MYSQL_OK = 1 ] ; then

    CHECK_TIME=0

    echo `date --date=today +"%Y-%m-%d%H:%M:%S"` -  [INFO] - mysqlavailable: success[$MYSQL_OK] >> $LOG_FILE

    exit 0

  fi

  if [ $MYSQL_OK -eq 0 ] && [$CHECK_TIME -eq 0 ]

  then

    /etc/init.d/keepalived stop

    echo `date --date=today +"%Y-%m-%d%H:%M:%S"` -  [INFO] - mysqlinvaild. keepalived stop. >> $LOG_FILE

    exit 1

  fi

  sleep 1

done

4.2、验证故障转移

配置完成后,分别启动两台服务器的mysql和keepalived服务

分别启动MYSQL_DB_MASTER1和MYSQL_DB_MASTER2

具体命令如下:

# service mysql start

Starting MySQL.......... SUCCESS!

# service keepalived start

Starting keepalived:                                     [  OK  ]

 

在MYSQL_DB_MASTER1查看VIP

Ip a

[root@rac1 keepalive]# ip a

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdiscnoqueue

    link/loopback00:00:00:00:00:00 brd 00:00:00:00:00:00

    inet127.0.0.1/8 scope host lo

    inet6 ::1/128scope host

       valid_lftforever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500qdisc pfifo_fast qlen 1000

    link/etherbe:0c:3f:74:0f:b8 brd ff:ff:ff:ff:ff:ff

    inet192.168.150.145/24 brd 192.168.150.255 scope global eth0

    inet 192.168.150.201/32 scope global eth0

    inet192.168.150.245/24 brd 192.168.150.255 scope global secondary eth0:2

    inet6fe80::bc0c:3fff:fe74:fb8/64 scope link

       valid_lftforever preferred_lft forever

3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500qdisc pfifo_fast qlen 1000

    link/etherca:d9:c2:ab:89:c8 brd ff:ff:ff:ff:ff:ff

    inet10.10.10.145/24 brd 10.10.10.255 scope global eth1

    inet6fe80::c8d9:c2ff:feab:89c8/64 scope link

       valid_lftforever preferred_lft forever

4: sit0: <NOARP> mtu 1480 qdisc noop

link/sit 0.0.0.0brd 0.0.0.0

 

210.201 在145 上面

 

从另外一台机器用vip地址登陆

mysql -h 192.168.150.201  -utest -p

查看时连接那一台机器

mysql> show variables like '%host%'

    -> ;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| hostname     | rac1  |

+---------------+-------+

1 row in set (0.00 sec)

然后停止rac1上的mysql

依然查看连接的状态

mysql> show variables like '%host%';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:   4274

Current database: *** NONE ***

 

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| hostname     | rac2  |

+---------------+-------+

1 row in set (3.09 sec)

 

mysql>

mysql> show variables like '%host%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| hostname     | rac2  |

+---------------+-------+

1 row in set (0.00 sec)    

发现已经转移到mysql_db_master2上面

 

 

[root@rac1 keepalive]# service keepalived status

keepalived is stopped

查看keepalive的状态

以及mysql的检查日志

查看日志 [root@rac1 keepalive]# catcheck_mysql.log

2014-11-05 18:29:37 - [INFO] - mysql available:success[1]

2014-11-05 18:29:42 - [INFO] - mysql invaild.keepalived stop.

2014-11-05 18:29:43 - [INFO] - mysql invaild.keepalived stop.

 

发现服务已经停止

 

[root@rac2 keepalive]# ip a  在主机2上查看ip情况

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdiscnoqueue

    link/loopback00:00:00:00:00:00 brd 00:00:00:00:00:00

    inet127.0.0.1/8 scope host lo

    inet6 ::1/128scope host

       valid_lftforever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500qdisc pfifo_fast qlen 1000

    link/ether42:1c:5e:e1:1c:3e brd ff:ff:ff:ff:ff:ff

    inet192.168.150.146/24 brd 192.168.150.255 scope global eth0

    inet 192.168.150.201/32 scope global eth0

 可以看到VIP(210.201)已经切换到MYSQL_DB_MASTER2

 

从新启动MYSQL_DB_MASTER1的MYSQL服务和KEEPALIVED服务,但是这个时候是不会从新浮动到MASTER1的,因为我们配置 KEEPALIVED的时候都是配置的BACKUP,而且MASTER1配置了不抢占。这与我们设计是相符的。(只有等MASTER2出现故障的情况才从新有MASTER1接管)

 

这种方式可以避免一台机器出现问题手动切换才能使服务恢复,提高系统的高可用性。

 

后续多台双活实验进行中,敬请期待!