第二十一周-day91-数据库day(13-14) 分布式架构与 MyCAT安装和使用(1)-CSDN博客

本文链接：https://blog.csdn.net/m0_59157465/article/details/138320367

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=/application/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=/application/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=/application/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=/application/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=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF

db02=======
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/application/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=/application/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=/application/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=/application/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


#### 3.5 修改权限，启动多实例

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

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’”



> 
> ![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNjk1MjE0OS0wODQ4MzA2OGI0ZGQ5OTEwLnBuZw?x-oss-process=image/format,png)
> 
> 
> 


#### 3.6 节点主从规划

箭头指向谁是主库
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

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


#### 3.7 分片规划

shard1：
Master：10.0.0.51:3307
slave1：10.0.0.51:3309
Standby Master：10.0.0.52:3307
slave2：10.0.0.52:3309
shard2：
Master：10.0.0.52:3308
slave1：10.0.0.52:3310
Standby Master：10.0.0.51:3308
slave2：10.0.0.51:3310


#### 3.8 开始配置

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”


#### 3.9 检测主从状态

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:



> 
> ![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNjk1MjE0OS0yOGJiYTViNTk2NTUzMmE4LnBuZw?x-oss-process=image/format,png)
> 
> 
> 


#### 3.10 MySQL分布式架构介绍


![oldguo导师的图](https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNjk1MjE0OS04ODQ5NWYzODIxN2JkN2IwLnBuZw?x-oss-process=image/format,png)

schema拆分及业务分库
垂直拆分-分库分表
水平拆分-分片




---


## 4. MyCAT安装


#### 4.1 预先安装Java运行环境

[root@db02 ~]# yum install -y java


#### 4.2 上传到/application

[root@db02 /server/tools]# wget http://192.168.0.163/mysql/Mycat-server-1.6.5-release-20180117003034-linux.tar.gz
[root@db02 /server/tools]# tar xf Mycat-server-1.6.5-release-20180117003034-linux.tar.gz

[root@db02 /server/tools]# ll mycat/
total 12
drwxr-xr-x 2 root root 190 Aug 21 17:10 bin
drwxrwxrwx 2 root root 6 Mar 1 2016 catlet
drwxrwxrwx 4 root root 4096 Aug 21 17:10 conf
drwxr-xr-x 2 root root 4096 Aug 21 17:10 lib
drwxrwxrwx 2 root root 6 Jan 17 2018 logs
-rwxrwxrwx 1 root root 219 Jan 17 2018 version.txt

[root@db02 /server/tools/mycat]# ll conf
autopartition-long.txt
auto-sharding-long.txt
auto-sharding-rang-mod.txt
cacheservice.properties
dbseq.sql
ehcache.xml
index_to_charset.properties
log4j2.xml
migrateTables.properties
myid.properties
partition-hash-int.txt
partition-range-mod.txt
rule.xml
schema.xml
sequence_conf.properties
sequence_db_conf.properties
sequence_distributed_conf.properties
sequence_time_conf.properties
server.xml
sharding-by-enum.txt
wrapper.conf
zkconf
zkdownload


#### 4.3 启动和连接

移动到/application下
[root@db02 ~]# mv /server/tools/mycat /application/

配置环境变量
vim /etc/profile
export PATH=/application/mycat/bin:$PATH
source /etc/profile

启动
[root@db02 ~]# mycat start
Starting Mycat-server…

连接mycat：
mysql -uroot -p123456 -h 10.0.0.52 -P8066
mysql>


#### 4.4. 测试数据准备

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


#### 4.5. 核心配置文件使用介绍(schema.xml)

[root@db02 /application/mycat/conf]# pwd
/application/mycat/conf
[root@db02 /application/mycat/conf]# vim schema.xml

<?xml version="1.0"?>

<mycat:schema xmlns:mycat=“http://io.mycat/”>

select user()

</mycat:schema>


#### 配置文件的介绍：


**https://blog.csdn.net/qq\_34457768/article/details/81625758**

