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

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

4 Clinet客户端:
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 --now chronyd.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
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 80 *: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 KB 7 KB/sec 00:00:01 0 Errors

[root@MariaDB-Master ]#ll
-rw-r--r-- 1 root root 7786 Sep 1 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;
+-----------------------------------+
| query |
+-----------------------------------+
| User: 'root'@'127.0.0.1'; |
| User: 'repluser'@'192.168.250.%'; |
| User: 'root'@'192.168.250.%'; |
| User: 'root'@'::1'; |
| User: 'root'@'localhost'; |
| User: 'root'@'mariadb-master'; |
+-----------------------------------+
6 rows in set (0.001 sec)

3.2.2 从节点

###   MariaDB从节点 IP192.168.250.28
[root@CentOS84 ]#hostnamectl set-hostname MariaDB-Slave
[root@CentOS84 ]#exit
# 退出后重新登录
[root@MariaDB-Slave ]#hostname
MariaDB-Slave
[root@MariaDB-Slave ]#hostname -I
192.168.250.28
# 验证SELinux和防火墙已经关闭
[root@MariaDB-Slave ]#getenforce
Disabled
[root@MariaDB-Slave ]#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-Slave ]#systemctl enable --now chronyd.service
Created symlink /etc/systemd/system/multi-user.target.wants/chronyd.service → /usr/lib/systemd/system/chronyd.service.
# 安装mariadb-server
[root@MariaDB-Slave ]#dnf -y install mariadb-server

[root@MariaDB-Slave ]#vim /etc/my.cnf.d/mariadb-server.cnf
# 增加下面一行
[mysqld]
server-id=28
# 启动并验证数据库状态
[root@MariaDB-Slave ]#systemctl enable --now mariadb
[root@MariaDB-Slave ]#systemctl status mariadb

# 进入数据库配置复制,并验证
[root@MariaDB-Slave ]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB 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)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.250.18',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='shone',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002',
-> MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.095 sec)

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

MariaDB [(none)]> show slave status\G
*************************<strong> 1. row </strong>*************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.250.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 545
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 758
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 545
Relay_Log_Space: 1069
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 18
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.001 sec)

MariaDB [(none)]>

# 此是切换到主节点上导入hellodb,在从节点查看验证被正常复制
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]>

# 创建用户并对mycat授权,用于mycat代理连接后端时用的账户和密&码,必须和mycat的/apps/mycat/conf/schema.xml这个文件内的配置一致
[root@MariaDB-Slave ]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.28-MariaDB 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)]> GRANT ALL ON *.* TO 'root'@'192.168.250.%' IDENTIFIED BY 'host123456' ;
Query OK, 0 rows affected (0.000 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;
+-----------------------------------+
| query |
+-----------------------------------+
| User: 'root'@'127.0.0.1'; |
| User: 'repluser'@'192.168.250.%'; |
| User: 'root'@'192.168.250.%'; |
| User: 'root'@'::1'; |
| User: 'root'@'localhost'; |
| User: 'root'@'mariadb-slave'; |
+-----------------------------------+
6 rows in set (0.002 sec)

#至此两台主从复制配置完成

3.3 MyCAT代理服务器配置

###   MyCat服务器 IP192.168.250.8
[root@CentOS84 ]#hostnamectl set-hostname Mycat-Server
[root@CentOS84 ]#exit
# 退出后重新登录
[root@Mycat-Server ]#hostname
Mycat-Server
[root@Mycat-Server ]#hostname -I
192.168.250.8
# 验证SELinux和防火墙已经关闭
[root@Mycat-Server ]#getenforce
Disabled
[root@Mycat-Server ]#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@Mycat-Server ]#systemctl enable --now chronyd.service
Created symlink /etc/systemd/system/multi-user.target.wants/chronyd.service → /usr/lib/systemd/system/chronyd.service.

# Mycat服务器配置了8CPU和16G内存,确保java正常高效运行
[root@Mycat-Server ]#lscpu
CPU(s): 8
[root@Mycat-Server ]#free -h
total used free shared buff/cache available
Mem: 15Gi 297Mi 14Gi 17Mi 759Mi 14Gi

# 安装java,确认安装成功并查看版本
[root@Mycat-Server ]#dnf -y install java
[root@Mycat-Server ]#java -version
openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)

# 下载Mycat-server-1.6.7.6 安装包,创建/apps/目录,并解压
[root@Mycat-Server ]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

