Mysql5.6 master-slave hot standby configuration, mysql5.6 master-slave Configuration

Mysql5.6 master-slave hot standby configuration, mysql5.6 master-slave Configuration

Database is the core of the application system. To ensure database security, it is common to adopt master-slave Hot Standby, that is, the DDL and DML of the master database will be synchronized to the slave database.

I. experiment environment

Operating System: windowsserver 2008 R2

Database: mysql-advanced-5.6.21-winx64

2. Preparations

1. Back up the database instance to be synchronized from the primary database, and then manually import the database to ensure that the master and slave databases are consistent during database synchronization.

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

3. Modify the master database

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

If the ini file name is a my-default.ini, change it to my. ini.

[Mysqld] # [required] unique server ID. The default value is 1, generally, the last segment of the IP address server_id = 41 # [required] enables binary log log_bin = mysql-bin # names of the databases to be backed up multiple databases separated by commas (,) binlog-do-db = test # if you need to configure and synchronize functions or stored procedures, otherwise, an exception occurs in the master and slave databases. log_bin_trust_function_creators = TRUE cannot be synchronized.

4. Modify the slave database slave

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

If the ini file name is a my-default.ini, change it to my. ini.

[Mysqld] # [required] unique server ID. The default value is 1. Generally, the last segment of the IP address is server_id = 44.

5. Restart the database

Stop Service. net stop mysql start service. net start mysql

6. Create an account in the primary database and authorize slave

grant replication slave on *.* to 'tantuls'@'%' identified by '123456';

Generally, the root account is not used. "%" indicates that all clients may be connected. If the account and password are correct, the specific client IP address can be used here, such as 192.168.1.44, to enhance security.

Check Authorization result select * from user where host = '%' and user = 'tantuls' \ G;

If Repl_slave_priv is Y, authorization is successful.

7. log on to the master database and view the master Status

show master status;

Note: Do not operate MYSQL on the master server after this step is completed to prevent the status of the master server from changing.

8. Configure slave 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 the server Load balancer instance is in the starting state, run the stopslave command to stop the instance, and then run the changemaster... Configure the slave and run start slave to start the slave.

9. Check the status of the slave database replication function

show master status;

Both of the following values must be yes.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

10. Verification

Create a database table tt in the master database test instance, and check whether a table named tt has been created in the corresponding database instance.

Table creation for the master database:

The slave database detection table is synchronized: