Shulou(Shulou.com)05/31 Report--

This article introduces you how to achieve one-way master-slave backup in MySql database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

One-way master-slave backup of MySql under Windows

Purpose: to synchronize the MySql database in the master server to the slave server

So that the operation on the master server can be immediately updated to the slave server, so as to prevent the master server from being unable to use temporarily due to environmental or network anomalies.

Achieve the backup effect.

Environment: www.2cto.com

The master / slave server operating system and database version are the same, as follows:

Operating system: Windows 2003 Server R2

MySql database version: 5.0.51b

Primary server (replaced by A) IP address: 192.168.4.100

Slave server (replace B) IP address: 192.168.4.101

3. Configuration process:

1. Create a remote access backup account in A.

Set up a backup account in A's MySql database with the following command:

> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *. * TO july@'192.168.4.101'

IDENTIFIED BY '123456'

Mysql > Flush privileges

Mysql > Quit

Statement explanation:

In the first sentence, *. * indicates all databases, if you want to back up a database, if the database name is some

Then replace it with some.*, where TO july@'192.168.4.101',july is the remote access user name.

IP address is remote access 123456 in IP,BY '123456' is remote access password

The creation of a remote access user takes effect after the execution of the second statement

Www.2cto.com

Instructions for execution:

After executing the above statement, to see if the remote access user has been created successfully, execute the following statement:

Mysql > use mysql

Mysql > select host,user from user

An extra entry has been added to the user list

+-+ +

| | host | user |

+-+ +

| | 192.168.4.101 | july | |

+-+ +

At this point, it means that the remote access user has been created successfully.

2. Shut down the MySql service of An and copy the data to B.

Turn off the Mysql service of An and copy the data from A to B, so that the initial data of An and B servers are exactly the same.

And the database write operation in the server is prohibited before the synchronous backup, so as to ensure that the initial data of An and B servers are exactly the same.

3. Modify the my.ini configuration information in A.

Open the my.ini file and add the following under [mysqld] (# is a comment):

# ID value of server-id:A server

Server-id=10

# log-bin: binary change log

Log-bin=c:\ log/log-bin.log

4. Restart server A.

5. Close the B server and configure the my.ini of B

It should be noted here:

Because the configuration information for slave is set, mysql generates master.info in the database data directory

Therefore, if you want to modify the configuration of the relevant slave, you must first delete the file, otherwise the modified configuration cannot take effect.

Open the my.ini configuration file for B and add the following in [mysqld]:

# B Server ID value www.2cto.com

Server-id=11

# remote access IP address

Master-host=192.168.4.100

# remote access user name

Master-user=july

# remote access user password

Master-password=123456

# Primary server port (default is 3306. Note here whether the primary server port is prohibited because of the firewall)

Master-port=3306

# synchronization interval is 60 seconds

Master-connect-retry=60

# set the database to be backed up synchronously, where the database is some

Replicate-do-db=some

6. Restart the B server, and the master-slave backup has been completed. If there is any error, you can check it through mysql_error.log.

On how to achieve one-way master-slave backup in the MySql database is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.