Detailed explanation of MYSQL log and backup and restore issues

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

1. Error log

When the database fails and cannot be used, check the log first.

1. Information during server startup and shutdown

2. Error information during server operation

The log storage path can be viewed through the command:

Log file naming format: host_name.err

2. Binary Log

Also known as BINLOG, it records all DDL statements and DML statements, excluding query statements. This log is not only very important, but as a developer I also really like this log. As can be seen from its definition, this log records all events that will change the table structure and table data, so once the data is deleted by mistake or lost due to other reasons, we can restore 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

Every time MySQL starts or flushes the log, it generates a new binlog, and the numbering starts from 1 and increases. When a single log reaches a certain size, a new file is generated.

1. Turn on the switch for recording binlog

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

log-bin indicates that the switch is turned 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 an error log. You need to use the tool provided by MySQL: 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 whole day's log, you need to define it as:
--start-datetime="2017/07/12 00:00:00" --stop-datetime="2017/07/13 00:00:00": The query time range is 7/12 00:00:00 - 7/12 24:59:59

3. Data backup

Data backup actually uses the tool mysqldump provided by msyql to back up data to a specified file in a 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

Export the table structure and table data in the database wd_msg in the database instance with port 3306 to the cd.sql file

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

This file records DML statements and DDL statements, excluding query-related operations. When recovering data, 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 we just exported:

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

MySQL backup and restore has different options for different scenarios. This is just one of the concepts introduced here. There will be articles that will introduce it in detail later.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.