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节点只负责写。 (编辑:大连站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
站长推荐