This article uses examples to illustrate the principles and usage of MySQL master-slave replication and read-write separation. Share with you for your reference, the details are as follows:

Master-slave replication

concept

Operations that affect the MySQL-A database are written to the local log system A after being executed on the database.
Assume that the changed database event operations in the log system are sent to MYSQL-B through the network at port 3306 of MYSQL-A in real time.
After receiving it, MYSQL-B writes it to the local log system B, and then completes the database events one by one in the database.
Then, if MYSQL-A changes, MYSQL-B will also change. This is the so-called MYSQL replication.
In the above model, MYSQL-A is the master server, and MYSQL-B is the slave server.
Log system A is actually a binary log in the MYSQL log type, which is specifically used to save all actions that modify database tables, namely bin log. [Note that MYSQL will write to the binary log after executing the statement and before releasing the lock to ensure transaction security]
Log system B is not a binary log. Since it is copied from the binary log of MYSQL-A and is not generated by its own database changes, it has a relay feeling and is called a relay log.
It can be found that through the above mechanism, the database data of MYSQL-A and MYSQL-B can be guaranteed to be consistent, but there will definitely be a delay in time, that is, the data of MYSQL-B is lagging.
Even without considering any network factors, MYSQL-A's database operations can be executed concurrently, but MYSQL-B can only read one entry from the relay log and execute it. Therefore, MYSQL-A writes frequently and MYSQL-B may not be able to keep up. 】

Solving the problem

How to prevent data loss, backup, read-write separation, database load balancing, high availability

Environment Construction

1. Prepare the environment. The IP addresses of the two Windows operating systems are: 172.27.185.1 (master) and 172.27.185.2 (slave)
2. Connect to the master service (172.27.185.1) server and assign account permissions to the slave node.
GRANT REPLICATION SLAVE ON . TO 'root'@'172.27.185.2' IDENTIFIED BY 'root';
3. Add in the main service my.ini file

server-id=200
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index

Restart mysql service
4. Add in the slave service my.ini file

server-id = 210
replicate-do-db =itmayiedu #Database synchronization required

Restart mysql service
5. Synchronize the master database from the service

stop slave;
change
master to master_host='172.27.185.1',master_user='root',master_password='root';
start slave;
show slave status;

Notes: ① Make sure to be in the same LAN ② Use 360WiFi to create a LAN ③ It is best to turn off all firewalls

What is read-write separation

In the database cluster architecture, the master database is responsible for processing transactional queries, while the slave database is only responsible for processing select queries, so that the division of labor between the two is clear to improve the overall read and write performance of the database. Of course, another function of the master database is to synchronize data changes caused by transactional queries to the slave database, which is the write operation.

Benefits of read-write separation

1) Share the server pressure and improve the system processing efficiency of the machine. Read-write separation is suitable for scenarios where reading far outweighs writing. If there is a server with a lot of selects, update and delete will be blocked by the data being accessed by these selects and wait for the select to end. The concurrency performance is not high. The master and slave are only responsible for their own writes and reads, which greatly alleviates the contention for X locks and S locks.
Assume that we have 1 master and 3 slaves, ignoring the unilateral setting of the slave library mentioned in 1 above, and assume that there are 10 writes and 150 reads in 1 minute. Then, 1 master and 3 slaves is equivalent to a total of 40 writes, while the total number of reads remains unchanged, so on average each server is responsible for 10 writes and 50 reads (the master database does not undertake read operations). Therefore, although the writes remain unchanged, the reads are greatly distributed, improving system performance. In addition, when the read is amortized, the write performance is indirectly improved. Therefore, the overall performance has been improved. To put it bluntly, it means trading machines and bandwidth for performance.
2) Increase redundancy and improve service availability. When a database server goes down, another slave can be adjusted to restore service as quickly as possible.

Master-slave replication principle

Depends on binary log, binary-log.
The binary log records the statements that cause changes to the database
Insert, delete, update, create table

Difference between Scale-up and Scale-out

Scale Out means that the application can be expanded horizontally. Generally speaking, for data center applications, scale out means that when more machines are added, the application can still make good use of the resources of these machines to improve its efficiency and achieve good scalability.
Scale Up means that the Application can be expanded in the vertical direction. Generally speaking, for a single machine, Scale Up means that when a computing node (machine) adds more CPU Cores, storage devices, and uses larger memory, the application can make full use of these resources to improve its efficiency and achieve good scalability.

MyCat

What is Mycat

