Synchronizing data between MySQL databases

http://blog.csdn.net/swandy45/article/details/6982421

Environmental requirements:

Windows Operating System

A later version of MySQL 3.23.15 is required.

Assume that database A is a host and that database B is a slave (A to B provides synchronization services, that is, the data in B is from a)

A machine: ip=10.10.151.166

Machine B: ip=10.10.151.156

Here are the configuration steps for one-way synchronization:

1 Create a new database in machine A, SQL statement:

[SQL]View PlainCopy

2. CREATE DATABASE backup_db;
4. Use test;
6. CREATE TABLE ' backup_table ' (
8. ' ID ' int (one) not NULL auto_increment,
10. 'name ' varchar (character) set UTF8 not NULL,
12. ' Sex ' varchar (2) character set UTF8 not NULL,
14. PRIMARY KEY (' id ')
16. ) Engine=innodb DEFAULT charset=latin1;

2 Open the My.ini file in the MySQL installation directory of the A machine and add it at the end of the file:

[Plain]View PlainCopy

1. Server-id=1
2. Log-bin=c:\mysqlback #启动同步事件的日志记录文件
3. Binlog-do-db=test #提供数据同步服务的数据库

3 Create a database with the same structure as machine A in machine B, SQL statement:

[Plain]View PlainCopy

1. CREATE DATABASE backup_db;
2. Use test;
3. CREATE TABLE ' backup_table ' (
4. ' id ' int (one) not NULL auto_increment,
5. ' Name ' varchar (character) set UTF8 not NULL,
6. ' Sex ' varchar (2) Character set UTF8 not NULL,
7. PRIMARY KEY (' id ')
8. ) Engine=innodb DEFAULT charset=latin1;

Note: The database structure of machine A and B must be consistent, otherwise it will not make a synchronization

4 Open the My.ini file in the MySQL installation directory of the B machine and add it at the end of the file:

[Plain]View PlainCopy

1. server-id=2
2. master-host=10.10.151.166 #主机A的地址
3. Master-user=ym #主机A提供给B的用户, the user needs to include the database test permissions
4. Master-password=ym #访问密码
5. master-port=3306 #端口, the host's MySQL port
6. master-connect-retry=60 #重试间隔60秒 A database is reconnected every 60 seconds when the master-slave connection is accidentally disconnected
7. Replicate-do-db=test #同步的数据库
8. P>5 after completing the above configuration, enter:</p> in the MySQL console of machine A

[Plain]View PlainCopy

1. GRANT REPLICATION slave,reload,super On * * to [e-mail protected] identified by ' YM ';

#这句是为让从服务器有足够的权限从主服务器是哪个接收二进制日志文件

6 Restart the MySQL database for machine A and B,

[Plain]View PlainCopy

1. In machine B, MySQL console:
2. Mysql>slave start;
3. If you want to see the synchronization configuration, you can enter it as follows:
4. MySQL Console for machine A:
5. Mysql>show Master status;
6. Machine B's MySQL console:
7. mysql>show slave status;

7 Insert some data into the Backup_table table in the test database in machine A to view the Backup_table table in the test database in machine B should be synchronized to implement the data changes.

Here are the two-way data synchronization configuration steps:

1 is the machine A and machine B used above, which is B is the host, A is slave, keep the above configuration do not change in the MySQL installation directory of machine a My.ini file last added:
master-host=10.10.151.156 #主机B的地址
Master-user=ym #主机B提供给A的用户, the user needs to include the database test permissions
Master-password=ym #访问密码
master-port=3306 #端口, the host's MySQL port
Master-connect-retry=60 #重试间隔60秒当主从服务器连接意外断开时数据库每隔60秒进行一个重新连接
Replicate-do-db=test #同步的数据库
The My.ini file in the MySQL installation directory of machine B is added last
Log-bin=c:\mysqlback #启动同步事件的日志记录文件
Binlog-do-db=test #提供数据同步服务的数据库

