MySQL data backup and recovery commands in Linux

This article describes how to use MySQL data backup and recovery commands in Linux, including Mysqldump, LVM snapshot, and xtrabackup! For more information, see back up data regularly to ensure data security. There are many backup methods and the effects are different. Once an error occurs in the data in the database, you must use the backup data for restoration. This minimizes the loss. Next, let's take a look at three common MySQL backup recovery methods:

1. back up data using Mysqldump + binary logs
2. use LVM snapshot + binary log for backup
3. use Xtrabackup for backup

I. Experiment environment introduction:

System Introduction: CentOS6.4 _ X64
Database version: mysql-5.5.33

II. backup recovery based on the Mysqldump command

2.1 Concept

Mysqldump is a logical backup command that backs up data in a database into a text file. It can also be said that the structure and data of a table are stored in a text file.

The Mysqldump command works very easily. it first detects the structure of the table to be backed up, and then generates a CREATE statement in a text file. Then, convert all the records in the table into an INSTERT statement. These CREATE statements and INSTERT statements are used for restoration. When restoring data, you can use the CREATE statement to CREATE a table. Use the INSERT statement to restore data. It can be used to back up the entire server, or to back up certain rows, stored procedures, stored functions, and triggers in a single or partial database, single or partial table, or table; it can automatically record the binary log files and their corresponding locations at the backup time. The InnoDB storage engine supports hot backup based on the single transaction mode, while MyISAM supports hot backup at most.

2.2. backup policy

Mysqldump full backup + binary log incremental backup

2.3 process implementation

(1) Mysqldump full backup
Because the Mysql database uses the MyISAM storage engine by default, it only uses warm backup (backup only supports read requests). Therefore, we need to add read locks for all databases.

[root@stu18 ~]#mysqldump -uroot -pmypass --lock-all-tables --master-data=2 --events --routines--all-databases > /zhao/database_`date +%F`.sql

Resolution:-lock-all-tables indicates that the read lock is applied to all tables;-master-data = 2 indicates that the current binary log location is recorded in the backup file; -events indicates the code of the time scheduler for backing up data at the same time;-routines indicates the stored procedure and storage function for backing up data at the same time;-all-databases indicates backing up all databases.

[Root @ stu18 zhao] # less database_2013-08-13. SQL -- # indicate annotation item -- Position to start replication or point-in-time recovery from ---- CHANGE MASTER TO MASTER_LOG_FILE = 'MySQL-bin.000001 ', MASTER_LOG_POS = 14203; # Here it indicates that the Current is in the binary log of the mysql-bin.000001, the event is 14203 which is generated through -- master-data = 2 ---- Current Database: 'hellodb' -- create database /*! 32312 if not exists */'hellodb '/*! 40100 default character set utf8 */;

(2) binary full backup

Method 1: export binary log file content

[root@stu18 data]# mysqlbinlog mysql-bin.000001 >/zhao/binlog_`date +%F`.sql

Method 2: copy a file by rolling logs

Mysql> flush logs; # rolling logs [root @ stu18 data] # cp mysql-bin.000001/zhao/mysql-bin.000001 # Copy and export binary files

(3) binary incremental backup
First add data information

mysql> use hellodb;mysql> INSERT INTO students(Name,Age,Gender,ClassID,TeacherID) values ('Yang kang',22,'M',3,3);

Then add binary data

[root@stu18 data]# mysqlbinlog --start-position=14203 --stop-position=14527 mysql-bin.000001 > /zhao/binlog_`date +%F_%H`.sql

Resolution:-start-position = 14203 indicates the location of the binary event after the last full backup;-stop-position = 14527 indicates the location of the binary event on the last day.

2.4 simulate database corruption to achieve recovery

Mysql> drop database hellodb; # delete a database ########### the following processes must be executed offline ############# mysql> SET SQL _log_bin = 0; # First disable the binary log mysql> flush logs; # rolling log [root @ stu18 ~] # Mysql-uroot-pmypass </zhao/database_2013-08-13. SQL # import database backup file [root @ stu18 ~] # Mysql-uroot-pmypass </zhao/binlog_2013-08-13_19. SQL # import incremental backup file [root @ stu18 ~] # Mysql-uroot-pmypass # log on to mysql and restore mysql> SET SQL _log_bin = 1;

This backup method is easy to recover, but after restoration, the index will be restored with errors, and the backup results will occupy a lot of space. please use it as appropriate.

PS: Summary of common mysqldump commands
Command for backing up MySQL database

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

