XtraBackup online MySQL Master/Slave deployment 1

XtraBackup online MySQL Master/Slave deployment 1

[Preface] There are many master-slave deployment methods for MySQL. The most common method is mysqldump. As we all know, mysqldump has two major disadvantages:

1. The master database needs to lock the table, which means that the master database cannot be modified during this period (related interfaces and applications need to be stopped during export, which may not affect small companies, however, if it is an e-commerce platform, it is equivalent to stopping services;

2. When the data volume is small, the mysqldump method is fast, but when the data volume exceeds 20 GB, the whole process is quite slow. It once took 50 minutes to export a 70 GB database using high-end storage.

Percona provides xtrabackup open-source backup tools to quickly and unlocks mysql backups and record the corresponding log information. The features are as follows:
• The backup process is fast and reliable;
• The backup process does not interrupt ongoing transactions;
• Saves disk space and traffic based on compression and other functions;
• Automatic backup check;
• Fast Restoration speed;

This document describes how to build a mysql Master/Slave database through xtrabackup;

[1] Software Installation

1.1 https://www.percona.com/downloads/XtraBackup/

The current version has been updated to 2.2.11;

1.2 Software Installation

After downloading the appropriate rpm package, run: rpm-ivh percona-xtrabackup-2.2.11-1.el6.x86_64.rpm

[Root @ OTO-DB-T02 share] # rpm-ql percona-xtrabackup
/Usr/bin/innobackupex
/Usr/bin/xbcrypt
/Usr/bin/xbstream
/Usr/bin/xtrabackup
 

1.3 introduction to software

XtraBackup mainly contains three tools

Xtrabackup: supports innodb Storage engine tables and xtradb storage engine tables.

Innobackupex: supports innodb Storage engine tables, xtradb storage engine tables, and myisam storage engine tables.

Xbstream: A stream backup tool used to reduce IO () during Backup and transmission ()
 

1.4 configure datadir Parameters

[Root @ OTO-DB-T02 backup] # more/etc/my. cnf | grep datadir
Datadir =/data/mysql

Note: When the datadir parameter is not configured, the following error is returned:

Innobackupex: got a fatal error with the following stacktrace: at/usr/bin/innobackupex line 2510
Main: copy_back (0) called at/usr/bin/innobackupex line 1570
Innobackupex: Error: Backup data file '/data/backup/2015-06-15_14-06-59/ibdata1' does not exist. at/usr/bin/innobackupex line 2510.
 

[2] use of tools

[Root @ OTO-DB-T02 backup] # innobackupex -- help
Options:
-- Apply-log
Prepare a backup in BACKUP-DIR by applying the transaction log file
Named "xtrabackup_logfile" located in the same directory. Also,
Create new transaction logs. The InnoDB configuration is read from
The file "backup-my.cnf ".

-- Backup-locks
This option controls if backup locks shoshould be used instead of FLUSH
Tables with read lock on the backup stage. The option has no effect
When backup locks are not supported by the server. This option is
Enabled by default, disable with -- no-backup-locks.

-- Close-files
Do not keep files opened. This option is passed directly
Xtrabackup. Use at your own risk.

-- Compact
Create a compact backup with all secondary index pages omitted. This
Option is passed directly to xtrabackup. See xtrabackup
Documentation for details.

-- Compress
This option instructs xtrabackup to compress backup copies of InnoDB
Data files. It is passed directly to the xtrabackup child process.
Try 'xtrabackup -- help' for more details.
 

2.1 complete backup and Restoration Process

Step 1: complete database backup

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

2.2 InnoDB backup to make backup available

Innobackupex -- user = root -- password = '000000' -- apply-log/data/backup/2015-06-15_14-57-24 (after Step 1 backup is complete, the 2015-06-15_14-57-24 directory will be generated)

Note: The first step is to back up the data files of the current database. However, during the backup, the system changes cannot be backed up. Therefore, we need to back up data in step 2. This step can be used to record all changes made in the backup Step 1 system. In this way, backups in step 1 and Step 2 will be accumulated during the recovery process to form a complete backup;

2.3 database Restoration

Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = '000000' -- copy-back/data/xtrabackup/2015-02-01_08-30-13/

2.4 file authorization

Chown-R mysql: mysql/data/mysql

2.5 start the database

Service mysqld start; start the database

2.6 connect to the master database based on the information in/data/mysql

[Root @ OTO-DB-T02 mysql] # cat xtrabackup_binlog_pos_innodb
./Mysql-bin.000027 17645

MySQL management-using XtraBackup for Hot Backup

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

XtraBackup details: click here
XtraBackup: click here

This article permanently updates the link address: