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