CN100449548C - Database synchronization method and system - Google Patents

Abstract

The invention discloses a database synchronization method. In the method, a synchronization table including a synchronization identification number and an SQL statement is set in a main server database, and the data synchronization of the database is realized by using the synchronization table. The invention also discloses a database synchronization system, which includes a main server and a backup server. The main server includes a backup unit and a synchronization table unit, and the backup server includes a request unit, an acquisition unit and a synchronization unit. The method and system disclosed in the present invention use the synchronization table to realize database synchronization, simplify the modification of server configuration, make the database synchronization not limited by the operating system and the database system, and improve the efficiency of data synchronization.

Description

Database synchronization method and system

technical field

The invention relates to database technology, in particular to a database synchronization method and system.

Background technique

In the telecommunications management network, each network unit has an operation and maintenance unit (OMU, Operation & Maintenance Unit), which can be specifically a server with OMU software installed, which is responsible for providing the operation and maintenance function of the network unit. In order to maintain the normal operation of the network unit, telecom operators have very high requirements on the reliability of the operation and maintenance function of the network unit, generally more than 99.999%. Therefore, in order to ensure the reliability of OMU operation and maintenance functions, telecom equipment suppliers generally adopt a dual system (Dual System) backup mechanism for OMU. The so-called dual-computer system is a system composed of two computers. Among them, the server corresponding to the active OMU is the active server (Active Server, hereinafter referred to as AS), which provides external service functions; the server corresponding to the standby OMU is the standby server ( StandbyServer, hereinafter referred to as SS), provides backup functions for the main server and does not provide external service functions. The active server and the standby server can be switched under certain conditions, that is, the active server becomes the standby server, and the standby server becomes the active server. The combination of the two can continuously provide external service functions.

The dual-server backup mechanism can ensure the reliability of the system operation. When the main server fails to operate and maintain the OMU due to some reasons such as crash or human negligence, the backup server will immediately replace the main server and continue to maintain the OMU. Operation and maintenance. Adopting this mechanism greatly reduces the probability of system operation halting, and provides reliable protection for both individual users and enterprise users. In addition, by adopting the dual-computer backup mechanism, user data can be transmitted to two hosts to obtain proper backup and improve the security of information processing.

To implement the operation and maintenance of network units, the server has to process a large amount of data, such as configuration data, alarm data, and performance statistics. These data are stored in the database system of the server. database access interface. These data stored in the database system are modified in real time as the system runs. Therefore, to realize the dual-machine backup function, it is necessary to ensure real-time synchronization between the data in the database system of the active server and the data in the database system of the standby server. Only in this way can it be ensured that when the active server fails, the standby server can provide correct operation and maintenance functions after being upgraded to the active server.

In the prior art, there are the following technical solutions to realize the synchronization of dual-machine databases:

Option One:

Utilize database replication mechanisms, such as SQL replication. The so-called replication is a technique for copying a set of data from one data source to multiple data sources. The basic elements of SQL (Structured Query Language, Structured Query Language) replication include publishers, subscribers, distributors, publications, and articles. The server where the source data resides is the publishing server, which is responsible for publishing the data. The publishing server copies all the changes of the data to be published to the distribution server. The distribution server contains a distribution database, which can receive all the changes of the data from the publishing server, save these changes, and then distribute these changes to the subscription server.

Taking Microsoft's SQL Server database system as an example, the system provides three replication technologies, namely snapshot replication, transactional replication, and merge replication. Real-time database synchronization between primary and secondary servers generally uses transactional replication technology. Using transactional replication requires the following configuration steps:

1. Configure a database as the distribution database (the distribution database can be configured on the main server or on the standby server), and specify which servers can use the database;

2. On the active server, configure the distribution database used by the active server; configure the subscribers allowed on the active server; configure the database that needs to be published on the active server and the data tables and fields that need to be published in the database;

3. On the standby server, configure the subscription to the publishing database;

4. Configure the scheduling and attributes of each agent process, including snapshot agent process, distribution agent process and log reading agent process, etc.;

The above configuration process is very complicated, error-prone and takes a long time to configure. If there are multiple databases and each database has multiple data tables for synchronization, the configuration process generally takes several minutes. At the same time, the configuration process modifies the attributes of the database, and the operations on the database will be restricted. For example, after configuring a database as a publishing database, the database cannot be deleted.

