mysql5.7 master slave replication, mycat2 configuration, read-write separation

Posted by tom2oo8 on Sat, 12 Feb 2022 04:18:03 +0100

mysql5.7 master-slave synchronization, mycat2 configuration, read-write separation (windows)

This article is referenced and reproduced from the article MySQL5. Master slave configuration,mycat2 fast build,mycat2 read write separation configuration , only used to record your learning process

1, Mysql5 7 configure master-slave synchronization

Master slave synchronization, which copies the data on the master server to the slave server.

Application scenario
Separate reading and writing, improve query access performance and effectively reduce the access pressure of the main database.
Real time disaster recovery. When the master database fails, it can quickly switch to the slave database.
Data summary: multiple master databases can be synchronously summarized into one slave database to facilitate data statistical analysis.
Deployment environment
Note: I use this machine to start two mysql on different ports.

Database: MySQL 5.7.0 X (compared with 5.5 and 5.6, 5.7 has better synchronization performance, supports multi-source replication, can realize multiple master and one slave, and the master-slave library version should be consistent)
Operating system: windows10
Main library: IP=localhost; PORT=3307; server-id=1; database=test; table=user
Slave Library: IP=localhost; PORT=3308; server-id=2; database=test; table=user
Configuration constraints
The master-slave library must ensure that the network is unblocked and accessible
The binlog log must be enabled for the main database
The server ID of the master-slave database must be different

[main library] operation and configuration

Configure my ini

[mysql]
default-character-set=utf8

[mysqld]
port=3307
#Other configurations are ignored. The following are the main configuration items
server_id=1
log-bin=mysql-bin  #[must] enable binary logging
expire-logs-days  = 7 #Keep binary logs for only 7 days to prevent the disk from being full of logs
binlog-ignore-db  = mysql       #Database not backed up
binlog-ignore-db  = information_schema
binlog-ignore-db  = performation_schema
binlog-ignore-db  = sys
binlog-do-db      = test #The name of the database that needs to be copied. If there are multiple names, copy binlog do dB

Create authorized user
Connect to the mysql main database, type the command mysql -u root -p, enter the password and log in to the database. Create a user to synchronize replication from the library and grant replication and synchronization access

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

log_ Is bin on

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

View master status

mysql> show master status;
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| mysql-bin.000008 |  2581064 | test         | mysql,information_schema,performation_schema,sys |                   |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
1 row in set

Note: MySQL bin 00000 1 and 154 parameters will be used from the library and modified according to the actual situation

[slave] configuration and operation

Configure my ini

[mysql]
default-character-set=utf8

[mysqld]
port=3308
#Other configurations are ignored. The following are the main configuration items
server_id=2

Set main library information
Log in to [from database] and enter the mysql command line.

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO MASTER_HOST='localhost',
MASTER_PORT=3307,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000008', # Master database information
MASTER_LOG_POS=2581064;   # Master database information
Query OK, 0 rows affected

mysql> start slave;
Query OK, 0 rows affected

View synchronization status

mysql> show slave status;
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------------------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File                   | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File                                                  | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------------------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | localhost   | slave       |        3307 |            60 | mysql-bin.000008 |             2581064 | DESKTOP-RA9R2VD-relay-bin.000002 |       2580327 | mysql-bin.000008      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |             2581064 |         2580544 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | ae0a8ec4-6fc1-11e9-821a-4ccc6a4d7345 | E:\work\phpstudy_pro\Extensions\mysql-other\3308\data\master.info |         0 | NULL                | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------------------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set

The status is Slave has read all relay log; waiting for more updates means no problem

Verify data synchronization
Build database
Log in to the [master server] with the root account and create the test database

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed

Build table
Create user table in main database

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

newly added
Insert a piece of data into the user table in the main database:

mysql> use test;
Database changed
mysql> INSERT INTO user (id, name, age) VALUES (1, 'Zhang San', 18);
Database changed
mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Zhang San |  18 |
+----+------+-----+
1 row in set

Query user table data in [slave database]:

mysql> use test;
Database changed
mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Zhang San |  18 |
+----+------+-----+
1 row in set

Synchronization of new records succeeded.

to update
Modify the data just inserted in [main database]:

mysql> UPDATE user SET name='Li Si' where id=1;
Database changed
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Li Si |  18 |
+----+------+-----+
1 row in set

Query user table data in [slave database]:

mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Li Si |  18 |
+----+------+-----+
1 row in set

Update record synchronization succeeded.

delete
Delete the data just updated in the main database:

mysql> DELETE FROM user WHERE id=1;
Database changed
mysql> SELECT * FROM user;
Empty set

Query user table data in [slave database]:

mysql> SELECT * FROM user;
Empty set

Delete record synchronization succeeded.

Supplement:

If the master server restarts the mysql service, the slave server will wait for reconnection with the master server. When the master server returns to normal, the slave server will automatically reconnect to the master server and synchronize data normally.
If the [slave database] server is abnormal and the synchronization is interrupted for a certain period of time (possibly due to the mismatch of synchronization point positions), try the following recovery methods: enter the [master database] server (normal), find the position before the [slave database] error in the bin log, and then re execute the change master in the [slave database] to start synchronization.

