percona-xtrabackup使用(二)
上一篇简单介绍了percona-xtrabackup的安装,percona-xtrabackup安装(一),这篇主要介绍innobackupex的工作以及实现原理和具体的使用等。
xtrabackup包含两个工具:xtrabackup和innobackupex。
1 xtrabackup只能备份innodb和xtradb两种引擎的表,不能备份myisam引擎的表;
2 innobackupex是一个封装了xtrabackup的Perl脚本,支持同时备份innodb和myisam引擎的表,但在对myisam引擎的表备份时需要加一个全局的读锁。还有就是对于myisam引擎的表不支持增量备份。
innobackupex is a script written in Perl that wraps the xtrabackup and performs the tasks where the performance and efficiency of C program isn’t needed. In this way, it provides a convenient and integrated approach to backing up in many common scenarios.
1.Making a Backup If no mode is specified, innobackupex will assume the backup mode.
2.By default, it starts xtrabackup with the --suspend-at-end option, and lets it copy the InnoDB data files. When xtrabackup finishes that, innobackupex sees it create the xtrabackup_suspended_2 file and executes FLUSH TABLES WITH READ LOCK. Then it begins copying the rest of the files.
3.innobackupex will then check MySQL variables to determine which features are supported by server. Special interest are backup locks, changed page bitmaps, GTID mode, etc. If everything goes well, the binary is started as a child process.
4.innobackupex will wait for slaves in a replication setup if the option --safe-slave-backup is set and will flush all tables with READ LOCK, preventing all MyISAM tables from writing (unless option --no-lock is specified).
Note: Locking is done only for MyISAM and other non-InnoDB tables, and only after Percona XtraBackup is finished backing up all InnoDB/XtraDB data and logs.
5.Once this is done, the backup of the files will begin. It will backup .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM,.ARZ, .CSM, .CSV, .par, and .opt files.
6.When all the files are backed up, it resumes ibbackup and wait until it finishes copying the transactions done while the backup was done. Then, the tables are unlocked, the slave is started (if the option --safe-slave-backup was used) and the connection with the server is closed. Then, it removes the xtrabackup_suspended_2 file and permits xtrabackup to exit.
上述步骤完成之后,会在目标目录生产以下文件
xtrabackup_checkpoints containing the LSN and the type of backup;
xtrabackup_binlog_info containing the position of the binary log at the moment of backing up;
xtrabackup_binlog_pos_innodb containing the position of the binary log at the moment of backing up relative to InnoDB transactions;
xtrabackup_slave_info containing the MySQL binlog position of the master server in a replication setup via SHOW SLAVE STATUS if the --slave-info option is passed;
backup-my.cnf containing only the my.cnf options required for the backup. For example, innodb_data_file_path, innodb_log_files_in_group, innodb_log_file_size, innodb_fast_checksum, innodb_page_size, innodb_log_block_size;
xtrabackup_binary containing the binary used for the backup;
mysql-stderr containing the STDERR of mysqld during the process and
mysql-stdout containing the STDOUT of the server.
Note that the STDERR of innobackupex is not written in any file. You will have to redirect it to a file, e.g., innobackupex OPTIONS 2> backupout.log.
innobackupex备份时候会读取my.cnf中的配置选项datadir, innodb_data_home_dir, innodb_data_file_path,innodb_log_group_home_dir
一、使用xtrabackup进行备份
创建备份用户
mysql>CREATEUSER'bkpuser'@'localhost'IDENTIFIEDBY's3cret';QueryOK,0rowsaffected(0.49sec)mysql>GRANTRELOAD,LOCKTABLES,REPLICATIONCLIENT,PROCESSON*.*TO'bkpuser'@'localhost';QueryOK,0rowsaffected(0.00sec)mysql>FLUSHPRIVILEGES;QueryOK,0rowsaffected(0.35sec)
1.1备份类型 (全备)
Creating a Backup
[root@ora12cbackup]#xtrabackup--user=bkpuser--password=s3cret--socket=/tmp/mysql.sock--backup--target-dir=/oracle/app/backup/base......xtrabackup:Transactionlogoflsn(2536404)to(2536413)wascopied.17072617:11:44completedOK![root@ora12cbackup]#ls-lbase/total12340-rw-r-----1rootroot426Jul2617:11backup-my.cnf-rw-r-----1rootroot313Jul2617:11ib_buffer_pool-rw-r-----1rootroot12582912Jul2617:11ibdata1drwxr-x---2rootroot4096Jul2617:11mysqldrwxr-x---2rootroot4096Jul2617:11performance_schemadrwxr-x---2rootroot12288Jul2617:11sysdrwxr-x---2rootroot4096Jul2617:11testdrwxr-x---2rootroot4096Jul2617:11xtra-rw-r-----1rootroot21Jul2617:11xtrabackup_binlog_info-rw-r-----1rootroot113Jul2617:11xtrabackup_checkpoints-rw-r-----1rootroot526Jul2617:11xtrabackup_info-rw-r-----1rootroot2560Jul2617:11xtrabackup_logfile
1.2准备备份
Preparing the backup
这个过程就是使得备份达到一致性。类似oracle的recover过程,但是根据prepare时使用参数的不同有所不同,--apply-log 和--apply-log-only
After you make a backup with --backup, the next step is to prepare it. The data files are not point-in-time consistent until they’ve been prepared, because they were copied at different times as the program ran, and they might have been changed while this was happening. If you try to start InnoDB with these data files, it will detect corruption and crash itself to prevent you from running on damaged data. The --prepare step makes the files perfectly consistent at a
single instant in time, so you can run InnoDB on them.
[root@ora12cbackup]#xtrabackup--user=bkpuser--password=s3cret--socket=/tmp/mysql.sock--prepare--target-dir=/oracle/app/backup/base......InnoDB:Shutdowncompleted;logsequencenumber253648817072617:31:13completedOK!
上述步骤执行完之后,备份就是一致的,可以用来进行恢复。如果应用的基础是增量备份,应该使用-apply-log-only参数
1.3恢复备份
Restoring a Backup
拷贝备份到目标数据目录(使用cp或者rsync)
如:rsync -avrP /data/backup/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
注意;xtrabackup备份的只是InnoDB 数据.
详细操作步骤如下所示;
[root@ora12cbackup]#xtrabackup--user=bkpuser--password=s3cret--socket=/tmp/mysql.sock--backup--target-dir=/oracle/app/backup/base[root@ora12cbackup]#xtrabackup--user=bkpuser--password=s3cret--socket=/tmp/mysql.sock--prepare--target-dir=/oracle/app/backup/basemysql>showvariableslike'datadir';+---------------+-----------------------------+|Variable_name|Value|+---------------+-----------------------------+|datadir|/oracle/app/mysql-5.7/data/|+---------------+-----------------------------+1rowinset(0.12sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||sys||test||xtra|+--------------------+6rowsinset(0.16sec)[root@ora12cbackup]#servicemysqldstopShuttingdownMySQL....[OK][root@ora12cbackup]#mv/oracle/app/mysql-5.7/data//oracle/app/mysql-5.7/data-bak[root@ora12cbackup]#cp-r/oracle/app/backup/base/oracle/app/mysql-5.7/data/[root@ora12cbackup]#chown-Rmysql.mysql/oracle/app/mysql-5.7/data[root@ora12cbackup]#servicemysqldstartStartingMySQL..[OK]mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||sys||test||xtra|+--------------------+6rowsinset(0.00sec)
二、其他类型备份
增量备份
xtrabackup 和innobackupex都支持增量备份,意味着可以备份自从上次全备之后的改变的数据。因此可以设置每周一全备,其余天增量备份,或者每天全备,每小时增量备份。
之所以可以进行增量备份,原理如下:
Incremental backups work because each InnoDB page (usually 16kb in size) contains a log sequence number, or LSN.The LSN is the system version number for the entire database. Each page’s LSN shows how recently it was changed.An incremental backup copies each page whose LSN is newer than the previous incremental or full backup’s LSN.There are two algorithms in use to find the set of such pages to be copied. The first one, available with all the server types and versions, is to check the page LSN directly by reading all the data pages. The second one, available with Percona Server, is to enable the changed page tracking feature on the server, which will note the pages as they are being changed. This information will be then written out in a compact separate so-called bitmap file. The xtrabackup binary will use that file to read only the data pages it needs for the incremental backup, potentially saving many read
requests. The latter algorithm is enabled by default if the xtrabackup binary finds the bitmap file. It is possible to specify --incremental-force-scan to read all the pages even if the bitmap data is available.
2.1创建增量备份
增量备份的创建是基于一个全备的,xtrabackup会在备份目录创建一个xtrabackup_checkpoints文件,包含了to_lsn(数据库备份结束时的LSN)。
全备[root@ora12cbackup]#xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock--backup--target-dir=/oracle/app/backup/base......xtrabackup:Transactionlogoflsn(2536507)to(2536516)wascopied.17072710:27:45completedOK![root@ora12cbackup]#morebase/xtrabackup_checkpointsbackup_type=full-backupedfrom_lsn=0to_lsn=2536507last_lsn=2536516compact=0recover_binlog_info=0创建测试数据库、表mysql>createdatabasetest_incrementdefaultcharactersetutf8;QueryOK,1rowaffected(0.36sec)mysql>createtabletest(idint(11)NOTNULLAUTO_INCREMENT,emailvarchar(100)DEFAULTNULL,namevarchar(10)DEFAULTNULL,PRIMARYKEY(id));QueryOK,0rowsaffected(0.10sec)mysql>insertintotest(email,name)values('123@gmail.com','a');QueryOK,1rowaffected(0.01sec)mysql>insertintotest(email,name)values('234@gmail.com','b');QueryOK,1rowaffected(0.67sec)mysql>select*fromtest;+----+---------------+------+|id|email|name|+----+---------------+------+|1|123@gmail.com|a||2|234@gmail.com|b|+----+---------------+------+2rowsinset(0.00sec)增量备份一[root@ora12cbackup]#xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock--backup--target-dir=/oracle/app/backup/incr1--incremental-basedir=/oracle/app/backup/basextrabackup:Transactionlogoflsn(2555122)to(2555131)wascopied.17072710:41:06completedOK!/oracle/app/backup/incr1包含deltafiles,类似ibdata1.delta和test/table1.ibd.delta,这些代表从上次LSN之后的变化[root@ora12cbackup]#moreincr1/xtrabackup_checkpointsbackup_type=incrementalfrom_lsn=2536507to_lsn=2555122last_lsn=2555131compact=0recover_binlog_info=0表更新mysql>insertintotest(email,name)values('345@gmail.com','c');QueryOK,1rowaffected(0.35sec)mysql>insertintotest(email,name)values('456@gmail.com','d');QueryOK,1rowaffected(0.02sec)mysql>select*fromtest;+----+---------------+------+|id|email|name|+----+---------------+------+|1|123@gmail.com|a||2|234@gmail.com|b||3|345@gmail.com|c||4|456@gmail.com|d|+----+---------------+------+4rowsinset(0.00sec)mysql>deletefromtestwhereid=1;^[[AQueryOK,1rowaffected(0.42sec)mysql>select*fromtest;+----+---------------+------+|id|email|name|+----+---------------+------+|2|234@gmail.com|b||3|345@gmail.com|c||4|456@gmail.com|d|+----+---------------+------+3rowsinset(0.00sec)增量备份二[root@ora12cbackup]#xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock--backup--target-dir=/oracle/app/backup/incr2--incremental-basedir=/oracle/app/backup/incr1.....xtrabackup:Transactionlogoflsn(2560224)to(2560233)wascopied.17072710:55:09completedOK!
2.2准备增量备份
Preparing the Incremental Backups
The --prepare step for incremental backups is not the same as for normal
backups. In normal backups, two types of operations are performed to make the database consistent: committed transactions are replayed from the log file against the data files, and uncommitted transactions are rolled back. You must skip the rollback of uncommitted transactions when preparing a backup, because transactions that were uncommitted
at the time of your backup may be in progress, and it’s likely that they will be committed in the next incremental backup. You should use the --apply-log-only option to prevent the rollback phase.
对于增量备份,在prepare的时候,使用--apply-log-only参数阻止事务回滚阶段。
[root@ora12cbackup]#xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock--prepare--apply-log-only--target-dir=/oracle/app/backup/base......InnoDB:Shutdowncompleted;logsequencenumber2536525InnoDB:Numberofpools:117072711:00:47completedOK!应用第一次增量备份至全备上[root@ora12cbackup]#xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock--prepare--apply-log-only--target-dir=/oracle/app/backup/base--incremental-dir=/oracle/app/backup/incr1......InnoDB:AllocatedtablespaceID21forsys/sys_config,oldmaximumwas0xtrabackup:pagesizefor/oracle/app/backup/incr1//ibdata1.deltais16384bytesApplying/oracle/app/backup/incr1//ibdata1.deltato./ibdata1...xtrabackup:pagesizefor/oracle/app/backup/incr1//sys/sys_config.ibd.deltais16384bytes......17072711:10:33completedOK![root@ora12cbackup]#ls-lbase/total20536-rw-r-----1rootroot426Jul2710:27backup-my.cnf-rw-r-----1rootroot313Jul2710:27ib_buffer_pool-rw-r-----1rootroot12582912Jul2711:10ibdata1drwxr-x---2rootroot4096Jul2711:10mysqldrwxr-x---2rootroot4096Jul2711:10performance_schemadrwxr-x---2rootroot12288Jul2711:10sysdrwxr-x---2rootroot4096Jul2711:10testdrwxr-x---2rootroot4096Jul2711:10test_incrementdrwxr-x---2rootroot4096Jul2711:10xtra-rw-r-----1rootroot22Jul2711:10xtrabackup_binlog_info-rw-r--r--1rootroot22Jul2711:10xtrabackup_binlog_pos_innodb-rw-r-----1rootroot111Jul2711:10xtrabackup_checkpoints-rw-r-----1rootroot562Jul2711:10xtrabackup_info-rw-r-----1rootroot8388608Jul2711:00xtrabackup_logfile可以看到全备目录已经有了第一次全备之后创建的数据库test_increment。如果此时从/oracle/app/backup/base恢复备份,你应该可以看到第一次增量备份之后的数据库状态。应用第二次增量备份至全备上[root@ora12cbackup]#xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock--prepare--target-dir=/oracle/app/backup/base--incremental-dir=/oracle/app/backup/incr2
Note: --apply-log-only should be used when merging all incrementals except the last one. That’s why the previous line doesn’t contain the --apply-log-only option. Even if the --apply-log-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.
2.3恢复备份
[root@ora12cbackup]#servicemysqldstopShuttingdownMySQL.....[OK][root@ora12cbackup]#mv/oracle/app/mysql-5.7/data/oracle/app/mysql-5.7/data-bak[root@ora12cbackup]#cp-r/oracle/app/backup/base/oracle/app/mysql-5.7/data[root@ora12cbackup]#chown-Rmysql.mysql/oracle/app/mysql-5.7/data[root@ora12cbackup]#servicemysqldstartStartingMySQL...[OK]mysql>select*fromtest;ERROR2006(HY000):MySQLserverhasgoneawayNoconnection.Tryingtoreconnect...Connectionid:2Currentdatabase:test_increment+----+---------------+------+|id|email|name|+----+---------------+------+|2|234@gmail.com|b||3|345@gmail.com|c||4|456@gmail.com|d|+----+---------------+------+3rowsinset(0.45sec)
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。