After the above configuration is successful, the SQL SERVER on the primary server will automatically generate a snapshot of the data in the data table that needs to be synchronized in each database (similar to the data export file of the data table), and the snapshot information will be sent to the distribution database, and the distribution database Then distribute to the standby server, and the standby server imports the data in the snapshot into the subscription database, so that the primary and secondary servers complete the initial data synchronization. After transactional replication is configured, the initial synchronization is completed first, and real-time synchronization is performed automatically. When data modification occurs on the primary server, the publishing database will transmit the log information of the data modification to the distribution database, and the distribution database will distribute it to the subscription database, and the subscription database will modify the data according to the log information to ensure synchronization with the distribution database.

The disadvantages of this scheme are:

1. Must be based on the SQL Server database system, correspondingly, the system must be installed on the WINDOWS operating system;

2. The transaction replication relationship must be configured between the active and standby servers, and the transaction replication relationship needs to be reconfigured when the active and standby servers are switched. Since the transaction replication relationship is very complicated, the configuration process of the database system is very complicated.

3. The publisher and subscriber take a long time to complete the initial database synchronization. The time for transactional replication to generate and apply snapshots is proportional to the number of data tables. If there are 500 data tables in the database that need to be synchronized, it usually takes more than 30 minutes to complete the initial synchronization. Moreover, the application of snapshots on the subscriber server is applied one by one table by table, not in one transaction. Generally, the switchover of the active and standby servers cannot occur before the initial synchronization process is completed, otherwise the data on the standby server will be confused. This time is difficult for telecom operators to accept;

4. This solution has many restrictions and impacts on the server. Transactional replication relies on the database's log, and marks the log to identify the log that has been delivered to the distribution database and the log that has not been delivered, so as to ensure that the database changes are consistent. If the standby server fails, the database log of the active server will continue to grow, causing the database to fail to run; if the active server restores the database, the log will be confused, and transaction replication will fail.

Option II:

Take Microsoft's SQL Server 2000 Enterprise Edition as an example. This system provides the function of log transfer, that is, it continuously backs up transaction logs from the source database, then copies and restores them to the destination database, so that the destination database and the source database remain the same. Synchronize. Log shipping is slightly simpler than transactional replication. Log transfer ensures that the two databases are synchronized, and transactional replication can keep the data tables that need to be synchronized in sync, and the data tables that do not need to be synchronized can not be synchronized. In terms of initial synchronization, SQL SERVER provides a wizard for log transfer operations to create a source database on the destination server to ensure that the initial data is consistent.

The disadvantages of this scheme are:

1. It must be based on the Sql Server database system. Correspondingly, the system must be installed on the WINDOWS operating system;

2. The real-time performance of log transmission is poor, and the real-time synchronization cycle is generally configured at 15 minutes, which is not suitable for servers with high real-time requirements;

3. The reliability of log transfer is not as good as transactional replication, and it is not suitable for servers with high reliability requirements.

Contents of the invention

In view of this, the main problem to be solved by the embodiment of the present invention is to provide a method and system for database synchronization, which can simplify the modification of server configuration, database synchronization is not limited by the operating system and database system, and the efficiency of data synchronization is improved.

The method for database synchronization provided by an embodiment of the present invention is used for the initial synchronization of the active server database and the standby server database. The active server database is provided with a synchronization table comprising a synchronization identification number. The method includes the following steps: standby The server sends an initial synchronization request to the active server; the active server updates the synchronization identification number in the synchronization table according to the received initial synchronization request, and backs up the database of the active server to generate a backup file; the standby server obtains the updated synchronization identification number and backup files, use the backup files to restore the standby server database.

The method for database synchronization provided by another embodiment of the present invention is used for the real-time synchronization of the active server database and the standby server database. The active server database is provided with a synchronization table comprising a synchronization identification number and an SQL statement, the synchronization identification number Real-time update according to the database operation, the method comprises the following steps: the primary server replicates the SQL statement of the database operation that needs to be synchronized to the synchronization table; the standby server is directly connected to the primary server database, obtains the SQL statement from the synchronization table, and uses the SQL statements access the standby server database.

The method for database synchronization provided by another embodiment of the present invention is used for the synchronization of the active server database and the standby server database. The active server database is provided with a synchronization table comprising a synchronization identification number and an SQL statement. The method includes:

A, standby server checks whether the identification number it saves exists in the synchronization table, if it does not exist, then execute steps B, C, D, if it exists, then execute steps E, F;

