MySQL Backup recovery basics and mysqldump explanation

Database Backup Recovery Knowledge Essentials:

Timeline Backup Type classification:

Full backup : Backing up the entire data set

Incremental backup : Backup of the last full backup, or data that changed after the last incremental backup (Restore trouble, save space)

differential backup : Backs up only data that has changed since the last full backup (simple restore, large space consumption)

What is a physical backup, a logical backup:

Physical Backup: Direct copy of data files for backup (potentially taking up more space, faster backups, harder to make hot spares)

logical backup : To export data from the database "Save as" and the backup (from binary to text format, it is possible to lose precision, requires specialized protocol client to do, and data storage engine independent, backup recovery time is longer, make hot preparation easy)

What should I back up?

① Data

② binary log, InnoDB transaction log

③ code (stored procedures, stored functions, triggers, event schedulers)

④ configuration file for server

Backup tool:

①mysqldump Logical Backup tool

②CP, tar physical Backup tool, cold standby

Snapshot of ③lvm2: Almost hot spare (request to apply global lock), backup with file System Management tool

④mysqlhotcopy: Almost cold, only for MyISAM storage engine

Selection of Backup scenarios:

Scenario One : mysqldump+ replication Binlog (slow time, remote backup supported)

MySQLdump: Full backup

Replication event for a specified time range in Binlog: Incremental backup

Scenario Two : lvm2 snapshot + Copy Binlog

LVM2 Snapshot: Physical backup using CP or tar: Full backup

Replication event for a specified time range in Binlog: Incremental backup

Scenario three : Xtrabackup

Tools provided by Percona for hot standby (physical backup) of InnoDB

Supports full backup, incremental backup

The Mysqldump Backup tool explains:

What is the dump of MySQL?

MySQL or mariadb comes with a client command, logical Backup tool. Is the backup invocation tool for the graphical backup management tool Mydumper, phpMyAdmin. Based on the MySQL client protocol, for all storage engines, Win Bei: Full backup, partial backup. Support for InnoDB backup (longer) is appropriate for smaller databases (GB or less).

Support for common storage engines:

InnoDB: Hot standby or Win Bei;

MyISAM: Win Bei;

Backup mechanism:

connect to MySQL server via MySQL protocol. Initiate a full-scale query to the MySQL server, take all the data locally, and save the read data in a file to complete the backup.

Library: CREATE Database

Table: CREATE table

Data: INSERT into

Usage Method Usage:

①mysqldump [OPTIONS] Database [tables] # Backup single library, you can only back up some of these tables (partial backup, need to create a database manually);

②mysqldump [Options]-b/--databases [options] DB1 [DB2 DB3 ...] # Backup multi-Library (recommended);

③mysqldump [Options]-a/--all-databases [options] # back up all libraries;

Examples of primary backup experiments:

① Backup: mysqldump-uroot-p-B Test > Test.sql

② login MySQL Delete Test library: drop database test;

③ Recovery: Mysql-uroot-p <test.sql

Mysqldump Use Advanced:

In actual production use, taking into account the length of backup, backup accuracy, the impact of the backup process on the online experience, data recovery methods resulting from a full backup, this section describes mysqldump for the main storage engine additional options for transaction logging, as well as demonstration production using mysqldump.

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

- x,--lock-all-tables: Locks All tables of all libraries, reads locks;

-L,--lock-tables: Locks All tables in the specified library;

InnoDB Storage Engine : Supports Win Bei and hot spares;

--single-transaction: Create a transaction to perform a backup based on this snapshot, followed by a post-crash recovery operation

Other options:

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

--triggers: Backup triggers for the specified library;

-E,--events: Backs up all event Scheduler related to the specified database;

--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;

--flush-logs: After the lock table is completed, the log scrolling operation is performed;

Mysqldump Backup Experiment:

Experimental Scenario: Two database hosts a, B. After a global backup of host a data, new data (self-modifying table information) is generated, and the full backup file of host A and the binary log file are sent to Host B to complete the backup.

Experiment Preparation:

① two hosts to install MySQL or mariadb-server

② turn on the binary logging feature (default off)

Methods: Vim/etc/my.cnf.d/server.cnf #安装方式不同, the path will be different, this article with Yum installation explained

Add under [Server] or [mysqld]: log_bin=/path/prefix (MySQL user must have RWX permissions on the log path, this example log_bin=/app/test)

Restart Service

③ Host A data example:

SELECT * from Shudian.mybook;

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/11/93b9b56468a5ee5d94bffdea3fbfab66.png "title=" 1.png " alt= "93b9b56468a5ee5d94bffdea3fbfab66.png"/>

Backup recovery process:

1. Global Backup :

Mysqldump-uroot-p--single-transaction--master-data=2--flush-logs-b Shudian > Shudian.sql--single-transaction: Create a transaction--master-data=2: Record the location of the binary record at the time of the backup, this statement is commented--flush-logs: Log scrolling occurs when the locked table is complete

2. Modify host a data

INSERT into MyBook values (6, ' Xuamingzhang ', 40,512);

Modified data:

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/11/60ac94c8a3599a4526a0706324d0ff6d.png "title=" 2.png " alt= "60ac94c8a3599a4526a0706324d0ff6d.png"/>

3. Send backup information

① Global Backup files

SCP Shudian.sql B Host Ip:/app

② View the global backup file to see which node to back up and log:

grep "Master_log_pos" shudian.sql--change MASTER to master_log_file= ' test.000002 ', master_log_pos=1545;

③ backup node after binary log

Mysqlbinlog--start-position=1545/app/test.000002 >incre.sql

Send the binary log to Host B:

SCP Incre.sql B Host Ip:/app

④ Host B Data Recovery

Set sql_log_bin=0; Temporarily close record binary log MySQL <shudian.sqlmysql <incre.sqlset sql_log_bin=1; open record binary log

Note: Binaries should not be placed on the same disk as the data file and should be placed on disks with redundancy, such as Ride10.

This article is from the Linux OPS blog, so be sure to keep this source http://arm2012.blog.51cto.com/2418467/1980832

MySQL Backup recovery basics and mysqldump explanation