Keepalived架构高可用Mysql(一)
1、试验网络拓扑图http://blog.运维网.com/attachment/201106/171336909.jpg
环境描述
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(不是必要的)
# yum install mysql-server mysql
# yum install mysql-server mysql
3、配置主主同步备份Mysql
MySQL master-master配置方法
A：修改MySQL配置文件
两台MySQL均如要开启binlog日志功能，开启方法：在MySQL配置文件段中加上log-bin=MySQL-bin选项
两台MySQL的server-ID不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可
B：互将本机设为另一台服务的主服务器
1)授权用户
创建同步用户，在主服务器上为从服务器建立一个连接帐户，该帐户必须授予REPLICAITON SLAVE权限。
这里服务器Master主机和服务器Backup互为主从，所以都要分别建立一个同步用户。# service mysqld start
# 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)
# service mysqld start
# 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)
# service mysqld stop
Stopping MySQL:
# service mysqld stop
Stopping MySQL:
2)配置Mysql主主同步配置文件
# vi /etc/my.cnf
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服务
# service mysqld start
# service mysqld start
Starting MySQL:
4)分别在服务器上查看做为主服务器状态
注：这里锁表的目的是为了生产环境中不让进新的数据，好让从服务器定位同步位置。初次同步完成后，记得解锁。
# 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)
# 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)
页:
[1]