Database backup tool mysqldump specific usage introduction

MySQL is a logical backup tool: Based on the MySQL client protocol, can be remotely implemented, remote backup, to consider disk I/O, it is recommended that the same library does not use multiple engines one of the reasons is that the Backup tool support is not the same

Full backup, partial backup;

InnoDB: Hot standby or Win Bei;

MyISAM: Win Bei;

Two-time Package tool:

Mydumper:perl scripts to simulate the effects of parallel backups, consuming the server's IO

PhpMyAdmin

The backup mechanism is to create the database before the backup, then create the table, and finally insert all the data by inserting into

Mysqldump backup mechanism: first create a library, then create related tables, insert data into the table

Library: Create Database

Table: CREATE TABLE

Data: INSERT INTO, using an INSERT INTO statement, you can insert the table data to complete the recovery. CREATE table for each table, then insert into to restore

Execution Myslqdump will put all the content is now on the screen, and then in step-by-step operation, show the results inside can see the specific action, if you want to restore, you need to redirect these display results to a file, with the newly generated files for recovery

Mysqldump usage, there are three, as follows:

Method One:

mysqldump [OPTIONS] Database [tables] # Backup single library, you can only back up some of these tables (partial backup);

Method Two:

mysqldump [Options]--databases [options] DB1 [DB2 DB3 ...] # Backup multiple libraries, it is recommended this way, even if you back up a single library, because you do not have to create a library

Example:

[[Email protected] ~] #mysqldump-u root-ppass123456-h 192.168.1.71--databases Sunny

After executing the statement, the contents of the sunny database are printed to the screen, not redirected to the relevant file, not for recovery, but you can view the actions performed using the Mysqldump command procedure.

Method Three:

mysqldump [Options]--all-databases [options] # back up all libraries; Create libraries Yourself

Note that when you perform a backup, you add an option to lock the corresponding table to prevent inconsistencies in the point-in-time and not to restore the data

MyISAM Storage Engine: Support Win Bei, lock table when backing up;

-X,--lock-all-tables: Locks All tables of all libraries, reads locks, and locks wide.

-L,--lock-tables: Locks All tables in the specified library, and only locks the table when it is backed up

InnoDB Storage Engine: Supports Win Bei and hot spares;

You can use-X or-L to achieve temperature

--single-transaction: Creates a transaction, performs a backup based on this snapshot, and implements a hot standby; but the problem is that if something is not being commit or rolled back at the time of the backup, there is a problem with recovering the data, so the recovery will have to perform a crash recovery. Therefore, it is also possible to complete the data by using the recovery operation after the crash.

Other options:

-R,--routines: Backs up stored procedures and storage functions for the specified library;

--triggers: Backup triggers for the specified library;

-E,--events :

Example

Data backup and restore with MySQL

Hot spare, add related options, and redirect to file/root/sunny.sql

[[email protected] sunny] #mysqldump-u root-ppass123456-h 192.168.1.71--single-transaction-r--triggers-e--databases Sunny >/root/sunny.sql

Example: Using redirection to restore the generated files to a local MySQL library, if there is already a local database, you will not create a database with the same name, creating a new table directly and importing the data into the newly created tables.

Note that the binary log of the restored local server is closed before recovery, otherwise the newly imported data is logged to the binary log, but this part of the log is a recovered file and does not need to be recorded again

Turn off the binary log function as follows

MariaDB [sunny]> SET @ @session. Sql_log_bin=off;

Recovering a Database

[[Email protected] ~] #mysql-uroot-ppass1234 </root/sunny.sql

When restoring a backup, there is a small portion that is not backed up to the backup file, so it needs to be replayed with a binary file, so when doing a full backup, combine the options--master-data and--flush-logs

--master-data[=#]

1: Record as change MASTER to statement, this statement is not commented;

2: Record as change MASTER to statement, this statement is commented; typically set to 2

--flush-logs: When the locked table is complete, the log flush operation is done for the Binary tab; Drive Server log scrolling

Example:

[[Email protected] ~] #mysqldump-u root-ppass1234-h 192.168.1.71--single-transaction-r--triggers-e--databases Sunny --master-data=2--flush-logs >/root/sunny-$ (Date +%f-%h-%m-%s). sql

[[Email protected] ~] #less sunny-2018-01-15-13-10-17.sql

Review the newly generated backup file, as follows the key comment information below,

Recovery can be from the master-log.000005 this file from the No. 245 byte to recover, because the last backup with the change MASTER to=2, the last time there is a scroll, restore the time only after 245 of the content to replay it can be

--Change MASTER to master_log_file= ' master-log.000005 ', master_log_pos=245;

Assuming that the sunny database on server 71 is operating at this time, the newly generated log will be logged to the binary log master-log.000005, assuming that 71 operates as follows

MariaDB [sunny]> Delete from students where id=1000;

MariaDB [sunny]> Delete from students where id=1002;

MariaDB [sunny]> INSERT INTO students (Id,name,age,gender) VALUES (1050, "new005", "M");

At this point, the master-log.000005 in 71 will log these newly generated logs to see the following

[[email protected] MySQL] #mysqlbinlog/mydata/log/master-log.000005

Restore to 75 on the MySQL server that just imported the full amount of backup

The recovery operation is as follows

First, redirect the file to master-log.000005 and then copy it to 75 for replay.

Redirect, Mysqlbinlog This tool will be converted to SQL statement after reading the data, note that here-j 245 can not be specified, because there is no valid content before 245 bytes, if it starts after 245, note to add-j option

[[email protected] MySQL] #mysqlbinlog-j 245/mydata/log/master-log.000005 >/tmp/binlog005.sql

Copy data

[[email protected] MySQL] #scp/tmp/binlog005.sql 192.168.1.75:/root/

Re-import the Binlog005.sql database on 75

[[Email protected] ~] #mysql-uroot-ppass123456 < Binlog005.sql

Note that in the future with Mysqldump backup, it is recommended to copy a binary file, for the exception backup, in the future, use the Mysqlbinlog command to read the file generated MySQL statement, and then the recovery of binary files. However mysqldump recovery efficiency is low, it is recommended to use xtrabackup this tool to achieve

Database backup tool mysqldump specific usage introduction