MySQL replication, mysql master-slave Replication

MySQL replication, mysql master-slave Replication

Replication refers to the upload of DDL and DML operations in the primary database to the replication server (also called slave server) through binary logs, and then re-Execute (also called redo) these logs on the server ), in this way, the data in the database of the slave server and the master server is synchronized.
MySQL supports copying data from one primary server to multiple slave servers at the same time. The slave server can also be used as the master server of other servers.
Advantages of replication:
 
 

  
  
If a problem occurs on the master server, you can quickly switch to the slave server to provide services;
  
  
The query operation can be performed on the server to reduce the access pressure on the master server.
  
  
Backup can be performed on the slave server to avoid affecting the services of the master server during the backup.
  
  
Because MySQL implements asynchronous replication, there are some differences between servers. data with high real-time requirements still needs to be obtained from the master server.
 
 

1. Install the configuration

Copy steps:

1. Ensure that databases of the same version are installed on the master and slave servers;

2. Set an account for replication on the master server and grant the replication slave permission;

3. modify the configuration file on the master server and set the server-id value.

4. On the master server, set the read lock to be valid. This operation ensures that there are no database operations for consistent snapshots:

flush tables with read lock;

5. Obtain the current binary log name and offset on the master server. The command for this operation is to restore data on this point after the database is started.

6. Back up the files in the primary database. You can refer to MySQL backup;

7. After the backup is complete, the master database can restore the write operation and execute "unlock tables ";

8.recover the consistency of the primary data warehouse to the Data Warehouse. If you use a .tar package, you only need to unbind it to the corresponding directory;

9. modify the configuration file of the slave database and add the server_id parameter. Note that the server_id parameter must be unique;

10. On the slave server, use the -- skip-slave-start option to start the slave database. This will not immediately start the replication process from the database service, so that we can further configure the slave Database Service;

11. Set the slave database server to specify the users used for replication, the IP address and port number of the master database server, and the log file to be copied. The syntax is as follows:

mysql > change master to-> master_host='master_host_name',-> master_user='replication_user_name',-> master_password='replication_password',-> master_log_file='record_log_file_name',-> master_log_pos='recorded_log_position';

 

12. start the slave thread from the server: start slave

13. The Server Load balancer is connected to the master and starts to accept and execute logs;

14. Update the data of the primary database, and the data in the slave database will also change.

 

Several other common replication startup options

Log-slave-updates is used to configure whether to write binary logs for update operations on the slave server. It is disabled by default. However, if the slave server is used as the master server of another server, to create a chain copy, you must enable this option;

Master-connect-retry is used to set the retry Interval when the connection to the master server is lost. The default value is 60 s.

Read-only allows the slave server to only accept updates from the Super User, thus limiting the update operations of application errors on the slave server.

Database or table to be copied

You can use replicate-do-db, replicate-do-table, replicate-ignore-db, replicate-ignore-table to specify the databases or tables that are copied from the primary database to the slave database,

 

2. Routine Management and Maintenance

1. view the slave server status: show slave status, which focuses on the information of two parameters:

Slave_IO_Running: This process reads binlog logs from the master server and writes them to the relay logs of the slave server;

Slave_ SQL _Running: Reads and executes BINLOG logs in the relay log.

As long as one of the statuses is no, the replication process is stopped. The error cause can be seen from "last_Error ".

2. synchronous maintenance of Master/Slave servers

In some busy OLTP (online transaction processing), because the master server is updated frequently, the update speed of slave server is slow due to various reasons, therefore, you need to regularly synchronize the data of the master-slave service.

Common Methods: when the load is low, the update of the master server is temporarily blocked, and the Master/Slave server is forced to update and synchronize;

Run the following command on the master server:

flush tables with read lock;show master status;+-------------------+----------+--------------------+--------------------+|       file        | position |  binlog_do_db      | binlog_ignore_db   |+-------------------+----------+--------------------+--------------------+| mysql-bin.00039   |  974     |                    |                    |+-------------------+----------+--------------------+--------------------+

 

Records the log name and offset output by the show statement,

Run the following statement on the server.Master_pos_wait ()The function parameter is the preceding coordinate value.

select master_pos_wait('mysql-bin.000039', '974');

 

This select statement will be blocked until it reaches the specified log file and offset from the service. 0 is returned, indicating master-slave synchronization.

 

Sets the update statement for skipping the master server from the slave server.:
Set global SQL _slave_skip_counter = n where n is 1 or 2. If auto-increment or last_insert_id () is not used for updates on the autonomous server, n should be 1; otherwise, 2
Processing of log event entry exceeded max_allowed_packed
If a large blog column or long string is used, the preceding "log event entry exceeded max_allowed_packed" error occurs because records containing large texts cannot be transmitted over the network, the final solution is to increase the size of the max_allowed_packet parameter.
Set @ global. max_allowed_packet = 16777216;
　　View the assignment progress of the slave server
The time of the slave_ SQL _running thread in the show processlist list is worth the time. It records the difference between the SQL timestamp currently executed by the server and the system time.

3. Switch Master/Slave servers

Assume that one master server (M) and two slave servers (S1 and S2) direct to the master database server (M) at the same time. When the master database (M) fails, you need to switch one slave server to the master database server, at the same time, direct another slave server to the new master server as follows:

1. first, ensure that all slave databases have performed all updates in the relay log, execute stop slave io_thread on each slave server, and then show processlist output, if the status is has read all relay log, the update is complete;

2. on the slave database (S1), execute stop slave to stop the service, and then reset the reset master to the master database.

Stop slave;

Reset master;

3. Execute stop slave on S2 to stop the service, and then execute change master to master_host = 's1' to reset the master database.

4. Delete the master.info and relay-log.info files on the new master database server, otherwise the slave server will be started at the next startup.

 

Summary

Replication is a common function in MySQL databases. It can effectively ensure the security of the primary database, reduce the backup pressure on the primary database, and share the query pressure.