[root@Mycat-Server ]#ll
-rw-r--r-- 1 root root 26030477 Mar 3 2021 Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

[root@Mycat-Server ]#mkdir /apps
[root@Mycat-Server ]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/

[root@Mycat-Server ]#ll /apps/mycat/
total 12
drwxr-xr-x 2 root root 190 Feb 24 11:17 bin
drwxrwxrwx 2 root root 6 Apr 15 2020 catlet
drwxrwxrwx 4 root root 4096 Feb 24 11:17 conf
drwxr-xr-x 2 root root 4096 Feb 24 11:17 lib
drwxrwxrwx 2 root root 6 Dec 23 2020 logs
-rwxrwxrwx 1 root root 227 Mar 3 2021 version.txt

[root@Mycat-Server ]#tree /apps/
/apps/
└── mycat
├── bin
│ ├── dataMigrate.sh
│ ├── init_zk_data.sh
│ ├── mycat
│ ├── rehash.sh
│ ├── startup_nowrap.sh
│ ├── wrapper-linux-ppc-64
│ ├── wrapper-linux-x86-32
│ └── wrapper-linux-x86-64
├── catlet
├── conf
│ ├── autopartition-long.txt
│ ├── auto-sharding-long.txt
│ ├── auto-sharding-rang-mod.txt
│ ├── cacheservice.properties
│ ├── dbseq.sql
│ ├── dbseq - utf8mb4.sql
│ ├── dnindex.properties
│ ├── ehcache.xml
│ ├── index_to_charset.properties
│ ├── log4j2.xml
│ ├── migrateTables.properties
│ ├── myid.properties
│ ├── partition-hash-int.txt
│ ├── partition-range-mod.txt
│ ├── rule.xml
│ ├── schema.xml
│ ├── schema.xml.bak
│ ├── sequence_conf.properties
│ ├── sequence_db_conf.properties
│ ├── sequence_distributed_conf.properties
│ ├── sequence_http_conf.properties
│ ├── sequence_time_conf.properties
│ ├── server.xml
│ ├── server.xml.bak
│ ├── sharding-by-enum.txt
│ ├── wrapper.conf
│ ├── zkconf
│ │ ├── autopartition-long.txt
│ │ ├── auto-sharding-long.txt
│ │ ├── auto-sharding-rang-mod.txt
│ │ ├── cacheservice.properties
│ │ ├── ehcache.xml
│ │ ├── index_to_charset.properties
│ │ ├── partition-hash-int.txt
│ │ ├── partition-range-mod.txt
│ │ ├── rule.xml
│ │ ├── schema.xml
│ │ ├── sequence_conf.properties
│ │ ├── sequence_db_conf.properties
│ │ ├── sequence_distributed_conf-mycat_fz_01.properties
│ │ ├── sequence_distributed_conf.properties
│ │ ├── sequence_time_conf-mycat_fz_01.properties
│ │ ├── sequence_time_conf.properties
│ │ ├── server-mycat_fz_01.xml
│ │ ├── server.xml
│ │ └── sharding-by-enum.txt
│ └── zkdownload
│ └── auto-sharding-long.txt
├── lib
│ ├── annotations-13.0.jar
│ ├── asm-4.0.jar
│ ├── checker-qual-2.10.0.jar
│ ├── commons-cli-1.2.jar
│ ├── commons-collections-3.2.1.jar
│ ├── commons-csv-1.8.jar
│ ├── commons-lang-2.6.jar
│ ├── curator-client-4.0.1.jar
│ ├── curator-framework-4.0.1.jar
│ ├── curator-recipes-4.0.1.jar
│ ├── disruptor-3.3.4.jar
│ ├── dom4j-1.6.1.jar
│ ├── druid-1.0.26.jar
│ ├── ehcache-core-2.6.11.jar
│ ├── error_prone_annotations-2.3.4.jar
│ ├── failureaccess-1.0.1.jar
│ ├── fastjson-1.2.68.jar
│ ├── guava-28.2-jre.jar
│ ├── hamcrest-core-1.3.jar
│ ├── hamcrest-library-1.3.jar
│ ├── j2objc-annotations-1.3.jar
│ ├── joda-time-2.9.3.jar
│ ├── jsr305-2.0.3.jar
│ ├── kotlin-stdlib-1.3.50.jar
│ ├── kotlin-stdlib-common-1.3.50.jar
│ ├── kryo-2.10.jar
│ ├── leveldb-0.7.jar
│ ├── leveldb-api-0.7.jar
│ ├── libwrapper-linux-ppc-64.so
│ ├── libwrapper-linux-x86-32.so
│ ├── libwrapper-linux-x86-64.so
│ ├── listenablefuture-9999.0-empty-to-avoid-conflict-with-guava.jar
│ ├── log4j-1.2.17.jar
│ ├── log4j-1.2-api-2.13.0.jar
│ ├── log4j-api-2.13.0.jar
│ ├── log4j-core-2.13.0.jar
│ ├── log4j-slf4j-impl-2.13.0.jar
│ ├── mapdb-1.0.7.jar
│ ├── minlog-1.2.jar
│ ├── mongo-java-driver-3.11.0.jar
│ ├── Mycat-server-1.6.7.6-release.jar
│ ├── mysql-binlog-connector-java-0.16.1.jar
│ ├── mysql-connector-java-5.1.35.jar
│ ├── netty-3.10.5.Final.jar
│ ├── netty-buffer-4.1.9.Final.jar
│ ├── netty-common-4.1.9.Final.jar
│ ├── objenesis-1.2.jar
│ ├── okhttp-4.2.2.jar
│ ├── okio-2.2.2.jar
│ ├── reflectasm-1.03.jar
│ ├── sequoiadb-driver-1.12.jar
│ ├── slf4j-api-1.6.1.jar
│ ├── univocity-parsers-2.8.4.jar
│ ├── velocity-1.7.jar
│ ├── wrapper.jar
│ └── zookeeper-3.5.3-beta.jar
├── logs
└── version.txt

