percona xtrabackup完全备份和恢复

数据运维 2023-07-15 捡田螺的小男孩 手机阅读
Scan me!

不管是mysqldump和lvm-snapshot都依赖于二进制文件，都会产生大量的IO，并不能做增量，我们提到过的xtrabackup则可以，xtrabackup由percona提供，官网：www.percona.com xtrabackup能够实现无阻塞备份（热备），完全备份，备份期间不阻塞事务，能够节约磁盘和网络带宽，自动备份校验（一致性恢复），很快的还原速度（提升在线时间），其他可参考：https://www.percona.com/software/mysql-database/percona-xtrabackup/feature-comparison

下载软件包：

wget https://www.percona.com/downloads/percona-toolkit/2.2.4/RPM/percona-toolkit-2.2.4-1.noarch.rpm
wget https://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.8/RPM/rhel6/x86_64/percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm

为了让他自动解决依赖关系，localinstall即可，yum -y localinstall *.rpm

[root@mysql-master local]# rpm -ql percona-xtrabackup
/usr/bin/innobackupex
/usr/bin/innobackupex-1.5.1
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/bin/xtrabackup_55
/usr/bin/xtrabackup_56
/usr/share/doc/percona-xtrabackup-2.1.8
/usr/share/doc/percona-xtrabackup-2.1.8/COPYING
[root@mysql-master local]#

修改配置文件vim /etc/my.cnf

log-bin=/mydata/binlogs/master-bin
datadir /data/mysql/
innodb_file_per_table = ON

mkdir /mydata/binlogs/chown -R mysql.mysql /mydata/binlogs

授权备份用户mysql> CREATE USER ’linuxea’@’localhost’ IDENTIFIED BY ’123’;收回mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’linuxea’;重新给权限mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’linuxea’@’localhost’;mysql> FLUSH PRIVILEGES;

查看

MariaDB [(none)]> show binary logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       245 |
+-------------------+-----------+
1 row in set (0.00 sec)
 
MariaDB [(none)]>

创建备份目录

mkdir /mybackups

导入表：如果表是Myisam则需要修改替换

:%s/ENGINE=MyISAM/ENGINE=InnoDB/g

使用show table status from hellodbG查看是否为innodb，如果不是则需要修改

MariaDB [hellodb]> show table status  from hellodbG
*************************** 1. row ***************************
           Name: classes
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 8
 Avg_row_length: 2048
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 8388608
 Auto_increment: 9
    Create_time: 2016-01-25 01:19:32
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ********************

1.导入导入前关闭二进制，不导入导入数据的操作MariaDB [(none)]> set sql_log_bin=0;Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> source /tmp/hellodb.sqlQuery OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show databases;
Database
information_schema
hellodb
linuxea
mysql
performance_schema
test

6 rows in set (0.00 sec)导入完成打开二进制日志打开二进制文件MariaDB [hellodb]> set sql_log_bin=1;Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> 导入完成！

1、完全备份需要指定账户和密码，并且需要mysql服务器是运行状态，同时给用户最小授权

innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

