MySQL读写分离概述
读写分离原理
基本原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作性操作导致的变更同步到集群中的从数据库=b。
读写分离原理图：
数据内部交换过程：
为什么要读写分离
面对越来越大的访问压力，单台的服务器的性能成为瓶颈需要分担负载
主从只负责各自的读和写，极大程度的缓解 X 锁和 S 锁争用
从库可配置 myisam 引擎，提升查询性能以及节约系统开销
增加冗余，提高可用性
实现读写分离的方式
一般有两种方式实现
应用程序层实现，网站的程序实现
实现程序层实现指的是在应用程序内部及连接器中实现读写分离
程序层实现优点：
应用程序内部实现读写分离，安装即可以使用
减少一定部署难度
访问压力在一定级别以下，性能很好
程序层实现缺点：
架构一但调整，代码要跟着变
难以实现高级应用，如自动分库，分表
无法适用大型应用场景
中间件实现
中间件层实现是指在外部中间件程序实现读写分离
中间件优点：
架构设计更灵活
可以在程序上实现一些高级控制，如：透明化水平拆分，failover,监控
可以依靠技术手段提高MySQL性能
对业务代码的影响小，同时也安全
中间件缺点：
需要一定的开发运维团队的支持
Atlas中间件
Atlas介绍
360团队基于mysql proxy把lua用C改写。原有版本是支持分表， 目前已经放出了分库分表版本。在网上看到一些朋友经常说在高并 发下会经常挂掉，如果大家要使用需要提前做好测试。
Atlas架构图
Atlas安装
环境准备：
主机
IP地址
数据节点
vip地址
db01
10.0.0.51
master
10.0.0.55
db02
10.0.0.52
slave-1
db03
10.0.0.53
slave-2
#在线下载，可能会会比较慢或者失败wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
#直接在MHA-2019-6.28.zip里面有
[root@db01~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
Preparing... ################################# [100%]
Updating/installing...1:Atlas-2.2.1-1 ################################# [100%]
配置文件
cd /usr/local/mysql-proxy/confmvtest.cnf test.cnf.bakcat > test.cnf <
[mysql-proxy]
admin-username =user
admin-password = pwdproxy-backend-addresses = 10.0.0.55:3306proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53:3306pwds= repl:3yb5jEku5h4=,mha:O2jBXONX098=daemon= truekeepalive= trueevent-threads = 8log-level =message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060admin-address = 0.0.0.0:2345charset=utf8
EOF
proxy-backend-addresses = 10.0.0.55:3306 #填写vip ip地址，谁有vip地址就去写数据
proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53:3306 #读数据的数据库
启动atlas
# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
#ps -ef |grepproxy
root10236 1 0 12:59 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root10237 10236 0 12:59 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root10253 9305 0 13:00 pts/1 00:00:00 grep --color=auto proxy
测试读写分离功能
注：master节点在db01上，我们在db03上连接db01进行测试
[root@db03 ~]# mysql -umha -pmha -h10.0.0.51 -P33060
读操作测试：
mysql> select@@server_id;+-------------+
| @@server_id |
+-------------+
| 53 |
+-------------+
1 row in set (0.00sec)
mysql> select@@server_id;+-------------+
| @@server_id |
+-------------+
| 52 |
+-------------+
1 row in set (0.00 sec)
注：可以看到我们的读操作分别在db2和db3上进行了操作
写操作测试：
mysql> begin;select@@server_id;commit;
Query OK,0 rows affected (0.00sec)+-------------+
| @@server_id |
+-------------+
| 51 |
+-------------+
1 row in set (0.00sec)
Query OK,0 rows affected (0.00sec)
mysql> begin;select@@server_id;commit;
Query OK,0 rows affected (0.00sec)+-------------+
| @@server_id |
+-------------+
| 51 |
+-------------+
1 row in set (0.00sec)
Query OK,0 rows affected (0.00 sec)
注：所有的写操作都在db1上进行
Atlas的管理
1. 连接查看管理帮助
#登录管理
[root@db01~]# mysql -uuser -ppwd -h10.0.0.51 -P2345
mysql> select *from help;+----------------------------+---------------------------------------------------------+
| command | description |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
| SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id | online backend server, ... |
| ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... |
| ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |
| SELECT * FROM clients | lists the clients |
| ADD CLIENT $client | example: "add client 192.168.1.2", ... |
| REMOVE CLIENT $client | example: "remove client 192.168.1.2", ... |
| SELECT * FROM pwds | lists the pwds |
| ADD PWD $pwd | example: "add pwd user:raw_password", ... |
| ADD ENPWD $pwd | example: "add enpwd user:encrypted_password", ... |
| REMOVE PWD $pwd | example: "remove pwd user", ... |
| SAVE CONFIG | save the backends to config file |
| SELECT VERSION | display the version of Atlas |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.01 sec)
2. 查看数据库节点状态
mysql> select *from backends;+-------------+----------------+-------+------+
| backend_ndx | address | state | type |
+-------------+----------------+-------+------+
| 1 | 10.0.0.55:3306 | up | rw |
| 2 | 10.0.0.52:3306 | up | ro |
| 3 | 10.0.0.53:3306 | up | ro |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)
rw：支持读写
ro：支持只读
3. 上线和下线节点
set offline $backend_id --下线例子：set offline 2;
set online $backend_id--上线例子：set online 2;
4. 添加删除节点
ADD MASTER $backend --添加主节点例子：add master 10.0.0.54:3306;
ADD slave $backend--添加从节点例子：add slave 10.0.0.54:3306;
REMOVE BACKEND $backend_id--删除节点
5. 用户管理
select * from pwds --查看用户
ADD PWD $pwd --添加一个用户，使用的是明文
ADD ENPWD $pwd --添加一个用户，使用密文
REMOVE PWD $pwd --删除用户
6. 永久生效
mysql> save config;
企业用户管理案例
1. 数据库主节点
grant all on *.* to china@'10.0.0.%' identified by '123';
2. Atlas中添加数据库用户
#明文
ADD PWD china:123;
#密文/usr/local/mysql-proxy/bin/encrypt 123[root@db01~]# /usr/local/mysql-proxy/bin/encrypt 1233yb5jEku5h4=ADD ENPWD china:3yb5jEku5h4=
配置永久生效
save config;
MySQL分布式架构
基础环境准备
架构表格
主机
实例
端口
数据目录
db01
mysql3307
3307
/data/3307/data
db02
mysql3308
3308
/data/3308/data
mysql3309
3309
/data/3309/data
mysql3310
3310
/data/3310/data
架构图：
环境准备
数据库版本：MySQL 5.7.28
两台虚拟机：db01、db02
每台创建四个MySQL实例：3307、3308、3309、3310
删除历史环境
pkill mysqldrm -rf /data/33{07..10}mv /etc/my.cnf /etc/my.cnf.bak
3. 创建相关目录初始化数据
mkdir /data/33{07..10}/data -p
mysqld--initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/database/mysql
mysqld--initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/database/mysql
mysqld--initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/database/mysql
mysqld--initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/app/database/mysql
4.准备配置文件和脚本
db01配置文件和启动脚本
#db01配置文件cat >/data/3307/my.cnf<
[mysqld]
basedir=/app/database/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1EOFcat >/data/3308/my.cnf<
[mysqld]
basedir=/app/database/mysql
datadir=/data/3308/data
port=3308socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1EOFcat >/data/3309/my.cnf<
[mysqld]
basedir=/app/database/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1EOFcat >/data/3310/my.cnf<
[mysqld]
basedir=/app/database/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1EOF
#db01启动文件cat >/etc/systemd/system/mysqld3307.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE= 5000EOFcat >/etc/systemd/system/mysqld3308.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE= 5000EOFcat >/etc/systemd/system/mysqld3309.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE= 5000EOFcat >/etc/systemd/system/mysqld3310.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE= 5000EOF
db02配置文件和启动文件
#配置文件cat >/data/3307/my.cnf<
[mysqld]
basedir=/app/database/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=17gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1EOFcat >/data/3308/my.cnf<
[mysqld]
basedir=/app/database/mysql
datadir=/data/3308/data
port=3308socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1EOFcat >/data/3309/my.cnf<
[mysqld]
basedir=/app/database/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=19gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1EOFcat >/data/3310/my.cnf<
[mysqld]
basedir=/app/database/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=20gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1EOF
#启动文件cat >/etc/systemd/system/mysqld3307.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE= 5000EOFcat >/etc/systemd/system/mysqld3308.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE= 5000EOFcat >/etc/systemd/system/mysqld3309.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE= 5000EOFcat >/etc/systemd/system/mysqld3310.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE= 5000EOF
5. 修改权限，启动多实例
chown -R mysql.mysql /data/*systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310
netstat -lntup
mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
6. 节点主从规划
箭头指向谁是主库
shard1
10.0.0.51:3307 10.0.0.52:3307
10.0.0.51:3309 ------> 10.0.0.51:3307
10.0.0.52:3309 ------> 10.0.0.52:3307
shard2
10.0.0.52:3308 10.0.0.51:3308
10.0.0.52:3310 -----> 10.0.0.52:3308
10.0.0.51:3310 -----> 10.0.0.51:3308
7.开始配置
##shard1
10.0.0.51:3307 10.0.0.52:3307
db02
mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"mysql-S /data/3307/mysql.sock -e "grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;"
db01
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"mysql-S /data/3307/mysql.sock -e "start slave;"mysql-S /data/3307/mysql.sock -e "show slave status\G"
db02
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"mysql-S /data/3307/mysql.sock -e "start slave;"mysql-S /data/3307/mysql.sock -e "show slave status\G"
10.0.0.51:3309 ------> 10.0.0.51:3307
db01
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"mysql-S /data/3309/mysql.sock -e "start slave;"mysql-S /data/3309/mysql.sock -e "show slave status\G"
10.0.0.52:3309 ------> 10.0.0.52:3307
db02
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"mysql-S /data/3309/mysql.sock -e "start slave;"mysql-S /data/3309/mysql.sock -e "show slave status\G"
##shard2
10.0.0.52:3308 10.0.0.51:3308
db01
mysql -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"mysql-S /data/3308/mysql.sock -e "grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;"
db02
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"mysql-S /data/3308/mysql.sock -e "start slave;"mysql-S /data/3308/mysql.sock -e "show slave status\G"
db01
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"mysql-S /data/3308/mysql.sock -e "start slave;"mysql-S /data/3308/mysql.sock -e "show slave status\G"
10.0.0.52:3310 -----> 10.0.0.52:3308
db02
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"mysql-S /data/3310/mysql.sock -e "start slave;"mysql-S /data/3310/mysql.sock -e "show slave status\G"
10.0.0.51:3310 -----> 10.0.0.51:3308
db01
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"mysql-S /data/3310/mysql.sock -e "start slave;"mysql-S /data/3310/mysql.sock -e "show slave status\G"
8. 检查主从状态
mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep "Running:"mysql-S /data/3308/mysql.sock -e "show slave status\G"|grep "Running:"mysql-S /data/3309/mysql.sock -e "show slave status\G"|grep "Running:"mysql-S /data/3310/mysql.sock -e "show slave status\G"|grep "Running:"
注：(没问题不要做这部！！！！) 如果中间出现错误，在每个节点进行执行以下命令
mysql -S /data/3307/mysql.sock -e "stop slave; reset slave all;"mysql-S /data/3308/mysql.sock -e "stop slave; reset slave all;"mysql-S /data/3309/mysql.sock -e "stop slave; reset slave all;"mysql-S /data/3310/mysql.sock -e "stop slave; reset slave all;"
分布式架构演变
Mycat中间件安装
Mycat介绍
Mycat是目前最流行的基于Java语言编写的数据库中间件,是一个实现了MySQL协议的服务器，前端用户可以把它看作是一个数据库代理,用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生协议与多个MySQL服务器通信,也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。
Mycat是基于阿里开源的Cobar产品研发，Cobar的稳定性、可靠性、优秀的bib架构和性能以及众多成熟的使用案例使得Mycat变得非常强大。
Mycat发展到目前版本，已经不是一个单纯的MySQL代理了,它的后端可以支持MySQL、SQL Server、Orale、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。而在最终用户看来，无论是那种存储方式，在Mycat里，都是一个传统的数据库表,支持标准的SQL语句,这样一来，对前端业务系统来说，可以大幅度降低开发难度，提升开发速度。
Mycat架构图：
Mycat应用
上传解压软件
mkdir /data/mycat/cd/data/mycatwget http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
#添加
export PATH=/data/mycat/bin:$PATH
#生效
source/etc/profile
配置Java环境
解压到/usr/local，创建软连接
mkdir /app/
tar xf jdk-8u221-linux-x64.tar.gz -C /app/
ln -s /app/jdk1.8.0_221/ /app/jdk
全局环境变量
export JAVA_HOME=/app/jdk
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
export CLASSPATH=.$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib:$JAVA_HOME/lib/tools.jarq
./etc/profile
测试版本
[root@db01 /app]# java -version
java version"1.8.0_221"Java(TM) SE Runtime Environment (build1.8.0_221-b11)
Java HotSpot(TM)64-Bit Server VM (build 25.221-b11, mixed mode)
注：实验环境直接yum install java -y
启动测试
[root@db01 mycat]# mycat start
Starting Mycat-server...
[root@db01 mycat]# mysql-uroot -p123456 -h 127.0.0.1 -P8066
软件结构
bin：程序目录
mycat程序，启动和关闭mycat中间件
conf：配置相关目录
schema.xml主配置文件
rule.xml分片配置
server.xmlmycat服务相关配置
logs：日志相关目录
wrapper.log
mycat.log
schema.xml配置文件结构
[root@db01 conf]# cd /data/mycat/conf/
mvschema.xml schema.xml.bak
vim schema.xml
# 逻辑库：
# DN数据节点(逻辑分片)：数据节点(逻辑分片)：作用：
垂直和水平查分。
# DH 数据主机
作用： 高可用和读写分离
select user()
#写的节点#读的节点
schema.xml文件逻辑结构
测试环境准备
注：测试word.sql数据库
配置文件
vim /data/mycat/conf/schema.xml<?xml version="1.0"?>
select user()
db01：
mysql -S /data/3307/mysql.sock
grant all on*.* to root@'10.0.0.%' identified by '123';
source/root/world.sql
mysql-S /data/3308/mysql.sock
grant all on*.* to root@'10.0.0.%' identified by '123';
source/root/world.sql
重启mycat
# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
读写分离测试
连接mycat服务
[root@db01 ~]# mysql -uroot -p123456 -h 10.0.0.51 -P8066
# 测试读
mysql> select@@server_id;+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.05sec)
# 测试写
mysql> begin ; select@@server_id;commit;+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.00 sec)
配置读写分离及高可用
[root@db01 conf]# mvschema.xml schema.xml.rw
[root@db01 conf]# vim schema.xml<?xml version="1.0"?>
select user()
[root@db01 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
真正的 writehost：负责写操作的writehost
standby writehost：和readhost一样，只提供读服务
当写节点宕机后，后面跟的readhost也不提供服务，这时候standby的writehost就提供写服务，后面跟的readhost提供读服务
测试
mysql -uroot -p123456 -h 127.0.0.1 -P 8066show variables like'server_id';
读写分离测试
mysql -uroot -p -h 127.0.0.1 -P8066
show variables like'server_id';
show variables like'server_id';
show variables like'server_id';
begin;
show variables like'server_id';
参数介绍
有两个参数需要注意，balance和switchType。
其中，balance指的负载均衡类型，目前的取值有4种：
balance="0"：不开启读写分离机制，所有读操作都发送发到当前可用的writeHost上。
balance="1"：全部的readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1-->S1，M2-->S2，并且 M1与 M2 互为主备)，正常情况下，M2，S1，S2 都参与 select 语句的负载均衡。
balance="2"：所有读操作都随机的在writeHost、readhost上分发。
balance="3"：所有读请求随机的分发到writeHost对应的readhost执行，writeHost不负担读压力
switchType指的是切换模式，目前的取值也有4种：
switchType="-1"：表示不自动切换
switchType="1"：默认值，表示自动切换
switchType="2"：基于MySQL主从同步的状态决定是否切换，心跳语句为 show slave status;
switchType="3"：基于 MySQL galary cluster 的切换机制(适合集群)，心跳语句为 show status like 'wsrep%'。
datahost其他配置
maxCon="1000"：最大的并发连接数
minCon="10"：mycat在启动之后，会在后端节点上自动连接线程
tempReadHostAvailable="1"：这个一主一从(1个writehost，1个readhost时)，可以开启这个参数，如果2个writehost，2个readhost时select user()监测心跳
Mycat分库分表
环境恢复
重启所有节点
systemctl restart mysqld3307
systemctl restart mysqld3308
systemctl restart mysqld3309
sysetmctl restart mysqld3310
查看主从状态
mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep "Running:"mysql-S /data/3308/mysql.sock -e "show slave status\G"|grep "Running:"mysql-S /data/3309/mysql.sock -e "show slave status\G"|grep "Running:"mysql-S /data/3310/mysql.sock -e "show slave status\G"|grep "Running:"
Mycat分布式架构--垂直分表
分片
垂直分片：将不同的表切分到不同的数据库中
水平切片：将同一种表按照某个条件切分到不同的数据库中
分表架构图：
cd /data/mycat/confmvschema.xml schema.xml.ha
vim schema.xml<?xml version="1.0"?>
select user()
select user()
创建测试库和表
mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;"mysql-S /data/3308/mysql.sock -e "create database taobao charset utf8;"mysql-S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";
mysql-S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"
重启mycat
mycat restart
读操作测试
mysql -uroot -p123456 -h10.0.0.51 -P8066
mysql>use TESTDB;
mysql>show tables;+------------------+
| Tables_in_taobao |
+------------------+
| order_t |
| user |
+------------------+
2 rows in set (0.00sec)
[root@db01/data/mycat/conf]# mysql -S /data/3307/mysql.sock -e "show tables from taobao"
+------------------+
| Tables_in_taobao |
+------------------+
| user |
+------------------+[root@db01/data/mycat/conf]# mysql -S /data/3308/mysql.sock -e "show tables from taobao"
+------------------+
| Tables_in_taobao |
+------------------+
| order_t |
+------------------+
Mycat分布式架构--水平拆分
垂直分片：将不同的表切分到不同的数据库中
水平切片：将同一种表按照某个条件切分到不同的数据库中
水平拆分架构图：
水平分库分表重要概念
水平拆分：
水平拆分也有两种：水平分表和水平分库分表。
水平分表：
水平分表和垂直分表区别在于垂直分表针对列，将不常用的列拆分到辅助表。而水平分表针对的是表，在同一个数据库中创建多张一样的表，比如我们在order数据库中创建三张订单表order1、order2、order3，然后插入订单时将id对3取余，根据不同的值存入不同的订单表，但是由于水平分表是将数据表存放在同一个数据库，水平分表可以降低单标的数据量，有助于提高查询效率。如果业务量很大一样会受到服务器lo的瓶颈，所以我们经常使用的还是水平分库分表。
水平分开库分表：
水平分库实际上就是在多台不同的机器分别创建数据库和数据表，比如订单表我们可以在两个有主从复制和MHA的集群里分别创建order数据库和order数据表，然后根据一定的逻辑将不同的数据存放在不同机器的数据库中。一般来说水平拆分主要有以下几种拆分规则：
范围拆分：比如订单表通过id拆分，id在1到500w在集群db01内的order数据库，id在501-1000w在集群db02内的order数据库，依次类推
地理拆分：将订单表按照不同地区将表中的数据拆分到不同数据库的order数据库中存储。比如可以安装省份来进行拆分。
取模拆分：取模就是刚才在水平分表讲到的比如有三个订单表，可以将id对3取余，但是区别在于水平分库分表是将同样的数据库和数据表存放在不同机器，所以可以说有效缓解单机瓶颈问题。
时间拆分：根据时间来拆分表中的数据，可以将表中创建时间超过一年的数据拆分到其他数据库的order数据库存储，原表中只保留被查询概率较高的数据而这部分数据往往都是近期才创建的，所以可以通过时间拆分实现冷热数据分离。
范围分片
比如说t3表
行数非常多，2000w(1-1000w:sh1 1000w-2000w:sh2)
访问非常频繁，用户访问较离散
修改schema.xml文件，定制分片策略
cp schema.xml schema.xml.1vim schema.xml
---对t3表进行定制分片策略rule.xml文件配置切分规则
vim rule.xml
id
rang-long
autopartition-long.txt
autopartition-long.txt 定制范围-range
# range start-end,data node index
# k=1000,M=10000.0-10=0
10-20=1#0,1是分片的编号
创建测试表
mysql -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"mysql-S /data/3308/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
测试
# 重启mycat
mycat restart
mysql-uroot -p123456 -h 127.0.0.1 -P 8066insert into t3(id,name) values(1,'a');
insert into t3(id,name) values(2,'b');
insert into t3(id,name) values(3,'c');
insert into t3(id,name) values(4,'d');
insert into t3(id,name) values(11,'aa');
insert into t3(id,name) values(12,'bb');
insert into t3(id,name) values(13,'cc');
insert into t3(id,name) values(14,'dd');
[root@db01 conf]# mysql-S /data/3308/mysql.sock -e "select * from taobao.t3"
+----+------+
| id | name |
+----+------+
| 11 | aa |
| 12 | bb |
| 13 | cc |
| 14 | dd |
+----+------+[root@db01 conf]# mysql-S /data/3307/mysql.sock -e "select * from taobao.t3"
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
取模(取余数)分片
取余分片方式：分片键(一个列)与节点数量进行取余，得到余数，将数据写入对应节点
复制配置文件
#修改配置文件
vim schema.xml
#查看和定义分片使用vim rule.xml2 #这里我们有几个节点就写几，这里我们是2个多实例节点
准备测试环境
#创建t4测试表
mysql-S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"mysql-S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"#重启mycat
mycat restart
#测试
mysql-root -p123456 -h10.0.0.051 -P8066
use TESTDB
insert into t4(id,name) values(1,'a');
insert into t4(id,name) values(2,'b');
insert into t4(id,name) values(3,'c');
insert into t4(id,name) values(4,'d');
insert into t4(id,name) values(6,'x'),(8,'y'),(10,'z');
#分别登录后端节点查询数据
mysql-S /data/3308/mysql.sock -e "select * from taobao.t4"
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
+----+------+mysql-S /data/3307/mysql.sock -e "select * from taobao.t4"
+----+------+
| id | name |
+----+------+
| 2 | b |
| 4 | d |
| 6 | x |
| 8 | y |
| 10 | z |
+----+------+
枚举分片
所谓的枚举分片，就是通过在配置文件中配置可能的枚举id，自己配置分片，本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国省份区县固定的，具体使用场景可根据自己具体的业务场景确定。
表格规划
t5 表idname telnum1 bj 1212
2 sh 22222
3 bj 3333
4 sh 44444
5 bj 5555#使用的函数
sharding-by-intfile
schema.xml配置文件
rule.xml分片配置文件
vim rule.xml
name
hash-int
partition-hash-int.txt
1###type这一列，打开中英文的支持1###这里行是添加的其他数据默认加入sh=1里面
注：根据name这个列条件分片name
partition-hash-int.txt配置
bj=0
sh=1DEFAULT_NODE=1#上面这个参数是其他数据往sh=1这个表里走
注：columns标识将要分片的表字段，algorithm分片函数， 其中分片函数配置中，mapFile标识配置文件名称
准备测试环境
mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"mysql-S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
重启mycat，查看结构
#重启
mycat restart
#连接加入表数据
mysql-uroot -p123456 -h10.0.0.51 -P8066
use TESTDB
insert into t5(id,name) values(1,'bj');
insert into t5(id,name) values(2,'sh');
insert into t5(id,name) values(3,'bj');
insert into t5(id,name) values(4,'sh');
insert into t5(id,name) values(5,'tj');
#查看结果
mysql-S /data/3308/mysql.sock -e "select * from taobao.t5"
+----+------+
| id | name |
+----+------+
| 2 | sh |
| 4 | sh |
| 5 | tj |
+----+------+mysql-S /data/3307/mysql.sock -e "select * from taobao.t5"
+----+------+
| id | name |
+----+------+
| 1 | bj |
| 3 | bj |
+----+------+
mycat全局表
a b c d .....jointselectt1.name ,t.x from t1jointselectt2.name ,t.x from t2jointselectt3.name ,t.x from t3join t
注：所有表都要去joint这张表，我们要把这张表变成全局表
应用场景：
如果你的业务中有些数据类似于数据字典，比如配置文件的配置，常用业务的配置或者数据量不大很少变动的表，这些表往往不是特别大，而且大部分的业务场景都会用到，那么这种表适合于Mycat全局表，无须对数据进行切分，要在所有的分片上保存一份数据即可，Mycat 在Join操作中，业务表与全局表进行Join聚合会优先选择相同分片内的全局表join，避免跨库Join，在进行数据插入操作时，mycat将把数据分发到全局表对应的所有分片执行，在进行数据读取时候将会随机获取一个节点读取数据。
编辑配置文件schema.xml
vim schema.xml
后端数据准备
mysql -S /data/3307/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"mysql-S /data/3308/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"
重启mycat
mycat restart
测试
mysql -uroot -p123456 -h10.0.0.51 -P8066
use TESTDB
insert into t_area(id,name) values(1,'a');
insert into t_area(id,name) values(2,'b');
insert into t_area(id,name) values(3,'c');
insert into t_area(id,name) values(4,'d');
#查看表
mysql-S /data/3308/mysql.sock -e "select * from taobao.t_area"
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+mysql-S /data/3307/mysql.sock -e "select * from taobao.t_area"
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
E-R分片
join是关系数据库最常用的一个特性，然而在分布式环境中，跨分片的join最复杂，最难解决
比如：
mycat逻辑库word，两张表格t1，t2.做了分库策略，t1放到了datanode1，t2放到了datanode2，如果我t1 join t2检索数据，
怎么办?
这就是E-R关系策略要解决的问题。
mycat借鉴了table group的概念，将子表的存储位置依赖于子表，并且在物理上紧邻存放，解决了join的效率和性能问题。E-R关系的数据库分片策略，根据这一思路，将子表的记录和所关联的父表存档在同一个数据分片上。
ajoinb
on a.xx=b.yy
为了防止跨分片join，可以使用E-R模式
### a表结构idname1a2b3c4d
### b表结构idaddr aid1001 bj 1
1002 sh 2
1003 tj 3
1004 wh 4### 两表之间join关联select * from a join b on a.id = b.aid where a.name=d
例子：a表和b表做E-R分片，并进行join
1. 修改配置文件
vim schema.xml
注：a是主，b是负
2. 修改rule.xml mod-log分片策略：
vim rule.xml
id
mod-long_china
2
注：tableRule里的name是我们自定义的，在文件下方的函数也要进行创建，可以参考旁边的mod-log
3. 创建测试表
mysql -S /data/3307/mysql.sock -e "use taobao;create table a (id int not null primary key auto_increment,name varchar(20) not null);"mysql-S /data/3307/mysql.sock -e "use taobao;create table b (id int not null primary key auto_increment,addr varchar(20) not null ,aid int );"mysql-S /data/3308/mysql.sock -e "use taobao;create table a (id int not null primary key auto_increment,name varchar(20) not null);"mysql-S /data/3308/mysql.sock -e "use taobao;create table b (id int not null primary key auto_increment,addr varchar(20) not null ,aid int );"
4. 重启mycat测试
mycat restart
mysql-uroot -p123456 -h10.0.0.51 -P8066
use TESTDB
insert into a(id,name) values(1,'a');
insert into a(id,name) values(2,'b');
insert into a(id,name) values(3,'c');
insert into a(id,name) values(4,'d');
insert into a(id,name) values(5,'e');
insert into b(id,addr,aid) values(1001,'bj',1);
insert into b(id,addr,aid) values(1002,'sj',3);
insert into b(id,addr,aid) values(1003,'sd',4);
insert into b(id,addr,aid) values(1004,'we',2);
insert into b(id,addr,aid) values(1005,'er',5);
5. 后端数据节点数据分布
mysql -S /data/3307/mysql.sock -e "select * from taobao.a"mysql-S /data/3307/mysql.sock -e "select * from taobao.b"mysql-S /data/3308/mysql.sock -e "select * from taobao.a"mysql-S /data/3308/mysql.sock -e "select * from taobao.b"
server.xml解读
管理逻辑库 默认是TESTDB
1. 逻辑库名
schema.xmlserver.xml
123456
oldboy,oldguo
oldboy
user
oldboy
true
oldboy
2. 添加一个逻辑库
schema.xml
server.xmloldboy,oldguo
198
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
