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

Editor to share with you the MYSQL log and backup restore example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Error log

When there is any failure in the database so that it cannot be used, check the log first.

1. Information during server startup and shutdown

2. Error messages during the operation of the server

The log storage path can be viewed through the command:

Log file naming format: host_name.err

Binary log

Also known as BINLOG, records all DDL statements and DML statements, excluding query statements. This log is not only very important, but also loved it as a developer. 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 mistakenly deleted 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 is hostname-bin + numbering

Each time mysql starts or the flush log generates a new binlog, the number is incremented from 1. When a single log reaches a certain size, a new file is also generated.

1. Turn on the switch to record binlog

Under the myslq installation directory, 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=10log-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, you can't view it directly like the error log. You need to use the tool provided by mysql: mysqlbinlog

3. View BINLOG by time

One thing to note about querying by time is that start-datetime is a closed interval and stop-datetime is an open interval, so if you need to query all-day log, you need to define it as:

-- start-datetime= "00:00:00 on 2017-07-12"-- stop-datetime= "00:00:00 on 2017-07-13": the time range for such a query is 7 take 12 00:00:00-7 take 12 24:59:59

III. Data backup

Data backup is actually using mysqldump, a tool provided by msyql, to back up data to a specified file under 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 from the database wd_msg in the database instance with port 3306 to a file in cd.sql

The contents of the cd.sql file are as follows

This file records DML statements and DDL statements, except for query-related operations. When the data is recovered, it can be executed one by one through these statements, thus completing the restore of the data.

IV. 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

The above is all the contents of the article "sample Analysis of MYSQL Log and backup restore". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.