Shulou(Shulou.com)06/01 Report--

The following to understand the use of xtrabackup tools to back up and restore MySQL database methods, I believe you will benefit a lot after reading, the text in the essence is not much, hope to use xtrabackup tools to back up and restore MySQL database methods this short content is what you want.

one。 Why do you need a backup?

In the actual production environment, mistakenly deleted operations, hardware damage will lead to data unavailable, at this time we need backup to achieve disaster recovery. It should also be noted that hardware-level redundancy, such as raid, cannot replace backup operations, because there is no data after data deletion, so backup is essential.

two。 Classification of backups 1. Physical backup and logical backup

Physical backup: it is a direct copy of mysql's data files stored on disk. This backup uses a large amount of space, especially the tablespace used by innodb to store data, because the tablespace allocation size increases by a certain proportion, so there is unused space. But it takes less time to recover.

Logical backup: export data in the form of sql statements. It takes more time to export and recover data, but takes up less space.

two。 Hot standby, warm standby, cold standby

Hot backup: there is no lock on the online environment, which does not affect any database operation on the line.

Warm standby: acquire locks between backups and impose locks. Some operations are affected during locking.

Cold backup: stop the CVM before backup, and then back up.

three。 XtraBackup

XtraBackup is an open source hot backup tool under Percona that can back up MySQL data without locking the database. It can back up InnoDB,XtraDB,MyISAM tables, and the MySQL version supports 5.1 Magi 5.5 Magi 5.5 5.7.

XtraBackup has the following functions:

Can implement hot backup for InnoDB without pausing database, can perform incremental backup for MySQL, can achieve streaming compression and transfer to other CVM MySQL service, can migrate tables between MySQL CVM, can easily create a MySQL to back up MySQL from CVM without increasing the burden on CVM.

XtraBackup can implement hot backup with tables created by InnoDB engine and warm backup for MyISAM engine.

four。 How to implement backup in XtraBackup

When backing up data, XtraBackup first backs up InnoDB data, then MyISAM data, and finally records the backup information.

How to implement backup to InnoDB by 1.XtraBackup

Before explaining the principle, we need to introduce two important features of the InnoDB engine, one is the transaction feature, and the other is the tablespace used to store data.

1) transaction

In the InnoDB engine, once the user's operation involves modifying MySQL data, he will not directly write to disk, so a transaction will be generated and the transaction will be recorded in the transaction log, which is the round-robin method used to record the log, and the transaction log space will be fixed, that is to say, the later transaction log will cover the front. The data is persisted to disk only after the user commits the transaction.

2) tablespace

InnoDB uses a tablespace to store data. In this space, InnoDB organizes the data on its own, and all the data is placed on page. The size of each page is fixed to 16KB by default, and you can resize it through innodb_page_size. In addition to database data, a page stores metadata for the table to describe the page. One of the metadata is the log sequence number log sequence number, which is the basis for incremental backups.

3) backup principle

XtraBackup starts by recording the log sequence number (LSN) and then starts copying the data files on disk. If the data changes during this period, the data is in a different location. The data has changed, we do not have to copy the changed data, we can record the transaction log of the changes during this period, because the transaction log also records the changes of the data. So during the copy of the data, XtraBackup starts a background process to monitor the transaction log and copy the transaction log written during that time. This process is a continuous process because the transaction log overwrites the previous one.

4) the principle of incremental backup.

As mentioned earlier, the implementation of incremental backup depends on LSN, which is an attribute of a page. So how to use it to achieve incremental backup during backup? First of all, we make a full backup of the data. when the data is fully backed up, the data has four pages, and the number of the page is 1pens 2pens 3pens 4. If the page data in which LSN is numbered as 1 is changed after a full backup, his LSN number will be increased by 1 to the largest LSN number, which means his number will become 5 instead of 2. By the same token, if LSN is changed to 3, his LSN number will become 6. For incremental backups, we only need to back up the page whose lsn number is 5 and 6.

Because the MyISAM engine stores data without LSN, it implements incremental backup under physical backup.

2.XtraBackup backs up the MyISAM table

XtraBackup runs LOCK TABLES FOR BACKUP to copy the MyISAM table and .frm file after backing up the InnoDB table. The lock is added after the InnoDB data is copied. The lock is a backup lock, which is more lightweight than the FLUSH TABLES WITH READ LOCK lock. And the InnoDB DML operation will not be affected during the locking period, which is why XtraBackup backup InnoDB is a hot backup and backup MyISAM is a warm backup.