Back up the MySQL database in the format of a table with deletion

Backing up a MySQL database is in the format of a table with deletion, so that the backup can overwrite existing databases without the need to manually delete the original database.

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

Directly compress and back up the MySQL database

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

Back up a MySQL database table

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

Back up multiple MySQL databases at the same time

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

Back up database structures only

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

Back up all databases on the server

mysqldump –all-databases > allbackupfile.sql

Command for restoring MySQL database

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

Restore a compressed MySQL database

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

Transfer Database to new server

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

III. LVM snapshot-based backup recovery

3.1 train of thought details

(1) LVM backup requires that Mysql data be stored on a logical volume.
(2) you need to apply a read lock (Mysql> flush tables with readlock;) to the mysql server. you cannot exit the server directly.
(3) create a snapshot (lvcreate) for the volume where the data is located on another terminal to ensure that the transaction log and data file must be created on the same volume (the data files and transaction logs may be inconsistent if they are created separately, this may lead to abnormal recovery)

3.2. backup policy

LVM snapshot full backup + binary log incremental backup (for instant point recovery, it must be restored to the subsequent binary location)

3.3 prerequisites

(1) create and mount a logical volume. this process is not demonstrated here.

(2) initialize mysql to direct its data directory to/mydata/data

[root@stu18 ~]# cd /usr/local/mysql/[root@stu18 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data

(3) edit and view the configuration file and restart the service.

[Root @ stu18 mysql] # vim/etc/my. cnfdatadir =/mydata/data # check whether this item defines the data directory location sync_binlog = 1 # add this item. when each transaction is committed, the transaction log is written from the cache to the log file, and refresh the log file data to the disk. [root @ stu18 mysql] # service mysqld start

3.4 process Display

(1) ensure that the transaction log and data file must be on the same volume

[root@stu18 ~]# ls /mydata/data/hellodb  myclass   mysql-bin.000003 stu18.magedu.com.erribdata1  mysql    mysql-bin.000004 stu18.magedu.com.pidib_logfile0 mysql-bin.000001 mysql-bin.index  studentib_logfile1 mysql-bin.000002 performance_schema test

Resolution: ib_logfile0 and ib_logfile1 are log files.
(2) apply a global lock and scroll logs

mysql> FLUSH TABLES WITH READ LOCK;mysql> FLUSH LOGS;

(3) view and save the currently used binary log and the location of the currently executed binary log (very important)

mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File    | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000004 |  187 |    |     |+------------------+----------+--------------+------------------+[root@stu18 zhao]# mysql -uroot -pmypass -e 'SHOW MASTER STATUS;' >/zhao/lvmback-2013-08-14/binlog.txt

(4) create a snapshot volume

[root@stu18 zhao]# lvcreate -L 100M -s -p r -n mydata-lvm /dev/vg1/mydata

(5) immediately switch the terminal to release the lock

mysql> UNLOCK TABLES;

(6) back up data

[root@stu18 data]# cp -a * /zhao/lvmback-2013-08-14/

(7) binary incremental backup

Mysql> use hellodb; # specify the default Database changedmysql> create table testtb (id int, name CHAR (10); # CREATE a TABLE Query OK, 0 rows affected (0.35 sec) mysql> insert into testtb VALUES (1, 'Tom '); # Add data Query OK, 1 row affected (0.09 sec) [root @ stu18 data] # mysqlbinlog -- start-position = 187 mysql-bin.000004>/zhao/lvmlogbin_2013-08-14/binlog. SQL # incremental log backup

(8) simulate database crash

[root@stu18 ~]# service mysqld stop[root@stu18 ~]# cd /mydata/data/[root@stu18 data]# rm -rf *

(9) restore data

[Root @ stu18 ~] # Cp/zhao/lvmback-2013-08-14/*/mydata/data/-a # full backup recovery [root @ stu18 ~] # Cd/mydata/data/# view the recovered data content [root @ stu18 data] # chown-R mysql. mysql * # Change owner group [root @ stu18 data] # service mysqld start # start service [root @ stu18 data] # mysql-uroot-pmypass # logon test

Mysql> show databases; # View data integrity, no Test table testtd use binary restore mysql> SET SQL _log_bin = 0 # Disable binary log mysql> source/zhao/lvmlogbin_2013-08-14/binlog. SQL; # Restoring mysql in binary mode> SHOW TABLES; # View recovery results + ----------------- + | Tables_in_hellodb | + ------------------- + | classes | coc | courses | scores | students | teachers | testtb | toc | + cores + mysql> SET SQL _log_bin = 1; # enable binary log

