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

This article will explain in detail how to set up MySql data synchronization. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Mysql has provided database replication since version 3.23.15. By using this function, the functions of two databases synchronization, master-slave mode and mutual backup mode can be realized.

Configuration file for mysql (usually my.cnf)

In the window environment, take the configuration file if there is a my.cnf file in the c: root directory. When you run mysql's winmysqladmin.exe tool, the tool names the my.cnf under the c: root as mycnf.bak. And create a my.ini under the winnt directory. The configuration file is read when the mysql server starts up. So you can copy the contents of my.cnf into the my.ini file and use the my.ini file as the configuration file of the mysql server.

Setting method:

Set up the sample environment:

Operating system: window2000 professional

Mysql:4.0.4-beta-max-nt-log

An ip:10.10.10.22

B ip:10.10.10.53

A: settin

1. Add a user account with the most synchronized user:

GRANT FILE ON *. * TO backup@'10.10.10.53' IDENTIFIED BY '1234'

two。 Add a database as a synchronization database:

Create database backup

B: settin

1. Add a user account with the most synchronized user:

GRANT FILE ON *. * TO backup@'10.10.10.22' IDENTIFIED BY '1234'

two。 Add a database as a synchronization database:

Create database backup

Master-slave mode: a-> B

An is master

Modify the my.ini file of A mysql. Add the following configuration to the mysqld configuration item:

Server-id=1

Log-bin

# set the directory where you need to record log. You can set log-bin=c:mysqlbakmysqllog to set the directory of log files

# where mysqllog is the name of the log file, and mysql will establish several log files with different extensions and file names of mysqllog.

Binlog-do-db=backup # specify the database that requires logging

Restart the database service.

Use the show master status command to check the log.

B is slave

Modify the my.ini file of B mysql. Add the following configuration to the mysqld configuration item:

Server-id=2

Master-host=10.10.10.22

Master-user=backup # synchronize user accounts

Master-password=1234

Master-port=3306

Master-connect-retry=60 preset retry interval of 60 seconds

Replicate-do-db=backup told slave to update only the backup database.

Restart the database

Use show slave status to see the synchronization configuration.

Note: because the configuration information for slave is set, mysql generates master.info in the database directory

So if you want to modify the configuration of the relevant slave, you should delete the file first. Otherwise, the modified configuration cannot take effect.

Dual-computer mutual backup mode.

If you add the slave setting in An and the master setting in B, you can synchronize B-> A.

Add the following settings to the mysqld configuration item in the configuration file of A:

Master-host=10.10.10.53

Master-user=backup

Master-password=1234

Replicate-do-db=backup

Master-connect-retry=10

Add the following settings to the mysqld configuration item in the configuration file of B:

Log-bin=c:mysqllogmysqllog

Binlog-do-db=backup

Note: when an error occurs, * .err log file. The synchronized thread exits, and when the error is corrected, let the synchronization mechanism work and run slave start

If you restart the AB machine, you can achieve two-way hot backup.

Test:

This is the end of the article on "how to set up MySql data synchronization". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please 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.