MySQL master-slave principle and configuration, MySQL master-slave principle Configuration

MySQL master-slave principle and configuration, MySQL master-slave principle Configuration

Infi-chu:

Http://www.cnblogs.com/Infi-chu/

 

MySQL master-slave configuration and principles

1. Environment selection:

1. Centos 6.5

2. MySQL 5.7

 

Ii. 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 the other or multiple servers act as slave database servers. Data in the master server is automatically copied to the slave server. For multi-level replication, the database server can act as the host or slave machine. 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.

 

Iii. MySQL master-slave replication type

1. Statement-based Replication

The statements executed on the master server are re-executed on the slave server and are supported after the MySQL-3.23 version.

Disadvantage: The time may not be completely synchronized, causing deviation. the user who executes the statement may also be a different user.

 

2. Row-based Replication

Directly copy the content adapted on the master server, regardless of the statement that caused the change to the content, introduced after the MySQL-5.0 version.

Disadvantages: for example, if there are 10 thousand million users in a payroll table and we have a salary of + 1000 for each user, we need to copy the contents of 10 thousand rows based on Row-based replication, resulting in a large overhead, statement-based replication is only one statement.

 

3. Hybrid Replication

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

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 ), in the replication of a single master model, the master server is bound to become a performance bottleneck.

 

Iv. Principles

1. master-slave (master-slave)

Any modifications made to the master server will be saved in the Binary log of the Binary log, and an I/O thread (actually a client process of the master server) will be started from the server ), connect to the master server and request to Read Binary logs. Then, write the binary logs to a local Realy log. Enable an SQL thread on the server to regularly check the Realy log. If any change is found, immediately execute the change on the local machine.

 

2. master-slave (master-slave)

If one master database has multiple slave databases, the master database is responsible for both writing and providing binary logs for several slave databases. In this case, you can slightly adjust the binary log to only one slave, which then enables the binary log and sends its binary log to another slave. Or simply, this never record is only responsible for forwarding binary logs to other slaves, so the Architecture performance may be much better, and the latency between data should be slightly better.

 

Note]

1. in earlier versions of MySQL, the slave segment of master-slave replication is not completed by two processes, but by one process. Many problems related to risks and performance have emerged. Specific problems are as follows:

1. A process will replicate the bin-log and parse the log and become a serial process in its own execution process. The performance is limited, and the latency of asynchronous replication will also be relatively long.

2. After the Slave side obtains the bin-log from the Master side, it needs to parse the log Content and execute it on its own. In this process, the Master may produce a lot of changes and add a lot of logs. If an irreparable error occurs in the Master storage at this stage, all changes made at this stage will never be recovered. If the load on the Slave side is relatively high, the process may take a long time.

 

V. MySQL master-slave replication process

1. Two cases: Synchronous replication and asynchronous replication. Most production environments use asynchronous replication.

2. Basic replication process:

1. the I/O Process on the slave connects to the master, and requests the log content after the specified location of the specified file (or from the initial log.

2. After the Master receives a request from the Slave IO process, the IO process responsible for replication reads the log information at the specified location based on the request information and returns it to the Slave 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 end and the location of the bin-log.

3. after the Slave IO process receives the information, it adds the received log content to the end of the relay-log file at the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, in this way, you can clearly tell the Master "from where a bin-log is to be read, send it to me ".

4. after the Slave SQL process detects the newly added content in relay-log, it will immediately parse the relay-log content into the executable content during actual execution on the Master end, and execute it on your own.

 

Vi. Configuration:

1. First, clarify the two situations:

1. Two independent servers or virtual machines.

2. Two virtual machines created using the template.

2. The following two servers are named respectively: MySQL master server and MySQL slave server.

 

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

1. MySQL versions are consistent

2. initialize the table

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

Vim/etc/my. cnf [mysqld] log-bin = retrieve any name // enable the binary log server-id = any number // unique server ID, the default value is 1, it is generally set to the last digit of the IP address.

5. in the first case, restart the two servers. In the second case, you need to set the auto of the two servers. rename the cnf file to auto. cnf. bak, and then restart the MySQL service.

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

Grant replication slave on *. * to 'username '@' % 'identified by 'arbitrary password ';

7. query the status of the MySQL master server

 show master status;

8. Configure the MySQL slave server

Change master to master_host = 'IP address of the MySQL master server ', master_user = 'username created on the MySQL master server', master_password = 'password created earlier ', master_log_file = 'binary file name in the MySQL master server status ', master_log_pos = 'position value in the MySQL master server status ';

9. Enable slave

 start slave;

10. view the MySQL slave server status

 show slave status\G

View

Slave_IO_Running: Yes // This status must be YES

Slave_ SQL _Running: Yes // This status must be YES

YES indicates that the master-slave configuration is successful. When the master server of MySQL changes, the slave server synchronizes the configuration accordingly.