Mycat middleware realizes the separation of Mysql master and slave read and write

Environmental planning:

IP address hostname role remark 10.4.132.50k8s01mycatpender master

10.4.132.42k8s02slave

10.4.132.66k8s03slave

Mycat download address: http://dl.mycat.io/1.6.7.3/20190828135747/Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz

Mysql download address: http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

Mycal manages cluster port: [root@k8s01 conf] # mysql-h 127.0.0.1-u root-p123456-P 9066

Mycat data port: [root@k8s01 conf] # mysql-h 127.0.0.1-u root-p123456-P 8066

1. Download and install mysql (1 master node and 2 slave nodes)

[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 Tencent 5yQuan 7Troujc-login password

[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 node and slave node act as master and slave

Master node:

[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. Two slave nodes (both slave nodes are connected to the master node)

[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. Verify whether the master-slave data is synchronized

Master node:

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 (an 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 node:

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 node:

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. Download installation configuration Mycat (master node)

[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

Configuration instructions:

Schema name= "wuhan"-- A library that needs to do read-write separation

CheckSQLschema= "true"-whether to remove the schema name when executing sql

SqlMaxLimit= "100"-if the sql statement does not have a limit limit, the default value is 100

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 >

Log view results:

You can log in repeatedly and write the data results (you must exit the session and log in again):