MySQL log file maintenance

MySQL when the MySQL server is started, it checks its command line action to see if it should perform a login and open the appropriate log file (if it should). You can have the server generate two main types of log files:
Regular log files. It reports client connections, queries, and various other events. It is useful for the activities of the service: who is connecting, where to connect, and what they are doing.
Update the log. It reports queries that modify the database. The term "Update" in this context does not only involve update statements, but it also involves modifying all the statements of the database. For this reason, it contains a search for D e L e T E, INSERT, REPLACE, CREATE table, DROP TABLE, GRANT, and revoke
Inquiry record. The contents of the update log are written as SQL statements, which are used as input to MySQL. Updating logs and backups is useful if you have to recover the table after a crash. You can recover the database from the backup file, and then rerun any queries that modify the database after the backup file by using the update log as input to MySQL. In this way, the table can be restored to the state of the crash moment.
For the log to be valid, use the--log option to turn on the regular log and open the update log with the--log-update option. You can specify these options in the Mysqld.safe_mysqld or Mysql.server command line, or in the [mysqld] group of an option. Log files are written to the server's data directory by default when the journal is valid.
The author recommends that both types of log should be valid for the first time you use MySQL. After gaining some experience with MySQL, you might want to use only the update log to reduce disk space requirements.
After making the log valid, make sure that the disk is not filled with large amounts of log information, especially if the server is processing a large number of queries. You can use log file loops and deadlines to keep the last few logs available online while avoiding the growth of log files without boundaries.
The log file loop works as follows. Assume the log file name is L o G. In the first loop, log is renamed L o G. 0, and the server begins to write a new l o G file. In the second cycle, the log.0 is renamed L o G. 1,log renamed L o G. 0, the server begins to write another new log file. In this way, each file loops through the name L o G. 0, L o G. 1, and so on. When a file reaches a point in the loop, it can be terminated.
Update log and load DATA statements
Typically, when the server executes the load DATE statement, it writes only the contents of the statement itself, not the loaded row, to the update log. This means that the recovery operation using the update log will be incomplete unless the data file remains accessible. To ensure this security, data files should not be deleted unless the database has been backed up.
System backup
Update logs are not always good for database recovery, and you should make sure that you perform regular file system backups if a disk crash causes you to lose the update log. It is also a good idea to write the update log to a disk that is not the same as the storage database. For an introduction to reloading a log file, see the "MySQL Data Directory" in chapter 10th
For example, if you cycle the log every day and want to keep the log for a week, you should keep log.0 to L o G. 6. In the next loop, the log.5 will be overwritten by log.6 to make it a new log.6 to terminate l o G. 6. This allows you to keep a number of logs and avoid them exceeding the disk limit.
The log cycle frequency and the number of old logs kept will depend on how busy the server is (the Active server generates more log information) and how much disk space you want to put into the old log. When looping through regular logs, you can use the Mysqla d-min flush-logs command to tell the server to close the current log file and open a new log file.
A script that performs a regular log loop resembles the following (you can modify it to reflect the location of your log base name and data directory, and perhaps the number of old logs that you want to keep):

It is best to run this script from the MYSQLADM account to ensure that the log files belong to that user. If you leave the connection parameters in the. my.cnf option file, you do not need to specify any parameters in the script's Mysqladmin command. If you do not, you can create a limited user that does nothing but publish the Refresh command. The user's password can then be placed in the script with minimal risk. If you want to do this, the user should have only reload permissions. For example, to invoke the user flush and assign a password F l US h pass, you can use the following grant statement:
GRANT RELOAD on *.* to Flush@localhost identifiedby "Flushpass"
When you need to perform a refresh operation in a script, you can do this:
Mysqladmin-uflush-pflushpass Flush-logs
In Linux, it is best to use Logrotate to install the mysql-log-rotate script in the MySQL distribution, instead of writing your own script. If the mysql-log-rotate does not automatically install through the rpm file, you should view the Support-files directory of the MySQL distribution.
Because the server handles update log files differently, the loop of the log files is slightly different between the update log and the regular log. If you tell the server to use an update log file name that does not have an extension, such as up date, the server will use the sequential up date. 0 0 1, update.002, etc. automatically create update log file name. A new update log is generated when the server is started and the log is refreshed. If you turn on the update log without specifying a file name, the server uses the hostname as the base name to produce a sequence of update log files.
When you terminate a sequence of files generated by this method, you may want to terminate them based on their duration (the time last modified) rather than the name. The reason for this is that you do not know when the Flush-log command will be published, so you cannot expect to create a fixed number of update logs for any given time period. For example, if you use mysqldump to back up a table and use the--flush-logs option, a new file in the update log name sequence is created with each backup.
For an update log with a sequential file name that is automatically generated by the server, the expiration script based on the log age resembles the following:

The Find command locates and deletes update log files that have been modified for more than one weeks. It is important to use the-name parameter to test the file name extension of a number to avoid deleting the table specified by the error update.
You can also tell the server to use a fixed update log file name (if desired), which is useful if you want to cycle through the logs in the same way as regular logs. To use a fixed update log name, you should specify a name that contains the extension. For example, you can start the server with the--log-update=update.log option to use the name up date. L o G. The server shuts down and opens the log when the Flush-logs command is received, but the server does not produce new files every time. In this case, the log loop script used to update the log and the script used for the regular log differ only on the file base name of the loop.
If you want to automate log loops and terminations, you can use the C R o N. The scripts for the circular and update logs are assumed to be rotate-logs and r o t a T e-up date-l o G S, and are installed in the/usr/user/mysql/bin directory. Register with the Mysqlladm user, and then edit the Mysqladm user's crontab file with the following command:
% CRONTAB-E
This command allows you to edit the backup of the current crontab file (it may be empty if it was not previously done). Add a row to the file as follows:

This item tells Cron to run this script at 4 o ' Day every morning. You can change the time or schedule as needed. For instructions, see the CRONTAB manual page.