Mysql backup three ways to explain _mysql

First, the purpose of the backup

Do disaster recovery: Restore and restore corrupted data
Change in demand: need to restore data to change before it changes
Testing: Testing whether new features are available

Second, backup needs to consider the issue

Data that can tolerate the amount of time lost;
How long the recovery data will be completed;
Whether there is a need for continuous service during recovery;
The object being recovered is the entire library, multiple tables, or a single library, a single table.

Iii. Type of Backup

1, according to whether the need for the database offline

Lengbei (cold Backup): Need to shut down the MySQL service, read and write requests are not allowed in the state;
Win Bei (Warm Backup): The service is online, but only read requests are supported, write requests are not allowed;
Hot Backup: Backup While the business is not affected.

Note:

1, this type of backup, depending on the business needs, rather than backup tools
2, MyISAM does not support hot standby, INNODB support hot standby, but need special tools

2, according to the scope of the data set to be backed up
Full backups: Full backup, backup of 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 the backups depends on how often the data is updated.
Differential backup: Differential data that has changed since the last full backup.
Recommended recovery strategy:
Full + incremental + binary log
Full + variance + binary Log

3, according to the backup data or documents

Physical backup: Backing up data files directly

Advantages:

Backup and restore operations are simpler and can span MySQL versions,
Fast recovery, belonging to file system level

Suggestions:

Do not assume that backups are necessarily available, test
Mysql>check tables, checking table availability
Logical backup: Data and code in a backup table

Advantages:

Recovery Simple,
The result of the backup is an ASCII file that can be edited
Nothing to do with the storage engine
Can be backed up and restored over the network

Disadvantages:

Backup or restore requires MySQL server process involvement
The backup results occupy more space,
Floating-point numbers may lose precision
After restoration, miniature needs rebuilding

Four: Objects to be backed up

1, data;
2, configuration documents;
3, code: stored procedures, stored functions, triggers
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 Code code as follows:

mysql> use Hellodb; Open Hellodb Library
Mysql> SELECT * from students; View properties for Students
Mysql> SELECT * from students where age > into outfile '/tmp/stud.txt '; Back up the information of the students who are older than 30

Attention:

The backup directory path 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 purpose of the backup.

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

[Root@www ~]# Cd/tmp

[Root@www tmp]# Cat Stud.txt

3Xie yanke53m216

4Ding DIAN32M44

6Shi qing46m5\n

13Tian boguang33m2\n

25Sun dasheng100m\n\n

[Root@www tmp]#

You'll find it's a text file. Therefore, you cannot import the database directly. Need to use load data infile recovery

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

Mysql> Delete from students where age > 30;

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

2, using the Mysqldump tool to backup and restore data

Mysqldump is often used to do Win Bei, so we first need to apply a read lock to the data we want to back up.

2.1 Ways of applying the read lock:

1. Add options directly at the time of backup

--lock-all-tables is to impose read locks on 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 is also when we back up a table to the table to apply a read lock, so for the backup sheet

2, in the server-side writing commands,

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

Mysql> flush tables with read lock;

But this for the InnoDB storage engine, although you can also request read lock, but does not mean that all of its data has been synchronized to disk, so when the face of InnoDB, we have to use the Mysql> show engine InnoDB status; Look at the InnoDB all the data has been synchronized to disk, before the backup operation.

2.2 Strategy for Backup:

Full backup + incremental backup + binary log

Demo the backup process;

2.3 Make a full backup of the database first:

Copy Code code as follows:

[Root@www ~]# mysqldump-uroot--single-transaction--master-data=2--databases hellodb >/backup/hellodb_ ' Date +%F '. Sql

--single-transaction: The hot standby innodb table can be realized based on this option; Therefore, the--lock-all-tables is not needed at the same time;
--master-data=2 records the location of the binary log at the time the backup was made, and commented out that 1 is not annotated
--databases Hellodb Specify the database to be backed up

And then back to the MySQL server side,

2.4 Back to MySQL server side update data

Copy Code code as follows:

Mysql> CREATE TABLE tb1 (id int); Create a table
mysql> INSERT INTO TB1 values (1), (2), (3); Inserts the data, here only does the demonstration, randomly inserts several data

