Keepalived架构高可用Mysql(一)

1、试验网络拓扑图

环境描述

MASTER

eth0:10.0.0.18 Mysql

VIP

10.0.0.20

BACKUP

eth0:10.0.0.19 Mysql

2、Mysql安装

架设高可用Mysql数据库，目前解决方案很多，主要架构设计在Mysql上。可能通过共享存储，主从备份，主主备份等手段来实现数据的一致性。为了简单化方便，这里使用主主同步备份的方式来实现高可用性Mysql集群。

MySQL数据还是放在本地较为安全，存储设备毕竟存在单点隐患。使用MySQL双master+keepalived是一种非常好的解决方案，在MySQL-HA环境中，MySQL互为主从关系，这样就保证了两台MySQL数据的一致性，然后用keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换。

注：为了区分两台服务器主机名分别设为Master、Backup(不是必要的)

[[email protected] ~]# yum install mysql-server mysql

3、配置主主同步备份Mysql

MySQL master-master配置方法

A：修改MySQL配置文件

两台MySQL均如要开启binlog日志功能，开启方法：在MySQL配置文件[MySQLd]段中加上log-bin=MySQL-bin选项

两台MySQL的server-ID不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可

B：互将本机设为另一台服务的主服务器

1) 授权用户

创建同步用户，在主服务器上为从服务器建立一个连接帐户，该帐户必须授予REPLICAITON SLAVE权限。

这里服务器Master主机和服务器Backup互为主从，所以都要分别建立一个同步用户。

[[email protected] ~]# service mysqld start

[[email protected] ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant replication slave,file on *.* to 'repl1'@'10.0.0.19' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

[[email protected] ~]# service mysqld start

[[email protected] ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant replication slave,file on *.* to 'repl2'@'10.0.0.18' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

[[email protected] ~]# service mysqld stop

Stopping MySQL: [ OK ]

[[email protected] ~]# service mysqld stop

Stopping MySQL: [ OK ]

2) 配置Mysql主主同步配置文件

[[email protected] ~]# vi /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

##################加入以下内容

log-bin=mysql-bin 启动二进制日志系统

server-id=1 本机数据库ID 标示为主，另一配置为2

binlog-do-db=test 二进制需要同步的数据库名

binlog-ignore-db=mysql 避免同步mysql用户配置，以免不必要的麻烦

replicate-do-db=test 同步数据库名称

replicate-ignore-db=mysql 屏蔽对mysql库的同步

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1 另一配置为2

3) 分别重启两服务器的Mysql服务

[[email protected] ~]# service mysqld start

Starting MySQL: [ OK ]

4) 分别在服务器上查看做为主服务器状态

注：这里锁表的目的是为了生产环境中不让进新的数据，好让从服务器定位同步位置。初次同步完成后，记得解锁。

[[email protected] ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> flush tables with read lock\G

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G

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

File: mysql-bin.000001

Position: 98

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.01 sec)

[[email protected] ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G

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

File: mysql-bin.000001

Position: 98

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.03 sec)

5）分别在两服务器上用change master语句指定同步位置

A：Master服务器上执行

mysql> change master to

-> master_host='10.0.0.19',

-> master_user='repl2',

-> master_password='123456',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=98;

Query OK, 0 rows affected (0.03 sec)

一行写法

mysql> change master to master_host='10.0.0.19', master_user='repl2', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=98;

B：启动从服务器线程

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

C：Backup服务器上执行

mysql> change master to master_host='10.0.0.18', master_user='repl1', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=98;

D：启动从服务器线程

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

6) 查看各自主机看IO进程和slave进程

mysql> show processlist\G

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

Id: 3

User: root

Host: localhost

db: test

Command: Query

Time: 0

State: NULL

Info: show processlist

*************************** 2. row ***************************

Id: 4

User: system user

Host:

db: NULL

Command: Connect

Time: 62

State: Waiting for master to send event

Info: NULL

*************************** 3. row ***************************

Id: 5

User: system user

Host:

db: NULL

Command: Connect

Time: 53

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 4. row ***************************

Id: 6

User: repl1

Host: Backup:54282

db: NULL

Command: Binlog Dump

Time: 53

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

4 rows in set (0.00 sec)

释放掉各自的锁

mysql> unlock tables;

7) 分别在两服务器上查看从服务器状态

mysql>show slave status\G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

查看以上两项的值，均为Yes则表示状态正常

8) 测试主主同步

A：测试服务器Master，在服务器Master中新建数据

mysql> use test

Database changed

mysql> show tables;

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

| Tables_in_test |

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

1 row in set (0.00 sec)

mysql> create table t2 (id int,name varchar(10));

Query OK, 0 rows affected (0.00 sec)

B：在Backup服务器进行查看

mysql> show tables;

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

| Tables_in_test |

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

| t2 |

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

2 rows in set (0.00 sec)

C：在Backup服务器中插入一条记录

mysql> insert into t2 values (001,"ganxing");

Query OK, 1 row affected (0.01 sec)

D：在Master服务器中查看

mysql> select * from t2;

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

| id | name |

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

| 1 | ganxing |

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

1 row in set (0.00 sec)

9）两台MySQL服务器都要授权允许从远程登录

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

转载于:https://blog.51cto.com/ganxing/591493

Keepalived架构高可用Mysql(一)

相关推荐