Centos6.5 IP address :192.168.117.129
1. Download the installation package links: www.percona.com/downloads/X…
Three packages: Percona xtrabackup - 24-2.4.12-1. El6. I686. RPM percona xtrabackup - 24 - debuginfo - 2.4.12-1. El6. I686. RPM Percona xtrabackup - test - 24-2.4.12-1. El6. I686. RPMCopy the code2. Run the download
Yum -y install percona-xtrabackup-24-2.4.12-1.el6.i686. RPM percona-xtrabackup-24-debuginfo-2.4.12-1.el6.i686.rpm \ Percona xtrabackup - test - 24-2.4.12-1. El6. I686. RPMCopy the codePercona-xtrabackup depends on libev.so.4(64bit) package
Download package: ftp://rpmfind.net/linux/dag/redhat/el6/en/i386/dag/RPMS/libev-4.15-1.el6.rf.i686.rpm ftp://rpmfind.net/linux/dag/redhat/el6/en/i386/dag/RPMS/libev-devel-4.15-1.el6.rf.i686.rpm can be installed wellCopy the code-
1. Back up a full copy of data:
Innobackupex --defaults-file=/etc/my.cnf --host=192.168.117.129 --user=root --password=82111 /backup recognized server arguments: --datadir=/var/lib/mysql xtrabackup: recognized client arguments: --datadir=/var/lib/mysql 180809 01:20:56 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!" . 180809 01:20:56 version_check Connecting to MySQL server with DSN 'dbi:mysql:; mysql_read_default_group=xtrabackup; Host =192.168.117.129' as 'root' (using password: YES). Failed to connect to MySQL server: DBI connect('; mysql_read_default_group=xtrabackup; The host = 192.168.117.129 ', 'root',...). failed: Host '192.168.117.129' is not allowed to connect to this MySQL server at-line 1314 180809 01:20:57 Connecting to MySQL Server host: 192.168.117.129, user: root, password: set, port: not set, socket: not set Failed to connect to MySQL server: Host '192.168.117.129' is not allowed to connect to this MySQL server. innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=82111 --port=3306 /backup --host Server host: 127.0.0.1, user: root, password: set, port: 3306, socket: not set Error: Built-in InnoDB in MySQL 5.1 is not supported in this release. You can either use Percona XtraBackup 2.0, or upgrade to InnoDB plugin.Copy the code2. Modified Version:
Percona xtrabackup - 2.0.0-417. The rhel6. I686. RPM percona xtrabackup - debuginfo 2.0.0-417. The rhel6. I686. RPMCopy the code–host is 192.168.117.129
Innobackupex --defaults-file=/etc/my.cnf --host=192.168.117.129 --user=root --password=82111 --port=3306 /backup InnoDB Backup Utility v1.5.1 - xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Inc 2009-2012. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 180809 02:38:31 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password= XXXXXXXX --user='root' --host='192.168.117.129' --port='3306' --unbuffered -- 180809 02:38:31 innobackupex: Connected to database with mysql child process (pid=2752) innobackupex: Error: Mysql child process has died: ERROR 1130 (HY000): Host '192.168.117.129' is not allowed to connect to this mysql serverCopy the codeFull backup:
Innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=82111 --port=3306 /backup successful --defaults-file Specifies that the configuration file can be omittedCopy the codeCNF: this file contains some of the Settings of my.cnf, but not all of them. Xtrabackup_bin_info: this file records the location of a binary log file that is used to backup data. Xtrabackup_checkpoints: This file records the backup type, whether it is a full or incremental backup, and the starting LSN number during the backup. Xtrabackup_info: indicates the general information about the backup. Xtrabackup_logfile: records logs about the backup process. After preparing for data, logs are used to restore the dataCopy the codeXtrabackup_checkpoints:
Backup and restore:
When we started the innodb backup data, we also need to corresponding transaction log backups at the same time, because, at the beginning of the backup some issues existing in the transaction log, these transactions may have been submitted, but has not yet been synchronized to the datafile, so we have submitted to replay the transaction, some transactions may not submit, So we want to roll back those uncommitted transactions. Xtrabackup does this. When abackup is started, two threads are running at the same time. One thread is responsible for backing up innoDB pages and the other thread is responsible for backing up innoDB transaction logs. After the backup, two file, we will get a backup file is not available, is a transaction log backup, backup file is unavailable, because a part of the uncertain data may be in the transaction log, but also in the process of hot standby data may change, so we have to through the two documents, create a backup file available, that's right, The final backup will be a usable backup by applying the transaction log, the transaction log will be applied, the committed transactions in the transaction log file will be replayed, the uncommitted transactions will be roolback, the whole process is similar to the recovery process after mysql crash. However, this process is called "prepare" in Xtrabackup. The "prepare" operation ensures the consistency of backup data. Backup data without prepare is not available. You need to do what's called preparation or what's called data recovery. If you want to prepare for a backup, you need to prepare for a backup. If you want to prepare for a backup, you need to prepare for a backup. If you want to prepare for a backup, you need to prepare for a backup. Synchronization of committed transactions written to RedologFile but not yet written to datafile and rollback of changes made by uncommitted transactions without deltas. However, if there is an incremental backup, Xtrabackup merges all deltas to the previous full volume. Then use the last complete data reduction, then, in the process of merging data, the last time the uncommitted transactions may have submitted in the next time the incremental backup, so if there are multiple incremental backup, we prepare to work in, just need to have committed transaction synchronization to the data file, Uncommitted transactions do not need to be rolled back because they may have already been committed in the increment. The uncommitted transactions need to be rolled back when the last increment is prepared. As we know, Xtrabackup can do hot standby for InnoDB and warm standby for MyISam. When it does warm standby for MyISam, it will add read lock on MyISAM table. That is to say, the data in the backup myISAM table should be consistent. Xtrabackup will first backup innDB data. After innoDB data is backed up, xtrabackup will first backup innDB data. When we need to "prepare" the backup data, we only need to manipulate innoDB data. After preparing, innoDB consistency time is the same as that of non-InnoDB data.Copy the codeRestore only full backups, no increments
innobackupex --apply-log --use-memory=100M /backup/2018-08-09_02-39-03/ --apply-log: This option is the same as the --prepare parameter of xtrabackup. Generally, after the backup is complete, the data cannot be used for the recovery operation because the backup data may contain transactions that have not been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file is still handling inconsistent state at this point. -- Apply-log is used to keep data files in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files. -- Use-memory: you can specify the size of memory to use because our demo data was backed up with very little transaction log, so it worked very well. If you are backing up a large amount of data, the backup will take longer, and the volume of transaction log backups may be large. If the server has a certain amount of free memory, we can tell Xtrabackup to use the specified amount of memory to complete the preparation. To speed up the preparation, the following is an example statement. Ok, now that we have a usable, consistent backup of the data, we are ready to actually restore the data, which is as simple as "copying" the available backup back to the data directory of the database. However, copying is not done manually using cp or mv, but innobackupex. Innobackupex --datadir=/var/lib/mysql --copy-back /backup/2018-08-09_02-39-03/ --datadir innobackupex --datadir=/var/lib/mysql --copy-back /backup/2018-08-09_02-39-03/ Mysql > select datadir from my.cnf; select datadir from my.cnf; select datadir from my.cnf Innobackupex will read the configuration in my.cnf of the database when executing a copyback, but if there is no datadir configured in my.cnf, then the --datadir option must exist, and the datadir directory must be empty and cannot contain data. Otherwise, an error will be reported when the command is executed. The --copy-back option corresponds to the directory in which the data is available. Ok, the restoration is done, but wait, we can't start mysql yet, because we copied the data as root, so the owner group of the data files in the data directory is still root, yes, we need to set the owner group of these files to mysql. chown -R mysql: /var/lib/mysql/ If you encounter a startup failure, you can check whether the size of the transaction log file in the data directory is the same as the size specified in the backup database. If the size of the transaction log is automatically increased (e.g. from the default 5M to 50M), In this case, delete the following transaction log files or back them up to another directory and start mysql again.Copy the codeIncremental backup:
Mysql > create table user(id int primary key,name varchar(6),say char(20)); Query OK, Mysql > insert into user (id,name,say) VALUES (1,'vict','hello'),(2,' Chen ','word'); Query OK, 2 rows affected (0.00 SEC) Records: 2 Duplicates: 0 Warnings: 0 incremental backup: Innobackupex-user = root-password =82111 --incremental /backup --incremental-basedir=/backup/2018-08-09_02-39-03/Copy the codeRestore:
[root@chenkaidashuaige ~]# innobackupex --apply-log --redo-only --use-memory=100M /backup/2018-08-09_02-39-03/ Above commands in the command and above is different, the above command a more - redo - the only option, this option indicates that when he worked in preparation (log), only for redo operation, because at the beginning of a backup, some transaction log has been submitted, but has not yet been fully applied to the data file and transaction log has not been submitted, These uncommitted transactions need to be rolled back. If --redo-only is added to prepare, only committed transactions that have not been applied will be redone. If --redo-only is not added to prepare, uncommitted transactions will not be rolled back. Before a full amount to only restore the backup, full amount after the backup without any other backup need to merge, but this moment is different, in addition to the full amount of backup, followed by corresponding incremental backup, then the whole volume of the backup uncommitted transactions may have submitted in the back of the incremental backup, so, in the preparation of "last backup" before "backup", You do not need to roll back. You only need to roll back in the last backup. Integration of incremental and full backups: [root@chenkaidashuaige 2018-08-09_02-39-03]# innobackupex --apply-log /backup/2018-08-09_02-39-03/ --use-memory=100M -- incredo-dir =/backup/2018-08-09_03-38-23/ do not add redo-only to the uncommitted transactions that should be rolled back because it is the last incremental backup Xtrabackup_checkpoints backup file that I change to a full-prepared backup using xtrabackup_checkpoints backup file This backup has already gone through the logging application process (preparation has already been done), and the corresponding maximum LSN number is 61918, which is the end LSN of the first increment, that is, we have integrated the first increment with the full volume. If the backup that we're integrating is not the last incremental backup and the full backup is of the log-applied type and if there are incremental backups, just integrate them with the consolidated backup, For the last incremental backup, prepare is not prepared for redo-onlyCopy the codeRestore:
So far, all the preparation work has been done, we have merged all the incremental backups into the original first full backup, and now we can restore the database from this full backup only. After completing the preparation work, shut down the mysql service, delete the data file in it, and perform full restore. At /usr/bin/innobackupex line 568.) give permission to chown -r mysql: /var/lib/mysql/Copy the codeData recovery succeededCopy the code
