A detailed explanation of the principle and usage of MySQL master-slave copy and read-write separation

Time:2020-3-21

This article describes the principle and usage of MySQL master-slave copy and read-write separation. To share with you for your reference, as follows:

Master slave replication

在这里插入图片描述

concept

The operations affecting mysql-a database will be written to local log system a after the database is executed.
Suppose that the database event operation in the changed log system is sent to mysql-b through the network on port 3306 of mysql-a in real time.
After mysql-b receives it, it writes to local log system B, and then completes database events in the database one by one.
Then, mysql-a changes and mysql-b changes. This is the so-called MySQL replication.
In the above model, MYSQL-A is the master server, that is, master, and MYSQL-B is the slave server, that is, slave.
Log system a, in fact, is a binary log in MySQL’s log type, that is, it is specially used to save all actions of modifying database tables, that is, bin log. [note that MySQL will write binary log after executing statement and before releasing lock to ensure transaction security]
Log system B is not binary log, because it is copied from the binary log of mysql-a, and it is not generated by the change of its own database. It is a bit of relay log, which is called relay log.
It can be found that the above mechanism can ensure the consistency of mysql-a and mysql-b database data, but there must be a delay in time, that is, mysql-b data is lagging.
[mysql-a’s database operations can be executed concurrently, regardless of network factors, but mysql-b can only read one from the relay log and execute it. Therefore, mysql-a writes frequently, and mysql-b may not keep up with it. ]

Solve the problem

How not to lose data
backups
Read / write separation
Database load balancing
High availability

Environment building

1. Preparation 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 from the service my.ini file

server-id = 210
Replicate do DB = itmayiedu ා need to synchronize database

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;

Matters needing attention
① Must be in the same LAN
② Create a LAN with 360wifi
③ It’s better to turn off all the firewalls

What is the separation of reading and writing

In the database cluster architecture, the main 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 them is clear to improve the overall read-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, that is, write operations.

The benefits of separation of reading and writing

1) Share the server pressure and improve the system processing efficiency of the machine
The read-write separation is applicable to the scenario where read is far better than write. If there is a server, when there are many select, update and delete will be blocked by the data in these select accesses, waiting for the end of select, and the concurrency performance is not high, while the master and slave are only responsible for their own write and read, greatly easing the X-lock and S-lock contention;
If we have 1 master and 3 slaves, regardless of the slave database unilateral setting mentioned in 1 above, assume that there are 10 writes and 150 reads in 1 minute. Then, 1 master and 3 slave are equivalent to a total of 40 writes, while the total number of reads remains unchanged, so on average, each server undertakes 10 writes and 50 reads (the master database does not undertake the read operation). Therefore, although the write has not changed, the read has been greatly allocated and the system performance has been improved. In addition, when the read is allocated, the write performance is improved indirectly. Therefore, the overall performance has been improved, that is to say, the performance of the machine and bandwidth has been replaced;
2) Increase redundancy and improve service availability. When one database server goes down, another slave database can be adjusted to restore service at the fastest speed

Principle of master-slave replication

Depending on binary log, binary log
The statement that causes the database to change is recorded in the binary log
Insert 、delete、update、create table

The difference between scale up and scale out

Scale out means that application can expand horizontally. Generally speaking, for the application of data center, 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 refers to the vertical expansion of application. Generally speaking, for a single machine, the Scale Up value is that when a computing node (machine) adds more CPU Cores, storage devices and uses more 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, but because the database generally has its own database engine, and MYCAT does not belong to its own unique database engine, all in a strict sense is not a complete database system, only a middleware that plays a bridge role between the application and the database.
Before the emergence of MYCAT middleware, MySQL master-slave replication cluster, if you want to achieve read-write separation, is generally implemented in the program segment, which brings a problem, that is, the coupling degree between the data segment and the program is too high, if the address of the database has changed, then my program should also be modified accordingly. If the database is accidentally hung up, it also means the program’s Not available, but not acceptable for many applications;

The introduction of MYCAT middleware can decouple the program and database well. In this way, the program only needs to pay attention to the address of database middleware without knowing how the underlying database provides services. A large number of general data aggregation, transactions, data source switching and other work are handled by the middleware;
The principle of MYCAT middleware is to segment the data. From the original database, it is divided into multiple segmented databases. All the segmented database clusters form the completed database storage, which is 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' register user ID ',
 `User email ` varchar (64) not null comment 'register user email',
 `User_password ` varchar (64) not null comment 'register user password',
 `User_nikename ` varchar (64) not null comment 'register user nickname',
 `User "create ` datetime not null comment 'registration time',
 `User_status ` tinyint (1) not null comment 'validation status 1: validated 0: not validated',
 `User [deleteflag ` tinyint (1) not null comment 'delete flag 1: deleted 0: not deleted',
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -------------------------------------
--Table structure for 't'message' microblog
-- -------------------------------------
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 'publish 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 flag 1: deleted 0: not deleted',
 `Message \ viewnum ` int (12) default null comment 'browsed',
 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/">
  <! -- consistent with 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. - You 
	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">
 	<property name="mapFile">autopartition-long.txt</property>
  </function>
</mycat:rule>

To better locate the error, modify log4j.xml

Double click Startup? Nowrap.bat to start

Common problem
Show master status if is, add a line to my.ini file
log-bin=mysql-bin

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

For more information about mysql, readers who are interested in MySQL can see the special topics of this site: MySQL query skills, MySQL common functions, MySQL log operation skills, MySQL transaction operation skills, MySQL stored procedure skills and MySQL database lock related skills

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