MySQL主主同步方案

l MySQL主主+Keepalived

l MySQL+DRBD+Heartbeat

在企业中，数据库高可用一直是企业的重中之重，中小企业很多都是使用mysql主主方案，一主多从，读写分离等，但是单主存在单点故障，从库切换成主库需要作改动。因此，如果是双主或者多主，就会增加mysql入口，增加高可用。不过多主需要考虑自增长ID问题，这个需要特别设置配置文件，比如双主，可以使用奇偶，总之，主之间设置自增长ID相互不冲突就能完美解决自增长ID冲突问题。

主主方案实现思路

1、两台mysql都可读写，互为主备。默认只使用一台masterA负责数据的写入，另一台masterB备用处于备用状态；

2、 masterA是masterB的主库，masterB又是masterA的主库，它们互为主从；

3、两台主库之间做高可用,可以采用keepalived等方案，使用VIP对外提供服务；

4、所有提供服务的从服务器与masterB进行主从同步（双主多从）;

5、建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP（非抢占模式）；

这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上，尽可能减少主库宕机对业务造成的影响，减少了主从同步给生产主库带来的压力；

但是也有几个不足的地方:

masterB可能会一直处于空闲状态（可以用它当从库，负责部分查询）；
主库后面提供服务的从库要等masterB先同步完了数据后才能去masterB上去同步数据，这样可能会造成一定程度的同步延时；

第1台机器

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

server-id=1

log-bin=mysql-binlog#打开二进制功能

log-slave-updates=true

max_binlog_size=1024M#binlog单文件最大值

auto_increment_offset = 1

auto_increment_increment = 2#奇数ID

replicate-ignore-db = information_schema#忽略不同步主从的数据库

replicate-ignore-db = performance_schema

replicate-ignore-db = test

replicate-ignore-db = mysql

max_connections = 3000

max_connect_errors = 30

skip-character-set-client-handshake#忽略应用程序想要设置的其他字符集

init-connect='SET NAMES utf8'#连接时执行的SQL

character-set-server=utf8#服务端默认字符集

wait_timeout=1800#请求的最大连接时间

interactive_timeout=1800#和上一参数同时修改才会生效

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#sql模式

relay-log=relay-log-bin#开启中继日志

relay-log-index=slave-relay-bin.index

[root@localhost ~]# systemctl restart mariadb

[root@localhost ~]# mysql

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.200.112' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;

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

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

| mysql-binlog.000004 | 486 | | |

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

1 row in set (0.00 sec)

第2台机器

[root@localhost ~]# cat /etc/my.cnf

[mysqld]

server-id = 2

log-bin=mysql-binlog

log-slave-updates=true

max_binlog_size=1024M

auto_increment_offset = 2

auto_increment_increment = 2#偶数ID

replicate-ignore-db = information_schema

replicate-ignore-db = performance_schema

replicate-ignore-db = test

replicate-ignore-db = mysql

max_connections = 3000

max_connect_errors = 30

skip-character-set-client-handshake

init-connect='SET NAMES utf8'

character-set-server=utf8

wait_timeout=1800

interactive_timeout=1800

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

relay-log=relay-log-bin

relay-log-index=slave-relay-bin.index

[root@localhost ~]# systemctl restart mariadb

[root@localhost ~]# mysql

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.200.111' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;

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

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

| mysql-binlog.000001 | 889 | | |

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

1 row in set (0.00 sec)

特别参数说明

log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启

masterA自增长ID

auto_increment_offset = 1

auto_increment_increment = 2 #奇数ID

masterB自增加ID

auto_increment_offset = 2

auto_increment_increment = 2 #偶数ID

测试环境，可以保证没数据写入。否则需要的步骤是：先masterA锁表-->masterA备份数据-->masterA解锁表 -->masterB导入数据-->masterB设置主从-->查看主从

第1台机器

MariaDB [(none)]> stop slave;

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

MariaDB [(none)]> change master to master_host='192.168.200.112',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-binlog.000001',master_log_pos=889;

Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.112

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-binlog.000001

Read_Master_Log_Pos: 889

Relay_Log_File: relay-log-bin.000002

Relay_Log_Pos: 532

Relay_Master_Log_File: mysql-binlog.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

第2台机器

MariaDB [(none)]> stop slave;

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> change master to master_host='192.168.200.111',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-binlog.000004',master_log_pos=486;

Query OK, 0 rows affected (0.13 sec)

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.111

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-binlog.000004

Read_Master_Log_Pos: 486

Relay_Log_File: relay-log-bin.000002

Relay_Log_Pos: 532

Relay_Master_Log_File: mysql-binlog.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

第1台机器

MariaDB [(none)]> create database test01;

Query OK, 1 row affected (0.01 sec)

第2台机器

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test01 |

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

5 rows in set (0.00 sec)

MariaDB [(none)]> create database test02;

Query OK, 1 row affected (0.00 sec)

第1台机器

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test01 |

| test02 |

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

6 rows in set (0.00 sec)

MySQL主主高可用方案

masterA配置

[root@localhost ~]# yum -y install keepalived

[root@localhost ~]# cat /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id LVS_MASTER-A

}

vrrp_script mysql {

script "/opt/mysql.sh"

interval 2

weight -5

fall 2

rise 1

}

