Method and system for automatically creating table partition of MySQL dynamic table range
By using Linux shell scripts and crontab for scheduled operations, we can automate the management of MySQL table partitions, which solves the problems of high cost and error-proneness caused by manual maintenance. It can dynamically adapt to newly added tables, control the data scale, and improve the efficiency of operation and maintenance and query performance.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- INSPUR TIANYUAN COMM INFORMATION SYST CO LTD
- Filing Date
- 2026-02-09
- Publication Date
- 2026-06-02
Smart Images
Figure CN122132404A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database technology, specifically to a method and system for automatically creating table partitions using dynamic table ranges in MySQL. Background Technology
[0002] Existing MySQL table partitioning methods require manually executing the ALTER TABLE command to create or delete partitions (e.g., partitioning by time range). The disadvantages are high maintenance costs and a high risk of errors. While specific tables can be maintained using scripts, they cannot dynamically adapt to newly added tables, leading to decreased read / write performance when new tables experience data expansion, requiring manual intervention.
[0003] How to solve the inefficiency of manually maintaining table partitions, achieve dynamic partition management for newly added tables, and reduce the operational burden and risks of human operation are technical problems that need to be solved. Summary of the Invention
[0004] The technical objective of this invention is to address the above-mentioned shortcomings by providing a method and system for automatically creating table partitions for dynamic table ranges in MySQL. This addresses the inefficiency of manually maintaining table partitions, enables dynamic partition management for newly added tables, and reduces operational burden and human error risks.
[0005] In a first aspect, the present invention provides a method for automatically creating table partitions using a dynamic table range in MySQL, comprising the following steps: Tables to be maintained: Dynamically obtain the list of table names of the partitions to be maintained in a specified MySQL database using a Linux shell script, and use the tables in the list as the target tables; Table partition management: For each target table, create new partitions for each table according to time periods using Linux shell scripts, and delete historical data partitions; Partition maintenance: Fully automated partition maintenance is achieved through Linux crontab scheduled scripts.
[0006] As a preferred approach, the deletion of historical data partitions is based on a time period, and expired data partitions are removed periodically to maintain data volume.
[0007] As a preferred method, when dynamically obtaining the list of table names for the partitions to be maintained in a specified MySQL database using a Linux shell script, this can be achieved by querying the information_schema.tables system table to ensure that newly added tables are included.
[0008] As a preferred option, the partition time threshold is dynamically generated using the Shell date command, and custom time offsets are supported.
[0009] As a preferred approach, when creating new partitions for each table and deleting historical data partitions using Linux shell scripts according to time periods, the partition status of each target table is checked to avoid repeatedly creating existing partitions or deleting non-existent partitions.
[0010] Secondly, the present invention provides a system for automatically creating table partitions for dynamic MySQL tables, comprising a table collection module, a table partition management module, and a partition maintenance module. The table collection module is used to perform the following: dynamically obtain the list of table names of the partitions to be maintained in the specified MySQL database through a Linux shell script, and use the table corresponding to the table name in the list as the target table; The table partition management module is used to perform the following: For each target table, create new partitions for each table according to the time period using Linux shell scripts, and delete historical data partitions; The partition maintenance module is used to perform the following: fully automate partition maintenance through Linux crontab scheduled scripts.
[0011] As a preferred approach, when performing the operation of deleting historical data partitions, time periods should be used as a benchmark to periodically remove expired data partitions in order to maintain data scale.
[0012] As a preferred method, when dynamically obtaining the list of table names for the partitions to be maintained in a specified MySQL database via a Linux shell script, the table collection module is used to achieve this by querying the information_schema.tables system table to ensure that newly added tables are included.
[0013] Preferably, the table partition management module is used to dynamically generate partition time thresholds via Shell date commands and supports custom time offsets.
[0014] As a preferred approach, when creating new partitions for each table according to time periods and deleting historical data partitions via Linux shell scripts, the table partition management module is used to detect the partition status of each target table to avoid repeatedly creating existing partitions or deleting non-existent partitions.
[0015] The method and system for automatically creating table partitions for dynamic table ranges in MySQL of the present invention have the following advantages: 1. Dynamically adapt to newly added tables: Automatically retrieve all tables in the current database (including newly added tables) every month, eliminating the need to manually maintain the table name list; 2. Controllable data scale: Automatically deletes partitions older than 6 months (time adjustable) to prevent unlimited data growth; 3. Efficiency Improvement: Fully automated operation and maintenance reduces labor costs, and partitioning strategies ensure query performance under large data volumes. Attached Figure Description
[0016] To more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0017] The invention will be further described below with reference to the accompanying drawings.
[0018] Figure 1 This is a flowchart of a method for automatically creating table partitions using a dynamic table range in MySQL, as described in Example 1. Figure 2 This is a flowchart illustrating the automatic table partition maintenance process in Example 1, a method for automatically creating table partitions using a dynamic table range in MySQL. Figure 3 This is a schematic diagram of the partition timeline in Example 1, a method for automatically creating table partitions using dynamic table ranges in MySQL. Figure 4 This is a schematic diagram illustrating the interaction between the Shell script and MySQL in Example 1, a method for automatically creating table partitions using a dynamic table range in MySQL. Figure 5 This is a flowchart illustrating the automatic maintenance process of MySQL dynamic table partitions in Example 1, a method for automatically creating table partitions within a MySQL dynamic table range. Detailed Implementation
[0019] The present invention will be further described below with reference to the accompanying drawings and specific embodiments, so that those skilled in the art can better understand and implement the present invention. However, the embodiments are not intended to limit the present invention. In the absence of conflict, the embodiments of the present invention and the technical features in the embodiments can be combined with each other.
[0020] This invention provides a method and system for automatically creating table partitions for dynamic table ranges in MySQL, which solves the problem of inefficiency in manually maintaining table partitions, enables dynamic partition management of newly added tables, and reduces the operational burden and risks of human operation. Example
[0021] This invention provides a method for automatically creating table partitions within a dynamic range of MySQL tables, comprising three steps: collection of tables to be maintained, table partition management, and partition maintenance.
[0022] Step S100: Table collection to be maintained: Dynamically obtain the list of table names of the partitions to be maintained in the specified MySQL database using a Linux shell script, and use the tables in the list of table names as the target tables.
[0023] As a specific implementation of the table collection process, when dynamically obtaining the list of table names for the partitions to be maintained in a specified MySQL database using a Linux shell script, the system table information_schema.tables is queried to ensure that newly added tables are included.
[0024] Step S200 Table Partition Management: For each target table, create new partitions for each table according to the time period using Linux shell scripts, and delete historical data partitions.
[0025] As a specific implementation of table partition management, the operation of deleting historical data partitions is based on time periods. Expired data partitions are removed periodically to maintain data scale. The partition time threshold is dynamically generated through the Shell date command, and custom time offsets are supported.
[0026] Step S300 Partition Maintenance: Fully automated partition maintenance is achieved through a Linux crontab scheduled script.
[0027] In this embodiment, before executing step S100, MySQL parameters need to be configured. Specifically, the MySQL main configuration file ` / etc / my.cnf` is configured to allow MySQL to input the list of table names for the partitions that need to be maintained into a specified directory on the Linux server, for example, specifying the output directory as ` / opt / mysqlshell / `. Modifying the `my.cnf` configuration file requires restarting MySQL for the changes to take effect.
[0028] In step S200, during table partition management, define the partition variable to be created (partition_name1, partitioned by month), and define the partition variable to be deleted (partition_name6); obtain the list of table names for which partitions need to be maintained and write it to the file / opt / mysqlshell / list_$partition_name1.txt; use a for loop to sequentially read the table names for which partitions need to be maintained and create new table partitions and delete historical partitions for these tables.
[0029] During step S300 partition maintenance, the script is set to execute periodically via crontab as follows: Create new table partitions and delete data from old table partitions at midnight on the 1st of each month. The code is as follows: 0 0 1 * * / opt / mysqlshell / paratition_by_month.sh.
[0030] During the execution of this embodiment, a list of table names is dynamically obtained, and newly added tables are dynamically adapted using the `SELECT table_name FROM information_schema.tables` method. The output file path includes the time variable `list_$partition_name1.txt`.
[0031] When managing partitions, a dual-loop structure is supported, specifically: (1) Partition creation loop: Execute ADD PARTITION $partition_name1 (e.g., p202507); (2) Partition deletion loop: Execute DROP PARTITION $partition_name6 (e.g., p202412).
[0032] The time-based face-changing design follows these rules: partition name variables $partition_name1 (current month) and $partition_name6 (6 months ago).
[0033] The method in this embodiment obtains the range of tables that need to be maintained in each cycle through a Linux shell script. This script creates and deletes partitions for these tables, and the automatic maintenance of table partitions is achieved by periodically calling this script through Linux crontab. Example
[0034] This invention relates to a system for automatically creating table partitions within a dynamic range of MySQL tables, comprising a table collection module, a table partition management module, and a partition maintenance module.
[0035] The maintenance table collection module is used to perform the following: dynamically obtain the list of table names of the partitions to be maintained in the specified MySQL database through a Linux shell script, and use the tables in the list of table names as the target tables.
[0036] As a specific implementation of the table collection module that needs to be maintained, this module dynamically obtains the list of table names of the partitions that need to be maintained in a specified MySQL database through a Linux shell script. It does so by querying the information_schema.tables system table to ensure that newly added tables are included.
[0037] The table partition management module is used to perform the following: for each target table, create new partitions for each table according to the time period, and delete historical data partitions.
[0038] As a specific implementation of the table partition management module, this module deletes historical data partitions based on time periods, periodically removing expired data partitions to maintain data size. The partition time threshold is dynamically generated through the Shell date command, and custom time offsets are supported.
[0039] The partition maintenance module is used to perform the following: fully automate partition maintenance through Linux crontab scheduled scripts.
[0040] In this embodiment, before the table name acquisition module obtains the table name list, MySQL parameters need to be configured. Specifically, the MySQL main configuration file ` / etc / my.cnf` is configured to allow MySQL to input the obtained list of table names for the partitions requiring maintenance into a specified directory on the Linux server, for example, specifying the output directory as ` / opt / mysqlshell / `. Modifying the `my.cnf` configuration file requires a MySQL restart to take effect.
[0041] When the table partitioning management module performs table partitioning, it executes the following operations: defines the partition variable to be created (partition_name1, partitioned by month), and defines the partition variable to be deleted (partition_name6); it retrieves the list of table names for which partitions need to be maintained and writes it to the file / opt / mysqlshell / list_$partition_name1.txt. During table partitioning management operations, it uses a for loop to sequentially read the table names for which partitions need to be maintained and creates new table partitions and deletes historical partitions for these tables.
[0042] When the partition maintenance module performs automatic table partition maintenance, the script is set to execute periodically via crontab as follows: Create new table partitions and delete data from old table partitions at midnight on the 1st of each month. The code is as follows: 0 0 1 * * / opt / mysqlshell / paratition_by_month.sh.
[0043] During system execution in this embodiment, a list of table names is dynamically obtained, and newly added tables are dynamically adapted by using SELECT table_name FROM information_schema.tables. The file output path includes the time variable list_$partition_name1.txt.
[0044] When managing partitions, a dual-loop structure is supported, specifically: (1) Partition creation loop: Execute ADD PARTITION $partition_name1 (e.g., p202507); (2) Partition deletion loop: Execute DROP PARTITION $partition_name6 (e.g., p202412).
[0045] The time-based face-changing design follows these rules: partition name variables $partition_name1 (current month) and $partition_name6 (6 months ago).
[0046] The system in this embodiment can execute the method disclosed in Embodiment 1 to automatically create table partitions for dynamic table ranges in MySQL.
[0047] The above provides a detailed description of the method and system for automatically creating table partitions in MySQL dynamic table ranges provided by this invention. Specific examples have been used to illustrate the principles and implementation methods of this invention. The descriptions of the above embodiments are only for the purpose of helping to understand the method and core ideas of this invention. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this invention. Therefore, the content of this specification should not be construed as a limitation of this invention.
Claims
1. A method for automatically creating table partitions from a dynamic table range in MySQL, characterized in that, Includes the following steps: Tables to be maintained: Dynamically obtain the list of table names of the partitions to be maintained in a specified MySQL database using a Linux shell script, and use the tables in the list as the target tables; Table partition management: For each target table, create new partitions for each table according to time periods using Linux shell scripts, and delete historical data partitions; Partition maintenance: Fully automated partition maintenance is achieved through Linux crontab scheduled scripts.
2. The method for automatically creating table partitions for dynamic table ranges in MySQL according to claim 1, characterized in that, The deletion of historical data partitions is based on a time period, and expired data partitions are removed periodically to maintain data size.
3. The method for automatically creating table partitions for dynamic table ranges in MySQL according to claim 1, characterized in that, When dynamically retrieving the list of table names for partitions that need to be maintained in a specified MySQL database using a Linux shell script, this is achieved by querying the information_schema.tables system table, ensuring that newly added tables are included.
4. The method for automatically creating table partitions for dynamic table ranges in MySQL according to claim 1, characterized in that, The partition time threshold is dynamically generated using the Shell date command, and custom time offsets are supported.
5. The method for automatically creating table partitions for dynamic table ranges in MySQL according to claim 1, characterized in that, When creating new partitions for each table and deleting historical data partitions according to time periods using Linux shell scripts, the partition status of each target table is checked to avoid duplicate creation of existing partitions or deletion of non-existent partitions.
6. A system for automatically creating table partitions from a dynamic table range in MySQL, characterized in that, This includes the table collection module, the table partition management module, and the partition maintenance module. The table collection module is used to perform the following: dynamically obtain the list of table names of the partitions to be maintained in the specified MySQL database through a Linux shell script, and use the table corresponding to the table name in the list as the target table; The table partition management module is used to perform the following: For each target table, create new partitions for each table according to the time period using Linux shell scripts, and delete historical data partitions; The partition maintenance module is used to perform the following: fully automate partition maintenance through Linux crontab scheduled scripts.
7. The system for automatically creating table partitions for dynamic table ranges in MySQL according to claim 6, characterized in that, When performing the operation of deleting historical data partitions, the time period is used as the basis to periodically remove expired data partitions in order to maintain the data scale.
8. The system for automatically creating table partitions for dynamic table ranges in MySQL according to claim 6, characterized in that, When dynamically retrieving the list of table names for a specified partition in a MySQL database that needs maintenance via a Linux shell script, the maintenance table collection module uses the information_schema.tables system table to ensure that newly added tables are included.
9. The system for automatically creating table partitions for dynamic table ranges in MySQL according to claim 6, characterized in that, The table partition management module is used to dynamically generate partition time thresholds using Shell date commands and supports custom time offsets.
10. The system for automatically creating table partitions for dynamic table ranges in MySQL according to claim 6, characterized in that, When creating new partitions for each table according to time periods and deleting historical data partitions via Linux shell scripts, the table partition management module is used to detect the partition status of each target table to avoid repeatedly creating existing partitions or deleting non-existent partitions.