DBA-known MYSQL backup and restore method

One mysqldump backup combined with Binlog log recovery

Description: MySQL backups generally take the form of full-database backups plus log backups, such as performing a full backup every day, and performing a binary log backup every hour. This allows you to recover data to any location or time prior to the last binary log backup using full backup and log backups after a MySQL failure.

The Binlog feature is turned off by default and is not turned on.

View Binlog, with Mysqlbinlog-v mysql-bin.000001

Master-Slave synchronization

Recovering a Database

Turn on binary log function: By editing the Log-bin option in the MY.CNF, you can turn on binary logging, form right: Log-bin[=dir/[filename], note: Each time you restart the MySQL service or run mysql> flush log s; a new binary log file is generated, and the number of these log files is incremented continuously, and a file named Filename.index is generated in addition to the above files. The list of all binary log files stored in this file is also known as the index of the binary file.

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

To view the events in the specified binary log:

The command also contains additional options for flexible viewing:

Summary: The above method can be viewed to the server on the existing binary log files and events in the file, but want to see the specific contents of the file and should be restored to the scene with the help of mysqlbinlog this tool.

Syntax format: mysqlbinlog [options] log_file ...

The output is slightly different depending on the format of the log file and the options used by the Mysqlbinlog tool.

The available options for Mysqlbinlog refer to the man manual.

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

Let's show you how to delete data before using Binlog recovery (id=2 that record)

Note: In the actual production environment, if you encounter the need to recover the database, do not let the user access to the database to avoid new data insertion, as well as in the master-slave environment, shut down the master and slave.

Delete from Bdqn.test where id=2

# cd/usr/local/mysql/data/

# mysqlbinlog-v mysql-bin.000002

The results are as follows: (copied logs)

# at 219

#170316 21:52:28 Server ID 1 end_log_pos 287 CRC32 0xff83a85b Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1489672348/*!*/;

SET @ @session. pseudo_thread_id=2/*!*/;

SET @ @session. foreign_key_checks=1,@ @session. sql_auto_is_null=0, @ @session. unique_checks=1,@ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=1075838976/*!*/;

SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\c UTF8 *//*!*/;

[Email protected] @session. character_set_client=33,@ @session. collation_connection=33,

@ @session. collation_server=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

BEGIN

/*!*/;

# at 287

#170316 21:52:28 Server ID 1 end_log_pos 337 CRC32 0x343e7343 table_map: ' bdqn '. ' Test ' mapped to number 108

# at 337

#170316 21:52:28 Server ID 1 end_log_pos 382 CRC32 0xa3d1ce0d delete_rows:table ID 108 flags:stmt_end_f

BINLOG '

Njjkwbmbaaaamgaaafebaaaaagwaaaaaaaeabgjkcw4abhrlc3qaagmpajwaaknzpjq

=njjkwcabaaaalqaaah4baaaaagwaaaaaaaeaagac//wcaaaabgxpc2knztgj

‘/*!*/;

# # # DELETE from ' bdqn '. ' Test '

# # WHERE

# # @1=2

# # # @2= ' Lisi '

# at 382

#170316 21:52:28 Server ID 1 end_log_pos 413 CRC32 0x257e7073 Xid = 10

commit/*!*/;

Note: You can see that the delete time occurs position is 287, the event ends position is 413.

② recovery process: Directly use the Bin-log log to restore the database to the deletion location 287, and then skip the point of failure, and then restore all the following operations, the command is as follows

Since no full-library backup has been done before, all Binlog log recoveries are used, so it takes a long time to recover from the production environment and export the relevant binlog files.

③ Delete BDQN database (to turn off Binlog function before deleting bdqn and recovering data)

Role: Mysqldump is a tool for backup and data transfer that comes with MySQL.

It only produces SQL statements (that is, SQL commands) encapsulated in files rather than real data.

Mysqldump is a logical backup, not a physical backup, which backs up SQL statements, not data files.

Mysqldump is suitable for small databases, the data capacity is generally in a few G size, when the amount of data is very large, it is not recommended to use Mysqldump.

Export objects: You can target a single table, multiple tables, a single database, multiple databases, and all databases.

#mysqldump [Options] Library name [table name 1] [table Name 2] ... >/backup path/backup file name

To export a single or multiple tables for a specified database

#mysqldump [options]--databases library name 1 [library Name 2] ... >/backup path/backup file name

Export a specified database or multiple databases

#mysqldump [Options]--all-databases >/backup path/backup file name

Export all the databases

#mysqldump-uroot-p123456--flush-logs bdqn >/opt/bdqn.sql

Export the database bdqn, where "-flush-logs" is the option to open a new binlog after the full backup is complete

#mysql-uroot-p123456 BDQN </opt/bdqn.sql

Import a database from a backup file bdqn

The following is a concrete experiment to illustrate the use of mysqldump to achieve full-database backup +binlog data recovery

4) Start a full library backup (note: A full-Library backup does not back up the Binlog log file)

