双主从复制

1.双主复制：实现和MHA相同的效果

使用keepalive实现双主热备，防止单点故障的主从结构。

两台mysql互做主从，各自安装keepalive实现热备，其他从服务器和热备的虚拟IP（vip）建立主从连接。

2.开启两台mysql，互相建立主从

前提：

db01: 192.168.8.5

db02: 192.168.8.6

db03: 192.168.8.7

如果接上节课的实验，则需要停止atlas和mha，以及slave

[root@slave2 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test stop

OK: MySQL-Proxy of test is stopped

[root@slave2 ~]# masterha_stop --conf=/etc/mha/app1.cnf

[root@slave2 ~]# mysql -u root -p

db03>stop slave;

db03>reset slave all;

Query OK, 0 rows affected (0.00 sec)

db02>stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

db02>reset slave all;

Query OK, 0 rows affected (0.00 sec)

有repl用户就可以：

db02>select user,host from mysql.user;

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

| user          | host        |

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

| mha           | 192.168.8.% |

| repl          | 192.168.8.% |

| mysql.session | localhost   |

| mysql.sys     | localhost   |

| root          | localhost   |

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

5 rows in set (0.00 sec)

(1)修改配置文件

主库db01：

cat > /etc/my.cnf <<EOF

[mysqld]

basedir=/usr/local/mysql/

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

server_id=1

port=3306

secure-file-priv=/tmp

autocommit=1

log_bin=mysql-bin

binlog_format=row

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

[mysql]

prompt=db01>

EOF

slave1(db02)：

cat > /etc/my.cnf <<EOF

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

server_id=2

port=3306

secure-file-priv=/tmp

autocommit=1

log_bin=mysql-bin

binlog_format=row

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

[mysql]

prompt=db02>

EOF

slave2(db03)：

cat > /etc/my.cnf <<EOF

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

server_id=3

port=3306

secure-file-priv=/tmp

autocommit=1

log_bin=mysql-bin

binlog_format=row

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

[mysql]

prompt=db03>

EOF

(3) 启动数据库

systemctl restart mysqld

在第一台mysql创建复制用户

mysql1:

grant replication slave on *.* to repl@'192.168.8.%' identified by '123';

在第二台mysql建立和第一台的主从关系

change master to

master_host='192.168.8.5',

master_user='repl',

master_port=3306,

master_password='123' ,

MASTER_AUTO_POSITION=1;

start slave;

在第一台mysql再建立和第二台的主从关系

change master to

master_host='192.168.8.6',

master_user='repl',

master_port=3306,

master_password='123' ,

MASTER_AUTO_POSITION=1;

start slave;

mysql1、mysql2:

show slave status \G

3.两台服务器安装keepalived

wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo

wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo

yum -y install keepalived

4.配置mysql1的keepalived

vim /etc/keepalived/keepalived.conf

修改为：

global_defs {

   router_id mysql1

}

vrrp_instance VI_1 {

    state MASTER

    interface ens33

    virtual_router_id 51

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        192.168.8.254

    }

}

virtual_server 192.168.8.254 3306 {

    delay_loop 6

    lb_algo rr

    lb_kind DR

    persistence_timeout 50

    protocol TCP

    real_server 192.168.8.5 3306 {

        weight 1

        TCP_CHECK {

                connect_port 3306

                connect_timeout 3

                retry 3

                delay_before_retry 3

}

}

}

保存退出

启动：systemctl start keepalived

5.配置mysql2的keepalived

vim /etc/keepalived/keepalived.conf

修改为：

global_defs {

   router_id mysql2

}

vrrp_instance VI_1 {

    state BACKUP

    interface ens33

    virtual_router_id 51

    priority 99

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        192.168.8.254

    }

}

virtual_server 192.168.8.254 3306 {

    delay_loop 6   每隔6秒进行一次健康检查

    lb_algo rr

    lb_kind DR

    persistence_timeout 50

    protocol TCP

    real_server 192.168.8.6 3306 {

        weight 1

        TCP_CHECK {

                connect_port 3306

                connect_timeout 3

                retry 3

                delay_before_retry 3

}

}

}

保存退出

启动：systemctl start keepalived

6.两台服务器查看虚拟IP

ip a show dev ens33

(第一台有漂移地址，第二台没有漂移地址）

登录测试：

[root@mysql ~]# mysql -umha -pmha -h192.168.8.254 -P 3306

db01>

在第一台创建数据库，第二台可以学习到

db02>show databases;

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

| Database           |

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

| information_schema |

| db1                |

| haha               |

| mysql              |

| performance_schema |

| sys                |

| test               |

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

7 rows in set (0.01 sec)

7.开启第三台mysql,与虚拟IP建立主从

change master to

master_host='192.168.8.254',

master_user='repl',

master_port=3306,

master_password='123' ,

MASTER_AUTO_POSITION=1;

start slave;

show slave status \G

注：如果其他主机ping 192.168.8.254可能会不通，第三个从服务器也无法建立主从；

    keepalived切换几遍，再尝试ping。