Mycat中间件实现Mysql主从读写分离

环境规划：

10年积累的网站制作、成都网站设计经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站制作后付款的网站建设流程，更有万全免费网站建设让你可以放心的选择与我们合作。

IP地址	主机名	角色	备注
10.4.132.50	k8s01	mycat,master
10.4.132.42	k8s02	slave
10.4.132.66	k8s03	slave

Mycat下载地址：http://dl.mycat.io/1.6.7.3/20190828135747/Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz

MySQL下载地址： http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

Mycal管理集群端口：[root@k8s01 conf]# mysql -h 127.0.0.1 -u root -p123456 -P 9066

Mycat数据端口：[root@k8s01 conf]# mysql -h 127.0.0.1 -u root -p123456 -P 8066

1.下载安装mysql(1台master节点和2台slave节点)

[root@k8s01 soft]# wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

[root@k8s01 soft]# tar xvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

[root@k8s01 soft]# cd /usr/local/

[root@k8s01 local]# mv mysql-5.7.27-linux-glibc2.12-x86_64/ mysql-5.7.27

[root@k8s01 local]# chown -R root:root mysql-5.7.27/

[root@k8s01 local]# cd mysql-5.7.27/

[root@k8s01 mysql-5.7.27]# mkdir data

[root@k8s01 mysql-5.7.27]# useradd -r -M -s /bin/nologin mysql

[root@k8s01 mysql-5.7.27]# chown -R mysql:mysql data/

[root@k8s01 mysql-5.7.27]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql-5.7.27 --datadir=/usr/local/mysql-5.7.27/data

2019-11-02T04:24:41.908404Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2019-11-02T04:24:46.687678Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-11-02T04:24:47.428823Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-11-02T04:24:47.487404Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b42cef88-fd28-11e9-a5cc-000c29ee86d5.

2019-11-02T04:24:47.488204Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-11-02T04:24:47.612739Z 1 [Note] A temporary password is generated for root@localhost: 3m;5yQ_7T#jc--登陆密码

[root@k8s01 mysql-5.7.27]# cp -a support-files/mysql.server /etc/init.d/mysqld

[root@k8s01 mysql-5.7.27]# chkconfig --add mysqld

[root@k8s01 mysql-5.7.27]# chkconfig mysqld on

[root@k8s01 mysql-5.7.27]# vim /etc/init.d/mysqld

basedir=/usr/local/mysql-5.7.27

datadir=/usr/local/mysql-5.7.27/data

[root@k8s01 mysql-5.7.27]# vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql-5.7.27
datadir=/usr/local/mysql-5.7.27/data
socket=/tmp/mysql.sock
symbolic-links=0
server_id=10
binlog_format=ROW
max_binlog_size=2G
sync_binlog=1
binlog_cache_size=64M
log_bin=bin-log
log_bin_index=bin-index

[mysqld_safe]

log-error=/usr/local/mysql-5.7.27/data/mariadb.log

pid-file=/usr/local/mysql-5.7.27/data/mariadb.pid

[root@k8s01 mysql-5.7.27]# /etc/init.d/mysqld restart

ERROR! MySQL server PID file could not be found!

Starting MySQL.Logging to '/usr/local/mysql-5.7.27/data/mariadb.log'.

... SUCCESS!

[root@k8s01 mysql-5.7.27]# vim /etc/profile

export PATH=$PATH:/usr/local/mysql-5.7.27/bin

[root@k8s01 mysql-5.7.27]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.27

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respectiveowners.

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

mysql> set password=password('System135');

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@k8s01 mysql-5.7.27]# mysql -u root -pSystem135

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.27 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql>

2.master节点和slave节点做主从

master节点：

[root@k8s01 mysql-5.7.27]# mysql -u root -pSystem135
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.7.27-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.

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

mysql>

3.两个slave节点（两个slave节点都要连接到master节点）

[root@k8s02 ~]# mysql -u root -pSystem135
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.

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

mysql> change master to master_host='10.4.132.50',master_user='repl',master_password='123456',master_port=3306,master_log_file='bin-log.000002',master_log_pos=3093;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.4.132.50
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000002
          Read_Master_Log_Pos: 3093
               Relay_Log_File: k8s02-relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: bin-log.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

mysql>

4.验证主从数据是否同步

master节点：

mysql> system hostname
k8s01

mysql> create database wuhan charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use wuhan
Database changed
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql>

slave1节点：

mysql> system hostname
k8s02

mysql> use wuhan
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> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql>

slave2节点：

mysql> system hostname
k8s03
mysql> use wuhan
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> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql>

5.下载安装配置Mycat（master节点）

[root@k8s01 soft]# rpm -ivh jdk-8u221-linux-x64.rpm
warning: jdk-8u221-linux-x64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:jdk1.8-2000:1.8.0_221-fcs ################################# [100%]
Unpacking JAR files...
tools.jar...
plugin.jar...
javaws.jar...
deploy.jar...
rt.jar...
jsse.jar...
charsets.jar...
localedata.jar...

[root@k8s01 soft]# tar xvf Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz -C /usr/local/

[root@k8s01 soft]# cd /usr/local/mycat/conf/

[root@k8s01 conf]# vim schema.xml Mycat中间件实现Mysql主从读写分离

配置讲解：

schema name="wuhan" --需要做读取写分离的库

checkSQLschema="true" --执行sql时是否去掉schema名

sqlMaxLimit="100" --如果sql语句没有加limit限制，此时默认值是100

dataNode="dn1" --数据节点

dataHost="10.4.132.50" --数据主机名（任意起）

database="wuhan" --库名

balance="0" --不开启读写分离机制，所有操作都在master上。1 所有读操作都在slave节点上。 2 所有读操作都随机在master和slave节点上。 3 所有读操作都发送到slave节点，master节点只负责写。

select user() --心跳探测

--后端主机(用户名和密码是mycat服务器可以登陆后面mysql的权限)

--不写table name关键字，表示全库所有表做读写分离。

[root@k8s01 conf]# vim server.xml

123456 --mycat登陆帐号（密码可以随意指定）
wuhan --读写分离的库名

[root@k8s01 logs]# ../bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@k8s01 logs]#

6.查看各节点的状态

Mycat中间件实现Mysql主从读写分离

7.测试mycat实现的读写分离（登陆一次查询后要退出，再登陆查询）

[root@k8s01 conf]# mysql -h 127.0.0.1 -u root -p123456 -P 8066 -A
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.7.3-release-20190828215749 MyCat Server (OpenCloudDB)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use wuhan
Database changed
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 rows in set (0.08 sec)

mysql> exit
Bye
[root@k8s01 conf]# mysql -h 127.0.0.1 -u root -p123456 -P 8066 -A
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6.7.3-release-20190828215749 MyCat Server (OpenCloudDB)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use wuhan
Database changed
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 rows in set (0.00 sec)

mysql>

日志查看结果：

Mycat中间件实现Mysql主从读写分离