Set MySql Data Synchronization

Source: Internet
Author: User
Document directory
  • Set MySql Data Synchronization
 
Set MySql Data Synchronization

Author:Abaowu

Mysql versions later than 3.23.15 provide the database replication function. This feature allows you to synchronize two databases, master-slave mode, and mutual backup mode.

The settings of the Database Synchronization replication function are reflected in the mysql settings file. Mysql configuration file (usually my. cnf), in the unix environment in/etc/mysql/my. cnf or in the mysql user's home directory my. cnf.

In the window environment, if the c: root directory contains the my. cnf file, the configuration file is used. When running the winmysqladmin.exe tool of mysql, this tool will name my. cnf in the c: root directory mycnf. bak. Create my. ini in the winnt directory. The configuration file is read when the mysql server is started. Therefore, you can copy the content in my. cnf to the my. ini file and use the my. ini file as the configuration file of the mysql server.

Setting method:

Set the sample environment:

Operating System: window2000 professional

Mysql: 4.0.4-beta-max-nt-log

A ip: 10.10.10.22

B ip: 10.10.10.53

A: Set

1. Add the most synchronous User Account:

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

2. Add a database as the synchronization database:

create database backup

B: Set

1. Add the most synchronous User Account:

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

 

2. Add a database as the synchronization database:

create database backup

Master/Slave Mode: A-> B

A is the master

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

Server-id = 1log-bin # You can set log-bin = c: mysqlbakmysqllog to set the directory of log files,
# Mysqllog is the name of the log file. mysql creates several log files with Different Extensions named mysqllog.
Binlog-do-db = backup # specify the database for logs

Restart the database service.

Use the show master status Command to view the logs.

B is slave

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

Server-id = 2master-host = 10.10.22master-user = backup
# Master-password = 1234master-port = 3306master-connect-retry = 60
The preset Retry Interval is 60 seconds. replicate-do-db = backup tells slave to only update the backup database.

Restart Database

Use show slave status to check the synchronization configuration.

Note: Because the slave configuration information is set, mysql generates master.info in the database directory. to modify the slave configuration, delete the file first. Otherwise, the modified configuration does not take effect.

Dual-host mutual backup mode.

If you add slave settings to aand master settings to B, you can synchronize data between B and.

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

master-host=10.10.10.53master-user=backupmaster-password=1234
replicate-do-db=backupmaster-connect-retry=10

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

log-bin=c:mysqllogmysqllogbinlog-do-db=backup

Note: *. err log file is generated when an error occurs. The synchronization thread exits. When an error is corrected, the synchronization mechanism should be executed to run slave start.

Restarting the AB machine enables two-way hot standby.

Test:

Insert A large data table AA (1872000) into database B in batches. Database A can update 2500 data entries per second.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.