Three methods for mysql backup _ MySQL

Three methods for mysql backup: bitsCN.com

I. purpose of backup

Disaster Recovery: recover and restore damaged data
Demand Change: data needs to be restored to a time before the change due to demand changes
Test: test whether the new function is available

II. backup considerations

How long data can be lost;
How long will the data be restored;
Whether to provide continuous services during recovery;
The recovered object is the entire database, multiple tables, or a single database and a single table.

III. backup type

1. database offline as required

Cold standby (cold backup): mysql service needs to be closed and read/write requests are not allowed;
Warm backup: The service is online, but only supports read requests. write requests are not allowed;
Hot backup: services are not affected when backup is performed.

Note:

1. this type of backup depends on business needs, rather than backup tools.
2. MyISAM does not support hot backup. InnoDB supports hot backup, but specialized tools are required.

2. based on the range of the data set to be backed up
Full backup: full backup backs up all character sets.
Incremental backup: The data changed since the last full backup or incremental backup cannot be used independently. to use full backup, the backup frequency depends on the data update frequency.
Differential backup: The data changed since the last full backup of differential backup.
Recommended recovery policy:
Full + incremental + binary log
Full + difference + binary log

3. backup data or files

Physical Backup: directly back up data files

Advantages:

Backup and recovery operations are relatively simple and can be performed across mysql versions,
Fast recovery, which belongs to the file system level

Suggestion:

Do not test if the backup is available.
Mysql> check tables; check whether the table is available
Logical backup: data and code in the backup table

Advantages:

Simple recovery,
The backup result is an ASCII file and can be edited.
Independent from the storage engine
Network backup and recovery are supported.

Disadvantages:

The mysql server process is required for backup or recovery.
The backup results occupy more space,
Floating point numbers may lose precision.
After restoration, the epitome needs to be rebuilt.

IV. backup objects

1. data;
2. configuration file;
3. Code: stored procedures, stored functions, and triggers
4. OS-related configuration files
5. copy Related configurations
6. binary logs

V. implementation of backup and recovery

1. use select into outfile to back up and restore data
1.1 back up the data to be backed up

Mysql> use hellodb; // open the hellodb database
Mysql> select * from students; view the attributes of students
Mysql> select * from students where Age> 30 into outfile '/tmp/stud.txt'; // back up the information of students older than 30

Note:

The directory path of the backup must allow the user currently running the mysql server to access mysql.

After the backup is complete, copy the backup file from the tmp directory, or you will lose the backup purpose.

Go back to the tmp directory to view the backup file.

[Root @ www ~] # Cd/tmp

[Root @ www tmp] # cat stud.txt

3Xie Yanke53M216

4 Ding Dian32M44

6Shi Qing46M5/N

13 Tian Boguang33M2/N

25Sun Dasheng100M/N

[Root @ www tmp] #

You will find a text file. Therefore, the database cannot be imported directly. Use load data infile to restore data

Return to the mysql server, delete users older than 30, and the simulation data is damaged.

Mysql> delete from students where Age> 30;

Mysql> load data infile '/tmp/stud.txt' into table students;

2. use mysqldump to back up and restore data

Mysqldump is often used for warm Backup. Therefore, we need to apply a read lock to the data to be backed up first,

2.1 method of applying a read lock:

1. add options directly during backup

-- Lock-all-tables: Apply a read lock to all tables in the database to be backed up.

-- Lock-table only applies a read lock to a single table. even if it backs up the entire database, it also applies a read lock to the table when we back up a table. Therefore, it applies to backing up a single table.

2. write commands on the server,

Mysql> flush tables with read lock; apply a lock to synchronize all tables in the memory to the disk, and then apply a read lock.

Mysql> flush tables with read lock; release the read lock

But for the InnoDB storage engine, although you can also request the channel read lock, it does not mean that all its data has been synchronized to the disk, so when facing InnoDB, we need to use mysql> show engine innodb status; check that all InnoDB data has been synchronized to the disk before performing the backup operation.

2.2 backup policy:

Full Backup + incremental backup + binary log

Demonstrate the backup process;

2.3 make a full backup of the database first:

[Root @ www ~] # Mysqldump-uroot -- single-transaction -- master-data = 2 -- databases hellodb>/backup/hellodb _ 'date + % f'. SQL

