MySQL master-slave principle and configuration details

MySQL master-slave configuration and principle, for your reference, the specific contents are as follows

1. Environment selection:

1. Centos 6.5

2.MySQL 5.7

2. What is MySQL master-slave replication

MySQL master-slave replication is one of its most important functions. Master-slave replication means that one server acts as the master database server, and another or more servers act as slave database servers. The data in the master server is automatically copied to the slave servers. With multi-level replication, a database server can act as either a master or a slave. The basis of MySQL master-slave replication is that the master server records binary logs for database modifications, and the slave server automatically performs updates through the binary logs of the master server.

3. Types of MySQL master-slave replication

1. Statement-based replication

The statements executed on the master server are executed again on the slave server. This is supported in MySQL version 3.23 and later.

Disadvantages: The time may not be completely synchronized, causing deviations, and the user who executes the statement may be different.

2. Row-based replication

Copy the modified content on the main server directly to it, regardless of which statement caused the change. This method was introduced in MySQL 5.0 and later.

Disadvantages: For example, if there are 10,000 users in a salary table, and we add 1000 to the salary of each user, then row-based replication will have to copy 10,000 rows, which will cause a large overhead, while statement-based replication only requires one statement.

3. Mixed Types of Replication

MySQL uses statement-based replication by default. When statement-based replication causes problems, row-based replication is used, and MySQL automatically chooses it.

In the MySQL master-slave replication architecture, read operations can be performed on all servers, while write operations can only be performed on the master server. Although the master-slave replication architecture provides expansion for read operations, if there are many write operations (multiple slave servers need to synchronize data from the master server), the master server will inevitably become a performance bottleneck in the single-master model replication.

4. Principle

1. Master-slave

Any modification on the master server will be saved in the binary log. An I/O thread (actually a client process of the master server) is started on the slave server, which connects to the master server to request to read the binary log, and then writes the read binary log to a local realy log. Start a SQL thread on the server to periodically check the realy log. If any changes are found, immediately execute the changes on the local computer.

2. Master-slave-slave

If there is one master and multiple slaves, the master database is responsible for both writing and providing binary logs for several slave databases. At this time, you can make some adjustments and give the binary log only to a certain slave. This slave will then enable the binary log and send its own binary log to other slaves. Or simply make this slave not record anything and just forward the binary log to other slaves. This architecture may have much better performance, and the delay between data should also be slightly better.

【Notice】

1. In the old version of MySQL, the slave segment of the master-slave replication was not completed by two processes, but by one process, which then caused many risks and performance-related issues. The specific issues are as follows:

1. A process will make the process of copying bin-log logs and parsing logs and executing them in itself a serial process, which will limit the performance to a certain extent and the delay of asynchronous replication will be longer.

2. After the Slave side obtains the bin-log from the Master side, it needs to parse the log content and then execute it on its own. During this process, a large number of changes may have occurred on the Master side and a large number of new logs may have been added. If an unrecoverable error occurs in the storage on the Master side during this stage, all changes made during this stage will be irreversible. If the pressure on the Slave end is relatively high, this process may take a longer time.

5. The process of MySQL master-slave replication

1. Two situations: synchronous replication and asynchronous replication. Asynchronous replication is mostly used in production environments.

2. The basic process of replication:

1. The I/O process on the slave connects to the master and requests the log content after the specified position of the specified file (or from the beginning of the log).

2. After the Master receives the request from the Slave's IO process, the IO process responsible for replication will read the log information after the specified position of the log according to the request information and return it to the Slave's IO process. In addition to the information contained in the log, the returned information also includes the name of the bin-log file on the Master side where the information returned this time has arrived and the location of the bin-log.

3. After receiving the information, the Slave's IO process adds the received log content to the end of the Slave's relay-log file in sequence, and records the file name and position of the bin-log read from the Master into the master-info file, so that the next time it reads, it can clearly tell the Master "I need the log content from a certain position in a bin-log, please send it to me".

4. After the Slave's SQL process detects the new content added to the relay-log, it will immediately parse the content of the relay-log into the executable content when it is actually executed on the Master side, and execute it on itself.

6. Configuration:

1. First, clarify two situations:

1. Two independent servers or virtual machines.

2. Two virtual machines created using the template.

2. Name the two servers as follows: MySQL master server and MySQL slave server.

3. The master and slave servers perform the following operations respectively:

1. The MySQL version is consistent

2. Initialize the table

4. Modify the MySQL master server and MySQL slave server:

vim /etc/my.cnf 
[mysqld] 
log-bin=any name //Enable binary log server-id=any number //Server unique ID, the default value is 1, usually set to the last digit of the IP address

5. In the first case, restart the two servers directly; in the second case, rename the auto.cnf files of the two servers to auto.cnf.bak, and then restart the MySQL service.

6. Create an account on the MySQL master server and authorize the slave

grant replication slave on *.* to 'user name'@'%' identified by 'any password';

7. Query the status of the MySQL master server

show master status;

8. Configure MySQL slave server

change master to 
master_host = 'MySQL master server IP address', 
master_user = 'the user name previously created on the MySQL master server', 
master_password = 'previously created password', 
master_log_file = 'MySQL master server status binary file name', 
master_log_pos='position value in MySQL master server status';

9. Enable slave

start slave;

10. Check the status of the MySQL slave server

show slave status\G

Check

Slave_IO_Running: Yes //This status must be YES

Slave_SQL_Running: Yes //This status must be YES

If all changes are YES, the master-slave configuration is successful. When the MySQL master server is updated, the slave server will synchronize accordingly based on the specific configuration.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.