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.
This article shares the MYSQL log and backup with everyone. Restore, for your reference, the specific content is as follows
1. Error log
When there is any failure in the database that makes it unusable, check the log as soon as possible
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 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 logNaming method: The default method is hostname-bin + numbermysql 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.iniinnodb_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
#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: mysqlbinlog3. 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 Back up to the specified file in the specified directory. 1. Back up the specified database or some tables in the databasemysqldump [option] db_name [table_names]2. Back up one or more specified databasesmysqldump [option] --database db_name1 db_name23. Back up all databasesmysqldump [option] -all -databasesChange 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.The above is the detailed content of Detailed explanation of the graphic code of logs and backup and restore in MySQL. For more information, please follow other related articles on the PHP Chinese website!