W indows System MySQL server master-slave data synchronization Backup

MySQL master-Slave synchronization backup steps under Windows 2003 System

A: Master server
IP Address: 192.168.1.124
B: From the server
IP Address: 192.168.1.125

Prerequisite: Please keep the data of two databases the same before setting the sync!

Modify the configuration of server A, open the Mysql/my.ini file, and add the following under [Mysqld]:

The code is as follows	Copy Code
Server-id=1 Log-bin=e:mailmysqldata

Server-id: ID value for primary server A
Log-bin: Binary Change Day value

Reboot a server

Modify the configuration of the B server, open the Mysql/my.ini file, and add the following under [Mysqld]:

The code is as follows	Copy Code
[Mysqld] server-id=2 master-host=192.168.1.124 Master-user=root Master-password=d5qj0y4stk master-port=3306 #主服务器端口 Master-connect-retry=60 #同步时间间隔为60秒

The following items are optional

#需要同步的数据库

The code is as follows	Copy Code
Replicate-do-db=mail

#不需要同步的数据库, ignore

The code is as follows	Copy Code
Binlog-ignore-db=mysql

Reboot the server!!

The code is as follows	Copy Code
show slave statusg;

Experiment finally succeeded, after the final test add delete modify data can be synchronized!

MySQL master-slave synchronization settings under Windows 7

Environment: Windows 7, Mysql-5.5.18-win32.msi

Home Server address: 192.168.1.192

From database address: 192.168.1.193

Process:

1: Find the My.ini file under MySQL, add the main server-side configuration under [mysqld]:

The code is as follows	Copy Code
Server-id=1 #服务器 ID Log-bin=c:program filesmysqlmysql Server 5.5mysql-bin #二进制文件存放路径 Binlog-do-db=test1 #待同步的数据库 Binlog-ignore-db=information_schema #忽略不同步的数据库, this doesn't have to be written. Binlog-ignore-db=mysql #忽略不同步的数据库, this doesn't have to be written. Binlog-ignore-db=test #忽略不同步的数据库, this doesn't have to be written.

2: Save the My.ini file, restart the database, and then see Mysql-bin.index files and mysql-bin.000001 files under C:Program filesmysqlmysql Server 5.5;

3: Enter MySQL via cmd:

The code is as follows	Copy Code
CD C:Program Filesmysqlmysql Server 5.5bin

Enter username and password: mysql-u user name-p password (mysql-uroot-proot)

4: Set permissions on the server to which you want to connect:

The code is as follows	Copy Code
Grant replication Slave,reload,super on *.* toslave@192.168.1.193 identified by ' 123456 ';

PS: Add permissions to the host 192.168.1.193, username: Slave, password: 123456; (just enter it once)

5: Input command show master status; # Find the values of file and Position and record them;

In fact, the primary server does not need to set too much information, only need the first three lines in the 1th is enough.

The following configuration is from server:

1: Find the My.ini file under MySQL, add the server-side configuration under its [mysqld]:

The code is as follows	Copy Code
server-id=2 #服务器 ID and cannot be consistent with the primary server Replicate-do-db=test1 #待同步的数据库 Binlog-ignore-db=information_schema #忽略不同步的数据库, this doesn't have to be written. Binlog-ignore-db=mysql #忽略不同步的数据库, this doesn't have to be written. Binlog-ignore-db=mysql #忽略不同步的数据库, this doesn't have to be written. Binlog-ignore-db=test #忽略不同步的数据库, this doesn't have to be written.

2: Save My.ini file, restart from the database, in mysql5.1 above version is not supported in 1 master set, if the addition of master settings, the database can not be restarted;

3: Enter MySQL via cmd:

The code is as follows	Copy Code
CD C:Program Filesmysqlmysql Server 5.5bin

4: Test from the server: Mysql-u slave-p 123456-h 192.168.1.192, see if you can connect to the main database success, if successful, you must quit, before I have been made a mistake, according to the online steps after the test has not quit MySQL, Results The following steps are always error, because the main database is always set;

5: Modify the parameters of the connection to the primary database:

The code is as follows	Copy Code
Mysql>change Master to master_host= ' 192.168.1.193 ', master_user= ' slave2 ', master_password= ' 1234562 ', Master_log_ File= ' mysql-bin.000001 ', master_log_pos=107;

PS: May report an error, presumably means that slave thread is running, can not be set, so that the implementation of Mysql>stop slave; Stop the slave thread, and then set the parameters of the connection;

6: After setting up, execute mysql> start slave; Open the slave thread; perform mysql> show slave statusg (no semicolon), view

The code is as follows	Copy Code
Slave_io_running:yes Slave_sql_running:yes

7: Over, in the process of my test results have been slave_io_running:no; let me very depressed, has not found the reason, the error code is as follows,

Later found, in fact, the reason is very simple, when I test the connection to the main database I did not quit, resulting in all the next steps are to the primary database operation