8 directories, 111 files
[root@Mycat-Server ]#tree /apps/ -d
/apps/
└── mycat
├── bin
├── catlet
├── conf
│ ├── zkconf
│ └── zkdownload
├── lib
└── logs

8 directories
# 查看mycat运行程序路径并统计
[root@Mycat-Server ]#ll /apps/mycat/bin/
total 384
-rwxr-xr-x 1 root root 3658 Apr 15 2020 dataMigrate.sh
-rwxr-xr-x 1 root root 1272 Apr 15 2020 init_zk_data.sh
-rwxr-xr-x 1 root root 15701 Mar 3 2021 mycat
-rwxr-xr-x 1 root root 2986 Sep 29 2020 rehash.sh
-rwxr-xr-x 1 root root 2526 Sep 29 2020 startup_nowrap.sh
-rwxr-xr-x 1 root root 140198 Mar 3 2021 wrapper-linux-ppc-64
-rwxr-xr-x 1 root root 99401 Mar 3 2021 wrapper-linux-x86-32
-rwxr-xr-x 1 root root 111027 Mar 3 2021 wrapper-linux-x86-64
[root@Mycat-Server ]#wc -l /apps/mycat/bin/mycat
574 /apps/mycat/bin/mycat

# 配置环境变量
[root@Mycat-Server ]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@Mycat-Server ]#source /etc/profile.d/mycat.sh
# 启动mycat
[root@Mycat-Server ]#mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@Mycat-Server ]#mycat start
Starting Mycat-server...
# 注: 此步根据机器的配置不通,启动速度又差异,需要耐心等一会儿,java程序内存太小,可能会导致无法启动

# 下面通过查看日志和端口,验证启动状况
[root@Mycat-Server ]#ll /apps/mycat/logs/
total 36
-rw-r--r-- 1 root root 26294 Feb 24 11:29 mycat.log
-rw-r--r-- 1 root root 6 Feb 24 11:29 mycat.pid
-rw-r--r-- 1 root root 488 Feb 24 11:29 wrapper.log
[root@Mycat-Server ]#tail -f /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/02/24 11:29:33 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/02/24 11:29:33 | Launching a JVM...
INFO | jvm 1 | 2022/02/24 11:29:34 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/02/24 11:29:34 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/02/24 11:29:34 |
INFO | jvm 1 | 2022/02/24 11:29:36 | MyCAT Server startup successfully. see logs in logs/mycat.log #查看日志,确定成功,可能需等一会儿才看到成功的提示

