How to synchronize MySQL data to a third-party system

From: http://xiaofengtoo.iteye.com/category/57504? Show_full = true

 

1.
Problem proposal

When a third party needs to provide data synchronization updates for some tables, such as a platform system, and the upper-layer CRM system needs to provide data for some tables, how do we generally adopt solutions? In addition, if there are multiple upper-layer systems that are used for multiple purposes, how can we synchronize others' data?

2.
Solution 2.1 adopts the Database Synchronization Function

2.1.1 solution description
This solution utilizes the data synchronization functions of various databases. For example, databases such as MySQL and SQL server have the database or table synchronization functions, in this case, you need to create the structure of the table to be synchronized on the machine to be synchronized. This feature is also used when our system has a master-slave database. TheAdvantagesYes:

1) You do not need to write your own program;

2) You can synchronize tables from multiple slave nodes.

TheDisadvantagesYes:

1) it depends on the functions of the database. If the two databases are different, the configuration may fail;

2) the configuration of the master database server needs to be changed;

3) The structure of the table to be synchronized must be completely consistent.

The reference for this solution is as follows:

 

 

 

2.1.2 solution example
MySQL databases support data synchronization and can synchronize some of the tables, but must be configured in a third-party database and a database to be synchronized.

Assume that there are two MySQL servers 192.168.2.89 and 192.168.2.170, both of which are in the Linux environment. The configuration of the two servers is the same, and 192.168.2.89 is used as the master database, use 192.168.2.170 as the Server Load balancer (slave) database. In this case, the Server Load balancer takes the initiative to synchronize data from the master node.

First, find my In the master database (192.168.2.89. the Directory of the CNF configuration file (the author here is/etc/My. CNF), modify the content of the file, and then add the following two rows (the first row indicates the synchronization mode, and the second row indicates the database to be synchronized is SMP ):

SQL code

1. SQL-bin-Update-same
2. BINLOG-do-DB = SMP

sql-bin-update-samebinlog-do-db=smp

 

To make the configuration take effect, restart the master database server. The command is as follows:

 

 

 

SQL code

1. MySQL. Server restart

mysql.server restart

After the master database is configured, configure the slave Database Server (192.168.2.170). First, edit the/etc/My. CNF file and find the following lines:

SQL code

1. Server-id = 1

server-id       = 1

 

 

Modify the row and add the following rows. The content is as follows:

SQL code

1. Server-id = 2 // This MySQL is a slave server
2. Master-host = 192.168.2.89 // ip address of the master server
3. Master-user = MySQL // the user who connects to the master server
4. Master-Password = mysql123 // password used to connect to the master server
5. Master-Port = 3306 // connection Port
6. Master-connect-retry = 10 // Number of Retries
7. Replicate-do-DB = SMP: test_mysql // synchronize the database table as the test_mysql table of the SMP Database
8. Log-slave-Updates // synchronous form

Server-id = 2 // This MySQL is the slave server master-host = 192.168.2.89 // ipmaster-user of the master server = MySQL // master-Password = mysql123/ /connect to the master server password master-Port = 3306 // connection port master-connect-retry = 10 // Number of Retries replicate-do-DB = SMP: test_mysql // The log-slave-Updates // synchronous form of the test_mysql table of the SMP Database

 

After modification, restart the slave database in the same way. However, after the restart, the following error is reported in/home/MySQL/var/localhost. localdomain. Err of MySQL on the slave machine, and the restart is not successful:

SQL code

1. 101223 20:37:01 [Error] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet o
2. N master (server_errno = 1236)
3. 101223 20:37:01 [Error] slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Log event entry exceeded Max
4. _ Allowed_packet; Increase max_allowed_packet on Master ', error_code: 1236

101223 20:37:01 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236)101223 20:37:01 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master', Error_code: 1236

 

I can only check the information on the Internet. It turns out to be the max_allowed_packet Of My. CNF on two machines.
The configuration is 1 MB, which is too small. You can change it to 16 MB and restart the Master/Slave server in the following order: Note: I found some problems in practice, please try this solution first.

Note: because the table test_mysql of the SMP database is synchronized, you must first create a simple table test_mysql In the SMP database of both hosts, the reference statement is as follows:

SQL code

1. Create Table test_mysql
2. (
3. Id integer not null auto_increment,
4. Name varchar (32) not null,
5. Primary Key (ID)
6. );

create table test_mysql (     id   integer not null auto_increment,    name varchar(32) not null,    primary key (id));

 

To test whether the configuration is successful, the simplest way is to insert a piece of data in the table of the master database (192.168.2.89) to check whether the table is from the host (192.168.2.170) the corresponding data is also inserted.

2.2 synchronously write FTP directory files

2.2.1 solution description
During the development process of the author, I also encountered this situation: the third party has been connected to multiple platforms, and the write format of some information (such as the ticket information) has been uniformly defined by this third party, you need to write data to the specified FTP directory in real time, and the programs on their side will automatically crawl and delete the data every one or half hour. See the following:

2.2.2 solution implementation suggestions
This allows the platform to trigger one or more programs in real time when writing synchronization information (such as phone information) locally, and write data to the specified FTP directory according to the format defined by a third party. Or the program is triggered at intervals (the same or shorter time than the time when the other party obtains the file) to write the ticket information to the FTP directory.

When the data volume is large, it is generally written to the file by hour, and the corresponding hourly summary file is provided to indicate which files need to be read in this hour. To delete a file, a third-party platform is generally allowed to delete the file after obtaining the file.

To facilitate data verification or cope with exceptions in the future, it is best to write the same file content as the file synchronized to a third party in the local directory when writing files under the FTP directory.

2.3 update the data table by receiving update statements

2.3.1 solution description
This solution requires you to write programs to update or insert data from one or more third-party systems.

2.3.2 solution implementation suggestions

At the beginning of the program design, you need to consider multiple issues, such:

1) use configuration files for table Synchronization

To configure which tables trigger synchronization by a third-party system, you must be able to flexibly configure them in the configuration file.

2) configurable triggering actions

Multiple operations should be performed to facilitate backup. For example, write data only to a third-party database and write data to a local backup file when writing data to a third-party database.

3) The trigger program needs flexible configuration

To cope with the inconsistency between the table structure of the platform and the third-party platform, or to query some information before insertion or update, you must also support the trigger event of a table, the program that is triggered can be configured and specified. The default program can be used by default (generally, a running statement is used to directly insert or update a third-party system ).

4) Database Configuration configurability of third-party systems

The number of third-party systems that need to synchronize data can be multiple. These database configuration information should be flexibly configured in the configuration file.

5) The trigger time is configurable.

The trigger is triggered every half hour, hour, or real-time. It should be configurable.

3.
References

1) synchronization between two MySQL Databases and between specified tables:

Http://www.360doc.com/content/06/1215/18/3500_295248.shtml

2) How to synchronize data between two MySQL Databases

Http://www.ltesting.net/html/51/n-131551.html