2, mycat2 environment configuration

1. Download the corresponding tar installation package and the corresponding jar package
zip package http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.20.zip
jar package http://dl.mycat.org.cn/2.0/1.21-release/ (download the latest jar package)
Because the official website may be contaminated by DNS, you need to modify the hosts file to access it

210.51.26.184 mycat.org.cn www.mycat.org.cn dl.mycat.org.cn

Download the required fat jar of mycat2, which is generally a jar file with a size of 100mb
Put the jar into the unzipped mycat\lib folder

2. mysql creates users and configures permissions
Create a user with the user name mycat and password 123456, and assign permission

CREATE USER 'mycat'@'%' IDENTIFIED BY '123456';
-- Authority to assign values as appropriate
GRANT ALL PRIVILEGES ON *.* TO 'mycat'@'%' ;
-- *[mysql8]Permission to assign is required 
GRANT XA_RECOVER_ADMIN ON *.* TO 'root'@'%';
flush privileges;

3. Modify the prototype configuration of mycat
Before starting mycat, you need to confirm the mysql database configuration corresponding to the prototype data source and modify mycat \ conf \ datasources \ prototypes datasource. The JSON file is as follows

{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"WRITE",
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"prototypeDs",
	"password":"123456",
	"type":"JDBC",
	"url":"jdbc:mysql://localhost:3307/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
	"user":"root",
	"weight":0
}

4. Start mycat

The following three methods are used to start mycat service. Select the following methods according to the environment

linux startup command

cd mycat/bin
./mycat start
./mycat status
./mycat start start-up
./mycat stop stop it
./mycat console Foreground operation
./mycat install Add to system auto start (not implemented yet)
./mycat remove Cancel automatic startup with the system (not implemented yet)
./mycat restart Restart service
./mycat pause suspend
./mycat status View startup status

windows startup command

cd mycat/bin
 
 
#PowerShell
 
./mycat install
./mycat start
./mycat status
 
 
#CMD
 
mycat install
mycat start
mycat status

jar execution

java -DMYCAT_HOME=mycat2\src\main\resources -jar mycat2-0.5-SNAPSHOP.jar Mycat2\mycat2\src\main\resources 

#Is the folder where the configuration file is located

‚Äč

test

Use mysql command for connection test. The default user of mycat is root, password: 123456 and port: 8066

mysql -uroot -p123456 -h192.168.28.130 -P8066

#Create a database

CREATE DATABASE db1;
#Create single table
CREATE TABLE db1.`travelrecord` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` varchar(100) DEFAULT NULL,
  `traveldate` date DEFAULT NULL,
  `fee` decimal(10,0) DEFAULT NULL,
  `days` int DEFAULT NULL,
  `blob` longblob,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
#query
select * from db1.travelrecord;

3, mycat2 configure read / write separation

Note: there is no create Library in mycay. To create a table, the master node needs to configure the logical library in the mycat configuration file after creating the library (physical library). Similarly, the sub database and sub table need additional configuration.
1. Preparation
The installed mycat and the master-slave mysql. There is a test library in mysql, and tables and data already exist

2. Configure directly through commands
Connect mycat port 8066 account root password 123456

mysql -uroot -p123456 -P8066 -h127.0.0.1
#Create db2 logical library
create database test;

3. View the configuration of the created schema

customTables places the configuration of mycat's default table
globalTables places the configuration of global tables
shardingTables configuration for placing sharding tables
Normal tables configuration for placing normal tables

mycat\conf\schemas\test.schema.json

{
    "customTables":{},
    "globalTables":{},
    "normalTables":{},
    "schemaName":"db2",
    "shardingTables":{},
    "views":{}
}

4. Configure data source
Add the configuration file from database data source under mycat\conf\datasources: DR0 datasource. json

{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"READ",
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"dr0",
	"password":"123456",
	"type":"JDBC",
	"url":"jdbc:mysql://localhost:3308/?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
	"user":"root",
	"weight":0
}

5. Cluster configuration
Modify MYCAT \ conf \ clusters \ prototype cluster. The JSON file is as follows
Master data source
replicas is a data source from a database

{
	"clusterType":"MASTER_SLAVE",
	"heartbeat":{
		"heartbeatTimeout":1000,
		"maxRetry":3,
		"minSwitchTimeInterval":300,
		"slaveThreshold":0
	},
	"masters":[
		"prototypeDs"
	],
	"maxCon":200,
	"name":"prototype",
	"readBalanceType":"BALANCE_ALL",
	"switchType":"SWITCH",
	"replicas": [
		"dr0"
	]
}

6. Modify the schema configuration of test

{
  "customTables":{},
  "globalTables":{},
  "normalTables":{},
  "schemaName":"test",
  "shardingTables":{},
  "targetName":"prototype"
}

After modification, restart mycat

Architecture diagram at this time

Configuration complete
Reference link

Mycat2 · YuQue

Topics: Database MySQL server