B. The standby server sends an initial synchronization request to the active server;

C. The active server updates the synchronization identification number in the synchronization table according to the received initial synchronization request, backs up the database database of the active server, and generates a backup file;

D, the backup server obtains the updated synchronization identification number and the backup file, and restores the backup server database with the backup file;

E. The primary server copies the SQL statement of the database operation that needs to be synchronized to the synchronization table;

F. The standby server obtains the SQL statement from the synchronization table, and uses the SQL statement to access the database of the standby server.

The database synchronization system provided by another embodiment of the present invention includes a primary server configured with a primary server database, and a standby server configured with a standby server database. The synchronization table for the statement.

The active server of the system includes: a backup unit, used for generating a backup file from the database of the active server after receiving the initial synchronization request sent by the standby server in the initial synchronization phase; a synchronization table unit, used in the initial synchronization phase, Update the synchronization identification number in the synchronization table, and also used to update the synchronization identification number in the synchronization table during the real-time synchronization stage, and copy the SQL statement of the database operation that needs to be synchronized to the synchronization table;

The backup server includes: a request unit, used to send an initial synchronization request to the active server; an acquisition unit, used to obtain the backup file generated by the backup unit during the initial synchronization phase, and the synchronization identification number updated by the synchronization table unit in the synchronization table ; The synchronization unit is used to obtain the backup file from the acquisition unit in the initial synchronization phase, and use the backup file to restore the standby server database, and is also used to obtain the SQL statement from the synchronization table in the real-time synchronization phase, and use the SQL statement to access the standby server database.

The method and system provided by the embodiments of the present invention use the identification number in the synchronization table to judge whether initial synchronization is required and ensure that the primary and secondary servers maintain data consistency after initial synchronization, and use the secondary server to read the synchronization table on the primary server Directly obtain the data modification of the primary server by means of the SQL statement in the This simplifies the modification of the server configuration, the database synchronization is not restricted by the operating system and the database system, and the efficiency of data synchronization is improved.

Description of drawings

FIG. 1 is a schematic structural diagram of a synchronization table in an embodiment of the present invention;

Fig. 2 is the flow chart of the method for database synchronization in the first embodiment of the present invention;

Fig. 3 is the flow chart of the method for database synchronization in the second embodiment of the present invention;

Fig. 4 is the flow chart of the method for database synchronization of the third embodiment of the present invention;

Fig. 5 is a system diagram of database synchronization in the fourth embodiment of the present invention.

Detailed ways

In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be further described in detail below in conjunction with the embodiments and accompanying drawings.

The technical solution provided by the embodiment of the present invention utilizes the synchronization table to implement database synchronization at the application layer, simplifies the modification of the server configuration, and the database synchronization is not restricted by the operating system and the database system, thereby improving the efficiency of data synchronization.

FIG. 1 is a schematic structural diagram of a synchronization table in an embodiment of the present invention.

As shown in FIG. 1 , the synchronization table set in the primary server database includes two fields: a synchronization identification field (identity) and a SQL statement field.

The synchronization identification field generally adopts the seed attribute provided by the database and is automatically incremented. This field does not need to be maintained. If the seed attribute is set to increase from 1, when inserting the first record, there is no need to specify the value of this field, and the database system will automatically set it to 1, insert the second record, and automatically set it to 2. If All records in the database are deleted. When inserting next time, the value of this field is the value before deletion plus 1. The synchronization identification field is used to indicate the synchronization identification number.

The SQL statement field stores the SQL statement for modifying the database. SQL is a data manipulation language supported by almost all large-scale data servers, and it provides some narratives that can quickly perform data operations such as data query, update, and deletion. As far as the relationship between the identifier and SQL is concerned, the identifier is a number given when inserting each SQL record, that is, the value of the identifier field is increased by 1 for each SQL record inserted.

In the embodiment of the present invention, by setting a synchronization table on the active server, the synchronization identification number of the synchronization table is compared with the identification number of the standby server to check whether initial synchronization is required. Real-time synchronization is realized through SQL statements, and at the same time, during the process of real-time synchronization, it is checked in real time whether initial synchronization is required. In this way, database synchronization is realized by synchronizing the identifiers and SQL statements in the table.