如下：如果配置文件中没有定义datadir则需要指定--defaults-file=/etc/my.cnf 即可`[root@mysql-master mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root /mybackups/

[root@mysql-master local]# innobackupex --user=root /mybackups/截取一段如下

innobackupex: Backing up files '/data/mysql/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (73 files)
>> log scanned up to (1744653)
innobackupex: Backing up files '/data/mysql/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up file '/data/mysql/hellodb/courses.frm'
innobackupex: Backing up file '/data/mysql/hellodb/coc.frm'
innobackupex: Backing up file '/data/mysql/hellodb/scores.frm'
innobackupex: Backing up file '/data/mysql/hellodb/students.frm'
innobackupex: Backing up file '/data/mysql/hellodb/db.opt'
innobackupex: Backing up file '/data/mysql/hellodb/teachers.frm'
innobackupex: Backing up file '/data/mysql/hellodb/toc.frm'
innobackupex: Backing up file '/data/mysql/hellodb/classes.frm'
innobackupex: Backing up file '/data/mysql/linuxea/nettb1.frm'
innobackupex: Backing up file '/data/mysql/linuxea/db.opt'
160125 01:22:18  innobackupex: Finished backing up non-InnoDB tables and files
 
160125 01:22:18  innobackupex: Waiting for log copying to finish
 
xtrabackup: The latest check point (for incremental): '1744653'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1744653)
 
xtrabackup: Creating suspend file '/mybackups/2016-01-25_01-22-16/xtrabackup_log_copied' with pid '25158'
xtrabackup: Transaction log of lsn (1744653) to (1744653) was copied.
160125 01:22:19  innobackupex: All tables unlocked
 
innobackupex: Backup created in directory '/mybackups/2016-01-25_01-22-16'
innobackupex: MySQL binlog position: filename 'master-bin.000001', position 8012
160125 01:22:19  innobackupex: Connection to database server closed
160125 01:22:19  innobackupex: completed OK!

`[root@mysql-master 2016-01-25_01-22-16]# lltotal 18472-rw-r--r--. 1 root root 260 Jan 25 01:22 backup-my.cnfdrwxr-xr-x. 2 root root 4096 Jan 25 01:22 hellodb-rw-r-----. 1 root root 18874368 Jan 25 01:22 ibdata1drwxr-xr-x. 2 root root 4096 Jan 25 01:22 linuxeadrwxr-xr-x. 2 root root 4096 Jan 25 01:22 mysqldrwxr-xr-x. 2 root root 4096 Jan 25 01:22 performance_schemadrwxr-xr-x. 2 root root 4096 Jan 25 01:22 test-rw-r--r--. 1 root root 13 Jan 25 01:22 xtrabackup_binary-rw-r--r--. 1 root root 25 Jan 25 01:22 xtrabackup_binlog_info-rw-r-----. 1 root root 89 Jan 25 01:22 xtrabackup_checkpoints-rw-r-----. 1 root root 2560 Jan 25 01:22 xtrabackup_logfile[root@mysql-master 2016-01-25_01-22-16]# `

备份的版本，会自动选择[root@mysql-master 2016-01-25_01-22-16]# cat xtrabackup_binary xtrabackup_55

binary logs[root@mysql-master 2016-01-25_01-22-16]# cat xtrabackup_binlog_info master-bin.000001 245

[root@mysql-master 2016-01-25_01-22-16]# cat xtrabackup_checkpoints backup_type = full-backuped 备份类型，full-backuped完全备份from_lsn = 0 开始的单元号to_lsn = 1713909 结束的单元号last_lsn = 1713909 最后一个单元号compact = 0 打包机制

要想使用更多的高级备份机制，innodb每表使用单独表空间，配置文件中加上innodb_file_per_table = ON

在备份的同时，innobackupex还会在备份目录中创建如下文件：(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态,如果备份完不做还原，则需要做一个整理）和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。(2)xtrabackup_binlog_info—— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；(5)backup-my.cnf —— 备份命令用到的配置选项信息；

在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

在一个逻辑层次上，将一个一个数据文件，划分为一个个数据块，这个数据不是文件系统的数据块，而是mysql存储引擎自行管理的数据快，而每个数据块都有一个innodb的逻辑单元号，每一次当逻辑块上的数据发生变化，其单元号自动加1完全备份时，假如是10，则是从1-10都备份增量备份时，则找块大于10的 mysql-xtarbackup.png需要一次数据的提交

[root@mysql-master mybackups]# innobackupex --apply-log /mybackups/2016-01-25_01-42-33/
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 7283, file name /data/binlogs/master-bin.000001
 
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
160125  1:35:15  InnoDB: Starting shutdown...
160125  1:35:19  InnoDB: Shutdown completed; log sequence number 1775628
160125 01:35:19  innobackupex: completed OK!

2、从一个完全备份中恢复数据

  • 注意：恢复不用启动MySQL

innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

提交未提交的数据

[root@mysql-master hellodb]# innobackupex --apply-log /mybackups/2016-01-25_01-29-44/

在进行恢复时，需清空data目录

[root@mysql-master mysql]# rm -rf /data/mysql/

清空后进行恢复

[root@mysql-master mysql]# innobackupex --copy-back /mybackups/2016-01-25_01-29-44/

当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。如：

[root@mysql-master mysql]# chown -R  mysql:mysql  /data/mysql/
[root@mysql-master mysql]# service mysqld start