5) Back up all the Binlog log files before the mysqldump full library backup (note: There may be more than one Binlog file in the production environment)

6) because the Binlog before the full-library backup has been backed up, delete them now (i.e. all Binlog deleted before the newly generated binlog)

① full-Library recovery with mysqldump backup (i.e. all data restored to full backup)

② analyze the starting and ending positions of the newly opened Binlog log files (which I'm mysql-bin.000002 here), as long as you skip the event

Copied logs:

# at 219

#170318 21:14:42 Server ID 1 end_log_pos 291 CRC32 0xddbf8eff Query thread_id=5 exec_time=0 error_code=0

SET timestamp=1489842882/*!*/;

SET @ @session. pseudo_thread_id=5/*!*/;

SET @ @session. foreign_key_checks=1,@ @session. sql_auto_is_null=0, @ @session. unique_checks=1,@ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=1075838976/*!*/;

SET @ @session. auto_increment_increment=1,@ @session. auto_increment_offset=1/*!*/;

/*!\c UTF8 *//*!*/;

SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session.

collation_server=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

BEGIN

/*!*/;

# at 291

#170318 21:14:42 Server ID 1 end_log_pos 339 CRC32 0x4a9ec8f2 table_map: ' bdqn '. ' It ' mapped to number 108

# at 339

#170318 21:14:42 Server ID 1 end_log_pos 388 CRC32 0x2e8a3da8 delete_rows:table ID 108 flags:stmt_end_f

BINLOG '

wjlnwbmbaaaamaaaafmbaaaaagwaaaaaaaeabgjkcw4aaml0aaiddwi8aalyyj5k

Wjlnwcabaaaamqaaaiqbaaaaagwaaaaaaaeaagac//wbaaaachpoyw5nc2fuqd2klg

= = '/*!*/;

# # # DELETE from ' bdqn '. ' It '

# # WHERE

# # @1=1

# # # @2= ' Zhangsan '

# at 388

#170318 21:14:42 Server ID 1 end_log_pos 419 CRC32 0xa1c06a4f Xid = 43

commit/*!*/;

③ start recovery of incremental data after a full-library recovery using an binlog log file backup file with an incremental backup after a full-library backup

Summary: From the display can see the data recovery to a normal state, the actual production environment of the MySQL database backup is a recurring recurring operation, all is usually to write a script implementation, through the Crond scheduled task periodically execute backup script.

Sunday 1 o'clock in the morning full database backup;

From Monday to Saturday, incremental backups every 4 hours

Set up the crontab task to execute the backup script daily:

2) write mysqlfullbackup.sh script (i.e. MySQL full library backup script)

3) Write mysqldailybackup.sh script (i.e. MySQL incremental backup script)

To send a message test:

Installing libmysqlclient.so.18

Test again:

II using XTRABACKUP for MySQL database backup

The previous introduction of Mysqldump backup method is the use of logical backup, the biggest drawback is that the backup and recovery is slow, for a database less than 50G, this speed is acceptable, but if the database is very large, then use mysqldump backup is not suitable.

At this time need a useful and efficient tool, xtrabackup is one of them, known as the free version of the InnoDB hotbackup.

