table_rotate
NAME
table_rotate(): rotate a table logrotate-styleTYPE
ProcedureDESCRIPTION
This routine rotates a table much like logrotate rotates log files.
A call onto table_rotate renames (or versions) given table, while pushing already existing versions tables down the line. A new empty table is created to replace existing table. The "oldest" table is optionally dropped.
Table creation and dropping are done in the background, and table rotation for all tables is done as atomic operation via RENAME TABLE.
A rotated table is renamed by appending "__n", where n is the version number (1, 2, 3, ...).
SYNOPSIS
table_rotate(
IN table_schema varchar(64) charset utf8,
IN table_name varchar(64) charset utf8,
IN rotate_limit smallint unsigned
) Input:
- table_schema: schema of table to rotate
- table_name: name of table to rotate
- rotate_limit: maximum number of rotated tables to keep. Negative, 0 or NULL imply "no limitation".
EXAMPLES
Create a table, populate it, rotate it:
mysql> create table test.t select 17 as id from dual;
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| t |
+----------------+
mysql> select * from test.t;
+----+
| id |
+----+
| 17 |
+----+
mysql> call table_rotate('test', 't', 3);
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| t |
| t__1 |
+----------------+
mysql> call table_rotate('test', 't', 3);
mysql> call table_rotate('test', 't', 3);
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| t |
| t__1 |
| t__2 |
| t__3 |
+----------------+
mysql> select * from test.t;
Empty set (0.00 sec)
mysql> select * from test.t__3;
+----+
| id |
+----+
| 17 |
+----+
Note in the above how the value 17 is found in rotated table t__3, not in newly created empty t.
Further rotate the table:
mysql> call table_rotate('test', 't', 3);
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| t |
| t__1 |
| t__2 |
| t__3 |
+----------------+
mysql> select * from test.t__3;
Empty set (0.00 sec)
In the above rotated tables reached the rotate_limit count; the oldest was dropped. The value 17 is now lost.