The configuration method of dual-computer hot standby in Mysql database _mysql

1. MySQL database does not have the mechanism of incremental backup, when the amount of data is too large to 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. In order to realize the hot standby of two machines, you must first 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. To set up 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 be the default is 1 do not have to change
Log-bin=log_name//log file name, where you can create a log to another directory if not set then a log name for the default host name
Binlog-do-db=db_name//Log database
Binlog-ignore-db=db_name//Log Database 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 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 for later use.
D. Then open the lock on the database
Mysql> UNLOCK TABLES;
4. Settings 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//Main database port number
Master-user=pertinax//Sync database users
Master-password=freitag//Sync Password for database
MASTER-CONNECT-RETRY=60//If the primary server is found to be disconnected from the server, the time difference between 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 ',//IP address of the primary server
-> master_user= ' replication_user_name ',//Synchronizing database users
-> master_password= ' Replication_password ',//Sync database Password
-> master_log_file= ' recorded_log_file_name ',///The file name of the primary server binary log (parameters previously required to remember)
-> 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 Processlist\g//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 | 79 | | 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 ',//IP address of the primary server
-> master_user= ' replication_user_name ',//Synchronizing database users
-> master_password= ' Replication_password ',//Sync database Password
-> master_log_file= ' recorded_log_file_name ',///The file name of the primary server binary log (parameters previously required to remember)
-> master_log_pos=recorded_log_position; The starting position of the log file (parameters that are required to remember earlier)
6. Start the slave from the database
mysql> slave start;