Mycat中间件实现Mysql主从读写分离
环境规划:
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
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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节点:
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)
affiliates. Other names may be trademarks of their respective owners.
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 | 3093 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.两个slave节点(两个slave节点都要连接到master节点)
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)
affiliates. Other names may be trademarks of their respective owners.
Query OK, 0 rows affected, 2 warnings (0.08 sec)
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
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
4.验证主从数据是否同步
master节点:
mysql> system hostname
k8s01
mysql> create database wuhan charset utf8;
Query OK, 1 row affected (0.00 sec)
Database changed
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
slave1节点:
mysql> system hostname
k8s02
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
slave2节点:
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
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
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
配置讲解:
schema name="wuhan" --需要做读取写分离的库
checkSQLschema="true" --执行sql时是否去掉schema名
sqlMaxLimit="100" --如果sql语句没有加limit限制,此时默认值是100
<table name="t1" --指定读写分离的表
dataNode="dn1" --数据节点
<dataNode name="dn1" --对应上面的数据节点(任意起)
dataHost="10.4.132.50" --数据主机名(任意起)
database="wuhan" --库名
<dataHost name="10.4.132.50" --对应以上值
balance="0" --不开启读写分离机制,所有操作都在master上。1 所有读操作都在slave节点上。 2 所有读操作都随机在master和slave节点上。 3 所有读操作都发送到slave节点,master节点只负责写。
<heartbeat>select user()</heartbeat> --心跳探测
<writeHost host="W_k8s01" url="10.4.132.50:3306" user="repl" password="123456"> --后端主机(用户名和密码是mycat服务器可以登陆后面mysql的权限)
<schema name="hubei" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> --不写table name关键字,表示全库所有表做读写分离。
[root@k8s01 conf]# vim server.xml
<property name="password">123456</property> --mycat登陆帐号(密码可以随意指定)
<property name="schemas">wuhan</property> --读写分离的库名
[root@k8s01 logs]# ../bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@k8s01 logs]#
6.查看各节点的状态
7.测试mycat实现的读写分离(登陆一次查询后要退出,再登陆查询)
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)
affiliates. Other names may be trademarks of their respective
owners.
Database changed
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 rows in set (0.08 sec)
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)
affiliates. Other names may be trademarks of their respective
owners.
Database changed
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 rows in set (0.00 sec)
日志查看结果:
可重复登陆查询,写入数据结果(必须退出会话再登陆查询):