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

This article mainly introduces what MySQL backup tools generally have, the contents of the articles are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand what MySQL backup tools generally have.

MySQL backup tool

Common MySQL backup tools

Disadvantages of physical backup

-poor cross-platform

-long backup time, redundant backup, waste of storage space

Disadvantages of mysqldump backup

-low efficiency and slow backup and restore

-data insert and update operations are suspended during backup

XtraBackup tool

A powerful online hot backup tool

-the library table is not locked during backup, which is suitable for production environment

-provided by the professional organization Percona (improved MySQL score

Branch)

It mainly contains two components.

-xtrabackup: C program, which supports InnoDB/XtraDB

-innobackupex: encapsulates xtrabackup in Perl script and supports MyISAM

Step 1: install the XtraBackup package

1) learn about package description information

[root@dbsvr1 pub] # rpm-qpi percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpmName: percona-xtrabackup-24Version: 2.4.6Release: 2.el7Architecture: x86_64Install Date: (not installed) Group: Applications/DatabasesSize: 32416340License: GPLv2Signature: DSA/SHA1, Monday, February 27, 2017, 20:28:17 Key ID 1c4cbdcdcd2efd2aSource RPM: percona-xtrabackup-24-2.4.6-2.el7.src.rpmBuild Date: Monday, February 27, 2017 20:27:21 Build Host: vps-centos7-x64-01.ci.percona.comRelocations: (not relocatable) URL: http://www.percona.com/software/percona-xtrabackupSummary: XtraBackup online backup for MySQL / InnoDBDescription: Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines

2) install the dependency package perl-DBD-MySQL perl-Digest-MD5 libev

Use the one that comes with RHEL 7 and install it in yum:

`[ root@dbsvr1 pub] # yum-y install perl-DBD-MySQL perl-Digest-MD5

Libev uses the rpm package found online libev-4.15-1.el6.rf.x86.rpm

[root@dbsvr1 pub] # rpm-ivh libev-4.15-1.el6.rf.x8664.rpm``

If these dependent packages are not installed, there will be an error when installing percona-xtrabackup directly: 3) install percona-xtrabackup

[root@dbsvr1 pub] # rpm-ivh percona-xtrabackup-*.rpm

Warning: percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm: header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing. # # [100%]

Upgrading / installing...

1:percona-xtrabackup-24-2.4.6-2.el7## [33%]

2:percona-xtrabackup-test-24-2.4.6 music # [67%]

3:percona-xtrabackup-24-debuginfo-2## [100%]

4) confirm the main program / script installed

[root@dbsvr1 pub] # rpm-ql percona-xtrabackup-24-2.4.6-2.el7.x86_64

/ usr/bin/innobackupex

/ usr/bin/xbcloud

/ usr/bin/xbcloud_osenv

/ usr/bin/xbcrypt

/ usr/bin/xbstream

/ usr/bin/xtrabackup

/ usr/share/doc/percona-xtrabackup-24-2.4.6

/ usr/share/doc/percona-xtrabackup-24-2.4.6/COPYING

/ usr/share/man/man1/innobackupex.1.gz

/ usr/share/man/man1/xbcrypt.1.gz

/ usr/share/man/man1/xbstream.1.gz

/ usr/share/man/man1/xtrabackup.1.gz

`Step2: innobackupex full backup and incremental backup operation

-- host hostname

-- port 3306

-- user user name

-- password password

-- databases= "Library name"

-- databases= "Library 1 Library 2"

-- databases= "Library. Table"

-- no-timestamp uses the backup database name as the backup directory name without a date to name the subdirectory stored by the backup file.

-- no-timestmap does not use date naming backup directory name``

1) make a full backup

By default, the subdirectory of the backup file store is named after the date

Innobackupex, as a client tool, connects to mysqld with mysql protocol and backs up the data to / backup folder

