加入收藏 | 设为首页 | 会员中心 | 我要投稿 大连站长网 (https://www.0411zz.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

Mycat中间件达成Mysql主从读写分离

发布时间:2022-03-21 15:57:40 所属栏目:MySql教程 来源:互联网
导读: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]# tar xvf mysql
      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]# 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
  
[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
 
Enter password:
 
Welcome to the MySQL monitor. Commands end with ; or g.
 
Your MySQL connection id is 3
 
Server version: 5.7.27
 
[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
 
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 ;
 
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
 
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节点只负责写。

(编辑:大连站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!