How to implement MySQL master-slave replication on a Windows host?

MySQL’s master-slave replication is implemented through binlog logs. The “master” in master-slave replication refers to the database on the MySQL master server, and the “slave” refers to the MySQL slave server. database, and this kind of replication is based on the database level. For this reason, the database name in the slave server must be consistent with the database name in the master server. Then, to achieve master-slave replication, we must have at least two MySQL servers ( It is best if the two MySQL servers are located on different hosts, or two MySQL servers are installed on one host with different ports).

Generally speaking, the main database and slave database of MySQL database are distributed on different hosts. If we only have one host now and it is a windows system, how to implement master-slave replication of MySQL? The method is as follows:

Here we only introduce the operation method of one master and one slave .

My computer has installed the xampp integrated environment (similar to the wamp installation package), and the MySQL service in it can be used as the main server of MySQL. Then, we also need to install another MySQL on this computer as the slave server of the database.

The MySQL version installed in xampp of my computer is 5.6.20, and the port is 3306.

We need to install another MySQL (it is best to install the same version or a similar version to avoid problems), and change the port to 3307

Database server parameters:

• Master server (master): IP is 127.0.0.1, port is 3306

• Slave: IP is 127.0.0.1, port is 3307

Master server Configuration:

Modify the database configuration file of the main server (

E:\xampp\mysql\bin\my.ini), at the bottom of the [mysqld] label , add the following code:

#The database that needs to be backed up

binlog-do-db=test

#The database that does not need to be backed up

binlog-ignore-db=mysql

#Enable binary log

log-bin=mysql-bin

#Server id

server-id=1

Save and exit, restart the MySQL main server.

binlog-do-db is used to specify the database that needs to be synchronized,

binlog-ignore-db specifies the database that does not need to be synchronized. If neither parameter is set, the slave server will copy the master server all databases.

Generally, the root account is not used for synchronization account. For this reason, we need to create a new user on the main server (such as user01, password is 123456).

Here we use the command line to create it. The method is as follows:

Open cmd and switch to E:\xampp\mysql \bin, use the root account to connect to the MySQL main server:

mysql -uroot -p -P3306

Create a new user:

create user 'user01'@'127.0.0.1' identified by '123456';

（ The IP address after @ is the IP address of the client that is allowed to connect.)

Then, configure the master-slave replication permissions for the new user:

grant replication slave on *.* to 'user01'@'127.0.0.1' identified by '123456';

##(The IP address after @ is allowed to connect The client's IP address, if changed to '%', it means that the client has no IP address restrictions)

If there is already data in the main server's database (test), we need First manually copy the data from the master server to the slave server. The method is as follows:

In this case, we only back up one database (test). There is a table basic_user in test and there is already data in the table. In order to prevent the data in the database test from being updated when we copy the data, we need to lock the database first. The command is as follows:

flush tables with read lock;

This command is a global read lock. It will add read locks to all databases in the main server. By the way, let’s talk about the difference between read locks and write locks. :

• read lock: also called shared lock, allows all reads operation, but blocks write operations, that is, all connections can only read data, but are not allowed to write data.

• write lock (write lock): also called exclusive lock, exclusive lock, only allows reading and writing of the current connection, and does not allow other concurrent reading operations and write operations.

After locking the database of the master server, we also create a database test in the slave server and add all Tables (including table structure and table data) are imported.

Then, we execute the following command to unlock:

##unlock tables;

View the master status of the main server: ##mysql> show master status;

------------------ ---------- --------------- ------- ----------- -------------------

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

------------------ ---------- --------------- ----- ------------- ------------------

| mysql-bin.000008 | 498 | test | mysql |                                                                           | ------------------ ------------------

Slave server configuration:

Modify the database configuration file of the slave server (E:\mysql\

my.ini) , at the bottom of the

[mysqld] tag, add the following code: #Port

port = 3307

#serveridserver_id = 2

#Enable binary log (the slave server does not have to enable binary log)

log-bin=mysql-bin

Save and exit, restart the MySQL service.

Connect to the MySQL slave server:

mysql -uroot -p -P3307

##Configure the parameters for replication:

change master to master_host='127.0.0.1',master_user='user01',master_password='123456',master_port=3306,master_log_file= 'mysql-bin.000008',master_log_pos=498;

Parameter details:

master_host: IP of the main server
master_user: The newly created user name on the master server
master_password: The user’s password
master_port: The port of the master server, if it has not been modified , the default is enough.

master_log_file: The name of the main server binary log file, fill in the value of File displayed when viewing the master status of the main server

master_log_pos: The location of the log, fill in the Position value displayed when viewing the master status of the master server

Start Slave replication function from the server:

start slave;

View the slave status of the slave server:

mysql> show slave status \G

*** ********************** 1. row **************************** *****

Slave_io_State: WAITING for Master to Send Event

Master_host: 127.0.0.1

## Master_user: User01

## Master_Port: 3306

This

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 120

Relay_Log_F ile: hp-PC-relay-bin.000004

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If the values ​​of Slave_IO_Running and Slave_SQL_Running are both Yes, it means that all configurations of master-slave replication have been successful, that is, the slave server can automatically synchronize with the database data of the master server.

After that, as long as the data on the main server is updated (for example: a new table is created in the test database or the data in the table changes), the slave server will The server will automatically remain consistent with the main server. But if someone deliberately changes the data on the slave server, the data on the master server will not be updated synchronously unless we set the two MySQL servers to be mutual master-slave.
The above is what I have compiled about the master-slave architecture of configuring mysql in the window environment. Interested friends can try it.

Related articles:

Installing mysql-5.7.21 under windows

Summary of basic knowledge of MySQL

navicat for mysql download, installation and simple use

The above is the detailed content of How to implement MySQL master-slave replication on a Windows host?. For more information, please follow other related articles on the PHP Chinese website!