-- Single-transaction: This option enables hot backup of InnoDB tables. Therefore, you do not need to use -- lock-all-tables at the same time;
-- Master-data = 2 record the location of the binary log at the time of backup, and comment it out. 1 is not commented out.
-- Databases hellodb specifies the database to be backed up

Then return to the mysql server,

2.4 Return to the mysql server to update data

Mysql> create table tb1 (id int); create a table
Mysql> insert into tb1 values (1), (2), (3); insert data. this example only shows how to insert data.

2.5 First view the location recorded in the full backup file:

[Root @ www backup] # cat hellodb_2013-09-08. SQL | less
-- Change master to MASTER_LOG_FILE = 'MySQL-bin.000013 ', MASTER_LOG_POS = 15684; the location of the binary log is recorded.

2.6 Return to the server:

Mysql> show master status; displays the location of the binary log at this time
From the location recorded in the backup file to our location at this time, that is, the incremental part
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000004 | 15982 |
+ ------------------ + ---------- + -------------- + ------------------ +

2.7 perform incremental backup

[Root @ www backup] # mysqlbinlog -- start-position = 15694 -- stop-position = 15982
/Mydata/data/mysql-bin.000013>/backup/hellodb _ 'date + $ F _ % H'. SQL

2.8 Return to the server

Mysql> insert into tb1 values (4), (5); insert some values
Mysql> drop database hellodb; delete hellodb database

2.9 export this binary log:

[Root @ www backup] # mysqlbinlog -- start-position = 15982/mydata/data/mysql-bin.000013 to view the location of the binary log during the delete operation
[Root @ www backup] # mysqlbinlog -- start-position = 15982 -- stop-position = 16176/mydata/data/mysql-bin.000013>/tmp/hellodb. SQL // export binary logs

2.10 let mysql go offline first

Return to the server:

Mysql> set SQL _log_bin = 0; disable binary logs
Mysql> flush logs; scroll down logs

2.11 simulate database corruption

Mysql> drop database hellodb;

2.12 start data recovery:

[Root @ www] # mysql </backup/hellodb_2013-09-08. SQL // import a full backup file
[Root @ www] # mysql </backup/hellodb_2013-09-08_05. SQL // Import incremental backup file
[Root @ www] # mysql

The verification is complete and the result is shown as we expected

Note:

1. in the production environment, we should export data from the entire mysql server instead of a single database. Therefore, we should use -- all-databases
2. when exporting binary logs, you can directly copy the files, but note that the logs are rolled down before backup.
3. use lvm snapshots for almost hot backup data backup and recovery

3.1 Policy:

Full Backup + binary log;

3.2 Preparation:

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

3.3 Create lvm Lvm creation here is not much said, would like to know click http://www.bitsCN.com/LINUXjishu/105937.html

3.4 modify the permissions of the files in the mysql main configuration file storage directory and the owner group, and initialize mysql

[Root @ www ~] # Mkdir/mydata/data // create a data directory
[Root @ www ~] # Chown mysql: mysql/mydata/data // change owner of the group
[Root @ www ~] #
[Root @ www ~] # Cd/usr/local/mysql // must be in this directory
[Root @ www mysql] # scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data // initialize mysql

3.5 modify the configuration file:

Vim/etc/my. cof
Datadir =/mydata/data add data directory
Sync_binlog = 1 enable this function

3.6 start the service

[Root @ www mysql] # service mysqld start
Mysql> set session SQL _log_bin = 0; disable binary logs
Mysql> source/backup/all_db_2013-09-08. SQL read backup files

3.7 return to the mysql server:

Mysql> flush tables with read lock; read lock request
Note: do not exit. start another terminal:
Mysql> show master status; view the location of the 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 to scroll down the log. This makes it easy to back up logs.

3.8 export binary files and create directories for separate storage

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

3.9 create a snapshot for the volume where the data is located:

[Root @ www ~] # Lvcreate-L 100 M-s-p r-n mysql_snap/dev/myvg/mydata

Return to the server and release the read lock.

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 database data and delete the first data file in the data directory to simulate database corruption

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 the service