It is an open source distributed database system. However, because databases generally have their own database engines, and Mycat does not have its own unique database engine, it cannot be considered a complete database system in a strict sense. It can only be said to be a middleware that acts as a bridge between applications and databases.
Before the emergence of Mycat middleware, if you want to achieve read-write separation in MySQL master-slave replication cluster, you usually do it in the program segment. This brings a problem, that is, the coupling between the data segment and the program is too high. If the address of the database changes, then my program must be modified accordingly. If the database accidentally hangs up, it also means that the program is unavailable, which is unacceptable for many applications.

The introduction of Mycat middleware can effectively decouple the program and the database. In this way, the program only needs to pay attention to the address of the database middleware without knowing how the underlying database provides services. A large number of common data aggregation, transactions, data source switching and other tasks are handled by the middleware.
The principle of Mycat middleware is to shard the data. From the original database, it is divided into multiple shard databases. All shard database clusters constitute the complete database storage, which is somewhat similar to RAID0 in the disk array.

Mycat Installation

Create table structure

CREATE DATABASE IF NOT EXISTS `weibo_simple`;
-- ------------------------------------
-- Table structure for `t_users` user table-- ------------------------------------
DROP TABLE IF EXISTS `t_users`;
CREATE TABLE `t_users` (
 `user_id` varchar(64) NOT NULL COMMENT 'Registered user ID',
 `user_email` varchar(64) NOT NULL COMMENT 'Registered user email',
 `user_password` varchar(64) NOT NULL COMMENT 'Registered user password',
 `user_nikename` varchar(64) NOT NULL COMMENT 'Registered user nickname',
 `user_creatime` datetime NOT NULL COMMENT 'Registration time',
 `user_status` tinyint(1) NOT NULL COMMENT 'Verification status 1: verified 0: not verified',
 `user_deleteflag` tinyint(1) NOT NULL COMMENT 'Delete mark 1: deleted 0: not deleted',
 PRIMARY KEY (`user_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -------------------------------------
-- Table structure for `t_message` microblog table-- -------------------------------------
DROP TABLE IF EXISTS `t_message`;
CREATE TABLE `t_message` (
 `messages_id` varchar(64) NOT NULL COMMENT 'Weibo ID',
 `user_id` varchar(64) NOT NULL COMMENT 'Posting user',
 `messages_info` varchar(255) DEFAULT NULL COMMENT 'Weibo content',
 `messages_time` datetime DEFAULT NULL COMMENT 'Release time',
 `messages_commentnum` int(12) DEFAULT NULL COMMENT 'Number of comments',
 `message_deleteflag` tinyint(1) NOT NULL COMMENT 'Delete mark 1: deleted 0: not deleted',
 `message_viewnum` int(12) DEFAULT NULL COMMENT 'Number of views',
 PRIMARY KEY (`messages_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `t_message_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_users` (`user_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Configure server.xml

	<!-- Add user -->
  <user name="mycat">
  <property name="password">mycat</property>
  <property name="schemas">mycat</property>
  </user>
	
	<!-- Add user -->
  <user name="mycat_red">
  <property name="password">mycat_red</property>
  <property name="schemas">mycat</property>
	<property name="readOnly">true</property>
  </user>

Configure schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
  <!-- Same as the schema name of user in server.xml -->
  <schema name="mycat" checkSQLschema="true" sqlMaxLimit="100">
    <table name="t_users" primaryKey="user_id" dataNode="dn1" rule="rule1"/>
   
    <table name="t_message" type="global" primaryKey="messages_id" dataNode="dn1" />
  </schema>
<dataNode name="dn1" dataHost="jdbchost" database="weibo_simple


" />
  
  <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"
        slaveThreshold="100">
     <heartbeat>select user()</heartbeat> 
    <writeHost host="hostMaster" url="172.27.185.1:3306" user="root" password="root">
    </writeHost>
    <writeHost host="hostSlave" url="172.27.185.2:3306" user="root" password="root"/>
  </dataHost>
  
</mycat:schema>

Configure the rule.xml file

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- You may not use this file except in compliance with the License. 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
	 <tableRule name="rule1">
    <rule>
      <columns>user_id</columns>
      <algorithm>func1</algorithm>
    </rule>
  </tableRule>
  <function name="func1" class="org.opencloudb.route.function.AutoPartitionByLong">
 	<property name="mapFile">autopartition-long.txt</property>
  </function>
</mycat:rule>

To better locate errors, modify log4j.xml

Double-click startup_nowrap.bat to start

Frequently asked questions
SHOW MASTER STATUS If yes, add a line to the my.ini file
log-bin=mysql-bin

Assign permissions to accounts
grant all privileges on . to 'root'@'172.27.185.1' identified by 'root';

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.