When the MySQL server starts, it checks its command line for operations to see if it should perform a login and opens the appropriate log file (if so). You can have the server generate two main types of log files: Regular log files. It reports client connections, queries, and various other events. When the MySQL server starts, it checks its command line for operations to see if it should perform a login and opens the appropriate log file (if so). 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's useful for tracking server activity: who is connecting, from where, and what they are doing.
Change log
It reports queries that modify the database. The term "update" in this context refers not only to UPDATE statements, but to all statements that modify the database. For this reason, it contains query records for DELETE, 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. Update logs and backups are useful if the table must be restored after a crash. You can restore the database from a backup file and then rerun any queries that modified the database after that backup file by using the update log as input to mysql. This restores the table to its state at the time of the crash.
To make the logs effective, use the --log option to enable regular logging and the --log-update option to enable update logging. These options can be specified on the command line in mysqld.safe_mysqld or mysql.server, or in the [mysqld] group of an option. When logging is enabled, log files are written to the server's data directory by default.
The author recommends that both log types be valid when using MySQL for the first time. After you gain some experience with MySQL, you may be tempted to just use the update log to reduce disk space requirements.
After enabling logging, be sure not to fill up the disk with a large amount of log information, especially if the server is handling a large number of queries. Log file rotation and expiration times can be used to prevent log files from growing unbounded while keeping the most recent logs available online.
Log file rotation works as follows. Assume that the log file is named log. In the first loop, log is renamed log . 0 and the server starts writing a new log file. In the second loop, log.0 is renamed to log.1, log is renamed to log.0, and the server starts writing another new log file. This way, each file cycles through the names log . 0, log . 1, and so on. When the file reaches a certain point in the loop, it can be terminated.
Update logs and LOAD DATA statements
Normally, when the server executes a LOAD DATE statement, it writes only the statement itself, not the loaded row contents, to the update log. This means that recovery operations using the update log will be incomplete unless the data files remain accessible. To ensure this is safe, 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 if a disk crash causes you to lose your update logs, make sure you perform regular file system backups. It's also a good idea to write the update log to a different disk than where the database is stored.
For example, if you cycle logs every day and want to keep logs for a week, you would keep log.0 to log.6. In the next loop, log.6 will be terminated by letting log.5 overwrite log.6 to become the new log.6. This way, you can keep many logs without exceeding the disk limit.
The frequency of log rotation and the number of old logs kept will depend on how busy the server is (active servers produce more log messages) and how much disk space you want to devote to old logs. When rotating 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 to perform a regular log rotation would look something like this (this can be modified to reflect your log base name and the location of your data directory, and perhaps the number of old logs you wish to retain):
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 options file, you do not need to specify any parameters in the mysqladmin command of the script. If you don't do this you can create a restricted user that does nothing but issue refresh commands. This user's password can then be placed in the script with minimal risk. If you want to do this, the user should only have RELOAD permissions. For example, to invoke user flush and assign a password, fl us h pass, 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 -ufush -pflushpass flush -logs
In Linux, it is better to use logrotate to install the mysql-log-rotate script in the MySQL distribution package rather than writing the script yourself. If mysql-log-rotate is not installed automatically through an RPM file, you should check the support-files directory of the MySQL distribution package.
Due to differences in the way servers handle update log files, the rotation of log files differs slightly between update logs and regular logs. If you tell the server to use an update log file name without an extension (such as up date), the server will automatically create update log file names using the sequence up date. 0 0 1, update.002, etc. A new update log is generated when the server starts and when the log is refreshed. If you enable update logging without specifying a file name, the server generates a sequence of update log files using the hostname as the base name.
When terminating a sequence of files generated this way, you may want to terminate them based on their age (the time they were last modified) rather than by name. The reason for this is that since you don't know when the flush-log command will be issued, you cannot expect to create a fixed number of update logs in any given time period. For example, if you back up a table with mysqldump and use the --flush-logs option, a new file in that sequence of update log names is created with each backup.
For update logs with sequential file names automatically generated by the server, the log age-based termination script looks like this:
The find command locates and deletes update log files that are modified more than one week ago. It is important to use the -name parameter to test for a numeric file extension to avoid deleting tables specified by the wrong 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 the update log in the same way as a regular log. To use a fixed update log name, specify a name that includes an extension. For example, you can start the server with the --log-update=update.log option to use the name up date .log. The server will always shut down and open the log when receiving the flush-logs command, but the server will not generate new files every time. In this case, the log rotation script for the update log and the script for the regular log differ only in the file base name of the rotation.
If you want to automate log rotation and termination, use cron. It is assumed that the scripts for rotating regular logs and update logs are rotate-logs and rotate - up date - logs and are installed in the /usr/user/mysql/bin directory. Register as the mysqlladm user, and then use the following command to edit the mysqladm user's crontab file: % crontab -e
This command allows editing of the current crontab file's backup (it may be empty if this has not been done previously). Add lines to the file as follows:
This entry tells cron to run this script every morning at 4 am. You can change the time or schedule it as needed. See the crontab manual page for instructions.
-