This tool is similar to the hot standby method, and it is very fast to back up and restore data.

IV. xtrabackup-based backup recovery

4.1. advantages and features

It is fully performed in the form of hot standby, which enables fast and reliable full backup and partial Backup. it supports incremental backup and time point restoration, and does not disturb transaction operations during the backup process, network Transmission and compression can be implemented to effectively save disk space. after the backup is complete, the system can automatically verify whether the data is available and the recovery speed is fast. For more advantages, see http://www.percona.com/software/percona-xtrabackup

Note: these advantages can only be achieved perfectly on the InnoDB engine, while on the MyISAM storage engine, you can only use the warm backup mode at most, and incremental backup is not supported yet.
In addition, more advanced Xtrabackup functions depend on the Mysql database to implement separate tablespaces for InnoDB. Otherwise, there is no way to import or export a single table as follows:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file%';+--------------------------+----------+| Variable_name   | Value |+--------------------------+----------+| innodb_file_format  | Antelope || innodb_file_format_check | ON  || innodb_file_format_max | Antelope || innodb_file_per_table | ON  |+--------------------------+----------+

If innodb_file_per_table is ON, a single form space is implemented. If it is OFF, use mysqldump for full backup, change the configuration file, delete the original data file, reinitialize the server, and re-import the data. Therefore, we recommend that you set the option to 1 by default when installing the Mysql server (innodb_file_per_table = 1 ). The data in a single form space is displayed as follows:

[root@stu18 hellodb]# lsclasses.frm coc.MYD  courses.MYI scores.MYI teachers.frm testtb.ibdclasses.MYD coc.MYI  db.opt  students.frm teachers.MYD toc.frmclasses.MYI courses.frm scores.frm students.MYD teachers.MYI toc.MYDcoc.frm  courses.MYD scores.MYD students.MYI testtb.frm toc.MYI

4.2 install Xtrabackup

Download percona-xtrabackup latest version 2.1.4 (percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm)
Installation:

[root@stu18 ~]# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

If an error occurs, install the perl-DBD-mysql dependency package.

[root@stu18 ~]# yum -y install perl-DBD-mysql

Note: There may be multiple dependency packages in different environments. please follow the prompts to configure them.

4.3 full backup

When innobakupex is used for backup, it will call xtrabackup to back up all InnoDB tables and copy all the files related to table structure definition (. frm), and files related to MyISAM, MERGE, CSV, and ARCHIVE tables. files related to triggers and database configuration information are also backed up. These files are saved to a time command directory. The full backup command is as follows:

# innobackupex --user=DBUSER--password=DBUSERPASS /path/to/BACKUP-DIR/

Implementation process and description:

[Root @ stu18 ~] # Mkdir/innobackup # Create a backup file directory [root @ stu18 ~] # Innobackupex -- user = root -- password = mypass/innobackup/# full backup ################ output after correct execution the row information is usually as follows ############ xtrabackup: transaction log of lsn (1604655) to (1604655) was copied. # location of the binary log (lsn) 130814 07:04:55 innobackupex: All tables unlockedinnobackupex: Backup created in directory '/innobackup/2013-08-14_07-04-49' # location of the Backup file innobackupex: MySQL binlog position: filename 'MySQL-bin.000003 ', position 538898130814 07:04:55 innobackupex: Connection to database server closed130814 07:04:55 innobackupex: completed

OK! Backup complete
Switch to the backup file directory to view the backup data and the generated files:

 
[root@stu18 ~]# cd /innobackup/2013-08-14_07-04-49/[root@stu18 2013-08-14_07-04-49]# lsbackup-my.cnf myclass    student   xtrabackup_binlog_infohellodb  mysql    test    xtrabackup_checkpointsibdata1  performance_schema xtrabackup_binary xtrabackup_logfile

For file parsing:

(1) xtrabackup_checkpoints -- backup type (such as full or incremental), backup status (such as whether it is already in prepared status), and LSN (log serial number) range information;
Each InnoDB page (usually 16 kB) contains a log serial number, that is, the LSN. The LSN is the system version number of the entire database system. The LSN related to each page can indicate how the page has changed recently.
(2) 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.
(3) xtrabackup_binary -- the xtrabackup executable file used in backup;
(4) backup-my.cnf-the configuration option information used for backup, that is, the configuration file related to mysqld configuration;
(5) xtrabackup_logfile -- non-text files are the log files of xtrabackup;

4.4 prepare a full backup