At present, there are two main tools to achieve physical heat preparation: Ibbackup and xtrabackup;

Ibbackup is commercial software that requires authorization and is very expensive.

Xtrabackup function is more powerful than ibbackup, but it is open source. So we're here to introduce the use of xtrabackup.

Xtrabackup: Data dedicated to backing up the InnoDB and XtraDB engines;

Innobackupex: This is a Perl script that invokes the Xtrabackup command during execution so that the backup InnoDB can be implemented with the command, or the object of the MyISAM engine can be backed up.

(1) The backup process is fast and reliable;

(2) The backup process does not interrupt the executing transaction;

(3) Can save disk space and traffic based on functions such as compression;

(4) Automatic implementation of backup inspection;

(5) Fast restore speed.

Official link Address:

Http://www.percona.com/software/percona-xtrabackup;

You can download the source code to compile the installation, or you can download the appropriate RPM package or use Yum to install or download the binary source package.

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/ Percona-xtrabackup-2.4.4-linux-x86_64.tar.gz

The xtrabackup consists of two main tools:

Xtrabackup: is a tool for hot backup of data in the INNODB,XTRADB table, Support Online hot backup, you can back up the INNODB data table without locking, but this tool does not operate the Myisam engine table;

Innobackupex: is a Perl script that encapsulates Xtrabackup to handle both Innodb and Myisam, but requires a read lock when handling Myisam.

Because a read lock is required to operate the Myisam, this can block write operations on the online service, and Innodb does not have such limitations, so the larger the proportion of Innodb table types in the database, the more advantageous.

#wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

This completes the installation of Xtrabackup and the backup can be started below.

2) Create a backup directory (full: fully stocked directory; inc: directory where incremental backups are stored)

When using Innobackupex for backup, you can also use the--NO-TIMESTAMP option to prevent commands from automatically creating a time-named directory, so that the Innobackupex command creates a Backup-dir directory to store the backup data.

You can also add the-database option to specify the database to be backed up, the database specified here is only valid for the MyISAM table, is fully-prepared for InnoDB data (all InnoDB data in all databases are backed up, not just the specified database, but also when recovering).

Let's look at a few of these files:

Description: Xtrabackup_binlog_pos_innodb and xtrabackup_binary were not available in this release, since the version was newer, these two files were removed in the new edition, only existing in the old version.

I use the delete data Catalog file directly here to simulate corruption.

In general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The primary role of Prep is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

At the end of the prepare (prepare) process, the InnoDB table data has been rolled forward to the point where the entire backup ended, rather than rolling back to the point at which the Xtrabackup was initially started.

The--apply-log option of the Innobakupex command can be used to implement the above functions. As in the following command:

--apply-log indicates that the log is applied to the data file and the data in the backup file is restored to the database after completion:

In the process of "preparing," Innobackupex can typically use the--use-memory option to specify the amount of memory it can use, which is typically 100M by default. If there is enough memory available, you can partition some memory into the prepare process to improve its completion speed.

Description: The--copy-back option of the Innobackupex command is used to perform a recovery operation, which performs the recovery process by copying all data-related files to the MySQL server DataDir directory. Innobackupex backup-my.cnf to get information about the DataDir directory.

⑧ officially starts restoring incremental backups

⑨ restarting the binary log and verifying the restored data

Attached: Xtrabackup "stream" and "Backup compress" function

function: Xtrabackup supports the "stream" function for the backed up data file, which means that the backed up data can be transferred to the TAR program through STDOUT instead of being saved directly to a backup directory by default.

To use this feature, you only need to use the--stream option. Such as:

You can see the following commands for copying and pasting:

# Innobackupex--user=root--password= "123456"--stream=tar/opt/mysqlbackup/full/| Gzip >/opt/mysqlbackup/full/full_ ' date+%f_%h%m%s '. tar.gz

(in addition, in a real-world production environment, the flow and backup compression is basically used, as this can save a lot of space)

Links: http://zpf666.blog.51cto.com/11248677/1913451

DBA-known MYSQL backup and restore method