W & #8204; synchronous backup of Master/Slave Data of mysql server in indows System

A friend who maintains the server may know how important mysql master-slave backup is to the website. Next I will introduce you to the methods for implementing data synchronization and backup in mysql in windows and windows 7 systems, for more information, see.

Mysql master-slave synchronous backup procedure in windows 2003

A: master server
IP Address: 192.168.1.124
B: slave server
IP Address: 192.168.1.125
 
Premise: keep the data of the two databases the same before setting synchronization!
 
Modify the configuration of server A, open the mysql/my. ini file, and add the following content under [mysqld:

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

 
Server-id: ID value of master server
Log-bin: Binary Change Daily Value
 
Restart server
 
Modify the configuration of server B, open the mysql/my. ini file, and add the following content 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 server port Master-connect-retry = 60 # synchronization interval: 60 seconds

// Optional
 
# Databases to be synchronized

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

 
# Ignore databases that do not need to be synchronized

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

Restart the server !!

The Code is as follows:	Copy code
Show slave statusG;

 
The experiment succeeded. After the test, the added, deleted, and modified data can be synchronized!

Mysql master-slave synchronization settings in windows 7

Environment: windows 7, mysql-5.5.18-win32.msi

Master server address: 192.168.1.192

Slave database address: 192.168.1.193

Process:

1: Find the my. ini file in mysql and add the master server configuration under its [mysqld:

The Code is as follows:

Copy code

Server-id = 1 # server id Log-bin = C: Program FilesMySQLMySQL Server 5.5mysql-bin # binary file storage path Binlog-do-db = test1 # database to be synchronized Binlog-ignore-db = information_schema # ignore non-synchronous databases. You do not need to write Binlog-ignore-db = mysql # ignore non-synchronous databases, this can be left empty Binlog-ignore-db = test # ignore non-synchronous databases.

2: Save my. ini file, restart the database, and then you will see the mysql-bin.index file and mysql-bin.000001 file under C: Program FilesMySQLMySQL Server 5.5;

3: Enter mysql through cmd:

The Code is as follows:	Copy code
Cd C: Program FilesMySQLMySQL Server 5.5bin

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

4: Set permissions for the slave server to be connected:

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

Ps: add permissions to host 192.168.1.193, User name: slave, password: 123456; (you only need to enter the permission once)

5: Enter the command show master status; # record the values of File and Position;

In fact, the master server does not need to set too much information. It only needs the first three lines of the first 1st lines.

Configure slave server as follows:

1: Find the my. ini file in mysql and add the slave server configuration under its [mysqld:

The Code is as follows:

Copy code

Server-id = 2 # server id, which cannot be the same as the master server Replicate-do-db = test1 # database to be synchronized Binlog-ignore-db = information_schema # ignore non-synchronous databases. You do not need to write Binlog-ignore-db = mysql # ignore non-synchronous databases, this can be left empty Binlog-ignore-db = mysql # ignore non-synchronous databases, this can be left empty Binlog-ignore-db = test # ignore non-synchronous databases.

2: Save the my. ini file and restart the slave database. In MySQL or later versions, master Settings in MySQL 1 are not supported. If master Settings are added, the database cannot be restarted;

3: Enter mysql through cmd:

The Code is as follows:	Copy code
Cd C: Program FilesMySQLMySQL Server 5.5bin

4: Test mysql-u slave-p 123456-h 192.168.1.192 on the server to check whether the connection to the master database is successful. If yes, exit, I made a mistake all the time before. I did not exit mysql after testing according to the online steps. As a result, I always reported an error when performing the following steps, because the primary database is always set;

5. Modify the connection parameters of 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 = '000000', master_log_file = 'mysql-bin.000001', master_log_pos = 192;

Ps: An error may be reported, which means that the slave thread is running and cannot be set. In this case, execute mysql> stop slave; stop the slave thread, and then set the connection parameters;

6: After setting, run mysql> start slave; enable the slave thread; execute mysql> show slave statusG (no semicolon) to view

The Code is as follows:	Copy code
Slave_IO_Running: Yes Slave_ SQL _Running: Yes

7: the test result is always Slave_IO_Running: No in this process. The error code is as follows,

I found it later, but the reason is very simple. When I test the connection to the primary database, I did not exit, resulting in all the subsequent steps being performed on the primary database.