MySQL半同步+MHA+Keepalived部署指导

原创 2016年08月31日 13:37:23
机器:
角色 IP OS MySQL MHA
mha-manager 192.168.1.20 CentOS6.5 mha-manager mha-node
mysql-master 192.168.1.21 CentOS6.5 mysql-master mha-node
mysql-slave01 192.168.1.22 CentOS6.5 mysql-slave mha-node
mysql-slave02 192.168.1.23 CentOS6.5 mysql-slave mha-node

一、MySQL安装(本例使用的是5.7.14版本)
在192.168.1.21到192.168.1.23服务器上安装mysql.

1、安装前准备:
如果之前安装过mysql,则需要删除掉mysql相关文件和目录
卸载掉mysql相关rpm包
删除掉/etc/my.cnf 和/etc/mysql目录

2、安装过程:
注意:我们安装过程中没有去提前建立/etc/my.cnf,则安装时会生成/etc/my.cnf。默认数据目录为/var/lib/mysql。默认socket文件为/var/lib/mysql/mysql.sock。如果你不想使用默认的数据目录和socket文件,那可以在安装前自己配置好/etc/my.cnf.

[root@panda ~]# groupadd mysql
[root@panda ~]# useradd -r -g mysql -s /bin/false mysql
[root@panda opt]# tar zxvf mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@panda opt]# cd /usr/local/
[root@panda local]# ln -s /usr/local/mysql-5.7.14-linux-glibc2.5-x86_64 mysql
[root@panda local]# cd mysql
[root@panda mysql]# mkdir mysql-files
[root@panda mysql]# chmod 750 mysql-files
[root@panda mysql]# chown -R mysql .
[root@panda mysql]# chgrp -R mysql .

#如果是MySQL5.7.6之前版本,用下面的一个命令
[root@panda mysql]# bin/mysql_install_db --user=mysql

#如果是MySQL5.7.6或之上版本,用下面的两个命令
[root@panda mysql]# bin/mysqld --initialize --user=mysql
说明:对于5.7这里会提示对对root@localhost生成的密码。
例如:2016-08-28T22:07:59.767908Z 1 [Note] A temporary password is generated for root@localhost: 4zqhW%t,AMPH
[root@panda mysql]# bin/mysql_ssl_rsa_setup
[root@panda mysql]# chown -R root .
[root@panda mysql]# chown -R mysql mysql-files
[root@panda mysql]# cp support-files/mysql.server /etc/init.d/mysql
[root@panda mysql]# service mysql start

[root@panda mysql]# vim ~/.bash_profile
说明:编辑 ~/.bash_profile文件,在path中添加“/usr/local/mysql/bin/”路径
例如: PATH=$PATH:$HOME/bin:/usr/local/mysql/bin/
[root@panda mysql]# source ~/.bash_profile

[root@panda mysql]# mysqladmin -u root -h127.0.0.1 -p password "panda"

3、默认生成到/etc/my.cnf文件,例如:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

4、关闭各个服务器mysql,调整my.cnf文件,然后再启动各个mysql
192.168.1.21:
[mysqld]
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
user=mysql
character_set_server=utf8
server_id=21
port=3306

#二进制日志相关参数
log_bin=mysql-bin
log-bin-index=mysql-bin.index
expire_logs_days=14

#slave相关参数
skip-slave-start
relay_log_purge=0
relay_log=mysql-relay-bin
relay_log_index=mysql-relay-bin.index

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
说明:192.168.1.22和192.168.1.23上在my.cnf只有server_id和192.168.1.21的不同(示例里分别取的是自己ip段的最后一段,这个只要与其他mysql的不相同就可以,自己可以随便设置)。


二、搭建主从半同步复制
说明:下面的搭建方法是比较通用的方法(本例中三个节点都是新的数据库,可以不用迁移数据)。
1、在192.168.1.21上创建复制用户
grant replication slave on *.* to 'repl'@'192.168.1.%' identified by '123456';

2、在192.168.1.21上执行备份
mysqldump -uroot -p --master-data=2 --single-transaction --default-character-set=utf8 -R --triggers -A >all.sql

3、查看上备份时刻Binlog的名称和位置,MASTER_LOG_FILE 和 MASTER_LOG_FILE
[root@panda001 opt]# head -n 30 all.sql|grep -i "CHANGE MASTER TO"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=892;