The identification number of the standby server can be saved in the database or in the configuration file. The value is initialized to 0, so when it is started for the first time, it must be different from the smallest identification number in the synchronization table of the active server, so the initial synchronization will be performed. After the initial synchronization is complete, the identification numbers held by the standby server are present in the synchronization table. Then enter real-time synchronization. In this process, every time the primary server modifies data, it inserts a SQL record into the synchronization table, adds a synchronization table record, and correspondingly increases the identification number; The record in the table whose identification number is smaller than the identification number saved by itself, and then read the record in the synchronization table on the primary server whose identification number is greater than the identification number saved by itself. Before the backup server executes a read record, it checks the ID number saved by itself and the ID number of the read record. If the primary server keeps in sync all the time, execute this record and update the saved identification number, that is, save the latest synchronized identification number. If not, it means that the primary and standby servers have lost data synchronization, and the standby server needs to restart data synchronization. It is judged whether the identification number stored by the standby server is continuous with the read identification number, that is, it is judged whether the identification number stored by the standby server exists in the synchronization table.

If the standby server restarts, check whether the synchronization identification number saved by it still exists in the synchronization table of the active server after restarting, and if so, only need to continue the real-time synchronization. If the standby server has stopped for a long time, causing the active server to clear the synchronization table, the synchronization identification number saved by the standby server does not exist in the synchronization table, and the initial synchronization is restarted.

The first embodiment of the present invention relates to a method for initial synchronization of databases, which is used for the initial synchronization of the primary server database and the standby server database, wherein the primary server database is provided with a synchronization table including a synchronization identification number, and the specific flow of the method as shown in picture 2.

Step 201: check whether the identification number saved by the standby server exists in the synchronization table, and judge whether to start the initial synchronization accordingly;

For example, in the following three scenarios, the judgment of step 201 is required:

(1) As mentioned above, the identification field adopts the seed attribute provided by the database, which is automatically incremented and can be set to start from 1. The identification number of the standby server can be saved in the database or in the configuration file. The value is initialized to 0. Therefore, when starting for the first time, it must be different from the smallest identification number in the synchronization table of the active server, that is, the identification number saved by the standby server does not exist in the synchronization table, so the initial synchronization will be performed, and then follow-up steps will be performed;

(2) In the process of real-time synchronization, when the standby server executes a record, it updates the identification number saved by itself, and checks the identification number saved by itself and the identification number of the read record before execution. If it is continuous with the identification number of the read record, you only need to continue the real-time synchronization; if not, it means that the primary server has cleared the synchronization table record, then restart the initial synchronization and perform the next steps;

(3) If the standby server restarts, check whether the saved synchronization identification number still exists in the active synchronization table after the restart, and if so, only need to continue the real-time synchronization. If the standby server has stopped for a long time, causing the active server to clear the synchronization table, the synchronization identification number saved by the standby server does not exist in the synchronization table, then restart the initial synchronization and perform subsequent steps.

Step 202: the standby server sends a request message requesting initial synchronization to the active server;

Step 203: After the active server receives the request message sent by the standby server, it updates the synchronization identification number in the synchronization table, backs up the database of the active server, and generates a backup file;

In this step, after receiving the request message sent by the standby server, the active server will clear the data records in the synchronization table before backing up the database. In addition, before backing up the database, in order to prevent any module from modifying the database data during the backup process, the database must be locked. After the locking is successful, the data records in the synchronization table are cleared, the primary server clears the data records and then inserts an empty record, and the value of the synchronization identification field is added to the value before deletion to form an updated synchronization identification number. Next, the primary server backs up the database and generates a backup file. After the backup is completed, the database is unlocked, and a backup success message carrying the new synchronization identification number is sent to the standby server.

Step 204: the standby server obtains the backup file and the updated synchronization identification number;

After receiving the backup success message sent by the active server, the standby server will obtain and save the synchronization identification number, that is, the identification corresponding to the first synchronization record, so as to ensure that the identification number saved by the standby server exists in the active server's in the synchronization table. In addition, the backup file can be transferred from the active server to the standby server through FTP or the file transfer function provided by the operating system. The specific way of transmission can be either a way that the standby server actively obtains the backup file, or a way that the active server actively transmits. And adopting the mode that the standby server actively obtains the backup file can reduce the processing complexity of the active server.

Step 205: the standby server restores the database of the standby server by using the received backup file.

At this point, the initial database synchronization process is completed.

