快上网专注成都网站设计 成都网站制作 成都网站建设
成都网站建设公司服务热线：028-86922220

网站建设知识

十年网站开发经验 + 多家企业客户 + 靠谱的建站团队

量身定制 + 运营维护+专业推广+无忧售后，网站问题一站解决

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

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

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)
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主从读写分离


文章题目：Mycat中间件实现Mysql主从读写分离
网站地址：http://6mz.cn/article/pcgjii.html

其他资讯

免费获取网站建设与品牌策划方案报价

*主要业务范围包括：高端网站建设， 集团网站建设(网站建设网站制作)找网站建设公司就上快上网。
提交需求

    联系我们

    028-86922220
  • 手机：13518219792
  • 地址：成都市太升南路288号锦天国际A幢1002号
  • 24小时服务热线：400-028-6601

    网站建设服务

  • 网页设计
  • 网站制作
  • 网站开发

    网站推广服务

  • 营销网站建设
  • 百度快速排名
  • 整站网站推广

    网站运维服务

  • 基础维护
  • 网站改版
  • 网站维护

    FOLLOW US

  • 微信二维码

    微信二维码

友情链接： 抖音短视频   成都标识标牌设计制作   SF双层罐   上海除甲醛   成都网站制作   移动APP   成都免费做网站公司   中建建业环保   彭山网站制作   云主机   
Copyright © 2022 成都快上网科技有限公司 成都网站建设公司-选网站建设公司快上网！国内专业的网站制作公司！
All Rights Reserved 版权所有 蜀ICP备19037934号-11