Win2003 Mysql one-way sync configuration steps [measured]_mysql

Prerequisites, both servers are installed MySQL the same version, the database name is the same, the best data are as much as possible.

MySQL Server side 192.168.0.1:

Create a new standby account that is used only to copy data
User name: Backup
Password: wwwjb51net

GRANT REPLICATION slave,reload,super on *.* to backup@ ' 192.168.0.2 ' identified by ' wwwjb51net ';
When a new user is created, the client is required to detect if the client can connect to the server side.

Add under [mysqld]

Copy Code code as follows:

server-id=10
Log-bin=mysql-bin

MySQL client 192.168.0.2:

Detection method in 192.168.0.1
Mysql-h 192.168.0.1-u root-p How to connect please refer to this article

Copy Code code as follows:

server-id=11
master-host=192.168.0.1
Master-user=backup
Master-password=wwwjb51net
Replicate-do-db=jb51

JB51 the name of the database to be backed up

Test method: is to add data in the background, and then in the client's web site backstage to see whether the data are there.

But after testing the general situation will not be a success will have some problems, the following article you must see. Basically, it's okay.

And then we're looking at sync.

To view the slave status:
Mysql> Show Slave Status\g
Slave_io_running:yes
Slave_sql_running:no
last_errno:1062
....
Seconds_behind_master:null
Reason:
1. The program may be written on the slave
2. It may also be the slave machine after the transaction rollback caused.

Solution I:
1. First stop slave service: Slave stop

2. View host status on the primary server:
Record the values for file and position.
Mysql> Show master status;
+------------------+-----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+-----------+--------------+------------------+
| mysql-bin.000020 | 135617781 | | |
+------------------+-----------+--------------+------------------+
1 row in Set (0.00 sec)

3. Perform a manual synchronization on the slave server:

Copy Code code as follows:

Mysql> Change Master to
> master_host= ' master_ip ',
> master_user= ' user ',
> master_password= ' pwd ',
> master_port=3307,
> master_log_file= ' mysql-bin.000020 ',
> master_log_pos=135617781;

1 row in Set (0.00 sec)
mysql> slave start;
1 row in Set (0.00 sec)
View slave status Discovery again:
Slave_io_running:yes
Slave_sql_running:yes
...
seconds_behind_master:0

Solution II:
mysql> slave stop;
Mysql> set GLOBAL sql_slave_skip_counter=1;
mysql> slave start;

Own experience: Method one is mandatory from a certain point of synchronization, there will be a part of the data loss is not synchronized, subsequent primary server delete record synchronization will also have some error messages, will not affect the use. Method two does not necessarily have an effect.