From the time when the database is locked to unlocked, each module of the primary server will be blocked from accessing the database. Generally, the total amount of configuration, alarm, and performance data in the network element database is between 50M and 500M. The hard disk of the OMU server uses high-performance SCSI hard disk or SAS hard disk, with a throughput of more than 30M per second. More than 150M. The time from locking to locking the database is about 0 to 3 seconds, and the total time for the database to be locked is about 3 to 20 seconds. The initial data synchronization time also includes the time for the standby server to obtain backup files and restore the database. The high-speed bandwidth between the active and standby servers is generally 1000MBits/S, and the backup files can be transferred to the standby server within 10 seconds. The database recovery time is shorter than the database backup time, plus the previous time, the entire initial synchronization can be completed within 1 minute, which improves the efficiency of the initial synchronization.

During the entire initial synchronization process, if a certain step is not successfully completed, such as the failure of the active server to return a message or wait for timeout, database locking failure, database backup failure, backup file transfer failure, or standby database recovery failure, etc., All will return to step 201.

Because the method for the initial synchronization of the database in the embodiment of the present invention uses the identification number in the synchronization table to judge whether the initial synchronization is needed and to ensure that the primary and backup servers maintain data consistency after the initial synchronization, specifically adopt the method of database backup and restoration to simplify the server configuration. Modification, database synchronization is not limited by the operating system and database system, which improves the efficiency of initial data synchronization.

From the point of view of the entire database synchronization process, the standby server first synchronizes the initial database of the active server; after completing the initial synchronization, the standby server synchronizes the data modification in the database on the active server in real time. After the initial synchronization is complete, the identification numbers held by the standby server are present in the synchronization table. The second embodiment of the present invention will be described below with reference to the accompanying drawings.

The second embodiment of the present invention relates to a method for real-time database synchronization, which is used for the real-time synchronization of the active server database and the standby server database, wherein the active server database is provided with a synchronization table comprising a synchronization identification number and an SQL statement, and the synchronization The identification number is updated in real time according to the database operation, and the specific flow of the method is shown in FIG. 3 .

Step 301: The primary server judges whether there is a database operation that requires real-time synchronization, and if so, executes steps 302 and 303, and if not, proceeds to the judgment of this step;

The OMU software provides a unified database access interface. Generally, the OMU functions such as configuration, performance statistics, and alarms are implemented by different software modules. Each software module calls the database access interface to operate the database. The database provider provides an optional parameter indicating whether the operation requires synchronization. For example, you can use TRUE to indicate that synchronization is required, FALSE to indicate that synchronization is not required, and the default is TRUE.

Step 302: the primary server copies the SQL statement of the database operation requiring real-time synchronization to the synchronization table;

This statement is a statement for a certain module to access the database and modify the database data. In this way, the standby server can obtain the SQL statement from the synchronization table.

Step 303: the standby server acquires the SQL statement from the synchronization table;

A specific method for obtaining the SQL statement may be that the standby server obtains the user name and password of the database of the primary server, and directly connects to the primary database according to the obtained user name and password to obtain the SQL statement of the synchronization table. In this way, the standby server reads a batch of records from the data synchronization table of the active server.

Step 304: the standby server uses the same SQL to access the database of the standby server.

After obtaining the SQL statement, the standby server accesses its database with the same SQL through the database access interface, that is, executes these records one by one. Since the basic data of the standby server is consistent with that of the active server, the data modification of the two is synchronized, so that the databases of the standby server and the active server are kept in sync.

During real-time synchronization, every time the primary server modifies data, a synchronization table record is added. When the standby server is synchronized in real time, the identification number of the standby server is updated synchronously, and the latest synchronized identification number is saved. After the modification record is completed, the synchronized record in the synchronization table of the active server is deleted. Because when the standby server synchronizes the Nth record of the active server, it only deletes the first N-1 records in the active synchronization table, and the Nth record is kept in the synchronization table, so that the standby server can check itself every time Whether the last record that has been synchronized is in the synchronization table determines whether initial synchronization is required.

The standby server can set a synchronization period, and after completing a period of synchronization, the next period will continue to synchronize in real time. The synchronization period is generally set at the second level, so the data modification on the primary server can be synchronized to the standby server within seconds, achieving real-time synchronization.

Because the database real-time synchronization method provided by the embodiment of the present invention adopts the mode of the standby server to read the SQL statement in the synchronization table record on the active server, directly obtains the data modification of the active server, simplifies the modification to the server configuration, and the database Synchronization is not limited by the operating system and database system, which improves the efficiency of data synchronization.

