testrecord database stores player behavior records, which need to be cleaned up every three months.
Preparation before deleting data
To view the size of the testrecord database:
MySQL [(none)]> USE information_schema;
Database changed
MySQL [information_schema]> SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024/1024),2),'GB')
-> as DATA FROM TABLES WHERE
-> table_schema='testrecord';
+---------+
| DATA |
+---------+
| 56.70GB |
+---------+
1 row in set (0.00 sec)
View the size of each data table in testrecord database:
MySQL [information_schema]> SELECT
-> table_schema as 'database',
-> table_name as 'Table name',
-> table_rows as 'Number of records',
-> TRUNCATE(data_length/1024/1024/1024, 2) as 'Data capacity(GB)',
-> TRUNCATE(index_length/1024/1024/1024, 2) as 'Index capacity(GB)'
-> FROM information_schema.tables
-> WHERE table_schema='testrecord'
-> ORDER BY data_length DESC, index_length DESC;
+----------- +-------------------+-----------+----------------+----------------+
| database | Table name | Number of records | Data capacity(GB) | Index capacity(GB) |
+----------- +-------------------+----------------------------+----------------+
| testrecord | playeritem | 71206129 | 9.71 | 4.17 |
| testrecord | dropcord | 102342337 | 7.92 | 6.22 |
| testrecord | ipcord | 22256444 | 4.97 | 3.93 |
| testrecord | store | 16583137 | 2.58 | 1.20 |
| testrecord | teip | 6887136 | 2.43 | 1.28 |
From the above:
The first three data tables that need to clean up data are: playeritem, dropcord and ipcord
To view the SQL statement that created the data table:
MySQL [(none)]> USE testrecord;
Database changed
MySQL [testrecord]> SHOW CREATE TABLE playeritem \G
*************************** 1. row ***************************
Table: playeritem
Create Table: CREATE TABLE `playeritem` (
#---------Field structure omitted————————————————————————#
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
/*!50100 PARTITION BY RANGE (`cordTime`)
(PARTITION `playeritem_2021-05` VALUES LESS THAN (1622476800) ENGINE = InnoDB,
PARTITION `playeritem_2021-06` VALUES LESS THAN (1625068800) ENGINE = InnoDB,
PARTITION `playeritem_2021-07` VALUES LESS THAN (1627747200) ENGINE = InnoDB,
PARTITION `playeritem_2021-08` VALUES LESS THAN (1630425600) ENGINE = InnoDB,
PARTITION `playeritem_2021-09` VALUES LESS THAN (1633017600) ENGINE = InnoDB,
PARTITION `playeritem_2021-10` VALUES LESS THAN (1635696000) ENGINE = InnoDB,
PARTITION `playeritem_2021-11` VALUES LESS THAN (1638288000) ENGINE = InnoDB,
PARTITION `playeritem_2021-12` VALUES LESS THAN (1640966400) ENGINE = InnoDB,
PARTITION playeritem_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
MySQL [testrecord]> SHOW CREATE TABLE dropcord \G
*************************** 1. row ***************************
Table: dropcord
Create Table: CREATE TABLE `dropcord` (
#---------Field structure omitted————————————————————————#
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(cordtime))
(PARTITION p202105 VALUES LESS THAN (738307) ENGINE = InnoDB,
PARTITION p202106 VALUES LESS THAN (738337) ENGINE = InnoDB,
PARTITION p202107 VALUES LESS THAN (738368) ENGINE = InnoDB,
PARTITION p202108 VALUES LESS THAN (738399) ENGINE = InnoDB,
PARTITION p202109 VALUES LESS THAN (738429) ENGINE = InnoDB,
PARTITION p202110 VALUES LESS THAN (738460) ENGINE = InnoDB,
PARTITION p202111 VALUES LESS THAN (738490) ENGINE = InnoDB,
PARTITION p202112 VALUES LESS THAN (738521) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
MySQL [testrecord]> SHOW CREATE TABLE ipcord \G
*************************** 1. row ***************************
Table: ipcord
Create Table: CREATE TABLE `ipcord` (
#---------Field structure omitted————————————————————————#
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(cordtime))
(PARTITION p202105 VALUES LESS THAN (738307) ENGINE = InnoDB,
PARTITION p202106 VALUES LESS THAN (738337) ENGINE = InnoDB,
PARTITION p202107 VALUES LESS THAN (738368) ENGINE = InnoDB,
PARTITION p202108 VALUES LESS THAN (738399) ENGINE = InnoDB,
PARTITION p202109 VALUES LESS THAN (738429) ENGINE = InnoDB,
PARTITION p202110 VALUES LESS THAN (738460) ENGINE = InnoDB,
PARTITION p202111 VALUES LESS THAN (738490) ENGINE = InnoDB,
PARTITION p202112 VALUES LESS THAN (738521) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
From the above:
The data table adopts RNAGE partition, which is created in months and days respectively
View data table partition
MySQL [testrecord]> SELECT PARTITION_NAME part,
-> PARTITION_EXPRESSION expr,
-> PARTITION_DESCRIPTION descr,
-> TABLE_ROWS
-> FROM information_schema.PARTITIONS
-> WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = 'playeritem';
+--------------------+------------+------------+------------+
| part | expr | descr | TABLE_ROWS |
+--------------------+------------+------------+------------+
| playeritem_2021-05 | `cordTime` | 1622476800 | 96549342 |
| playeritem_2021-06 | `cordTime` | 1625068800 | 26259322 |
| playeritem_2021-07 | `cordTime` | 1627747200 | 44541353 |
| playeritem_2021-08 | `cordTime` | 1630425600 | 27246100 |
| playeritem_2021-09 | `cordTime` | 1633017600 | 29226451 |
| playeritem_2021-10 | `cordTime` | 1635696000 | 6806093 |
| playeritem_2021-11 | `cordTime` | 1638288000 | 0 |
| playeritem_2021-12 | `cordTime` | 1640966400 | 0 |
| playeritem_max | `cordTime` | MAXVALUE | 0 |
+------------------------+-------------------+------------+------------+
9 rows in set (0.00 sec)
MySQL [testrecord]> SELECT PARTITION_NAME part,
-> PARTITION_EXPRESSION expr,
-> PARTITION_DESCRIPTION descr,
-> TABLE_ROWS
-> FROM information_schema.PARTITIONS
-> WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = 'dropcord';
+---------+-------------------+----------+------------+
| part | expr | descr | TABLE_ROWS |
+---------+-------------------+----------+------------+
| p202105 | to_days(cordtime) | 738307 | 60519201 |
| p202106 | to_days(cordtime) | 738337 | 30759304 |
| p202107 | to_days(cordtime) | 738368 | 55525655 |
| p202108 | to_days(cordtime) | 738399 | 51800453 |
| p202109 | to_days(cordtime) | 738429 | 58897747 |
| p202110 | to_days(cordtime) | 738460 | 14470693 |
| p202111 | to_days(cordtime) | 738490 | 0 |
| p202112 | to_days(cordtime) | 738521 | 0 |
| pmax | to_days(cordtime) | MAXVALUE | 0 |
+---------+---------------------+----------+------------+
9 rows in set (0.00 sec)
MySQL [testrecord]> SELECT PARTITION_NAME part,
-> PARTITION_EXPRESSION expr,
-> PARTITION_DESCRIPTION descr,
-> TABLE_ROWS
-> FROM information_schema.PARTITIONS
-> WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = 'ipcord';
+---------+---------------------+----------+------------+
| part | expr | descr | TABLE_ROWS |
+---------+---------------------+----------+------------+
| p202105 | to_days(cordtime) | 738307 | 70512202 |
| p202106 | to_days(cordtime) | 738337 | 50759503 |
| p202107 | to_days(cordtime) | 738368 | 65529654 |
| p202108 | to_days(cordtime) | 738399 | 11692729 |
| p202109 | to_days(cordtime) | 738429 | 17989204 |
| p202110 | to_days(cordtime) | 738460 | 5010364 |
| p202111 | to_days(cordtime) | 738490 | 0 |
| p202112 | to_days(cordtime) | 738521 | 0 |
| pmax | to_days(cordtime) | MAXVALUE | 0 |
+---------+---------------------+----------+------------+
9 rows in set (0.00 sec)
Delete data
Delete the data of May, June and July in turn
ALTER TABLE playeritem DROP PARTITION playeritem_2021-05; ALTER TABLE dropcord DROP PARTITION p202105; ALTER TABLE ipcord DROP PARTITION p202105;
Check after deleting data
Check the partition table in turn
MySQL [testrecord]> SELECT PARTITION_NAME part,
-> PARTITION_EXPRESSION expr,
-> PARTITION_DESCRIPTION descr,
-> TABLE_ROWS
-> FROM information_schema.PARTITIONS
-> WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = 'dropcord';
+---------+---------------------+----------+------------+
| part | expr | descr | TABLE_ROWS |
+---------+---------------------+----------+------------+
| p202108 | to_days(recordtime) | 738399 | 51800453 |
| p202109 | to_days(recordtime) | 738429 | 58897747 |
| p202110 | to_days(recordtime) | 738460 | 14496732 |
| p202111 | to_days(recordtime) | 738490 | 0 |
| p202112 | to_days(recordtime) | 738521 | 0 |
| pmax | to_days(recordtime) | MAXVALUE | 0 |
+---------+---------------------+----------+------------+
6 rows in set (0.00 sec)
And check the data table size again to confirm whether the storage space has been released.
Write the above operations to the script
clear_table_partition.sh
#!/bin/sh
source /root/.bash_profile
for db in $(mysql_cmd -Ne "SHOW DATABASES;" | grep testrecord)
do
# look the table partitions
l_t_cmd="SELECT PARTITION_NAME part, \
PARTITION_EXPRESSION expr, \
PARTITION_DESCRIPTION descr, \
TABLE_ROWS \
FROM information_schema.PARTITIONS \
WHERE \
TABLE_SCHEMA = schema() \
AND TABLE_NAME = 'ipcord';"
echo $l_t_cmd
echo $db
mysql_cmd -e "USE $db; $l_t_cmd;"
# drop the table partitions
d_t_cmd="ALTER TABLE playeritem DROP PARTITION playeritem_2021-05;
ALTER TABLE playeritem DROP PARTITION playeritem_2021-06;
ALTER TABLE playeritem DROP PARTITION playeritem_2021-07;
ALTER TABLE dropcord DROP PARTITION p202105;
ALTER TABLE dropcord DROP PARTITION p202106;
ALTER TABLE dropcord DROP PARTITION p202107;
ALTER TABLE ipcord DROP PARTITION p202105;
ALTER TABLE ipcord DROP PARTITION p202106;
ALTER TABLE ipcord DROP PARTITION p202107;"
echo $d_t_cmd
echo $db
mysql_cmd -e "USE $db; $d_t_cmd;"
done
summary
1. The source command can read and execute commands from files in the current shell environment
2. Environment variables file.bash_ The profile is valid for the current login user. Set the login MySQL alias in the file to simplify the code:
3. Explanation of mysql command line tool option - Ne:
-e. -- execute = name means execute MySQL statement and exit
-N. The -- skip column names option means that the column names should not be written in the results
The difference between with and without - N option is as follows:
[root@mysql ~]# mysql -uroot -p -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ [root@mysql ~]# mysql -uroot -p -Ne "SHOW DATABASES;" +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
4. $() is the command replacement character of the shell. The shell will first execute the command in parentheses and return the standard output result of the command execution
5. Variable name l_t_cmd defines the statement to view the table partition, so we can know that the data table adopts table partition
6. echo ${l_t_cmd}, echo ${db}. Use echo to output the variable value, making the script readable during execution
7. Variable name d_t_cmd defines the statement to delete the table partition, that is, use alter table_ name DROP PARTITION part_ Name statement delete table partition
8,INFORMATION_SCHEMA is a database in each MySQL instance that stores detailed information about all other databases maintained by the MySQL server
9. When the table creation statement is found in the previous query, the number in the descr column represents the timestamp. How do you get the value of the timestamp?
Obtained by the following statement,
TO_ The day (date) function returns the number of days from January 1, 0000.
UNIX_ The timestamp (date) function returns the UNIX timestamp, the difference in seconds from '1970-01-01 00:00:00' to the current time
MariaDB [(none)]> SELECT TO_DAYS('2021-07-01 00:00:00');
+--------------------------------+
| TO_DAYS('2021-07-01 00:00:00') |
+--------------------------------+
| 738337 |
+--------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT UNIX_TIMESTAMP('2021-07-01 00:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2021-07-01 00:00:00') |
+---------------------------------------+
| 1625068800 |
+---------------------------------------+
1 row in set (0.00 sec)