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

Backup has always been the most important link in the database, and mysql is also in proportion. The best backup tools for mysql are Percona xtrabackup and Mysql Enterprise backup (the official paid version).

There are two main tools in Xtrabackup:

Xtrabackup: a tool for hot backup of data in innodb, xtradb tables. You cannot back up other types of tables (Myisam tables), nor can you back up data table structures.

Innobackupex: a perl script that encapsulates xtrabackup to back up and restore MyISAM tables and data table structures.

However, the backup for myisam needs to be read-locked, which will have an impact on the online business. But at present, the vast majority of Mysql users use innodb, so myisam is only used by system tables, and the impact of locks can be ignored.

A description of an official document:

Http://www.percona.com/doc/percona-xtrabackup/2.1/manual.html

It is possible to use the xtrabackup binary alone, however, the recommend way is using it through the innobackupex wrapper script and let it execute xtrabackup for you. It might be helpful to first learn how to use innobackupex, and then learn how to use xtrabackup for having a better low-level understanding or control of the tool if needed.

Here, we use Percona Xtrabackup to back up Mysql.

1. Download the software and go to the download link

Https://www.percona.com/downloads/

two。 Select xtrabackup

3. Select the OS version and download

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm

4. You may encounter a lack of bread during installation:

-- > Processing Dependency: libev.so.4 () (64bit) for package: percona-xtrabackup-24-2.4.10-1.el6.x8654 Requires-> Finished Dependency ResolutionError: Package: percona-xtrabackup-24-2.4.10-1.el6.x86_64 (percona-release-x86_64) Requires: libev.so.4 () (64bit)

Go to the following URL, download the rpm package for the corresponding system, and install

Http://rpm.pbone.net/index.php3/stat/3/srodzaj/1/search/libev.so.4

download

Wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/rudi_m:/devel-snap/CentOS_CentOS-6/x86_64/libev4-4.15-7.1.x86_64.rpm

Installation

Rpm-ivh libev4-4.15-7.1.x86_64.rpm

5. Download the rpm package locally, then install it, and find that it conflicts with the existing mysql conflicts with file from package mysql-community-server

# yum-y localinstall percona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm.Transaction Check Error:file / etc/my.cnf from install of Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64 conflicts with file from package mysql-community-server-5.6.39-2.el6.x86_64

6. Install one first

# yum install MySQL-shared-compat*

7. Then install it again and resolve the conflict.

# yum localinstall percona-xtrabackup-24-2.4.10-1.el6.x86_64.rpm.Installed:percona-xtrabackup-24.x86_64 012.el6Complete 2.4.10-1.el6Dependency Installed:perl-DBD-MySQL.x86_64 0VR 4.013-3.el6 rsync.x86_64 0VR 3.0.6-12.el6Complete!

8. Backup and recovery testing

Innobackupex-defaults-file=/etc/my.cnf-user=root-password='password' / backup/20180423/

Delete files manually

/ mysql/help_topic.frmrm: remove regular file `events_stages_current.frm'? Yrm: remove regular file `events_stages_history.frm'? Yrm: remove regular file `events_stages_history_long.frm'? Yrm: remove regular file `events_stages_summary_by_account_by_event_name.frm'?

9. Integrate backup files

Innobackupex-- defaults-file=/etc/my.cnf-- apply-log / backup/20180423/2018-04-1800-58-36 /

10. Backup and restore

Innobackupex-- defaults-file=/etc/my.cnf-- copy-back / backup/20180423/2018-04-1800-58-36 /

As you can see, xtrabackup is already working properly.

For mysql backup and recovery testing, please refer to:

Https://blog.51cto.com/hsbxxl/2107383

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.