mysql keepalived主主同步_Mysql主主同步+keepalived实现高可用

一、实验环境

1、两台机器

MasterA：192.168.200.114

MasterB：192.168.200.115

2、实现思路

实现Mysql主主复制

二、安装mariadb

1、第一台

[root@masterA ~]# yum -y install mariadb mariadb-server

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

[mysqld]

server-id= 1 //标记ID

log-bin=mysql-binlog //开启二进制日志

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

max_binlog_size=1024M//masterA自增长ID

auto_increment_offset=1auto_increment_increment=2 //奇数ID

replicate-ignore-db =information_schema

replicate-ignore-db =performance_schema

replicate-ignore-db =test

replicate-ignore-db =mysql

max_connections=3000max_connect_errors=30skip-character-set-client-handshake

init-connect='SET NAMES utf8'character-set-server=utf8

wait_timeout=1800interactive_timeout=1800sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

relay-log=relay-log-bin

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

[root@masterA~]# systemctl start mariadb

[root@masterA~]# mysql

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

Your MariaDB connection idis 2Server version:5.5.64-MariaDB MariaDB Server

Copyright (c)2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

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

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>flush privileges;

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>show master status;+---------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-binlog.000001 | 245 | | |

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

1 row in set (0.00 sec)

2、第二台

[root@masterB ~]# yum -y install mariadb mariadb-server

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

[mysqld]

server-id = 2log-bin=mysql-binlog

log-slave-updates=truemax_binlog_size=1024M

auto_increment_offset= 2auto_increment_increment= 2#偶数ID

replicate-ignore-db =information_schema

replicate-ignore-db =performance_schema

replicate-ignore-db =test

replicate-ignore-db =mysql

max_connections= 3000max_connect_errors= 30skip-character-set-client-handshake

init-connect='SET NAMES utf8'character-set-server=utf8

wait_timeout=1800interactive_timeout=1800sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

relay-log=relay-log-bin

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

[root@masterB~]# systemctl start mariadb

[root@masterB~]# mysql

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

Your MariaDB connection idis 2Server version:5.5.64-MariaDB MariaDB Server

Copyright (c)2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

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

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>flush privileges;

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>show master status;+---------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-binlog.000001 | 486 | | |

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

1 row in set (0.00 sec)

三、配置两台从服务器

1、第一台

MariaDB [(none)]>stop slave;

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

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

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>start slave;

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master to send eventMaster_Host:192.168.200.115Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-binlog.000001Read_Master_Log_Pos:486Relay_Log_File: relay-log-bin.000002Relay_Log_Pos:532Relay_Master_Log_File: mysql-binlog.000001Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

2、第二台

MariaDB [(none)]>stop slave;

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

MariaDB [(none)]>change master to

master_host='192.168.200.114',

master_port=3306,

master_user='repl',

master_password='123123',

master_log_file='mysql-binlog.000001',

master_log_pos=245;

Query OK,0 rows affected (0.01sec)

MariaDB [(none)]>start slave;

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master to send eventMaster_Host:192.168.200.114Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-binlog.000001Read_Master_Log_Pos:245Relay_Log_File: relay-log-bin.000002Relay_Log_Pos:532Relay_Master_Log_File: mysql-binlog.000001Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

四、测试主主复制

第一台

MariaDB [(none)]>create database test01;

Query OK,1 row affected (0.00sec)

MariaDB [(none)]>show databases;+--------------------+

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test01 |

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

5 rows in set (0.00sec)

第二台

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.00sec)

MariaDB [(none)]>show databases;+--------------------+

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test01 |

| test02 |

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

6 rows in set (0.00sec)

第一台

MariaDB [(none)]>show databases;+--------------------+

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test01 |

| test02 |

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

6 rows in set (0.00 sec)

有以上的测试结果说明主主复制成功

实现Mysql主主高可用方案

1、第一台

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

[root@masterA~]# vim /etc/keepalived/keepalived.conf! Configuration File forkeepalived

global_defs {

router_id LVS_MASTER-A

}

vrrp_script mysql {

script"/opt/mysql.sh"interval2weight-5}

