MySQL database master-slave configuration tutorial under Windows

The detailed process of configuring the MySQL database master and slave under Windows is as follows

Tools used:

Mysql, Navicat Premium;

Main library settings:

one. Set up the my.ini file;

1. Find the my.ini file in the installation directory:

Default path: C:\Program Files\MySQL\MySQL Server 5.0

2. Find [mysqld] in the my.ini file (# comments are deleted by yourself)

Add the following configuration

server-id=1#The master and slave databases need to be inconsistent

log-bin=mysql-bin

binlog-do-db=mstest#Synchronized database

binlog-do-db=test#Synchronized database

binlog-do-db=keyboard#Synchronized database

binlog-ignore-db=mysql#Database that does not need to be synchronized

As shown in the figure:

Restart the MySql service (stop, start)

3. Confirm the serverid of the master database and the databases that need to be synchronized and the databases that do not need to be synchronized

Open Navicat Premium and connect to the main database

(1) Press F6 or find [Command List Interface] in [Tools].

Input: SHOW VARIABLES LIKE 'server_id';

Confirm serverId

Input: show master status;

Confirmation: The databases that need to be synchronized and the databases that do not need to be synchronized. The file and position in the figure below will be used in the slave database configuration (the file and position parameters will change every time the master server my.ini file is modified)

As shown in the figure:

4. Create an account for the slave library:

1). As shown in the figure:

The % means that any host can connect to the database

2). Add account permissions: Select all server permissions

Main library configuration OK

two. Configuration from the library

1). Use the account and password created by the main database to connect and see if it can be connected normally

2). Also find the my.ini file to configure the file

Find the [mysqld] node

server-id=2#Inconsistent with the main database

master-host=192.168.0.103#Master database IP (and slave database are in LAN)

master-user=slave

master-password=123

master-port=3306

master-connect-retry=60

replicate-do-db=test#Tables that need to be synchronized

replicate-do-db=keyboard#Tables that need to be synchronized

replicate-ignore-db=mysql#Tables that do not need to be synchronized

As shown in the figure:

3). Restart the MySql service. Open the task manager and find mysql. Stop it and then start it.

4). Set up the running code from the library configuration

(1) Input: stop slave;

Description: Pause slave database

(2) Input:

change master to master_host='192.168.0.103',master_port=3306,master_user='slave',master_password='123',master_log_file='mysql-bin.000005',master_log_pos=98;

Note: Change to your own configuration accordingly

The File and Position ('mysql-bin.000005' 98) are obtained in step 3 of the master database configuration. Enter the command show master status in the master database;

(3) Input: start slave;

Description Startup;

As shown in the figure:

5). Check whether the connection is established

Input: show slave status;

Of course they say this is also possible (but I can't) show slave status\G (without semicolon), check

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

After entering the command

There will be a long text that doesn't look good, so I copied it to text as shown below:

Three. Verification (optional):

Main library:

From the library:

(2) I added several tables to the main database:

Main library:

From the library:

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.