Mysql5.6 master/slave hot standby configuration

Database is the core of the application system, in order to ensure the security of the database using master-slave hot standby is a very common method, that is, the primary database DDL, DML will be synchronized to the slave database.

First, the experimental environment

Operating system: WindowsServer R2

Database: mysql-advanced-5.6.21-winx64

Ii. preparatory work

1, the need to synchronize the database instance from the primary database back up, and then manually import from the database to ensure that the database synchronization when the master-slave database is consistent.

2. Configure the firewall of the operating system where the primary database is located, open the database access port, and the default port 3306.

Third, modify the master database master

C:\soft\mysql-advanced-5.6.21-winx64\my.ini

If the INI file name is My-default.ini modified to My.ini.

[Plain]View PlainCopy 

1. [Mysqld]
2. #[must] server unique ID, default is 1, usually take IP last paragraph
3. server_id = 41
4. #[must] enable binary logging
5. Log_bin=mysql-bin
6. #需要备份的数据库名 multiple libraries separated by commas
7. Binlog-do-db =test
8. #若涉及及同步函数或者存储过程需要配置, otherwise the master will produce an exception that cannot be synchronized
9. Log_bin_trust_function_creators=true

Iv. modification from the database slave

C:\soft\mysql-advanced-5.6.21-winx64\my.ini

If the INI file name is My-default.ini modified to My.ini.

[Plain]View PlainCopy 

1. [Mysqld]
2. #[must] server unique ID, default is 1, usually take IP last paragraph
3. server_id = 44

V. Restart the database [Plain]View PlainCopy  

1. Stop Service
2. net stop MySQL
3. Start the service
4. net start MySQL

Establish an account in the main database and authorize slave [SQL]View PlainCopy 

1. Grant replication Slave on * * to ' tantuls ' @'% ' identified by ' 123456 ';

Generally do not use the root account, "%" means that all clients may be connected, as long as the account, the password is correct, here can be specific client IP instead, such as 192.168.1.44, enhance security.

[SQL]View PlainCopy 

1. Test Authorization Results
2. SELECT * from user where host='% ' and user=' tantuls ' \g;

If the Repl_slave_priv item is y, the authorization succeeds.

Vii. log in to the master database to view the master status

[SQL]View PlainCopy 

1. Show master status;

Note: Do not operate the master server MySQL again after performing this step to prevent the change of the primary server state value

VIII. configuration from the database slave

[SQL]View PlainCopy 

1. mysql> Change Master to
2. master_host=' 192.168.1.41 ',
3. master_user=' Tantuls ',
4. master_password=' 123456 ',
5. master_log_file=' mysql-bin.000004 ',
6. master_log_pos=326;

If slave is in the boot state, execute the stopslave command to stop and then execute changemaster to ... Configure slave, and finally execute start slave start slave.

Ix. Detecting the status of replication from the database

[SQL]View PlainCopy  

1. Show master status;

All of the following two items must be yes.

Slave_io_running:yes

Slave_sql_running:yes

X. Verification

Create a database table TT in the primary database test DB instance to see if a table named TT has been created under the corresponding DB instance from the database execution.

Main Library Build Table:

The check table from the library is synchronized:

Mysql5.6 master/slave hot standby configuration