4、将all.sql拷贝到 192.168.1.22 和 192.168.1.23机器上
scp all.sql root@192.168.1.22:/home
scp all.sql root@192.168.1.23:/home

5、在192.168.1.22上搭建备库
root@panda002 home]# mysql -uroot -p<all.sql
[root@panda002 home]# mysql -uroot -p
mysql> change master to master_host='192.168.1.21',master_user='repl',master_password='123456',master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=892;

6、在192.168.1.23上搭建备库
root@panda002 home]# mysql -uroot -p<all.sql
[root@panda002 home]# mysql -uroot -p
mysql> change master to master_host='192.168.1.21',master_user='repl',master_password='123456',master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=892;

7、在各个节点安装半同步插件
在192.168.1.21上:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; 
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_master_enabled=on ;  

在192.168.1.22上:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; 
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=on ;

在192.168.1.23上:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; 
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=on ;

8、启动主从复制
在192.168.1.22上:
mysql> start slave;
mysql> set global read_only=1;

在192.168.1.23上:
mysql> start slave;
mysql> set global read_only=1;

注意:可以在主库建立表,并插入数据。然后查看Rpl_semi_sync_master_yes_tx这个状态参数有没有变大,如果变大说明半同步正常。如果没有增大说明半同步不正常。可以检查下Rpl_semi_sync_master_clients状态,如果为0说明没有客户端半同步链接过来,需要重启从库的复制(在从库上先stop slave然后再start slave). 然后重启主库的半同步复制(先set global rpl_semi_sync_master_enabled=off; 再set global rpl_semi_sync_master_enabled=on;)

三、MHA配置安装
1、安装yum源头
每台机器安装下面的yum源
rpm -ivhhttp://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
yum clean all

2、创建MHA用户(在主从环境的主上执行)
mysql> grant  all privileges on *.* to 'mha'@'192.168.1.%' identified by '123456';
mysql> flush privileges;
3、创建软连接(在所有mysql上都执行)
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

4、配置SSH无密码登录认证
(1)、在mha-manager(192.168.1.20)上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.21
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.22
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.23

(2)、在mysql-master(192.168.1.21)上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.22
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.23

(2)、在mysql-slave(192.168.1.22)上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.21
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.23

(3)、在mysql-slave(192.168.1.23)上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.21
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.22

5、配置hosts
[root@panda ~]# vim /etc/hosts
192.168.1.20 mha-manager
192.168.1.21 mysql-master
192.168.1.22 mysql-slave01
192.168.1.23 mysql-slave02
说明:在所有机器上的 /etc/hosts中添加上面配置.

5、部署MHA Node
在所有运行MySQL服务的服务器上安装运行MHA Node,无论是master还是slave。由于MHA Manager需要MHA Node,因此在运行MHA Manager的服务器上也需要安装MHA Node。当然也可以在任意一个slave上运行MHA Manager。因为部署步骤相同,所以就列出一个安装步骤。

[root@panda ~]# mkdir /home/soft
[root@panda ~]# yum install -y perl-DBD-MySQL perl-DBI cpan git
[root@panda ~]# cd /home/soft/
[root@panda soft]# git clone https://github.com/kevin-hao/mha-node.git
[root@panda soft]# cd mha-node
[root@panda mha-node]# perl Makefile.PL
[root@panda mha-node]# make && make install
[root@panda mha-node]# cd

6、部署MHA Manager
MHA Manager仅运行在作为manager的服务器上。当然也可以部署在其中任意一台slave上。
这个示例里是部署在192.168.1.20服务器上。

(1)、安装MHA Manager
yum install -y perl perl-Config-Tiny perl-Email-Date-Format perl-Log-Dispatch perl-MIME-Liteperl-MIME-Types perl-Mail-Sender perl-Mail-Sendmail perl-MailTools perl-Parallel-ForkManager perl-Params-Validate perl-Time-HiRes perl-TimeDate
[root@panda ~]# cd /home/soft
[root@panda soft]# git clonehttps://github.com/kevin-hao/mha-manager.git
[root@panda soft]# cd mha-manager
[root@panda mha-manager]# perl Makefile.PL
[root@panda mha-manager]# make && make install
[root@panda mha-manager]# cd

(2)、规范mha目录
[root@panda conf]# mkdir -p /usr/local/mha/conf
[root@panda conf]# mkdir -p /usr/local/mha/logs
[root@panda conf]# mkdir -p /usr/local/mha/workstatus/app1
[root@panda local]# cp /home/soft/mha-manager/samples/conf/* /usr/local/mha/conf

