MYSQL log file maintenance [group chart] _ MySQL

MYSQL log file maintenance [group chart] 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 to be maintained depend on the server's busy schedule (the active server generates 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 (you can modify it to reflect the location of your log base name and data directory, or the number of old logs you want to retain ):

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