mysql分布式添加逻辑库怎么加 mysql分布式架构

转载

mob64ca14031c97 2023-08-16 04:47:21

文章标签 mysql分布式添加逻辑库怎么加数据库架构 mysql d3 sed 文章分类 MySQL 数据库

一、Mycat简介

Mycat是一个开源的分布式数据库系统，其核心功能是分库分表，即将一个达标水平分割成多个小表，存储在后端MySQL或者其他数据库里。取名Mycat原因一是简单好记，另一个则是希望未来能够入驻 Apache，Apache的开源产品Tomcat也是一只猫。MyCAT 是一个彻底开源的，面向企业应用开发的“大数据库集群” 支持事务、ACID、可以替代Mysql的加强版数据库; 一个可以视为“Mysql”集群的企业级数据库，用来替代昂贵的Oracle集群 ; 一个融合内存缓存技术、Nosql技术、HDFS大数据的新型SQL Server; 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品 ; 一个新颖的数据库中间件产品。

二、Mycat实验测试

1. Mycat基础架构准备

1.1 环境准备：

两台虚拟机 mysql1 mysql2

1.2 每台创建相关目录初始化数据

// mysql1：

mkdir /data/33{07..10}/data -p

#初始化
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/usr/local/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/usr/local/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3310/data --basedir=/usr/local/mysql

//  mysql2：
mkdir /data/33{07..10}/data -p

#初始化
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/usr/local/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/usr/local/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3310/data --basedir=/usr/local/mysql

1.3 准备mysql1和mysql2配置文件和启动脚本。

注意：server_id不能一致，要不然主从是做不了的

//mysql1
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/systemd/system/mysqld3307.service<<EOF
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service<<EOF
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3309.service<<EOF
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3310.service<<EOF
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF

//mysql2
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF


cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=20
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/systemd/system/mysqld3307.service<<EOF
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service<<EOF
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3309.service<<EOF
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF

1.4 修改一下权限，启动多实例

// mysql1

chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310

// -e不登录mysql，执行sql内部命令，查看自己的serverid，目的是检测是否启动配置成功
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'"

// 如果是桌面安装可以过滤一下端口号看看开没开起
netstat -anput | grep 33

// mysql2
chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310
// 查看自己的sever_id号
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'"

1.5 节点主从规划

mysql分布式添加逻辑库怎么加 mysql分布式架构_mysql分布式添加逻辑库怎么加

1.6 开始配置主从

// mysql1-3307 创建一个连接用户并附复制权
mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.8.%' identified by '123';"

// mysql1-3308 创建一个连接用户并附复制权
mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.8.%' identified by '123';"

// mysql2-3307 创建一个连接用户并附复制权

mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.8.%' identified by '123';"

// mysql2-3308 创建一个连接用户并附复制权

mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.8.%' identified by '123';"

//第一组四节点结构

## mysql1（8.129）3307-（8.130）3307：
// 连接8.130 3307端口的mysql并认作主
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.8.130', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
// 开启slave
mysql  -S /data/3307/mysql.sock -e "start slave;"
// 查看slave状态
mysql  -S /data/3307/mysql.sock -e "show slave status\G"

## mysql2（8.129）3308-（8.130）3308：
// 连接8.129 3307端口的mysql并认作主
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.8.129', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
// 开启slave
mysql  -S /data/3307/mysql.sock -e "start slave;"
// 查看slave状态
mysql1  -S /data/3307/mysql.sock -e "show slave status\G"

## mysql1（8.129） 3309-3307：
// 连接8.129 3307端口的mysql并认作主
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.8.129', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
// 开启slave
mysql  -S /data/3307/mysql.sock -e "start slave;"
// 查看slave状态
mysql  -S /data/3307/mysql.sock -e "show slave status\G"

## mysql1（8.130） 3309-3307：
// 连接8.130 3307端口的mysql并认作主
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.8.130', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
// 开启slave
mysql  -S /data/3307/mysql.sock -e "start slave;"
// 查看slave状态
mysql  -S /data/3307/mysql.sock -e "show slave status\G"

## mysql1（8.129） 3310-3308：
// 连接8.129 3307端口的mysql并认作主
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.8.129', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
// 开启slave
mysql  -S /data/3307/mysql.sock -e "start slave;"
// 查看slave状态
mysql  -S /data/3307/mysql.sock -e "show slave status\G"

## mysql1（8.130） 3310-3308：
// 连接8.130 3307端口的mysql并认作主
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.8.130', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
// 开启slave
mysql  -S /data/3307/mysql.sock -e "start slave;"
// 查看slave状态
mysql  -S /data/3307/mysql.sock -e "show slave status\G"

