重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
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)
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.
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)
+----------------+----------+--------------+------------------+-------------------+
| 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)
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.
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)
Query OK, 0 rows affected, 2 warnings (0.08 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 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
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
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)
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)
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
+------+
| 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
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> 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
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> 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
配置讲解：
schema name="wuhan" --需要做读取写分离的库
checkSQLschema="true" --执行sql时是否去掉schema名
sqlMaxLimit="100" --如果sql语句没有加limit限制，此时默认值是100
