mysql 代理 a_mysql主主、mysql-proxy读写分离、keepalived高可用完美组合

配置mysql主主复制

服务器A：172.16.100.9

服务器B：172.16.100.10

首先在服务器A和服务器B上各安装好单机版mysql5.7，安装好后，再继续下面的操作。

在服务器A和服务器B上都执行

wget ftp://ftp.pbone.net/mirror/ftp.centos.org/7.6.1810/cloud/x86_64/openstack-ocata/common/crudini-0.9-2.el7.noarch.rpm

wget ftp://ftp.pbone.net/mirror/ftp.centos.org/7.6.1810/cloud/x86_64/openstack-stein/openstack-utils-2017.1-1.el7.noarch.rpm

rpm -ivh crudini-0.9-2.el7.noarch.rpm

rpm -ivh openstack-utils-2017.1-1.el7.noarch.rpm

mkdir -p /usr/local/mysqldata/binlogs

mkdir -p /usr/local/mysqldata/relaylogs

chown -R mysql:mysql  /usr/local/mysqldata

chmod -R 770  /usr/local/mysqldata

1.控制自增列的初始值为2和增量步长为1

openstack-config --set /etc/my.cnf mysqld auto_increment_offset 1

openstack-config --set /etc/my.cnf mysqld auto_increment_increment 2

2.启用binlog日志，只保留7天内的日志

openstack-config --set /etc/my.cnf mysqld log-bin /usr/local/mysqldata/binlogs/mysql-bin

openstack-config --set /etc/my.cnf mysqld max_binlog_size  1G

openstack-config --set /etc/my.cnf mysqld binlog_format ROW

openstack-config --set /etc/my.cnf mysqld expire_logs_days 7

3.配置中继日志的位置

openstack-config --set /etc/my.cnf mysqld relay-log /usr/local/mysqldata/relaylogs/slave-relay-bin

4.开启从库上的更新操作要写入binlog日志，以便从库的从库能够进行日志同步

openstack-config --set /etc/my.cnf mysqld log-slave-updates

5.设置每缓存20个事务就采用同步写磁盘的方式写入binlog日志

openstack-config --set /etc/my.cnf mysqld innodb_support_xa 1

openstack-config --set /etc/my.cnf mysqld sync_binlog 20

6.不记录系统数据库的二进制日志

sed -i "/^\[mysqld/a\binlog_ignore_db = mysql" /etc/my.cnf

sed -i "/^\[mysqld/a\binlog_ignore_db = information_schema" /etc/my.cnf

sed -i "/^\[mysqld/a\binlog_ignore_db = information_schema" /etc/my.cnf

sed -i "/^\[mysqld/a\binlog_ignore_db = sys" /etc/my.cnf

7.设置服务器的server-id

openstack-config --set /etc/my.cnf mysqld server-id $RANDOM

8.重启数据库

ps -ef | grep mysql | grep -v grep | awk '{print $2}' |xargs kill -9

/usr/local/mysqlrun/bin/mysqld_safe --defaults-file=/etc/my.cnf &

9.创建同步账号

grant replication slave on *.* to 'repl'@'172.16.100.9' identified by 'repl1234!';

grant replication slave on *.* to 'repl'@'172.16.100.10' identified by 'repl1234!';

flush privileges;

10.查看并记录master状态中的File和Position

reset master;

show master status;

得到的结果为：

mysql-bin.000001 和 154

在服务器A上执行

stop slave;

reset slave all;

change master to master_host='172.16.100.10',master_user='repl',master_password='repl1234!',master_log_file='mysql-bin.000001',master_log_pos=154;

start slave;

show slave status\G;

在服务器B上执行

stop slave;

reset slave all;

change master to master_host='172.16.100.9',master_user='repl',master_password='repl1234!',master_log_file='mysql-bin.000001',master_log_pos=154;

start slave;

show slave status\G;

配置keepalived高可用(不抢占)

服务器A：172.16.100.9

服务器B：172.16.100.10