# MyCat 默认监听端口没修改前是8066,一般生产环境都会修改成3306.
[root@Mycat-Server ]#ss -tlnp
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:* users:(("java",pid=38219,fd=4))
LISTEN 0 128 0.0.0.0:111 0.0.0.0:* users:(("rpcbind",pid=915,fd=4),("systemd",pid=1,fd=130))
LISTEN 0 32 192.168.122.1:53 0.0.0.0:* users:(("dnsmasq",pid=1783,fd=6))
LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=1005,fd=4))
LISTEN 0 5 127.0.0.1:631 0.0.0.0:* users:(("cupsd",pid=1006,fd=9))
LISTEN 0 50 *:1984 *:* users:(("java",pid=38219,fd=73))
LISTEN 0 50 *:19872 *:* users:(("java",pid=38219,fd=72))
LISTEN 0 2048 *:8066 *:* users:(("java",pid=38219,fd=115))
LISTEN 0 50 *:20520 *:* users:(("java",pid=38219,fd=74))
LISTEN 0 2048 *:9066 *:* users:(("java",pid=38219,fd=111))
LISTEN 0 128 [::]:111 [::]:* users:(("rpcbind",pid=915,fd=6),("systemd",pid=1,fd=132))
LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1005,fd=6))
LISTEN 0 5 [::1]:631 [::]:* users:(("cupsd",pid=1006,fd=8))
# 切换到 Clinet客户端 CentOS 7.9  IP: 192.168.250.7/24  上测试已经用默认配置启动的 MyCAT
[root@centos79 <sub>]# hostname
centos79
[root@centos79 </sub>]# hostnamectl set-hostname Client-CentOS79
[root@centos79 <sub>]# exit
[root@client-centos79 </sub>]# hostname
client-centos79
[root@client-centos79 <sub>]# hostname -I
192.168.250.7
# 验证SELinux和防火墙已经关闭
[root@client-centos79 </sub>]# getenforce
Disabled
[root@client-centos79 <sub>]# 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@client-centos79 </sub>]# yum install -y ntpdate
[root@client-centos79 <sub>]# cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
cp: overwrite ‘/etc/localtime’? y
[root@client-centos79 </sub>]# ntpdate us.pool.ntp.org

#用默认密&码123456来连接mycat
[root@centos79 ~]# mysql -uroot -p123456 -h 192.168.250.8 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)

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

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

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
# 经过默认配置测试正常后,开始修改MyCAT的配置文件,并定义读写分离

[root@Mycat-Server ]#cp /apps/mycat/conf/server.xml /apps/mycat/conf/server.xml.bak2
[root@Mycat-Server ]#ll /apps/mycat/conf/
total 132
...............
-rwxrwxrwx 1 root root 3302 Mar 3 2021 schema.xml
-rwxrwxrwx 1 root root 3080 Jun 13 2020 schema.xml.bak
-rwxrwxrwx 1 root root 6443 Mar 3 2021 server.xml
-rwxrwxrwx 1 root root 6392 Jun 13 2020 server.xml.bak
-rwxr-xr-x 1 root root 6443 Feb 24 12:10 server.xml.bak2
...............

# 修改/apps/mycat/conf/server.xml文件
[root@Mycat-Server ]#cat /apps/mycat/conf/server.xml
# 省略部分内容,默认保存不变
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
<property name="handleDistributedTransactions">0</property>
# 省略部分内容,默认保存不变
<user name="root" defaultAccount="true">
<property name="password">shone123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
# 省略部分内容,默认保存不变
[root@Mycat-Server ]#

# 修改/apps/mycat/conf/schema.xml文件 实现读写分离
[root@Mycat-Server ]#
[root@Mycat-Server ]#ll /apps/mycat/conf/schema.xml
-rwxrwxrwx 1 root root 3302 Mar 3 2021 /apps/mycat/conf/schema.xml
[root@Mycat-Server ]#cp /apps/mycat/conf/schema.xml /apps/mycat/conf/schema.xml.bak2
[root@Mycat-Server ]#cat /apps/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="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.250.18:3306" user="root"
password="host123456">
<readHost host="hostM2" url="192.168.250.28:3306" user="root" password="host123456" />
</writeHost>
</dataHost>
</mycat:schema>

# 注:此配置是hellodb全库代理模式,hellodb为后端数据库名;

# 启动mycat,并通过端口和日志查看确保正确启动
[root@Mycat-Server ]#mycat restart
[root@Mycat-Server ]#ss -tln
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 50 *:1984 *:*
LISTEN 0 50 *:25188 *:*
LISTEN 0 50 *:36264 *:*
LISTEN 0 2048 *:3306 *:*
LISTEN 0 2048 *:9066 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*

