search
HomeDatabaseMysql TutorialDetailed explanation of the master-slave replication process in MySQL_Detailed explanation of Mysql examples

This article mainly introduces the implementation process of MySQL master-slave replication in detail. It has certain reference value. Interested friends can refer to it. I hope it can help everyone.

1. What is master-slave replication

Transmit the DDL and DML operations in the master database to the slave database through binary logs (BINLOG), and then transfer these logs Re-execute (redo); thus making the data in the slave database consistent with the master database.

2. The role of master-slave replication

1. If there is a problem with the master database, you can switch to the slave database.

2. Read and write separation can be performed at the database level.

3. Daily backup can be performed on the slave database.

3. Replication process

Binary log: Binary log of the master database

Relay log: Relay log of the slave server

Step one: master writes the operation record serially to the binlog file before each transaction update data is completed.

Second step: salve opens an I/O Thread. This thread opens a normal connection on the master and its main job is the binlog dump process. If the reading progress has caught up with the master, it enters sleep state and waits for the master to generate new events. The ultimate purpose of the I/O thread is to write these events to the relay log.

Step 3:SQL Thread will read the relay log and execute the SQL events in the log sequentially to be consistent with the data in the main database .

4. Specific operations of master-slave replication

I installed two msyql instances in different paths on the same windows. It is recommended that the installed versions of mysql between the master and slave here are consistent, although my own is inconsistent.

#1. Modify the configuration files my.ini of the master and slave databases respectively

master

3306 is the default port number of mysql, which does not need to be modified in the master instance; server-id is used to specify a unique ID, and different mysql instances do not need to be repeated; binlog-do-db needs to be copied if specified database; log-bin is used to open binary log files.

salve

Since the master-slave database will be run on the same computer later, the port needs to be set to different Same, here is 3307

replicate-do-db: the name of the database that needs to be synchronized, consistent with the configuration on the master.

2. Create an account specifically for replication on the master: weidai/123456

This new account can be found in the table mysql.user Query:

When I first operated, I completed the creation of this account here, but when I actually copied it, I found that there was no copy. Successfully, when troubleshooting the error, it was found that there was no problem with the binlong generated by the master, and then checked the status of the slave:

There is such an error line at the end:

Using the Weidai account cannot connect to the master, so the master's binlog should not be obtained, resulting in the relay log not being generated.

I repeatedly checked the account and password and found no problems. Then I searched for relevant information and found out that it was because there was a missing step when the master created a new user:

Set up a new user or change it After entering the password, you need to use flush privileges to refresh MySQL's system permissions related tables, otherwise access will be denied. This is why the previous error occurred. Another way is to restart the mysql server to make the new settings take effect.

3. Obtain the position of the data in the master database at this moment. It is mainly used to copy the starting position of the data after starting from the data. However, before obtaining this status value, the master The database can no longer have data modification operations, so it is necessary to set the read lock to be valid

4. The main library performs data backup. There are many methods of backup. I will not introduce them here. You can refer to my previous article. After the backup is completed, the read lock can be released and the main library can perform write operations.

5. Start the slave database and restore the data just backed up. At this time, the data of the master and slave databases at the backup time point are consistent.

6. Configuration related to replication behavior on the slave database

7. At this time, the configuration is completed, but the slave database cannot yet be synchronized and needs to be started. slave thread

#8. Create a table and add new data in the master, and observe in the slave:

Yes It can be seen that the operations I perform in the master can be reflected in the slave. At this time, the slave is like a mirror of the master.

5. Interpretation of master-slave synchronization status

Use the command on the slave to view:

Due to typesetting It’s too ugly, so I organized it as follows:

Slave_IO_STATE:Waiting for master to send event

Master_host:127.0.0.1

Master_user:weidai

Master_port: 3306

connnect_retry:60

Master_log_file:mysql-bin.000005

Read_Master_log_pos:1662

Relay_log_file:AE6Z*****-relay-bin .000002

Relay_log_pos:1415

Slave_IO_Running:yes

Slave_SQL_Running:yes

------------- -----------------------------------------------Gorgeous dividing line--- ----------------------------------------

Slave_IO_Running:yes

Slave_SQL_Running:yes

As mentioned earlier, these two threads are two very important threads involved in the replication process on the slave. YES means normal, NO means abnormal.

The Slave_IO thread mainly copies the binlong log content on the master to the slave's relay log (Relay_log). Generally, the probability of problems is small. Most problems are caused by permissions or network issues. Unable to connect to master. Just like the error mentioned earlier.

The Slave_SQL thread is responsible for executing the SQL in the relay log, and the probability of errors is relatively high. If someone manually inserts some records into the slave database, a primary key conflict will occur during master-slave synchronization.

Slave_IO_STATE:Waiting for master to send event

This status indicates that the relay log synchronization is completed and waiting for new events to be generated by the master.

Related recommendations:

Detailed explanation on setting up a master-slave instance for MySQL5.7.18 master-slave replication

Mycat read-write separation in MySQL Example implemented based on master-slave replication

Detailed example of how MySQL implements the master-slave replication process (picture)

The above is the detailed content of Detailed explanation of the master-slave replication process in MySQL_Detailed explanation of Mysql examples. For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

Explain the differences between InnoDB and MyISAM storage engines.Explain the differences between InnoDB and MyISAM storage engines.Apr 27, 2025 am 12:20 AM

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

What are the different types of JOINs in MySQL?What are the different types of JOINs in MySQL?Apr 27, 2025 am 12:13 AM

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use