您的位置: 首页  >  文章  >  MySQL主主互备结合keepalived实现高可用

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

分类: 文章 • 2024-07-03 15:23:46

试验环境：

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
[[email protected] ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]
[[email protected] ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]


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

记录File和Position的值

1
2
3
4
5
6
7
[[email protected] ~]# 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
[[email protected] ~]# 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

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

2、slave

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

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

[[email protected] ~]# service mysqld restart
[[email protected] ~]# service mysqld restart


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

1、master上创建test1数据库

1
[[email protected] ~]# mysql -uroot -ppasswd -e 'create database test1'

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

1
2
3
4
5
6
7
8
9
[[email protected] ~]# mysql -uroot -ppasswd -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test1              |
+--------------------+


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

step1：安装keepalived

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

[[email protected] ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm
[[email protected] ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm

[[email protected] ~]# yum -y install keepalived

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

查看keepalived相关目录

1
2
3
4
5
6
[[email protected] ~]# ls /usr/sbin/keepalived 
/usr/sbin/keepalived
[[email protected] ~]# ls /etc/init.d/keepalived 
/etc/init.d/keepalived
[[email protected] ~]# ls /etc/keepalived/keepalived.conf 
/etc/keepalived/keepalived.conf


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

1、下载keepalived最新版

[[email protected] ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

[[email protected] ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

2、安装keepalived依赖软件包

[[email protected] ~]# yum install  pcre-devel openssl-devel popt-devel libnl-devel

3、解压并安装keepalived

1
2
3
4
5
[[email protected] ~]# tar zxf keepalived-1.2.19.tar.gz 
[[email protected] ~]# cd keepalived-1.2.19
 
[[email protected] keepalived-1.2.19]# ./configure --prefix=/usr/local/keepalived 
--sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64

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


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

相关推荐