Shulou(Shulou.com)06/01 Report--

This article mainly explains how to configure MySQL master-slave replication under Windows, the content is clear, interested friends can learn, I believe it will be helpful after reading.

MySQL master-slave replication allows data from one database (master database) to be copied to one or more databases (slave databases).

The main database is generally a real-time business data writing and updating operation, mainly reading from the database.

Master-slave replication process:

1. Any changes on the primary server will be saved in the binary log Binary log through its own I tread O thread.

2. Start an I thread O from the server, connect to the master server with the configured user name and password and request to read the binary log, and then write the read binary log to a local Realy log (Relay Log).

3. At the same time, open a SQL thread from the server to check Realy log regularly (this file is also binary). If you find an update, immediately execute the updated content on the local database.

The environment is as follows:

Master database: MySQL version 5.7 Magi IP is 192.168.1.11

From the database: MySQL version 5.7 Magi IP is 192.168.1.12

I. configuration of the main database

1. Create a new test database named testdb.

2. Open my.ini to add configuration

# Master-slave copy server-id=1 # set server-idlog-bin=mysql-bin # enable binaries # synchronized database, other than not synchronized (choose one of the two with the following binlog-ignore-db) binlog_do_db=testdb # is not synchronized with the database, except for synchronization # binlog-ignore-db = information_schema # binlog-ignore-db = MySQL

3. Restart the MySQL service

4. Create a user account for synchronization

(1) after opening the cmd window as an administrator, run mysql-uroot-p, enter the password (if empty, enter directly), and log in.

(2) execute the following three commands to create a user (username 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 Master status and record binary file name and location

Show master status

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

Second, configure from the database

1. Create a new test database named testdb.

2. Verify whether the synchronous account can be logged in.

Open Navicat for MySQL, create a new link, IP fill in 192.168.1.11, user name MySlave, password 123456, click "connection Test" to verify whether you can connect.

3. Open my.ini to add configuration

Server-id=2 # set server-id

Log-bin=mysql-bin # Open binaries

4. Restart the MySQL service

5. Log in to the cmd database on the command line and execute the following command to manually synchronize

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. View slave status

Execute the command: show slave status\ G

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

8. Testing

Create a new table in the master database and refresh the slave database. You can see this table.

Note:

If there are tables and data in the master database before the database master-slave replication, this part of the data will not be synchronized and need to be manually exported and imported from the database.

After reading the above content, do you have a further understanding of how to configure MySQL master-slave replication under Windows? if you want to learn more, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.