When you copy MyISAM, it does not affect the operation of InnoDB data, that is, InnoDB data is changing. This can cause MyISAM and InnoDB data to be inconsistent at some point. In order to make its data consistent, transaction logs and binary logs are also needed after copying. In this process, he imposes a LOCK BINLOG FOR BACKUP lock, and the copy is completed before the binary log and table are unlocked.

3. The file created by the backup interprets backup-my.cnf: it is not the original my.cnf, but the data related to the InnoDB engine that Xtrabackup gets at the time of backup. It reads the contents of this file when it is pre-restored, or from the file specified by XtraBackup-- defaults-file. Xtrabackup_checkpoints: describes the type of backup (full or incremental), its status (for example, prepared), and its LSN scope. See the following example

Full backup backup_type = full-backupedfrom_lsn = 0to_lsn = 15188961605last_lsn = 15188961605

Incremental backup

Backup_type = incrementalfrom_lsn = 15188961605to_lsn = 15189350111last_lsn = 15189350111xtrabackup_binlog_info: obtain the current binary location of the xtrabackup_binlog_pos_innodb:InnoDB table through SHOW MASTER STATUS at the location of the CVM binary at the moment of backup. Xtrabackup_logfile related to InnoDB transactions: transaction logs copied during backup for pre-restore five. Experiment

This lab will demonstrate backup and restore operations using the innobackup command.

The operating system is centos 7.2

MySQL version 5.5

1. Install Percona-Xtrabackup

~] # wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.4-rdf58cf2-el7-x86_64-bundle.tar

Expand tarball, which contains three rpm

Percona-xtrabackup-24: contains the latest Percona XtraBackup GA binaries and related files

Percona-xtrabackup-24-debuginfo: used to debug binaries in percona-xtrabackup-24

Percona-xtrabackup-test-24:Percona Xtrabackup test component

~] # tar xf Percona-XtraBackup-2.4.4-rdf58cf2-el7-x86_64-bundle.tar

~] # yum install-y percona-xtrabackup-24-debuginfo-2.4.4-1.el7.x86_64.rpm percona-xtrabackup-24- 2.4.4-1.el7.x86_64.rpm percona-xtrabackup-test-24-2.4.4-1.el7.x86_64.rpm

two。 Full backup of data and its restore 1) create a full backup

~] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- host=127.0.0.1-- password=123456 / backups/full

If the full backup is successful, the final output information will appear completed OK!

Parameter interpretation-defaults-file: reads the default parameter of mysql, which cannot be a symbolic link file if it is the first argument on the command line. -- user-- host-- password log in to the host, username and password / backups/full: back up the directory, if not, it will be created automatically

Generate a file

[root@slave] # ls-1 / backups/full/total 0drwxrMurray Murray. 7 root root 227 Mar 16 14:20 2018-03-16 root root 14-20-35 [root@slave] # ls-1 / backups/full/2018-03-16 root root 14-20-35/total 18460 Murray RW Murray. 1 root root 417 Mar 16 14:20 backup-my.cnfdrwxr-x---. 2 root root 272 Mar 16 14:20 hellodb-rw-r-. 1 root root 18874368 Mar 16 14:20 ibdata1drwxr-x---. 2 root root 4096 Mar 16 14:20 mysqldrwxr-x---. 2 root root 4096 Mar 16 14:20 performance_schemadrwxr-x---. 2 root root 62 Mar 16 14:20 ptdrwxr-x---. 2 root root 20 Mar 16 14:20 test-rw-r-. 1 root root 25 Mar 16 14:20 xtrabackup_binlog_info-rw-r-. 1 root root 113 Mar 16 14:20 xtrabackup_checkpoints-rw-r-. 1 root root 522 Mar 16 14:20 xtrabackup_info-rw-r-. 1 root root 2560 Mar 16 14:20 xtrabackup_logfile

Innobackupex creates a directory at the current time under the specified directory where the data is stored, and all generated backup files will be in this time directory.

_ _ View xtrabackup_checkpoints file _ _ backup_type = full-backupedfrom_lsn = 0to_lsn = 1843549last_lsn = 1843549compact = 0recover_binlog_info = 0

You can see through backup_type that this is a full backup with lsn from 0 to 1843549.

2) full backup and restore

This backup cannot be used for recovery because 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 data file, which is in an inconsistent state. Therefore, we are now trying to make the data file consistent by rolling back uncommitted transactions and synchronizing committed transactions to the data file.

Pre-reduction

