基于xtrabackup实现数据备份还原——高效的数据备份还原工具

准备一个干净的备份目录；

[root@mysql ~]$ll /backups/total 0MariaDB [(none)]> show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |     30331 || mysql-bin.000002 |   1038814 || mysql-bin.000003 |      7698 || mysql-bin.000004 |       442 || mysql-bin.000005 |       423 |+------------------+-----------+5 rows in set (0.00 sec)

数据备份：

默认就是以root用户的身份进行的备份；[root@mysql ~]$innobackupex --user=root /backups/[root@mysql ~]$ll /backups/total 0drwxr-x--- 6 root root 217 Feb 25 14:14 2018-02-25_14-14-07[root@mysql ~]$ll /backups/2018-02-25_14-14-07/total 18460-rw-r----- 1 root root      417 Feb 25 14:14 backup-my.cnfdrwxr-x--- 2 root root      272 Feb 25 14:14 hellodb-rw-r----- 1 root root 18874368 Feb 25 14:14 ibdata1drwxr-x--- 2 root root     4096 Feb 25 14:14 mysqldrwxr-x--- 2 root root     4096 Feb 25 14:14 performance_schemadrwxr-x--- 2 root root       20 Feb 25 14:14 test-rw-r----- 1 root root       21 Feb 25 14:14 xtrabackup_binlog_info-rw-r----- 1 root root      113 Feb 25 14:14 xtrabackup_checkpoints-rw-r----- 1 root root      454 Feb 25 14:14 xtrabackup_info-rw-r----- 1 root root     2560 Feb 25 14:14 xtrabackup_logfile[root@mysql ~]$cd /backups/2018-02-25_14-14-07/[root@mysql 2018-02-25_14-14-07]$lsbackup-my.cnf  ibdata1  performance_schema  xtrabackup_binlog_info  xtrabackup_infohellodb        mysql    test                xtrabackup_checkpoints  xtrabackup_logfile[root@mysql 2018-02-25_14-14-07]$# lsn是日志序列号，在磁盘上保存了数据库的所有数据；文件很大，分成很多的小块存储在了磁盘上；每个数据块的小块都有所谓的lsn号；如从100-200，如果将100这个数据块中的数据做了修改，那么他的LSN会加1，即变为了201；所以根据LSN的大小，可以判断数据块中的数据是否备份过；这里是全备份，所以就是将所有的LSN对应的数据块的数据都进行了备份。    [root@mysql 2018-02-25_14-14-07]$less xtrabackup_checkpoints     backup_type = full-backuped    from_lsn = 0    to_lsn = 1640915    last_lsn = 1640915    compact = 0    recover_binlog_info = 0尽管表面来看是拷贝的文件，但是底层实际拷贝的是数据块，所有效率很高。这个文件中显示的是全备份备份到二进制文件的哪个位置；[root@mysql 2018-02-25_14-14-07]$cat xtrabackup_binlog_info mysql-bin.000005    423[root@mysql 2018-02-25_14-14-07]$file xtrabackup_logfile xtrabackup_logfile: data

还原数据到一个新的MySQL主机上

找一个干净的主机，将在27.7主机上备份的数据在27.17上实现还原；[root@mysql17 ~]$vim /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log_bininnodb_file_per_table[root@mysql17 ~]$yum install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm [root@mysql backups]$scp -rp /backups/2018-02-25_14-14-07/ 192.168.27.17:/app/[root@mysql17 ~]$ls /app/2018-02-25_14-14-07

数据库的整理操作：
包括将不完整的事务进行回滚；因为备份的时间点极有可能被一个事务横跨；

[root@mysql17 ~]$innobackupex --apply-log /app/2018-02-25_14-14-07/180225 01:53:05 completed OK!

确保要恢复的数据库的数据目录是空的；[root@mysql17 ~]$ll /var/lib/mysql/total 0将整理过数据复制到数据库的数据目录；这个过程就是复制数据，对于innodb引擎，他是基于块的方式实现的；对于myISAM引擎，那么就是单个文件的复制；[root@mysql17 ~]$innobackupex --copy-back /app/2018-02-25_14-14-07/...180225 01:56:10 completed OK![root@mysql17 ~]$ll /var/lib/mysql/total 40976drwxr-x--- 2 root root      272 Feb 25 01:56 hellodb-rw-r----- 1 root root 18874368 Feb 25 01:56 ibdata1-rw-r----- 1 root root  5242880 Feb 25 01:56 ib_logfile0-rw-r----- 1 root root  5242880 Feb 25 01:56 ib_logfile1-rw-r----- 1 root root 12582912 Feb 25 01:56 ibtmp1drwxr-x--- 2 root root     4096 Feb 25 01:56 mysqldrwxr-x--- 2 root root     4096 Feb 25 01:56 performance_schemadrwxr-x--- 2 root root       20 Feb 25 01:56 test-rw-r----- 1 root root       35 Feb 25 01:56 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root      454 Feb 25 01:56 xtrabackup_info[root@mysql17 ~]$chown -R mysql.mysql /var/lib/mysql/[root@mysql17 ~]$ll /var/lib/mysql/total 40976drwxr-x--- 2 mysql mysql      272 Feb 25 01:56 hellodb-rw-r----- 1 mysql mysql 18874368 Feb 25 01:56 ibdata1-rw-r----- 1 mysql mysql  5242880 Feb 25 01:56 ib_logfile0-rw-r----- 1 mysql mysql  5242880 Feb 25 01:56 ib_logfile1-rw-r----- 1 mysql mysql 12582912 Feb 25 01:56 ibtmp1drwxr-x--- 2 mysql mysql     4096 Feb 25 01:56 mysqldrwxr-x--- 2 mysql mysql     4096 Feb 25 01:56 performance_schemadrwxr-x--- 2 mysql mysql       20 Feb 25 01:56 test-rw-r----- 1 mysql mysql       35 Feb 25 01:56 xtrabackup_binlog_pos_innodb-rw-r----- 1 mysql mysql      454 Feb 25 01:56 xtrabackup_info[root@mysql17 ~]$ll /var/lib/mysql/ -ddrwxr-xr-x 6 mysql mysql 198 Feb 25 01:56 /var/lib/mysql/

[root@mysql17 ~]$systemctl start mariadbMariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || hellodb            || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)MariaDB [(none)]> MariaDB [(none)]> show master logs;+--------------------+-----------+| Log_name           | File_size |+--------------------+-----------+| mariadb-bin.000001 |       245 |+--------------------+-----------+1 row in set (0.00 sec)MariaDB [(none)]> select * from hellodb.students;Empty set (0.00 sec)以上就是使用xtrabackup实现将一个主机的数据全备份后还原到一个远程的新的主机的过程。