MySQL 5.6 Dual Machine Hot standby

MySQL 5.6 Dual machine Hot standby

Directory:

1. Description

2. Manual synchronization of data

3. Modify the master database configuration file

4. Modify the configuration file from the database

5. Add a backup user to the primary database

6. Set from database to slave

7. Verification

1. Description

1) database version is more than 5.1

2) from the version of the database to >= the primary database server version

3) the tools used for database operations are: Navicat for MySQL

4) Primary Database ip:192.168.1.105

From the database ip:192.168.1.103

2. Manual synchronization of data

Objective: To ensure the same data for two databases before automatic synchronization.

1) The Master data (192.168.1.105) library exports the data to be backed up as follows:

　　

　　

2) Import the backup data from the database (192.168.1.103) to the primary database as follows:

　　

　　

3. Modify the master database configuration file

1) Locate the configuration file (host ip:192.168.1.105)

D:\ProgramData\MySQL\MySQL Server 5.6\my.ini

The configuration file for the installation version defaults to the Data folder, the name is My.ini ( Note: Not in the Programs folder, the configuration file in the program folder is named My-default.ini, and the modification does not work)

2) Modify the configuration file (preferably back up the original configuration file)

Left for the original file, right for the modified file

　　　　server_id = 105 (Set the last one to ask IP)

Log_bin=mysql-bin (binary log file name)

Binlog-do-db =childrendb (the name of the database to be backed up)

　　　　Log_bin_trust_function_creators=true (if the synchronization function or the stored procedure needs to be configured, the master will not be able to synchronize the exception)

As follows:

　　　　

3) Restart the server

　　　　

4. Modify the configuration file from the database

1) Locate the configuration file from the database (host ip:192.168.1.103)

2) Modify the configuration file

Left for the original file, right for the modified file

　　　　server_id = 103 (Set the last one to ask IP)

As follows:

　　　　

3) Restart the server

5. Add a backup user to the primary database

1) Select the Run SQL File window

　　

2) Run the command

*.   'tantuls '@'%'         World

Role: Only give this user the "master and slave copy" permission

As follows:

　　

3) This user is used to synchronize data from the server.

6. Set from database to slave

1) Query the log name and POS for the primary server (192.168.1.105):

Command line:

Show master status;

As follows:

　　　　

2) Open the Run command interface from the database server (192.168.1.103) and enter the following:

Command line:

Change MasterToMaster_host='192.168.1.105',
Master_user='tantuls', Master_password='world', Master_log_file ='mysql-log.000001', master_log_pos=331;       

Description

Master_host: IP address of the primary server
Master_user: Backup user set up by master server
Master_password: Primary server Backup user password
Master_log_file: Log name of the primary server
Master_log_pos: Location of the primary server

As follows:

　　

3) Turn on slave

Input command

Start slave;

7. Verification

1) Query from server

Command:

show slave status;

　　

2) After adding a table and data, verify that you are synchronizing to the slave server.

MySQL 5.6 Dual Machine Hot standby