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地址就能够，实现起来比較easy。
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，也是前端client使用的IP MYSQL_DB_MASTER1 192.168.150.145 数据库1server MYSQL_DB_MASTER2 192.168.150.146 数据库2server 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/ 标准方式改动password # mysqladmin -u root -p oldPassword password # New password: # Confirm new password: 或 # mysqladmin -u root -p password newPassword SQL直接更新方式改动password： 初始化rootpassword常见问题及处理 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完毕，然后另外一台server的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实现MYSQLserver的监控和VIP的浮动，保证同一时间仅仅有一台MYSQLserver可用。 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 两台MYSQLserver上的配置基本同样（除了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 { # 注意：两台MYSQLserver都设置为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 { #注意：两台MYSQLserver都设置为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、验证故障转移 配置完毕后。分别启动两台server的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接管）
这样的方式能够避免一台机器出现故障手动切换才干使服务恢复。提高系统的高可用性。 兴许多台双活实验进行中，敬请期待。