Day57
percona软件介绍
MySQL备份工具
常用的MySQL备份工具
物理备份的缺点、
跨平台性差
备份时间长、冗余备份、浪费存储空间
mysqldump备份缺点
效率较低、备份和还原速度慢、锁表。
备份过程中,数据插入和更新操作被阻塞
XtraBackup工具
一款强大的在线热备份工具,备份过程中不锁库表,适合生产环境,由专业组织Percona提供(改进MySQL分支)
主要含两个组件:
xtrabackup:C程序,支持innoDB/XtraDB
innobackupex:以Perl脚本封装xtrabackup,还支持MySAM
安装percona
安装软件包
yum安装自动解决依赖
rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
查看安装信息
查看安装列表
rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex //备份innodb,xtrdb、myisam引擎
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup //备份innodb、xtrdb引擎的表
/usr/share/doc/percona-xtrabackup-24-2.4.7
/usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
查看帮助命令
rpm -ql percona-xtrabackup-24
innobackpex --help //常用选项
man innobackupex //详细帮助
innobackupex命令
常用选项
常用选项 | 含义 |
---|---|
–host | 主机名 |
–user | 用户名 |
–port | 端口号 |
–password | 密码 |
–databases | 数据库名 |
–no-timestamp | 不用日期命名备份文件存储的子目录名 |
–redo-only | 日志合并 |
–apply-log | 准备恢复数据 |
–copy-back | 拷贝数据 |
–incremental 目录名 | 增量备份 |
–incremental-basedir=目录名 | 增量备份时,指定上一次备份数据存储的目录名 |
–incremental-dir=目录名 | 准备恢复数据时,指定增量备份数据存储的目录名 |
–export | 导出表信息 |
import | 导入表空间 |
–databases=“库名” //1个库
–databases=“库1 库2” //多个库
–databases=“库1.表” //1张表
命令格式
完全备份
innobackupex --user 用户名 --password 密码 备份目录名 --no-timestamp
完全恢复
innobackupex --apply-log 目录 //准备恢复数据
innobackupex --copy-back 目录名 //恢复数据
增量备份
innobackupex --user 用户名 --password 密码 --incremental 增量目录 --increment-basedir=目录名 --no-timestamp
增量恢复
innobackupex --apply-log -redo-only 目录名 --incremental-dir=目录名 //准备恢复数据
innobackupex --copy-back 目录名 //恢复数据
innobackupex备份与恢复
完全备份与恢复
完全备份
备份所有数据到/allbak目录下
1)备份所有数据
[root@host50 ~]# innobackupex --user root --password 123456 /allbak --no-timestamp //执行备份命令
170425 11:05:44 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!".
Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
170425 11:05:45 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.17
innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
170425 11:05:45 >> log scanned up to (2543893)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
170425 11:05:45 [01] Copying ./ibdata1 to /backup/ibdata1
170425 11:05:45 [01] ...done
170425 11:05:46 [01] Copying ./mysql/plugin.ibd to /backup/mysql/plugin.ibd
170425 11:05:46 [01] ...done
170425 11:05:46 [01] Copying ./mysql/servers.ibd to /backup/mysql/servers.ibd
170425 11:05:46 [01] ...done
170425 11:05:46 [01] Copying ./mysql/help_topic.ibd to /backup/mysql/help_topic.ibd
170425 11:05:46 [01] ...done
170425 11:05:46 >> log scanned up to (2543893)
.. ..
170425 11:06:00 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /backup/sys/x@0024waits_global_by_latency.frm
170425 11:06:00 [01] ...done
170425 11:06:00 [01] Copying ./sys/session_ssl_status.frm to /backup/sys/session_ssl_status.frm
170425 11:06:00 [01] ...done
170425 11:06:00 [01] Copying ./db1/db.opt to /backup/db1/db.opt
170425 11:06:00 [01] ...done
170425 11:06:00 [01] Copying ./db1/tb1.frm to /backup/db1/tb1.frm
170425 11:06:00 [01] ...done
170425 11:06:00 Finished backing up non-InnoDB tables and files
170425 11:06:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2543884'
xtrabackup: Stopping log copying thread.
.170425 11:06:00 >> log scanned up to (2543893)
170425 11:06:00 Executing UNLOCK TABLES
170425 11:06:00 All tables unlocked
170425 11:06:00 [00] Copying ib_buffer_pool to /backup/ib_buffer_pool
170425 11:06:00 [00] ...done
170425 11:06:00 Backup created in directory '/backup/'
170425 11:06:00 [00] Writing backup-my.cnf
170425 11:06:00 [00] ...done
170425 11:06:00 [00] Writing xtrabackup_info
170425 11:06:00 [00] ...done
xtrabackup: Transaction log of lsn (2543884) to (2543893) was copied.
170425 11:06:01 completed OK
证数据恢复
1)启动服务
[root@host51 ~]# systemctl start mysqld
[root@host51 ~]# mysql -uroot -p123456
mysql> show databases;
mysql> select * from db3.user2;
mysql>select count(*) from db3.user;
2)查看数据
[root@host51 ~]# mysql -uroot -p123456
mysql> show databases;
mysql> select * from db3.user2;
mysql> select count(*) from db3.user;
恢复单张表
操作步骤
具体操作如下:
删除表空间
导出表信息
拷贝表信息到数据库目录下
修改表信息文件的所有者及用户为mysql
导入表空间
删除数据库目录下的表信息文件
查看表记录
相关命令
mysql> alter table 库名.表名 discard tablespace; //删除表空间
案例
1)执行删除数据命令
[root@host50 ~]# mysql –uroot -p123456
mysql> delete from db3.user2; //误删除数据操作
mysql>
- 删除表空间
mysql> alter table db3.user2 discard tablespace;
- 导出表信息
[root@host50 ~ ]# innobackupex --apply-log --export /allbak
- 拷贝表信息文件到数据库目录下
[root@host50 ~]# cp /allbak/db3/user2.{cfg,exp,ibd} /var/lib/mysql/db3/
- 修改表信息文件的所有者及组用户为mysql
[root@host50 ~]# chown mysql:mysql /var/lib/mysql/db3/user2.*
- 导入表空间
mysql> alter table db3.user2 import tablespace;
- 删除数据库目录下的表信息文件
[root@host50 ~]# rm -rf /var/lib/mysql/db3/user2.cfg
[root@host50 ~]# rm -rf /var/lib/mysql/db3/user2.exp
- 查看表记录
mysql> select * from db3.user2;
增量备份与恢复
增量备份
备份所有数据,在50主机执行
1)完全备份 (备份所有数据到/fullbak目录)
[root@host50 ~]# innobackupex --user root --password 123456 /fullbak --no-timestamp
增量备份 (每次执行备份,值备份新数据,在50主机执行)
- 插入新记录,并做增量备份
mysql> insert into db3.user2 values(5,"jack");// 插入新记录,多写几条
[root@host50 ~]# innobackupex --user root --password 123456 --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp //第1次增量备份 ,数据存储目录/new1dir
- 插入新记录,并做增量备份
mysql> insert into db3.user2 values(6,"jack");// 插入新记录,多写几条
[root@host50 ~]# innobackupex --user root --password 123456 --incremental /new2dir --incremental-basedir=/newdir1 --no-timestamp //第2次增量备份 ,数据存储目录/new2dir
- 把备份文件拷贝给目标主机51
[root@host50 ~]# scp -r /fullbak root@192.168.4.51:/root/
[root@host50 ~]# scp -r /new1dir/ root@192.168.4.51:/root/
[root@host50 ~]# scp -r /new2dir/ root@192.168.4.51:/root/
在主机51 恢复数据
- 停止服务,并清空数据
[root@host51 ~]# systemctl stop mysqld
[root@host51 ~]# rm -rf /var/lib/mysql/*
- 合并日志
[root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak //准备恢复数据
[root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new1dir //合并日志
[root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new2dir //合并日志
[root@host51 ~ ]# rm -rf /root/new2dir //恢复后,可以删除了
[root@host51 ~ ]# rm -rf /root/new1dir //恢复后,可以删除了
- 恢复数据
[root@host51 ~ ]# innobackupex --copy-back /root/fullbak //拷贝文件到数据库目录下
[root@host51 ~ ]# chown -R mysql:mysql /var/lib/mysql //修改所有者与组用户
[root@host51 ~ ]# systemctl start mysqld //启动服务
[root@host51 ~ ]# mysql -uroot -p123456 //登录
mysql> select count(*) from db3.user; //查看数据