锐客网

  1. 首页 > 睿知 > it技术 > >

MyCAT实现MySQL数据库读写分离

mysql数据库IT知识

【MyCAT实现MySQL数据库读写分离】大鹏一日同风起，扶摇直上九万里。这篇文章主要讲述MyCAT实现MySQL数据库读写分离相关的知识，希望能为你提供帮助。
3.MyCAT实现mysql读写分离3.1架构图及环境说明

四台服务器
1主节点：
MariaDB-Master
CentOS 8.4
IP: 192.168.250.18/24
10.3.28-MariaDB

2从节点：
MariaDB-Slave
CentOS 8.4
IP: 192.168.250.28/24
10.3.28-MariaDB

3MyCAT服务器：
Mycat-Server
CentOS 8.4
IP: 192.168.250.8/24
Mycat-server-1.6.7.6

4Clinet客户端：
Client-CentOS79
CentOS 7.9
IP: 192.168.250.7/24
10.3.28-MariaDB用其client

3.2主从复制配置
3.2.1主节点
###MariaDB主节点 IP192.168.250.18
[root@CentOS84 ]#hostnamectl set-hostname MariaDB-Master
[root@CentOS84 ]#exit
# 退出后重新登录
[root@MariaDB-Master ]#hostname
MariaDB-Master
[root@MariaDB-Master ]#hostname -I
192.168.250.18
# 验证SELinux和防火墙已经关闭
[root@MariaDB-Master ]#getenforce
Disabled
[root@MariaDB-Master ]#systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
# 时间同步设置
[root@MariaDB-Master ]#systemctl enable --nowchronyd.service
Created symlink /etc/systemd/system/multi-user.target.wants/chronyd.service → /usr/lib/systemd/system/chronyd.service.
# 安装mariadb-server
[root@MariaDB-Master ]#dnf -y install mariadb-server
[root@MariaDB-Master ]#vim /etc/my.cnf.d/mariadb-server.cnf
# 配置文件增加下面两行
[mysqld]
server-id=18
log-bin

[root@MariaDB-Master ]#systemctl enable --now mariadb
[root@MariaDB-Master ]#systemctl status mariadb
# 验证3306端口监听
[root@MariaDB-Master ]#ss -tln
StateRecv-QSend-QLocal Address:PortPeer Address:PortProcess
LISTEN080*:3306*:*

[root@MariaDB-Master ]#mysql
Welcome to the MariaDB monitor.Commands end with ; or \\g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type help; or \\h for help. Type \\c to clear the current input statement.

MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name| File_size |
+--------------------+-----------+
| mariadb-bin.000001 |28259 |
| mariadb-bin.000002 |344 |#记录下这行的值，在从节点配置需要用
+--------------------+-----------+
2 rows in set (0.000 sec)

# 创建复制账户
MariaDB [(none)]> grant replication slave on *.* to repluser@192.168.250.% identified by shone;
Query OK, 0 rows affected (0.001 sec)
# 验证复制账户
MariaDB [(none)]> SELECT DISTINCT CONCAT(User: ,user,@,host,; ) AS query FROM mysql.user;
+-----------------------------------+
| query|
+-----------------------------------+
| User: root@127.0.0.1; |
| User: repluser@192.168.250.%; |
| User: root@::1; |
| User: root@localhost; |
| User: root@mariadb-master; |
+-----------------------------------+
5 rows in set (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database|
+--------------------+
| information_schema |
| mysql|
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

# 导入一个用于实验的 hellodb_innodb.sql 数据库
[root@MariaDB-Master ]#rz
rz waiting to receive.
Starting zmodem transfer.Press Ctrl+C to cancel.
Transferring hellodb_innodb.sql...
100%7 KB7 KB/sec00:00:010 Errors

[root@MariaDB-Master ]#ll
-rw-r--r--1 root root 7786 Sep1 18:14 hellodb_innodb.sql

#这个步骤再完成从节点配置后，在主节点导入hellodb_innodb.sql数据库，并在从节点验证复制情况
[root@MariaDB-Master ]#mysql < hellodb_innodb.sql
[root@MariaDB-Master ]#

# 创建用户并对mycat授权，用于mycat代理连接后端时用的账户和密& 码，必须和mycat的/apps/mycat/conf/schema.xml这个文件内的配置一致
[root@MariaDB-Master ]#mysql
Welcome to the MariaDB monitor.Commands end with ; or \\g.
Your MariaDB connection id is 13
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type help; or \\h for help. Type \\c to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database|
+--------------------+
| hellodb|
| information_schema |
| mysql|
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]>


MariaDB [(none)]> create database mycat;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> GRANT ALL ON *.* TO root@192.168.250.% IDENTIFIED BY host123456 ;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT DISTINCT CONCAT(User: ,user,@,host,; ) AS query FROM mysql.user;
+--------------

    推荐阅读


    上一篇：在linux中如果查看进程，不懂得点进来吧！另外附加了如何静态启动进程！

    下一篇：XtraBackup实现MySQL数据库全量+增量+Binlog恢复库