1.7 重新检测主从状态

// mysql1过滤是否有8个yes

mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes

// mysql2过滤是否有8个yes

mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes

// 如果中间出现错误，在每个节点执行以下命令，从1.6部重新开始

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;"

2. Mycat安装

2.1 预先安装Java运行环境

// 配好网络yum源，直接下载java

yum -y install java

2.2 下载mycat包

http://www.mycat.org.cn/

2.3 解压文件

// tar解压到指定目录里

tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local

2.4 启动和连接

// 配置环境变量

vim /etc/profile
// 在最后一行添加环境变量
export PATH=/usr/local/mycat/bin:$PATH
// 重启文件
source /etc/profile

// 添加Mycat的最长启动时间，如果300秒内启动不成功会自动断开连接

vim /usr/local/mycat/conf/wrapper.conf
wrapper.startup.timeout=300

// 启动Mycat
mycat start

// 检查是否开启成功
tail -f /usr/local/mycat/logs/wrapper.conf

//连接Mycat
mysql -uroot -p123456 -h 127.0.0.1 -P8806

## 解释一下为什么密码是123456，这个密码可以在/usr/local/mycat/conf/server.xml里改，端口和密码都是默认的。

3. 数据库分布式架构方式

3.1 垂直拆分

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，可实现单库单表。

3.2 水平拆分

可以将一个大表拆分成很多小表，可分为

范围拆分：可分多个表，可以自定义，ID列1-50分一个表，50-100分一个表。

取模拆分：如果有ID列都会除2，余0分一个表，余1分个表。

枚举拆分：比如一个名单，有男有女，可以将男孩分一个表，女孩分一个表。

3.3 配置文件结构介绍

// 创建root用户，并把数据导入

## mysql1（在mycat下载点上创建）:

mysql -S /data/3307/mysql.sock 
grant all on *.* to root@'192.168.8.%' identified by '123';
source /root/world.sql

mysql -S /data/3308/mysql.sock 
grant all on *.* to root@'192.168.8.%' identified by '123';
source /root/world.sql

// 将root目录下的world表导入到数据库中，因为做了主从，导两个节点所有节点都会有。


// 进入目录
cd /usr/local/mycat/conf
// 将文件改名
mv schema.xml schema.xml.bak
cat schema.xml.bak 

##开头
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/">

## mycat 逻辑库定义:
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">  
</schema> 

##数据节点定义:
	<dataNode name="sh1" dataHost="laotian1" database= "world" />   

## 后端主机定义:
        <dataHost name="laoli1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">    
                <heartbeat>select user()</heartbeat>  
        <writeHost host="db1" url="192.168.8.129:3307" user="root" password="123">
                        <readHost host="db2" url="192.168.8.129:3309" user="root" password="123" /> 
        </writeHost> 
        </dataHost>  

## 文件结尾			
</mycat:schema>

3.4 Mycat高可用+读写分离

## 编辑schema.xml文件

vim /usr/local/mycat/conf/schema.xml 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> 
</schema>  
        <dataNode name="sh1" dataHost="laoli1" database= "world" />         
        <dataHost name="laoli1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">    
                <heartbeat>select user()</heartbeat>  
        ## 写1
        <writeHost host="db1" url="192.168.8.129:3307" user="root" password="123">
        ## 读1
        <readHost host="db2" url="192.168.8.129:3309" user="root" password="123" /> 
        </writeHost> 
        ## 写2
		<writeHost host="db3" url="192.168.8.130:3307" user="root" password="123">
        ## 读2
        <readHost host="db4" url="192.168.8.130:3309" user="root" password="123" /> 
        </writeHost> 
        </dataHost>  
</mycat:schema>
         


备注：
balance属性
负载均衡类型，目前的取值有3种： 
1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。 
2. balance="1"，全部的readHost与standby writeHost参与select语句的负载均衡，简单的说，
  当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。 
3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。
writeType属性
负载均衡类型，目前的取值有2种： 
1. writeType="0", 所有写操作发送到配置的第一个writeHost，
第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为主，切换记录在配置文件中:dnindex.properties . 
2. writeType=“1”，所有写操作都随机的发送到配置的writeHost，但不推荐使用
switchType属性
-1 表示不自动切换 
1 默认值，自动切换 
2 基于MySQL主从同步的状态决定是否切换 ，心跳语句为 show slave status 
datahost其他配置
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">

PS：本文章出自田写作，一篇文章固然讲不通Mycat的实际用法，大家可以按照我的步骤把Mycat装上，具体任务具体分析。希望大家能在IT行业越走越远，谢谢~

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。