Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to delete and restore binary logs in MySQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

# vim / etc/my.cnf

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

# old_passwords=1

Table_cache = 300

Default-character-set = utf8

Log = / var/lib/mysqllog/mysql.log

Log-bin = / var/lib/mysqllog/log-bin

Log-slow-queries = / var/lib/mysqllog/slowquery.log

Long_query_time=2

[mysql.server]

User=mysql

Basedir=/var/lib

[mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

In which the boldface content is added, the italic log-error=/var/log/mysqld.log original

Mysql has the following types of logs:

Error log:-log-err

Query log:-log

Slow log:-log-slow-queries

Update log: the version of-log-update is no longer used, and the operation of update is also recorded in the query log. There will be an error message in the error log after setting, but it does not affect the use of

Binary log:-log-bin

Appendix:

/ / display all binary logs on this computer

Mysql > SHOW MASTER LOGS

/ / Delete all binary logs on this computer

Mysql > RESET MASTER

/ / Delete all binary logs whose creation time is before binary-log.xxx

Mysql > PURGE MASTER LOGS TO 'binary-log.xxx'

/ / only keep the log for the last 6 days, and delete all the previous ones.

Find / var/intra-type f-mtime + 6-name "* .log"-exec rm-f {}\

/ / surrounded by the key in the upper left corner of the keyboard (that is, under Esc), it is a command. -1D was yesterday, and so on.-1m was last month and so on.

Day= `/ bin/date-v-1D +% Y% m% d`

/ / rename the file

Mv xxx.log xxx-$ {day} .log

/ / add the user name and password of the database to update the log (including binary log and query log, etc.)

Mysqladmin flush-logs

Turn on the slow query logging of MySQL

The MySQL slow log is very useful for tracking problematic queries. You can analyze the resource-consuming sql statements in the current program, so how to open the mysql slow log record?

In fact, it is easy to open the slow log of mysql. You only need to add the following code under [mysqld] in the configuration file of mysql (windows system is my.ini,linux system and my.cnf system):

Log-slow-queries=/var/lib/mysql/slowquery.log

Long_query_time=2

Note:

The log-slow-queries setting writes the log there, and when it is empty, the system will give the slow log a hostname and append slow.log.

/ var/lib/mysql/slowquery.log is the location of the files stored in the log. Generally, this directory must have the writeable permission of the running account of mysql. Generally, this directory is set to the data storage directory of mysql.

The 2 in long_query_time=2 means that the query takes more than two seconds to record.

If the parameter log-long-format is set, all queries that do not use the index will also be logged. Add the following line to the file my.cnf or my.ini to record these queries

This is a useful journal. It has little impact on performance (assuming that all queries are fast) and emphasizes the queries that need the most attention (missing indexes or indexes not being best applied)

# Time: 070927 8:08:52

# User@Host: root [root] @ [192.168.0.20]

# Query_time: 372 Lock_time: 136 Rows_sent: 152 Rows_examined: 263630

Select id, name from manager where id in (66 people 10135)

This is one of the slow query logs. It took 372seconds, locked 136seconds, returned 152lines, checked a total of 263630 lines.

If there is a lot of content in the log, it will be exhausting to read it one by one with your eyes. Mysql has its own analysis tools, which are used as follows:

From the command line, go to the mysql/bin directory and type mysqldumpslow-help or-- help to see the parameters of the tool.

The logging function of MySQL's log-bin

After installing mysql and running it for a period of time, a bunch of similar mysql-bin.000***, appears in the mysql directory, which has been arranged since mysql-bin.000001, and takes up a lot of hard disk space, up to dozens of gigabytes. What should I do with these files that take up a lot of space?

So what is the mysql-bin.00001 file in the mysql database folder?

Mysql-bin.000001, mysql- bin.000002 and other files are the operation logs of the database, such as UPDATE a table, or DELETE some data. Even if there is no matching data in the statement, the command will be stored in the log file, including the execution time of each statement, will also be recorded.

What are these documents in the shape of mysql-bin.00001 mainly used for?

1: data recovery

If there is something wrong with your database and you have a backup before, you can look at the log file to find out which command caused the problem with your database and find a way to recover the loss.

2: synchronize data between master and slave servers

All operations on the master server are in the log, and the slave server can do so according to the log to ensure that the two are synchronized.

What should I do if I don't want these files?

1: there is only one mysql server, so you can simply comment out this option.

Vim / etc/my.cnf comment out the line log-bin inside and restart the mysql service.

2: if your environment is a master-slave server, you need to do the following.

A: on each secondary server, use SHOW SLAVE STATUS to check which log it is reading.

B: use SHOW MASTER LOGS to get a series of logs on the primary server.

C: determine the earliest log of all secondary servers, this is the target log, and if all secondary servers are updated, it is the last log on the list.

D: clean up all logs, but not the target log, because you have to synchronize with it from the server.

In a nutshell, the files in the form of mysql-bin.000*** in these MySQL directories are MySQL's transaction logs.

It is safe to delete the binlog that the replication server has taken away. Generally speaking, when the network is in good condition, it is enough to keep the latest one.

Mysql binary log maintenance

Log in first to see if this machine has done replication. If so, be careful when clearing bin-log. Make sure that the bin-log you want to delete has been sent to slave.

Mysql > show processlist

+-+ +

| | Id | User | Host | db | Command | Time | State | Info |

+-+ +

| | 55467761 | root | localhost | NULL | Query | 0 | NULL | show processlist | |

+-+ +

If there is no binlog dump thread, it means that the machine is not doing replication. You can delete all binary logs listed in the index file, reset the binary log index file to empty, and create a new binary log file by clearing all bin-log directly with the following command.

Mysql > reset master

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | xxx.000001 | 180 | |

+-+

Suppose I only want to delete the previous bin-log from xxx.000018, I can use the following command.

Mysql > purge master logs to 'xxx.000018'

Conditional machines had better keep a copy of bin-log on another disk. Even if the database is down, we can recover it according to the binary log. How to keep the binary log? Edit your my.cnf and specify the following configuration log-bin=/diskb/bin-logs/xxx_db-bin. When you restart the database, you will find that there are two more files in / diskb/bin-logs: xxx_db-bin.000001, xxx_db-bin.index, one is the binary log file, which records all queries that change the data into the file, and the other is the index file of the binary log file name.

Here's how to recover data from binaries. If you accidentally execute drop table xxx_db, if you keep a complete binary log, don't sweat, it's recoverable.

Look at the log first.

Mysql > mysqlbinlog / diskb/bin-logs/xxx_db-bin.000001

Find the position after executing create table xxx_db and before drop table xxx_db, if it is 20, 1000.

Mysql > mysqlbinlog-start-position= "4"-stop-position= "1000" / diskb/bin-logs/xxx_db-bin.000001 | mysql-u root

With a lot of ERROR 1062 (23000) at line 12355: Duplicate entry '139' for key 1, the database is restored, but-- start-position= "20" is not allowed. We must start with-- start-position= "4". Why should we force it to start with 4? I don't understand this question for the time being.

Another way is to restore based on the date.

Mysql > mysqlbinlog-- start-datetime= "2009-09-14 0:20:00"-- stop-datetim= "2009-09-15 01:25:00" / diskb/bin-logs/xxx_db-bin.000001 | mysql-u root

If the time gap between create table xxx_db and drop table xxx_db is one year, or in different binary logs, and the location is far apart, wait for insomnia! Make a good backup, careful operation is the right way.

So much for sharing about how to delete and restore binary logs in MySQL. I hope the above content can be helpful to you and you can learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.