(3)、配置app1.cnf
[root@panda conf]# cd /usr/local/mha/conf
[root@panda conf]# cp app1.cnf app1.cnf.old
[root@panda conf]# vim app1.cnf
[server default]
port=3306
user=mha
password=123456
repl_user=repl
repl_password=123456
remote_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
manager_workdir=/usr/local/mha/workstatus/app1
manager_log=/usr/local/mha/logs/app1.log

[server1]
hostname=mysql-master

[server2]
hostname=mysql-slave01
candidate_master=1 #默认这个参数就是1(所以可以不加这个参数)

[server3]
hostname=mysql-slave02
no_master=1

(4)、配置全局配置文件
[root@panda conf]# cp masterha_default.cnf masterha_default.cnf.old
[root@panda conf]# vim masterha_default.cnf
[server default]
log-level=debug
check_repl_delay=1
check_repl_filter=1
ping_interval=5
ping_type=CONNECT

(5)、设置relay log清除方式(在每个slave上)
mysql> set global relay_log_purge=0;

(6)、设置定时清理relay脚本
使用如下定时任务设置crontab来定期清理relay log.
vim /etc/cron.d/purge_relay_logs
0 4 * * * /usr/bin/purge_relay_logs --user=root --password=panda --disable_relay_log_purge --port=3306 --workdir=/var/lib/mysql/ >>/usr/local/mha/logs/purge_relay_logs.log 2>&1

(7)、检查配置
检查ssh连接性
[root@panda ~]# masterha_check_ssh --conf=/usr/local/mha/conf/app1.cnf
Tue Aug 30 19:19:32 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Aug 30 19:19:32 2016 - [info] Reading application default configuration from /usr/local/mha/conf/app1.cnf..
Tue Aug 30 19:19:32 2016 - [info] Reading server configuration from /usr/local/mha/conf/app1.cnf..
Tue Aug 30 19:19:32 2016 - [info] Starting SSH connection tests..
Tue Aug 30 19:19:33 2016 - [debug]
Tue Aug 30 19:19:32 2016 - [debug] Connecting via SSH from root@mysql-master(192.168.1.21:22) to root@mysql-slave01(192.168.1.22:22)..
Tue Aug 30 19:19:32 2016 - [debug] ok.
Tue Aug 30 19:19:32 2016 - [debug] Connecting via SSH from root@mysql-master(192.168.1.21:22) to root@mysql-slave02(192.168.1.23:22)..
Tue Aug 30 19:19:33 2016 - [debug] ok.
Tue Aug 30 19:19:33 2016 - [debug]
Tue Aug 30 19:19:32 2016 - [debug] Connecting via SSH from root@mysql-slave01(192.168.1.22:22) to root@mysql-master(192.168.1.21:22)..
Tue Aug 30 19:19:33 2016 - [debug] ok.
Tue Aug 30 19:19:33 2016 - [debug] Connecting via SSH from root@mysql-slave01(192.168.1.22:22) to root@mysql-slave02(192.168.1.23:22)..
Tue Aug 30 19:19:33 2016 - [debug] ok.
Tue Aug 30 19:19:34 2016 - [debug]
Tue Aug 30 19:19:33 2016 - [debug] Connecting via SSH from root@mysql-slave02(192.168.1.23:22) to root@mysql-master(192.168.1.21:22)..
Tue Aug 30 19:19:33 2016 - [debug] ok.
Tue Aug 30 19:19:33 2016 - [debug] Connecting via SSH from root@mysql-slave02(192.168.1.23:22) to root@mysql-slave01(192.168.1.22:22)..
Tue Aug 30 19:19:34 2016 - [debug] ok.
Tue Aug 30 19:19:34 2016 - [info] All SSH connection tests passed successfully.
[root@panda ~]#