vrrp_instance VI_1 {

state BACKUP

interface ens32

virtual_router_id 51

priority 100

nopreempt

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

mysql

}

virtual_ipaddress {

192.168.200.254

}

[root@localhost ~]# cat /opt/mysql.sh

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

if [ "${counter}" -eq 0 ]; then

systemctl stop keepalived

[root@localhost ~]# chmod +x /opt/mysql.sh

[root@localhost ~]# systemctl start keepalived

[root@localhost ~]# ip a | grep ens32

2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000

inet 192.168.200.111/24 brd 192.168.200.255 scope global ens32

inet 192.168.200.254/32 scope global ens32

[root@localhost ~]# tail -f /var/log/messages

Feb 12 17:23:23 localhost Keepalived_vrrp[70087]: VRRP_Instance(VI_1) Transition to MASTER STATE

Feb 12 17:23:24 localhost Keepalived_vrrp[70087]: VRRP_Instance(VI_1) Entering MASTER STATE

Feb 12 17:23:24 localhost Keepalived_vrrp[70087]: VRRP_Instance(VI_1) setting protocol iptable drop rule

Feb 12 17:23:24 localhost Keepalived_vrrp[70087]: VRRP_Instance(VI_1) setting protocol VIPs.

Feb 12 17:23:24 localhost Keepalived_vrrp[70087]: Sending gratuitous ARP on ens32 for 192.168.200.254

Feb 12 17:23:24 localhost Keepalived_vrrp[70087]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens32 for 192.168.200.254

Feb 12 17:23:24 localhost Keepalived_vrrp[70087]: Sending gratuitous ARP on ens32 for 192.168.200.254

masterB配置

[root@localhost ~]# cat /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id LVS_MASTER-B

}

vrrp_script mysql {

script "/opt/mysql.sh"

interval 2

weight -5

fall 2

rise 1

}

vrrp_instance VI_1 {

state BACKUP

interface ens32

virtual_router_id 51

priority 99

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

mysql

}

virtual_ipaddress {

192.168.200.254

}

[root@localhost ~]# cat /opt/mysql.sh

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

if [ "${counter}" -eq 0 ]; then

systemctl stop keepalived

[root@localhost ~]# chmod +x /opt/mysql.sh

[root@localhost ~]# systemctl start keepalived

[root@localhost ~]# tail -f /var/log/messages

Feb 12 17:28:02 localhost Keepalived_healthcheckers[70075]: WARNING - script '/root/shutdown.sh' is not executable for uid:gid 0:0 - disabling.

Feb 12 17:28:02 localhost Keepalived_healthcheckers[70075]: SECURITY VIOLATION - check scripts are being executed but script_security not enabled.

Feb 12 17:28:02 localhost kernel: ip_set: protocol 6

Feb 12 17:28:02 localhost kernel: IPVS: [wrr] scheduler registered.

Feb 12 17:28:02 localhost Keepalived_healthcheckers[70075]: Activating healthchecker for service [192.168.200.254]:3306

Feb 12 17:28:02 localhost Keepalived_vrrp[70076]: VRRP_Instance(VI_1) removing protocol VIPs.

Feb 12 17:28:02 localhost Keepalived_vrrp[70076]: VRRP_Instance(VI_1) removing protocol iptable drop rule

Feb 12 17:28:02 localhost Keepalived_vrrp[70076]: Using LinkWatch kernel netlink reflector...

Feb 12 17:28:02 localhost Keepalived_vrrp[70076]: VRRP_Instance(VI_1) Entering BACKUP STATE

Feb 12 17:28:02 localhost Keepalived_vrrp[70076]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]

1、测试VIP转移

masterA配置

[root@localhost ~]# systemctl stop mariadb

[root@localhost ~]# ip a | grep ens32

2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000

inet 192.168.200.111/24 brd 192.168.200.255 scope global ens32

[root@localhost ~]# ps aux | grep mysql

root 83651 0.0 0.0 112720 980 pts/2 S+ 14:30 0:00 grep --color=auto mysql

masterB配置

[root@localhost ~]# ip a | grep ens32

2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000

inet 192.168.200.112/24 brd 192.168.200.255 scope global ens32

inet 192.168.200.254/32 scope global ens32

[root@localhost ~]# tail -f /var/log/messages

Feb 13 14:30:02 localhost Keepalived_vrrp[79095]: VRRP_Instance(VI_1) Entering MASTER STATE

Feb 13 14:30:02 localhost Keepalived_vrrp[79095]: VRRP_Instance(VI_1) setting protocol VIPs.

Feb 13 14:30:02 localhost Keepalived_vrrp[79095]: Sending gratuitous ARP on ens32 for 192.168.200.254

Feb 13 14:30:02 localhost Keepalived_vrrp[79095]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens32 for 192.168.200.254

2、在远程客户端测试

mysql服务器授权

[root@localhost ~]# mysql -uroot

MariaDB [(none)]> grant all on *.* to 'root'@'192.168.200.%' identified by '123456';

MariaDB [(none)]> flush privileges;

通过vip登陆测试

[root@localhost ~]# mysql -uroot -p123456 -h 192.168.200.254

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

Your MariaDB connection id is 5796

Server version: 5.5.56-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test01 |

| test02 |

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

6 rows in set (0.00 sec)

发表于 2019-10-21 19:17 繁梦阅读(456) 评论(0) 编辑收藏举报