一、数据完全备份与恢复
步骤：
- 安装percona软件包
- 备份所有数据到/allbak目录下
- 搭建新的数据库服务器，使用备份文件恢复数据
- 验证数据恢复
步骤一：安装XtraBackup软件包
1）安装软件
1 [root@host50 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm 2 [root@host50 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm 3 警告：percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm: 头V4 DSA/SHA1 Signature, 密钥 ID cd2efd2a: NOKEY 4 准备中... ################################# [100%] 5 正在升级/安装... 6 1:percona-xtrabackup-24-2.4.6-2.el7################################# [ 33%] 7 2:percona-xtrabackup-test-24-2.4.6-################################# [ 67%] 8 3:percona-xtrabackup-24-debuginfo-2################################# [100%]
2）确认安装的主要程序/脚本
1 [root@host50 ~]# rpm -qa | grep -i percona 2 percona-xtrabackup-24-2.4.7-1.el7.x86_64 3 [root@host50 ~]# rpm -ql percona-xtrabackup-24 4 /usr/bin/innobackupex 5 /usr/bin/xbcloud 6 /usr/bin/xbcloud_osenv 7 /usr/bin/xbcrypt 8 /usr/bin/xbstream 9 /usr/bin/xtrabackup 10 /usr/share/doc/percona-xtrabackup-24-2.4.7 11 /usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING 12 /usr/share/man/man1/innobackupex.1.gz 13 /usr/share/man/man1/xbcrypt.1.gz 14 /usr/share/man/man1/xbstream.1.gz 15 /usr/share/man/man1/xtrabackup.1.gz 16 [root@host50 ~]# 17 [root@host50 ~]# innobackupex --help //查看简单帮助 18 [root@host50 ~]# 19 [root@host50 ~]# man innobackupex //查看详细帮助 20 /usr/share/man/man1/xtrabackup.1.gz
步骤二：备份所有数据到/allbak目录下
1）备份所有数据
1 [root@host50 ~]# innobackupex --user root --password 123456 /allbak --no-timestamp //执行备份命令 2 170425 11:05:44 innobackupex: Starting the backup operation 3 IMPORTANT: Please check that the backup run completes successfully. 4 At the end of a successful backup run innobackupex 5 prints "completed OK!". 6 Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374. 7 170425 11:05:45 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set 8 Using server version 5.7.17 9 innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7) 10 xtrabackup: uses posix_fadvise(). 11 xtrabackup: cd to /var/lib/mysql 12 xtrabackup: open files limit requested 0, set to 1024 13 xtrabackup: using the following InnoDB configuration: 14 xtrabackup: innodb_data_home_dir = . 15 xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend 16 xtrabackup: innodb_log_group_home_dir = ./ 17 xtrabackup: innodb_log_files_in_group = 2 18 xtrabackup: innodb_log_file_size = 50331648 19 InnoDB: Number of pools: 1 20 170425 11:05:45 >> log scanned up to (2543893) 21 xtrabackup: Generating a list of tablespaces 22 InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0 23 170425 11:05:45 [01] Copying ./ibdata1 to /backup/ibdata1 24 170425 11:05:45 [01] ...done 25 170425 11:05:46 [01] Copying ./mysql/plugin.ibd to /backup/mysql/plugin.ibd 26 170425 11:05:46 [01] ...done 27 170425 11:05:46 [01] Copying ./mysql/servers.ibd to /backup/mysql/servers.ibd 28 170425 11:05:46 [01] ...done 29 170425 11:05:46 [01] Copying ./mysql/help_topic.ibd to /backup/mysql/help_topic.ibd 30 170425 11:05:46 [01] ...done 31 170425 11:05:46 >> log scanned up to (2543893) 32 .. .. 33 170425 11:06:00 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /backup/sys/x@0024waits_global_by_latency.frm 34 170425 11:06:00 [01] ...done 35 170425 11:06:00 [01] Copying ./sys/session_ssl_status.frm to /backup/sys/session_ssl_status.frm 36 170425 11:06:00 [01] ...done 37 170425 11:06:00 [01] Copying ./db1/db.opt to /backup/db1/db.opt 38 170425 11:06:00 [01] ...done 39 170425 11:06:00 [01] Copying ./db1/tb1.frm to /backup/db1/tb1.frm 40 170425 11:06:00 [01] ...done 41 170425 11:06:00 Finished backing up non-InnoDB tables and files 42 170425 11:06:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... 43 xtrabackup: The latest check point (for incremental): '2543884' 44 xtrabackup: Stopping log copying thread. 45 .170425 11:06:00 >> log scanned up to (2543893) 46 170425 11:06:00 Executing UNLOCK TABLES 47 170425 11:06:00 All tables unlocked 48 170425 11:06:00 [00] Copying ib_buffer_pool to /backup/ib_buffer_pool 49 170425 11:06:00 [00] ...done 50 170425 11:06:00 Backup created in directory '/backup/' 51 170425 11:06:00 [00] Writing backup-my.cnf 52 170425 11:06:00 [00] ...done 53 170425 11:06:00 [00] Writing xtrabackup_info 54 170425 11:06:00 [00] ...done 55 xtrabackup: Transaction log of lsn (2543884) to (2543893) was copied. 56 170425 11:06:01 completed OK
2) 确认备份好的文件数据：
1 [root@host50 ~]# ls /allbak 2 backup-my.cnf ib_buffer_pool mysql sys xtrabackup_info 3 db1 ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile
3）把备份文件传递给 目标服务器51
1 [root@host50 ~]# scp -r /allbak root@192.168.4.51:/root/
步骤三：在51主机，使用备份文件恢复数据
1）安装软件包，提供恢复命令
1 [root@host51 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm 2 [root@host51 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
2）恢复数据
1 [root@host51 ~]# systemctl stop mysqld 2 [root@host51 ~]# ls /var/lib/mysql 3 [root@host51 ~]# rm -rf /var/lib/mysql/* //清空数据 4 [root@host51 ~]#innobackupex--apply-log --redo-only /root/allbak //恢复数据 5 170425 11:42:19 innobackupex: Starting the apply-log operation 6 IMPORTANT: Please check that the apply-log run completes successfully. 7 At the end of a successful apply-log run innobackupex 8 prints "completed OK!". 9 innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7) 10 xtrabackup: cd to /backup/ 11 xtrabackup: This target seems to be already prepared. 12 InnoDB: Number of pools: 1 13 xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'. 14 xtrabackup: using the following InnoDB configuration for recovery: 15 xtrabackup: innodb_data_home_dir = . 16 xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend 17 xtrabackup: innodb_log_group_home_dir = . 18 xtrabackup: innodb_log_files_in_group = 2 19 xtrabackup: innodb_log_file_size = 50331648 20 xtrabackup: using the following InnoDB configuration for recovery: 21 xtrabackup: innodb_data_home_dir = . 22 xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend 23 xtrabackup: innodb_log_group_home_dir = . 24 xtrabackup: innodb_log_files_in_group = 2 25 xtrabackup: innodb_log_file_size = 50331648 26 xtrabackup: Starting InnoDB instance for recovery. 27 xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 28 InnoDB: PUNCH HOLE support available 29 InnoDB: Mutexes and rw_locks use GCC atomic builtins 30 InnoDB: Uses event mutexes 31 InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 32 InnoDB: Compressed tables use zlib 1.2.7 33 InnoDB: Number of pools: 1 34 InnoDB: Not using CPU crc32 instructions 35 InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M 36 InnoDB: Completed initialization of buffer pool 37 InnoDB: page_cleaner coordinator priority: -20 38 InnoDB: Highest supported file format is Barracuda. 39 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 40 InnoDB: Starting shutdown... 41 InnoDB: Shutdown completed; log sequence number 2544177 42 InnoDB: Number of pools: 1 43 170425 11:42:20 completed OK! 44 [root@host51 ~]# 45 [root@host51 ~]# innobackupex --copy-back /root/allbak //拷贝数据 46 170425 11:42:55 innobackupex: Starting the apply-log operation 47 IMPORTANT: Please check that the apply-log run completes successfully. 48 At the end of a successful apply-log run innobackupex 49 prints "completed OK!". 50 innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7) 51 incremental backup from 2543884 is enabled. 52 xtrabackup: cd to /backup/ 53 xtrabackup: This target seems to be already prepared with --apply-log-only. 54 InnoDB: Number of pools: 1 55 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2549924) 56 xtrabackup: using the following InnoDB configuration for recovery: 57 xtrabackup: innodb_data_home_dir = . 58 xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend 59 xtrabackup: innodb_log_group_home_dir = /incr01/ 60 xtrabackup: innodb_log_files_in_group = 1 61 xtrabackup: innodb_log_file_size = 8388608 62 xtrabackup: Generating a list of tablespaces 63 InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0 64 xtrabackup: page size for /incr01//ibdata1.delta is 16384 bytes 65 Applying /incr01//ibdata1.delta to ./ibdata1... 66 ... ... 67 170425 11:43:09 [01] Copying /incr01/performance_schema/global_status.frm to ./performance_schema/global_status.frm 68 170425 11:43:09 [01] ...done 69 170425 11:43:09 [01] Copying /incr01/performance_schema/session_status.frm to ./performance_schema/session_status.frm 70 170425 11:43:09 [01] ...done 71 170425 11:43:09 [00] Copying /incr01//xtrabackup_info to ./xtrabackup_info 72 170425 11:43:09 [00] ...done 73 170425 11:43:10 completed OK! 74 [root@host50 ~]# chown -R mysql:mysql /var/lib/mysql //修改所有者与组
步骤四：验证数据恢复
1）启动服务
1 [root@host51 ~]# systemctl start mysqld 2 [root@host51 ~]# mysql -uroot -p123456 3 mysql> show databases; 4 mysql> select * from db3.user2; 5 mysql>select count(*) from db3.user;
2）查看数据
1 [root@host51 ~]# mysql -uroot -p123456 2 mysql> show databases; 3 mysql> select * from db3.user2; 4 mysql> select count(*) from db3.user;
二、恢复单张表
步骤：
执行删除数据命令
使用备份目录/allbak 恢复表数据
验证数据恢复
步骤一：安装XtraBackup软件包
1）执行删除数据命令
1 [root@host50 ~]# mysql –uroot -p123456 2 mysql> delete from db3.user2; //误删除数据操作
2) 删除表空间
1 mysql> alter table db3.user2 discard tablespace;
3) 导出表信息
1 [root@host50 ~ ]# innobackupex --apply-log --export /allbak
4) 拷贝表信息文件到数据库目录下
1 [root@host50 ~]# cp /allbak/db3/user2.{cfg,exp,ibd} /var/lib/mysql/db3/
5) 修改表信息文件的所有者及组用户为mysql
1 [root@host50 ~]# chown mysql:mysql /var/lib/mysql/db3/user2.*
6) 导入表空间
1 mysql> alter table db3.user2 import tablespace;
7) 删除数据库目录下的表信息文件
1 [root@host50 ~]# rm -rf /var/lib/mysql/db3/user2.cfg 2 [root@host50 ~]# rm -rf /var/lib/mysql/db3/user2.exp
8) 查看表记录
1 mysql> select * from db3.user2;
三、增量备份与恢复
步骤：
备份所有数据
备份新产生的数据
删除数据
使用备份文件恢复数据
步骤一：备份所有数据,在50主机执行
1）完全备份 （备份所有数据到/fullbak目录）
1 [root@host50 ~]# innobackupex --user root --password 123456 /fullbak --no-timestamp
步骤二：增量备份 （每次执行备份，值备份新数据,在50主机执行）
1) 插入新记录，并做增量备份
1 mysql> insert into db3.user2 values(5,"jack");// 插入新记录,多写几条 2 [root@host50 ~]# innobackupex --user root --password 123456 --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp //第1次增量备份 ，数据存储目录/new1dir
2) 插入新记录，并做增量备份
1 mysql> insert into db3.user2 values(6,"jack");// 插入新记录,多写几条 2 [root@host50 ~]# innobackupex --user root --password 123456 --incremental /new2dir --incremental-basedir=/newdir1 --no-timestamp //第2次增量备份 ，数据存储目录/new2dir
3) 把备份文件拷贝给目标主机51
1 [root@host50 ~]# scp -r /fullbak root@192.168.4.51:/root/ 2 [root@host50 ~]# scp -r /new1dir/ root@192.168.4.51:/root/ 3 [root@host50 ~]# scp -r /new2dir/ root@192.168.4.51:/root/
步骤三：在主机51 恢复数据
1) 停止服务，并清空数据
1 [root@host51 ~]# systemctl stop mysqld 2 [root@host51 ~]# rm -rf /var/lib/mysql/*
2) 合并日志
1 [root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak //准备恢复数据 2 [root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new1dir //合并日志 3 [root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new2dir //合并日志 4 [root@host51 ~ ]# rm -rf /root/new2dir //恢复后，可以删除了 5 [root@host51 ~ ]# rm -rf /root/new1dir //恢复后，可以删除了
3) 恢复数据
1 [root@host51 ~ ]# innobackupex --copy-back /root/fullbak //拷贝文件到数据库目录下 2 [root@host51 ~ ]# chown -R mysql:mysql /var/lib/mysql //修改所有者与组用户 3 [root@host51 ~ ]# systemctl start mysqld //启动服务 4 [root@host51 ~ ]# mysql -uroot -p123456 //登录 5 mysql> select count(*) from db3.user; //查看数据
浙公网安备 33010602011771号