Windows MySQL master server Setup

Start by setting up two MySQL services locally (see here) and specify a different port. I am here a master (3306), one from (3307).
2. Then modify the master configuration file:
[Mysqld]
Server-id = 1
Binlog-do-db=test #要同步的数据库
#binlog-ignore-db=mysql #不同步的数据库, if you specify a binlog-do-db here, you should not use the specified
Log-bin=mysql-bin #要生成的二进制日记文件名称

To modify from a configuration file:
[Mysqld]
Server-id = 2
Log-bin = Mysql-bin
Replicate-do-db=test
3. Add a user Repl in the main library and specify replication permissions
Create user ' repl ' @ ' 127.0.0.1 ' identified by ' asdf ';

GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 127.0.0.1 '; ----Here I specify the database (test.*) times to be wrong, while specifying a full library (*. *) will succeed.

4, keep the master-slave MySQL test database in the initial state of the same.

It is common to add all the tables first read the lock, and then copy the disk 's database folder. I stop the service here and then copy the data file over.

5. Run Show Master Statu in the main database s Note the parameters for the file and position fields.

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | Test | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

6. Set its master from the library:

mysql> Change Master to master_host= ' 127.0.0.1 ', master_port=3306,master_user= ' repl ', master_password= ' asdf ', Master_log_file= ' mysql-bin.000001 ', master_log_pos=107;
Query OK, 0 rows affected (0.19 sec)

The Master_log_file and Master_log_pos here correspond to the parameters noted in the show master status.

7. From the library to open from the database copy function.

Slave start;

mysql> slave start;
Query OK, 0 rows Affected (0.00 sec)

Some parameters can be viewed from the library through show slave status.

8. When you create a table or insert data in the main library, you will see it quickly from the library.

--Main Library

Mysql> CREATE TABLE tianyc_02 (b int);
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO tianyc_02 values (2013);
Query OK, 1 row affected (0.13 sec)

--From the library

Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| tianyc_01 |
| tianyc_02 |
+----------------+
2 rows in Set (0.00 sec)

Mysql> select * from tianyc_02;
+------+
| B |
+------+
| 2013 |
+------+
1 row in Set (0.00 sec)

In the same vein, a second and a third slave node can be built.

Reference: http://blog.sina.com.cn/s/blog_4d06da1f01010m55.html

Note: The serve_id must be different for two services, otherwise the error will be prompted when the copy function is turned on

mysql> slave start;
ERROR HY000: The server is not configured as slave; Fix in config file or with change MASTER to

Here I set the primary node ID to 1, from Node ID to 2. can also be set according to IP or port, easy to distinguish. Refer here.

Attention:

* Master-slave database is not real-time synchronization, even if the network connection is normal, there is an instant, master-slave data inconsistency.
* If the master-slave network is disconnected, from the network after normal, batch synchronization.
* If you modify the data from, then it is very likely to be from the execution of the main Bin-log error and stop synchronization, this is a very dangerous operation. So in general, be very careful to modify the data from above.

Windows MySQL master server Setup