MYSQL master-slave replication knowledge points summary

An optimization solution when a single MYSQL server cannot meet the current website traffic. Need to build MySQL cluster technology.

1. Function:

When inserting, modifying, or deleting data on the master server, the data will be automatically synchronized to the slave server.

Note: Master-slave replication is one-way, only master->slave

There are two types: Transmitting type (one master and multiple slaves): generally used in: backup and read-write separation.

Ring (multi-master and multi-slave): General use: when the main server is under great pressure, cross-regional websites can achieve data synchronization

In a ring structure, if records are inserted into the same table on three servers at the same time, an "ID conflict problem" will occur.

Solution: Let the three servers generate different IDs;

The first one: 1,4,7...

Second channel: 2,5,8...

Channel 3: 3, 6, 9...

This can be set in the MYSQL configuration file:

2. The principle of master-slave (using bin log)

There is a log in MySQL called bin log (binary log). This log records all SQL statements that modify the database (insert, update, delete, ALTER TABLE, grant, etc.). The principle of master-slave replication is actually to copy the BIN log on the master server to the slave server and execute it again, so that the data on the slave server is the same as the data on the master server.

Extension: Logs in mysql:

Query log Error log Bin log

Slow log: You can set a time threshold, such as 0.5 seconds, so that all SQL statements whose execution time exceeds this value will be recorded in the future. In this way, we can record the slow SQL statements and optimize them specifically.

Purpose: You can quickly locate the SQL that is dragging down the website, and then optimize it by: creating an index and caching the results of this SQL.

3. Actual Configuration

Use the MYSQL of Windows system as the master server and the one under LINUX as the slave server.

Primary Server:

1. Enable bin logging

Modify the mysql configuration file: my.ini add:

Assign a server-id to the server (the ID values ​​of the master and slave servers cannot be repeated)

If it is a ring server, you need to add the following items:

log-slave-updates = on // If it is a ring multi-server, you need to set this item.

Create an account on the master server for the slave server to synchronize data

Login to MYSQL

Execute a SQL:

Created an account with only REPLICATION SLAVE permissions: Username: slave Password: 1234

Execute SQL on the primary server to view the status of the current bin log of the primary server

Note: These two values ​​will change every time you modify the data, so after checking these two values, do not operate the master server and go directly to the slave server configuration. Otherwise, the values ​​will not correspond and the synchronization will fail.

From the server (linux):

Enable bin log

Modify the configuration file /etc/my.cnf

Set a server-id:

3. If it is a ring server, you need to add the following items:

log-slave-updates = on // If it is a ring multi-server, you need to set this item.

4. Execute SQL statements on the slave server to configure the address of the master server:

Log in to MYSQL:

Set up the slave server and start replication

5. Execute SQL to query whether the status of the slave server is configured successfully:

If it is two YES then the code succeeds! Finish!

Note: Before the configuration is successful, the data on the master server will not automatically be transferred to the slave server. Therefore, you need to manually import all the data on the master server to the slave server before configuration, and then after configuring the master and slave, the data will be synchronized.

Applications:

The slave server acts as a backup server for data. When the server is under great pressure, you can use master-slave servers to separate read and write traffic to reduce the pressure on the server. Cross-region website optimization

How to create a message board function, taking into account the architectural ideas optimized for different regions?

The above is all the knowledge points about the principles of MYSQL master-slave replication. Thank you for your learning and support for 123WORDPRESS.COM.