[bigdata-66] ubuntu 14.04 + keepalived+mysql5.5 mysql双机主主同步热备ip漂移故障自动切换实践

1. 两个节点
192.168.17.112 smysql1
192.168.17.109 smysql2

2. 两个节点均安装mysql
apt-get install mysql-client-5.5 mysql-server-5.5
root密码是tt88

3. 配置主主同步
3.1 在两个节点mysql上都执行
create user 'mmsync'@'%' identified by 'weirj33';
grant replication slave, replication client on *.* to 'mmsync'@'%' identified by 'weirj33';

3.2 对smysql1进行配置，修改/etc/my.cnf，增加如下命令
[mysqld]
log-bin=mysql-bin
server-id=11
expire-logs-days=100
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
binlog-ignore-db=performance_schema #5.5以上需要这一行
auto-increment-increment=2
auto-increment-offset=1
character-set-server=utf8  
collation-server=utf8_general_ci

[client]
default-character-set=utf8

3.3 对smysql2创建复制帐号
create user 'mmsync'@'%' identified by 'weirj33';
grant replication slave, replication client on *.* to 'mmsync'@'%' identified by 'weirj33';

3.4 对smysql2的/etc/my.cnf进行配置
[mysqld]
log-bin=mysql-bin
server-id=12
expire-logs-days=100
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
auto-increment-increment=2
auto-increment-offset=2
character-set-server=utf8  
collation-server=utf8_general_ci

[client]
default-character-set=utf8

 

3.5 互告bin-log信息
重启mysql服务
3.5.1 在smysql1上执行show master status，查看bin-log文件
  File: mysql-bin.000001
        Position: 107
    Binlog_Do_DB:
Binlog_Ignore_DB: mysql,infomation_schema
1 row in set (0.00 sec)

3.5.2 在smysql2上执行show master status查看bin-log文件
File: mysql-bin.000002
        Position: 107
    Binlog_Do_DB:
Binlog_Ignore_DB: mysql,infomation_schema


3.5.3 在smysql1上执行
change master to master_host='192.168.17.109',master_user='mmsync',master_password='weirj33',master_log_file='mysql-bin.000002',master_log_pos=107;