00后的一套高可用，惊艳了面试官，当场开了20万年薪

作者：IT邦德
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证
从业10年以上DBA工作，在数据库领域有丰富的经验

擅长主流数据Oracle、MySQL、PG 运维开发，
备份恢复，安装迁移，性能优化、故障应急处理等。

文章目录

前言

本文详细阐述了MySQL1主2从实现读写分离&负载均衡的高可用搭建

一、MySQL Router

1.1 介绍

MySQL Router 的主要用途是读写分离，主主故障自动切换，负载均衡，连接池等。

MySQL Router 最早是作为 MySQL-Proxy 的替代方案出现的。
作为一个轻量级中间件，MySQL Router 可在应用程序和后端
MySQL 服务器之间提供透明路由和负载均衡，
从而有效提高 MySQL 数据库服务的高可用性与可伸缩行。
MySQL 强烈建议使用 Router 8 与 MySQL Server 8 和 5.7 一起使用。
MySQL Router 是 是 MySQL 官方提供的一个轻量级中间件，
可以在应用程序与 MySQL 服务器之间提供透明的路由方式。
主要用以解决 MySQL 主从库集群的高可用、负载均衡、易扩展等问题。
Router 可以与 MySQL Fabric 无缝连接，允许 Fabric 存储
和管理用于路由的高可用数据库服务器组，使管理 MySQL 服务器组更加简单。
MySQL Router 是一个可执行文件，可以与应用程序在同一平台上运行，也可以单独部署。
虽然 MySQL Router 是 InnoDB
Cluster（MySQL 7.X）的一部分，
MySQL 5.6 等版本数据库仍然可以使用 Router 作为其中间代理层。MySQL Router 的配
置文件中包含有关如何执行路由的信息。
它与 MySQL 服务器的配置文件类似，
也是由多个段组成，每个段中包含相关配置选项。

1.2 下载

各种安装包的下载地址为：
https://dev.mysql.com/downloads/router/

1.3 1主2从框架

二、搭建1主2从

Docker安装部署请参考博客：Docker入门

[root@centos7 ~]# docker images
[root@centos7 ~]# docker pull mysql:5.7.30
[root@centos7 ~]# docker network create --subnet=172.72.0.0/24 mysql-network
[root@centos7 ~]# docker network ls

[root@centos7 ~]# mkdir -p /mysqltest1/master/conf.d
[root@centos7 ~]# mkdir -p /mysqltest1/slave1/conf.d
[root@centos7 ~]# mkdir -p /mysqltest1/slave2/conf.d

2.1 初始化容器

docker run -d --name MSS5730M33062 \
-h master -p 33062:3306 --net=mysql-network --ip 172.72.0.2 \
-v /mysqltest1/master/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
mysql:5.7.30

[root@centos7 ~]# docker update --restart=always MSS5730M33062

docker run -d --name MSS5730M33063 \
-h slave1 -p 33063:3306 --net=mysql-network --ip 172.72.0.3 \
-v /mysqltest1/slave1/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
mysql:5.7.30

[root@centos7 ~]# docker update --restart=always MSS5730M33063

docker run -d --name MSS5730M33064 \
-h slave2 -p 33064:3306 --net=mysql-network --ip 172.72.0.4 \
-v /mysqltest1/slave2/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
mysql:5.7.30

[root@centos7 ~]# docker update --restart=always MSS5730M33064

2.2 配置参数

cat > /mysqltest1/master/conf.d/my.cnf <<"EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033062
log-bin =
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
skip-name-resolve
gtid-mode=ON
enforce-gtid-consistency=on
report_host=172.72.0.2
EOF

cat > /mysqltest1/slave1/conf.d/my.cnf <<"EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033063
log-bin =
binlog_format=row
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
report_host=172.72.0.3
EOF

cat > /mysqltest1/slave2/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033064
log-bin =
binlog_format=row
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
report_host=172.72.0.4
EOF

2.3 重启容器

[root@centos7 ~]# docker restart MSS5730M33062
[root@centos7 ~]# docker restart MSS5730M33063
[root@centos7 ~]# docker restart MSS5730M33064
[root@centos7 ~]# docker ps

[root@centos7 ~]# docker exec -it MSS5730M33062 bash
root@master:/# mysql -uroot -proot

#CMD登陆查看
mysql -uroot -proot -h192.168.1.54 -P33062 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -proot -h192.168.1.54 -P33063 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -proot -h192.168.1.54 -P33064 -e "select @@hostname,@@server_id,@@server_uuid"

2.4 主库创建用户

mysql -uroot -proot -h192.168.1.54 -P33062
grant replication slave on *.* to repl@'%' identified by 'jeames';
select user,host,grant_priv,password_last_changed,authentication_string from mysql.user;

mysql> create database jeamesdb;
mysql> use jeamesdb
mysql> create table mytb1(id int,name varchar(30));
mysql> insert into mytb1 values(1,'a'),(2,'b');
mysql> show variables like '%log_bin%';