vrrp_instance VI_1 {

state BACKUPinterfaceeno16777736

virtual_router_id51priority100nopreempt//VIP宕机修复后不会转移

advert_int 1authentication {

auth_type PASS

auth_pass1111}

track_script {

mysql

}

virtual_ipaddress {192.168.200.254}

}

[root@masterA~]# vim /opt/mysql.sh

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)if [ "${counter}" -eq 0]; then

systemctl stop keepalived

fi

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

[root@masterA~]# systemctl start keepalived

[root@masterA~]# ip a |grep eno167777362: eno16777736: mtu 1500 qdisc pfifo_fast state UP qlen 1000inet192.168.200.114/24 brd 192.168.200.255 scope globaleno16777736

inet192.168.200.254/32 scope global eno16777736

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

Oct7 16:42:43 localhost Keepalived_vrrp[27658]: Sending gratuitous ARP on eno16777736 for 192.168.200.254Oct7 16:42:43 localhost Keepalived_vrrp[27658]: Sending gratuitous ARP on eno16777736 for 192.168.200.254Oct7 16:49:02 localhost systemd: Stopped firewalld - dynamicfirewall daemon.

Oct7 17:00:47 localhost systemd: Starting Session 24of user root.

Oct7 17:00:47 localhost systemd: Started Session 24of user root.

Oct7 17:00:47 localhost systemd-logind: New session 24of user root.

Oct7 17:01:01 localhost systemd: Starting Session 25of user root.

Oct7 17:01:01 localhost systemd: Started Session 25of user root.

Oct7 18:01:01 localhost systemd: Starting Session 26of user root.

Oct7 18:01:01 localhost systemd: Started Session 26 of user root.

2、第二台

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

[root@masterB~]# vim /etc/keepalived/keepalived.conf! Configuration File forkeepalived

global_defs {

router_id LVS_MASTER-B

}

vrrp_script mysql {

script"/opt/mysql.sh"interval2weight-5}

vrrp_instance VI_1 {

state BACKUPinterfaceeno16777736

virtual_router_id51priority99advert_int1authentication {

auth_type PASS

auth_pass1111}

track_script {

mysql

}

virtual_ipaddress {192.168.200.254}

}

[root@masterB~]# vim /opt/mysql.sh

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)if [ "${counter}" -eq 0]; then

systemctl stop keepalived

fi

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

[root@masterB~]# systemctl start keepalived

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

Oct16 17:27:05 localhost Keepalived_vrrp[6159]: WARNING - default user 'keepalived_script' for script execution does not exist -please create.

Oct16 17:27:05 localhost Keepalived_vrrp[6159]: SECURITY VIOLATION -scripts are being executed but script_security not enabled.

Oct16 17:27:05 localhost Keepalived_vrrp[6159]: VRRP_Instance(VI_1) removing protocol VIPs.

Oct16 17:27:05 localhost Keepalived_vrrp[6159]: Using LinkWatch kernel netlink reflector...

Oct16 17:27:05 localhost Keepalived_vrrp[6159]: VRRP_Instance(VI_1) Entering BACKUP STATE

Oct16 17:27:05 localhost Keepalived_vrrp[6159]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]

Oct16 17:27:05 localhost Keepalived_vrrp[6159]: VRRP_Script(mysql) succeeded

Oct16 18:01:01 localhost systemd: Started Session 26of user root.

Oct16 18:18:39 localhost systemd: Started Session 27of user root.

Oct16 18:18:39 localhost systemd-logind: New session 27 of user root.

测试

1、第一台

#开启keepalived服务

[root@masterA~]# systemctl start keepalived

#过滤ip

[root@masterA~]# ip a |grep inet

inet127.0.0.1/8scope host lo

inet6 ::1/128scope host

inet192.168.200.111/24 brd 192.168.200.255 scope globaleno16777728

inet192.168.200.254/32 scope globaleno16777728

#关闭mysql服务

[root@masterA~]# /usr/local/mysql/support-files/mysql.server stop

Shutting down MySQL............ SUCCESS!

2、第二台

[root@masterB ~]# ip a |grep inet

inet127.0.0.1/8scope host lo

inet6 ::1/128scope host

inet192.168.200.112/24 brd 192.168.200.255 scope globaleno16777728

inet192.168.200.254/32 scope global eno16777728