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

2024-01-12 09:36:10

作者：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;

大家点赞、收藏、关注、评论啦、查看

码农公寓

文章目录

前言

一、MySQL Router

1.1 介绍

1.2 下载

1.3 1主2从框架

二、搭建1主2从

2.1 初始化容器

2.2 配置参数

2.3 重启容器

2.4 主库创建用户

三、搭建从库

四、搭建 MySQL Router

4.1 初始化容器

4.2 安装router

4.3 相关配置

4.4 启动

4.5 测试

4.6 从库设置为只读

相关文章