MySQL Backup summary (three ways to read more)

The essence of a backup is to save a copy of the dataset, but the original data is constantly changing, so the backup can only revert to the data before the data changes. What about the changes? So it's important to develop a good backup strategy.

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

Copy CodeThe code is as follows:
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

Attention:

The directory path of the backup must have access to the user MySQL currently running the MySQL server

After the backup is complete, you need to copy the backed up files from the TMP directory, or you will lose the backup purpose.

Go back to the TMP directory to view the files you just backed up

[Email protected] ~]# cd/tmp

[email protected] tmp]# cat Stud.txt

3Xie yanke53m216

4Ding DIAN32M44

6Shi qing46m5\n

13Tian boguang33m2\n

25Sun dasheng100m\n\n

[Email protected] tmp]#

You will find that it is a 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:

Copy CodeThe code is as follows:
[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

Copy CodeThe code is as follows:
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:

Copy CodeThe code looks like this: [[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:

Copy CodeThe code is as follows:
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

Copy CodeThe code is as follows:
[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

Copy CodeThe code is as follows:
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:

Copy CodeThe code is as follows:
[[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:

Copy CodeThe code is as follows:
Mysql> set sql_log_bin=0; Turn off binary logging
mysql> flush logs; Scroll down the log

2.11 Impersonation Database corruption

Copy CodeThe code is as follows:mysql> drop database hellodb;

2.12 Start Recovery data:

Copy CodeThe code is as follows:
[[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

The verification is complete and the results are as we expected

Note:

1, really in the production environment, we should export the entire MySQL server data, not a single library, so should use--all-databases
2, in the export of binary log, you can directly copy files, but note that the backup before scrolling down the log.
3, using LVM snapshot to achieve almost hot standby data backup and recovery

3.1 Strategy:

Full backup + binary log;

3.2 Preparation:

Note: The transaction log must be on the same LV as the data file;

3.3 Creating LVM LVM creation There's not much to talk about. Click http://www.jb51.net/LINUXjishu/105937.html

3.4 Modify the MySQL master profile to store files within the directory and belong to the main group, and initialize MySQL

Copy CodeThe code is as follows:
[[email protected] ~]# mkdir/mydata/data//Create data Directory
[Email protected] ~]# chown mysql:mysql/mydata/data//change to group owner
[Email protected] ~]#
[[email protected] ~]# cd/usr/local/mysql///must stand in this directory
[[email protected] mysql]# scripts/mysql_install_db--user=mysql--datadir=/mydata/data//Initialize MySQL

3.5 Modifying the configuration file:

Copy CodeThe code is as follows:
Vim/etc/my.cof
Datadir=/mydata/data Adding data directories
Sync_binlog = 1 Turn on this feature

3.6 Starting the Service

Copy CodeThe code is as follows:
[[Email protected] mysql]# service mysqld start
Mysql> set session sql_log_bin=0; Turn off binary logging
mysql> Source/backup/all_db_2013-09-08.sql Read backup file

3.7 Back to MySQL server:

Copy CodeThe code is as follows:
Mysql> FLUSH TABLES with READ LOCK; Request Read lock
Note: Do not exit from the other terminal:
mysql> SHOW MASTER STATUS; To view the location of a binary file
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000004 |              107 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Mysql> FLUSH LOGS; It is recommended that you scroll down the log. It would be handy to back up the logs.

3.8 Export binaries, create a directory to store it separately

Copy CodeThe code is as follows:
[Email protected] ~]# Mkdir/backup/limian
[[email protected] ~]# mysql-e ' show master status; ' >/backup/limian/binlog.txt
[Email protected] ~]#

3.9 Create a snapshot of the volume where the data resides:

Copy CodeThe code is as follows: [[email protected] ~]# lvcreate-l 100m-s-P r-n mysql_snap/dev/myvg/mydata

Back to server, release read lock

Copy CodeThe code is as follows:
Mysql> UNLOCK TABLES;
[Email protected] ~]# Mount/dev/myvg/mysql_snap/mnt/data
[[email protected] data]# CP */backup/limian/
[[email protected] data] #lvremove/dev/myvg/mylv_snap

3.10 Update database data, and delete data directory data file, simulate database corruption

Copy CodeThe code is as follows:
Mysql> CREATE TABLE Limiantb (ID int,name CHAR (10));
mysql> INSERT INTO LIMIANTB values (1, ' Tom ');
[Email protected] data]# mysqlbinlog--start-position=187 mysql-bin.000003 >/backup/limian/binlog.sql
[Email protected] backup]# cd/mydata/data/
[Email protected] data]# RM-RF *
[Email protected] ~]# cp-a/backup/limian/*/mydata/data/
[Email protected] data]# chown Mysql:mysql *

3.11 Testing

Start the service

Copy CodeThe code is as follows:
[[Email protected] data]# service mysqld start
[[email protected] data]# MySQL login test
Mysql> SHOW DATABASES;
Mysql> SET sql_log_bin=0
Mysql> Source/backup/limian/binlog.sql; #二进制恢复
Mysql> SHOW TABLES; #查看恢复结果
Mysql> SET sql_log_bin=1; #开启二进制日志

Note: This way to achieve a close to hot-standby backup data files, and data files in the LVM can be based on the size of the data flexibly change the size of LVM, backup is also very simple.

4, based on Xtrabackup to do backup recovery

Official site: www.percona.com

Advantage:

1. Fast and reliable full backup
2, in the process of backup does not affect the transaction
3, support data flow, network transmission, compression, so it can effectively save disk resources and network bandwidth.
4, can automatically back up the availability of calibration data.