[root@dbsvr1] # innobackupex-- user=root-- password=1234567 / backup/mysql-no-timestamp170425 11:05:44 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!" .Uncovered character\ X01 Marked by log scanned up to (2543893) xtrabackup: Generating a list of tablespacesInnoDB: Allocated tablespace ID 2 for mysql/plugin Old maximum was 0170425 11:05:45 [01] Copying. / ibdata1 to / backup/ibdata1170425 11:05:45 [01]... done170425 11:05:46 [01] Copying. / mysql/plugin.ibd to / backup/mysql/plugin.ibd170425 11:05:46 [01]... done170425 11:05:46 [01] Copying. / mysql/servers.ibd to / backup/mysql/servers.ibd170425 11:05:46 [01]... done170425 11 05:46 [01] Copying. / mysql/help_topic.ibd to / backup/mysql/help_topic.ibd170425 11:05:46 [01]... done170425 11:05:46 > > log scanned up to (2543893) .. 170425 11:06:00 [01] Copying. / sys/x@0024waits_global_by_latency.frm to / backup/sys/x@0024waits_global_by_latency.frm170425 11:06:00 [01]... done170425 11:06:00 [01] Copying. / sys/session_ssl_status.frm to / backup/sys/session_ssl_status.frm170425 11:06:00 [01]... done170425 11:06:00 [01] Copying. / Db1/db.opt to / backup/db1/db.opt170425 11:06:00 [01]... done170425 11:06:00 [01] Copying. / db1/tb1.frm to / backup/db1/tb1.frm170425 11:06:00 [01]... done170425 11:06:00 Finished backing up non-InnoDB tables and files170425 11:06:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...xtrabackup: The latest check point (for incremental): '2543884'xtrabackup Stopping log copying thread..170425 11:06:00 > > log scanned up to (2543893) 170425 11:06:00 Executing UNLOCK TABLES170425 11:06:00 All tables unlocked170425 11:06:00 [00] Copying ib_buffer_pool to / backup/ib_buffer_pool170425 11:06:00 [00]. Done170425 11:06:00 Backup created in directory'/ backup/'170425 11:06:00 [00] Writing backup-my.cnf170425 11 Executing UNLOCK TABLES170425 0615 00 [00]. .Transaction log of lsn 170425 11:06:00 [00] Writing xtrabackup_info170425 11:06:00 [00]... donextrabackup: Transaction log of lsn (2543884) to (2543893) was copied.170425 11:06:01 completed OK

Confirm the backed-up file data

[root@dbsvr1 ~] # ls / backup/backup-my.cnf ib_buffer_pool mysql sys xtrabackup_infodb1 ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile

2) make an incremental backup (based on the full backup of the previous step)

Feel free to add or change database tables, such as creating a new mytb table in the db1 library:

Mysql > USE db1;Database changedmysql > CREATE TABLE mytb (id int (4), name varchar (24)); Query OK, 0 rows affected (0.38 sec) mysql > INSERT INTO tb1 VALUES-> (1 INSERT INTO tb1 VALUES'),-> (2 rows affected (0.12 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > SELECT * FROM tb1 +-+-+ | id | name | +-+-+ | 1 | bob | | 2 | bo | +-+-+ 2 rows in set (0.00 sec)

Based on the previous full backup saved to / backup, making an incremental backup, saving to the base directory (full backup directory) referenced by the / incr01/, specified incremental backup requires the option-incremental-basedir. The related operations are as follows

[root@dbsvr1] # innobackupex-user=root-password=12345678-incremental / incr01-incremental-basedir=/backup/-no-timestamp170425 11:30:14 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!" .Uncovered character\ X01 Marked by log scanned up to (2549933) xtrabackup: Generating a list of tablespacesInnoDB: Allocated tablespace ID 2 for mysql/plugin Old maximum was 0xtrabackup: using the full scan for incremental backup170425 11:30:15 [01] Copying. / ibdata1 to / incr01/ibdata1.delta170425 11:30:15 [01]... done170425 11:30:15 > > log scanned up to (2549933) 170425 11:30:15 [01] Copying. / mysql/plugin.ibd to / incr01/mysql/plugin.ibd.delta170425 11:30:15 [01]... done... ... 170425 11:30:35 Executing UNLOCK TABLES170425 11:30:35 All tables unlocked170425 11:30:35 [00] Copying ib_buffer_pool to / incr01/ib_buffer_pool170425 11:30:35 [00]... done170425 11:30:35 Backup created in directory'/ incr01/'170425 11:30:35 [00] Writing backup-my.cnf170425 11:30:35 [00]... done170425 11 30 incr01/ib_buffer_pool170425 35 [00] Writing xtrabackup_info170425 11:30: 35 [00]... donextrabackup: Transaction log of lsn (2549924) to (2549933) was copied.170425 11:30:35 completed OK!

Confirm the backed up file data:

[root@dbsvr1 ~] # ls / incr01/

Backup-my.cnf ib_buffer_pool ibdata1.meta performance_schema xtrabackup_checkpoints xtrabackup_logfile

Db1 ibdata1.delta mysql sys

Compare the size of full backups and incremental backups:

[root@dbsvr1 ~] # du-sh / backup/ / incr01/142M / backup/ size of the full backup 3.5m / incr01/ ```/ / step 3: restore the database directory where the data is backed up through the XtraBackup tool, and if you want to restore to another MySQL CVM You need to do a "--apply-log-- redo-only" preparatory operation first. 1) after the "full backup" is ready, / backup can be used to rebuild the MySQL CVM. In this case, you need to do a "--apply-log-- redo-only" preparatory operation to ensure data consistency:

[root@dbsvr1] # innobackupex-- user=root-- password=12345678-- apply-log-- redo-only / backup/

170425 11:42:19 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

Prints "completed OK!".

Innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 8ec05b7)

