Xtrabackup installation, backup, restore, and error handling tutorials

Xtrabackup is a backup software for MySQL

Xtrabackup is a innodb to do data backup tools, Support Online hot backup (backup without affecting the data read and write), is a commercial backup tool InnoDB hotbackup a good alternative. Xtrabackup has two main tools: Xtrabackup, Innobackupex (1) Xtrabackup can only back up InnoDB and xtradb two data tables, Instead of backing up the MyISAM Data Sheet (2) innobackupex-1.5.1 encapsulates the Xtrabackup, which is a script wrapper, so the InnoDB and MyISAM can be backed up at the same time, but a read lock is required to handle MyISAM 1. First Download CD/ Usr/localwget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.1.5/binary/Linux/x86_64/ Percona-xtrabackup-2.1.5-680-linux-x86_64.tar.gz 2, check whether the server installed MySQL version, if not to install the MySQL my.cnf must have datadir=/usr/local /mysql/data (in particular, this parameter must be, the author on this issue followed)  3. Start Installation Xtrabackup 1) Unzip cd/usr/local tar zxvf percona-xtrabackup-2.1.5-680-linux-x86_64.tar.gz 2) Copy Innobackupex, Xtrabackup, xtrabackup_51 tools to/usr/bin Cp/usr/local/percona-xtrabackup-2.1.5-680-linux-x86_64/bin /innobackupex/usr/bin/innobackupex Cp/usr/local/percona-xtrabackup-2.1.5-680-linux-x86_64/bin/xtrabackup/usr/bi N/xtrabackup cp/usr/local/percona-xtrabackup-2.1.5-680-linux-x86_64/bin/xtrabackup_55/usr/bin/xtrabackup_55 4. Backup The backup that needs to be described is for the instance, not for a database/innobackupex--user=root--password=123456--defaults-file=/etc/my.cnf--databases= ' test '--slave-info/usr/local/note In this case, in the "/usr/local" directory to generate a folder based on the current time (such as "2016-03-21_13-17-34") to store the backup data, in the fifth step to restore data, you need to copy all the data in this directory past CP 2016-03-21_ 13-17-34/*/ROOT/TEST_DIR/2013-01-04_15-08-06/5. Restore Restore (1) Stop database service #/etc/init.d/mysqld Stop (2) defragment (preparing) Partial backup # Innobackupex--user=root--password=123456--defaults-file=/etc/my.cnf--apply-log--export/root/test_dir/ 2013-01-04_15-08-06/(3) Copy the backup file back to the Data directory #\cp-rf/root/test_dir/2013-01-04_15-08-06/*/usr/local/mysql/data/(4) Empowering # Chown-r Mysql:mysql/usr/local/mysql (5) Start database #/etc/init.d/mysqld start Special Note:If the following error occurs, the description does not have DBI and dbd innobackupex:connecting to MySQL server with DSN ' dbi:mysql:;mysql_read_default_file=/etc /my.cnf;mysql_read_default_group=xtrabackup ' as ' root   (using Password:yes). innobackupex:Error:Failed to Connect to MySQL server as Dbd::mysql module are not installed At/usr/bin/innobackupex line   follow these steps: Percona Toolkit installation:  1: Download Percona Toolkit Package: (can be a source package, or RPM)   access: http://www.percona.com/software/percona-toolkit/ Download the latest stable version. You can also download the latest version by following the command line.  wget Percona.com/get/percona-toolkit.tar.gz wget percona.com/get/percona-toolkit.rpm  2: Installation of Perl-related modules  yum install Perl-dbi yum install Perl-dbd-mysql yum install Perl-time-hires yum Install perl-io-socket-ssl 3: Installing Percona TOOLKIT RPM-UVH Percona-toolkit-2.2.1-2.noarch.rpm  ok , it is so simple, installation is complete.   Special note:  This machine is: Linux mysql.example.com 2.6.18-308.el5 #1 SMP Tue Feb 20:06:06 EST. x86_64 x86_64 x86_64 gnu/l inux  installation Error:  in Yum install perl-dbD-mysql, the following error message appears    ---> Package perl-dbd-mysql.x86_64 0:3.0007-2.el5 set to be updated --> Processing dependency:libmysqlclient.so.15 (libmysqlclient_15) (64bit) for package:perl-dbd-mysql --> Processing dependency:libmysqlclient.so.15 () (64bit) for package:perl-dbd-mysql --> Running transaction Check  ---> Package mysql.x86_64 0:5.0.95-5.el5_9 set updated --> processing conflict:mysql conflicts MyS Ql --> finished Dependency resolution mysql-5.0.95-5.el5_9.x86_64 from updates have depsolving problems    MySQL conflicts with mysql-server error:mysql conflicts with mysql-server  Since I installed the MySQL version of 5.5.xx, and the system comes with a 5.0xx version, and libmysqlclient.so.15 is provided by version 5.0, there is an error. If your system is CENTOS6. X-series, MySQL comes with more than 5.1, may not have this problem. The solution is as follows:  RPM-UVH Mysql-shared-compat-5.5.31-1.rhel5.x86_64.rpm mysql-shared-compat for client libraries, backwards compatible.   There's no problem with yum install Perl-dbd-mysql.   Official principles

A redo log file is maintained inside the InnoDB, and we can also call it a transaction log file. The transaction log stores record modifications for each of the InnoDB table data. When InnoDB starts, InnoDB examines the data file and transaction log and performs two steps: it applies (rolls forward) the transaction log to the data file that has been committed, and rolls back the data that has been modified but not committed.

Xtrabackup remembers the log sequence number (LSN) at startup and copies all the data files. The copy process takes some time, so if the data file changes during this period, the database will be at a different point in time. At this point, Xtrabackup runs a background process that monitors the transaction log and copies the latest modifications from the transaction log. Xtrabackup must continue to do this because the transaction log is rotated repeatedly and the transaction log can be reused. As a result, Xtrabackup has kept track of changes to every data file in the transaction log since it started.

The above is the Xtrabackup backup process. Next is the preparation (prepare) process. In this process, Xtrabackup uses the previously copied transaction log to perform a disaster recovery on individual data files (just as it did when MySQL was started). When this process is over, the database can be restored and restored.

The above procedure is implemented in the Xtrabackup binary program. The program Innobackupex allows us to back up MyISAM and frm files, thus adding convenience and functionality. Innobackupex starts xtrabackup until xtrabackup copies the data file, then executes flush TABLES with READ lock to block the new write-in and brushes the MyISAM table data to the hard disk. Then copy the MyISAM data file and finally release the lock.

The backup MyISAM and InnoDB tables will eventually be consistent, and after the preparation (prepare) process is complete, the InnoDB table data has been rolled forward to the point where the entire backup ended, rather than rolling back to the point at which the Xtrabackup was first started. This point in time is the same as the point at which the flush TABLES with READ lock is executed, so the MyISAM table data is synchronized with the InnoDB table data. Like Oracle, INNODB's prepare process can be called recover (recovery), and the MyISAM data replication process can be called restore.

Both the Xtrabackup and Innobackupex tools offer a number of features that are not mentioned in the previous article. Each feature is described in detail in the manual. In brief, these tools provide streaming (streaming) backups, incremental (incremental) backups, and so on, by replicating data files, copying log files, and committing logs to data files (roll forward) to achieve a variety of composite backup methods.

Xtrabackup installation, backup, restore, and error handling tutorials