Generally, after the backup is complete, the data cannot be used for restoration, because the backup data may contain uncommitted transactions or transactions that have been committed but not yet synchronized to the data file. Therefore, the data files are still inconsistent. The main function of "preparation" is to roll back uncommitted transactions and synchronize committed transactions to data files so that the data files are in a consistent state.

The-apply-log option of the innobakupex command can be used to implement the above functions. Run the following command:

[Root @ stu18 ~] # Innobackupex-apply-log/innobackup/2013-08-14_07-04-49/############# if the execution is correct, the last few lines of output information are usually as follows ############## xtrabackup: starting shutdown with innodb_fast_shutdown = 1130814 7:39:33 InnoDB: starting shutdown... 130814 7:39:37 InnoDB: Shutdown completed; log sequence number 1606156130814 07:39:37 innobackupex: completed OK!

4.5 simulate database crash to achieve full recovery

(1) simulate a crash

[root@stu18 ~]# service mysqld stop[root@stu18 ~]# cd /mydata/data/[root@stu18 data]# rm -rf *

(2) recover data from full backup (remember: Do not initialize the database or start the service before restoring data)
The-copy-back option of the innobackupex command is used to perform the restoration operation. it copies all data-related files to the DATADIR Directory of the mysql server to execute the restoration process. Innobackupex gets information about the DATADIR Directory through a backup-my.cnf.

[Root @ stu18 ~] # Innobackupex -- copy-back/innobackup/2013-08-14_07-04-49/############# if the execution is correct, the last few lines of output information are usually as follows ############## innobackupex: Starting to copy InnoDB log filesinnobackupex: in '/innobackup/2013-08-14_07-04-49' innobackupex: back to original InnoDB log directory '/mydata/data' innobackupex: copying '/innobackup/2013-08-14_07-04-49/ib_logfile0' to '/mydata/data' innobackupex: Copying'/innobackup/2013-08-1 4_07-04-49/ib_logfile1 'to'/mydata/data' innobackupex: Finished copying back files.130814 07:58:22 innobackupex: completed OK!

(3) after data is restored to the data directory, make sure that the owner and group of all data files are correct, such as mysql. otherwise, before starting mysqld, you must modify the owner and Group of the data file in advance.

# chown -R mysql:mysql /mydata/data/

(4) log on to the startup server and check that the restoration is complete.

[root@stu18 data]# service mysqld start

Note: after each restoration is complete, you must perform a full backup again !!

4.6 use innobackupex for incremental backup

Note: Each InnoDB page contains an LSN. when the related data changes, the LSN of the related page automatically increases. This is the foundation for InnoDB tables to perform incremental backup, that is, innobackupex is implemented by backing up pages that have changed since the last full backup.
Incremental backup with the first data change
To implement incremental backup, run the following command:

[root@stu18 data]# innobackupex --user=root --password=mypass --incremental /innobackup --incremental-basedir=/innobackup/2013-08-14_08-14-12/

Here,/innobackup refers to the directory where the full backup is located. after this command is executed, the innobackupex command creates a new directory named after time in the/backup directory to store all incremental backup data. -Incremental-basedir indicates the directory where the last full backup is located.

Perform incremental backup for the second data change:

[root@stu18 ~]# innobackupex --user=root --password=mypass --incremental /innobackup --incremental-basedir=/innobackup/2013-08-14_08-29-05/

The command for executing the second incremental backup is roughly the same as that for the first time. only its-incremental-basedir should refer to the directory where the last incremental backup is located.

Incremental Backup has not been performed for the third data change.

mysql> delete from coc where id=14;

4.7 use innobackupex to restore data based on full + incremental + binary logs

(1) because I have written binary logs and data files in the same file directory, we must copy the binary log files before simulating database crashes, we recommend that you store data directories and binary directories separately. The method is as follows:
The premise is to perform the following operations before the server has been created;

Mkdir/mybinlog # Create a directory to store the binary log chown mysql: mysql/mybinlog # Change the permission vim/etc/my. cnf # Modify the configuration file log-bin =/mybinlog/mysql-bin # prefix of the binary log directory and file name

Now let's get down to copying binary log files:

[root@stu18 data]# cp mysql-bin.000001/innobackup/

(2) simulate server crash

[root@stu18 ~]# service mysqld stop[root@stu18 ~]# cd /mydata/data/[root@stu18 data]# rm -rf *

(3) prepare backup

