MYSQL Log File Maintenance

When the MySQL server is started, it checks its command line operations to check whether it should perform logon and open the corresponding log file if necessary ). The server can generate two main types of log files: Regular log files. It reports client connections, queries, and various other events. It is useful for tracking server activity: Who is connecting, where is the connection, and what they are doing.

Update log

It reports queries for modifying databases. The term "UPDATE" in this context involves not only the UPDATE statement, but also all the statements for modifying the database. For this reason, it contains query records for d e l e t e, INSERT, REPLACE, create table, drop table, GRANT, and REVOKE. The content of the update log is written in the form of SQL statements, which are used as input to mysql. If the table must be restored after the crash, it is useful to update logs and back up the table. You can restore the database from the backup file, and then use the Update log as the input to mysql, re-run the backup file and modify any query of the database. In this way, the table can be restored to the State at the time of crash.

To make the log valid, you can use the -- log option to enable regular logs and use the -- log-update option to enable log update. You can specify these options in the command line of mysqld. safe_mysqld or mysql. server, or in the [mysqld] group of an option. When the log is valid, the log file is written to the server's data directory due to lack of time.

We recommend that you use both log types when using MySQL for the first time. After gaining some experience in using MySQL, you may only need to update logs to reduce disk space requirements.

After logs are valid, make sure that you do not need to fill the disk with a large amount of log information, especially if the server is processing a large number of queries. The log file cycle and cutoff time can be used to ensure that the latest logs are available online while avoiding the non-boundary growth of log files.

The log file cyclically works as follows. Assume that the log file name is l o g. In the first loop, log is renamed as l o g. 0, and the server starts to write a new l o g file. In the second loop, log.0 is renamed to l o g. 1, log is renamed to l o g. 0, and the server starts to write a new log file. In this way, each file is cyclically named l o g. 0, l o g. 1, and so on. When a file reaches a certain point of the loop, it can be terminated.

Update logs and load data statements

Generally, when the server executes the load date statement, it only writes the statement itself instead of the loaded row content to the Update log. This means that, unless the data file remains accessible, the restoration operations using the Update log will be incomplete. To ensure this security, data files should not be deleted unless the database has been backed up.

System Backup

The Update log is not always good for database recovery. If a disk crash causes you to lose the Update log, ensure that you perform regular file system backup. It is also a good idea to write the Update log to a disk that is different from the storage database.

For example, if you cycle logs every day and want to keep logs for one week, you should keep log.0 to l o g. 6. In the next loop, log.5 overwrites log.6 to create log.6 to terminate lO g. 6. In this way, you can retain a lot of logs and avoid them exceeding the disk limit.

The log cycle frequency and the number of old logs maintained will depend on the server's busy activity to generate more log information) and the disk space you want to invest in the old log. When circulating 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.

The script for executing a regular log loop is similar to the following to modify it to reflect the location of your log base name and data directory, and the number of old logs to be retained ):

It is best to run this script from the mysqladm account to ensure that the log file belongs to that user. If you retain the connection parameters in the. my. cnf option file, you do not need to specify any parameters in the mysqladmin command of this script. If you do not do this, you can create a restricted user, which has nothing to do with the release of the refresh command. Then, the user's password can be placed in the script at minimal risk. To do this, the user should have only the RELOAD permission. For example, to call 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 the refresh operation in the 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 package, instead of writing the script by yourself. If mysql-log-rotate is not automatically installed through the RPM file, check the support-files directory of the MySQL distribution package.

Because the server processes the Update log file in different ways, the log file cycle is slightly different between the Update log and the regular log. If you tell the server to use an Update log file name without an extension, such as up date, the server will automatically create an Update log file name using the order up date. 0 0 1, update.002, and so on. When the server is started and the log is refreshed, a new update log is generated. If you enable the Update log without specifying the file name, the server uses the host name as the base name to generate a sequence of Update log files.

When terminating a sequence of files generated by this method, you may want to terminate them based on the time at which they are last modified) rather than by name. The reason for this is that you do not know when the flush-log command will be released, so you cannot expect to create a fixed number of update logs in any given time period. For example, if you use mysqldump to back up a table and the -- flush-logs option, a new file in the log name sequence will be created with each backup.

For update logs with sequential file names automatically generated by the server, the termination script based on the log term is similar to the following:

The find command locates and deletes the Update log files that have been modified for more than one week. It is important to use the-name parameter to test the file extension of a number to avoid deleting the table specified by the incorrect update.

You can also tell the server to use a fixed Update log file name if desired. This is useful if you want to use the same method as regular logs to update logs cyclically. To use a fixed log update name, specify a name containing the extension. For example, you can use the -- log-update = update. log option to start the server and use the name up date. l o g. The server will always shut down and open the log when receiving the flush-logs command, but the server does not generate new files every time. In this case, the log loop script used to update logs and the script used for regular logs are only different on the base name of the cyclic file.

If you want to automatically execute the log loop and terminate, you can use c r o n. Assume that the scripts for General Logging and log updates are rotate-logs and r o t a t e-up date-l o g s, and install it in the/usr/user/mysql/bin directory. Register with the mysqlladm user and run the following command to edit the crontab file of the mysqladm User: % crontab-e

This command allows you to edit the backup of the current crontab file. If this is not done before, it may be empty ). Add rows to the file as follows:

This tells cron to run the script at every morning. You can change the time or schedule as needed. For more information, see the crontab manual page.