The third embodiment of the present invention relates to a method for synchronizing databases, which is used for synchronizing the primary server database and the standby server database, wherein the primary server database is provided with a synchronization table including a synchronization identification number and an SQL statement. In this embodiment, the initial synchronization is performed first, and the real-time synchronization is performed after completion as an example. The specific flow of the method is shown in FIG. 4 .

Step 401: check whether the identification number saved by the standby server exists in the synchronization table, and judge whether to start the initial synchronization accordingly;

If initial synchronization is not required, proceed directly to step 406; if initial synchronization is required, proceed to the following steps:

Step 402: the standby server sends a request message requesting initial synchronization to the active server;

Step 403: The active server updates the synchronization identification number in the synchronization table according to the received request message, backs up the database of the active server, and generates a backup file;

Step 404: the backup server obtains the backup file and the updated synchronization identification number;

Step 405: the standby server restores the database of the standby server by using the received backup file.

The specific processes in the steps of initial synchronization above are the same as the corresponding steps in the first embodiment, and will not be repeated here. At this point, the initial synchronization process of the database is completed, because the identification number saved by the standby server exists in the synchronization table at this time, so real-time synchronization is performed after the initial synchronization is completed.

Step 406: The primary server judges whether there is any database operation requiring real-time synchronization;

If there are database operations that need to be synchronized, perform the following steps.

Step 407: the primary server copies the SQL statement of the database operation that needs to be synchronized to the synchronization table;

Step 408: The standby server acquires the SQL statement from the synchronization table;

Step 409: The standby server uses the same SQL to access the database of the standby server.

The specific processes in the steps of the above real-time synchronization are the same as the corresponding steps in the second embodiment, and will not be repeated here. In the process of real-time synchronization, the standby server synchronously updates the identification number of the standby server, and deletes the synchronized records in the synchronization table of the active server after the execution is completed. And every time the standby server executes a record, it will check whether the identification number exists in the synchronization table, and judge whether initial synchronization is required. Because the data is read from the synchronization table, it is only necessary to judge whether the identifier saved by the standby server is continuous with the identifier of the read record.

Because the database synchronization method in this embodiment of the present invention utilizes the synchronization table to realize the initial synchronization and real-time synchronization of the database, simplify the modification of the server configuration, the database synchronization is not restricted by the operating system and the database system, and the efficiency of data synchronization is improved.

The fourth embodiment of the present invention relates to a database synchronization system, and Fig. 5 is a system diagram of the database synchronization, as shown in Fig. 5:

The database synchronization system includes an active server configured with an active server database, and an alternate server configured with an alternate server database. The active server database is provided with a synchronization table including a synchronization identification number and an SQL statement.

Among them, the main server includes:

The backup unit 501 is used to generate a backup file for the backup database after receiving the initial synchronization request sent by the standby server in the initial synchronization phase; the synchronization table unit 502 is used to update the synchronization identification number in the synchronization table in the initial synchronization phase, and also It is used to update the synchronization identification number in the synchronization table in the real-time synchronization stage, and copy the SQL statement of the database operation that needs to be synchronized to the synchronization table; the synchronization table can be set in the synchronization table unit.

Alternate servers include:

The request unit 503 is configured to send a request message requesting initial synchronization to the primary server; the obtaining unit 504 is used to obtain the backup file generated by the backup unit in the primary server during the initial synchronization phase, and the synchronization table unit 502 is in the synchronization table The updated synchronization table is marked and saved; the synchronization unit 505 is used to obtain the backup file from the acquisition unit 504 during the initial synchronization phase, and restores the backup server database with the backup file, and is also used to obtain the backup file from the synchronization table during the real-time synchronization phase. SQL statement, and use the same SQL statement to access the standby server database.

The backup unit of the primary server may further include:

The backup subunit is used to back up the database to generate a backup file after receiving the initial synchronization request sent by the backup server; the response subunit is used to send a backup success message to the backup server after the backup subunit successfully backs up the database; wherein, the backup The success message carries the synchronization identification number after the synchronization table unit is updated in the synchronization table in the initial synchronization phase. Correspondingly, the acquiring unit acquires the synchronization identification number from the backup success message, and acquires the backup file from the backup subunit.

The active server may further include: a locking subunit, configured to lock the database of the active server before the active server backs up the database.