Installing Xtrabackup

Copy CodeThe code is as follows:
[Email protected] ~]# RPM-IVH percona-xtrabackup-2.1.4-656.rhel6.i686.rpm

The latest version of the software can be obtained from http://www.percona.com/software/percona-xtrabackup/

Note: When backing up a database, we should have permissions, but be aware that the user should be given the least privilege to back up the database to ensure security.

4.1 Prerequisites:

It should be determined that a single table space is used, otherwise single table backup and recovery is not supported.
In the configuration file inside the mysqld section Plus

innodb_file_per_table = 1

4.2 Backup Policy
Full backup + incremental backup + binary log
4.3 Prepare a directory to store backup data

Copy CodeThe code is as follows:
[Email protected] ~]# Makdir/innobackup

4.4 Do a full backup:

Copy CodeThe code is as follows:
[Email protected] ~]# Innobackupex--user=root--password=mypass/innobackup/

Note:

1, as long as the last line display innobackupex:completed ok!, it means that your backup is correct.
2. Also note that after each backup, a directory named after the current point of time is automatically created in the data directory to hold the data for the backup, so let's go and see what we have.

[[email protected] 2013-09-12_11-03-04]# ls
BACKUP-MY.CNF ibdata1 Performance_schema xtrabackup_binary xtrabackup_checkpoints
Hellodb MySQL test xtrabackup_binlog_info xtrabackup_logfile
[Email protected] 2013-09-12_11-03-04]#
Xtrabackup_checkpoints: Backup type, backup status, and LSN (log sequence number) range information;
Xtrabackup_binlog_info:mysql the binary log file currently in use by the server and the location of the binary log event up to the moment the backup was made.
Xtrabackup_logfile: Non-text files, xtrabackup own log files
Xtrabackup_binlog_pos_innodb: Binary log files and current position for binary log files for InnoDB or xtradb tables.
BACKUP-MY.CNF: Configuration about mysqld in data files during backup

4.5 Back to MySQL server to update the data

Copy CodeThe code is as follows:
mysql> use Hellodb;
Mysql> Delete from students where stuid>=24;

4.6 Incremental Backup

Copy CodeThe code is as follows:
Innobackupex--user=root--password=mypass--incremental/innobackup/--incremental-basedir=/innobackup/2013-09-12_ 11-03-04/
--incremental Specifying a backup type
--incremental-basedir= Specifies which backup this incremental backup is based on, which is a full backup file, which consolidates the incremental backup data into a full backup

4.7 Second Increment

To modify the data first

Copy CodeThe code is as follows:
Mysql> INSERT into students (Name,age,gender,classid,teacherid) VALUES (' Tom ', ' M ', ' 2,4 ');
Innobackupex--user=root--password=mypass--incremental/innobackup/--incremental-basedir=/innobackup/2013-09-12_ 11-37-01/
You only need to change the last directory to the data directory for the first incremental backup.

4.8 Last change to data but no incremental backup

Copy CodeThe code is as follows:mysql> delete from COC where id=14;

4.9 Back up binary log files (because the last modification, no incremental backup, to rely on the binary log to do point-in-time recovery)

Copy CodeThe code is as follows: [[email protected] data]# CP mysql-bin.000003/tmp/

4.10 Simulation Database Crashes

Copy CodeThe code is as follows:
[[Email protected] data]# service mysqld stop
[Email protected] data]# RM-RF *

Pre-recovery Preparation

4.11 Data synchronization for full backups

Copy CodeThe code is as follows: [[email protected] ~]# Innobackupex--apply-log--redo-only/innobackup/2013-09-12_11-03-04/

4.12 Data synchronization for the first time increment

Copy CodeThe code is as follows:
Innobackupex--apply-log--redo-only/innobackup/2013-09-12_11-03-04/--incremental-basedir=/innobackup/2013-09-12 _11-37-01/

4.13 Data synchronization to the second increment

Copy CodeThe code is as follows:
Innobackupex--apply-log--redo-only/innobackup/2013-09-12_11-03-04/--incremental-basedir=/innobackup/2013-09-12 _11-45-53/
The meaning of--apply-log is to revoke a transaction that was not commit at the time of the backup, which is already commit but is still applied to the database in the transaction log.

Note:

For Xtrabackup, it is based on transaction logs and data file backups, and the backed up data may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the database file, and should be preprocessed to synchronize committed transactions to data files and uncommitted transactions to be rolled back. Therefore its backed up database cannot be recovered immediately.

Pre-processing process:

First of all, the full backup file only to synchronize committed transactions to the data file, it is important to note that there is an increment, you cannot do the data rollback of the transaction, or your incremental backup will have no effect.

Then merge the first incremental backup into the full backup file,

And so on, merge the last few increments into the file after the previous merge, so that we can do a point-in-time recovery by holding the full backup + binary log.

4.14 Data Recovery

Copy CodeThe code is as follows:
[[Email protected] ~]# service mysqld stop
[[email protected] data]# RM-RF * Simulation Database crashes
[Email protected] ~]# Innobackupex--copy-back/innobackup/2013-09-12_11-03-04/
--copy-back database recovery, followed by the location of the backup directory

4.15 Detection:

Copy CodeThe code is as follows:
[Email protected] ~]# cd/mydata/data/
[Email protected] data]# chown Mysql:mysql *
[[email protected] data] #service mysqld start

The test result data is normal.

MySQL Backup summary (three ways to read more)