[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; # binary recovery
Mysql> show tables; # View recovery results
Mysql> SET SQL _log_bin = 1; # enable binary log

Note: This method is similar to hot backup to back up data files, and the data files can be stored in lvm to flexibly change the lvm size based on the data size. the backup method is also very simple.

4. Xtrabackup-based backup recovery

Official site: www.percona.com

Advantages:

1. fast and reliable full backup
2. transactions are not affected during the backup process.
3. supports data stream, network transmission, and compression, so it can effectively save disk resources and network bandwidth.
4. data availability can be automatically verified by backup.

Install Xtrabackup

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

Its latest software available from http://www.percona.com/software/percona-xtrabackup/

Note: We should have the permission to back up the database, but note that we should grant the minimum permission to the user during database backup to ensure security,

4.1 prerequisites:

It should be determined that one tablespace is used for a single table; otherwise, backup and recovery for a single table are not supported.
Add the mysqld section in the configuration file

Innodb_file_per_table = 1

4.2 backup policy
Full Backup + incremental backup + binary log
4.3 prepare directories for storing backup data

[Root @ www ~] # Makdir/innobackup

4.4 Complete backup:

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

Note:

1. as long as innobackupex: completed OK is displayed in the last line !, It indicates that your backup is correct.
2. Note that after each backup, a directory named after the current time point is automatically created under the Data Directory to store the backup data. let's see what is there.

[Root @ www 2013-09-12_11-04 4] # ls
The backup-my.cnf ibdata1 performance_schema xtrabackup_binary xtrabackup_checkpoints.
Hellodb mysql test xtrabackup_binlog_info xtrabackup_logfile
[Root @ www 2013-09-12_11-04 4] #
Xtrabackup_checkpoints: backup type, backup status, and LSN (log serial number) range information;
Xtrabackup_binlog_info: The binary log file currently in use by the mysql server and the location of the binary log event until the moment of backup.
Xtrabackup_logfile: non-text file, xtrabackup your own log file
Xtrabackup_binlog_pos_innodb: the current position of the binary log file used for InnoDB or XtraDB tables.
Backup-my.cnf: Configure mysqld in the data file during backup

4.5 return to the mysql server to update the data

Mysql> use hellodb;
Mysql> delete from students where StuID> = 24;

4.6 incremental backup

Innobackupex -- user = root -- password = mypass -- incremental/innobackup/-- incremental-basedir =/innobackup/2013-09-12_11-03-04/
-- Incremental specifies the backup type
-- Incremental-basedir = specifies the backup based on which incremental backup is made. here it is a full backup file, which can merge incremental backup data into a full backup.

4.7 second increment

Modify data first

Mysql> insert into students (Name, Age, Gender, ClassID, TeacherID) values ('Tom ', 33, '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 of the first incremental backup.

4.8 last data change without incremental backup

Mysql> delete from coc where id = 14;

4.9 back up the binary log file (because incremental backup is not performed for the last modification, the binary log must be relied on for time point recovery)

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

4.10 simulate database crash

[Root @ www data] # service mysqld stop
[Root @ www data] # rm-rf *

Preparations before recovery

4.11 synchronize full backup data

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

4.12 perform data synchronization for the first increment

Innobackupex -- apply-log -- redo-only/innobackup/2013-09-12_11-03-04/-- incremental-basedir =/innobackup/2013-09-12_11-37-01/

4.13 perform data synchronization for the second increment

Innobackupex -- apply-log -- redo-only/innobackup/2013-09-12_11-03-04/-- incremental-basedir =/innobackup/2013-09-12_11-45-53/
-- Apply-log: cancels a transaction that has not been committed during Backup. The transaction logs that have been committed are still applied to the database.

Note:

For xtrabackup, it is backed up based on transaction logs and data files. the backed up data may contain uncommitted transactions or transactions that have been committed but not synchronized to the database files, we should also pre-process the transaction to synchronize the committed transaction to the data file, and roll back the Uncommitted transaction. Therefore, the database backed up cannot be recovered immediately.

Preprocessing process:

First, only committed transactions are synchronized to the data file for the full backup file. Note that data rollback cannot be performed for the transaction when there is an increment, otherwise, your incremental backup will be ineffective.

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

And so on, merge the subsequent increments into the files after the previous merge. in this way, we only need to take the full backup + binary log to restore the data at a time point.

4.14 data recovery

[Root @ www ~] # Service mysqld stop
[Root @ www data] # rm-rf * simulate database crash
[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:

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

Check result data is normal.

This article is from the "beautiful region with lost tokens ~" Blog

BitsCN.com