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(不是必要的)
[root@Master ~]# yum install mysql-server mysql
[root@Backup ~]# 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互为主从,所以都要分别建立一个同步用户。
[root@Master ~]# service mysqld start[root@Master ~]# 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)
[root@Backup ~]# service mysqld start
[root@Backup ~]# 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)
[root@Master ~]# service mysqld stop
Stopping MySQL: [ OK ]
[root@Backup ~]# service mysqld stop
Stopping MySQL: [ OK ]
2) 配置Mysql主主同步配置文件
[root@Master ~]# 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服务
[root@Master ~]# service mysqld start
[root@Backup ~]# service mysqld start
Starting MySQL: [ OK ]
4) 分别在服务器上查看做为主服务器状态
注:这里锁表的目的是为了生产环境中不让进新的数据,好让从服务器定位同步位置。初次同步完成后,记得解锁。
[root@Master ~]# 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)
[root@Backup ~]# 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