行业资讯

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

发布时间：2020-08-18 03:56:41 作者：z597011036
来源：ITPUB博客 阅读：359

环境规划：

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节点：

[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)
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 master status ;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 |     3093 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
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)
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> 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

<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

<user name="root" defaultAccount="true">
        <property name="password">123456</property>      --mycat登陆帐号（密码可以随意指定）
         <property name="schemas">wuhan</property>        --读写分离的库名
 </user>

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

日志查看结果：

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

可重复登陆查询，写入数据结果（必须退出会话再登陆查询）：

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

推荐阅读：
  1. Mysql mycat读写分离
  2. MyCat读写分离配置

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。

mycat mysql 实现

上一篇：软件测试要学什么（6）发展前景及工资待遇

下一篇：oracle 左表链接

相关阅读

您好，登录后才能下订单哦！

密码登录
登录注册
获取短信验证码
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》
产品服务
地区划分
帮助支持
关于我们
行业资讯-文章归档 问答-问答归档
广州亿速云计算有限公司

7*24小时在线电话：400-100-2938

7*24小时在线QQ：800811969

Copyright © Yisu Cloud Ltd. All Rights Reserved. 2018 版权所有
粤ICP备17096448号-1 粤公网安备 44010402001142号