How to configure MySQL master-slave replication under Windows

MySQL master-slave replication allows data from one database (the master database) to be replicated to one or more databases (the slave databases).
The master database is generally used for real-time business data writing and updating operations, while the slave database is mainly used for reading.

Master-slave replication process:

1. Any modification on the master server will be saved in the binary log through its own I/O tread (I/O thread).
2. An I/O thread is also started on the slave server. It connects to the master server with the configured username and password to request to read the binary log, and then writes the read binary log to a local realy log.
3. Start a SQL thread on the server to periodically check the Realy log (this file is also binary). If any update is found, immediately execute the updated content on the local database.

The environment is as follows:
Primary database: MySQL version 5.7, IP is 192.168.1.11
Slave database: MySQL version 5.7, IP is 192.168.1.12

1. Master database configuration

1. Create a new test database named testdb.

2. Open my.ini and add configuration

# Master-slave replication server-id=1 # Set server-id
log-bin=mysql-bin #Open binary file #Synchronize the database, and do not synchronize other databases (choose one of binlog-ignore-db below)
binlog_do_db=testdb 
#Do not synchronize the database, synchronize everything else #binlog-ignore-db = information_schema 
#binlog-ignore-db = MySQL

3. Restart MySQL service

4. Create a user account for synchronization (1) Open a cmd window as an administrator, run mysql -uroot -p, enter the password (if it is blank, press Enter), and log in.
(2) Execute the following three commands in sequence to create a user (user name MySlave, password 123456) and refresh permissions

CREATE USER 'MySlave'@'192.168.1.12' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'MySlave'@'192.168.1.12';
FLUSH PRIVILEGES;

5. Check the Master status and record the binary file name and location

show master status;

The binary file is mysql-bin.000005 and the position is 154

2. Configure from database

1. Create a new test database named testdb.

2. Verify whether the synchronization account can log in. Open Navicat for MySQL, create a new link, fill in IP 192.168.1.11, username MySlave, password 123456, and click "Connection Test" to verify whether it can connect.

3. Open my.ini and add configuration

server-id=2 #Set server-id
log-bin=mysql-bin #Open binary file

4. Restart MySQL service

5. Use cmd command line to log in to MySQL database and execute the following command for manual synchronization

mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.11',MASTER_PORT=3306,MASTER_USER='MySlave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154;

6. Start the salve synchronization process

mysql> start slave;

7. Check slave status

Execute command: show slave status\G;

When Slave_IO_Running: Yes and Slave_SQL_Running: Yes, it indicates that the two threads have been started and the master-slave replication configuration is successful.

8. Test by creating a new table in the master database and refreshing the slave database to see the table.

Remark:

Before performing database master-slave replication, if there are tables and data in the master database, this data will not be synchronized and needs to be manually exported and imported into the slave database.

The above is the detailed content of the configuration method of MySQL master-slave replication under Windows. For more information about MySQL master-slave replication, please pay attention to other related articles on 123WORDPRESS.COM!