MySQL8.0的分布式架构之MyCat搭建(详细)

最新推荐文章于 2024-07-30 20:43:11 发布

一个小小的MySQL DBA

最新推荐文章于 2024-07-30 20:43:11 发布

阅读量647

点赞数

分类专栏： mysql学习文章标签： mysql 分布式

本文链接：https://blog.csdn.net/qq_41405678/article/details/108936146

版权

mysql学习专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文详细介绍了如何在Centos7环境下搭建MySQL双主双从配置，采用增强半同步和GTID模式确保数据一致性。同时，通过MyCat实现读写分离，确保高可用性和负载均衡。在测试环节，验证了MyCat的读写分离功能正常工作。

摘要由CSDN通过智能技术生成

1.搭建背景

A、双主双从：主	10.0.0.51:3307   从	10.0.0.51:3309
		   主	10.0.0.52:3307   从	10.0.0.52:3309
	  互为主从	10.0.0.51:3307   	10.0.0.52:3307

B、Centos7、MySQL8.0.20、虚拟机VM、Xshell
mysql-8.0.20-linux-glibc2.12-x86_64

C、主从、增强半同步、MyCat
Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

2.准备基础环境

A.准备2套 Centos 7 ：db01 db02后面直接克隆db01
在DB01上，把安装包都放在/tools文件夹中（需要联网）

[root@db01 ~]# systemctl stop firewalld.service
[root@db01 ~]# systemctl disable firewalld.service
[root@db01 ~]# yum -y install net-tools
[root@db01 ~]# yum -y install java

B.准备2套MySQL(多实例)，分别是3307和3309。准备配置文件，在其加入增强半同步，并且将两个实例用systemctl管理。

[root@db01 ~]# mkdir /data/33{07,09}/data -p
[root@db01 ~]# yum -y install libaio-devel
[root@db01 ~]# rpm -qa |grep mariadb
[root@db01 ~]# yum -y remove mariadb-libs-5.5.65-1.el7.x86_64
[root@db01 ~]# useradd -s /sbin/nologin mysql
[root@db01 ~]# mkdir /data/app/mysql -p
[root@db01 tools]# tar -xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
[root@db01 tools]# mv mysql-8.0.20-linux-glibc2.12-x86_64 /* /data/app/mysql/
[root@db01 tools]# vi /etc/profile
export PATH=/data/app/mysql/bin:$PATH
[root@db01 tools]# cd /
[root@db01 /]# chown -R mysql.mysql /data/*
[root@db01 /]# source /etc/profile
[root@db01 /]# mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/data/app/mysql
[root@db01 /]# mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/data/app/mysql
[root@db01 /]# vi /data/3307/my.cnf 
[mysqld]
basedir=/data/app/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=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
#*************** rpl_semi_sync ***************
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 5000
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_no_slave = on
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_slave_enabled = on
#*************** group commit ***************
binlog_group_commit_sync_delay = 1
binlog_group_commit_sync_no_delay_count = 1000
#*************** gtid ***************
gtid_mode = on
enforce_gtid_consistency = on
log_slave_updates = 1
#*************** gtid ***************
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
master_info_repository = TABLE
relay_log_info_repository = TABLE
[root@db01 /]# vi /data/3307/my.cnf
[mysqld]
basedir=/data/app/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=29
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
#*************** rpl_semi_sync ***************
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 5000
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_no_slave = on
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_slave_enabled = on
#*************** group commit ***************
binlog_group_commit_sync_delay = 1
binlog_group_commit_sync_no_delay_count = 1000
#*************** gtid ***************
gtid_mode = on
enforce_gtid_consistency = on
log_slave_updates = 1
#*************** gtid ***************
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
master_info_repository = TABLE
relay_log_info_repository = TABLE
[root@db01 /]#  vi /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=/data/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
[root@db01 /]# vi /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=/data/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
[root@db01 /]# systemctl start mysqld3307
[root@db01 /]# systemctl start mysqld3309
[root@db01 /]# netstat -nultp
tcp6       0      0 :::3307                 :::*                    LISTEN      1810/mysqld                 
tcp6       0      0 :::3309                 :::*                    LISTEN      1911/mysqld

C.db02克隆db01，并且修改文件。修改的文件主要是UUID和service_id，若是不改，主从搭建就会出错。

[root@db02 ~]# rm -rf /data/330{7,9}/data/auto.cnf
[root@db02 ~]# vi /data/3307/my.cnf
server-id=17
[root@db02 ~]# vi /data/3309/my.cnf
server-id=19
[root@db02 ~]# systemctl start mysqld3307
[root@db02 ~]# systemctl start mysqld3309
[root@db02 ~]# netstat -nultp
tcp6       0      0 :::3307                 :::*                    LISTEN      1810/mysqld                 
tcp6       0      0 :::3309                 :::*                    LISTEN      1911/mysqld

3.配置双主双从

先在db01的3307上创建2个用户，一个用于主从，一个用于远程登陆。在db01上3309对3307进行复制.其次，在db02上对db01的3307进行复制，db02上的3309对其db02的3307进行复制。最后db01的3307对db02的3307进行复制，从而完成双主双从。

首先：

[root@db01 /]# mysql -S /data/3307/mysql.sock -e "create user repl@'10.0.0.%' identified with mysql_native_password by '123'; grant replication slave on *.* to repl@'10.0.0.%';"
[root@db01 /]# mysql -S /data/3307/mysql.sock -e "create user root@'10.0.0.%' identified with mysql_native_password by '123'; grant all on *.* to root@'10.0.0.%';"
[root@db01 /]# mysql -S /data/3307/mysql.sock -e "select host,user,plugin from mysql.user;"
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| 10.0.0.%  | repl             | mysql_native_password |
| 10.0.0.%  | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
[root@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';"
[root@db01 /]# mysql -S /data/3309/mysql.sock -e "start slave;"
[root@db01 /]# mysql -S /data/3309/mysql.sock -e "show slave status \G"|grep Running:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

其次：

[root@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';"
[root@db02 /]# mysql -S /data/3307/mysql.sock -e "start slave;"
[root@db02 /]# mysql -S /data/3307/mysql.sock -e "show slave status \G"|grep Running:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@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';"
[root@db02 /]# mysql -S /data/3309/mysql.sock -e "start slave;"
[root@db02 /]# mysql -S /data/3309/mysql.sock -e "show slave status \G"|grep Running:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

最后：

[root@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';"
[root@db02 /]# mysql -S /data/3307/mysql.sock -e "start slave;"
[root@db02 /]# mysql -S /data/3307/mysql.sock -e "show slave status \G"|grep Running:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4.简单搭建MyCat

Mycat最好在db02上搭建，这样以db01为主。从网上下载MyCat压缩包，解压后就直接移到/data/app下，配置好配置文件。

[root@db02 /]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@db02 /]# mv Mycat-server-1.6.7.4-release-20200105164103-linux /data/app/mycat
[root@db02 /]# vi /data/app/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
    <dataNode name="dn1" dataHost="localhost1" database= "world" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
            <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
     </writeHost>
     <writeHost host="db3" url="10.0.0.52:3307" user="root" password="123">
            <readHost host="db4" url="10.0.0.52:3309" user="root" password="123" />
     </writeHost>
    </dataHost>
</mycat:schema>

5.测试MyCat的读写分离

A.先在主库上创建配置文件中有的world库

[root@db02 /]# mysql -S /data/3307/mysql.sock -e "create database world;use world;create table t1(id int);insert into t1 (1),(2);"

B.配置环境变量，开启MyCat

[root@db02 /]# vi /ect/profile
export PATH=/data/app/mycat/bin:$PATH
[root@db02 /]# mycat start
Starting Mycat-server...

C.测试，17为db01:3307写节点，19为db01:3307读节点，27,29为db02:3307和db02:3309读节点

[root@db02 /]# mysql -uroot -p123456 -h10.0.0.52 -P8066 --default=mysql_native_password

mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.01 sec)

mysql> use TESTDB 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.01 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.04 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          19 |
+-------------+
1 row in set (0.11 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          29 |
+-------------+
1 row in set (0.02 sec)

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

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

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

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

Query OK, 0 rows affected (0.00 sec)

6.注意的细节

A.10.0.0.51和10.0.0.52为内网节点，前期10.0.0.51需要联网，可以从网上下包，也可以改VM为桥接，改IP连外网，再改回来。
B.数据库搭建可以查看我以前的数据库搭建
C.主从复制若出现问题，可以利用show slave status查看具体错误信息。
D.最容易出错的是MyCat的配置文件schema.xml，需要具体看看什么问题。
E.有什么问题可以给我留言。