Mysql database hot standby configuration

1. The mysql database does not have an incremental backup mechanism. Backup is a big problem when the amount of data is too large. Fortunately, the mysql database provides a master-slave backup mechanism, in fact, all the data of the primary database is written to the backup database at the same time. Implement a hot backup of the mysql database.

2. In order to achieve hot standby of dual-machine, we must first understand the requirements of the version of the master-slave database server. To achieve hot standby mysql version is higher than 3.2, there is a basic principle is that the database version from the database can be higher than the version of the main server database, but can not be lower than the database version of the main server. 3. Set the primary database server: a. First check if the version of the primary server is a hot standby version. Then check my.cnf (class unix) or my.ini (windows) configuration of the mysqld configuration block has no log-bin (record database change log), because the mysql replication mechanism is based on the log replication mechanism, so the primary server must To support the change log. Then set the database to be written to the log or the database that you do not want to write to the log. This way only changes to the database you are interested in are written to the database's log.

server-id=1 //The id of the database should be 1 by default without changing the log-bin=log_name //the name of the log file. Here you can make a log to another directory. If it is not set, the default host. Name of a log binlog-do-db=db_name //Logging database binlog-ignore-db=db_name //Do not log the database above If there are multiple databases with "," split and then set the synchronization database User account mysql> GRANT REPLICATION SLAVE ON *.*-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; prior to 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'; restart the database after setting the configuration file of the primary server b. Lock the existing database and Back up the current data lock database mysql> FLUSH TABLES WITH READ LOCK; there are two ways to back up the database is to go directly to the mysql data directory and then package the folder you need to back up the database, the second is to use mysqldump To back up the database but add the "--master-data" parameter, it is recommended to use the first method to back up the database c. View the status of the main 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. Then open the database lock mysql > UNLOCK TABLES; 4. Set from the server a. First set the database configuration file server-id = n //set the database id default primary server is 1 can be set casually but if there are multiple slave servers can not be repeated. Master-host=db-master.mycompany.com //The IP address or domain name of the primary server master-port=3306 //The port number of the primary database master-user=pertinax //The user of the synchronization database master-password=freitag //Synchronize the password of the database master-connect-retry=60 //If the host server is found to be disconnected from the server, the time difference of reconnection is report-host=db-slave.mycompany.com //Report the wrong server b. Put the server from the primary database The backup database is imported to the slave server c. Then start from the database server. If the "--skip-slave-start" parameter is not added at startup, it will enter mysql mysql> slave stop; //stop the slave Service d. Set various parameters of the main server mysql> CHANGE MASTER TO-> MASTER_HOST='master_host_name', //IP address of the primary server-> MASTER_USER='replication_user_name', //User of the synchronization database-> MASTER_PASSWORD = 'replication_password', //​​Synchronize database password -> MASTER_LOG_FILE = 'recorded_log_file_name', //​​file name of the main server binary log (parameters required to remember) -> MASTER_LOG_POS=recorded_log_position; //Start position of the log file (parameters that need to be remembered before) e. Start the thread of the synchronization database mysql> slave start; check the synchronization of the database. Congratulations if you can successfully synchronize! View the status of the master and slave servers mysql> SHOW PROCESSLIST\\G //can view the mysql process to see if there is a monitoring process if the log is too large to clear the log steps as follows 1. Lock the main database mysql> FLUSH TABLES WITH READ LOCK; Stop the slave mysql from the database. slave stop; 3. View the log file name of the primary database and the position show master status of the log file; +---------------+----- -----+--------------+------------------+ |  File |  Position |  Binlog_do_db |  Binlog_ignore_db |  +---------------+----------+--------------+------- -----------+ |  Louis-bin.001 |  79 |  |  Mysql |  +---------------+----------+--------------+------- -----------+ 4. Unlock the lock of the main database mysql> unlock tables; 5. Update the information of the main database from the database mysql> CHANGE MASTER TO -> MASTER_HOST='master_host_name', //Primary server IP address-> MASTER_USER='replication_user_name', //Synchronize database user-> MASTER_PASSWORD='replication_password', //Sync database password-> MASTER_LOG_FILE='recorded_log_file_name', //Main server binary The file name of the log (parameters that need to be remembered before) -> MASTER_LOG_POS=recorded_log_position; //The starting position of the log file (in front of 6. Start slave mysql from the database) slave start;