CN109189778A - A kind of method of online modification database table structure - Google Patents
A kind of method of online modification database table structure Download PDFInfo
- Publication number
- CN109189778A CN109189778A CN201810701116.6A CN201810701116A CN109189778A CN 109189778 A CN109189778 A CN 109189778A CN 201810701116 A CN201810701116 A CN 201810701116A CN 109189778 A CN109189778 A CN 109189778A
- Authority
- CN
- China
- Prior art keywords
- source data
- new
- library
- data table
- new table
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention provides a kind of method of online modification database table structure, comprising: checks the source data table with the presence or absence of external key, external key then creates a new table in master library according to the source data table of master library if it exists, modifies to new table according to target table structure；Trigger is created in master library, for the operation in source data table to be mapped to new table；Binlog log is opened, modifies the table structure from library automatically according to new table；Data are copied from source data table to new table；According to modified new table, sublist associated with external key is modified；New table is renamed, and deletes source data table；Delete trigger.The present invention considers the associated sublist of external key and the synchronized update from library example, online modification speed is fast, and not will cause cloud library server data obstruction, stability with higher can successfully manage the table structure online modification demand with more examples from library and cloud library.
Description
Technical field
The present invention relates to database technical field, in particular to a kind of method of online modification database table structure.
Background technique
The physical data table in database is all stored in database at present.According to the needs of project development,
Various functions can be increased and decreased during database O&M, this just needs to modify tables of data frequent progress, in some cases, very
To needing to modify to table structure, however when being modified accordingly database table structure, it is current to generally require pause
The project run on line, publication application again after modification.
At present open source online modification MySql table structure tool, as Percona pt-online-schema-change,
OSC and LHM of Facebook etc., but these can only modify to single example, can not carry out to more examples from library and cloud library
Line modification.
Therefore it provides one kind can not influence business on line and can modify to more examples from library and cloud database table structure
Method and system be those skilled in the art's technical problem urgently to be resolved.
Summary of the invention
In view of this, may be implemented the purpose of the present invention is to provide a kind of method of online modification database table structure
More examples will not influence the normal operation on database line from library and the online modification of master library, and modification speed is fast and can be effective
Guarantee the integrality of data.Its concrete scheme is as follows to achieve the above object:
A kind of method of online modification database table structure includes source data table in master library, includes the following steps:
Step 1, the source data table is checked with the presence or absence of external key, and external key then terminates execution if it does not exist, if it exists external key
Then enter step 2；
Step 2, a new table is created in the master library according to the source data table of master library, the new table has and the source number
According to the identical table structure of table, modify to the new table according to target table structure；
Step 3, trigger is created in the master library, for the operation in the source data table to be mapped to the new table；
Step 4, binlog log is opened, modifies the table structure from library automatically according to new table；
Step 5, data are copied from the source data table to the new table；
Step 6, according to the modified new table, sublist associated with external key is modified；
Step 7, the new table is renamed, and deletes the source data table；
Step 8, the trigger is deleted.
Preferably, to source data table during the recording trigger is from the source data table to the new table copy data
The operation of data modification is carried out, and after data copy, executes the operation in the new table.
Preferably, three triggers are created in the master library in the step 3, is INSERT trigger, UPDATE respectively
Trigger and DELETE trigger.
Preferably, in the step 4, using row mode record binlog log, method particularly includes:
After the master library more new data, receives binlog daily record data and local log is written；
Local log is read, inspection source tables of data whether there is major key or unique index, if it exists major key or unique index,
Then binlog daily record data is matched with major key or unique index；Major key or unique index if it does not exist, then full table scan matching
Binlog daily record data；
Update the table structure from library.
Preferably, in the step 6, the method for modifying sublist associated with external key includes following two:
(1) sublist for rebuilding external key reference, and is directed toward the new table, corresponding step 7 be exchange the source data table and
Then the table name of the new table deletes the source data table；
(2) inspection of sublist foreign key constraint is disabled, corresponding step 7 is first to delete the source data table, to the new table
Renaming.
A kind of online modification database table structure system disclosed by the invention, while realizing the modification of online table structure,
Consider the associated sublist of external key and the synchronized update from library example, online modification speed is fast, and not will cause cloud library server
Data jamming, stability with higher, can successfully manage needs with more examples from the table structure online modification in library and cloud library
It asks.
Detailed description of the invention
In order to more clearly explain the embodiment of the invention or the technical proposal in the existing technology, to embodiment or will show below
There is attached drawing needed in technical description to be briefly described, it should be apparent that, the accompanying drawings in the following description is only this
The embodiment of invention for those of ordinary skill in the art without creative efforts, can also basis
The attached drawing of offer obtains other attached drawings.
Fig. 1 is a kind of flow diagram of game SDK online updating method disclosed by the invention.
Specific embodiment
Following will be combined with the drawings in the embodiments of the present invention, and technical solution in the embodiment of the present invention carries out clear, complete
Site preparation description, it is clear that described embodiments are only a part of the embodiments of the present invention, instead of all the embodiments.It is based on
Embodiment in the present invention, it is obtained by those of ordinary skill in the art without making creative efforts every other
Embodiment shall fall within the protection scope of the present invention.
Referring to attached drawing 1, the invention discloses a kind of methods of online modification database table structure, include the following steps:
S1, inspection source tables of data whether there is external key, and external key then terminates execution if it does not exist, and external key then enters step if it exists
Rapid 2.
In one embodiment, if there is external key, according to the value of alter-foreign-keys-method parameter, inspection
The source data table for having external key is surveyed, is further processed.If not using the specified spy of alter-foreign-keys-method
Fixed value then terminates execution.
S2 creates a new table in master library according to the source data table of master library, and new table has table knot identical with source data table
Structure modifies to new table according to target table structure.
S3 creates trigger in the source data table of master library, for the operation in source data table to be mapped to new table.
Recording trigger carries out the operation of data modification to source data table during copying data to new table from source data table,
And after data copy, operation is executed in new table, three triggers are created in source data table, is INSERT touching respectively
Send out device, UPDATE trigger and DELETE trigger.
S4 opens binlog log, modifies the table structure from library automatically according to new table.
Using mysql ROW mode record binlog log, conveniently from the synchronous vacations in library, principle is as follows:
Row based replication-RBR
Duplication based on ROW, the binary log of row mode record, method particularly includes:
After master library more new data, receives binlog daily record data and local log is written；
Local log is read, inspection source tables of data whether there is major key or unique index, if it exists major key or unique index,
Then binlog daily record data is matched with major key or unique index；Major key or unique index if it does not exist, then full table scan matching
Binlog daily record data；
Update the table structure from library.
S5 copies data to new table from source data table.
S6 modifies sublist associated with external key according to modified new table.
In one embodiment, alter-foreign-keys-method option determines how to handle such case:
The method for modifying sublist associated with external key includes following two:
(1) sublist of external key reference is rebuild, and is directed toward new table, corresponding step 7 is the table for exchanging source data table and Xin Biao
Then name deletes source data table；
The preferential rebuild_constraints by the way of rebuilding.
Alter table t2drop fk1 is first passed through, add_fk1 rebuilds external key reference, is directed toward new table, then rename
T1t1_old, _ t1_new t1 exchange table name, do not influence client and delete source data table t1_old.But if sublist t2 is too big,
So that alter operation may take long time, then second of processing mode drop_swap operation of selection can be forced.
(2) inspection of sublist foreign key constraint is disabled, corresponding step 7 is first to delete source data table, is renamed to new table；
Determine_alter_fk_ of the main method being related in pt-online-schema-change file
It is executed in tri- functions of method, rebuild_constraints, swap_tables
Drop_swap operation.
Firstly, the foreign key constraint inspection of FOREIGN_KEY_CHECKS=0 disabling sublist t2,
Then, drop t1 deletes source data table,
Finally, rename_t1_new t1 renames new table.
This mode speed faster, will not blocking request.For drop table moment to rename process, receive pair
The case where source data table handling new request, only holds there are the operation of the online modification of external key referring-to relation between our law regulation table
It exercises and specifies the source data table of special parameter to modify operation with alter-foreign-keys-method, do not defined by table strong
Restrict beam.
S7 renames new table, and deletes source data table；The sequence of the step depends on the executive mode of step S6.
S8 deletes trigger.
The use example of this method enabling is given below:
Osc_cli copy--mysql-host=10.13.14.94--mysql-port=3306--mysql-use r=
Root--mysql-password='gelaoshi123, ' -- repl-status=master--ddl-file-list=
Orders.sql--database=anfanapi--socket="
In the examples described above, each explanation of field is as follows:
--mysql-host:
- h, long-range link address
--mysql-user:
- u, the user name of connection
--mysql-port:
- P connects the port of database
-- mysql-password:
- p, the password of connection
--repl-status:
Connect database role
--ddl-file-list:
The table structure for needing to modify
-- database:
- D, the database of connection
--socket:
- S, the socket file of connection
The table structure orders.sql that the example is modified as needed is carried out to master library master and from library anfanapi
Definition, may be implemented master library and from synchronized update.
The present invention is suitable for the modification application of following data database table structure:
1, there cannot be trigger in source data table before a modification in advance, trigger is that modification automatically creates in the process
Gained；
2, for there are the master libraries and cloud library of major key or unique index to realize online modification in source data table；
3, the external key of specified alter-foreign-keys-method parameter is needed when updating；
4, duplicate data source data table will appear to the update operation of major key.
A kind of reference-free quality evaluation method of blurred picture provided by the present invention is described in detail above, this
Apply that a specific example illustrates the principle and implementation of the invention in text, the explanation of above example is only intended to
It facilitates the understanding of the method and its core concept of the invention；At the same time, for those skilled in the art, think of according to the present invention
Think, there will be changes in the specific implementation manner and application range, in conclusion the content of the present specification should not be construed as pair
Limitation of the invention.
Herein, relational terms such as first and second and the like be used merely to by an entity or operation with it is another
One entity or operation distinguish, and without necessarily requiring or implying between these entities or operation, there are any this reality
Relationship or sequence.Moreover, the terms "include", "comprise" or its any other variant are intended to the packet of nonexcludability
Contain, so that the process, method, article or equipment for including a series of elements not only includes those elements, but also including
Other elements that are not explicitly listed, or further include for elements inherent to such a process, method, article, or device.
In the absence of more restrictions, the element limited by sentence "including a ...", it is not excluded that including the element
Process, method, article or equipment in there is also other identical elements.
Claims (5)
- It include source data table in master library, it is characterised in that: including walking as follows 1. a kind of method of online modification database table structure It is rapid:Step 1, check the source data table with the presence or absence of external key, external key then terminates execution if it does not exist, if it exists external key then into Enter step 2；Step 2, a new table is created in the master library according to the source data table of master library, the new table has and the source data table Identical table structure modifies to the new table according to target table structure；Step 3, trigger is created in the master library, for the operation in the source data table to be mapped to the new table；Step 4, binlog log is opened, modifies the table structure from library automatically according to new table；Step 5, data are copied from the source data table to the new table；Step 6, according to the modified new table, sublist associated with external key is modified；Step 7, the new table is renamed, and deletes the source data table；Step 8, the trigger is deleted.
- 2. a kind of method of online modification database table structure according to claim 1, which is characterized in that the trigger Operation during recording from the source data table to the new table copy data to source data table progress data modification, and After data copy, the operation is executed in the new table.
- 3. a kind of method of online modification database table structure according to claim 1, which is characterized in that the step 3 In the master library create three triggers, be INSERT trigger, UPDATE trigger and DELETE trigger respectively.
- 4. a kind of method of online modification database table structure according to claim 1, which is characterized in that the step 4 In, using row mode record binlog log, method particularly includes:After the master library more new data, receives binlog daily record data and local log is written；Local log is read, inspection source tables of data whether there is major key or unique index, and major key or unique index, then use if it exists Major key or unique index match binlog daily record data；Major key or unique index if it does not exist, then full table scan matches binlog Daily record data；Update the table structure from library.
- 5. a kind of method of online modification database table structure according to claim 1, which is characterized in that the step 6 In, the method for modifying sublist associated with external key includes following two:(1) sublist for rebuilding external key reference, and is directed toward the new table, and corresponding step 7 is to exchange the source data table and described The table name of new table, then deletes the source data table；(2) inspection of sublist foreign key constraint is disabled, corresponding step 7 is first to delete the source data table, is ordered again to the new table Name.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810701116.6A CN109189778A (en) | 2018-06-29 | 2018-06-29 | A kind of method of online modification database table structure |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810701116.6A CN109189778A (en) | 2018-06-29 | 2018-06-29 | A kind of method of online modification database table structure |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN109189778A true CN109189778A (en) | 2019-01-11 |
Family
ID=64948677
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810701116.6A Pending CN109189778A (en) | 2018-06-29 | 2018-06-29 | A kind of method of online modification database table structure |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109189778A (en) |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110597823A (en) * | 2019-09-20 | 2019-12-20 | 四川长虹电器股份有限公司 | Method for supporting online DDL operation of MySQL database |
| CN110909087A (en) * | 2019-11-28 | 2020-03-24 | 北京思特奇信息技术股份有限公司 | Method and device for modifying table structure of online DDL (distributed data storage) of relational database |
| CN112347115A (en) * | 2020-10-29 | 2021-02-09 | 北京新数科技有限公司 | Database online table changing method and device |
| CN113051265A (en) * | 2019-12-27 | 2021-06-29 | 中信百信银行股份有限公司 | Method, device, computer equipment and readable storage medium for reducing loss caused by relational database table structure change |
| CN113486016A (en) * | 2021-07-19 | 2021-10-08 | 瀚高基础软件股份有限公司 | Method for deleting multiple tables by means of MySQL compatible in SQL database and storage medium |
| CN113704265A (en) * | 2021-08-31 | 2021-11-26 | 上海华力集成电路制造有限公司 | Data maintenance method, system, electronic equipment and storage medium |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102929878A (en) * | 2011-08-09 | 2013-02-13 | 阿里巴巴集团控股有限公司 | Method and device for managing database changes |
| US20180137187A1 (en) * | 2016-11-11 | 2018-05-17 | International Business Machines Corporation | Copying data changes to a target database |
-
2018
- 2018-06-29 CN CN201810701116.6A patent/CN109189778A/en active Pending
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102929878A (en) * | 2011-08-09 | 2013-02-13 | 阿里巴巴集团控股有限公司 | Method and device for managing database changes |
| US20180137187A1 (en) * | 2016-11-11 | 2018-05-17 | International Business Machines Corporation | Copying data changes to a target database |
Non-Patent Citations (5)
| Title |
|---|
| DATA_IT_FARMER: "Mysql主从基本原理，主要形式以及主从同步延迟原理 (读写分离)导致主库从库数据不一致问题的及解决方案", 《CSDN》 * |
| JENKIN_LIN: "pt-online-schema-change的bug2", 《51CTO博客》 * |
| SAGA_GALLON: "pt-online-schema-change 使用介绍", 《CSDN》 * |
| SMART818: "Binlog格式为ROW 详解及遇到的问题", 《51CTO博客》 * |
| 奔跑的码农: "在线更改MySQL表结构工具pt-online-schema-change", 《CSDN》 * |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110597823A (en) * | 2019-09-20 | 2019-12-20 | 四川长虹电器股份有限公司 | Method for supporting online DDL operation of MySQL database |
| CN110909087A (en) * | 2019-11-28 | 2020-03-24 | 北京思特奇信息技术股份有限公司 | Method and device for modifying table structure of online DDL (distributed data storage) of relational database |
| CN113051265A (en) * | 2019-12-27 | 2021-06-29 | 中信百信银行股份有限公司 | Method, device, computer equipment and readable storage medium for reducing loss caused by relational database table structure change |
| CN112347115A (en) * | 2020-10-29 | 2021-02-09 | 北京新数科技有限公司 | Database online table changing method and device |
| CN113486016A (en) * | 2021-07-19 | 2021-10-08 | 瀚高基础软件股份有限公司 | Method for deleting multiple tables by means of MySQL compatible in SQL database and storage medium |
| CN113486016B (en) * | 2021-07-19 | 2023-07-14 | 瀚高基础软件股份有限公司 | Method and storage medium for deleting multiple tables in SQL database compatible with MySQL |
| CN113704265A (en) * | 2021-08-31 | 2021-11-26 | 上海华力集成电路制造有限公司 | Data maintenance method, system, electronic equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109189778A (en) | A kind of method of online modification database table structure | |
| US10445321B2 (en) | Multi-tenant distribution of graph database caches | |
| JP6816297B2 (en) | Data separation in blockchain network | |
| JP2022166013A (en) | Method, computer-readable medium and system for violation resolution in client synchronization | |
| DE112012005037B4 (en) | Manage redundant immutable files using deduplications in storage clouds | |
| US8706703B2 (en) | Efficient file system object-based deduplication | |
| US11294958B2 (en) | Managing a distributed knowledge graph | |
| CN105378725B (en) | Virtual data base is fallen back | |
| JP7408626B2 (en) | Tenant identifier replacement | |
| CN108369487A (en) | System and method for shooting snapshot in duplicate removal Virtual File System | |
| CN105593829A (en) | Excluding file system objects from raw image backups | |
| US11669504B2 (en) | Database schema branching workflow, with support for data, keyspaces and VSchemas | |
| WO2009147847A1 (en) | Database parallel editing method | |
| US10732840B2 (en) | Efficient space accounting mechanisms for tracking unshared pages between a snapshot volume and its parent volume | |
| CN107357691B (en) | Method and device for processing mirror image file | |
| US7953770B2 (en) | Conflict management in a versioned file system | |
| JP2011522337A (en) | Method of synchronizing software modules of computer system distributed to server cluster, application to synchronization system and data storage | |
| US9569461B2 (en) | Distributed data authority system | |
| US20190354538A1 (en) | Ordered list management in a replicated environment | |
| JP2019509553A (en) | Method and apparatus for replicating data between storage systems | |
| Bradberry et al. | Practical Cassandra: a developer's approach | |
| JP2010519646A (en) | Data management within a data storage system using datasets | |
| JP4855537B2 (en) | Database parallel editing method | |
| US20190303489A1 (en) | Globalized object names in a global namespace | |
| CN113282551B (en) | Data processing method, system and equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20190111 |