How to realize the configuration of two-machine hot standby in MySQL database

1, the MySQL database does not have an incremental backup mechanism, when the amount of data 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 in the primary database at the same time to the backup database. To achieve a hot backup of the MySQL database.

2, to realize the dual-machine hot standby first to understand the requirements of the master and slave database server version. To achieve a hot standby version of MySQL is higher than 3.2, there is also a basic principle is that the database version from the database can be higher than the primary server database version, but not lower than the primary server version of the database.

3, set the primary database server

A. First see if the version of the primary server is a backup-hot version. Then look at the configuration of the MYSQLD configuration block in My.cnf (Unix-like) or My.ini (Windows) there is no Log-bin (logging database change log), because the MySQL replication mechanism is based on the log replication mechanism, so the primary server must support the change log. Then set up the database to write to the log or do not write to the log. This will only make changes to the database that you are interested in writing to the database log.

Server-id=1//Database ID This should default to be 1 without changing the name of the Log-bin=log_name//log file, where you can make a log to another directory if you do not set a log name for the default host name Binlog-do-db=db_name Log database Binlog-ignore-db=db_name//logs are not logged

If there are more than one database with "," split open

Then set the user account for the synchronization database

Mysql> GRANT REPLICATION SLAVE on *.*-> to ' repl ' @ '%.mydomain.com ' identified by ' slavepass ';

4.0.2 previous versions, because replication is not supported by using the following statement to implement this feature

Mysql> GRANT FILE on *.*-> to ' repl ' @ '%.mydomain.com ' identified by ' slavepass ';

Restart the database after setting up the configuration file for the primary server

B. Locking an existing database and backing up current data

Lock Database

Mysql> FLUSH TABLES with READ LOCK;

There are two ways to back up a database one way is to go directly to MySQL's data directory and then pack the folders where you need to back up the database, and the second is to use mysqldump to back up the database but add the "--master-data" parameter, It is recommended that you use the first method to back up your database

C. View the status of the primary server

Mysql> Show Master STATUSG; +---------------+----------+--------------+------------------+ | File | Position | binlog_do_db | binlog_ignore_db | +---------------+----------+--------------+------------------+ | mysql-bin.003 | About Us | Test | Manual,mysql | +---------------+----------+--------------+------------------+

Record the values of the file and Position items for later use.

D. Then open the lock on the database

Mysql> UNLOCK TABLES;

4, set from the server

A. Set up the configuration file for the database first

Server-id=n//Set database ID The default primary server is 1 can be set casually but if there are more than one from the server cannot repeat. Master-host=db-master.mycompany.com//Primary server IP address or domain name master-port=3306//Primary database port number Master-user=pertinax//sync database user Master-password=freitag//Sync Database Password master-connect-retry=60//If the master server is found broken from the server, the time difference of reconnection report-host= db-slave.mycompany.com//reporting the wrong server

B. Importing databases backed up from the primary database server to the server

C. Then start from the database server, if the start of the time did not add "--skip-slave-start" This parameter is entered into MySQL

mysql> slave stop; Stop the Slave service

D. Setting various parameters for the primary server

mysql> Change Master to-> master_host= ' master_host_name ',//Primary server IP address-> master_user= ' replication_user_name ' ,//Sync database user-> master_password= ' replication_password ',//sync database password-> master_log_file= ' Recorded_log_file_name ' ,/////master server binary log file name (previously required to remember parameters)-> master_log_pos=recorded_log_position; The starting position of the log file (parameters that are required to remember earlier)

E. Start a synchronization database thread

mysql> slave start;

Check the synchronization of the database. If you can sync successfully, congratulations!

View the status of the master-slave server

Mysql> Show PROCESSLISTG//Can view the MySQL process to see if there is a listening process

If the log is too large to clear the 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 log file position of the primary database

Show master status; +---------------+----------+--------------+------------------+ | File | Position | binlog_do_db | binlog_ignore_db | +---------------+----------+--------------+------------------+ | louis-bin.001 | In the same | | MySQL | +---------------+----------+--------------+------------------+

4. Unlock the main database lock

mysql> unlock tables;

5. Update information from the primary database in the database

mysql> Change Master to-> master_host= ' master_host_name ',//Primary server IP address -> master_user= ' replication _user_name ',//sync database user -> master_password= ' Replication_password ',//sync database password -> master_log_file= ' Recorded_log_file_name ',///The file name of the primary server binary log (the parameters previously required to remember) -> master_log_pos=recorded_log_position;//The start of the log file ( Parameters required to remember earlier)

6. Start the slave from the database

mysql> slave start;