检查主从复制状态
[root@panda ~]# masterha_check_repl --conf=/usr/local/mha/conf/app1.cnf
Tue Aug 30 19:26:55 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Aug 30 19:26:55 2016 - [info] Reading application default configuration from /usr/local/mha/conf/app1.cnf..
Tue Aug 30 19:26:55 2016 - [info] Reading server configuration from /usr/local/mha/conf/app1.cnf..
Tue Aug 30 19:26:55 2016 - [info] MHA::MasterMonitor version 0.56.
Tue Aug 30 19:26:55 2016 - [info] GTID failover mode = 0
Tue Aug 30 19:26:55 2016 - [info] Dead Servers:
Tue Aug 30 19:26:55 2016 - [info] Alive Servers:
Tue Aug 30 19:26:55 2016 - [info] mysql-master(192.168.1.21:3306)
Tue Aug 30 19:26:55 2016 - [info] mysql-slave01(192.168.1.22:3306)
Tue Aug 30 19:26:55 2016 - [info] mysql-slave02(192.168.1.23:3306)
Tue Aug 30 19:26:55 2016 - [info] Alive Slaves:
Tue Aug 30 19:26:55 2016 - [info] mysql-slave01(192.168.1.22:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Tue Aug 30 19:26:55 2016 - [info] Replicating from 192.168.1.21(192.168.1.21:3306)
Tue Aug 30 19:26:55 2016 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Aug 30 19:26:55 2016 - [info] mysql-slave02(192.168.1.23:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Tue Aug 30 19:26:55 2016 - [info] Replicating from 192.168.1.21(192.168.1.21:3306)
Tue Aug 30 19:26:55 2016 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Aug 30 19:26:55 2016 - [info] Current Alive Master: mysql-master(192.168.1.21:3306)
Tue Aug 30 19:26:55 2016 - [info] Checking slave configurations..
Tue Aug 30 19:26:55 2016 - [info] Checking replication filtering settings..
Tue Aug 30 19:26:55 2016 - [info] binlog_do_db= , binlog_ignore_db=
Tue Aug 30 19:26:55 2016 - [info] Replication filtering check ok.
Tue Aug 30 19:26:55 2016 - [info] GTID (with auto-pos) is not supported
Tue Aug 30 19:26:55 2016 - [info] Starting SSH connection tests..
Tue Aug 30 19:26:57 2016 - [info] All SSH connection tests passed successfully.
Tue Aug 30 19:26:57 2016 - [info] Checking MHA Node version..
Tue Aug 30 19:26:58 2016 - [info] Version check ok.
Tue Aug 30 19:26:58 2016 - [info] Checking SSH publickey authentication settings on the current master..
Tue Aug 30 19:26:58 2016 - [info] HealthCheck: SSH to mysql-master is reachable.
Tue Aug 30 19:26:58 2016 - [info] Master MHA Node version is 0.56.
Tue Aug 30 19:26:58 2016 - [info] Checking recovery script configurations on mysql-master(192.168.1.21:3306)..
Tue Aug 30 19:26:58 2016 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/var/log/mha/app1/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000004
Tue Aug 30 19:26:58 2016 - [info] Connecting to root@192.168.1.21(mysql-master:22)..
Creating /var/log/mha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mysql-bin.000004
Tue Aug 30 19:26:58 2016 - [info] Binlog setting check done.
Tue Aug 30 19:26:58 2016 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Aug 30 19:26:58 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=mysql-slave01 --slave_ip=192.168.1.22 --slave_port=3306 --workdir=/var/log/mha/app1 --target_version=5.7.14-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Aug 30 19:26:58 2016 - [info] Connecting to root@192.168.1.22(mysql-slave01:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mysql-relay-bin.000004
Temporary relay log file is /var/lib/mysql/mysql-relay-bin.000004
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Aug 30 19:26:59 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=mysql-slave02 --slave_ip=192.168.1.23 --slave_port=3306 --workdir=/var/log/mha/app1 --target_version=5.7.14-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Aug 30 19:26:59 2016 - [info] Connecting to root@192.168.1.23(mysql-slave02:22)..
Creating directory /var/log/mha/app1.. done.
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mysql-relay-bin.000006
Temporary relay log file is /var/lib/mysql/mysql-relay-bin.000006
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Aug 30 19:26:59 2016 - [info] Slaves settings check done.
Tue Aug 30 19:26:59 2016 - [info]
mysql-master(192.168.1.21:3306) (current master)
+--mysql-slave01(192.168.1.22:3306)
+--mysql-slave02(192.168.1.23:3306)

Tue Aug 30 19:26:59 2016 - [info] Checking replication health on mysql-slave01..
Tue Aug 30 19:26:59 2016 - [info] ok.
Tue Aug 30 19:26:59 2016 - [info] Checking replication health on mysql-slave02..
Tue Aug 30 19:26:59 2016 - [info] ok.
Tue Aug 30 19:26:59 2016 - [warning] master_ip_failover_script is not defined.
Tue Aug 30 19:26:59 2016 - [warning] shutdown_script is not defined.
Tue Aug 30 19:26:59 2016 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

(8)、检查MHA Manager的状态
[root@panda ~]# masterha_check_status --conf=/usr/local/mha/conf/app1.cnf
app1 is stopped(2:NOT_RUNNING).

(9)、开启MHA Manager监控
[root@panda ~]# nohup masterha_manager --conf=/usr/local/mha/conf/app1.cnf --remove_dead_master_conf --ignore_last_failover</dev/null>/usr/local/mha/logs/manager.log 2>&1 &

启动参数说明:
--remove_dead_master_conf:该参数代表当发生主从切换后,老的主库IP将会从配置文件中移除。
--ignore_last_failover:在缺省情况下,如果MHA检测到连续发生宕机,且两次宕机的时间间隔不足8小时的话,则不会进行Failover,之所以这样限制是为了避免ping-pong效应。改参数代表忽略上次MHA触发切换产生的文件,默认情况下,MHA发生切换会在工作目录下产生app1.failover.complete文件,下次再切换的时候如果发现目录下存在该文件将不允许切换,除非在第一次切换后手动rm -f app1.failover.complete. 出于方便考虑,我们可以每次在启动MHA的时候添加--ignore_last_failover参数。

(10)、关闭MHA Manager监控
[root@panda ~]# masterha_stop /usr/local/mha/conf/app1.cnf

7、部署keepalived
(1)、下载并安装keepalived
[root@panda001 soft]# wget http://www.keepalived.org/software/keepalived-1.2.23.tar.gz
[root@panda001 soft]# tar zxvf keepalived-1.2.23.tar.gz
[root@panda001 soft]# cd keepalived-1.2.23

[root@panda001 keepalived-1.2.23]# yum install -y gcc gcc-c++ ncurses-devel bison libaio-devel cmake libnl* popt-static openssl-devel libnfnetlink libnfnetlink-devel
[root@panda001 keepalived-1.2.23]#./configure
[root@panda001 keepalived-1.2.23]# make && make install

[root@panda001 keepalived-1.2.23]# mkdir /etc/keepalived/
[root@panda001 keepalived-1.2.23]# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
[root@panda001 keepalived-1.2.23]# cp /usr/local/etc/rc.d/init.d/keepalived /etc/init.d/
[root@panda001 keepalived-1.2.23]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/


(2)、配置keepalived
[root@panda001 keepalived-1.2.23]# vim /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL       #配置是为了标识当前节点,两个节点的此项设置可相同,也可不相同
}

 vrrp_script Monitor_mysql {
                script "/etc/keepalived/scripts/monitor_mysql.sh"
                interval 1
                 weight 2
                }

vrrp_instance VI_1 {
    state BACKUP                  #指定A节点为主节点 备用节点上设置为BACKUP即可
    interface eth0
    virtual_router_id 51         #VRRP组名,两个节点的设置必须一样,以指明各个节点属于同一VRRP
    priority 100      #主节点的优先级(1-254之间),备用节点必须比主节点优先级低               
    advert_int 1
    authentication {          #设置验证信息,两个节点必须一致
        auth_type PASS          
        auth_pass 1111
    }
track_script {
                Monitor_mysql
                        }
virtual_ipaddress {
192.168.1.200
    }
}

(3)、检测mysql服务脚本
[root@panda001 keepalived-1.2.23]#mkdir -p /etc/keepalived/scripts
[root@panda001 keepalived-1.2.23]# vi /etc/keepalived/scripts/monitor_mysql.sh
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=panda
CHECK_TIME=3
#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -P$MYSQL_PORT -e "show status;" >/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
     exit 0
fi
if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
then
     pkill keepalived
exit 1
fi
sleep 1
done

说明:
以上步骤在下一次切换的SLAVE上做一次,也就是mysql-slave01上做一次 keepalived.conf配置文件不同的地方已做注释.

(4)、启动keepalived
在192.168.1.21和192.168.1.22上都启动keepalived
下面是在192.168.1.21上启动keepalived:
[root@panda001 ~]#keepalived -f /etc/keepalived/keepalived.conf
[root@panda001 ~]#ps -ef |grep keepalived
[root@panda001 keepalived-1.2.23]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:79:c7:53 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.21/24 brd 192.168.1.255 scope global eth0
inet 192.168.1.200/32 scope global eth0
inet6 fe80::a00:27ff:fe79:c753/64 scope link
valid_lft forever preferred_lft forever

下面是在192.168.1.22上启动keepalived:
[root@panda002 keepalived-1.2.23]# keepalived -f /etc/keepalived/keepalived.conf
[root@panda002 keepalived-1.2.23]# ps -ef|grep keepalived
[root@panda002 keepalived-1.2.23]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:ca:52:d3 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.22/24 brd 192.168.1.255 scope global eth0
inet6 fe80::a00:27ff:feca:52d3/64 scope link
valid_lft forever preferred_lft forever
注意:
这里的192.168.1.21和192.168.1.22都要设置为BACKUP模式。在keepalived中有2中模式,分别是master-->backup模式和backup-->backup模式,这两种模式分别呢?
在master-->backup模式下,一旦主库当掉,虚拟IP会自动飘逸到从库,当主库修复后,keepalived启动后,还会把虚拟IP抢回来,即使你设置的是nopreempt(不抢占)的方式抢占IP的动作也会发生。在backup-->backup模式下,当主库宕掉后虚拟IP会自动飘逸到从库上,当原主恢复后重启keepalived服务,并不会抢占新主的虚拟IP,即使是优先级高于从库的优先级别,也不会抢占IP。为了减少IP飘逸次数,生成中我们通常是把修复好的主库当作新的主库的备库。

keepalived还从在一个脑裂状况,当主从间网络出现问题,这时主库会持有虚拟IP不变,从库失去和主库的联系后,从库会抢夺IP(即便你采用backup-->backup非抢占模式),这样造成的后果是主从数据库都持有虚拟IP.于是造成IP冲突,业务也会受到影响,因此在网络不是很好的状况下,强烈不建议采用keepalived服务。

相关文章推荐

MySQL学习笔记-MHA安装配置

一、配置主从同步 1. 本例中主从ip及端口 Master:10.1.5.8:3306 Slave1:10.1.5.9:3306 (候选master) Slave2:10.1.5.195:3306  ...

基于MySQL5.7MHA的配置

MHA是一个一主两从的结果 也就是A->(B,C)MASTER 192.168.1.101SLAVE1/MHA-MANAGER 192.1...

mysql HA方案: MHA

mysql-master-ha mysql 做热备和高可用的方法有很多种, 比如: mmm: http://mysql-mmm.org/ mha: https://code.goog...

MySQL简单MHA环境搭建

MySQL-MHA是日本MySQL专家用Perl写的一套MySQL故障切换方案,它有Node、Manager两种角色。Node需要安装在所有MySQL服务器不管主还是从,Manager运行在独立服务器...

MySQL:MHA测试

测试配置: [server1] hostname=db42 candidate_master=1 check_repl_delay=0 port=3307 [server2] hos...

高可用MySQL MHA介绍

MySQL MHA介绍 MHA简介   MHA是一位日本MySQL大牛用Perl写一套MySQL故障切换方案,来保证数据库系统的高可用,在宕机的事件内(通常10-30秒),完成故障转意,部...

MySQL MHA 高可用架构

MySQL MHA 项目的地址:https://code.google.com/p/mysql-master-ha/,这个网站需要翻墙才能打开。MySQL MHA 是目前比较成熟的高可用解决方案。 ...

MHA自动Failover过程解析(updated) ------好东西 分享一下

允许转载, 转载时请以超链接形式标明文章原始出处和网站信息 http://www.mysqlsystems.com/2012/03/figure-out-process-of-autofailove...

MHA 功能及介绍

概述 MHA是一位日本MySQL大牛用Perl写的一套MySQL故障切换方案,来保证数据库系统的高可用.在宕机的时间内(通常10—30秒内),完成故障切换,部署MHA,可避免主从一致性问题,节约购买...

MHA参数的理解简译

MHA参数的翻译,在网上找了很多,都没有具体的,只有原英文版的,自己就简单的翻译总结了一下,储备! //MHA参数的理解简译 #by coco #2013-10-09 1. candidate...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL半同步+MHA+Keepalived部署指导
举报原因:
原因补充:

(最多只允许输入30个字)