Xtrabackup: cd to / backup/

Xtrabackup: This target seems to be already prepared.

InnoDB: Number of pools: 1

Xtrabackup: notice: xtrabackup_logfile was already used to'--prepare'.

Xtrabackup: using the following InnoDB configuration for recovery:

Xtrabackup: innodb_data_home_dir =.

Xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

Xtrabackup: innodb_log_group_home_dir =.

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 50331648

Xtrabackup: using the following InnoDB configuration for recovery:

Xtrabackup: innodb_data_home_dir =.

Xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

Xtrabackup: innodb_log_group_home_dir =.

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 50331648

Xtrabackup: Starting InnoDB instance for recovery.

Xtrabackup: Using 104857600 bytes for buffer pool (set by-use-memory parameter)

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin _ _ atomic_thread_fence () is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Not using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100m, instances = 1, chunk size = 100m

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority:-20

InnoDB: Highest supported file format is Barracuda.

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 2544177

InnoDB: Number of pools: 1

170425 11:42:20 completed OK!

Prepare to restore incremental backup

[root@dbsvr1] # innobackupex-user=root-password=12345678-apply-log-redo-only / backup/-incremental-dir=/incr01

170425 11:42:55 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

Prints "completed OK!".

Innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 8ec05b7)

Incremental backup from 2543884 is enabled.

Xtrabackup: cd to / backup/

Xtrabackup: This target seems to be already prepared with-- apply-log-only.

InnoDB: Number of pools: 1

Xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn= (2549924)

Xtrabackup: using the following InnoDB configuration for recovery:

Xtrabackup: innodb_data_home_dir =.

Xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

Xtrabackup: innodb_log_group_home_dir = / incr01/

Xtrabackup: innodb_log_files_in_group = 1

Xtrabackup: innodb_log_file_size = 8388608

Xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0

Xtrabackup: page size for / incr01//ibdata1.delta is 16384 bytes

Applying / incr01//ibdata1.delta to. / ibdata1...

......

170425 11:43:09 [01] Copying / incr01/performance_schema/global_status.frm to. / performance_schema/global_status.frm

170425 11:43:09 [01]... done

170425 11:43:09 [01] Copying / incr01/performance_schema/session_status.frm to. / performance_schema/session_status.frm

170425 11:43:09 [01]... done

170425 11:43:09 [00] Copying / incr01//xtrabackup_info to. / xtrabackup_info

170425 11:43:09 [00]... done

170425 11:43:10 completed OK!

2) close the mysql service and delete the file under / var/lib/mysql/, assuming the data is deleted. [root@dbsvr1 ~] # systemctl stop mysqld [root@dbsvr1 ~] # rm-rf / var/lib/mysql3) after the "full backup + incremental backup" is prepared, the final / backup is still used to rebuild the MySQL CVM, but in this case, the data of the relevant incremental backups need to be merged in advance.

[root@dbsvr1] # innobackupex-- user=root-- password=12345678-- copy-back / backup/

......

170425 11:51:39 [01] Copying. / performance_schema/global_status.frm to / var/lib/mysql/performance_schema/glo.frm

170425 11:51:39 [01]... done

170425 11:51:39 [01] Copying. / performance_schema/session_status.frm to / var/lib/mysql/performance_schema/seus.frm

170425 11:51:39 [01]... done

170425 11:51:39 [01] Copying. / ib_buffer_pool to / var/lib/mysql/ib_buffer_pool

170425 11:51:39 [01]... done

170425 11:51:39 [01] Copying. / ibtmp1 to / var/lib/mysql/ibtmp1

170425 11:51:39 [01]... done

170425 11:51:39 [01] Copying. / xtrabackup_info to / var/lib/mysql/xtrabackup_info

170425 11:51:39 [01]... done

170425 11:51:39 completed OK!

4) modify the file owner and group under / var/lib/mysql/ to view the data: after recovery, both the file group and owner under / var/lib/mysql are root and need to be changed to mysql

Root@dbsvr1 ~] # chown-R mysql:mysql / var/lib/mysql

[root@dbsvr1 ~] # systemctl start mysqld.service

[root@dbsvr1] # mysql-uroot-p12345678-e "select * from db1.tb1"

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | bob |

| | 2 | bo |

+-+ +

After reading the above about MySQL backup tools, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.