Configuration method of dual-machine hot standby for Mysql database

Configuration method of dual-machine hot standby for Mysql database

The MySQL database does not have a mechanism for incremental backups, which is a big problem when the amount of data is too large. Fortunately, MySQL database provides a mechanism of master-slave backup, in fact, all the data of the primary database is written to the backup database at the same time.

1. The MySQL database does not have a mechanism for incremental backups, which is a big problem when the amount of data is too large. Fortunately, MySQL database provides a mechanism of master-slave backup, 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. To realize the dual-machine hot-standby first of all to understand the master and slave database server version requirements. 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 version of the primary server database, but not lower than the primary server version of the database. 
3. To set up the primary database server: 
A. First look at whether the version of the primary server is a hot-standby version. Then look at my.cnf (Unix-like) or My.ini (Windows) mysqld configuration block configuration There is no Log-bin (log database change log), because the replication mechanism of MySQL is a log-based replication mechanism, so the primary server must support the change log to line. Then set up the database to write to the log, or do not write to the log. This only changes to the database that you are interested in will be written to the log in the database. 
Server-id=1//Database ID This should be 1 by default, no change. 
Log-bin=log_name//log file name, 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//Logging Database 
Binlog-ignore-db=db_name//log-not-logged database 
Above if there are multiple databases with "," split open 
Then set the user account to synchronize the database 
Mysql> GRANT REPLICATION SLAVE on *. * 
-Repl ' @ '%.mydomain.com ' identified by ' slavepass '; 
4.0.2 previous versions, because replication is not supported to use the following statement to implement this function 
Mysql> GRANT FILE on *. * 
-Repl ' @ '%.mydomain.com ' identified by ' slavepass '; 
Restart the database after you have set up the primary server's configuration file 
B. Lock the existing database and back up the current data 
Lock Database 
Mysql> FLUSH TABLES with READ LOCK; 
Back up the database there are two ways to go directly to the MySQL data directory and then package you need to back up the database folder, the second is to use the mysqldump way to back up the database but to add "--master-data" this parameter, We recommend that you use the first method to back up the database 
C. Viewing the status of the primary server 
Mysql> Show Master Status\g; 
+---------------+----------+--------------+------------------+ 
| File | Position | binlog_do_db | binlog_ignore_db | 
+---------------+----------+--------------+------------------+ 
| mysql-bin.003 | 73 | Test | Manual,mysql | 
+---------------+----------+--------------+------------------+ 
Records the values of the file and Position items, which are to be used later. 
D. Then open the database lock 
Mysql> UNLOCK TABLES; 
4. Set from server 
A. Set up the database configuration file first 
Server-id=n//Set database ID The default master server is 1 and can be set casually but cannot be duplicated if there are multiple slave servers. 
IP address or domain name of the master-host=db-master.mycompany.com//master server 
master-port=3306//port number of the primary database 
Master-user=pertinax//Synchronizing the database user 
Master-password=freitag//Synchronizing the password of the database 
MASTER-CONNECT-RETRY=60//If the primary server is found to be disconnected from the server, the time lag for reconnection 
report-host=db-slave.mycompany.com//reporting the wrong server 
B. Importing a database backed up from the primary database server to the slave server 
C. Then start from the database server, if you start without adding "--skip-slave-start" This parameter will go into MySQL 
mysql> slave stop; Stop the Slave service 
D. Setting various parameters of the master server 
Mysql> Change MASTER to 
Master_host= ' master_host_name ',//IP address of the primary server 
-master_user= ' replication_user_name ',//users synchronizing the database 
-master_password= ' Replication_password ',//password for synchronizing the database 
Master_log_file= ' recorded_log_file_name ',//The file name of the primary server binary log (the parameters that are required to be remembered earlier) 
Master_log_pos=recorded_log_position; The starting position of the log file (the parameters that you need to remember earlier) 
E. Starting a thread that synchronizes the database 
mysql> slave start; 
Check the synchronization of the database. If you can synchronize successfully then congratulations! 
View the status of the master and slave servers 
Mysql> Show Processlist\g//Can see the MySQL process to see if there is a listening process 
If the log is too large, the steps to clear the log are as follows 
1. Lock the primary database 
Mysql> FLUSH TABLES with READ LOCK; 
2. Stop slave from the database
mysql> slave stop; 
3. View the log file name of the primary database and the position of the log files 
Show master status; 
+---------------+----------+--------------+------------------+ 
| File | Position | binlog_do_db | binlog_ignore_db | 
+---------------+----------+--------------+------------------+ 
| louis-bin.001 | 79 | | MySQL | 
+---------------+----------+--------------+------------------+ 
4. Unlock the primary database lock 
mysql> unlock tables; 
5. Update information from the primary database in the database
Mysql> Change MASTER to 
Master_host= ' master_host_name ',//IP address of the primary server 
-master_user= ' replication_user_name ',//users synchronizing the database 
-master_password= ' Replication_password ',//password for synchronizing the database 
Master_log_file= ' recorded_log_file_name ',//The file name of the primary server binary log (the parameters that are required to be remembered earlier) 
Master_log_pos=recorded_log_position; The starting position of the log file (the parameters that you need to remember earlier) 
6. Start the slave from the database
mysql> slave start;

Configuration method of dual-machine hot standby for Mysql database