MySQL Backup recovery

I. Purpose of the backup

Disaster recovery: Recovering and restoring corrupted data
Change in demand: the need to restore data to change before it changes
Test: Test whether new features are available

Ii. issues to be considered in backup

Can tolerate the loss of data for how long;
How long will the recovered data be completed;
Whether it is necessary to continue to provide services at the time of recovery;
The restored object is an entire library, multiple tables, or a single library, a single table.

Iii. Types of Backups

1, according to whether the database is offline

Cold (cold Backup): Need to shut down the MySQL service, read and write requests are not allowed in the state;
Win Bei (warm backup): Service online, but only support read requests, do not allow write requests;
Hot backup: The business is not affected at the same time as the backup.

Note:

1. This type of backup depends on the needs of the business, not the Backup tool
2, MyISAM does not support hot-standby, InnoDB support hot-standby, but requires specialized tools

2. Depending on the range of data collection to be backed up
Full backups: Full backup, back up all character sets.
Incremental Backup: Incremental backup data that has changed since the last full or incremental backup, cannot be used alone, and with full backups, the frequency of backups depends on how often the data is updated.
Differential backup: Differential backup data that has changed since the last full backup.
Recommended recovery strategy:
Full + increment + binary log
Full + diff + binary log

3, according to backup data or files

Physical backup: Backing up data files directly

Advantages:

Backup and restore operations are relatively simple and can span MySQL versions,
Fast recovery, at the file system level

Suggestions:

Do not assume that the backup must be available, to test
Mysql>check tables; Test table is available
Logical backup: Backing up data and code in a table

Advantages:

Restore Simple,
The result of the backup is an ASCII file that can be edited
Independent of storage Engine
Can be backed up and restored over the network

Disadvantages:

Backup or restore requires MySQL server process participation
The backup result takes up more space,
Floating-point numbers may lose precision
After the restore, the epitome needs to be rebuilt

Four: backed-up objects

1, data;
2, configuration files;
3. Code: Stored procedure, storage function, trigger
4. OS-related configuration files
5. Replication-related configuration
6. Binary Log

V. Implementation of BACKUP and recovery

1, using SELECT INTO outfile to achieve data backup and restore
1.1 Back up the data that needs to be backed up

mysql> use Hellodb; Open the Hellodb Library
Mysql> SELECT * from students; To view the properties of students
Mysql> SELECT * from students where age > outfile '/tmp/stud.txt '; Back up the information of students older than 30

Text file. Therefore, you cannot import the database directly. Need to recover using load data infile

Back to MySQL server, delete users older than 30, the simulation data is destroyed

Mysql> Delete from students where age > 30;

mysql> load Data infile '/tmp/stud.txt ' into table students;

2. Backup and restore data using the Mysqldump tool

Mysqldump are often used to do Win Bei, so we first need to put a read lock on the data we want to back up,

2.1 How to apply a read lock:

1. Add options directly to the backup

--lock-all-tables A read lock is applied to all tables of the database to be backed up

--lock-table only a read lock on a single table, even if the entire database is backed up, it also applies a read lock on the table when we back up a table, so it works for backup sheets

2, write commands on the server side,

Mysql> flush tables with read lock; Applying a lock means that all the tables that are in memory are synchronized to disk, and then the read lock is applied

Mysql> flush tables With read lock, release read lock

However, for the InnoDB storage engine, although you can also request a read lock, it does not mean that all of its data has been synchronized to disk, so when facing InnoDB, we will use mysql> show engine InnoDB status; Look at InnoDB all the data has been synced to disk before the backup operation.

2.2 Strategies for Backup:

Full backup + incremental backup + binary log

Demonstrate the process of backup;

2.3 Make a full backup of the database first:

[Email protected] ~]# mysqldump-uroot--single-transaction--master-data=2--databases hellodb >/backup/hellodb_ ' Date +%f '. sql

--single-transaction: Based on this option, the hot standby InnoDB table can be implemented; Therefore,--lock-all-tables is not required at the same time;
--master-data=2 record the location of the binary log at the time of the backup, and comment out that 1 is not annotated
--databases Hellodb Specify the database to be backed up

Then go back to the MySQL server side,

2.4 Back to MySQL server-side update data

Mysql> CREATE TABLE tb1 (id int); Create a table
mysql> INSERT INTO TB1 values (1), (2), (3); Insert data, just demo, insert a few data

2.5 Check the location of the records inside the full backup file first:

[email protected] backup]# Cat Hellodb_2013-09-08.sql | Less
--Change MASTER to master_log_file= ' mysql-bin.000013 ', master_log_pos=15684; Record the location of the binary log

2.6 On the back server side:

Mysql> Show master status; Displays the location of the binary log at this time
From the location recorded in the backup file to where we are at this point, which is the incremental part
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000004 |              15982 |                  | |
+------------------+----------+--------------+------------------+

2.7 Doing an incremental backup

[Email protected] backup]# Mysqlbinlog--start-position=15694--stop-position=15982
/mydata/data/mysql-bin.000013 >/backup/hellodb_ ' date + $F _%h '. sql

2.8 Back to Server

mysql> INSERT INTO TB1 values (4), (5); When you insert some values
mysql> drop Database Hellodb; Delete Hellodb Library

2.9 Export this binary log:

[[email protected] backup]# mysqlbinlog--start-position=15982/mydata/data/mysql-bin.000013 View the location of the binary log when the delete operation
[Email protected] backup]# Mysqlbinlog--start-position=15982--stop-position=16176/mydata/data/mysql-bin.000013 >/tmp/hellodb.sql//Export binary log

2.10 Let MySQL go offline

Back to server side:

Mysql> set sql_log_bin=0; Turn off binary logging
mysql> flush logs; Scroll down the log

2.11 Impersonation Database corruption

mysql> drop Database Hellodb;

2.12 Start Recovery data:

[[email protected]]# MySQL </backup/hellodb_2013-09-08.sql//import Full backup file
[[email protected]]# MySQL </backup/hellodb_2013-09-08_05.sql//import Incremental backup file
[[email protected]]# mysql< hellodb.sql//Importing binary files

This article is from the "Linux" blog, so be sure to keep this source http://syklinux.blog.51cto.com/9631548/1759081

MySQL Backup recovery