First, note that "prepare" incremental backup is different from "complete backup". In particular, note the following:
1) on each backup (including full and incremental backup), you must "replay" the committed transactions ". After "Replay", all the backup data will be merged to the full backup.
2) roll back uncommitted transactions based on all backups ".

Full Backup "preparation"

[root@stu18 ~]# innobackupex --apply-log --redo-only/innobackup/2013-08-14_08-14-12/

The first incremental backup "preparation" means that the first incremental backup is merged into the full backup.

[root@stu18 ~]# innobackupex --apply-log--redo-only /innobackup/2013-08-14_08-14-12/--incremental-dir=/innobackup/2013-08-14_08-29-05/

The second incremental backup "preparation" means that the second incremental backup is also merged into the full backup.

[root@stu18 ~]# innobackupex --apply-log--redo-only /innobackup/2013-08-14_08-14-12/ --incremental-dir=/innobackup/2013-08-14_09-08-39/

-Redo-only synchronizes committed transactions to data files, and uncommitted transaction logs are not rolled back.

(4) Data Recovery (based on innobackupex + incremental)

[root@stu18 ~]# innobackupex --copy-back/innobackup/2013-08-14_08-14-12/

(5) change the owner of the group

[root@stu18 ~]# cd /mydata/data/[root@stu18 data]# chown -R mysql:mysql *

(6) start viewing

[root@stu18 ~]# mysql -uroot -pmypasmysql> select * from coc;+----+---------+----------+| ID | ClassID | CourseID |+----+---------+----------+| 1|  1 |  2 || 2|  1 |  5 || 3|  2 |  2 || 4|  2 |  6 || 5|  3 |  1 || 6|  3 |  7 || 7|  4 |  5 || 8|  4 |  2 || 9|  5 |  1 || 10 |  5 |  9 || 11 |  6 |  3 || 12 |  6 |  4 || 13 |  7 |  4 || 14 |  7 |  3 |+----+---------+----------+14 rows in set (0.00 sec)

The results show that the data is correct and complete, but the third change does not take effect.

(7) binary log-based data recovery
View the location of the last incremental backup binary log:

[root@stu18 data]# cd /innobackup/2013-08-14_09-08-39/[root@stu18 2013-08-14_09-08-39]# cat xtrabackup_binlog_infomysql-bin.000001 780

View the binary log file to export the binary logs of unbacked data

[Root @ stu18 innobackup] # mysqlbinlog mysql-bin.000001 # at 780 #130814 9:20:19 server id 1 end_log_pos 851 Query thread_id = 7 exec_time = 0 error_code = 0 set timestamp = 1376443219 /*! */; BEGIN /*! */; # At 851 #130814 9:20:19 server id 1 end_log_pos 944 Query thread_id = 7 exec_time = 0 error_code = 0 set timestamp = 1376443219 /*! */; Delete from coc where id = 14 /*! */; # At 944 #130814 9:20:19 server id 1 end_log_pos 1016 Query thread_id = 7 exec_time = 0 error_code = 0 set timestamp = 1376443219 /*! */; COMMIT /*! */; DELIMITER; # End of log fileROLLBACK/* added by mysqlbinlog */;/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;/*! 50530 SET @ SESSION. pseudo do_slave_mode = 0 */; [root @ stu18 innobackup] # mysqlbinlog -- start-position = 780 mysql-bin.000001>./all. SQL # export data

Restore data

[Root @ stu18 ~] # Mysql-uroot-pmypassmysql> SET SQL _LOG_BIN = 0; # Disable binary log mysql> source/innobackup/all. SQL # import data mysql> SET SQL _LOG_BIN = 1; # enable binary log mysql> select * from coc; # View data, recovery completed + ---- + --------- + ---------- + | ID | ClassID | CourseID | + ---- + --------- + ---------- + | 1 | 1 | 2 | 2 | 1 | 5 | 3 | 2 | 2 | 4 | 2 | 6 | 5 | 3 | 1 | 6 | 3 | 7 | 7 | 4 | 5 | 8 | 4 | 2 | 9 | 5 | 1 | 10 | 5 | 9 | 11 | 6 | 3 | 12 | 6 | 4 | 13 | 7 | 4 | + ---- + --------- + ---------- + 13 rows in set (0.00 sec)

This backup recovery method completely implements full backup and incremental backup and binary log restoration in the form of hot backup, and recovery speed is also very fast, is the best backup recovery method !!

Summary:The preceding three types of backup recovery can be performed based on binary log files, which reflects the importance of binary logs and maps the importance of logs; so learning to view and use log files is the top priority for learning Mysql!