CN107818167A - MySQL subregions automatic management method, system, equipment, storage medium - Google Patents
MySQL subregions automatic management method, system, equipment, storage medium Download PDFInfo
- Publication number
- CN107818167A CN107818167A CN201711088812.6A CN201711088812A CN107818167A CN 107818167 A CN107818167 A CN 107818167A CN 201711088812 A CN201711088812 A CN 201711088812A CN 107818167 A CN107818167 A CN 107818167A
- Authority
- CN
- China
- Prior art keywords
- subregion
- mysql
- subregions
- partition
- information
- 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.)
- Granted
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
Abstract
The present invention relates to a kind of MySQL subregions automatic management method, system, equipment, storage medium.The MySQL subregion automatic management methods, including：Configuration information is obtained, the configuration information includes partition table information, owner's information, divisional type information, partitioning strategies information；Corresponding storing process is called to be safeguarded automatically according to configuration information.It can be configured by a step and accomplish permanent O＆M, without manual operation, improved the accuracy of MySQL partition managements, greatly reduce O＆M human cost.
Description
Technical field
The present invention relates to database, more particularly to a kind of MySQL subregions automatic management method, system, equipment, storage Jie
Matter.
Background technology
As uses of the MySQL in formal production environment is more and more wider, and the growth of data, the management to data are in
Now many deficiencies,
During the use of MySQL partition tables, point that all kinds and strategy are customized according to business demand is frequently encountered
Area's table, need to delete subregion and addition subregion manually over time.
Before invention is not carried out, when can only estimate subregion spilling and the time cleared up and the free time when database
Subregion is added and deleted in a manual fashion to section, log database to reach the purpose of management, and the strategy of usual each table is not
Identical, this artificial management significantly increases O＆M human cost, and can introduce various control unknown risks to production environment.
The content of the invention
In order to solve the above-mentioned technical problem, it is an object of the invention to provide a kind of MySQL subregions automatic management method, be
System, equipment, storage medium.
According to an aspect of the invention, there is provided a kind of MySQL subregions automatic management method, comprises the following steps：
Configuration information is obtained, the configuration information includes partition table information, owner's information, divisional type information, subregion plan
Slightly information；
Corresponding storing process is called to be safeguarded automatically according to configuration information.
Further, corresponding program is called to be safeguarded according to configuration information, including：
Obtain the affairs amount of partition table；
If the affairs amount of partition table exceedes threshold value, adaptive adjustment and maintenance time；
Partition configuration information is obtained to be safeguarded.
Further, if the affairs amount of partition table exceedes threshold value, the adaptive adjustment and maintenance time, including：
Obtain the pressure of partition table；
Trigger condition is set according to the pressure of the partition table, is performed when trigger condition meets and safeguards step automatically.
Further, partition configuration information is obtained to be safeguarded, including：
Corresponding storing process is called according to the type of partition table.
Further, partition configuration information is obtained to be safeguarded, including：
Corresponding storing process is called to be safeguarded according to subregion key type and corresponding partitioning strategies.
Further, partition configuration information is obtained to be safeguarded, including：
According to partition table information, partitioning strategies information, subregion additions and deletions trustship information, point for needing to delete or adding is judged
Area；
Perform deletion or the addition step.
Further, described MySQL subregion automatic management methods, in addition to：Addition configuration.
Further, addition configuration, including：
Successively verify configuration syntax, the subregion table object of trustship, the divisional type of partition table, subregion maintenance time, point
The mode of the O＆M in area, the partition table of trustship partitioning strategies legitimacy, if legal, verify trustship partition table whether
Trustship, if adding allocation list without if, otherwise by the zone configuration strategy before automatic record, the configuration information of the table
It is revised as new configuration strategy.
Further, when verifying the maintenance of configuration syntax, the subregion table object of trustship, the divisional type of partition table, subregion
Between, the mode of the O＆M of subregion, trustship partition table partitioning strategies it is illegal when, generate error information.
Further, described MySQL subregion automatic management methods, in addition to：
(1) the incoming configuration information of verification, step (2) is performed if legal；
(2) data of all subregions of the partition table are traveled through, obtain the maximum subregion for being currently owned by data；
(3) judge whether to need to delete subregion according to maintenance mode, if so, then performing step (4)；
(4) number of partitions deleted according to the retention strategy of subregion；
(5) all subregions to be cleared up are deleted one by one；
(6) judge whether to also need to delete subregion, if then repeat step (5) is until nothing also needs to delete depositing for subregion
.
Further, described MySQL subregion automatic management methods, in addition to：
(1) judge whether to need to add subregion according to maintenance mode, if then performing step (2)；
(2) number of partitions added according to the addition strategy of subregion；
(3) judge whether partition table sets maximum upper limit；
(4) zone name for needing to add and partitioning expressions are calculated according to current maximum subregion and offset；
(5) according to zone name and partitioning expressions, the SQL of generation addition subregion, addition subregion is performed；
(6) judge whether to also need to add subregion, if then performing step (3)-(5), until nothing also needs to add subregion
Presence.
Further, described MySQL subregion automatic management methods, in addition to：The execution journal letter of record storage process
Breath.
Further, described MySQL subregion automatic management methods, in addition to：
Obtain the data numerical quantity of maximum subregion；
The data numerical quantity of the maximum subregion is compared with mobility threshold；
Zoned migration addition is carried out if data volume is less than mobility threshold, otherwise reports an error and exits.
According to another aspect of the present invention, there is provided a kind of equipment, the equipment include：
One or more processors；
Memory, for storing one or more programs,
When one or more of programs are by one or more of computing devices so that one or more of places
Manage device and perform the method described in any one as described above.
According to another aspect of the present invention, there is provided a kind of computer-readable storage medium for being stored with computer program
Matter, the method described in any one as described above is realized when the program is executed by processor.
Compared with prior art, the invention has the advantages that：
1st, the MySQL subregion automatic management methods of example of the present invention, obtain configuration information, and the configuration information includes subregion
Table information, owner's information, divisional type information, partitioning strategies information；Corresponding storing process is called to carry out certainly according to configuration information
It is dynamic to safeguard.It can be configured by a step and accomplish permanent O＆M, without manual operation, improve the accuracy of MySQL partition managements,
Greatly reduce O＆M human cost.
2nd, the equipment of example of the present invention, MySQL subregion automatic management methods are performed by one or more processors, without
Manual operation, it is automatic to carry out MySQL partition managements.
3rd, the computer-readable recording medium for being stored with computer program of example of the present invention, the program are executed by processor
The method described in any one, automatic management, maximization reduce O＆M cost, are easy to promote Shi Shixian as described above.
Brief description of the drawings
Fig. 1 is the integrated stand composition that MySQL storing process of the present invention realizes automatic O＆M function.
Embodiment
In order to be better understood by technical scheme, with reference to specific embodiment, Figure of description to the present invention
It is described further.
Embodiment one：
Based on background above, automatic O＆M function is realized according to MySQL storing process, integrated stand composition is illustrated in fig. 1 shown below
(pay attention to, the Organization Chart show only the framework and calling logic of an entirety, and the automatic management tool of the subregion also has other moulds
Block).
Specific logic module is described as follows in Fig. 1：
(1) outside is a big circulation first, i.e. main_partition, it is a total calling, can first be read
All information in allocation list, such as：Partition table, owner, divisional type, partitioning strategies, then according to the corresponding program of these calling
Safeguarded.
(2) the affairs amount of the partition table currently to be safeguarded can next be judged, can be rear when judging that the table is busier
Platform detects the pressure of the table every 5s, then the adaptive adjustment and maintenance time, obtains maintenance window.
(3) judge the type of the partition table, be range subregions, or columns points of the range introduced after MySQL5.6
Area, different storing process is then called to this two classes subregion respectively.
(4) it next can judge subregion key type, can be called when judging subregion bonding method according to different partitioning strategieses
Different storing process are safeguarded, can be reported an error and be exited when judging that subregion key is illegal.
(5) information of partition table, the subregion maintenance strategy of configuration, the mode of subregion additions and deletions trustship can be finally judged again,
Judgement needs the subregion that the subregion deleted and needs add, and subregion is added and deleted, then returnes to total circulation main_
partition。
Wherein, the assembly function introduction of the automatic maintenance tool of subregion：
(1) configuration feature
proc_main_partition_config
The execution authority of the storing process can license to user, work as the partition management strategy that user adds traffic table
In, realize automatic safeguard.
(2) scheduling feature
proc_main_partition
The procedure is a systemic circulation, realizes total calling, and information calling that can be in allocation list is a series of
Storing process.
(3) the automatic maintenance function of subregion
proc_main_partition_range_nofunc
Such as it is the subregion of range types, and when its subregion key be incremental numeral, the procedure completes its additions and deletions and divided
The concrete behavior in area
Such as：PARTITION BY RANGE(store_id)
proc_main_partition_range_day
Such as it is the subregion of range types, and its partitioning strategies is daily subregion, or complete by all subregions, the procedure
Into the concrete behavior of its additions and deletions subregion.
proc_main_partition_range_period
The strategy according to the first tenday period of a month, the middle ten days, the last ten-days period is needed to carry out subregion, such as June for some data, before 6.1-6.11
For a subregion, 6.11-6.21 is a subregion, 6.21-7.01 is a subregion.
Such as it is the subregion of range types, and its partitioning strategies is complete by the first tenday period of a month, the middle ten days, last ten-days period subregion, the procedure
Into the concrete behavior of its additions and deletions subregion.
proc_main_partition_range_month
Such as it is the subregion of range types, and its partitioning strategies is monthly subregion, the procedure complete its additions and deletions subregion
Concrete behavior.
proc_main_partition_range_year
Such as it is the subregion of range types, and its partitioning strategies is subregion, the procedure complete its additions and deletions subregion per year
Concrete behavior.
proc_main_part_rangecolumn_day
Such as it is the subregion of range columns types, and its partitioning strategies is daily subregion, or by all subregions, should
Procedure completes the concrete behavior of its additions and deletions subregion.
proc_main_part_rangecolumn_period
The strategy according to the first tenday period of a month, the middle ten days, the last ten-days period is needed to carry out subregion, such as June for some data, before 6.1-6.11
For a subregion, 6.11-6.21 is a subregion, 6.21-7.01 is a subregion.
Such as it is the subregion of range columns types, and its partitioning strategies is by the first tenday period of a month, the middle ten days, last ten-days period subregion, is somebody's turn to do
Procedure completes the concrete behavior of its additions and deletions subregion.
proc_main_part_rangecolumn_month
Such as it is the subregion of range columns types, and its partitioning strategies is monthly subregion, the procedure complete it
The concrete behavior of additions and deletions subregion.
proc_main_part_rangecolumn_year
Such as it is the subregion of range columns types, and its partitioning strategies is subregion, the procedure complete it per year
The concrete behavior of additions and deletions subregion.
(4) allocation list
main_partition_table_config
Allocation list, record add the information such as the partitioning strategies of all partition tables that subregion is safeguarded automatically.
main_partition_table_config_log
The execution log information of proc_main_partition_config storing process.
main_partition_operation_log
The log information that all procedure are performed, the table can record what operation some time point has done to some table.
main_partition_scheduler_log
The log information that all procedure are called, the table are mainly used in when partition management tool occurs abnormal, debugging
It is used.
Wherein, SQL and grammer are configured and use, it is as follows：
(1) database and user for the automatic management tool that created the division in MySQL storehouses, such as：
Create database
create database dbamon；
Create user
GRANT ALL PRIVILEGES ON`dbamon`.*TO'dbamon'@' 10.%'；
(2) operation and maintenance tools are created
Under the database that the first step creates, the user created with the first step performs the generation of the automatic management tool of subregion
Code.
(3) configuring timing tasks
Selection business free time creates event in MySQL storehouses
(4) to being authorized using user
GRANT EXECUTE ON PROCEDURE`dbamon`.`proc_main_partition_config`TO'
Business'@' 10.%'；
Business account is given configuration permission grant.
grant select on dbamon.main_partition_table_config TO'business'@'
10.%'；
The search access right of the table is awarded, so that business account checks configuration result.
grant select on dbamon.main_partition_table_config_log TO'business'@'
10.%'；
grant select on dbamon.main_partition_operation_log TO'business'@'
10.%'；
grant select on dbamon.main_partition_scheduler_log TO'business'@'
10.%'；
The search access right of log sheet is licensed to business account.
(5) partition table is added trustship gives subregion automatic management tool
call dbamon.proc_main_partition_config`(
OUT v_MSG varchar(400),
@v_MSG, simply a variable can export the result of configuration or report an error, keep acquiescence
IN v_username varchar (100), keep acquiescence
IN v_schema_name varchar(100),database
IN v_table_name varchar (100), partition table
IN v_part_column varchar(50),
Subregion key, auto-partition management procedure only support single-row subregion
IN v_policy varchar(50),
Partitioning strategies, divisional type only think YEAR, MONTH, PERIOD, WEEK, DAY, numeral.
IN v_valid_flag tinyint(4),
This is flag bit, and parameter is only 1,2,3,4,1 plus deleted, and 2 are not added with not deleting, 3, only plus do not delete, delete for 4 and be not added with.
IN v_keep_offset int (4), the number of partitions of reservation, it can at least retain 3 subregions.
IN v_add_offset int (4), add the quantity of subregion.
IN v_ifexist ENUM('true','false')
False typically is used, if when the table to be added has configured before, is reconfigured if desired, then the value is put
For true.
On this basis, the automatic management of MySQL partition tables is realized, can realize that dynamic change configures for request,
We are successively encapsulated each step, make user configuration more convenient.Further we according to MySQL in later release
The new features of the partition table of middle introducing, add new optimization and logical process.
The MySQL subregion automatic management methods of the present embodiment, comprise the following steps：
S1, configuration information is obtained, the configuration information includes partition table information, owner's information, divisional type information, subregion
Policy information.
S2, according to configuration information calling, correspondingly storing process is safeguarded automatically.
It is described, call corresponding program to be safeguarded according to configuration information, including：
(1) the affairs amount of partition table is obtained；
(2) if the affairs amount of partition table exceedes threshold value, adaptive adjustment and maintenance time；
(3) partition configuration information is obtained to be safeguarded.
If the affairs amount of partition table exceedes threshold value, the adaptive adjustment and maintenance time, including：
(1) pressure of partition table is obtained；
(2) trigger condition is set according to the pressure of the partition table, is performed when trigger condition meets and safeguard step automatically.
Partition configuration information is obtained to be safeguarded, including：Corresponding storing process is called according to the type of partition table；
Corresponding storing process is called to be safeguarded according to subregion key type and corresponding partitioning strategies.
It is described acquisition partition configuration information safeguarded, including：
(1) according to partition table information, partitioning strategies information, subregion additions and deletions trustship information, judge what is needed to delete or add
Subregion；
(2) deletion or the addition step is performed.
Described MySQL subregion automatic management methods, it is characterized in that, in addition to：Addition configuration.
Addition configuration, is specifically included：
Successively verify configuration syntax, the subregion table object of trustship, the divisional type of partition table, subregion maintenance time, point
The mode of the O＆M in area, the partition table of trustship partitioning strategies legitimacy, if legal, verify trustship partition table whether
Trustship, if adding allocation list without if, otherwise by the zone configuration strategy before automatic record, the configuration information of the table
It is revised as new configuration strategy.
Verify configuration syntax, the subregion table object of trustship, the divisional type of partition table, the maintenance time of subregion, subregion
The mode of O＆M, the partition table of trustship partitioning strategies it is illegal when, generate error information.
Described MySQL subregion automatic management methods, in addition to：
(1) the incoming configuration information of verification, step (2) is performed if legal；
(2) data of all subregions of the partition table are traveled through, obtain the maximum subregion for being currently owned by data；
(3) judge whether to need to delete subregion according to maintenance mode, if so, then performing step (4)；
(4) number of partitions deleted according to the retention strategy of subregion；
(5) all subregions to be cleared up are deleted one by one；
(6) judge whether to also need to delete subregion, if then repeat step (5) is until nothing also needs to delete depositing for subregion
.
Described MySQL subregion automatic management methods, in addition to：
(1) judge whether to need to add subregion according to maintenance mode, if then performing step (2)；
(2) number of partitions added according to the addition strategy (the add strategies of subregion) of subregion；
(3) judge whether partition table sets maximum upper limit；
(4) zone name for needing to add and partitioning expressions are calculated according to current maximum subregion and offset；
(5) according to zone name and partitioning expressions, the SQL of generation addition subregion, addition subregion is performed；
(6) judge whether to also need to add subregion, if then performing step (3)-(5), until nothing also needs to add subregion
Presence.
Described MySQL subregion automatic management methods, in addition to：The execution journal information of record storage process.
Described MySQL subregion automatic management methods, in addition to：
Obtain the data numerical quantity of maximum subregion；
The data numerical quantity of the maximum subregion is compared with mobility threshold；
Zoned migration addition is carried out if data volume is less than mobility threshold, otherwise reports an error and exits.
Mobility threshold is 1G.
The technical scheme of the present embodiment can be largely classified into 3 modules, be available to the module that user adds configuration first,
Then the automatic management tool of subregion reads the module that allocation list is scheduled, and is finally to calculate the subregion for needing to add and delete to enter
The module of the automatic O＆M of row, handling process are as described below：
1st, addition configuration
(1) configuration syntax is verified, does not conform to rule return such as and reports an error；
(2) whether legal verify the subregion table object of trustship, do not conform to rule return such as and report an error；
(3) whether legal verify the divisional type of partition table, do not conform to rule return such as and report an error；
(4) whether legal verify the maintenance time of subregion, do not conform to rule return such as and report an error；
(5) whether legal verify the mode of the O＆M of subregion, do not conform to rule return such as and report an error；
(6) whether legal verify the partitioning strategies of the partition table of trustship, do not conform to rule return such as and report an error；
(7) the partition table whether trustship of trustship is verified, allocation list is added if not, if any then jumping to (8)；
(8) partition configuration information before backing up, is revised as new configuration strategy；
(9) terminate.
2nd, main_partition is called
(1) it is whether consistent with the information of allocation list to verify the information of partition table, as inconsistent, return reports an error；
(2) whether verification partition table has big affairs, if any the then adaptive O＆M time；
(3) check whether there is busy process and access the partition table, if any the then adaptive O＆M time；
(4) partition table and configuration information are obtained；
(5) type of subregion key is judged；
(6) partitioning strategies is judged；
(7) call and specify storing process O＆M；
(8) user is returned.
3rd, the automatic maintenance of numeric type subregion
(1) the incoming configuration information of verification, do not conform to rule return such as and report an error；
(2) data of all subregions of the partition table are traveled through, obtain the maximum subregion for being currently owned by data；
(3) judge whether to need to delete subregion according to maintenance mode, such as otherwise jump to (7)；
(4) number of partitions deleted is calculated according to the retention strategy (subregion keep strategies) of subregion；
(5) all subregions to be cleared up are deleted one by one；
(6) judge whether to also need to delete subregion, then jump in this way (5)；
(7) judge whether to need to add subregion according to maintenance mode, such as otherwise jump to (7)；
(8) number of partitions added according to the addition policy calculation of subregion；
(9) judge whether partition table sets maxvalue；
(10) zone name for needing to add and partitioning expressions are calculated according to current maximum subregion and offset
(11) SQL of addition subregion is risked, is performed, adds subregion；
(12) judge whether to also need to add subregion, then jump in this way (9)；
(13) return.
The present embodiment can only need step configuration to give subregion automatic management tool partition table trustship at any time, it
Operation maintenance personnel manual maintenance again is not needed afterwards, and greatly reducing operation maintenance personnel craft additions and deletions subregion causes the probability of maloperation, maximum
Changing reduces O＆M cost.
Support manages the subregion of MySQL storehouses time and numeric type automatically.
The pressure of current database, adaptive adjustment and maintenance time can be detected.Operation maintenance personnel can be according to business window
Mouth configuration event, customize the O＆M time of partition table.All subregion O＆M times can be specified by job, allow it to call automatically
Safeguard.Adaptive subregion O＆M time adjustment also makes operation maintenance personnel not have to worry that DDL operations can lock table again, blocks asking for application
Topic.
The partition management tool can support all partitioning strategieses, support the automatic dimension to all partitioning strategieses of MySQL
Shield.
The strategy of period types is particularly added, means and was divided into upper, middle and lower ten days one month, such as 2 months, being more than January 31
Number be less than 2 months No. 11 position a subregion p1, be more than 2 months No. 11 be less than 2 months No. 21 position a subregion p2, be more than 2 months
No. 21 are less than one subregion p3 of positioning on March 01, make developer more flexible when in use.
The subregion operation and maintenance tools are for specifying maxvalue partition table, if detecting has data, meeting in max subregions
Automatically zoned migration is carried out to below the 1G subregion.
A kind of equipment is present embodiments provided, the equipment includes：
One or more processors；
Memory, for storing one or more programs,
When one or more of programs are by one or more of computing devices so that one or more of places
Manage device and perform the as above method described in any one.
A kind of computer-readable recording medium for being stored with computer program is present embodiments provided, it is characterized in that, the journey
The as above method described in any one is realized when sequence is executed by processor.
Embodiment two
The present embodiment repeats no more with the identical feature of embodiment one, and the present embodiment feature different from embodiment one exists
In：
The subregion operation and maintenance tools are for specifying maxvalue partition table, if detecting has data, meeting in max subregions
Automatically zoned migration is carried out to below the 1.5G subregion.
Above description is only the preferred embodiment of the application and the explanation to institute's application technology principle.People in the art
Member should be appreciated that invention scope involved in the application, however it is not limited to the technology that the particular combination of above-mentioned technical characteristic forms
Scheme, while should also cover in the case where not departing from the inventive concept, carried out by above-mentioned technical characteristic or its equivalent feature
The other technical schemes for being combined and being formed.Such as features described above has similar work(with (but not limited to) disclosed herein
Energy.
Claims (15)
1. a kind of MySQL subregions automatic management method, it is characterized in that, comprise the following steps：
Configuration information is obtained, the configuration information includes partition table information, owner's information, divisional type information, partitioning strategies letter
Breath；
Corresponding storing process is called to be safeguarded automatically according to configuration information.
2. MySQL subregions automatic management method according to claim 1, it is characterized in that, called according to configuration information corresponding
Program safeguarded, including：
Obtain the affairs amount of partition table；
If the affairs amount of partition table exceedes threshold value, adaptive adjustment and maintenance time；
Partition configuration information is obtained to be safeguarded.
3. MySQL subregions automatic management method according to claim 2, it is characterized in that, if the affairs amount of partition table exceedes
Threshold value, then adaptive adjustment and maintenance time, including：
Obtain the pressure of partition table；
Trigger condition is set according to the pressure of the partition table, is performed when trigger condition meets and safeguards step automatically.
4. MySQL subregions automatic management method according to claim 2, it is characterized in that, obtain partition configuration information and carry out
Safeguard, including：
Corresponding storing process is called according to the type of partition table.
5. MySQL subregions automatic management method according to claim 2, it is characterized in that, obtain partition configuration information and carry out
Safeguard, including：
Corresponding storing process is called to be safeguarded according to subregion key type and corresponding partitioning strategies.
6. MySQL subregions automatic management method according to claim 2, it is characterized in that, obtain partition configuration information and carry out
Safeguard, including：
According to partition table information, partitioning strategies information, subregion additions and deletions trustship information, judge to need the subregion deleted or added；
Perform deletion or the addition step.
7. MySQL subregions automatic management method according to claim 1, it is characterized in that, in addition to：Addition configuration.
8. MySQL subregions automatic management method according to claim 7, it is characterized in that, addition configuration, including：
Configuration syntax, the subregion table object of trustship, the divisional type of partition table, the maintenance time of subregion, subregion are verified successively
The mode of O＆M, the partition table of trustship partitioning strategies legitimacy, if legal, verify trustship partition table whether
Trustship, if adding allocation list without if, otherwise by the zone configuration strategy before automatic record, the configuration information of the table is changed
For new configuration strategy.
9. MySQL subregions automatic management method according to claim 8, it is characterized in that, verification configuration syntax, point of trustship
Area's table object, the divisional type of partition table, the maintenance time of subregion, the mode of O＆M of subregion, trustship partition table subregion
Strategy it is illegal when, generate error information.
10. MySQL subregions automatic management method according to claim 6, it is characterized in that, in addition to：
(1) the incoming configuration information of verification, step (2) is performed if legal；
(2) data of all subregions of the partition table are traveled through, obtain the maximum subregion for being currently owned by data；
(3) judge whether to need to delete subregion according to maintenance mode, if so, then performing step (4)；
(4) number of partitions deleted according to the retention strategy of subregion；
(5) all subregions to be cleared up are deleted one by one；
(6) judge whether to also need to delete subregion, if then repeat step (5) is until without the presence for also needing to delete subregion.
11. MySQL subregions automatic management method according to claim 6, it is characterized in that, in addition to：
(1) judge whether to need to add subregion according to maintenance mode, if then performing step (2)；
(2) number of partitions added according to the addition strategy of subregion；
(3) judge whether partition table sets maximum upper limit；
(4) zone name for needing to add and partitioning expressions are calculated according to current maximum subregion and offset；
(5) according to zone name and partitioning expressions, the SQL of generation addition subregion, addition subregion is performed；
(6) judge whether to also need to add subregion, if then performing step (3)-(5), until nothing also needs to add depositing for subregion
.
12. MySQL subregions automatic management method according to claim 1, it is characterized in that, in addition to：Record storage process
Execution journal information.
13. according to any described MySQL subregion automatic management methods of claim 1-12, it is characterized in that, in addition to：
Obtain the data numerical quantity of maximum subregion；
The data numerical quantity of the maximum subregion is compared with mobility threshold；
Zoned migration addition is carried out if data volume is less than mobility threshold, otherwise reports an error and exits.
14. a kind of equipment, it is characterized in that, the equipment includes：
One or more processors；
Memory, for storing one or more programs,
When one or more of programs are by one or more of computing devices so that one or more of processors
Perform the method as any one of claim 1-13.
15. a kind of computer-readable recording medium for being stored with computer program, it is characterized in that, when the program is executed by processor
Realize the method as any one of claim 1-13.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201711088812.6A CN107818167B (en) | 2017-11-08 | 2017-11-08 | MySQL partition automatic management method, system, equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201711088812.6A CN107818167B (en) | 2017-11-08 | 2017-11-08 | MySQL partition automatic management method, system, equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN107818167A true CN107818167A (en) | 2018-03-20 |
| CN107818167B CN107818167B (en) | 2020-06-30 |
Family
ID=61604241
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201711088812.6A Active CN107818167B (en) | 2017-11-08 | 2017-11-08 | MySQL partition automatic management method, system, equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107818167B (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109902126A (en) * | 2019-02-18 | 2019-06-18 | 国家计算机网络与信息安全管理中心 | Support the loading system and its implementation of HIVE auto-partition |
| CN112035486A (en) * | 2020-09-03 | 2020-12-04 | 中国银行股份有限公司 | Partition establishing method, device and equipment for partition table |
| CN112685403A (en) * | 2019-10-18 | 2021-04-20 | 上海同是科技股份有限公司 | High-availability framework system for hidden danger troubleshooting data storage and implementation method thereof |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103544302A (en) * | 2013-10-31 | 2014-01-29 | 北京锐安科技有限公司 | Partition maintenance method and device of database |
| CN103699676A (en) * | 2013-12-30 | 2014-04-02 | 厦门市美亚柏科信息股份有限公司 | MSSQL SERVER based table partition and automatic maintenance method and system |
| CN104503966B (en) * | 2014-10-16 | 2017-12-12 | 杭州斯凯网络科技有限公司 | The efficiently non-maintaining automatic Mesh Partition Method of PostgreSQL big datas |
-
2017
- 2017-11-08 CN CN201711088812.6A patent/CN107818167B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103544302A (en) * | 2013-10-31 | 2014-01-29 | 北京锐安科技有限公司 | Partition maintenance method and device of database |
| CN103699676A (en) * | 2013-12-30 | 2014-04-02 | 厦门市美亚柏科信息股份有限公司 | MSSQL SERVER based table partition and automatic maintenance method and system |
| CN104503966B (en) * | 2014-10-16 | 2017-12-12 | 杭州斯凯网络科技有限公司 | The efficiently non-maintaining automatic Mesh Partition Method of PostgreSQL big datas |
Non-Patent Citations (1)
| Title |
|---|
| INSIDEMYSQL: "分区自动管理与维护——MySQL Partition Manager", 《HTTP://WWW.SOHU.COM/A/114367615_487483》 * |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109902126A (en) * | 2019-02-18 | 2019-06-18 | 国家计算机网络与信息安全管理中心 | Support the loading system and its implementation of HIVE auto-partition |
| CN112685403A (en) * | 2019-10-18 | 2021-04-20 | 上海同是科技股份有限公司 | High-availability framework system for hidden danger troubleshooting data storage and implementation method thereof |
| CN112035486A (en) * | 2020-09-03 | 2020-12-04 | 中国银行股份有限公司 | Partition establishing method, device and equipment for partition table |
| CN112035486B (en) * | 2020-09-03 | 2023-09-19 | 中国银行股份有限公司 | Partition establishing method, device and equipment of partition table |
Also Published As
| Publication number | Publication date |
|---|---|
| CN107818167B (en) | 2020-06-30 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10205729B2 (en) | Automation tool for providing users with security access to an electronic system | |
| CN110503325B (en) | Construction progress resource automatic optimization method based on building information model | |
| CN107818167A (en) | MySQL subregions automatic management method, system, equipment, storage medium | |
| CN108416230B (en) | Data access method based on data isolation model | |
| WO2006002018A3 (en) | Geospatial information system and method for updating same | |
| US20060059059A1 (en) | Systems and methods for managing the execution of services | |
| CN110751358A (en) | Scheduling method for airport ground service personnel, electronic equipment and storage medium | |
| US20020062239A1 (en) | Program planning management system | |
| CA2457878A1 (en) | Project modelling and management tool | |
| CN101539855A (en) | Service basic software platform | |
| CN1860723A (en) | System and methods providing enhanced security model | |
| CN103699676B (en) | MSSQL SERVER based table partition and automatic maintenance method and system | |
| CN104463504B (en) | The execution system and execution method of operation flow | |
| CN105550590A (en) | Role-based access control mechanism | |
| CN105989137A (en) | Structured query language performance optimization method and system | |
| CN111475784A (en) | Authority management method and device | |
| CN1877534A (en) | Accomplishing method for embedded real-time file system task scheduling | |
| CN101645126A (en) | Method for controlling service-oriented authorization access based on RBAC model extension | |
| US10996983B1 (en) | Job scheduler for remote maintenance of servers and workstations | |
| CN106294185A (en) | Automated test frames based on five layers of framework and method | |
| EP2224369B1 (en) | Method, SOA registry and SOA repository for granting a user secure access to resources of a process | |
| CN106529230A (en) | Role-based permission control mechanism | |
| KR102426162B1 (en) | Information providing system for business day | |
| CN110968880A (en) | Account authority processing method and device | |
| EP2728507B1 (en) | Method and system for enabling and disabling execution of computer instructions |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |