Mysql Master/Slave configuration tutorial in windows

Mysql Master/Slave configuration tutorial in windows

The detailed process of configuring the mysql database Master/Slave in windows is as follows:

Tools used:

Mysql, Navicat Premium;

Master database settings:

1. Set the my. ini file;

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

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

2. In the my. ini file, find [mysqld] (# delete comments by yourself)

Add the following configuration

Server-id = 1 # The master database and slave database must 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 # databases that do not need to be synchronized

Restart MySql Service (stop and start)

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

Open Navicat Premium to connect to the master database

(1) Press F6 or [tools] To find the [command list interface]

Input: show variables like 'server _ id ';

Confirm serverId

Input: show master status;

Confirm: the files and positions in the databases to be synchronized and those that do not need to be synchronized will be used in the slave database configuration (the file and position Parameters of the my. ini file in the master server will be changed each time)

4. Create an account for the slave database:

1 ).

% Indicates that any host can connect to the database.

2). Add account permissions: select all server permissions.

Master Database Configuration OK

Ii. Slave Database Configuration

1) Use the account and password created in the master database to connect to the database to see if the connection is normal.

2). Find the my. ini file for the configuration file.

Find the [mysqld] Node

Server-id = 2 # inconsistent with the master database

Master-host = 192.168.0.103 # master database IP address (the same as the slave database's lan ip address)

Master-user = slave

Masters-password = 123

Master-port = 3306

Master-connect-retry = 60

Replicate-do-db = test # table to be synchronized

Replicate-do-db = keyboard # table to be synchronized

Replicate-ignore-db = mysql # tables that do not need to be synchronized

3) restart the MySql service, open the task manager, find mysql, stop it, and start bingo.

4). Configure the slave database for Running code

(1) input: stop slave;

Suspend slave Database

(2) input:

Change master to master_host = '192. 168.0.103 ', master_port = 192, master_user = 'save', master_password = '000000', master_log_file = 'mysql-bin.000005', master_log_pos = 98;

Note: The configuration is changed to your own.

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

(3) input: start slave;

Start up;

5) check whether the connection is available

Input: show slave status;

Of course, they can also (but I cannot) show slave status \ G (no semicolon), View

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

After the instruction is entered

Long text may not be easy to read, so I copied it to text, for example:

Iii. Verification (do not refer to self-verification ):

Master database:

Slave database:

(2) I add several tables to the master database:

Master database:

Slave database:

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.