MySQL log vs. backup restore

First, error log

When a database fails to work, the first time to view the log

1, the server starts the shutdown process information

2. Error messages during server operation

Log storage path, which can be viewed by command:

Log file naming format: Host_name.err

Second, binary log

Also known as Binlog, records all DDL statements and DML statements, not including query statements. This log is not only very important, but also very much liked by developers as a developer. As you can see from its definition, this log records all events that alter the table structure and table data, so once the data has been mistakenly deleted or because of other causes the data is lost, we can recover the data through this log. Isn't that cool.

Log Storage Path : In the same directory as the error log

named by: Hostname-bin + numbering by default

MySQL generates a new binlog every time the log is started or flush, and the number increments from 1. When a single log reaches a certain size, a new file is also generated.

1, open the record binlog switch

In the MYSLQ installation directory, there is a configuration file: My.ini

innodb_buffer_pool_size=25%-100% 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 and hardware as well as the os# Scheduler properties. A too high valuemay leads to thread thrashing.innodb_thread_concurrency =10log-bin=mysql-bin

Where Log-bin indicates that the switch is on, 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 as an error log, and requires the help of the tools provided by MySQL: Mysqlbinlog

3. View Binlog by Time

Query by time there is a need to note that the start-datetime is a closed interval, stop-datetime is open interval, so if you need to query all-day log session, it needs to be defined as:
--start-datetime= "2017/07/12 00:00:00"--stop-datetime= "2017/07/13 00:00:00": The time range for this query is 7/12 00:00:00-7/12 24:59:59

Third, data backup

The data backup is actually using the tools provided by MSYQL Mysqldump to back up the data 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 databases specified

mysqldump "option"--database db_name1 db_name2

3. Back up all databases

mysqldump "option"-all-databases

Export the table structure and table data from database wd_msg in a database instance with Port 3306 to a Cd.sql file

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

This file records DML statements and DDL statements, with the exception of query-related operations. The data can be restored with the execution of these statements.

Iv. Data Recovery

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

The following scenario is to restore the WD_MSG database in a MySQL instance with another port of 3307 on the same server

MySQL backup restore for different scenarios have different choices, here is just one of the concepts introduced, there will be an article to expand the detailed introduction.

MySQL log vs. backup restore