The system can also include a real-time synchronization judging unit, which is used to send a synchronization command to the synchronization unit when judging that there is a database operation that requires real-time synchronization according to the parameters provided by the database access interface; the synchronization unit obtains from the synchronization table according to the synchronization command. SQL statement, and use the same SQL statement to access the standby server database to complete real-time data synchronization.

In the data synchronization process, the request unit 503 determines whether initial synchronization is needed according to whether the synchronization identification number preserved by the acquisition unit 504 exists in the synchronization table of the synchronization table unit 502; if initial synchronization is required, then send a request message requesting initial synchronization Backup unit 501 for the active server. After receiving the request message sent by the standby server, the backup unit 501 backs up the database and generates a backup file. The synchronization table unit 502 forms an updated synchronization identification number in the synchronization table. After the backup is successful, the obtaining unit 504 obtains the backup file, and obtains the synchronization table identifier and saves it. The synchronization unit 505 uses the backup file acquired by the acquisition unit 504 to restore the database of the backup server, thereby realizing the initial synchronization of the database.

When the primary server judges that there is a database operation that needs to be synchronized in real time, the synchronization table unit 502 copies the SQL statement of the database operation that needs to be synchronized to the synchronization table, and updates the synchronization identifier in the synchronization table unit. During this process, every time the primary server modifies data, a synchronization table record is added. The synchronization unit 505 obtains the SQL statement from the synchronization table, and uses the same SQL to access the standby server database, so as to realize real-time data modification synchronization. When the standby server synchronizes the Nth record of the active server, it will only delete the first N-1 records in the same table of the active server, and the Nth record will be kept in the synchronization table, so that the standby server can check whether it has synchronized each time. Whether the last record of is in the synchronization table determines whether initial synchronization is required.

However, in this embodiment, judging whether there is a database operation requiring real-time synchronization can be realized by a real-time synchronization judging unit, which judges whether there is a database operation requiring real-time synchronization according to the parameters provided by the database access interface. If the judgment result is yes, a synchronization command is sent to the synchronization unit 505, and the synchronization unit 505 obtains the SQL statement from the synchronization table according to the command, and uses the same SQL statement to access the standby server database.

Because the database synchronization system of this embodiment of the present invention utilizes the synchronization table to realize the initial synchronization and real-time synchronization of the database, simplify the modification of the server configuration, the database synchronization is not restricted by the operating system and the database system, and the efficiency of data synchronization is improved.

The specific embodiments described above have further described the purpose, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a demonstration of the idea of the present invention, and is not used to limit the present invention scope of protection. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention shall be included within the protection scope of the present invention.

Claims (16)

1, a kind of database synchronization method is used for main initial synchronisation with server database and standby server database, it is characterized in that, described main with being provided with the synchronous meter that comprises sync id number in the server database, this method may further comprise the steps:

Standby server sends the initial synchronisation request to main with server;

Main with server according to the described initial synchronisation request that receives, in described synchronous meter, upgrade sync id number, and back up the described main server database of using, generation backup file;

Standby server obtains sync id number and the described backup file after the described renewal, with described backup file recovery standby server database.

2, method according to claim 1 is characterized in that, described main with server according to the described initial synchronisation request that receives, the step of renewal sync id number comprises in described synchronous meter:

Main receive described initial synchronisation request with server after, empty the data recording in the synchronous meter and insert null record, form the sync id number after upgrading.

3, method according to claim 2 is characterized in that, described master with after the server database success, sends the backup success message of the sync id number after carrying described renewal with the server backup master to standby server;

The step that described standby server obtains the sync id number after the described renewal is:

Standby server obtains and preserves the sync id number after the described renewal from described backup success message.

4, according to each described method of claim 1 to 3, it is characterized in that, further be included in and lead with the server backup master with before the server database, to the main step that locks with server database.

5, the main seed attribute that increases progressively automatically that provides with server database of sync id in the described synchronous meter number employing according to each described method of claim 1 to 3, is provided.

6, a kind of database synchronization method, be used for main the synchronous in real time of server database and standby server database of using, described main with being provided with the synchronous meter that comprises sync id number and SQL statement in the server database, described sync id is number according to the database manipulation real-time update, it is characterized in that this method may further comprise the steps:

The master needs the SQL statement of data in synchronization storehouse operation in described synchronous meter with server replicates;

Standby server is directly connected to the master with on the server database, obtains described SQL statement from described synchronous meter, and visits the standby server database with described SQL statement.