~] # innobackupex-- apply-log-- redo-only / backups/full/2018-03-16 million 14-20-35 /

If the pre-restore is successful, the final output information will appear completed OK!

Parameter description

-- apply-log: applies the transaction log generated during the backup. -- redo-only: indicates how the log is applied, resubmit the committed log, and do not roll back the uncommitted transaction, because it is likely to be committed in the next backup. Reduction

When restoring, the mysql CVM needs to be shut down, and the data directory needs to be empty.

~] # systemctl stop mariadb

~] # mv / var/lib/mysql / tmp

~] # mkdir / var/lib/mysql

~] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back / backups/full/2018-03-16 million 14-20-35 /

~] # chown-R mysql.mysql / var/lib/mysql

~] # systemctl start mariadb2. Full backup + incremental backup and its restore

Backup

Full backup

~] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- host=127.0.0.1-- password=123456 / backups/full

First incremental backup

~] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- host=127.0.0.1-- password=123456-- incremental / backups/incr-1-- incremental-basedir=/backups/full/2018-03-16 million 14-20-35 /

Second incremental backup

~] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- host=127.0.0.1-- password=123456 / backups/incr-2 / backups/incr-2-- incremental-basedir=/backups/incr-1/2018-03-16015-20-32 /

Parameter description

-- incremental indicates that this backup is incremental. -- incremental-basedir indicates who the incremental backup is based on. / backups/incr-1 indicates the directory where the backup is placed

Pre-reduction

Full backup pre-restore

~] # innobackupex-- apply-log-- redo-only / backups/full/2018-03-16 million 14-20-35 /

First incremental backup pre-restore

~] # innobackupex-- apply-log-- redo-only / backups/incr-1/2018-03-16 incremental-dir=/backups/full/2018 15-20-32 /-- incremental-dir=/backups/full/2018-03-16 million 14-20-35 /

The second incremental backup and restore

~] # innobackupex-- apply-log-- redo-only / backups/incr-1/2018-03-16-01-44 /-incremental-dir=/backups/full/2018-03-16-20-35 /

-- incremental-dir: indicates which backup to use for restore. Reduction

Refer to backup restore 6. Backup script #! / bin/bash# Description: innobackupex backup MySQL# Date: 2018-01-2 backup Version: 0.1. Backup Author: lirou# this backup plan is a weekly cycle, Monday is a full backup, and Tuesday to Sunday are incremental backups based on Monday. # when restoring in a week, you only need a full backup on Monday and an incremental backup on Sunday. Backup_dir_parent=/var/lib/mysql/backupsbackup_dir=full_backup_dir=increment_backup_dir=backup_times=backup_max_times=5# login parameters mysql_host=127.0.0.1mysql_user=rootmysql_password=123456#MySQL service configuration file defaults_file=/etc/my.cnf# record backup is the output information backup_log=/var/log/innobackup/innobackup.log# record whether each backup is successful backup_status=/var/log/innobackup/innobackup.status! [ -d $backup_dir_parent] & & mkdir-pv $backup_dir_parent! [- d $backup_log] & & mkdir-pv $backup_logweek=$ (date "+% u") if [$week-eq 1] Then backup_times=$ (ls-l $backup_dir_parent | grep-I'^ d.roombackup.backup'| wc-l) if [$backup_times-ge $backup_max_times] Then rm-rf ${backup_dir_parent} / $(ls-lt $backup_dir_parent | tail-l) fi backup_dir=$ {backup_dir_parent} / backup-$ (date "+% Y-%m-%d") full_backup_dir=$ {backup_dir} / full innobackupex-- defaults-file=$ {defaults_file}-- host=$ {mysql_host}-- user=$ {mysql_user}-password=$ {mysql_password} ${full_backup _ dir} & > $backup_logelse backup_dir=$ {backup_dir_parent} / $(ls-lt $backup_dir_parent | head-1 | grep-o 'backup.*') increment_backup_dir=$ {backup_dir} / incr-$ {week} innobackupex-- defaults-file=$ {defaults_file}-- host=$ {mysql_host}-- user=$ {mysql_user}-- password=$ {mysql_password}- Incremental=$ {backup_dir} / full ${increment_backup_dir} & > $backup_log fiecho "$(date'+% YMY% MMI% d'): $?" > > $backup_status

When making scheduled tasks, this backup script must make sure that Monday's full backup is performed, because the increments from Tuesday to Sunday are based on him.

After reading this article on how to back up and restore MySQL databases using xtrabackup tools, many readers will want to know more about it. For more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.