数据备份&数据迁移方案

数据备份方式

• 物理备份
  • 冷备:cp、tar、…
• 逻辑备份
  • mysqldump //备份
  • mysql //恢复

物理备份及恢复

备份

] cp -r /var/lib/mysql 目录/mysql.bak 
] tar -zcvf /root/mysql.tar.gz /var/lib/mysql/*

恢复

] cp -r 目录/mysql.bak /var/lib/mysql/
] tar -xvf /root/mysql.tar.gz -C /var/lib/mysql/
] chown -R mysql:mysql /var/lib/mysql

逻辑备份

数据备份策略

• 完全备份:备份所有数据源
• 增量备份:备份上次备份后,所有新产生的数据
• 差异备份:备份上次完全备份后,所有新产生的数据

完全备份和恢复

] mysqldump -uroot -p密码 库名 > 目录/文件.sql #备份
] mysqldump -uroot -p密码 -A|--all-databases > 目录/文件.sql #备份所有库
] mysqldump -uroot -p密码 库名 表名 > 目录/文件.sql #备份表
] mysqldump -uroot -p密码 -B 数据库1 数据库2 > 目录/文件.sql #备份多个数据库
] mysql -uroot -p密码 库 < 目录/文件.sql #恢复

增量备份

1.binlog日志文件

1)什么是binlog日志?

• 二进制日志
• MySQL服务日志文件的一种
• 记录除查询之外所有的SQL命令
• 可用于数据备份和恢复
• 配置mysql主从同步的必要条件

2)启用binlog日志

配置项	用途
server_id=数字	指定id值(1-255)
log_bin[=目录/文件]	启用binlog日志
max_binlog_size=数值m	指定日志文件容量,默认1G

] vim /etc/my.cnf
[mysqld]
log_bin
server_id=50   #启用binlog必须指定server_id

3)binlog相关文件
主机名-bin’.index //索引文件
主机名-bin.000001 //第一个二进制日志
主机名-bin.000002 //第二个二进制日志
…
4)手动生成新的日志文件
四个方法:

] systemctl restart mysqld
mysql> flush logs;
] mysql -uroot -p密码 -e 'flush logs'
] mysqldump -uroot -p密码 --flush-logs 库 > 目录/文件

5)清理日志
删除指定编号之前的binlog日志文件:

mysql> purge master logs to "binlog文件名";

删除所有的binlog日志,重建新日志:

mysql> reset master;

6)相关命令

mysql> show master status;	#查看日志信息

2.使用binlog日志文件恢复数据

命令格式

] mysqlbinlog [选项] binlog日志文件   #将文件内容输出到屏幕
] mysqlbinlog [选项] binlog日志文件 | mysql -uroot -ppassword	#恢复数据,不加选项时恢复全部数据
选项:
--start-position=数字	#指定起始偏移量
--stop-position=数字		#指定结束偏移量
--start-datetime="yyyy-mm-dd hh:mm:ss"   #起始时间
--stop-datetime="yyyy-mm-dd hh:mm:ss"	 #结束时间

binlog日志记录格式

• row行模式
• statement报表模式
• mixed混合模式

mysql> show variables like "binlog_format"; #查看日志文件记录格式

vim /etc/my.cnf	   #修改日志文件记录格式,mixed模式可以通过命令查看偏移量和时间
binlog_format="mixed"

使用binlog日志文件恢复指定范围内的数据

查看要恢复的数据所在的偏移量和时间:

mysqlbinlog binlog日志文件 

内容解析:

• at 数字 #数字为偏移量
• commit #代表’回车’,查找结束偏移量或时间需要包括一个commit

恢复数据:

mysqlbinlog 选项 binlog日志文件 | mysql -uroot -ppassword
例:
mysqlbinlog --start-position=300 --stop-position=1185 dzh.000002 | mysql -uroot -p123456

3.percona

常用备份工具的缺点:

• 物理备份缺点:跨平台性差,备份时间长,冗余备份,浪费存储空间
• mysqldump缺点:效率低,备份和还原速度慢,锁表

XtraBackup:

• 在线热备不锁库表,适合生产环境
• 由专业组织Percona提供(改进MySQL分支)
• 主要包括两个组件:xtrabackup(C程序,支持innodb和xtradb),innobackupex(perl脚本封装的xtrabackup,额外支持MyISAM)

yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
which innobackupex

常用选项	含义
- - host	主机名
- - user	用户名
- - port	端口号
- - password	密码
- - databases	数据库名
- -no-timestamp	不用日期命名存储备份文件的子目录名
- -redo-only	日志合并
- -apply-log	准备恢复数据
- -copy-back	拷贝数据
- -incremental 目录名	增量备份
- -incremental-basedir=目录名	增量备份时,指定上一次备份数据存储的目录名
- -incremental-dir=目录名	准备恢复数据时,指定增量备份数据存储的目录名
- -export	导出表信息

--databases="库"
--databases="库1 库2"
--databases="库1.表"

1)innobackupex完全备份和恢复
备份:(备份后数据在指定的目录下)

innobackupex 选项 目录
例:
innobackupex --host 地址 --port 端口号 --user root --password PASSWORD --databases=库 目录名 --no-timestamp  #备份所有数据,不指定库时为备份所有

恢复:

1. 停止数据库服务
2. 清空数据目录下的数据
3. 准备恢复数据
4. 恢复数据
5. 修改数据目录下文件的所有者/组为mysql
6. 启动数据库服务
7. 管理员登录查看数据

systemctl stop mysqld   #关闭服务
rm -rf /var/lib/mysql/*  #清空数据
yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm  #安装工具
innobackupex --apply-log 备份文件路径  #准备恢复数据
innobackupex --copy-back 备份文件路径  #拷贝数据到数据库目录下
chown -R mysql:mysql /var/lib/mysql/*  #修改权限

2)innobackupex在完全备份恢复某表的所有数据

表空间
存储表记录的文件

1. 删除表空间
2. 导出表信息
3. 拷贝表信息文件到数据库目录下
4. 修改表信息文件的所有者及组用户为mysql
5. 导入表空间
6. 删除数据库目录下的表信息文件
7. 查看表记录

mysql> alter table 库.表 discard tablespace;	 #删除表空间
innobackupex --apply-log --export 数据备份目录 #导出表信息
cp 数据备份目录/数据库名目录/表名.{ibd,exp,cfg} /var/lib/mysql/库名目录/  #cp表信息文件到数据库目录下
chown -R mysql:mysql /var/lib/mysql/库名目录/  #修改权限
mysql> alter table 库.表 import tablespace;  #导入表空间
rm -rf /var/lib/mysql/库名目录/表.{exp,cfg}  #删除表信息文件

3)innobackupex增量备份和恢复

增量备份:

1. 判断是否有新数据产生
2. 有新数据时判断哪些数据是新数据

备份目录/xtrabackup_checkpoints文件:

• lsn:日志序列号

backup_type	含义
full-backuped	完全备份
incremental	增量备份
log-applied	已应用(已准备好恢复数据)

应用实例:

周一:完全备份

] innobackupex --user root --password PASSWORD /allbak --no-timestamp  #备份所有数据
] cat /allbak/xtrabackup_checkpoints 
#lsn:日志序列号
backup_type = full-backuped
from_lsn = 0  	    #备份数据开始序列号
to_lsn = 2880259      #备份数据结尾序列号
last_lsn = 2880268      #增量备份时,使用此序列号与数据库目录下的ib_logfile进行比对,确定是否有新数据
compact = 0
recover_binlog_info = 0

周二:增量备份

] innobackupex --user root --password PASSWORD --incremental /new1dir --incremental-basedir=/allbak --no-timestamp
] cat /new1dir/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2880259    #通过上一次备份的结尾确定这一次备份的开始
to_lsn = 2882360    
last_lsn = 2882369    #再次进行增量备份时,同样进行比对
compact = 0
recover_binlog_info = 0

周三:增量备份

] innobackupex --user root --password PASSWORD --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp
] cat /new2dir/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2882360
to_lsn = 2884453
last_lsn = 2884462
compact = 0
recover_binlog_info = 0

增量恢复:

1. 停止数据库服务
2. 清空数据库目录下内容
3. 准备恢复数据(合并日志)
4. 拷贝数据到数据库目录下
5. 修改文件所有者/组为mysql
6. 启动数据库服务

systemctl stop mysqld
rm -rf /var/lib/mysql/*
innobackupex --apply-log --redo-only /allbak  #合并日志
innobackupex --apply-log --redo-only /allbak --incremental-dir=/new1dir  #合并增量日志
innobackupex --apply-log --redo-only /allbak --incremental-dir=/new2dir  #合并增量日志
innobackupex --copy-back /allbak  #拷贝文件
chown -R mysql:mysql /var/lib/mysql/*
systemctl start mysqld

4)数据迁移方案

1. 从服务器安装mysql,启动服务完成初始化，然后stop服务，删除/var/lib/mysql/下的所有文件，为恢复数据做准备

2. 使用innobackupex工具备份所有数据，并记录当前的binlog日志名和偏移量

innobackupex  --user root --password PASSWORD  --slave-info  /allbak --no-timestamp  #使用--slave-info参数，备份所有数据，并记录备份数据对应的binlog日志名

3. 将备份文档发送到从服务器

scp -r /allbak root@slave:

4. 从服务器使用备份文件恢复数据
5. 启动服务
6. 查看备份文件中记录的binlog日志信息，配置主从change master to

] cat /root/allbak/xtrabackup_info  | grep master11   #master11为当前主服务器的binlog日志前缀
binlog_pos = filename 'master11.000001', position '7700'

7. stop从服务器的mysqld服务，卸载，安装pxc，启动pxc，此时该服务器依然为线上服务器的从服务器，数据保证了同步，继续配置其余pxc服务器即可