How to implement dual-machine hot standby for Mysql database

This article describes how to implement dual-machine hot standby configuration for Mysql databases. If you need it, refer to it.

This article describes how to implement dual-machine hot standby configuration for Mysql databases. If you need it, refer to it.

1. Mysql databases do not have an incremental backup mechanism. When the data volume is too large, backup is a big problem. Fortunately, the mysql database provides a master-slave backup mechanism, which is to write all the data of the master database to the backup database at the same time.

2. To achieve dual-machine Hot Standby, you must first understand the master-slave database server version requirements. To enable Hot Standby mysql versions, both must be higher than 3.2. Another basic principle is that the database version of the slave database can be higher than that of the master server database, but it cannot be earlier than the database version of the master server.
3. Set the master database server:
A. Check whether the master server version supports hot standby. View my. cnf (unix-like) or my. in ini (windows), does the configuration of mysqld configuration block have log-bin (Record Database Change logs), because mysql replication mechanism is log-based replication mechanism, therefore, the master server must support Log changes. Set the database to which logs are to be written or the database to which logs are not to be written. In this way, only changes to the database you are interested in are written into the database log.

The Code is as follows:
Server-id = 1 // Database id. The default value is 1 and no changes are required. Log-bin = log_name // The name of the log file. You can specify a log name for the default host name if it is not set to another directory. Binlog-do-db = db_name // The database that records logs Binlog-ignore-db = db_name // database without logs

If multiple databases exist, separate them ","
Set the user account for database synchronization.

The Code is as follows:
Mysql> grant replication slave on *.* -> TO 'repl' @ '% .mydomain.com' identified by 'slavepass ';

In versions earlier than 4.0.2, because REPLICATION is not supported, use the following statement to implement this function.

The Code is as follows:
Mysql> grant file on *.* -> TO 'repl' @ '% .mydomain.com' identified by 'slavepass ';

Set the configuration file of the master server and restart the database.
B. Lock existing databases and back up existing data
Lock Database

The Code is as follows:
Mysql> flush tables with read lock;

There are two ways to back up a database: directly go to the mysql data DIRECTORY And package the folder where you want to back up the database, the second method is to use mysqldump to back up the database, but the "-- master-data" parameter must be added. We recommend that you use the first method to back up the database.
C. view the status of the master server

The Code is as follows:
Mysql> show master statusG; + --------------- + ---------- + -------------- + ------------------ + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + --------------- + ---------- + -------------- + ------------------ + | Mysql-bin.003 | 73 | test | manual, mysql | + --------------- + ---------- + -------------- + ------------------ +

Record the values of the File and Position items, which will be used later.
D. Lock the database and open it.

The Code is as follows:
Mysql> unlock tables;

4. Set slave server
A. First set the Database Configuration File

The Code is as follows:

Server-id = n // set the database id. The default master server is 1, which can be set at will, but it cannot be repeated if there are multiple slave servers. Master-host = db-master.mycompany.com // ip address or domain name of the master server Master-port = 3306 // The port number of the master database Master-user = pertinax // the user who synchronizes the database Master-password = freitag // password for Database Synchronization Master-connect-retry = 60 // time difference between the reconnection if the master server is disconnected from the slave server Report-host = db-slave.mycompany.com // server that reports errors

B. Import the database backed up from the master database server to the slave server.
C. start the slave database server. If the "-- skip-slave-start" parameter is not added during startup, the server enters mysql.
Mysql> slave stop; // stop the slave Service
D. set parameters of the master server.

The Code is as follows:

Mysql> CHANGE MASTER -> MASTER_HOST = 'master _ host_name ', // ip address of the master server -> MASTER_USER = 'replication _ user_name ', // Database Synchronization user -> MASTER_PASSWORD = 'replication _ password', // password for Database Synchronization -> MASTER_LOG_FILE = 'recorded _ log_file_name ', // File Name of the binary log of the master server (the parameter to be remembered earlier) -> MASTER_LOG_POS = recorded_log_position; // the start position of the log file (the parameters that need to be remembered earlier)

E. Start the Database Synchronization thread.

The Code is as follows:
Mysql> slave start;

Check the Database Synchronization status. If synchronization is successful, congratulations!
View the status of the Master/Slave Server

The Code is as follows:
Mysql> show processlistg // you can check the mysql process to see if there are any listening processes.

To clear a large log, follow these steps:
1. Lock the primary database

The Code is as follows:
Mysql> flush tables with read lock;

2. Stop the slave from the database

The Code is as follows:
Mysql> slave stop;

3. view the log file name and position of the log file of the master database.

The Code is as follows:
Show master status; + --------------- + ---------- + -------------- + ------------------ + | File | Position | Binlog_do_db | Binlog_ignore_db | + --------------- + ---------- + -------------- + ------------------ + | Louis-bin.001 | 79 | mysql | + --------------- + ---------- + -------------- + ------------------ +

4. Unlock the master database lock

The Code is as follows:
Mysql> unlock tables;

5. Update master database information from the database

The Code is as follows:

Mysql> CHANGE MASTER -> MASTER_HOST = 'master _ host_name ', // ip address of the master server -> MASTER_USER = 'replication _ user_name ', // Database Synchronization user -> MASTER_PASSWORD = 'replication _ password', // password for Database Synchronization -> MASTER_LOG_FILE = 'recorded _ log_file_name ', // File Name of the binary log of the master server (the parameter to be remembered earlier) -> MASTER_LOG_POS = recorded_log_position; // the start position of the log file (the parameters that need to be remembered earlier)

6. Start the slave from the database

The Code is as follows:
Mysql> slave start;