技术分享
mysqldump 实时增量备份
2018-02-15
一 、数据备份相关概念
1.1 数据备份的目的
数据被误删除 或 设备损害导致数据丢失 ，用备份文件恢复数据。
1.2 数据备份方式
物理备份:
–冷备：cp tar
逻辑备份:
–mysqldum,mysql
物理备份及恢复： 指定备份库和表对应的文件
• 备份操作
– cp -rp /var/lib/mysql/ 数据库 备份目录 / 文件名
– tar -zcvf xxx.tar.gz /var/lib/mysql/ 数据库 /*
• 恢复操作
– cp -rp 备份目录 / 文件名 /var/lib/mysql/
– tar -zxvf xxx.tar.gz -C /var/lib/mysql/ 数据库名
cp -r /var/lib/mysql /opt/mysql.bak
cp -r /var/lib/mysql/bbsdb /opt/bbsdb.bak
rm -rf /var/lib/mysql/bbsdb
cp -r /opt/bbsdb.bak /var/lib/mysql/bbsdb
chown -R mysql:mysql /var/lib/mysql/bbsdb
systemctl restart mysqld
scp -r /opt/mysql.bak 192.168.4.51:/root/
rm -rf /var/lib/mysql
cp -r /root/mysql.bak /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl restart mysqld
逻辑备份及恢复： 在执行备份命令时，根据备份的库表及数据生成对应的sql命令，把sql存储到指定的文件里。
• 备份操作
– mysqldump 源库名 > 路径 /xxx.sql
• 恢复操作
– mysql 目标库名 < 路径 /xxx.sql
• 源库名的表示
– –all-databases 或 -A 所有库
– 数据库名 单个库
– 数据库名 表名 单张表
备份多个库
– -B 数据库 1 数据库 2 备份多个库
• 注意事项
– 无论备份还是恢复,都要验证用户及权限
1.3数据备份策略
完全备份:
备份所有数据（一张表的所有数据 一个库的所有数据 一台数据库的所有数据）备份新产生数据（差异备份 和 增量备份 都备份新产生的数据 ）
差异备份:
备份自完全备份后，所有新产生的数据。
增量备份:
备份自上次备份后，所有新产生的数据。
1.4如何对数据做备份
1.4.1 选择备份策略：
完全备份+差异备份
完全备份+增量备份
1.4.2 数据备份时间 数据服务器访问量少的时候执行备份
1.4.3 数据备份频率 根据数据产生量，决定备份频率
1.4.4 备份文件的命名 库名-日期.sql
1.4.5 备份文件的存储设置 准备独立的存储设备存储备份文件
1.4.6 使用周期性计划任务执行本机脚本
需求:每周一 23点备份数据库服务器上db3库下的所有表到系统的/bakdir目录。
完全备份+差异备份
周 23:00 bbsdb 文件名 数据
1 完全 10 1.sql 10
2 差异 5 2.sql 5
3 差异 3 3.sql 8
4 差异 2 4.sql 10
5 差异 1 5.sql 11
6 差异 7 6.sql 18
7 差异 2 7.sql 20
完全备份+增量备份（常用）
周 23:00 bbsdb 文件名 数据
1 完全 10 1.sql 10
2 增量 1 2.sql 1
3 增量 3 3.sql 3
4 增量 7 4.sql 7
5 增量 4 5.sql 4
6 增量 2 6.sql 2
7 增量 10 7.sql 10
+++++++++++++++++++++++++++++
二、完全备份与完全恢复
2.1 完全备份数据
]# mkdir -p /mydatabak
]# mysqldump -uroot -p654321 studb > /mydatabak/studb.sql //通过mysqldump把数据库的数据通过sql语句的形式导出到文件，这条命令备份的是studb库下的所有表。
]# mysqldump -uroot -p654321 db3 user3 > /mydatabak/db3-user3.sql
]#cat /mydatabak/studb.sql
]#cat /mydatabak/db3-user3.sql
2.2 完全恢复数据
]# mysql -uroot -p654321 studb < /mydatabak/studb.sql //先建空库，再执行sql导入
]# mysql -uroot -p654321 db3 < /mydatabak/db3-user3.sql
使用source 命令恢复数据
mysql> create database bbsdb;
mysql> use bbsdb;
mysql> source /mydatabak/studb.sql
每周一晚上18:00备份studb库的所有数据到本机的/dbbak目录下，备份文件名称要求如下 日期_库名.sql。
]#vim /root/bakstudb.sh
#!/bin/bash
day=`date +%F`
if [ ! -e /dbbak ];then
mkdir /dbbak
fi
mysqldump -uroot -p654321 studb > /dbbak/${day}_studb.sql
]# chmod +x /root/bakstudb.sh
]#/root/bakstudb.sh
]# ls /dbbak/*.sql
]#crontab -e
00 18 * * 1 /root/bakstudb.sh &> /dev/null
:wq
三、实时增量备份/恢复
3.1 启动mysql数据库服务的binlog日志文件 实现实时增量备份
3.1.1 binlog日志介绍
• 二进制日志用途及配置方式
| 类型 | 用途 | 配置 |
|---|---|---|
| 二进制日志 | 记录所有更改数据的 操作 |
log_bin[=dir/name] server_id= 数字 max_binlog_size= 数字 m |
binlog是mysql数据库服务日志文件的一种，默认没有启用。记录除查询之外的sql命令。
查询命令例如： select show desc
写命令例如： insert update delete create drop
3.1.2 启用binlog日志
• 采用 binlog 日志的好处
– 记录除查询之外的所有 SQL 命令,
– 可用于数据恢复
– 配置 mysql 主从同步的必要条件
]#vim /etc/my.cnf
[mysqld]
server_id=51 //指定id值
log-bin //启用binlog日志
]# systemctl restart mysqld
]# ls /var/lib/mysql/主机名-bin.000001 //binlog默认日志文件存放于/var/lib/mysql/,默认的日志文件名称是 主机名-bin.00000x
]# cat /var/lib/mysql/主机名-bin.index //记录已有的日志文件名
3.1.3 改变binlog日志文件存储路径及文件名
]#mysqlbinlog /var/lib/mysql/mysql51-bin.000001
****可以自定义binlog日志文件存储的位置和文件名称
]#mkdir /mylog
]#chown mysql /mylog
]#setenforce 0
]#vim /etc/my.cnf
[mysqld]
server_id=51
#log-bin
log-bin=/mylog/lijun //log-bin后面的内容可以指定也可以不指定
]# systemctl restart mysqld
]#ls /mylog/lijun.*
lijun.000001 lijun.index
3.1.4 分析binlog日志
• 查看日志当前记录格式
mysql> show variables like “binlog_format”;
三种记录格式 :
1. statement :每一条修改数据的 sql 命令都会记录在 binlog 日志中。
2. row: 不记录 sql 语句上下文相关信息,仅保存哪条记录被修改。
3. mixed: 是以上两种格式的混合使用。
修改日志记录格式
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
....
binlog_format=mixed
[root@localhost ~]# systemctl restart mysqld
3.1.5 手动生成新的日志文件方法
默认日志文件大于500M时自动创建新日志文件
下面四种方式会产生新的日志文件:
]# systemctl restart mysqld
mysql> flush logs;
]# mysql -uroot -p密码 -e “flush logs”
]# mysqldump -uroot -p密码 –flush-logs 库名 > 目录/xx.sql
使用一个新的日志文件 记录 新创建的webdb库的初始操作。
mysql> flush logs;
mysql> create database webdb; create table webdb.a(id int);
mysql> insert into webdb.a values(100);
mysql> insert into webdb.a values(101);
mysql> flush logs;
删除webdb库，使用 binlog日志文件恢复webdb库的数据。
mysql> drop database webdb;
3.1.6 使用binlog日志恢复数据（通常搭配mysql使用）
]#mysqlbinlog /mylog/lijun.000008 | mysql -uroot -p654321 //binlog产生的日志有建库的sql语句，所以不需要指定数据库名称就可以实现数据恢复
3.1.7 binlog日志记录sql命令方式
记录方式有2种： 偏移量 、记录sql命令执行的时间
指定偏移量范围选项
–start-position=偏移量的值
–stop-position=偏移量的值
指定时间范围选项
–start-datetime=”yyyy-mm-dd hh:mm:ss”
–stop-position=”yyyy-mm-dd hh:mm:ss”
++++++++++++++++++++++++++++
读取日志文件指定范围内的sql命令恢复数据。
]# mysqlbinlog –start-position=293 –stop-position=1450 lijun.000001 | mysql -uroot -p654321
++++++++++++++++++++++++++++
使用binlog日志恢复数据
]#mysqldump -uroot -p654321 –flush-logs db3 user3 > /root/user3.sql //导出sql的时候会生成一个全新的binlog日志文件
mysql> select * from user3;
mysql> insert into db3.user3 values(7);
mysql> insert into db3.user3 values(8);
mysql> insert into db3.user3 values(9);
mysql> insert into db3.user3 values(10);
mysql> delete from db3.users;
mysql> select * from db3.user3;
]#mysql -uroot -p654321 db3 < /root/user3.sql //把没插入数据之前的数据还原
mysql> select * from db3.user3; //这个时候查询到的是插入之前的原始数据
]#mysqlbinlog –start-position=偏移量的值 –stop-position=偏移量的值 日志文件名 | mysql -uroot -p654321 //–stop-positon的偏移量只要写到delete from db3.users之前就可以恢复新插入的数据； 采用binlog日志和mysql搭配使用不需要指定库名，因为binlog会记录所有除查询之外的命令，包括建库的命令。
mysql> select * from db3.user3;
3.1.7 删除已有的binlog日志文件
mysql> purge master logs to “binlog文件名; //删除指定日志文件之前的日志文件
mysql> purge master logs to “lijun.000005”;
mysql> reset master ; //删除所有的日志文件重新生成第一个日志文件
mysql> show master status; 显示当前正在使用的binlog日志信息
]#mysqlbinlog 日志文件名； //mysqlbinlog是一个查看lijun.000001的工具
四、innobackupex备份/恢复
常用的 MySQL 备份工具
• 物理备份缺点
– 跨平台性差
– 备份时间长、冗余备份、浪费存储空间
• mysqldump 备份缺点
– 效率较低,备份和还原速度慢
– 备份过程中,数据插入和更新操作会被挂起
XtraBackup 工具
• 一款强大的在线热备份工具
– 备份过程中不锁库表,适合生产环境
– 由专业组织 Percona 提供(改进 MySQL 分支)
• 主要含两个组件
– xtrabackup : C 程序,支持 InnoDB/XtraDB
– innobackupex :以 Perl 脚本封装 xtrabackup ,还支持 MyISAM
4.1 安装 xtrabackup
]#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
man xtrabackup
man innobackupex
innobackupex 基本选项
常用选项
–host 主机名
–user 用户名
–port 端口号
–password 密 码
–databases 数据库名
–no-timestamp 不用日期命名备份文件存储的子目录名
–databases=” 库名 ” // 单个库
- -databases=” 库 1 库 2” // 多个库
–databases=” 库 . 表 ” // 单个表
–redo-only 日志合并
–apply-log 准备还原 ( 回滚日志 )
–copy-back 恢复数据
–incremental 目录名 增量备份
–incremental–basedir=目录名 增量备份时,指定上一次备份数据存储的目录名
–incremental-dir= 目录名 准备恢复数据时,指定增量备份数据存储的目录名
–export 导出表信息
import 导入表空间
4.2 完全备份和恢复
]# innobackupex –user root –password 123456 –databases=”mysql performance_schema sys gamedb ” /allbak –no-timestamp
]# ls /allbak （备份目录下既有备份的数据又有日志文件）
cat xtrabackup_checkpoints
完全恢复
–apply-log //准备恢复数据
–copy-back //恢复数据
(要求数据库目录必须是空的)
1.准备恢复数据 （回滚备份目录下日志信息）
]#innobackupex –user root –password 123456 –databases=”mysql performance_schema sys gamedb ” –apply-log /allbak
]# systemctl stop mysqld
]# rm -rf /var/lib/mysql/* //恢复时要求是空目录
2. 把备份目录下数据库拷贝回数据库目录下
]#innobackupex –databases=”mysql performance_schema sys lijunDB ” –copy-back /allbak
3. 修改数据库目录所有者和组用户为mysql
]# chown -R mysql:mysql /var/lib/mysql
启动数据库服务
]# systemctl start mysqld
4. 登录查看数据。
]# mysql -uroot -p123456
mysql> show databases;
mysql> select * from gamedb.a;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4.3 增量备份与恢复
• 应用示例
– 必须先有一次完全备份 , 备份到 /allbak
– 第 1 次增量备份到 /new1bak
– 第 2 次增量备份到 /new2bak
LSN （log sequence number）日志序列号/事务日志文件
增量备份的工作过程：
每一个备份目录下，都有记录当前目录备份信息的配置文件。innobackupex组件会检查备份目录下的/xtrabackup_checkpoints的结束位置(结束lsn)和mysql工作目录下的事物日志文件(ib_logfile0、ib_logfile1)结束位置的lsn进行对比。如果不一样就进行增量备份，一样则不要要进行增量备份。]# cat 备份目录名/xtrabackup_checkpoints (记录备份类型 和lsn范围)
4.3.1 innobackupex增量备份
–incremental 目录名 #增量备份
–incremental-basedir=目录名 #增量备份时，指定上一次备份文件存储的目录名
1.先要有一次完全备份 存放目录 /fullbak
]# innobackupex –user root –password 654321 \
–databases=”gamedb mysql performance_schema sys” \
/fullbak –no-timestamp
2.插入新记录，执行增量备份 存放目录 /new1bak
mysql> insert into gamedb.t1 values(8080),(8080);
]# innobackupex –user root –password 654321 \
–databases=”gamedb mysql performance_schema sys” \
–incremental /new1dir –incremental-basedir=/fullbak –no-timestamp
3.插入新记录，执行增量备份 存放目录 /new2bak
mysql> insert into gamedb.t1 values(8099),(8099);
]# innobackupex –user root –password 654321 \
–databases=”gamedb mysql performance_schema sys” \
–incremental /new2dir –incremental-basedir=/new1dir –no-timestamp
4.3.2 innobackupex增量恢复
–apply-log 准备恢复数据
–redo-only 合并日志
–incremental-dir=目录名 #增量恢复数据时，指定备份目录名称
–copy-back 恢复数据
1.清空数据库目录
]# rm -rf /var/lib/mysql
]# mkdir /var/lib/mysql
2.准备恢复数据
]# innobackupex –user root –password 654321 \
–databases=”gamedb mysql performance_schema sys” \
–apply-log –redo-only /fullbak
3.合并日志
]# innobackupex –user root –password 654321 \
–databases=”gamedb mysql performance_schema sys” \
–apply-log –redo-only /fullbak –incremental-dir=/new1dir
]# innobackupex –user root –password 654321 \
–databases=”gamedb mysql performance_schema sys” \
–apply-log –redo-only /fullbak –incremental-dir=/new2dir
4.拷贝备份文件到数据库目录
]# innobackupex –user root –password 654321 \
–databases=”gamedb mysql performance_schema sys” \
–copy-back /fullbak
]# ls /var/lib/mysql -l
5.修改文件的所有者/组mysql
]# chown -R mysql:mysql /var/lib/mysql
6.重启数据库服务
]# systemctl restart mysqld
]# mysql -uroot -p123456
4.3.3 使用完全备份文件恢复单个表
–export 导出表信息
mysql> alter table 库.表 discard tablespace; 删除表空间
mysql> alter table 库.表 import tablespace; 导入表空间
1完全备份
]# innobackupex –user root –password 123456 –databases=”lijunDB” /allbaklijunDB –no-timestamp
2查看备份目录文件列表
]# ls /allbakstudb
]# ls /allbakstudb/studb
3误删除a表: mysql> drop table studb.a;
4 使用完全备份文件恢复单个表
4.1 按照备份时的表结构创建删除的表
create table studb.a(name char(10));
4.2 删除创建表的表空间文件
mysql> alter table studb.a discard tablespace;
4.3 使用备份文件导出表信息
]#innobackupex –user root –password 654321 \
–databases=”studb” –apply-log –export /allbakstudb
4.4 把导出的表信息文件，拷贝到对应的数据库目录下，并修改所有者和组为mysql
]# cp /allbaklijunDB/lijunDB/person.{cfg,exp,ibd} /var/lib/mysql/lijunDB/
]# chown mysql:mysql /var/lib/mysql/studb/a.*
4.5 导入表空间
mysql> alter table studb.a import tablespace;
[root@mysql51 ~]# rm -rf /var/lib/mysql/lijunDB/person.cfg
[root@mysql51 ~]# rm -rf /var/lib/mysql/lijunDB/person.exp
4.6 查看记录
mysql> select * from lijunDB.person;
- 标签：
-
技术分享