7, method according to claim 6 is characterized in that, before described master needs data in synchronization storehouse operation with server replicates SQL statement was in the described synchronous meter, described method also comprised the steps:

The master according to the parameter that database access interface provides, need to have judged whether the operation of real-time data in synchronization storehouse with server.

8, method according to claim 7 is characterized in that, described standby server obtains described SQL statement from described synchronous meter step comprises:

Described standby server obtains main username and password with server database;

Be connected to the master according to the described username and password that obtains and use server, obtain the described SQL statement of synchronous meter.

9, a kind of database synchronization method, be used for main with server database and standby server database synchronously, described main with being provided with the synchronous meter that comprises sync id number and SQL statement in the server database, it is characterized in that this method comprises:

A, standby server check whether the identification number of its preservation is present in the described synchronous meter, if there is no, then execution in step B, C, D, if exist, then execution in step E, F;

B, standby server send the initial synchronisation request to main with server;

C, main with server according to the described initial synchronisation request that receives, in described synchronous meter, upgrade sync id number, back up described master and uses server database, the generation backup file;

D, standby server obtain sync id number and the described backup file after the described renewal, recover the standby server database with described backup file;

E, master need the SQL statement of data in synchronization storehouse operation in described synchronous meter with server replicates;

F, standby server obtain described SQL statement from described synchronous meter, and visit the standby server database with described SQL statement.

10, method according to claim 9 is characterized in that, described main with server according to the described initial synchronisation request that receives, the step of renewal sync id number comprises in described synchronous meter:

Main receive described initial synchronisation request with server after, empty the data recording in the synchronous meter and insert null record, form the sync id number after upgrading;

Main with the server backup master with the server database success after, send the backup success message of the sync id number after carrying described renewal to standby server.

11, method according to claim 9 is characterized in that, also comprises before step e:

The main parameter that provides according to database access interface with server, judging has the operation of the real-time data in synchronization of needs storehouse.

12, method according to claim 9 is characterized in that, described standby server obtains described SQL statement from described synchronous meter step comprises:

Standby server obtains main username and password with server database, is connected to the master with on the server according to described username and password, obtains the described SQL statement of synchronous meter.

13, a kind of database synchronous system comprises that disposing main master with server database uses server, and the standby server that disposes the standby server database, it is characterized in that,

Described main with being provided with the synchronous meter that comprises sync id number and SQL statement in the server database;

Described master comprises with server:

Backup units was used in the initial synchronisation stage, and after receiving the initial synchronisation request that standby server sends, backup is main to generate backup file with server database;

The synchronous meter unit was used in the initial synchronisation stage, upgraded sync id number in described synchronous meter, also was used at real-time synchronous phase, upgraded the sync id number in the described synchronous meter, duplicate need the operation of data in synchronization storehouse SQL statement in synchronous meter;

Described standby server comprises:

Request unit is used for sending the initial synchronisation request to main with server;

Acquiring unit was used in the initial synchronisation stage, obtained the backup file that described backup units generates, and the sync id of synchronous meter unit after upgrading in described synchronous meter number;

Lock unit, be used in the initial synchronisation stage, obtain described backup file from described acquiring unit, and recover the standby server database with this backup file, also be used at real-time synchronous phase, from described synchronous meter, obtain described SQL statement, and visit the standby server database with described SQL statement.

14, system according to claim 13 is characterized in that, described main backup units with server further comprises:

The backup subelement is used for after receiving the initial synchronisation request that standby server sends, and backup is main to generate backup file with server database;

The response subelement is used for sending the backup success message to standby server after described backup subelement backup master is with the server database success;

Described backup success message carries the sync id number after initial synchronisation stage described synchronous meter unit upgrades in described synchronous meter;

Described acquiring unit obtains the sync id number after the described renewal from described backup success message, obtain described backup file from described backup subelement.

15, system according to claim 13 is characterized in that, described master further comprises with server:

The subelement that locks, be used for main with the server backup master with before the server database, lock with server database to main.

16, system according to claim 13, it is characterized in that this system also comprises main with the in real time synchronous judging unit in the server, and the parameter that provides according to database access interface is provided, judge when the operation of the real-time data in synchronization of needs storehouse is arranged, send synch command to described lock unit;

Described lock unit obtains described SQL statement according to described synch command from described synchronous meter, and visits the standby server database with described SQL statement.

Images