[root@Mycat-Server ]#tail -n 6 /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/02/24 18:45:28 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/02/24 18:45:28 | Launching a JVM...
INFO | jvm 1 | 2022/02/24 18:45:29 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/02/24 18:45:29 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/02/24 18:45:29 |
INFO | jvm 1 | 2022/02/24 18:45:30 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@Mycat-Server ]#

# 至此通过MyCat实现了MariaDB数据库的读写分离,下面我通过开启日志监测读写分离

3.4 在主从节点MariaDB内查看日志及客户端检测连接状况

从下面的日志信息,可以分析得出如下几个结论:

  • 写操作是在MariaDB-Master上,读操作是在Mmycatgirlmaid攻略ariaDB-Slavemysql密码忘记了怎么办
  • 仅停掉Maria数据库技术DB-Slave上的数据库,经过短暂的自我检测后,读操作会跳到MariaDB-Master服务器上
  • mysql停掉MariaDB-Ma数据库系统ster上的数据库,经过短暂时间后,读和写均报失败
### 主节点查看并配置日志及选项
[root@MariaDB-Master ]#mysql
# 查看日志是否开启
MariaDB [(none)]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.002 sec)
# 开启日志功能
MariaDB [(none)]> set global general_log=on;
Query OK, 0 rows affected (0.001 sec)
# 验证开启状态
MariaDB [(none)]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.002 sec)
# 查看日志文件保存位置
MariaDB [(none)]> show variables like 'general_log_file';
+------------------+--------------------+
| Variable_name | Value |
+------------------+--------------------+
| general_log_file | MariaDB-Master.log |
+------------------+--------------------+
1 row in set (0.001 sec)

[root@MariaDB-Master ]#ll /var/lib/mysql/
total 122960
-rw-rw---- 1 mysql mysql 16384 Feb 24 10:38 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Feb 24 10:38 aria_log_control
drwx------ 2 mysql mysql 300 Feb 24 19:36 hellodb
-rw-rw---- 1 mysql mysql 972 Feb 24 10:38 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Feb 24 19:36 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Feb 24 19:36 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Feb 24 10:38 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 Feb 24 10:38 ibtmp1
-rw-rw---- 1 mysql mysql 28259 Feb 24 10:38 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 10046 Feb 24 19:36 mariadb-bin.000002
-rw-rw---- 1 mysql mysql 42 Feb 24 10:38 mariadb-bin.index
-rw-rw---- 1 mysql mysql 3147 Feb 24 19:45 MariaDB-Master.log
-rw-rw---- 1 mysql mysql 0 Feb 24 10:38 multi-master.info
drwx------ 2 mysql mysql 20 Feb 24 18:03 mycat
drwx------ 2 mysql mysql 4096 Feb 24 10:38 mysql
srwxrwxrwx 1 mysql mysql 0 Feb 24 10:38 mysql.sock
-rw-rw---- 1 mysql mysql 16 Feb 24 10:38 mysql_upgrade_info
drwx------ 2 mysql mysql 20 Feb 24 10:38 performance_schema

