部署架构图

image.png

1 环境搭建

1.1 部署多实例

1、虚拟机环境
两台虚拟机 db01 db02，每个节点创建四个mysql实例：3307、3308、3309、3310
2、删除历史环境数据
pkill mysqld
rm -rf /data/330
mv /etc/my.cnf /etc/my.cnf.bak
*3、创建相关目录初始化数据

  1. mkdir /data/mysql/instance-33{07,08,09,10}/{data,binlog,errorlog} -pv
  2. chown -R mysql.mysql /data/*
  3. mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/instance-3307/data --basedir=/app/mysql
  4. mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/instance-3308/data --basedir=/app/mysql
  5. mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/instance-3309/data --basedir=/app/mysql
  6. mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/instance-3310/data --basedir=/app/mysql

4、准备db01配置文件和启动脚本

  1. cat >/data/mysql/instance-3307/my.cnf<<EOF
  2. [mysqld]
  3. basedir=/app/mysql
  4. datadir=/data/mysql/instance-3307/data
  5. socket=/data/mysql/instance-3307/mysql.sock
  6. port=3307
  7. log-error=/data/mysql/instance-3307/errorlog/mysql.log
  8. log_bin=/data/mysql/instance-3307/binlog/mysql-bin
  9. binlog_format=row
  10. skip-name-resolve
  11. server-id=7
  12. gtid-mode=on
  13. enforce-gtid-consistency=true
  14. log-slave-updates=1
  15. EOF
  17. cat >/data/mysql/instance-3308/my.cnf<<EOF
  18. [mysqld]
  19. basedir=/app/mysql
  20. datadir=/data/mysql/instance-3308/data
  21. socket=/data/mysql/instance-3308/mysql.sock
  22. port=3308
  23. log-error=/data/mysql/instance-3308/errorlog/mysql.log
  24. log_bin=/data/mysql/instance-3308/binlog/mysql-bin
  25. binlog_format=row
  26. skip-name-resolve
  27. server-id=8
  28. gtid-mode=on
  29. enforce-gtid-consistency=true
  30. log-slave-updates=1
  31. EOF
  33. cat >/data/mysql/instance-3309/my.cnf<<EOF
  34. [mysqld]
  35. basedir=/app/mysql
  36. datadir=/data/mysql/instance-3309/data
  37. socket=/data/mysql/instance-3309/mysql.sock
  38. port=3309
  39. log-error=/data/mysql/instance-3309/errorlog/mysql.log
  40. log_bin=/data/mysql/instance-3309/binlog/mysql-bin
  41. binlog_format=row
  42. skip-name-resolve
  43. server-id=9
  44. gtid-mode=on
  45. enforce-gtid-consistency=true
  46. log-slave-updates=1
  47. EOF
  49. cat >/data/mysql/instance-3310/my.cnf<<EOF
  50. [mysqld]
  51. basedir=/app/mysql
  52. datadir=/data/mysql/instance-3310/data
  53. socket=/data/mysql/instance-3310/mysql.sock
  54. port=3310
  55. log-error=/data/mysql/instance-3310/errorlog/mysql.log
  56. log_bin=/data/mysql/instance-3310/binlog/mysql-bin
  57. binlog_format=row
  58. skip-name-resolve
  59. server-id=10
  60. gtid-mode=on
  61. enforce-gtid-consistency=true
  62. log-slave-updates=1
  63. EOF
  65. cat >/etc/systemd/system/mysqld-3307.service<<EOF
  66. [Unit]
  67. Description=MySQL Server
  68. Documentation=man:mysqld(8)
  69. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  70. After=network.target
  71. After=syslog.target
  72. [Install]
  73. WantedBy=multi-user.target
  74. [Service]
  75. User=mysql
  76. Group=mysql
  77. ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/mysql/instance-3307/my.cnf
  78. LimitNOFILE = 5000
  79. EOF
  81. cat >/etc/systemd/system/mysqld-3308.service<<EOF
  82. [Unit]
  83. Description=MySQL Server
  84. Documentation=man:mysqld(8)
  85. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  86. After=network.target
  87. After=syslog.target
  88. [Install]
  89. WantedBy=multi-user.target
  90. [Service]
  91. User=mysql
  92. Group=mysql
  93. ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/mysql/instance-3308/my.cnf
  94. LimitNOFILE = 5000
  95. EOF
  97. cat >/etc/systemd/system/mysqld-3309.service<<EOF
  98. [Unit]
  99. Description=MySQL Server
  100. Documentation=man:mysqld(8)
  101. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  102. After=network.target
  103. After=syslog.target
  104. [Install]
  105. WantedBy=multi-user.target
  106. [Service]
  107. User=mysql
  108. Group=mysql
  109. ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/mysql/instance-3309/my.cnf
  110. LimitNOFILE = 5000
  111. EOF
  112. cat >/etc/systemd/system/mysqld-3310.service<<EOF
  113. [Unit]
  114. Description=MySQL Server
  115. Documentation=man:mysqld(8)
  116. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  117. After=network.target
  118. After=syslog.target
  119. [Install]
  120. WantedBy=multi-user.target
  121. [Service]
  122. User=mysql
  123. Group=mysql
  124. ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/mysql/instance-3310/my.cnf
  125. LimitNOFILE = 5000
  126. EOF

5、准备db02配置文件和启动脚本

  1. cat >/data/mysql/instance-3307/my.cnf<<EOF
  2. [mysqld]
  3. basedir=/app/mysql
  4. datadir=/data/mysql/instance-3307/data
  5. socket=/data/mysql/instance-3307/mysql.sock
  6. port=3307
  7. log-error=/data/mysql/instance-3307/errorlog/mysql.log
  8. log_bin=/data/mysql/instance-3307/binlog/mysql-bin
  9. binlog_format=row
  10. skip-name-resolve
  11. server-id=17
  12. gtid-mode=on
  13. enforce-gtid-consistency=true
  14. log-slave-updates=1
  15. EOF
  17. cat >/data/mysql/instance-3308/my.cnf<<EOF
  18. [mysqld]
  19. basedir=/app/mysql
  20. datadir=/data/mysql/instance-3308/data
  21. socket=/data/mysql/instance-3308/mysql.sock
  22. port=3308
  23. log-error=/data/mysql/instance-3308/errorlog/mysql.log
  24. log_bin=/data/mysql/instance-3308/binlog/mysql-bin
  25. binlog_format=row
  26. skip-name-resolve
  27. server-id=18
  28. gtid-mode=on
  29. enforce-gtid-consistency=true
  30. log-slave-updates=1
  31. EOF
  33. cat >/data/mysql/instance-3309/my.cnf<<EOF
  34. [mysqld]
  35. basedir=/app/mysql
  36. datadir=/data/mysql/instance-3309/data
  37. socket=/data/mysql/instance-3309/mysql.sock
  38. port=3309
  39. log-error=/data/mysql/instance-3309/errorlog/mysql.log
  40. log_bin=/data/mysql/instance-3309/binlog/mysql-bin
  41. binlog_format=row
  42. skip-name-resolve
  43. server-id=19
  44. gtid-mode=on
  45. enforce-gtid-consistency=true
  46. log-slave-updates=1
  47. EOF
  49. cat >/data/mysql/instance-3310/my.cnf<<EOF
  50. [mysqld]
  51. basedir=/app/mysql
  52. datadir=/data/mysql/instance-3310/data
  53. socket=/data/mysql/instance-3310/mysql.sock
  54. port=3310
  55. log-error=/data/mysql/instance-3310/errorlog/mysql.log
  56. log_bin=/data/mysql/instance-3310/binlog/mysql-bin
  57. binlog_format=row
  58. skip-name-resolve
  59. server-id=20
  60. gtid-mode=on
  61. enforce-gtid-consistency=true
  62. log-slave-updates=1
  63. EOF
  65. cat >/etc/systemd/system/mysqld-3307.service<<EOF
  66. [Unit]
  67. Description=MySQL Server
  68. Documentation=man:mysqld(8)
  69. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  70. After=network.target
  71. After=syslog.target
  72. [Install]
  73. WantedBy=multi-user.target
  74. [Service]
  75. User=mysql
  76. Group=mysql
  77. ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/mysql/instance-3307/my.cnf
  78. LimitNOFILE = 5000
  79. EOF
  81. cat >/etc/systemd/system/mysqld-3308.service<<EOF
  82. [Unit]
  83. Description=MySQL Server
  84. Documentation=man:mysqld(8)
  85. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  86. After=network.target
  87. After=syslog.target
  88. [Install]
  89. WantedBy=multi-user.target
  90. [Service]
  91. User=mysql
  92. Group=mysql
  93. ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/mysql/instance-3308/my.cnf
  94. LimitNOFILE = 5000
  95. EOF
  97. cat >/etc/systemd/system/mysqld-3309.service<<EOF
  98. [Unit]
  99. Description=MySQL Server
  100. Documentation=man:mysqld(8)
  101. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  102. After=network.target
  103. After=syslog.target
  104. [Install]
  105. WantedBy=multi-user.target
  106. [Service]
  107. User=mysql
  108. Group=mysql
  109. ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/mysql/instance-3309/my.cnf
  110. LimitNOFILE = 5000
  111. EOF
  112. cat >/etc/systemd/system/mysqld-3310.service<<EOF
  113. [Unit]
  114. Description=MySQL Server
  115. Documentation=man:mysqld(8)
  116. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  117. After=network.target
  118. After=syslog.target
  119. [Install]
  120. WantedBy=multi-user.target
  121. [Service]
  122. User=mysql
  123. Group=mysql
  124. ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/mysql/instance-3310/my.cnf
  125. LimitNOFILE = 5000
  126. EOF

6、权限修改，启动多实例

  1. chown -R mysql.mysql /data/*
  2. systemctl restart mysqld-3307
  3. systemctl restart mysqld-3308
  4. systemctl restart mysqld-3309
  5. systemctl restart mysqld-3310
  6. mysql -S /data/mysql/instance-3307/mysql.sock -e "show variables like 'server_id'"
  7. mysql -S /data/mysql/instance-3308/mysql.sock -e "show variables like 'server_id'"
  8. mysql -S /data/mysql/instance-3309/mysql.sock -e "show variables like 'server_id'"
  9. mysql -S /data/mysql/instance-3310/mysql.sock -e "show variables like 'server_id'"

1.2 主从及分片规划

1.2.1 主从规划

  1. 箭头指向谁是主库
  2.     10.0.0.191:3307    <----->  10.0.0.192:3307
  3.     10.0.0.191:3309    ------>  10.0.0.191:3307
  4.     10.0.0.192:3309    ------>  10.0.0.192:3307
  5.     10.0.0.192:3308  <----->    10.0.0.191:3308
  6.     10.0.0.192:3310  ----->     10.0.0.192:3308
  7.     10.0.0.191:3310  ----->     10.0.0.191:3308

1.2.2 分片规划

  1. shard1:
  2.     Master:10.0.0.191:3307
  3.     slave1:10.0.0.191:3309
  4.     Standby Master:10.0.0.192:3307
  5.     slave2:10.0.0.192:3309
  6. shard2:
  7.     Master:10.0.0.192:3308
  8.     slave1:10.0.0.192:3310
  9.     Standby Master:10.0.0.191:3308
  10.     slave2:10.0.0.191:3310

1.3 主从及分片配置

1.3.1 分片1配置

1.3.1.1 10.0.0.191:3307 <-> 10.0.0.192:3307 互为主从配置

1、db01和db02上创建复制用户

  1. mysql  -S /data/mysql/instance-3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by 'abc123..';"
  2. mysql  -S /data/mysql/instance-3307/mysql.sock -e "grant all  on *.* to root@'10.0.0.%' identified by 'abc123..'  with grant option;"

2、db01上执行，此时db01为从库加入主库db02

  1. mysql  -S /data/mysql/instance-3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.192', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='abc123..';"
  2. mysql  -S /data/mysql/instance-3307/mysql.sock -e "start slave;"
  3. mysql  -S /data/mysql/instance-3307/mysql.sock -e "show slave status\G"

3、在db02上执行，此时db02为从库加入主库db01

  1. mysql  -S /data/mysql/instance-3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.191', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='abc123..';"
  2. mysql  -S /data/mysql/instance-3307/mysql.sock -e "start slave;"
  3. mysql  -S /data/mysql/instance-3307/mysql.sock -e "show slave status\G"

1.3.1.2 10.0.0.191:3309 -> 10.0.0.191:3307

将10.0.0.191:3309实例配置为10.0.0.191:3307实例的从库
db01上执行，加入主库10.0.0.191:3307

  1. mysql  -S /data/mysql/instance-3309/mysql.sock  -e "CHANGE MASTER TO MASTER_HOST='10.0.0.191', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='abc123..';"
  2. mysql  -S /data/mysql/instance-3309/mysql.sock  -e "start slave;"
  3. mysql  -S /data/mysql/instance-3309/mysql.sock  -e "show slave status\G"

1.3.1.3 10.0.0.192:3309 -> 10.0.0.192:3307

将10.0.0.192:3309实例配置为10.0.0.192:3307实例的从库
db02上执行，加入主库10.0.0.192:3307

  1. mysql  -S /data/mysql/instance-3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.192', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='abc123..';"
  2. mysql  -S /data/mysql/instance-3309/mysql.sock -e "start slave;"
  3. mysql  -S /data/mysql/instance-3309/mysql.sock -e "show slave status\G"

1.3.2 分片2配置

1.3.2.1 10.0.0.192:3308 <-> 10.0.0.191:3308 互为主从配置

1、在db01和db02上创建复制用户

  1. mysql  -S /data/mysql/instance-3308/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by 'abc123..';"
  2. mysql  -S /data/mysql/instance-3308/mysql.sock -e "grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"

2、在db02上执行，此时db02为从库加入主库db01

  1. mysql  -S /data/mysql/instance-3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.191', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='abc123..';"
  2. mysql  -S /data/mysql/instance-3308/mysql.sock -e "start slave;"
  3. mysql  -S /data/mysql/instance-3308/mysql.sock -e "show slave status\G"

3、在db01上执行，此时db01为从库加入主库db02

  1. mysql  -S /data/mysql/instance-3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.192', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='abc123..';"
  2. mysql  -S /data/mysql/instance-3308/mysql.sock -e "start slave;"
  3. mysql  -S /data/mysql/instance-3308/mysql.sock -e "show slave status\G"

1.3.2.2 10.0.0.192:3310 -> 10.0.0.192:3308

将10.0.0.192:3310实例配置为10.0.0.192:3308实例的从库
db02上执行，加入主库10.0.0.192:3308

  1. mysql  -S /data/mysql/instance-3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.192', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='abc123..';"
  2. mysql  -S /data/mysql/instance-3310/mysql.sock -e "start slave;"
  3. mysql  -S /data/mysql/instance-3310/mysql.sock -e "show slave status\G"

1.3.2.3 10.0.0.191:3310 -> 10.0.0.191:3308

将10.0.0.191:3310实例配置为10.0.0.191:3308实例的从库
db01上执行，加入主库10.0.0.191:3308

  1. mysql  -S /data/mysql/instance-3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.191', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='abc123..';"
  2. mysql  -S /data/mysql/instance-3310/mysql.sock -e "start slave;"
  3. mysql  -S /data/mysql/instance-3310/mysql.sock -e "show slave status\G"

1.4 检查主从状态

在db01和db02上都执行

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

image.png
image.png

1.5 Mycat安装部署

1.5.1 安装

1、安装java环境
yum install -y java
2、下载
http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/
3、解压
tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
4、配置环境变量
vim /etc/profile
export PATH=/app/mycat/bin:$PATH
source /etc/profile
5、启动
mycat start
6、连接mycat
mysql -uroot -p123456 -h 127.0.0.1 -P8066

1.5.2 配置文件说明

1、logs目录
wrapper.log ——>mycat启动日志
mycat.log ——>mycat详细工作日志
2、conf目录
schema.xml —-主配置文件（读写分离、高可用、分布式策略定制、节点控制）
server.xml —-mycat软件本身相关的配置
rule.xml —-分片规则配置文件,记录分片规则列表、使用方法等
3、用户创建和数据导入

  1. #db01:
  2. mysql -S /data/mysql/instance-3307/mysql.sock  
  3. grant all on *.* to root@'10.0.0.%' identified by 'abc123..';
  4. source /root/world.sql
  5. exit
  6. mysql -S /data/mysql/instance-3308/mysql.sock  
  7. grant all on *.* to root@'10.0.0.%' identified by 'abc123..';
  8. source /root/world.sql

1.5.3 schema配置文件重要字段说明

1、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上分发。
2、writetype属性
负载均衡类型，目前的取值有2种：
1）writeType=”0”, 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为主，切换记录在配置文件中:dnindex.properties
2）writeType=“1”，所有写操作都随机的发送到配置的writeHost，但不推荐使用
3、switchtype属性

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

4、datahost其他配置

  • maxCon=”1000” ：最大的并发连接数
  • minCon=”10” ：mycat在启动之后，会在后端节点上自动开启的连接线程
  • tempReadHostAvailable=”1”：这个一主一从时（1个writehost，1个readhost时），可以开启这个参数，如果2个writehost，2个readhost时建议不加此参数
  • select user() 监测心跳

    2 读写分离、高可用架构和分片配置

    2.1 配置读写分离

    一主一从的读写分离，10.0.0.191:3307这个实例为主，10.0.0.191:3309这个实例为从
    1、定义配置文件
    ]# mv schema.xml schema.xml.bak
    ]# vim schema.xml
    1. <?xml version="1.0"?>
    2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
    3. <mycat:schema xmlns:mycat="http://io.mycat/">
    4. #mycat逻辑库定义
    5. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="cluster-01"> 
    6. </schema>
    7. #数据节点定义
    8.   <dataNode name="cluster-01" dataHost="host-01" database= "world" />
    9. #后端主机定义
    10.   <dataHost name="host-01" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">                  <heartbeat>select user()</heartbeat>  
    11.       <writeHost host="db1" url="10.0.0.191:3307" user="root" password="abc123.."> 
    12.            <readHost host="db2" url="10.0.0.191:3309" user="root" password="abc123.." /> 
    13.       </writeHost> 
    14.   </dataHost>  
    15. </mycat:schema>
    2、读写分离测试
    1. 重启mycat
    2. [root@db-01 ~]# mycat restart
    3. 链接mycat
    4. [root@db-01 ~]# mysql -uroot -p123456 -h10.0.0.191 -P8066
    5. mysql> select @@server_id;
    6. +-------------+
    7. | @@server_id |
    8. +-------------+
    9. |           9 |
    10. +-------------+
    11. 1 row in set (0.00 sec)
    12. mysql> begin;select @@server_id;commit;
    13. Query OK, 0 rows affected (0.00 sec)
    14. +-------------+
    15. | @@server_id |
    16. +-------------+
    17. |           7 |
    18. +-------------+
    19. 1 row in set (0.00 sec)
    20. Query OK, 0 rows affected (0.00 sec)

    2.2 Mycat高可用+读写分离

1、定义配置文件

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
  3. <mycat:schema xmlns:mycat="http://mycat.org.cn/">
  4. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="cluster-01"> 
  5. </schema>  
  6.         <dataNode name="cluster-01" dataHost="host-01" database= "world" />         
  7.         <dataHost name="host-01" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">    
  8.                 <heartbeat>select user()</heartbeat>  
  9.         <writeHost host="db01" url="10.0.0.191:3307" user="root" password="abc123.."> 
  10.                         <readHost host="db02" url="10.0.0.191:3309" user="root" password="abc123.." /> 
  11.         </writeHost>
  12.         <writeHost host="db03" url="10.0.0.192:3307" user="root" password="abc123.."> 
  13.                         <readHost host="db04" url="10.0.0.192:3309" user="root" password="abc123.." /> 
  14.         </writeHost> 
  15.         </dataHost>  
  16. </mycat:schema>

2、配置段说明
image.png
第一个 writeHost：10.0.0.191:3307 真正的写节点负责写操作
第二个 writeHost：10.0.0.192:3307 stanby写节点，负责读，当10.0.0.191:3307宕掉,会切换为真正的写节点
3、测试读写分离

  1. 读会落到191:3309、192:3307、192:3309这三个节点上
  2. mysql> select @@server_id;
  3. +-------------+
  4. | @@server_id |
  5. +-------------+
  6. |           9 |
  7. +-------------+
  8. 1 row in set (0.00 sec)
  10. mysql> select @@server_id;
  11. +-------------+
  12. | @@server_id |
  13. +-------------+
  14. |          19 |
  15. +-------------+
  16. 1 row in set (0.00 sec)
  18. mysql> select @@server_id;
  19. +-------------+
  20. | @@server_id |
  21. +-------------+
  22. |          17 |
  23. +-------------+
  24. 1 row in set (0.01 sec)
  26. 写只会落到191:3307这个实例上
  27. mysql> begin;select @@server_id; commit;
  28. Query OK, 0 rows affected (0.01 sec)
  30. +-------------+
  31. | @@server_id |
  32. +-------------+
  33. |           7 |
  34. +-------------+
  35. 1 row in set (0.00 sec)
  37. Query OK, 0 rows affected (0.00 sec)
  39. mysql> begin;select @@server_id; commit;
  40. Query OK, 0 rows affected (0.00 sec)
  42. +-------------+
  43. | @@server_id |
  44. +-------------+
  45. |           7 |
  46. +-------------+
  47. 1 row in set (0.01 sec)
  49. Query OK, 0 rows affected (0.00 sec)

4、测试高可用
模拟故障，将191:3307服务停止，测试主库会切换到192:3307这个实例，写操作也只会落到192:3309这个实例
db01~#] systemctl stop mysqld-3307

  1. 读操作只会落到192:3309这个实例
  2. mysql> select @@server_id;
  3. +-------------+
  4. | @@server_id |
  5. +-------------+
  6. |          19 |
  7. +-------------+
  8. 1 row in set (0.00 sec)
  10. mysql> select @@server_id;
  11. +-------------+
  12. | @@server_id |
  13. +-------------+
  14. |          19 |
  15. +-------------+
  16. 1 row in set (0.00 sec)
  17. 写操作只会落到192:3307这个实例
  18. mysql> begin;select @@server_id; commit;
  19. Query OK, 0 rows affected (0.00 sec)
  21. +-------------+
  22. | @@server_id |
  23. +-------------+
  24. |          17 |
  25. +-------------+
  26. 1 row in set (0.00 sec)
  28. Query OK, 0 rows affected (0.00 sec)
  30. mysql> begin;select @@server_id; commit;
  31. Query OK, 0 rows affected (0.00 sec)
  33. +-------------+
  34. | @@server_id |
  35. +-------------+
  36. |          17 |
  37. +-------------+
  38. 1 row in set (0.00 sec)
  40. Query OK, 0 rows affected (0.01 sec)

2.3 锤子分片

2.3.1 定义

将数据库当中不同的表存储到不同的数据库实例当中，以达到将写压力分到不同节点的目的，这种拆分方式就叫锤子拆分

2.3.2 功能配置演示

1、配置文件定义

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  4. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="slice-01">
  5.         <table name="user" dataNode="slice-01"/>
  6.         <table name="order_t" dataNode="slice-02"/>
  7. </schema>
  8.     <dataNode name="slice-01" dataHost="host-01" database= "taobao" />
  9.     <dataNode name="slice-02" dataHost="host-02" database= "taobao" />
  10.     <dataHost name="host-01" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
  11.         <heartbeat>select user()</heartbeat>
  12.     <writeHost host="db1" url="10.0.0.191:3307" user="root" password="abc123..">
  13.             <readHost host="db2" url="10.0.0.191:3309" user="root" password="abc123.." />
  14.     </writeHost>
  15.     <writeHost host="db3" url="10.0.0.192:3307" user="root" password="abc123..">
  16.             <readHost host="db4" url="10.0.0.192:3309" user="root" password="abc123.." />
  17.     </writeHost>
  18.     </dataHost>
  19.     <dataHost name="host-02" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
  20.         <heartbeat>select user()</heartbeat>
  21.     <writeHost host="db1" url="10.0.0.191:3308" user="root" password="abc123..">
  22.             <readHost host="db2" url="10.0.0.191:3310" user="root" password="abc123.." />
  23.     </writeHost>
  24.     <writeHost host="db3" url="10.0.0.192:3308" user="root" password="abc123..">
  25.             <readHost host="db4" url="10.0.0.192:3310" user="root" password="abc123.." />
  26.     </writeHost>
  27.     </dataHost>
  28. </mycat:schema>

2、在db01上创建测试库和表
在两个分片上分别创建相同的taobao库，并在分片1上创建user表，在分片2上创建order_t表

  1. mysql -S /data/mysql/instance-3307/mysql.sock -e "create database taobao charset utf8;"
  2. mysql -S /data/mysql/instance-3308/mysql.sock -e "create database taobao charset utf8;"
  3. mysql -S /data/mysql/instance-3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";
  4. mysql -S /data/mysql/instance-3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"

3、功能测试
当连接mycat后，刚在分片1和分片2创建的表，此时表都在TESTDB这个逻辑库下面

  1. [root@db-01 ~]# mysql -uroot -p123456 -h 10.0.0.191 -P 8066
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 1
  5. Server version: 5.6.29-mycat-1.6.7.6-release-20220221174943 MyCat Server (OpenCloudDB)
  7. Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> show databases;
  16. +----------+
  17. | DATABASE |
  18. +----------+
  19. | TESTDB   |
  20. +----------+
  21. 1 row in set (0.01 sec)
  23. mysql> use TESTDB 
  24. Reading table information for completion of table and column names
  25. You can turn off this feature to get a quicker startup with -A
  27. Database changed
  29. mysql> show tables;
  30. +------------------+
  31. | Tables_in_taobao |
  32. +------------------+
  33. | order_t          |
  34. | user             |
  35. +------------------+
  36. 2 rows in set (0.00 sec)
  38. mysql> insert into user(id,name) values(1,'a'),(2,'b');
  39. Query OK, 2 rows affected (0.02 sec)
  40. Records: 2  Duplicates: 0  Warnings: 0
  42. mysql> commit;
  43. Query OK, 0 rows affected (0.01 sec)
  45. mysql> insert into order_t(id,name) values(3,'c'),(4,'d');
  46. Query OK, 2 rows affected (0.00 sec)
  47. Records: 2  Duplicates: 0  Warnings: 0
  49. mysql> commit;
  50. Query OK, 0 rows affected (0.00 sec)
  52. [root@db-01 ~]# mysql -S /data/mysql/instance-3307/mysql.sock -e "use taobao;show tables;" 
  53. +------------------+
  54. | Tables_in_taobao |
  55. +------------------+
  56. | user             |
  57. +------------------+
  58. [root@db-01 ~]# mysql -S /data/mysql/instance-3308/mysql.sock -e "use taobao;show tables;" 
  59. +------------------+
  60. | Tables_in_taobao |
  61. +------------------+
  62. | order_t          |
  63. +------------------+

2.4 水平分片

在锤子拆分的情况下，虽然将表拆分到了不同的数据库实例，达到了缓解单节点写的压力，但是还是会存在热表数据的情况，即对某一张表的写操作较为频繁，这一张表的写操作可能占了整个写操作的80%甚至更高，因此水平分片解决了这种问题，即水平分片就是将一张大表进行拆分成多张小表
水平拆分又分多种拆分策略：

  • 范围拆分（range）
  • 取模
  • 枚举
  • hash
  • 时间
  • 等

    2.4.1 范围拆分

    1、配置文件
    1. <?xml version="1.0"?>
    2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    3. <mycat:schema xmlns:mycat="http://io.mycat/">
    4. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="slice-01">
    5.   <table name="t3" dataNode="slice-01,slice-02" rule="auto-sharding-long" />
    6. </schema>
    7.   <dataNode name="slice-01" dataHost="host-01" database= "taobao" />
    8.   <dataNode name="slice-02" dataHost="host-02" database= "taobao" />
    9.   <dataHost name="host-01" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
    10.       <heartbeat>select user()</heartbeat>
    11.   <writeHost host="db1" url="10.0.0.191:3307" user="root" password="abc123..">
    12.           <readHost host="db2" url="10.0.0.191:3309" user="root" password="abc123.." />
    13.   </writeHost>
    14.   <writeHost host="db3" url="10.0.0.192:3307" user="root" password="abc123..">
    15.           <readHost host="db4" url="10.0.0.192:3309" user="root" password="abc123.." />
    16.   </writeHost>
    17.   </dataHost>
    18.   <dataHost name="host-02" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
    19.       <heartbeat>select user()</heartbeat>
    20.   <writeHost host="db1" url="10.0.0.191:3308" user="root" password="abc123..">
    21.           <readHost host="db2" url="10.0.0.191:3310" user="root" password="abc123.." />
    22.   </writeHost>
    23.   <writeHost host="db3" url="10.0.0.192:3308" user="root" password="abc123..">
    24.           <readHost host="db4" url="10.0.0.192:3310" user="root" password="abc123.." />
    25.   </writeHost>
    26.   </dataHost>
    27. </mycat:schema>
    2、编辑rule.xml
    找到定义范围拆分的规则auto-sharding-long
    image.png
    3、根据auto-sharding-long规则找到对应的拆分算法
    image.png
    4、修改autopartition-long文件，将拆分范围写入文件
    vim autopartition-long.txt
    此处只演示20行数据，1-10行数据存储在分片1上面，11-20行数据存储在分片2上面
    1. 1-10=0
    2. 11-20=1
    5、创建测试表
    1. mysql -S /data/mysql/instance-3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
    2. mysql -S /data/mysql/instance-3308/mysql.sock  -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
    6、连接mycat插入数据进行测试
    重启mycat
    mycat restart
    mysql -uroot -p123456 -h 10.0.0.191 -P 8066
    1. insert into t3(id,name) values(1,'a');
    2. insert into t3(id,name) values(2,'b');
    3. insert into t3(id,name) values(3,'c');
    4. insert into t3(id,name) values(4,'d');
    5. insert into t3(id,name) values(11,'aa');
    6. insert into t3(id,name) values(12,'bb');
    7. insert into t3(id,name) values(13,'cc');
    8. insert into t3(id,name) values(14,'dd');
    7、在两个分片上查看数据
    可以看到数据已经分布到了两个分片上 ```sql

[root@db-01 ~]# mysql -S /data/mysql/instance-3307/mysql.sock -e “select from taobao.t3;”
+——+———+ | id | name | +——+———+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +——+———+ [root@db-01 ~]# mysql -S /data/mysql/instance-3308/mysql.sock -e “select from taobao.t3;” +——+———+ | id | name | +——+———+ | 11 | aa | | 12 | bb | | 13 | cc | | 14 | dd | +——+———+

  1. <a name="i1Usi"></a>
  2. ### 2.4.2 取模拆分
  3. **1、修改配置文件**
  4. ```sql
  5. <?xml version="1.0"?>
  6. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  7. <mycat:schema xmlns:mycat="http://io.mycat/">
  8. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="slice-01">
  9.         <table name="t3" dataNode="slice-01,slice-02" rule="auto-sharding-long" />
  10.         <table name="t4" dataNode="slice-01,slice-02" rule="mod-long" />
  11. </schema>
  12.     <dataNode name="slice-01" dataHost="host-01" database= "taobao" />
  13.     <dataNode name="slice-02" dataHost="host-02" database= "taobao" />
  14.     <dataHost name="host-01" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
  15.         <heartbeat>select user()</heartbeat>
  16.     <writeHost host="db1" url="10.0.0.191:3307" user="root" password="abc123..">
  17.             <readHost host="db2" url="10.0.0.191:3309" user="root" password="abc123.." />
  18.     </writeHost>
  19.     <writeHost host="db3" url="10.0.0.192:3307" user="root" password="abc123..">
  20.             <readHost host="db4" url="10.0.0.192:3309" user="root" password="abc123.." />
  21.     </writeHost>
  22.     </dataHost>
  23.     <dataHost name="host-02" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
  24.         <heartbeat>select user()</heartbeat>
  25.     <writeHost host="db1" url="10.0.0.191:3308" user="root" password="abc123..">
  26.             <readHost host="db2" url="10.0.0.191:3310" user="root" password="abc123.." />
  27.     </writeHost>
  28.     <writeHost host="db3" url="10.0.0.192:3308" user="root" password="abc123..">
  29.             <readHost host="db4" url="10.0.0.192:3310" user="root" password="abc123.." />
  30.     </writeHost>
  31.     </dataHost>
  32. </mycat:schema>

2、编辑rule.xml文件找到取模规则对应的函数
image.png
3、修改mod_long函数取模参数
因为只有两个分片，所以这改为了2
image.png
4、创建测试表

  1. mysql -S /data/mysql/instance-3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
  2. mysql -S /data/mysql/instance-3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

5、重启mycat并插入测试数据
重启mycat
mycat restart
连接mycat
mysql -uroot -p123456 -h10.0.0.191 -P8066

  1. use TESTDB
  2. insert into t4(id,name) values(1,'a');
  3. insert into t4(id,name) values(2,'b');
  4. insert into t4(id,name) values(3,'c');
  5. insert into t4(id,name) values(4,'d');

6、登录后端节点查询数据

  1. [root@db-01 ~]# mysql -S /data/mysql/instance-3307/mysql.sock -e "select * from taobao.t4;"
  2. +----+------+
  3. | id | name |
  4. +----+------+
  5. |  2 | b    |
  6. |  4 | d    |
  7. +----+------+
  8. [root@db-01 ~]# mysql -S /data/mysql/instance-3308/mysql.sock -e "select * from taobao.t4;" 
  9. +----+------+
  10. | id | name |
  11. +----+------+
  12. |  1 | a    |
  13. |  3 | c    |
  14. +----+------+

2.4.3 枚举拆分

1、配置文件

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  4. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="slice-01">
  5.         <table name="t3" dataNode="slice-01,slice-02" rule="auto-sharding-long" />
  6.         <table name="t4" dataNode="slice-01,slice-02" rule="mod-long" />
  7.         <table name="t4" dataNode="slice-01,slice-02" rule="sharding-by-intfile" />
  8. </schema>
  9.     <dataNode name="slice-01" dataHost="host-01" database= "taobao" />
  10.     <dataNode name="slice-02" dataHost="host-02" database= "taobao" />
  11.     <dataHost name="host-01" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
  12.         <heartbeat>select user()</heartbeat>
  13.     <writeHost host="db1" url="10.0.0.191:3307" user="root" password="abc123..">
  14.             <readHost host="db2" url="10.0.0.191:3309" user="root" password="abc123.." />
  15.     </writeHost>
  16.     <writeHost host="db3" url="10.0.0.192:3307" user="root" password="abc123..">
  17.             <readHost host="db4" url="10.0.0.192:3309" user="root" password="abc123.." />
  18.     </writeHost>
  19.     </dataHost>
  20.     <dataHost name="host-02" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
  21.         <heartbeat>select user()</heartbeat>
  22.     <writeHost host="db1" url="10.0.0.191:3308" user="root" password="abc123..">
  23.             <readHost host="db2" url="10.0.0.191:3310" user="root" password="abc123.." />
  24.     </writeHost>
  25.     <writeHost host="db3" url="10.0.0.192:3308" user="root" password="abc123..">
  26.             <readHost host="db4" url="10.0.0.192:3310" user="root" password="abc123.." />
  27.     </writeHost>
  28.     </dataHost>
  29. </mycat:schema>

2、编辑rule.xml文件找到枚举分片规则
修改列值为name，columns 标识将要分片的表字段
image.png
3、查看hash-int函数
需要编辑mapFile文件，partition-hash-int.txt，mapFile标识配置文件名称
1 默认枚举分片只能是数值型，修改此参数便是字符也可以
0 默认节点
image.png
4、编辑partition-hash-int.txt文件
vim partition-hash-int.txt

  1. bj=0 
  2. sh=1
  3. DEFAULT_NODE=1

5、创建测试表

  1. mysql -S /data/mysql/instance-3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
  2. mysql -S /data/mysql/instance-3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

6、重启mycat并插入测试数据
重启mycat
mycat restart
连接mycat
mysql -uroot -p123456 -h10.0.0.191 -P8066

  1. use TESTDB
  2. insert into t5(id,name) values(1,'bj');
  3. insert into t5(id,name) values(2,'sh');
  4. insert into t5(id,name) values(3,'bj');
  5. insert into t5(id,name) values(4,'sh');
  6. insert into t5(id,name) values(5,'tj');

7、登录后端节点查询数据

  1. [root@db-01 ~]# mysql -S /data/mysql/instance-3307/mysql.sock -e "select * from taobao.t5;"
  2. +----+------+
  3. | id | name |
  4. +----+------+
  5. |  1 | bj   |
  6. |  3 | bj   |
  7. |  5 | tj   |
  8. +----+------+
  9. [root@db-01 ~]# mysql -S /data/mysql/instance-3308/mysql.sock -e "select * from taobao.t5;" 
  10. +----+------+
  11. | id | name |
  12. +----+------+
  13. |  2 | sh   |
  14. |  4 | sh   |
  15. +----+------+