2.5 First view the location of the full backup file in the record:

Copy Code code as follows:

[Root@www 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 back to the server side:

Copy Code code as follows:

Mysql> Show master status; Displays the location of the binary log at this time
From the backup file, the location of the record to our position at this point, that is the incremental part
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000004 |              15982 |                  | |
+------------------+----------+--------------+------------------+

2.7 Making incremental backups

Copy Code code as follows:

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

2.8 to the server again

Copy Code code as follows:

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

2.9 Export this get binary log:

Copy Code code as follows:

[Root@www backup]# mysqlbinlog--start-position=15982/mydata/data/mysql-bin.000013 View the location of the binary log when the delete operation was made
[Root@www backup]# mysqlbinlog--start-position=15982--stop-position=16176/mydata/data/mysql-bin.000013 >/tmp/ Hellodb.sql//Export binary log

2.10 Get MySQL offline first

Back to server side:

Copy Code code as follows:

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

2.11 Simulated database corruption

Copy Code code as follows:

mysql> drop Database Hellodb;

2.12 Starting Data recovery:

Copy Code code as follows:

[root@www]# MySQL </backup/hellodb_2013-09-08.sql//import Full backup file
[root@www]# MySQL </backup/hellodb_2013-09-08_05.sql//import Incremental backup files
[Root@www]# mysql< hellodb.sql//import binary files

Validation completed, showing results as we expected

Note:

1, really in the production environment, we should export the entire MySQL server data, rather than a single library, so you should use the--all-databases
2, in the export of binary log, you can directly copy the file, but to note that the backup before scrolling under the log.
3, using LVM snapshots 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 Create LVM LVM creation here is not much to say, want to understand the words click http://www.jb51.net/LINUXjishu/105937.html

3.4 Modify the MySQL main configuration file to store the files within the directory permissions and the owner of the group, and initialize MySQL

Copy Code code as follows:

[Root@www ~]# mkdir/mydata/data//Create data Directory
[Root@www ~]# chown mysql:mysql/mydata/data//Change Group owner
[Root@www ~]#
[Root@www ~]# cd/usr/local/mysql///must stand in this directory
[Root@www mysql]# scripts/mysql_install_db--user=mysql--datadir=/mydata/data//initialization of MySQL

3.5 Modify configuration file:

Copy Code code as follows:

Vim/etc/my.cof
Datadir=/mydata/data Add Data Directory
Sync_binlog = 1 Turn on this feature

3.6 Start Service

Copy Code code as follows:

[root@www mysql]# Service mysqld start
Mysql> set session sql_log_bin=0; Turn off binary log
mysql> Source/backup/all_db_2013-09-08.sql Read backup files

3.7 Back to MySQL server:

Copy Code code as follows:

Mysql> FLUSH TABLES with READ LOCK; Request Read lock
Note: Do not exit, another 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. This will be convenient when you back up the logs.

3.8 Export binary files, create a directory to store separately

Copy Code code as follows:

[Root@www ~]# Mkdir/backup/limian
[Root@www ~]# mysql-e ' show master status; ' >/backup/limian/binlog.txt
[Root@www ~]#

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

Copy Code code as follows:

[Root@www ~]# lvcreate-l 100m-s-P r-n mysql_snap/dev/myvg/mydata

Back to server, release read lock

Copy Code code as follows:

Mysql> UNLOCK TABLES;
[Root@www ~]# Mount/dev/myvg/mysql_snap/mnt/data
[Root@www data]# CP */backup/limian/
[Root@www data] #lvremove/dev/myvg/mylv_snap

3.10 Update the data of the database, and delete the data file before the data directory, simulate the database damage

Copy Code code as follows:

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

3.11 Test

Start a service

Copy Code code as follows:

[root@www data]# Service mysqld start
[root@www 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 approach enables close to hot standby to back up data files, and the data files are placed in LVM to vary the size of the LVM based on the size of the data, and the backup is simple.

4, based on Xtrabackup to do backup recovery

Official site: www.percona.com

Advantage:

1, fast and reliable for a full backup
2, in the process of backup will not affect the transaction
3, support data flow, network transmission, compression, so it can effectively save disk resources and network bandwidth.
4, can automatically backup verify the availability of data.

Install Xtrabackup

Copy Code code as follows:

[Root@www ~]# RPM-IVH percona-xtrabackup-2.1.4-656.rhel6.i686.rpm

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

Note: When backing up a database, we should have permissions, but it should be noted that the minimum permissions should be given to the user when backing up the database to ensure security.

4.1 Prerequisites:

It should be determined that a single table table space is used, otherwise Tanku backup and recovery is not supported.
The Mysqld section in the configuration file adds

innodb_file_per_table = 1

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

Copy Code code as follows:

[Root@www ~]# Makdir/innobackup

4.4 Make a full backup:

Copy Code code as follows:

[Root@www ~]# Innobackupex--user=root--password=mypass/innobackup/

Note:

1, as long as the last line shows innobackupex:completed ok!, it means that your backup is correct.
2, also note that after each backup, automatically in the data directory to create a directory named at the current point of time to store the backup data, then we go to see what is

[Root@www 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
[Root@www 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 that the server is currently using, and the location of the binary log event until this moment of backup.
Xtrabackup_logfile: Non-text file, xtrabackup own log file
Xtrabackup_binlog_pos_innodb: Binary log files and the current position of binary log files for InnoDB or xtradb tables.
BACKUP-MY.CNF: Configuration of mysqld in data files during backup

4.5 Back to MySQL server side to update the data

Copy Code code as follows:

mysql> use Hellodb;
Mysql> Delete from students where stuid>=24;

4.6 Incremental Backups

Copy Code code as follows:

Innobackupex--user=root--password=mypass--incremental/innobackup/--incremental-basedir=/innobackup/2013-09-12_ 11-03-04/
--incremental Specify the type of backup
--incremental-basedir= specifies which backup to base this incremental backup is, and this is a full backup file, which merges the incremental backup data into a full backup

4.7 Second Increase

Change the data first.

Copy Code code as follows:

Mysql> INSERT into students (Name,age,gender,classid,teacherid) VALUES (' Tom ', A, ' M ', 2,4);
Innobackupex--user=root--password=mypass--incremental/innobackup/--incremental-basedir=/innobackup/2013-09-12_ 11-37-01/
Here 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 Code code as follows:

Mysql> Delete from CoC where id=14;

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

Copy Code code as follows:

[Root@www data]# CP mysql-bin.000003/tmp/

4.10 Analog Database crashes

Copy Code code as follows:

[root@www data]# service mysqld Stop
[Root@www data]# RM-RF *

Prepare before recovery

4.11 Data synchronization for full backups

Copy Code code as follows:

[Root@www ~]# Innobackupex--apply-log--redo-only/innobackup/2013-09-12_11-03-04/

4.12 Data synchronization for the first increment

Copy Code code 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 for the second increment

Copy Code code as follows:

Innobackupex--apply-log--redo-only/innobackup/2013-09-12_11-03-04/--incremental-basedir=/innobackup/2013-09-12 _11-45-53/
The significance of the--apply-log is to undo the transactions that were not commit at the time of the backup, but also to apply them to the database in the transaction log

Note:

For Xtrabackup, it is based on transaction log and data file backup, the backed-up data may contain transactions that have not yet been committed, or transactions 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. So its backed up database cannot be recovered immediately.

The process of preprocessing:

First of all, the full backup file to synchronize only committed transactions to the data file, to note that there is an increment, the transaction can not be rolled back, otherwise your incremental backup will have no effect.

The first incremental backup is then merged into the full backup file,

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

4.14 Data Recovery

Copy Code code as follows:

[root@www ~]# service mysqld Stop
[Root@www data]# RM-RF * Simulate database crashes
[Root@www ~]# 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 Code code as follows:

[Root@www ~]# cd/mydata/data/
[Root@www data]# chown Mysql:mysql *
[Root@www data] #service mysqld start

The test result data is normal.

This article from "Lost ぜ ァ Beautiful ~" blog