[root@MariaDB-Master ]#ll /var/lib/mysql/MariaDB-Master.log
-rw-rw---- 1 mysql mysql 3190 Feb 24 19:45 /var/lib/mysql/MariaDB-Master.log
# 实时监测日志
[root@MariaDB-Master ]#tail -f /var/lib/mysql/MariaDB-Master.log
220224 19:44:50 23 Query select user() # 此为健康监测
220224 19:45:00 23 Query select user()
220224 19:45:10 23 Query select user()
220224 19:45:20 23 Query select user()
220224 19:45:30 23 Query select user()
30 Connect root@192.168.250.8 as anonymous on hellodb
31 Connect root@192.168.250.8 as anonymous on hellodb
32 Connect root@192.168.250.8 as anonymous on hellodb
220224 19:45:40 23 Query select user()
220224 19:45:50 31 Query select user()
220224 19:46:00 30 Query select user()
220224 19:46:10 32 Query select user()
220224 19:46:20 23 Query select user()
220224 19:46:30 31 Query select user()
220224 19:46:40 30 Query select user()
220224 19:46:50 32 Query select user()
220224 19:46:57 23 Query create table t1(id int)
220224 19:47:00 31 Query select user()
220224 19:47:08 30 Query SET names utf8;create table t2(id int)
220224 19:47:10 32 Query select user()
220224 19:47:20 23 Query select user()
220224 19:47:30 31 Query select user()
220224 19:47:40 30 Query select user()
220224 19:47:50 32 Query select user()
220224 19:48:00 23 Query select user()
220224 19:48:10 31 Query select user()
220224 19:48:14 30 Query update teachers set age=@@server_id where tid=4
220224 19:48:20 32 Query select user()
220224 19:48:30 23 Query select user()
220224 19:48:40 31 Query select user()
220224 19:48:50 30 Query select user()
220224 19:48:53 32 Query SET names utf8;update teachers set age=99 where tid=4
220224 19:49:00 23 Query select user()
220224 19:49:10 31 Query select user()
220224 19:49:20 30 Query select user()
220224 19:49:30 32 Query select user()
220224 19:49:40 23 Query select user()
220224 19:49:50 31 Query select user()
220224 19:50:00 30 Query select user()
220224 19:50:10 32 Query select user()
220224 19:50:20 23 Query select user()
220224 19:50:30 31 Query select user()
33 Connect root@192.168.250.8 as anonymous on hellodb
34 Connect root@192.168.250.8 as anonymous on hellodb
220224 19:50:40 30 Query select user()
220224 19:50:50 32 Query select user()
220224 19:51:00 23 Query select user()
220224 19:51:10 31 Query select user()
220224 19:51:20 33 Query select user()
220224 19:51:30 34 Query select user()
220224 19:51:34 35 Connect repluser@192.168.250.28 as anonymous on
35 Query SELECT UNIX_TIMESTAMP()
35 Query SHOW VARIABLES LIKE 'SERVER_ID'
35 Query SET @master_heartbeat_period= 30000001024
35 Query SET @master_binlog_checksum= @@global.binlog_checksum
35 Query SELECT @master_binlog_checksum
35 Query SET @mariadb_slave_capability=4
35 Query SELECT binlog_gtid_pos('mariadb-bin.000002',10638)
35 Binlog Dump Log: 'mariadb-bin.000002' Pos: 10638
220224 19:51:40 30 Query select user()
220224 19:51:50 32 Query select user()
220224 19:52:00 23 Query select user()
220224 19:52:10 31 Query select user()
220224 19:52:20 33 Query select user()
220224 19:52:30 34 Query select user()
220224 19:52:40 30 Query select user()
220224 19:52:50 32 Query select user()
220224 19:53:00 23 Query select user()
220224 19:53:10 31 Query select user()
220224 19:53:20 33 Query select user()
220224 19:53:30 34 Query select user()
220224 19:53:40 30 Query select user()
220224 19:53:50 32 Query select user()
220224 19:54:00 23 Query select user()
220224 19:54:10 31 Query select user()
220224 19:54:20 33 Query select user()
220224 19:54:30 34 Query select user()
220224 19:54:40 30 Query select user()
220224 19:54:50 32 Query select user()
220224 19:55:00 23 Query select user()
220224 19:55:10 31 Query select user()
220224 19:55:20 33 Query select user()
220224 19:55:30 34 Query select user()
36 Connect root@192.168.250.8 as anonymous on hellodb
220224 19:55:40 30 Query select user()
220224 19:55:50 32 Query select user()
220224 19:56:00 23 Query select user()
220224 19:56:10 31 Query select user()
220224 19:56:20 33 Query select user()
220224 19:56:30 34 Query select user()
220224 19:56:40 36 Query select user()
220224 19:56:50 30 Query select user()
220224 19:57:00 32 Query select user()
220224 19:57:10 23 Query select user()
220224 19:57:20 31 Query select user()
220224 19:57:30 33 Query select user()
220224 19:57:40 34 Query select user()
220224 19:57:50 36 Query select user()
220224 19:57:51 30 Query select @@server_id
220224 19:58:00 32 Query select user()
220224 19:58:10 23 Query select user()
220224 19:58:20 31 Query select user()
220224 19:58:30 33 Query select user()
220224 19:58:40 34 Query select user()
220224 19:58:50 36 Query select user()
220224 19:59:00 30 Query select user()
220224 19:59:10 32 Query select user()
220224 19:59:20 23 Query select user()
220224 19:59:30 31 Query select user()
220224 19:59:40 33 Query select user()
220224 19:59:50 34 Query select user()
220224 20:00:00 36 Query select user()
220224 20:00:10 30 Query select user()
220224 20:00:16 37 Connect repluser@192.168.250.28 as anonymous on
37 Query SELECT UNIX_TIMESTAMP()
37 Query SHOW VARIABLES LIKE 'SERVER_ID'
37 Query SET @master_heartbeat_period= 30000001024
37 Query SET @master_binlog_checksum= @@global.binlog_checksum
37 Query SELECT @master_binlog_checksum
37 Query SET @mariadb_slave_capability=4
37 Query SELECT binlog_gtid_pos('mariadb-bin.000002',10638)
37 Binlog Dump Log: 'mariadb-bin.000002' Pos: 10638
220224 20:00:20 32 Query select @@server_id
23 Query select user()
220224 20:00:30 31 Query select user()
38 Connect root@192.168.250.8 as anonymous on hellodb
220224 20:00:40 33 Query select user()
220224 20:00:50 34 Query select user()
220224 20:01:00 36 Query select user()
220224 20:01:10 30 Query select user()
220224 20:01:20 32 Query select user()
220224 20:01:30 23 Query select user()
220224 20:01:38 31 Query SET names utf8;update teachers set age=111 where tid=4
220224 20:01:40 38 Query select user()
220224 20:01:50 33 Query select user()
220224 20:02:00 34 Query select user()