balance属性
负载均衡类型，目前的取值有3种：

balance=“0”, 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
balance=“1”，全部的readHost与standby writeHost参与select语句的负载均衡，简单的说，
当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。
balance=“2”，所有读操作都随机的在writeHost、readhost上分发。

writeType属性
负载均衡类型，目前的取值有2种：

writeType=“0”, 所有写操作发送到配置的第一个writeHost，
第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为主，切换记录在配置文件中:dnindex.properties .
writeType=“1”，所有写操作都随机的发送到配置的writeHost，但不推荐使用

switchType属性
-1 表示不自动切换
1 默认值，自动切换
2 基于MySQL主从同步的状态决定是否切换，心跳语句为 show slave status

datahost其他配置

maxCon=“1000”：最大的并发连接数
minCon=“10” ：mycat在启动之后，会在后端节点上自动开启的连接线程

tempReadHostAvailable=“1”
这个一主一从时（1个writehost，1个readhost时），可以开启这个参数，如果2个writehost，2个readhost时
select user() 监测心跳

(1) 逻辑库定义

(2) 数据节点定义

(3) 数据主机定义

select user()


#### 4.6 连接测试读写分离

[root@db02 ~]# mysql -uroot -p123456 -h 10.0.0.52 -P8066
mysql> select @@server_id; #读
±------------+
| @@server_id |
±------------+
| 9 |
±------------+
1 row in set (0.00 sec)

mysql> begin;select @@server_id;commit; #写
Query OK, 0 rows affected (0.00 sec)

±------------+
| @@server_id |
±------------+
| 7 |
±------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


#### 4.7 读写分离+高可用功能

#将原本的配置文件备份
[root@db02 /application/mycat/conf]# mv schema.xml schema.xml.bak

#添加新的配置文件(有四个节点)
[root@db02 /application/mycat/conf]# vim schema.xml

<?xml version="1.0"?>

<mycat:schema xmlns:mycat=“http://io.mycat/”>

select user()

</mycat:schema>



> 
> ![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNjk1MjE0OS04NjJkYTczMGFmMjhjMGNiLnBuZw?x-oss-process=image/format,png)
> 
> 
> 


#### 4.8 拥有了高可用的读写分离

#重启mycat
[root@db02 /application/mycat/conf]# mycat restart
Stopping Mycat-server…
Stopped Mycat-server.
Starting Mycat-server…
[root@db02 /application/mycat/conf]# mysql -uroot -p123456 -h 10.0.0.52 -P8066

读是三个节点轮询

mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 9 |
±------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 17 |
±------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 9 |
±------------+
1 row in set (0.00 sec)

#写只有一个节点，现在节点是 7
mysql> begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)

±------------+
| @@server_id |
±------------+
| 7 |
±------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.00 sec)


#### 4.9 检查高可用功能



> 
> 节点号的切换与配置文件指定的有关，莫懵
> 
> 
>

#将db01的mysql3307数据库宕掉
[root@db01 ~]# systemctl stop mysqld3307.service

#在db02上进mycat库读写
mysql> select @@server_id; 切换为19节点
±------------+
| @@server_id |
±------------+
| 19 |
±------------+
1 row in set (0.00 sec)

mysql> begin;select @@server_id;commit; 3切换为17节点
Query OK, 0 rows affected (0.00 sec)

±------------+
| @@server_id |
±------------+
| 17 |
±------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


#### 4.10 垂直分表


垂直拆分  
 ![灵魂画师—oldguo](https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNjk1MjE0OS0xZDZiYzg2ZDc4NDdkNTcyLnBuZw?x-oss-process=image/format,png)



> 
> 修改mycat的配置文件：
> 
> 
>

[root@db02 ~]# cd /application/mycat/conf/
[root@db02 /application/mycat/conf]# mv schema.xml schema.xml.ha
[root@db02 /application/mycat/conf]# vim schema.xml

<?xml version="1.0"?>

