Introduction to some of the ways to manipulate MySQL logs

This article mainly introduces the operation of the MySQL log some methods, the deletion of the log to do a key explanation, the need for friends can refer to the

We can see the MySQL in the installation directory of MySQL binary log files, such as mysql-bin.000***, many people are not in time to deal with, resulting in the entire hard drive is stuffed is also possible. These are the operational logs of the database. It records the SQL statement commands we normally use, even if the SQL statement is not executed, it will be recorded, including execution time and so on in detail, in fact, it has two purposes: first, it is convenient for us to check and restore the database, and the second is to synchronize data between the master and the server, The master and slave server All operations are recorded in the log, from the server can be based on these logs to do the same operation to ensure that the master and slave two synchronization.

To view the MySQL log command:

?

1 2 3 4 5 6 7 8 9 10 11

#mysql-U user-p//user on behalf of your MySQL username Enter password: (enter password)//Enter the password to enter the MySQL console mysql> show master logs; Show MySQL log file +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000010 | 63328652 | +------------------+-----------+ 1 row in Set (0.06 sec)

You can also enter the following command in MySQL's command mode:

?

1 2 3 4 5 6 7

Mysql> show binary logs; View binary log file list and size +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000010 | 63355376 | +------------------+-----------+ 1 row in Set (0.00 sec)

Master and binary are synonyms.

To delete a MySQL binary log:

The first method is for a separate MySQL server, which is not suitable for master-slave synchronization. Remember, of course, if you make sure your master-slave sync is done, you can use this command too, but I don't really recommend it.

The first method: Mysql>reset master; Clear all MySQL binary log files

Master-Slave synchronization, the proposed use of the following two methods:

First, we need to know whether the log is being read from the MySQL server, or which log is being read.

Enter the command on the secondary server:

?

1 2 3

Mysql>show slave hosts//view all information connected to master slave mysql>show slave status; Check which log is being read from the server

The second and third methods below will fail when you are reading the log you are deleting from the server, and there will be an error. If the secondary server does not read the log and you delete the log to read after it starts, it will not replicate when it starts. Therefore, the following two methods can safely delete log files.

Master-Slave Server cleanup log operation steps are as follows:

On each subordinate server, use Show SLAVE status to check which log it is reading.

Use show master logs to obtain a series of logs on the primary server.

Determine the oldest log in all subordinate servers, this is the target log, and if all the subordinate servers are updated, the last log on the list.

Clears all logs, but does not include the target log because it is synchronized with the server.

Note: It is best to do a backup of all the logs you want to delete, there are backups, there is no fear of error

The second method of deleting binary logs:

?

1	PURGE {MASTER | BINARY} LOGS to ' Log_name '

Example:

?

1	Mysql>purge master logs to ' mysql-bin.000800 '; Clear the binary log before mysql-bin.000800

The third way to delete a binary log:

?

1	PURGE {MASTER | BINARY} LOGS before ' date '

Example:

?

1	Mysql>purge master logs before ' 2012-12-22 12:12:12 '; Delete binary files before 2012-12-22 12:12:12 this time

The fourth approach is to modify the My.cnf file and add in [mysqld]:

?

1	Expire_logs_day=5//sets the number of days for the log to expire, which is automatically deleted after a specified number of days

Fifth way: Disable the MySQL logging function (single server)

Modify the my.cnf and comment out the following two lines:

?

1 2	#log-bin=mysql-bin #binlog_format =mixed

and restart MySQL.