#仅停掉主节点的数据库,从节点开启,但是短暂时间后,在客户端就报连接错误
[root@MariaDB-Master ]#systemctl stop mariadb
### 从节点查看并配置日志及选项
[root@MariaDB-Slave ]#mysql
# 查看日志是否开启
MariaDB [(none)]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.002 sec)
# 开启日志功能
MariaDB [(none)]> set global general_log=on;
Query OK, 0 rows affected (0.001 sec)
# 验证
MariaDB [(none)]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.002 sec)
# 查看日志文件名称和路径
MariaDB [(none)]> show variables like 'general_log_file';
+------------------+-------------------+
| Variable_name | Value |
+------------------+-------------------+
| general_log_file | MariaDB-Slave.log |
+------------------+-------------------+
1 row in set (0.002 sec)

[root@MariaDB-Slave ]#ll /var/lib/mysql/*.log
-rw-rw---- 1 mysql mysql 1111 Feb 24 19:44 /var/lib/mysql/MariaDB-Slave.log
-rw-rw---- 1 mysql mysql 24576 Feb 24 10:40 /var/lib/mysql/tc.log

[root@MariaDB-Slave ]#tail -f /var/lib/mysql/MariaDB-Slave.log
220224 19:43:10 14 Query select user()
220224 19:43:20 13 Query select user()
220224 19:43:30 17 Query select user()
220224 19:43:40 18 Query select user()
220224 19:43:50 19 Query select user()
220224 19:44:00 15 Query select user()
220224 19:44:10 16 Query select user()
220224 19:44:20 20 Query select user()
220224 19:44:30 14 Query select user()
220224 19:44:40 13 Query select user()
220224 19:44:49 17 Query show tables
18 Query SET names utf8;select @@server_id
220224 19:44:50 19 Query select @@server_id
15 Query select user()
220224 19:44:51 16 Query select @@hostname
220224 19:45:00 20 Query select user()
220224 19:45:10 14 Query select user()
220224 19:45:20 13 Query select user()
220224 19:45:30 17 Query select user()
21 Connect root@192.168.250.8 as anonymous on hellodb
220224 19:45:40 18 Query select user()
220224 19:45:50 19 Query select user()
220224 19:46:00 15 Query select user()
220224 19:46:10 16 Query select user()
220224 19:46:20 20 Query select user()
220224 19:46:27 14 Query select @@hostname
220224 19:46:30 13 Query select user()
220224 19:46:40 17 Query select user()
220224 19:46:50 21 Query select user()
220224 19:47:00 18 Query select user()
220224 19:47:08 10 Query create table t2(id int)
220224 19:47:10 19 Query select user()
220224 19:47:20 15 Query select user()
220224 19:47:23 16 Query select * from students
220224 19:47:28 20 Query select * from teachers
220224 19:47:30 14 Query select user()
220224 19:47:40 13 Query select user()
220224 19:47:50 17 Query select user()
220224 19:48:00 21 Query select user()
220224 19:48:10 18 Query select user()
220224 19:48:14 10 Query BEGIN
10 Query COMMIT /* implicit, from Xid_log_event */
220224 19:48:20 19 Query select user()
220224 19:48:30 15 Query select user()
220224 19:48:39 16 Query select * from teachers
220224 19:48:40 20 Query select user()
220224 19:48:50 14 Query select user()
220224 19:48:53 10 Query BEGIN
10 Query update teachers set age=99 where tid=4
10 Query COMMIT /* implicit, from Xid_log_event */
220224 19:49:00 13 Query select user()
220224 19:49:10 17 Query select user()
220224 19:49:20 21 Query select user()