VIP：172.16.100.12

VIP：172.16.100.13

首先在服务器A和服务器B上各安装好keepalived，安装好后，再继续下面的操作。

在服务器A上执行

1.编写脚本监控mysql状态

echo '#!/bin/bash

count = `ps aux | grep -v grep | grep mysql | wc -l`

if [ $count > 0 ];then

exit 0

else

exit 1

fi' > /etc/keepalived/checkmysql.sh

2.配置全局定义块

global_defs {

router_id mysqlserver01

}

3.keepalived只能做到对网络故障和keepalived本身的监控,配置脚本监控mysql服务状态

weight对priority的影响详情请参考https://www.cnblogs.com/arjenlee/p/9258188.html

vrrp_script checkmysql

{

script "/etc/keepalived/checkmysql.sh"

interval 3

weight -20

}

4.配置VRRP实例定义块

vrrp_instance instance1 {

state MASTER

virtual_router_id 1

interface eth0

mcast_src_ip 172.16.100.9

priority 100

advert_int 3

nopreempt

authentication {

auth_type PASS

auth_pass kee1234

}

virtual_ipaddress {

172.16.100.12

}

track_script {

checkmysql

}

}

vrrp_instance instance2 {

state BACKUP

virtual_router_id 2

interface eth0

mcast_src_ip 172.16.100.9

priority 90

advert_int 3

nopreempt

authentication {

auth_type PASS

auth_pass kee1234

}

virtual_ipaddress {

172.16.100.13

}

track_script {

checkmysql

}

}

在服务器B上执行

1.编写脚本监控mysql状态

echo '#!/bin/bash

count = `ps aux | grep -v grep | grep mysql | wc -l`

if [ $count > 0 ];then

exit 0

else

exit 1

fi' > /etc/keepalived/checkmysql.sh

2.配置全局定义块

global_defs {

router_id mysqlserver02

}

3.keepalived只能做到对网络故障和keepalived本身的监控,配置脚本监控mysql服务状态

weight对priority的影响详情请参考https://www.cnblogs.com/arjenlee/p/9258188.html

vrrp_script checkmysql

{

script "/etc/keepalived/checkmysql.sh"

interval 3

weight -20

}

4.配置VRRP实例定义块

vrrp_instance instance1 {

state BACKUP

virtual_router_id 1

interface eth0

mcast_src_ip 172.16.100.10

priority 90

advert_int 3

nopreempt

authentication {

auth_type PASS

auth_pass kee1234

}

virtual_ipaddress {

172.16.100.12

}

track_script {

checkmysql

}

}

vrrp_instance instance2 {

state MASTER

virtual_router_id 2

interface eth0

mcast_src_ip 172.16.100.10

priority 100

advert_int 3

nopreempt

authentication {

auth_type PASS

auth_pass kee1234

}

virtual_ipaddress {

172.16.100.13

}

track_script {

checkmysql

}

}

mysql-proxy实现读写分离

服务器：172.16.100.7

首先在服务器上安装好mysql-proxy，安装好后，再继续下面的操作。

1.修改配置文件

cat << EOF > /etc/mysql-proxy.cnf

[mysql-proxy]

user = root

admin-username=root

admin-password=123456

proxy-address=172.16.100.7

proxy-lua-script = /usr/local/myapps/mysql-proxy/lua/rw-splitting.lua

admin-lua-script = /usr/local/myapps/mysql-proxy/lua/admin-sql.lua

proxy-backend-addresses = 172.16.100.12

proxy-read-only-backend-addresses = 172.16.100.13

log-file=/usr/local/myapps/mysql-proxy/logs/mysql-proxy.log

log-level=debug

EOF

2.重启mysql-proxy服务

ps -ef | grep mysql-proxy | grep -v grep | awk '{print $2}' |xargs kill -9

/usr/local/myapps/mysql-proxy/bin/mysql-proxy --daemon --keepalive  --defaults-file=/etc/mysql-proxy.cnf