Description of real-time backup of MySql database from Synchronous Server Load balancer

If you are concerned about the real-time backup of the MySql database master-slave synchronization server Load balancer, the actual operation steps are very depressing. You can browse the following article. On a reputable website, I found a real-time backup operation for MySql database master-slave synchronization server Load balancer. For your sharing.

Recently, four MySQL database servers have been configured in master-slave mode to achieve load balancing. It seems acceptable, at least there is no major problem.

The MySQL synchronization mechanism records all database updates and deletions Based on the master node in binary logs. Therefore, to enable the synchronization mechanism, binary logs must be enabled on the master node. Each slave receives an update operation recorded in the binary log on the master. Therefore, a copy of this operation is executed on the slave. It should be very important to realize that binary logs only record update operations at the beginning of enabling binary logs. All slave data must be copied from the master when binary logs are enabled. If the data on the slave is inconsistent with that on the master node when binary logs are enabled during synchronization, the slave synchronization will fail.

One of the methods to copy DATA on the master is to execute the load data from master Statement on slave. However, you must note that load data from master is available only after MySQL 4.0.0, and only MyISAM tables on the master are supported. Similarly, this operation requires a global read lock, so that no update operation will be performed on the master when the log is transferred to the slave.

When the free lock table hot backup is implemented (in MySQL 5.0), the global read lock is unnecessary. Because of these restrictions, we recommend that you run the load data from master statement only when the DATA on the MASTER is small, or allow a long read lock on the master. Since each system loads data from the MASTER at different speeds, a good measurement rule is that 1 MB of DATA can be copied per second. This is just a rough estimate, but the master and slave are both Pentium 100 MHz machines and can achieve this speed when connected with a MBit/s network.

After the master data has been fully copied on the slave, you can connect to the master and wait for updates. If the master is disconnected from the server or slave, slave periodically tries to connect to the master until it can be reconnected and waits for updates. The retry Interval is controlled by the-master-connect-retry option. The default value is 60 seconds. Each slave records the log location when it is disabled. Msater does not know how many slave connections are established or when the slave is updated.

The MySQL Database Synchronization function is implemented by three threads (one on the master node and two on the slave node. After the start slave statement is executed, slave creates an I/O thread. The I/O thread connects to the master and requests the master to send statements in binary logs. The master creates a thread to send the log content to the slave. The Binlog Dump thread in the result of executing the show processlist Statement on the master is.

The I/O thread on the slave reads the statements sent by the Binlog Dump thread of the master and copies them to the relay logs in the data directory. The third is the SQL thread, which salve uses to read relay logs and then execute them to update data. As mentioned above, each mster/slave has three threads. Each master has multiple threads. It creates a thread for each slave connection, and each slave has only I/O and SQL threads.

Before MySQL 4.0.2, only two threads (one master and one slave) are required for synchronization ). The I/O and SQL threads on the slave are merged into one. It does not use relay logs. The advantage of using two threads on slave is to split the read log and execution into two independent tasks. If the task is executed slowly, the log reading task will not be slowed down.

For example, if slave is stopped for a period of time, the I/O thread can quickly read all the logs from the master after the slave is started, although the SQL thread may lag behind the I/O thread for several hours. If slave stops running all the SQL threads, but the I/O thread has read all the update logs and saved them in the local relay log, therefore, after the slave is started again, it will continue to execute them. This allows you to clear the binary log on the master, because the slave does not need to read the Update log from the master. The execution of the show processlist statement will tell us what happens on the master and slave. The following is the specific configuration.

1. Create a user for the slave server on the master server:

Grant replication slave on *. * to 'username '@ 'host' identified by 'Password'; replace replication slave with the FILE permission before MySQL database 4 dot 0.2)

If you want to execute the load table from master or load data from master Statement on slave, you must grant additional permissions to this account:
Grant global SUPER and RELOAD permissions.

Grant SELECT permission to all tables to be loaded. Any table on the master without the SELECT permission will be skipped by the load data from master.

2. Edit the configuration file of the master server:/etc/my. cnf

Server-id = 1

Log-bin

Binlog-do-db = Name of the database to be backed up. If multiple databases are backed up, set this option again.

Binlog-ignore-db = databases that do not need to be backed up suffer. If multiple databases are backed up, set this option again.

3. Edit the slave server configuration file:/etc/my. cnf

Server-id = 2 When configuring multiple slave servers, set the id number in sequence)

Master-host = host

Master-user = user Name

Master-password = password

Master-port = port

Replicate-do-db = Name of the database to be backed up. If multiple databases are backed up, set this option again.

Remember to manually synchronize the databases to be backed up on the master and slave servers, and then restart the master and slave servers.

To verify whether the Master/Slave settings are successful, log on to the slave server and enter the following command:

 
 

  
  
mysql> show slave status\G 
 
 

The obtained list contains data similar to the following:

 
 

  
  
Slave_IO_State: Waiting for master to send event  
  
  
Slave_IO_Running: Yes  
  
  
Slave_SQL_Running: Yes 
 
 

If the two options below are not all "Yes", it indicates that the configuration in the previous step is incorrect.

If your settings are correct, try to insert several records on the master server, and then switch to the slave server, and you will find that the corresponding new records have been automatically synchronized.

If your Master/Slave server has been configured, make sure that all the insert, delete, and update operations are performed on the master server, then the corresponding data changes will be automatically synchronized to the slave server, so that we can share the select Operation with multiple slave databases, thus reducing the server load.

If you want to back up the database by copying data files, just press slave stop on the mysql database command line on the slave server, and then copy the database file, then, type slave start in the mysql command line to start the slave server. In this way, the data is backed up to ensure data integrity, and the mysql of the master server does not need to be stopped throughout the process.

Tip: If you have modified the configuration of the master server, remember to delete the master.info file on the slave server. Otherwise, the old configurations used by the slave server may cause errors.

Note: When you want to copy multiple databases, binlog-do-db and replicate-do-db options are set. If you want to back up multiple databases, you only need to set the corresponding options again.

For example:

 
 

  
  
binlog-do-db=a 
  
  
bbinlog-do-db=b  
  
  
replicate-do-db=a  
  
  
replicate-do-db=b  
 
 

Supplement:

Use show slave status on the slave server

If Slave_IO_Running is set to No, IO_THREAD is not started. Run slave start [IO_THREAD]

If Slave_ SQL _Running is No, a replication error occurs. Check the Last_error field to exclude the error and run slave start [SQL _THREAD].