220224 19:49:30 18 Query select user()
^Z
[2]+ Stopped tail -f /var/lib/mysql/MariaDB-Slave.log


# 仅停掉从节点的数据库,经过短暂等待,在客户端可以看到切换到主节点读
[root@MariaDB-Slave ]#systemctl stop mariadb
### 客户端检测的过程
# 准备测试客户端的环境
[root@centos79 <sub>]# hostname
centos79
[root@centos79 </sub>]# hostnamectl set-hostname Client-CentOS79
[root@centos79 <sub>]# exit
[root@client-centos79 </sub>]# hostname
client-centos79
[root@client-centos79 <sub>]# hostname -I
192.168.250.7
[root@client-centos79 </sub>]# getenforce
Disabled
[root@client-centos79 <sub>]# 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@client-centos79 </sub>]# yum install -y ntpdate
[root@client-centos79 <sub>]# cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
cp: overwrite ‘/etc/localtime’? y
[root@client-centos79 </sub>]# ntpdate us.pool.ntp.org
24 Feb 23:20:09 ntpdate[867]: adjust time server 23.131.160.7 offset 0.019633 sec

[root@centos79 <sub>]# yum -y install mysql

# 下面这端是在Mycat默认安装完成后,没修改两个配置文件启动后的,连接测试
[root@centos79 </sub>]# mysql -uroot -p123456 -h 192.168.250.8 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)

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

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

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)

# 下面这段是在Mycat配置文件修改好,完成读写分离后的连接测试
[root@centos79 ~]# mysql -uroot -pshone123456 -h 192.168.250.8
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)

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

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

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)

MySQL [(none)]> 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 [TESTDB]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
# 默认已经跳转到后端的从节点hellodb数据库
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
# 通过命令可以看到确实是连接在从节点IP192.168.250.28上
MySQL [TESTDB]> select @@hostname;
+---------------+
| @@hostname |
+---------------+
| MariaDB-Slave |
+---------------+
1 row in set (0.00 sec)

MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 28 |
+-------------+
1 row in set (0.01 sec)

MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.01 sec)

MySQL [TESTDB]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.01 sec)

# 下面测试插入一条记录
MySQL [TESTDB]> create table t1(id int);
Query OK, 0 rows affected (0.09 sec)

MySQL [TESTDB]> create table t2(id int);
Query OK, 0 rows affected (0.03 sec)

MySQL [TESTDB]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.01 sec)

# 通过获取服务器IP地址参数方式,修改数据库记录,可以看出写操作是在主节点IP192.168.250.18上完成的
MySQL [TESTDB]> update teachers set age=@@server_id where tid=4
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 18 | F |
+-----+---------------+-----+--------+
4 rows in set (0.01 sec)

MySQL [TESTDB]> update teachers set age=99 where tid=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# 转到从数据IP28上 [root@MariaDB-Slave ]#systemctl stop mariadb 停掉数据,模拟从服务器挂掉状况,读将自动转到主数据,但是主数据库挂掉,写是不能自动转到从上的。

MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 18 |
+-------------+
1 row in set (0.00 sec)

MySQL [(none)]>

# 启动了从节点上的数据库,短暂等待后跳转回28完成读
MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 28 |
+-------------+
1 row in set (0.00 sec)


# 在主数据库节点上停掉数据库服务 [root@MariaDB-Master ]#systemctl stop mariadb 读和写都不行了的,读还又短暂等待时间,写立刻就报错了
MySQL [(none)]> update teachers set age=122 where tid=4;
ERROR 1184 (HY000): java.net.ConnectException: Connection refused

MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 28 |
+-------------+
1 row in set (0.00 sec)

MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 28 |
+-------------+
1 row in set (0.00 sec)

MySQL [(none)]>
MySQL [(none)]> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
MySQL [(none)]> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
MySQL [(none)]>