Configuration notes for MySQL master-slave synchronization under XP

The master needs to use a computer, which can have multiple computers. Here we use a master server and an slave server as an example.

Scenario: master server IP Address: 192.168.1.104

Slave Server IP Address: 192.168.1.114

Windows XP SP3

MySQL version: 5.0.22 (the versions of the two servers must be the same)

1. Configure the master server (that is, the operation is performed on the master server)

1. Set a synchronization account:

Command: grant file on *. * To 'hfut' @ '192. 168.1.114 'identified by 'hzh'

// Add the user hfut to the slave server with the password hzh. Note that the SQL statement allocates a file

// Operation permission. You must also assign the insert and other permissions to them as needed;

Command: grant replication slave on *. * To 'hfut' @ 192.168.1.114 'identifiedby 'hzh'

2. The above two commands set an account hfut that can access the master server from the slave server. The password is hzh, and the permission can be added on its own;

3. Create a database and table for synchronization:

Command: Create Database huzunhua;

Usehuzunhua;

Createtable mytest (username varchar (20), password varchar (20 ));

NOTE: If other data already exists in the synchronized database, manually copy the existing data and synchronize the new data.

4. Open My. ini and enter the following content in [mysqld:

Log-bin = c: \ master. Log

BINLOG-do-DB = huzunhua

Server_id = 1

Note: The first line is the log record location, and the second line is the name of the database to be synchronized. Here we use the case database created in step 3. The third row is the ID of a master, which is equivalent to the ID and can be left unspecified.

5. run the net stop MySQL command in cmd to terminate the MySQL service, and then run the Net start MySQL command to start the MySQL service. In this way, the MySQL service is restarted, use the following command in the MySQL Command window of the master server (the window used in step 3:

Show Master Status \ G

The output is as follows:

Record File and position values, which need to be used later.

2. Configure the slave server (that is, the operation is performed on the slave server)

1. Open My. ini and enter the following content in [mysqld:

Log_bin = C:/master. Log

Master-host = 192.168.1.104

Master-user = hfut

Master-pass = hzh

Master-Port = 3306

Master_log_file = master.000006

Master_log_pos = 98

 

Replicate-ignore-DB = MySQL

Replicate-do-DB = huzunhua

Replicate-ignore-DB = information_schema

The master_log_file and master_log_pos parameters are the two values above, and must be set correctly. Of course, this setting can also be done using commands. ,

2. Restart the MySQL service;

3. Run the following command:

Show slave status \ G

The following information should be displayed:

Note that the first line shows that the waiting for Master to sent event configuration is successful. In addition, the display of slave_io_running and slave_ SQL _running should be yes, otherwise the synchronization will fail.

Iii. Test

You can perform the insert operation on the master server, and then use the SELECT statement on the slave server to insert the values in the master server,