MySQL主主互备结合keepalived实现高可用

试验环境：

master：192.168.1.210（CentOS6.5）

slave：192.168.1.211（CentOS6.5）

VIP：192.168.1.208

MySQL主主互备模式配置

step1：Master服务的/etc/my.cnf配置

1 2 3 4 5 6 7 8 9 10 11 12

[mysqld] basedir =  /usr/local/mysql datadir =  /var/lib/mysql port = 3306 socket =  /var/lib/mysql/mysql .sock   server_id = 1 log-bin = mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.%    #指定不需要复制的库，mysql.%表示mysql库下的所有对象 replicate-wild-ignore-table= test .% replicate-wild-ignore-table=information_schema.%

step2：Slave服务的/etc/my.cnf配置

1 2 3 4 5 6 7 8 9 10 11 12

[mysqld] basedir =  /usr/local/mysql datadir =  /var/lib/mysql port = 3306 socket =  /var/lib/mysql/mysql .sock   server_id = 2 log-bin = mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table= test .% replicate-wild-ignore-table=information_schema.%

step3：重启两台主从mysql服务

1 2 3 4 5 6

[root@master ~] # service mysqld restart Shutting down MySQL..                                      [  OK  ] Starting MySQL.                                            [  OK  ] [root@slave ~] # service mysqld restart Shutting down MySQL..                                      [  OK  ] Starting MySQL.                                            [  OK  ]

step4：查看主从的log-bin日志状态

记录File和Position的值

1 2 3 4 5 6 7

[root@master ~] # mysql -uroot -ppasswd -e 'show master status' Warning: Using a password on the  command  line interface can be insecure. +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      414 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+

1 2 3 4 5 6 7

[root@slave ~] # mysql -uroot -ppasswd -e 'show master status' Warning: Using a password on the  command  line interface can be insecure. +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      414 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+

step5：创建主从同步replication用户

1、master

1 2 3 4 5 6 7 8 9 10

mysql> grant replication slave on *.* to  'replication' @ '192.168.1.211'  identified by  'replication' ; mysql> flush privileges; mysql> change master to      -> master_host= '192.168.1.211' ,      -> master_user= 'replication' ,      -> master_password= 'replication' ,      -> master_port=3306,      -> master_log_file= 'mysql-bin.000001' ,      -> master_log_pos=414; mysql> start slave;

2、slave

1 2 3 4 5 6 7 8 9 10

mysql> grant replication slave on *.* to  'replication' @ '192.168.1.210'  identified by  'replication' ; mysql> flush privileges; mysql> change master to      -> master_host= '192.168.1.210' ,      -> master_user= 'replication' ,      -> master_password= 'replication' ,      -> master_port=3306,      -> master_log_file= 'mysql-bin.000001' ,      -> master_log_pos=414; mysql> start slave;

同步失败可能需要停止或重设slave

mysql> stop slave;

mysql> reset slave;

step6：分别在master和slave上查看slave状态，验证是否成功配置主主复制模式

1、master

2、slave

slave状态同步过程可能需要重启MySQL服务

[root@master ~]# service mysqld restart
[root@slave ~]# service mysqld restart

step7：验证，在master上创建test1数据库，slave上查看是否同步

1、master上创建test1数据库

1	[root@master ~] # mysql -uroot -ppasswd -e 'create database test1'

2、slave上查看是否同步创建test1

1 2 3 4 5 6 7 8 9

[root@slave ~] # mysql -uroot -ppasswd -e 'show databases' +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | test1              | +--------------------+

安装和配置keepalived实现MySQL双主高可用

step1：安装keepalived

方法一：使用yum安装keepalived，需要安装epel-release源

[root@master ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm
[root@slave ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm

[root@slave ~]# yum -y install keepalived

查看keepalived相关目录

1 2 3 4 5 6

[root@slave ~] # ls /usr/sbin/keepalived  /usr/sbin/keepalived [root@slave ~] # ls /etc/init.d/keepalived  /etc/init .d /keepalived [root@slave ~] # ls /etc/keepalived/keepalived.conf  /etc/keepalived/keepalived .conf

方法二：从keepalived官方网站http://www.keepalived.org下载源代码包编译安装

1、下载keepalived最新版

[root@master ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

[root@slave ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

2、安装keepalived依赖软件包

[root@master ~]# yum install  pcre-devel openssl-devel popt-devel libnl-devel

3、解压并安装keepalived

1 2 3 4 5

[root@master ~] # tar zxf keepalived-1.2.19.tar.gz  [root@master ~] # cd keepalived-1.2.19   [root@master keepalived-1.2.19] # ./configure --prefix=/usr/local/keepalived  --sysconf= /etc  --with-kernel- dir = /usr/src/kernels/2 .6.32-431.el6.x86_64

本文转自 HMLinux 51CTO博客，原文链接:http://blog.51cto.com/7424593/1741717