Shulou(Shulou.com)06/01 Report--

This article mainly talks about the combination of binlog log to achieve the content of mysqldump backup recovery, interested in it, then take a look at this article, I believe that after reading the combination of binlog log to achieve mysqldump backup recovery for everyone somewhat reference value.

mysqldump backup generally adopts the method of full database backup plus log backup, such as performing a full backup once a day and a binary log backup once an hour. Full and log backups can be used to restore data after a mysql failure to any location or time prior to the last binary log backup.

Binlog Introduction:

mysql binary log records all the database add, delete and change operation log, in this case must open binlog, which includes the execution time of these operations. Display binary content can be viewed using the mysqlbinlog command.

binlog: 1, master-slave replication 2, recovery database

Open binary log function

Edit the log-bin option in my.cnf to enable binary logging: log-bin[=DIR/[filename]]. The DIR parameter specifies the storage path of the binary file, and the filename parameter specifies the file name of the binary file in the form filename.number, number in the form 000001, 000002, etc. Every time you restart the mysql service or run mysql > flush logs;, a new binary log file is generated, and the binary number of these files is incremented. A file called filename.index is also generated, which stores a list of all binary log files, also known as the binary file index.

mysql> show variables like 'log_bin': Check to see if bing-log logging is enabled.

Bin-log because it is a binary file, can not be directly opened through the file content view command to view, mysql provides two ways to view.

show binary logs; View binary logs on mysql server

Command format for viewing binary information: show binlog events [in 'log_name'][ffrom pos] [limit [offset,] row_count]

By default, it displays the time in the first binary log file that can be found, including the log file name, the start position of the event, the time type, the end position, information, etc.

show binary logs is equivalent to show master logs

purge binary logs is used to delete binary logs.

purge binary logs before '2016-08-28 22:46: 26': Delete binary logs before specified time

Peset master and reset slave

The former clears all binary logs listed in the index file, resets the index file to empty, and creates a new binary log file, typically used when the master is first started. The latter causes the slave to forget its copy location in the master binary log file, deleting master.info, relay-log.info and all relay log files and starting a new relay log file in order to start a clean copy. You need to close the slave assignment thread before using reset slave.

To view the contents of the file and respond to the recovery scenario, you have to use the mysqlbinlog tool format: mysqlbinlog [options] log_file... The output varies slightly depending on the format of the log file and the options used by the mysqlbinlog tool.

Binary log file format contains line pattern, statement pattern, mixed pattern, event information in statement-based log contains executed statements, etc., event information in line-based log contains line change information.

mysqlbinlog and binary log files can be read from remote Cloud Virtual Machine via--read-from-remote-server option, which requires some additional connection parameters, such as-h, -p, -P, -u, etc., which are valid only after--read-from-remote-server is specified.

Whether it is a local binary log file or a binary log file on a remote Cloud Virtual Machine, whether it is a binary log file in line mode, statement mode or mixed mode, after being parsed by mysql binlog tool, it can be directly applied to mysql server for recovery based on time point, location or database.

mysqldump is mysql used to backup a good data transfer tool, mainly produces a series of sql statements, can be encapsulated into a file, the file contains all the sql commands needed to rebuild your database such as create database, create table, insert and so on. Can be used to implement lightweight rapid migration or recovery of databases.

mysqldump is to export data tables into sql script files, which is relatively suitable for upgrading between different mysql versions, and is also the most commonly used backup method. mysqldump can generally be used for backup when the amount of data is small. When the amount of data is relatively large, it is not recommended to use mysqldump tool for backup.

Database export, export object Description: mysqldump can export for a single table, multiple tables, a single database, multiple databases, and all databases.

mysqldump [options] db_name [tbl_name ...] Export specified databases or individual tables

mysqldump [options] --databases db_name... Export multiple databases

mysqldump [optoins] --all-databases Export all

mysqldump -u username-p --flush-logs test >/opt/test. sql--flush-logs This option will reopen a new binlog when the backup is complete.

Database import

The requested URL/opt/test.sql was not found on this server.

Full database backup +binlog data recovery

Environment preparation and backup restore:

Thread open binlog

The above on the combination of binlog log to achieve mysqldump backup recovery details, to help you? If you want to know more about it, you can continue to pay attention to our industry information section.

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.