<mycat:schema xmlns:mycat=“http://io.mycat/”>

select user()

select user()

</mycat:schema>

#重启mycat
mycat restart



> 
> ![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNjk1MjE0OS0wMzBkNGYzZGVjYjA1ZDkwLnBuZw?x-oss-process=image/format,png)
> 
> 
> 


#### 4.11 创建测试库和表:（db01）

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))”

#db02上重启mycat
[root@db02 /application/mycat/conf]# mycat restart
Stopping Mycat-server…
Stopped Mycat-server.
Starting Mycat-server…


#### 4.12 测试垂直分表

#为user表和order表添加数据,随便添加，只是测试

mysql> insert into user(id,name) values(2,‘wanglaoji’);
Query OK, 1 row affected (0.02 sec)
…
mysql> select * from user;
±-----±----------+
| id | name |
±-----±----------+
| 1 | zs |
| 2 | wahaha |
| 3 | quechao |
±-----±----------+

mysql> insert into order_t(id,name) values(99,‘lcx’);
Query OK, 1 row affected (0.01 sec)
…
mysql> select * from order_t;
±-----±--------+
| id | name |
±-----±--------+
| 10 | huiyuan |
| 99 | lcx |
±-----±--------+


#### 4.13 查看

#查看3307节点的user表
[root@db01 ~]# mysql -S /data/3307/mysql.sock
mysql> use taobao
mysql> show tables;
±-----------------+
| Tables_in_taobao |
±-----------------+
| user |
±-----------------+
mysql> select * from user;
±-----±----------+
| id | name |
±-----±----------+
| 1 | zs |
| 1 | zs |
| 2 | wahaha |
| 3 | quechao |
±-----±----------+

#查看3308节点的order表
[root@db01 ~]# mysql -S /data/3308/mysql.sock
mysql> use taobao
mysql> show tables;
±-----------------+
| Tables_in_taobao |
±-----------------+
| order_t |
±-----------------+

mysql> select * from order_t;
±-----±--------+
| id | name |
±-----±--------+
| 10 | huiyuan |
| 99 | lcx |
±-----±--------+


#### 4.14 MyCAT核心特性——分片（水平拆分）

分片：对一个"bigtable"，比如说t3表
(1)行数非常多，800w
(2)访问非常频繁

分片的目的：
（1）将大数据量进行分布存储
（2）提供均衡的访问路由

分片策略：
范围 range 800w 1-400w 400w01-800w
取模 mod 取余数
枚举
哈希 hash
时间流水

优化关联查询
全局表
ER分片


#### 4.15 范围分片

比如说t3表
(1)行数非常多，2000w（1-1000w:sh1 1000w01-2000w:sh2）
(2)访问非常频繁，用户访问较离散

[root@db02 /application/mycat/conf]# cp schema.xml schema.xml.1

vim schema.xml #修改这一段

vim rule.xml

最全的Linux教程，Linux从入门到精通

======================

linux从入门到精通(第2版)
Linux系统移植
Linux驱动开发入门与实战
LINUX 系统移植第2版
Linux开源网络全栈详解从DPDK到OpenFlow

华为18级工程师呕心沥血撰写3000页Linux学习笔记教程

第一份《Linux从入门到精通》466页

====================

内容简介

====

本书是获得了很多读者好评的Linux经典畅销书**《Linux从入门到精通》的第2版**。本书第1版出版后曾经多次印刷，并被51CTO读书频道评为“最受读者喜爱的原创IT技术图书奖”。本书第﹖版以最新的Ubuntu 12.04为版本，循序渐进地向读者介绍了Linux 的基础应用、系统管理、网络应用、娱乐和办公、程序开发、服务器配置、系统安全等。本书附带1张光盘，内容为本书配套多媒体教学视频。另外,本书还为读者提供了大量的Linux学习资料和Ubuntu安装镜像文件，供读者免费下载。

华为18级工程师呕心沥血撰写3000页Linux学习笔记教程