Configuration of Dual-machine hot standby for MySQL database and dual-machine hot standby for mysql database

Configuration of Dual-machine hot standby for MySQL database and dual-machine hot standby for mysql database

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. Achieve hot backup of mysql database.

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.

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.

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.

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

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

Mysql> show master status \ G;

+ --------------- + ---------- + -------------- + ------------------ +

| 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.

Mysql> unlock tables;

4. Set slave server

A. First set the Database Configuration File

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.

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.

Mysql> slave start;

Check the Database Synchronization status. If synchronization is successful, congratulations!

View the status of the Master/Slave Server

Mysql> show processlist \ G // 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

Mysql> flush tables with read lock;

2. Stop the slave from the database

Mysql> slave stop;

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

Show master status;

+ --------------- + ---------- + -------------- + ------------------ +

| File | Position | Binlog_do_db | Binlog_ignore_db |

+ --------------- + ---------- + -------------- + ------------------ +

| Louis-bin.001 | 79 | mysql |

+ --------------- + ---------- + -------------- + ------------------ +

4. Unlock the master database lock

Mysql> unlock tables;

5. Update master database information from the database

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

Mysql> slave start;