2 MySQL console input for machine B:
GRANT REPLICATION slave,reload,super on *.[email protected]Identified by ' YM ';
3 Restart the MySQL database for machine A and machine B
Insert some data into the Backup_table table in the test database in machine B and see that the Backup_table table in test database in machine A should be synchronized to make changes to the data. But does not cause loops
Note: To achieve data synchronization for MySQL database, you must turn off Windows Firewall for both Windows operating systems
How Mysql data is synchronized:
MySQL data synchronization, on the MySQL official website document, called replication literal meaning is redo, this very accurate indicates the MySQL database operation Essence, is to redo the same operation, to maintain the main number According to the library server, the data between master and subordinate database server slave is consistent.
From MySQL3.23.15 onwards, MySQL supports one-way asynchronous replication. That is, 1 MySQL servers act as master (the main library), 1 or more MySQL servers act as slaves (from the library), and data is replicated asynchronously from master to slaves. Note that this replication is asynchronous, unlike MySQL's synchronous replication implementations (this implementation is called MySQL cluster, MySQL Cluster).
When the main library is updated, the main library writes the SQL of the update operation to the binary log (Bin log, which records all the statements that have updated the data or has potentially updated the data), and maintains an index of the binary log file for the log file reincarnation (Rotate). When starting asynchronous replication from the library, two I/O threads are opened from the library, one of which connects to the main library, requiring the main library to pass the changes of the binary log to the library and write the returned logs to the local disk. The other thread is responsible for reading the locally written binary log and performing it locally to reflect this change to ensure data synchronization between the master and slave databases. Older versions enable only one I/O thread when replicating, enabling both parts of the functionality. This method uses the MySQL database Master (master) and slave (slave) asynchronous replication functions to achieve synchronization between databases.
In the Windows system, in the MySQL installation directory under the My.ini file:
Log-bin=c:\mysqlback;
This sentence indicates that the binary log files of the database are stored in the C packing directory, and the Mysqlback is the file name, in. 000001 the sequence number is the extension of the file, and each Binlog file defaults to 1GB. A file that is automatically swapped to a file name extension of. 000002 is exceeded, and the index file Mysqlback.index file records all Mysqlback file names. Of course, you can also delete these binary log files:
Use the following two commands
PURGE {MASTER | BINARY} LOGS to ' Log_name '//log_name will not be cleared, delete this sequence number to//before the
PURGE {MASTER | BINARY} LOGS before ' date '//date will not be erased, delete this date before the
Delete all previous binary log files and regenerate the new binary log file suffix starting with. 000001.
However, if you have a subordinate server that is running and the server is currently reading the log file that is trying to delete, the DELETE statement will not work, but will fail with an error. However, if the secondary server is stopped and it happens to clean up the log file it wants to read, the secondary server cannot replicate after it is started. When a subordinate server is replicating, the DELETE statement can run safely without stopping them.
After the configuration of the My.ini file for master server A is completed, the primary server A is restarted, and the additions, deletions, and modifications to the primary server a database from now on are recorded in the binary log file (the query operation is not logged). However, you can also specify where to start from the server and synchronize with the primary server, performing the following SQL operation from the server:
First enter the command: slave stop;
Then enter: Mysql > Change Master to
->master_host= ' Master_host_name ', #主机IP地址
->master_user= ' Replication_user_name ',
->master_password= ' Replication_password ',
->master_log_file= ' Recorded_log_file_name ',
->master_log_pos= ' recorded_log_position ';
Last input command: slave start;
Here, change the above recorded_log_file_name to the file name that you want to start synchronizing from which binary log file, and recorded_log_position to start synchronizing from the first few records of the file.
First input slave stop, instruction, then enter the above instruction, and finally input slave start;
When data synchronization generates an error, the *.err log file is generated in the MySQL installation directory, and the synchronization thread exits.
Purge binary logs to ' mysql-bin.000003′;
PURGE {MASTER | BINARY} LOGS to ' Log_name '
PURGE {MASTER | BINARY} LOGS before ' date '
Used to delete all binary logs that are listed in the log index before the specified log or date. These logs are also deleted from the list in the log index file, so that the given log becomes the first one.
For example:
PURGE MASTER LOGS to ' mysql-bin.010 ';
PURGE MASTER LOGS before ' 2008-06-22 13:00:00 ';
Clear the Binlog 3 days ago
PURGE MASTER LOGS before Date_sub (now (), INTERVAL 3 day);
The date argument of the before variable can be in the ' yyyy-mm-dd hh:mm:ss ' format. Master and binary are synonyms.

Synchronizing data between MySQL databases