Server Administration - MySQL - page 768

connection parameters that enable it to connect to the server to flush the logs, using an
account that has the RELOAD privilege. One strategy is to put the parameters in an option
file and pass the file to mysqladmin using a --defaults-file= file_name option. For
example:
#!/bin/sh
mv err.log err.log.old
mysqladmin --defaults-file=/usr/local/mysql/data/flush-opts.cnf flush-logs
22.5. Rotating Log Tables or Expiring Log Table Rows
Problem
Tables used for logging grow indefinitely unless managed.
Problem
Rotate the tables or expire rows within them.
Discussion
Recipe 22.4 discusses rotation and expiration of logfiles. Analogous techniques apply
to log tables:
• To rotate a log table, rename it and open a new table with the original name.
• To expire log table contents, remove rows older than a certain age.
The examples here demonstrate how to implement these methods using the general
query log table, mysql.general_log . The same methods apply to the slow query log
table, mysql.slow_log , or to any other table containing rows that have a timestamp.
Prime examples are the Apache log table in Recipe 20.14 , and the session-storage tables
in Chapter 21 .
To employ log table rotation, create an empty copy of the original table to serve as the
new table (see Recipe 4.1 ), then rename the original table and rename the new one to
take its place:
DROP TABLE IF EXISTS mysql . general_log_old , mysql . general_log_new ;
CREATE TABLE mysql . general_log_new LIKE mysql . general_log ;
RENAME TABLE mysql . general_log TO mysql . general_log_old ,
mysql . general_log_new TO mysql . general_log ;
To employ log row expiration, you can either empty the table completely or selectively:
• To empty a log table completely, truncate it:
TRUNCATE TABLE mysql . general_log ;