Detailed explanation of MYSQL logs and backup and restore

This article mainly introduces the MYSQL log and backup and restore issues in detail. It has certain reference value. Interested friends can refer to it. I hope it can help everyone.

This article shares MYSQL logs and backups and restores for your reference. The specific content is as follows

1. Error log

When the database appears When any failure makes it unusable, check the log as soon as possible

1. Information during server startup and shutdown

2. Error information during server operation

Log storage path, you can view it through the command:

Log file naming format: host_name.err

2. Binary log

Also known as BINLOG, records all DDL statements and DML statements, excluding query statements. Not only is this log very important, but as a developer I also love this log. As can be seen from its definition, this log records all events that change the table structure and table data, so once the data is deleted accidentally or is lost due to other reasons, we can recover the data through this log. Don't you think it's cool?

Log storage path: in the same directory as the error log

Naming method: The default method is hostname-bin + number

mysql will be used every time it starts or flushes the log Generate a new binlog, with the number starting from 1 and increasing. When a single log reaches a certain size, new files are also generated.

1. Turn on the binlog recording switch

In the installation directory of myslq, there is a configuration file: my.ini

innodb_buffer_pool_size=107M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=54M

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=10

log-bin=mysql-bin

where log-bin indicates that the switch is on, and mysql-bin is the prefix of the log name.

#2. How to view BINLOG

Because it is a binary file, it cannot be viewed directly like the error log. You need to use mysql Tools provided: mysqlbinlog

3. View BINLOG

by time

One thing to note when querying by time is that start-datetime is a closed interval and stop-datetime is an open interval, so if you need to query the all-day log, you need Defined as:
--start-datetime="2017/07/12 00:00:00" --stop-datetime="2017/07/13 00:00:00": The time range of this query is 7 /12 00:00:00 - 7/12 24:59:59

3. Data backup

Data backup is actually to use the tool mysqldump provided by msyql to transfer data Back up to the specified file in the specified directory.

1. Back up the specified database or some tables in the database

mysqldump [option] db_name [table_names]

2. Back up one or more specified databases

mysqldump [option] --database db_name1 db_name2

3. Back up all databases

mysqldump [option] -all -databases

Change the port to 3306 The table structure and table data in the database wd_msg in the database instance are exported to the cd.sql file

The contents of the cd.sql file are as follows;

This file records DML statements and DDL statements, except query-related operations. During data recovery, these statements can be executed one by one to complete the data restoration.

4. Data recovery

We delete the table and re-import the data just exported:

The following scenario is to restore the wd_msg database in another mysql instance with port 3307 on the same server

The backup and restore of Mysql is for Different scenarios have different choices. Here is just one of the concepts introduced. There will be articles to introduce it in detail later.

Related recommendations:

Sample code sharing that explains the details of the MySQL log system

Introduction to mysql log recovery data method

MySQL log setting optimization

The above is the detailed content of Detailed explanation of MYSQL logs and backup and restore. For more information, please follow other related articles on the PHP Chinese website!