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.

[mysqld]#[must] server unique ID, default is 1, generally take IP last paragraph server_id = 41 #[must] enable binary log log_bin=mysql-bin# the database name  to be backed up Multiple libraries are separated by commas binlog-do-db =test# if a synchronization function or a stored procedure needs to be configured, the master will produce an exception that cannot be synchronized 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.

[mysqld]#[must] server unique ID, default is 1, generally take IP last paragraph server_id = 44

V. Restart the database

Stop service net  stop  MySQL startup service net  start  MySQL

Establish an account in the main database and authorize slave

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.

Detection authorization Result 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

Show master status;

Note: Do not re-operate the primary server after you have completed this stepMYSQLto prevent changes in the state value of the primary server

VIII. configuration from the database slave

mysql> Change Master to master_host= ' 192.168.1.41 ', master_user= ' tantuls ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000004 ', 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

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