...
A table named metering_archive exists in the Abiquo database schema, which is an actual copy of the metering table designed to contain older events data. Data can be migrated from the metering table to the metering_archive table by calling the following procedure:
| Code Block |
|---|
CALL kinton.ArchiveMeteringData(90); |
The number supplied to the stored procedure is the number of days old that the data must be to migrate into the archive table.
...
Additionally, it is possible to automate the archiving process by creating a a database event as shown below:
...
| Code Block |
|---|
USE kinton;
CREATE EVENT IF NOT EXISTS metering_archive_job
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 DAY
COMMENT 'Migrates metering data into the archive table'
DO
CALL kinton.ArchiveMeteringData(90); |
You will need to make sure the event scheduler in MariaDB is turned on:
| Code Block |
|---|
MariaDB [kinton]> select @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | OFF | +-------------------+ 1 row in set (0.00 sec) |
To turn it on you can just change the value of the global variable:
| Code Block |
|---|
MariaDB [kinton]> SET GLOBAL event_scheduler = ON; Query OK, 0 rows affected (0.00 sec) MariaDB [kinton]> select @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | ON | +-------------------+ 1 row in set (0.00 sec) |
Now you will be able to see the event scheduler in the process list:
| Code Block |
|---|
MariaDB [kinton]> SHOW PROCESSLIST; +--------+-----------------+-------------------+------------+---------+------+-----------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+-----------------+-------------------+------------+---------+------+-----------------------------+------------------+----------+ ... | 594892 | event_scheduler | localhost | NULL | Daemon | 4 | Waiting for next activation | NULL | 0.000 | ... +--------+-----------------+-------------------+------------+---------+------+-----------------------------+------------------+----------+ 38 rows in set (0.00 sec) |
More info on MariaDB events can be found at MariaDB documentation.