#主库导出时不能加--set-gtid-purged=off 参数
mysqldump -uroot -proot -h172.72.0.2 -P3306 --single-transaction \
--hex-blob --routines --events --triggers \
--master-data=2 --databases jeamesdb \
--default-character-set=utf8 --max_allowed_packet=512M > /tmp/salve.sql

mysql> select @@hostname,@@server_id,@@server_uuid;

三、搭建从库

--S1
[root@centos7 ~]# mysql -uroot -proot -h172.72.0.3 -P3306 < /tmp/salve.sql
[root@centos7 ~]# mysql -uroot -proot -h172.72.0.3 -P3306

change master to
master_host='172.72.0.2',
master_port=3306,
master_user='repl',
master_password='jeames',
master_auto_position=1;

mysql> start slave;
mysql> show slave status \G;

主库：mysql> insert into mytb1 values(3,'c'),(4,'d');
从库：mysql> show slave status \G;
从库：mysql> SELECT * FROM jeamesdb.mytb1;

注意：此处如果报错，则通过以下方法处理

以上报错显示密码错误，修改密码重新执行即可
mysql> stop slave;
mysql> start slave;

注：以上方法适合binlog日志不存在可以通过这个方法同步
--S2
[root@centos7 ~]# mysql -uroot -proot -h172.72.0.4 -P3306

change master to master_host='172.72.0.2',
master_port=3306,
master_user='repl',
master_password='jeames',
master_auto_position=1;

mysql> start slave;
mysql> show slave status \G;
mysql> SELECT * FROM jeamesdb.mytb1;
注：以上方法是通过binlog日志同步

确认主库状态：
[root@centos7 ~]# mysql -uroot -proot -h172.72.0.2 -P3306
mysql> show slave hosts;

确认备库状态：
[root@centos7 ~]# mysql -uroot -proot -h172.72.0.4 -P3306
mysql> show slave statuS \G

四、搭建 MySQL Router

[root@centos7 ~]# mysql -uroot -proot -h172.72.0.2 -P3306   主库
[root@centos7 ~]# mysql -uroot -proot -h172.72.0.3 -P3306   从库1
[root@centos7 ~]# mysql -uroot -proot -h172.72.0.4 -P3306   从库2

4.1 初始化容器

docker run -d --name MysqlRouter \
-h MysqlRouter \
--net=mysql-network --ip 172.72.0.7 --privileged=true \
-p 7001-7005:7001-7005 -p 2271:22 \
techerwang/oracle:centos76 init

[root@centos7 ~]# docker update --restart=always MysqlRouter
[root@centos7 ~]# docker exec -it MysqlRouter bash
[root@MysqlRouter /]# ifconfig

4.2 安装router

[root@centos7 ~]# docker cp mysql-router-community-8.0.23-1.el7.x86_64.rpm MysqlRouter:/
[root@centos7 ~]# docker exec -it MysqlRouter bash
[root@MysqlRouter /]# rpm -ivh mysql-router-community-8.0.23-1.el7.x86_64.rpm

mysqlrouter --version
mysqlrouter --help

4.3 相关配置

[root@MysqlRouter /]# mkdir -p /var/log/mysql-router
[root@MysqlRouter /]# mkdir -p /etc/mysql-router/
[root@MysqlRouter /]# cat /etc/passwd      查看是否有mysql用户
[root@MysqlRouter /]# useradd mysql
[root@MysqlRouter /]# chown mysql:mysql /var/log/mysql-router

cat > /etc/mysql-router/mysqlrouter.conf <<"EOF"
[DEFAULT]
logging_folder = /var/log/mysql-route
[logger]
level = INFO
[routing:secondary]
bind_address = 172.72.0.7
bind_port = 7001
destinations = 172.72.0.3:3306,172.72.0.4:3306
routing_strategy = round-robin
[routing:primary]
bind_address = 172.72.0.7
bind_port = 7002
destinations = 172.72.0.2:3306
routing_strategy = next-available
EOF

注：round-robin代表一种轮训算法

4.4 启动

4.5 测试

#测试读负载均衡（7001端口）
[root@centos7 ~]# for i in $(seq 1 10); do mysql -uroot -proot -h172.72.0.7 -P7001 -e 'select @@server_id;'; done | egrep '[0-9]'

#测试写（7002端口）
for i in $(seq 1 10); do mysql -uroot -proot -h172.72.0.7 -P7002 -e 'select @@server_id;'; done | egrep '[0-9]'

4.6 从库设置为只读

mysql> set global read_only=1;
# set global read_only=0 为取消普通账号的只读模式
# set global read_only=1 对拥有super权限的账号是不生效的，所以在授权账号的时候尽量避免添加super权限
从库1设置
mysql> grant all on *.* to repl@'%' identified by 'jeames' with grant option;
mysql> flush privileges;
mysql> update mysql.user set grant_priv='N',super_priv='N' where user='repl';
mysql> select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;

大家点赞、收藏、关注、评论啦 、查看👇🏻👇🏻👇🏻微信公众号获取联系方式👇🏻👇🏻👇🏻