Mysqldump backup combined with Binlog log recovery

Mysqldump backups generally take the form of full-Library backups plus log backups, such as performing a full backup every day, and a binary log backup every hour. after a MySQL failure, you can use full backup and log backups to recover data to any location or time before the last binary log backup.

Binlog Introduction:

MySQL binary log records all the database additions and deletions of the operation log, on this premise must open Binlog, whichincludes the execution time of these operations. Display binary content can be viewed using the Mysqlbinlog command.

Binlog Role:1, master-slave replication 2, Recovery database

              open Binary Log function

              edit my.cnf Log-bin dir parameter specifies the storage path of the binary file, filename parameter specifies the file name of the binary file, in the form filename.number number 000001 000002 service or run mysql > flush logs; number filename.index

Mysql> Show variables like ' Log_bin ': See If Bing-log logs are turned on.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/8E/CF/wKiom1jLgVXi2i2DAAAcNT-H31M428.png-wh_500x0-wm_ 3-wmp_4-s_1102926246.png "title=" 1.PNG "alt=" Wkiom1jlgvxi2i2daaacnt-h31m428.png-wh_50 "/>

Bin-log because it is a binary file, can not be viewed directly by the file content View command, MySQL provides two ways to view the way.

Show binary logs; Viewing binary logs on MySQL server

View binary information in command format: Show Binlog events [in ' Log_name '] [ffrom pos] [limit [offset,] row_count]

The default displays the time in the first binary log file that can be found, including the log file name, the starting position of the event, the type of time, the end position, the information, and so on.

Show binary logs 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 files before specified time

Peset Master and Reset Slave

The former empties all binary logs listed in the index file, resets the index file to null, and creates a new binary log file, typically used when master first starts. The latter causes slave to forget its copy location in the master binary log file, it deletes the master.info,relay-log.info and all the trunk log files and starts a new trunk log file so that a clean copy is started. You need to close the slave assignment thread before using reset slave.

Want to see the file in the vomit content and should be restored to the scene with the help of Mysqlbinlog this tool format: mysqlbinlog [options] log_file ... The content of the output will vary slightly depending on the format of the log file and the options used by the Mysqlbinlog tool.

The format of the binary log file contains the line pattern, the statement pattern, the mixed mode, the event information in the statement-based log contains the executed statements, and so on, in the row-based log events Thank you for the change information that contains the rows.

Mysqlbinlog and can read the binary log file from the remote server through the--read-from-remote-server option, need some connection parameters, such as-h,-p,-p,-u, etc. These lateral ventricles are nearly effective after the designation of--read-from-remote-server.

Whether it is a local binary log file or a binary log file on a remote server, whether it is a row mode, statement mode, or mixed-mode binary log file, the Mysqlbinlog tool can be applied directly to MySQL server for Point-in-time, location, or database-based recovery.

Mysqldump is a tool that MySQL uses to back up good data transfer, mainly produces a series of SQL statements that can be encapsulated into a file that 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 a lightweight fast migration or recovery database.

Mysqldump is the data table into the SQL script file, the upgrade between different versions of MySQL is relatively appropriate, but also the most common backup method. Mysqldump generally can be used for backup when the amount of data is very small, and when the amount of data is large, it is not recommended to use the Mysqldump tool for backup.

Export of database, export object Description: Mysqldump can be exported for a single table, multiple tables, a single database, multiple databases, and all databases.

mysqldump [Options] db_name [tbl_name ...] export a specified database or a single table

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 re-open a new binlog when full backup is completed.

Import of databases

Mysql-u User name-p test//opt/test.sql

Data recovery for full-Library backup +binlog

Environment readiness and backup restore:

Thread Open Binlog

This article is from the "11853028" blog, please be sure to keep this source http://tanhong.blog.51cto.com/11853028/1907702

Mysqldump backup combined with Binlog log recovery