Distributed database table structure grayscale upgrade method, device and system
By establishing temporary tables in distributed databases and performing data migration, the problems of long downtime and complex operations of database table structure upgrades are solved, and rapid and general table structure upgrades are achieved, which improves business continuity and development efficiency.
Patent Information
- Application Number
- CN202110183548.4
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-02-10
- Publication Date
- 2024-11-05
- Estimated Expiration
- 2041-02-10
AI Technical Summary
The existing database table structure upgrade method has problems such as too many distributed database nodes that lead to too long downtime, requiring developers to write data migration programs, and complex operation steps and not universal.
By determining the target database and source table to be upgraded according to the table structure change instructions, establishing temporary tables and migrating the source table data to temporary tables through data migration, replacing them with source tables, and achieving rapid changes and upgrades of the target database table structure without the need for Downtime, reduce developer participation and highly available library construction.
Shorten the downtime of table structure changes, from minute or hour to second level, improves the continuity of business operations, and implements a general distributed database table structure upgrade solution, reducing development workload and operation and maintenance difficulties. .
Smart Images

Figure CN112783868B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of database upgrade, and in particular to a method, device and system for gray-scale upgrade of a distributed database table structure. Background Art
[0002] With the large-scale promotion and use of distributed databases, when changing and upgrading the table structure of the database, it is necessary to ensure the continuity of business operations and reduce the downtime of the database change to reduce the business impact. When changing and upgrading the existing database table structure, different applications need to combine their actual business scenarios, and the application developers need to formulate a table structure change plan that is only applicable to this application. For example, the traditional MySQL database table structure upgrade method is to separate a standby database from the synchronized MySQL master database and the standby database, upgrade the table structure of the standby database, rebuild the master-slave synchronization high-availability architecture, and then stop the business of the master database, migrate the incremental data from the master database to the standby database through the migration program, and finally switch the business to the standby database, and let the standby database serve as the new master database to provide database services to the outside world. There are several problems with this type of database table structure upgrade: first, there are too many distributed database nodes, and the business downtime is too long, requiring minutes or even hours of downtime; second, developers are required to write incremental data migration programs; third, the backup database needs to rebuild a high-availability architecture; fourth, the operation steps are numerous and complex, and problems that arise in the middle will increase downtime, and the length of downtime is uncontrollable; fifth, the upgrade plan is only applicable to this upgrade of this application and is not universal. Summary of the invention
[0003] In order to solve at least one of the above problems, one object of the present invention is to provide a distributed database table structure grayscale upgrade method to achieve rapid upgrade of database table structure and reduce downtime of database table structure upgrade. Another object of the present invention is to provide a distributed database table structure grayscale upgrade device. Another object of the present invention is to provide a distributed database table structure grayscale upgrade system. Another object of the present invention is to provide a computer device. Another object of the present invention is to provide a readable medium.
[0004] In order to achieve the above objectives, the present invention discloses a method for gray-scale upgrading of a distributed database table structure, comprising:
[0005] Determine all target databases, source tables, and grayscale upgrade priorities to be upgraded based on the table structure change instructions;
[0006] A temporary table with a changed table structure is established in the target database with the highest priority for grayscale upgrade, and the table data of the source table to be upgraded is migrated to the temporary table by data migration, and the temporary table is replaced by the source table, and the target database table structure change is completed;
[0007] When the target database table structure with the highest priority for the grayscale upgrade is changed, the upgrade success information is fed back to the application server.
[0008] Preferably, further comprising:
[0009] Based on preset conditions, a temporary table with upgraded table structure is established in the target database where the table structure has not been changed. The table data of the source table to be upgraded is migrated to the temporary table through data migration and the temporary table is replaced with the source table. The table structure upgrade of the target database where the table structure has not been changed is completed.
[0010] Preferably, determining all target databases and source tables to be upgraded according to the table structure change instruction specifically includes:
[0011] Determine the database information and source table information of the target database according to the table structure change instruction;
[0012] The IP address of the database is determined according to the database information, and the table data of the source table in the database is acquired according to the source table information.
[0013] Preferably, the step of establishing a temporary table after the table structure is changed in the target database with the highest priority for grayscale upgrade specifically includes:
[0014] Determine the target table structure after the table structure is changed according to the table structure change instruction;
[0015] An empty table is established as the temporary table according to the target table structure.
[0016] Preferably, migrating the source table data to the temporary table by means of data migration and replacing the temporary table with the source table specifically includes:
[0017] Determine data migration rules according to the table structure change instruction;
[0018] Migrating the data in the source table to be upgraded to the temporary table according to the data migration rule;
[0019] After the data migration is completed, the temporary table is replaced with the source table.
[0020] Preferably, migrating the data in the source table to be upgraded to the temporary table according to the data migration rule specifically includes:
[0021] Determine, according to the data migration rule, the assignment rule of the newly added field of the temporary table relative to the source table to be upgraded, the table data processing rule of the source table to be upgraded, and the fields to be migrated;
[0022] Obtaining changed data from the table data of the field to be migrated in the source table according to the table data processing rule;
[0023] The changed data is migrated to the position corresponding to the temporary table and the newly added fields in the temporary table are assigned values.
[0024] Preferably, the method further comprises, before replacing the temporary table with the source table:
[0025] Obtaining a log file of the target database;
[0026] Parsing the log file to obtain data records;
[0027] Table data is formed according to the data records and written into the temporary table.
[0028] Preferably, replacing the temporary table with the source table specifically includes:
[0029] Stop all data processing operations on the source table to be upgraded;
[0030] Change the table name of the source table to be upgraded to a temporary name;
[0031] The table name of the temporary table is changed to the table name of the source table to be upgraded, and the temporary table is replaced by the source table.
[0032] Preferably, the target database is a master database, and the method further comprises:
[0033] When the database table structure in the main database is changed, the source table after the table structure in the main database is changed is backed up to the standby database.
[0034] The present invention also discloses a distributed database table structure grayscale upgrade device, comprising:
[0035] The table structure change module is used to determine all target databases, source tables and grayscale upgrade priorities to be upgraded according to the table structure change instructions;
[0036] A data migration module is used to establish a temporary table after the table structure is changed in the target database with the highest priority for grayscale upgrade, migrate the table data of the source table to be upgraded to the temporary table through data migration, and replace the temporary table with the source table, so that the target database table structure change is completed;
[0037] The management and control module is used to feedback the upgrade success information to the application server after the target database table structure with the highest priority for the grayscale upgrade is changed.
[0038] The present invention also discloses a distributed database table structure grayscale upgrade system, including an application server, a distributed database and a distributed database table structure grayscale upgrade device;
[0039] The application server transmits a table structure change instruction to the distributed database table structure grayscale upgrade device, and the distributed database table structure grayscale upgrade device is used to determine all target databases, source tables and grayscale upgrade priorities to be upgraded according to the table structure change instruction; establish a temporary table after the table structure change in the target database with the highest grayscale upgrade priority, migrate the table data of the source table to be upgraded to the temporary table through data migration, and replace the temporary table with the source table, and the target database table structure change is completed; when the target database table structure change with the highest grayscale upgrade priority is completed, feedback upgrade success information to the application server.
[0040] The present invention also discloses a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor.
[0041] When the processor executes the program, the method described above is implemented.
[0042] The present invention also discloses a computer readable medium having a computer program stored thereon.
[0043] When the program is executed by a processor, the method described above is implemented.
[0044] The present invention can determine all target databases to be upgraded, source tables to be upgraded in the target databases, and grayscale upgrade priorities according to the table structure change instruction. Then, a temporary table after the table structure change is established in the target database with the highest grayscale upgrade priority, and the table data in the source table to be upgraded is migrated to the temporary table by data migration. After the data migration is completed, the temporary table obtained by data migration is replaced with the source table after the table structure change is obtained by the source table to be upgraded, so as to realize the rapid change and upgrade of the target database table structure. After the target database table structure change with the highest grayscale upgrade priority is completed, the upgrade success information is fed back to the application server so that the application server can give priority to providing data processing services to the application server after the key target database table structure with the highest grayscale upgrade priority is upgraded. The hierarchical database table structure grayscale upgrade method can shorten the upgrade time, and prevent the problem of long downtime of the distributed database and reduced user experience caused by the long switching time of some database source tables and temporary tables. The present invention obtains the source table after the table structure is changed by establishing a temporary table and migrating data. There is no need to shut down during the data migration process. It is only necessary to stop all data processing operations on the source table in the process of replacing the temporary table with the source table. Therefore, the database table structure of the present invention is fast to change, which greatly shortens the downtime of the table structure change. The downtime can be shortened from minutes or hours to seconds, which greatly improves the continuity of business operations. In addition, the database table structure change process of the present invention does not require the participation of the application server, nor does it require developers to write migration programs, does not require the reconstruction of the database high-availability backup library, does not require manual data migration, and can achieve the table structure change and upgrade of all target databases by setting a one-time operation of the table structure change instruction, reducing the development workload and operation and maintenance difficulty of developers. BRIEF DESCRIPTION OF THE DRAWINGS
[0045] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.
[0046] Figure 1 A flowchart showing a specific embodiment of the method for gray-scale upgrading of a distributed database table structure of the present invention;
[0047] Figure 2 A flowchart showing a specific embodiment S100 of the method for gray-scale upgrading of a distributed database table structure of the present invention;
[0048] Figure 3 A flowchart of establishing a temporary table is shown in S200, a specific embodiment of the method for gray-scale upgrading of a distributed database table structure of the present invention;
[0049] Figure 4 A schematic diagram showing a specific example of the method for gray-scale upgrading of a distributed database table structure of the present invention;
[0050] Figure 5 A flowchart showing a specific embodiment S200 of the method for gray-scale upgrading of a distributed database table structure of the present invention, wherein data migration is used to achieve table structure upgrading;
[0051] Figure 6 A flowchart showing a specific embodiment S220 of the method for gray-scale upgrading of a distributed database table structure of the present invention;
[0052] Figure 7 A flowchart showing incremental data migration in a specific embodiment of the method for gray-scale upgrading of a distributed database table structure of the present invention is shown;
[0053] Figure 8 A flowchart showing the switching between the source table and the temporary table in S200 of a specific embodiment of the method for gray-scale upgrading of a distributed database table structure of the present invention is shown;
[0054] Fig. 9 An upgrade flow chart showing a specific example of the method for gray-scale upgrade of a distributed database table structure of the present invention, which implements a rapid upgrade of a distributed MySQL database table structure based on data migration technology;
[0055] Fig.10 A flowchart showing the switching between the source table to be upgraded and the temporary table for rapid upgrading of the database table structure in a specific example of the method for gray-scale upgrading of the distributed database table structure of the present invention;
[0056] Fig.11 A structural diagram showing a specific embodiment of the device for gray-scale upgrading of a distributed database table structure of the present invention;
[0057] Fig.12 A schematic diagram showing the structure of a computer device suitable for implementing an embodiment of the present invention is shown. DETAILED DESCRIPTION
[0058] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0059] The table structure change and upgrade of the distributed database currently used in the application needs to upgrade the table structure in the standby database first, and rebuild the master-slave synchronization high-availability architecture. When upgrading, it is necessary to stop the business of the main database, migrate the incremental data from the main database to the standby database through the migration program, and finally switch the business to the standby database, which will provide database services to the outside world as the new main database. Therefore, the current database table structure change and upgrade have the following problems: First, there are too many distributed database nodes, and the business downtime is too long, requiring minutes or even hours of downtime; second, developers are required to write incremental data migration programs; third, the standby database needs to rebuild the high-availability architecture; fourth, the operation steps are many and complicated, and problems in the middle will increase the downtime, and the downtime is uncontrollable; fifth, the upgrade plan is only applicable to this upgrade of this application and is not universal. In view of the many problems existing in the traditional database table structure upgrade, the present invention can determine all target databases to be upgraded and the source tables to be upgraded in the target database according to the table structure change instruction. Then, a temporary table after the table structure change is established in the target database, and the table data in the source table to be upgraded is migrated to the temporary table by data migration. After the data migration is completed, the temporary table obtained by data migration is replaced with the source table to be upgraded to obtain the source table after the table structure change, so as to realize the rapid change and upgrade of the target database table structure. The present invention obtains the source table after the table structure change by establishing a temporary table and data migration. There is no need to stop the system during the data migration process. It is only necessary to stop all data processing operations on the source table during the process of replacing the temporary table with the source table. Therefore, the database table structure of the present invention changes quickly, which greatly shortens the downtime of the table structure change. In summary, the present invention proposes a grayscale upgrade method for the table structure of a distributed database. On the one hand, it can realize automatic upgrade of all database nodes in seconds to reduce business downtime; on the other hand, it realizes a general distributed database table structure upgrade solution. The upgrade process is transparent to developers, and developers do not need to write data migration programs, and do not need to rebuild the high-availability environment between the main database and the backup database. Furthermore, the present invention can shorten the upgrade time through the hierarchical database table structure grayscale upgrade method, and prevent the problem that the switching time of some database source tables and temporary tables is too long, resulting in long downtime of the distributed database and reduced user experience.
[0060] According to one aspect of the present invention, this embodiment discloses a method for grayscale upgrading of a distributed database table structure. Figure 1 As shown, in this embodiment, the method includes:
[0061] S100: Determine all target databases, source tables and grayscale upgrade priorities to be upgraded according to the table structure change instruction.
[0062] S200: A temporary table with a changed table structure is established in the target database with the highest priority for grayscale upgrade, and table data of the source table to be upgraded is migrated to the temporary table by data migration, and the temporary table is replaced by the source table, and the target database table structure change is completed.
[0063] S300: When the target database table structure with the highest priority for the grayscale upgrade is changed, the upgrade success information is fed back to the application server.
[0064] The present invention can determine all target databases to be upgraded, source tables to be upgraded in the target databases, and grayscale upgrade priorities according to the table structure change instruction. Then, a temporary table after the table structure change is established in the target database with the highest grayscale upgrade priority, and the table data in the source table to be upgraded is migrated to the temporary table by data migration. After the data migration is completed, the temporary table obtained by data migration is replaced with the source table after the table structure change is obtained by the source table to be upgraded, so as to realize the rapid change and upgrade of the target database table structure. After the target database table structure change with the highest grayscale upgrade priority is completed, the upgrade success information is fed back to the application server so that the application server can give priority to providing data processing services to the application server after the key target database table structure with the highest grayscale upgrade priority is upgraded. The hierarchical database table structure grayscale upgrade method can shorten the upgrade time, and prevent the problem of long downtime of the distributed database and reduced user experience caused by the long switching time of some database source tables and temporary tables. The present invention obtains the source table after the table structure is changed by establishing a temporary table and migrating data. There is no need to shut down during the data migration process. It is only necessary to stop all data processing operations on the source table in the process of replacing the temporary table with the source table. Therefore, the database table structure of the present invention is fast to change, which greatly shortens the downtime of the table structure change. The downtime can be shortened from minutes or hours to seconds, which greatly improves the continuity of business operations. In addition, the database table structure change process of the present invention does not require the participation of the application server, nor does it require developers to write migration programs, does not require the reconstruction of the database high-availability backup library, does not require manual data migration, and can achieve the table structure change and upgrade of all target databases by setting a one-time operation of the table structure change instruction, reducing the development workload and operation and maintenance difficulty of developers.
[0065] In a preferred embodiment, the method further comprises:
[0066] S400: Based on preset conditions, a temporary table with upgraded table structure is established in the target database where the table structure is not changed. Table data of the source table to be upgraded is migrated to the temporary table through data migration and the temporary table is replaced with the source table. The table structure upgrade of the target database where the table structure is not changed is completed.
[0067] It is understandable that the grayscale upgrade priority may include multiple preset levels, among which the target database with the highest grayscale upgrade priority can be upgraded first. After the table structure of the target database with the highest grayscale upgrade priority is changed, the upgrade success information is fed back to the application server. On the one hand, a part of the target database can be upgraded first, and the operation of the upgraded application server can be monitored. If there is a problem in the operation after the table structure is upgraded, the impact range can be reduced and it is convenient for the application server to troubleshoot in time. On the other hand, the target database with the highest grayscale upgrade priority is upgraded first. Compared with the method of ending the shutdown and restoring the service to the application server after all target databases are upgraded, this preferred implementation method can further shorten the downtime. In addition, after receiving the upgrade success information, the application server first sends the data processing task to the upgraded target database, and when the target database without the table structure change is also upgraded, it resumes the normal operation of sending the data processing task to all target databases. At this time, even if the target database with a lower grayscale upgrade priority is shut down during the upgrade, it will not affect the execution of the normal data processing tasks of the application server.
[0068] It should be noted that the grayscale upgrade priority includes multiple levels that can be flexibly set according to actual needs. For example, the grayscale upgrade priority may include the highest level, high level, medium level and low level. When determining the priority of the target database, it can be determined according to the priority set in the table structure change instruction, or it can be determined according to the importance of the data stored in the source table to be upgraded in each target database and whether the source tables to be upgraded in multiple target databases are the same. For example, if the source tables to be upgraded in multiple target databases are the same, it can be determined that one or more of them have a higher priority and provide services for priority upgrade. Those skilled in the art can set the grayscale upgrade priority and determine the priority of the target database according to actual conditions, which will not be repeated here. It should also be noted that the preset condition can be a table structure change instruction based on the transmission of the application server to start the continued table structure upgrade, or it can be a preset time for restarting the table structure change upgrade of the database without changing the table structure. For example, the application idle time can be preset, and for the target database that does not prioritize the table structure change, the upgrade can continue during the application idle time. Those skilled in the art can set the preset conditions according to actual conditions, and the present invention is not limited to this.
[0069] In a preferred embodiment, Figure 2 As shown, the step S100 determines all target databases and source tables to be upgraded according to the table structure change instruction, specifically including:
[0070] S110: Determine the database information and source table information of the target database according to the table structure change instruction.
[0071] S120: Determine the IP address of the database according to the database information, and obtain table data of a source table in the database according to the source table information.
[0072] It should be noted that currently, applications that provide corresponding services to users need to set up application servers to implement the business logic of the applications. When the application server is running, it needs to perform tasks such as storing business data in the distributed database, adding, deleting, modifying, and starting table structure upgrades in the distributed database. Distributed databases can provide data processing services such as sub-library and sub-table, data storage, data query and modification to application servers. Distributed databases usually include multiple distributed nodes, with one or more databases set up in each node. Usually, each node is equipped with two databases, a main database and a backup database. After the main database provides data processing services for the data processing tasks transmitted by the application server, it will back up the updated data to the backup database. Therefore, when the main database fails, it can switch to the backup database to provide data processing services to the application server, avoiding the problem of abnormal application operation caused by the inability of the main database to provide services to the application server when it fails, thereby improving the operation stability of the application server and the ability to provide services continuously.
[0073] In this preferred embodiment, it can be understood that the table structure change instruction can be input by the developer through the human-computer interaction device, or can be transmitted by the application server, or can be pre-set in other ways. The table structure change instruction can be obtained in a variety of ways, and the present invention is not limited to this.
[0074] Furthermore, according to the received or preset table structure change instruction, database information such as the IP address, database number, and database type of the target database that needs to upgrade the table structure can be obtained, and source table information such as the table name and file size of the source table to be upgraded can also be obtained. Among them, the IP address of the target database can be obtained according to the database information, and the target database can be directly accessed according to the IP address to perform data processing such as adding, deleting, modifying, and checking the data in the target database. Therefore, the target database can be accessed through the IP address, and the table data in the source table can be obtained according to the source table information. A temporary table can be established according to the table data through data migration. The database does not need to suspend service during the process of migrating data to the temporary table, thereby greatly reducing the downtime required for upgrading the source table structure. The database number can be pre-set according to demand, and the database type can include common database types such as MySql, SQLServer, Oracle, Sybase, and DB2.
[0075] In a preferred embodiment, Figure 3 As shown, the step S200 of establishing a temporary table after the table structure is changed in the target database with the highest priority for grayscale upgrade specifically includes:
[0076] S211: Determine the target table structure after the table structure is changed according to the table structure change instruction.
[0077] S211: Create an empty table as the temporary table according to the target table structure.
[0078] In this preferred embodiment, the table structure change instruction further includes table structure change conditions, and the target table structure can be obtained according to the table structure change conditions. The table structure change conditions can be expressed in various forms, and the change conditions can be the change of the target table structure relative to the source table or directly give various parameters such as the fields of the target table structure. Therefore, according to the table structure change conditions in the table structure change instruction, an empty table can be established in the target database, and the empty table has the target table structure and can be used as a temporary table for data migration. For example, Figure 4 As shown, in a specific example, the database uses a distributed MySQL database, and the distributed MySQL database includes n nodes of MySQL node 1, MySQL node 2...MySQL node n. During the table structure upgrade process, the MySQL main database of MySQL node 1 in the distributed database can be accessed through the database IP address. Further, two source tables, Table A and Table B, that need to be upgraded by changing the table structure in the MySQL main database are determined. According to the table structure change instruction, the target table structure of Table A and Table B after the table structure change is respectively determined, and two empty tables, Table A' and Table B', are established as temporary tables of Table A and Table B.
[0079] In a preferred embodiment, Figure 5 As shown, in S200, migrating the source table data to the temporary table by data migration and replacing the temporary table with the source table may specifically include:
[0080] S210: Determine data migration rules according to the table structure change instruction.
[0081] S220: Migrate the data in the source table to be upgraded to the temporary table according to the data migration rule.
[0082] S230: After the data migration is completed, the temporary table is replaced with the source table.
[0083] In this preferred embodiment, it is understandable that after the temporary table is established, the existing data in the source table to be upgraded needs to be migrated to the corresponding position of the temporary table by data migration, that is, the existing data in the source table to be upgraded is migrated to the temporary table. It should be noted that the data migration refers to migrating the data in the source table to be upgraded to the temporary table, and the data migration includes the process of copying the data and filling it into the temporary table, and also includes the process of processing the existing data in the source table to be upgraded based on the table structure change instruction and filling it into the temporary table during the migration process.
[0084] Specifically, the data migration rules can be determined according to the table structure change instruction, that is, according to the data migration rules, it can be determined what data processing the existing data in the source table to be upgraded needs to undergo before it can be written into the temporary table. Among them, common data processing can include multiple processing methods such as migration only, migration after changing the storage format, and migration based on preset functions. When all the existing data (existing data) in the source table to be upgraded are written into the temporary table by means of data migration, the temporary table can be replaced with the source table to complete the table structure change upgrade. The replaced source table already has the target table structure, so when the data processing task of the application server is subsequently received, the incremental data can be directly written to the replaced source table. Therefore, in this preferred embodiment, through the method of data migration and temporary table switching, there is no need to perform complex upgrade processes such as changing the table structure in the standby database and then switching between the main database and the standby database, and during the data migration process, the main database can still provide services to the application server normally, and only temporarily shuts down when the temporary table is switched to the source table. The downtime is short, and the user can basically achieve the effect of not being perceived by the user, thereby improving the ability of the application to provide continuous services and improving the user experience. For example, in the above specific example, if Figure 4 As shown, after creating temporary tables A' and B' for the source tables A and B to be upgraded in the main database, data migration from Table A to Table A' and from Table B to Table B' can be started. The existing data in Table A and Table B are migrated to Table A' and Table B' based on the data migration rules corresponding to Table A and Table B respectively. When all the existing data in Table A and Table B are migrated, Table A can be replaced by Table A', and Table B can be replaced by Table B'. Then, the replaced Table A and Table B have the target table structure after the table structure change, thereby realizing the table structure upgrade.
[0085] In a preferred embodiment, Figure 6 As shown, in S220, migrating the data in the source table to be upgraded to the temporary table according to the data migration rule may specifically include:
[0086] S221: Determine, according to the data migration rule, the assignment rule of the temporary table relative to the newly added field of the source table to be upgraded, the table data processing rule of the source table to be upgraded, and the fields to be migrated.
[0087] S222: obtaining changed data from the table data of the field to be migrated in the source table according to the table data processing rule.
[0088] S223: Migrate the changed data to the position corresponding to the temporary table and assign values to the newly added fields in the temporary table.
[0089] It is understandable that upgrading the table structure of the source table to be upgraded requires changing the table structure such as the fields of the source table. In this preferred embodiment, in order to enable the stock data in the source table to be upgraded to be migrated to the temporary table, the newly added fields of the source table to be upgraded and the assignment rules of the newly added fields, the table data processing rules for the stock data in the source table to be upgraded before migrating to the temporary table, and the fields to be migrated are pre-set in the data migration rules. Among them, according to the fields to be migrated set in the data migration rules, it can be determined which fields of the source table to be upgraded need to be migrated to the temporary table, and which fields do not need to be migrated to the temporary table, so that the table data to be migrated to the temporary table is written into the temporary table after data processing based on the table data processing rules, and the newly added fields in the temporary table are assigned. Among them, the assignment of the newly added fields can be all set to default values, or it can be obtained according to the table data in the source table to be upgraded. The data corresponding to the newly added fields is assigned, and the present invention is not limited to this.
[0090] In a preferred embodiment, Figure 7 As shown, the method further includes before replacing the temporary table with the source table:
[0091] S231: Obtain the log file of the target database.
[0092] S232: Parse the log file to obtain data records.
[0093] S233: Form table data according to the data record and write into the temporary table.
[0094] It is understandable that after all the existing data in the source table to be upgraded is migrated to the temporary table, the temporary table is replaced with the source table. This table structure upgrade method requires entering a shutdown state after data migration. In the shutdown state, the main database no longer provides services to the application server. Shutdown for upgrades during the peak application access period may result in a decrease in user experience or application crashes. In this preferred embodiment, after the data migration is completed, the log file of the target database continues to be obtained, and data records are parsed from the log file. Newly added table data, that is, incremental data, can be obtained based on the data record, and the incremental data can be written into the temporary table, while ensuring that the target database continues to provide services to the application server, while keeping the table data in the temporary table consistent with the data in the source table to be upgraded. Therefore, when the application server is idle, the source table to be upgraded can be replaced with a temporary table to obtain the source table after the table structure is changed, thereby improving the ability and reliability of the application server to provide continuous services. For example, for Figure 4 In the specific example shown, the Binlog log can be read from the Binlog log file of the MySQL main library, and then the Binlog log is parsed into data records, and table data is formed according to the data records and written into the temporary table.
[0095] In a preferred embodiment, Figure 8 As shown, replacing the temporary table with the source table in S200 may specifically include:
[0096] S241: Stop all data processing operations on the source table to be upgraded.
[0097] S242: Change the table name of the source table to be upgraded to a temporary name.
[0098] S243: Change the table name of the temporary table to the table name of the source table to be upgraded, and replace the temporary table with the source table.
[0099] It is understandable that when replacing the source table to be upgraded with a temporary table, it is necessary to perform a downtime upgrade. Specifically, all data processing tasks of the target database can be stopped, that is, all data processing operations for the source table to be upgraded can be stopped, and the addition, deletion, modification and query of the table data in the source table to be upgraded can be stopped. Then the table name of the source table to be upgraded is changed to a temporary name, so that the table name of the temporary table can be changed to the table name of the source table to be upgraded, and the temporary table is replaced with the source table to achieve the table structure change upgrade of the source table. In this preferred embodiment, the data in the source table to be upgraded and the temporary table are consistent, so it is only necessary to replace the table name of the temporary table with the table name of the source table to be upgraded. At this time, the table structure in the source table has been changed to the target table structure, and the upgraded data processing service can be provided to the application server. The downtime during the upgrade is short, the operation is simple, and no complex manual upgrade operations by developers are required.
[0100] In a preferred embodiment, the target database is a main database, and the method further comprises:
[0101] S500: After the database table structure in the main database is changed, the source table after the table structure in the main database is changed is backed up to the standby database.
[0102] It is understandable that each node of a distributed database usually includes a master database and a standby database (backup database). After the source table in the master database is upgraded with table structure changes, the upgraded source table is further backed up to each standby database, and the source table in the standby database is upgraded. Figure 4 In the specific example shown, a main database and three standby databases are provided in MySQL node 1. After the table structures of Table A and Table B in the main database are changed, Table A and Table B with the changed table structures are backed up to the three standby databases respectively, thereby improving the service capability of the database node.
[0103] The present invention is further described below by using a specific example. Fig. 9 The upgrade flow chart of the present invention for realizing the rapid upgrade of the distributed MySQL database table structure based on the data migration technology is shown. Fig. 9 As shown, the method comprises the following steps:
[0104] Step S201: The application server enters the database information of the distributed MySQL database node to be upgraded, the source table information of the source table to be upgraded, the target table structure and the table structure change instruction of the data migration rule in the management and control module, and starts the table structure upgrade task.
[0105] Step S202: the table structure changing module creates a temporary table on the main database of the distributed MySQL target database with reference to the target table structure according to the IP address of the target database in the distributed MySQL database node and the source table to be upgraded.
[0106] Step S203: The data migration module automatically generates, according to the data migration rules, stock data migration tasks and incremental data migration tasks from source tables to temporary tables of all to-be-upgraded node MySQL databases in the distributed MySQL database cluster.
[0107] Step S204: Automatically start the stock data migration task from the source table to the temporary table of all MySQL node target databases until the stock data migration is completed.
[0108] Step S205: After the data is leveled, the incremental data migration task from the source table to the temporary table of all MySQL node target databases is automatically started, and the target database continues to run.
[0109] Fig.10The flowchart of switching between the source table to be upgraded and the temporary table for rapid database table structure upgrade is further shown, which specifically includes the following steps:
[0110] Step S301: The application server starts the switching task of the management and control module.
[0111] Step S302: the data migration module stops the incremental data migration of all MySQL node target databases of the distributed MySQL database.
[0112] Step S303: The data migration module completes the replacement of source tables and temporary tables of all MySQL nodes.
[0113] Step S304: After the data migration module waits for all MySQL nodes to return a successful operation, the management and control module notifies the application server that the table structure upgrade is complete.
[0114] In summary, the database table structure of the present invention is fast to change, which greatly shortens the downtime of table structure change. The downtime can be shortened from minutes or hours to seconds, which greatly improves the continuity of business operation. In addition, the database table structure change process of the present invention does not require the participation of the application server, nor does it require developers to write migration programs, does not require the reconstruction of the database high-availability backup library, and does not require manual data migration. The table structure change and upgrade of all target databases can be achieved by setting a one-time operation of the table structure change instruction, reducing the development workload and operation and maintenance difficulty of developers.
[0115] Based on the same principle, this embodiment also discloses a distributed database table structure grayscale upgrade device. Fig.11 As shown, in this embodiment, the device includes a table structure changing module 11, a data migration module 12 and a management and control module 13.
[0116] The table structure change module 11 is used to determine all target databases, source tables and grayscale upgrade priorities to be upgraded according to the table structure change instruction.
[0117] The data migration module 12 is used to establish a temporary table after the table structure is changed in the target database with the highest priority for grayscale upgrade, migrate the table data of the source table to be upgraded to the temporary table through data migration and replace the temporary table with the source table, and the target database table structure change is completed.
[0118] The management and control module 13 is used to feedback the upgrade success information to the application server after the target database table structure change with the highest priority for the grayscale upgrade is completed.
[0119] Since the principle of solving the problem by the device is similar to that of the above method, the implementation of the device can refer to the implementation of the method and will not be repeated here.
[0120] Based on the same principle, this embodiment also discloses a distributed database table structure grayscale upgrade system. Figure 4 As shown, in this embodiment, the distributed database table structure grayscale upgrade system includes an application server, a distributed database, and a distributed database table structure grayscale upgrade device.
[0121] Among them, the application server transmits a table structure change instruction to the distributed database table structure grayscale upgrade device, and the distributed database table structure grayscale upgrade device is used to determine all target databases, source tables and grayscale upgrade priorities to be upgraded according to the table structure change instruction; establish a temporary table after the table structure change in the target database with the highest grayscale upgrade priority, migrate the table data of the source table to be upgraded to the temporary table through data migration and replace the temporary table with the source table, and the target database table structure change is completed; when the target database table structure change with the highest grayscale upgrade priority is completed, feedback upgrade success information to the application server.
[0122] Since the principle of solving the problem by this system is similar to that of the above method, the implementation of this system can refer to the implementation of the method and will not be repeated here.
[0123] The systems, devices, modules or units described in the above embodiments may be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer device, and specifically, the computer device may be, for example, a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or a combination of any of these devices.
[0124] In a typical example, the computer device specifically includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, the method executed by the client as described above is implemented; or, when the processor executes the program, the method executed by the server as described above is implemented.
[0125] Reference below Fig.12 , which shows a schematic diagram of the structure of a computer device 600 suitable for implementing an embodiment of the present application.
[0126] like Fig.12As shown, the computer device 600 includes a central processing unit (CPU) 601, which can perform various appropriate operations and processes according to a program stored in a read-only memory (ROM) 602 or a program loaded from a storage part 608 to a random access memory (RAM) 603. In the RAM 603, various programs and data required for the operation of the system 600 are also stored. The CPU 601, the ROM 602, and the RAM 603 are connected to each other via a bus 604. An input / output (I / O) interface 605 is also connected to the bus 604.
[0127] The following components are connected to the I / O interface 605: an input section 606 including a keyboard, a mouse, etc.; an output section 607 including a cathode ray tube (CRT), a liquid crystal feedback device (LCD), etc., and a speaker, etc.; a storage section 608 including a hard disk, etc.; and a communication section 609 including a network interface card such as a LAN card, a modem, etc. The communication section 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to the I / O interface 605 as needed. A removable medium 611, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 610 as needed, so that a computer program read therefrom is installed as needed as the storage section 608.
[0128] In particular, according to an embodiment of the present invention, the process described above with reference to the flowchart can be implemented as a computer software program. For example, an embodiment of the present invention includes a computer program product, which includes a computer program tangibly contained on a machine-readable medium, and the computer program includes program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through the communication part 609, and / or installed from the removable medium 611.
[0129] Computer readable media include permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. Information can be computer readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disk read-only memory (CD-ROM), digital versatile disk (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include temporary computer readable media (transitory media), such as modulated data signals and carrier waves.
[0130] For the convenience of description, the above device is described in various units according to their functions. Of course, when implementing the present application, the functions of each unit can be implemented in the same or multiple software and / or hardware.
[0131] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.
[0132] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.
[0133] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.
[0134] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.
[0135] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.
[0136] The present application may be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application may also be practiced in distributed computing environments where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules may be located in local and remote computer storage media, including storage devices.
[0137] Each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.
[0138] The above is only an embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included in the scope of the claims of the present application.
Claims
1. A method for grayscale upgrade of distributed database table structure, It is characterized in that include: Determine all target databases, source tables and grayscale upgrade priorities to be upgraded according to the table structure change instruction. The grayscale upgrade priority is determined according to the importance of data stored in the source tables to be upgraded in each target database and whether the source tables to be upgraded in multiple target databases are the same; A temporary table with a changed table structure is established in the target database with the highest priority for grayscale upgrade, and the table data of the source table to be upgraded is migrated to the temporary table by data migration, and the temporary table is replaced by the source table, and the target database table structure change is completed; When the target database table structure with the highest priority for the grayscale upgrade is changed, the upgrade success information is fed back to the application server.
2. According to the method for gray-scale upgrading of the distributed database table structure according to claim 1, It is characterized in that Further including: Based on preset conditions, a temporary table with upgraded table structure is established in the target database where the table structure has not been changed. The table data of the source table to be upgraded is migrated to the temporary table through data migration and the temporary table is replaced with the source table. The table structure upgrade of the target database where the table structure has not been changed is completed.
3. According to the method for gray-scale upgrading of the distributed database table structure in claim 1, It is characterized in that Determining all target databases and source tables to be upgraded according to the table structure change instruction specifically includes: Determine the database information and source table information of the target database according to the table structure change instruction; The IP address of the database is determined according to the database information, and the table data of the source table in the database is acquired according to the source table information.
4. The method for gray-scale upgrading of a distributed database table structure according to claim 1, It is characterized in that The step of establishing a temporary table after the table structure is changed in the target database with the highest priority for grayscale upgrade specifically includes: Determine the target table structure after the table structure is changed according to the table structure change instruction; An empty table is established as the temporary table according to the target table structure.
5. The method for gray-scale upgrading of a distributed database table structure according to claim 1, It is characterized in that The step of migrating the source table data to the temporary table and replacing the temporary table with the source table by means of data migration specifically includes: Determine data migration rules according to the table structure change instruction; Migrating the data in the source table to be upgraded to the temporary table according to the data migration rule; After the data migration is completed, the temporary table is replaced with the source table.
6. The method for gray-scale upgrading of a distributed database table structure according to claim 5, It is characterized in that Migrating the data in the source table to be upgraded to the temporary table according to the data migration rule specifically includes: Determine, according to the data migration rule, the assignment rule of the newly added field of the temporary table relative to the source table to be upgraded, the table data processing rule of the source table to be upgraded, and the fields to be migrated; Obtaining changed data from the table data of the field to be migrated in the source table according to the table data processing rule; The changed data is migrated to the position corresponding to the temporary table and the newly added fields in the temporary table are assigned values.
7. The method for gray-scale upgrading of a distributed database table structure according to claim 1, It is characterized in that Further comprising, before replacing the temporary table with the source table: Obtaining a log file of the target database; Parsing the log file to obtain data records; Table data is formed according to the data records and written into the temporary table.
8. The method for gray-scale upgrading of a distributed database table structure according to claim 1, It is characterized in that The replacing the temporary table with the source table specifically includes: Stop all data processing operations on the source table to be upgraded; Change the table name of the source table to be upgraded to a temporary name; The table name of the temporary table is changed to the table name of the source table to be upgraded, and the temporary table is replaced by the source table.
9. The method for gray-scale upgrading of a distributed database table structure according to claim 1, It is characterized in that The target database is a main database, and the method further comprises: When the database table structure in the main database is changed, the source table after the table structure in the main database is changed is backed up to the standby database.
10. A distributed database table structure grayscale upgrade device, It is characterized in that include: A table structure change module is used to determine all target databases, source tables and grayscale upgrade priorities to be upgraded according to the table structure change instruction. The grayscale upgrade priority is determined according to the importance of data stored in the source tables to be upgraded in each target database and whether the source tables to be upgraded in multiple target databases are the same; A data migration module is used to establish a temporary table after the table structure is changed in the target database with the highest priority for grayscale upgrade, migrate the table data of the source table to be upgraded to the temporary table through data migration, and replace the temporary table with the source table, so that the target database table structure change is completed; The management and control module is used to feedback the upgrade success information to the application server after the target database table structure with the highest priority for the grayscale upgrade is changed.
11. A distributed database table structure grayscale upgrade system, It is characterized in that It includes an application server, a distributed database, and a grayscale upgrade device for a distributed database table structure; The application server transmits a table structure change instruction to the distributed database table structure grayscale upgrade device, and the distributed database table structure grayscale upgrade device is used to determine all target databases, source tables and grayscale upgrade priorities to be upgraded according to the table structure change instruction; A temporary table after the table structure change is established in the target database with the highest priority for grayscale upgrade, and the table data of the source table to be upgraded is migrated to the temporary table through data migration, and the temporary table is replaced with the source table, and the target database table structure change is completed; when the table structure change of the target database with the highest priority for grayscale upgrade is completed, the upgrade success information is fed back to the application server, and the grayscale upgrade priority is determined according to the importance of the data stored in the source tables to be upgraded in each target database and whether the source tables to be upgraded in multiple target databases are the same.
12. A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, It is characterized in that When the processor executes the program, the method according to any one of claims 1 to 9 is implemented.
13. A computer readable medium having a computer program stored thereon, It is characterized in that When the program is executed by a processor, the method according to any one of claims 1 to 9 is implemented.
Citation Information
Patent Citations
Database device, database matching system, and database matching method
CN102405465A
Method and device for altering table structure of data table
CN104965879A
Android database upgrade method, device and terminal
CN109241071A
Database synchronous replication method and device
CN111382199A
System task automatic scheduling method and device, computer equipment and storage medium
CN111475267A
