• +86 400-9955-698
  • support@vinchin.com
logo
关于我们

技术分享

技术分享 mysqldump 实时增量备份

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
• 恢复操作
&ndash; mysql 目标库名 < 路径 /xxx.sql

&bull; 源库名的表示
&ndash; &ndash;all-databases 或 -A   所有库
&ndash; 数据库名                      单个库
&ndash; 数据库名 表名              单张表
备份多个库
&ndash; -B 数据库 1 数据库 2    备份多个库
&bull; 注意事项
&ndash; 无论备份还是恢复,都要验证用户及权限


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

 
  1. #!/bin/bash

  2. day=`date +%F`

  3. if [ ! -e /dbbak ];then

  4. mkdir /dbbak

  5. fi

  6. 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日志介绍

&bull; 二进制日志用途及配置方式

      类型                用途              配置
二进制日志 记录所有更改数据的
操作
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日志

&bull; 采用 binlog 日志的好处
&ndash; 记录除查询之外的所有 SQL 命令,
&ndash; 可用于数据恢复
&ndash; 配置 mysql 主从同步的必要条件

]#vim /etc/my.cnf

 
  1. [mysqld]

  2. server_id=51 //指定id值

  3. 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

 
  1. [mysqld]

  2. server_id=51

  3. #log-bin

  4. log-bin=/mylog/lijun    //log-bin后面的内容可以指定也可以不指定

]# systemctl restart mysqld
]#ls /mylog/lijun.*
lijun.000001 lijun.index

3.1.4 分析binlog日志

&bull; 查看日志当前记录格式
mysql> show variables like &ldquo;binlog_format&rdquo;;

三种记录格式 :
1. statement :每一条修改数据的 sql 命令都会记录在 binlog 日志中。
2. row: 不记录 sql 语句上下文相关信息,仅保存哪条记录被修改。
3. mixed: 是以上两种格式的混合使用。

修改日志记录格式
[root@localhost ~]# vim /etc/my.cnf

 
  1. [mysqld]

  2. ....

  3. binlog_format=mixed

[root@localhost ~]# systemctl restart mysqld

3.1.5 手动生成新的日志文件方法

默认日志文件大于500M时自动创建新日志文件

下面四种方式会产生新的日志文件:

]# systemctl restart mysqld
mysql> flush logs;
]# mysql -uroot -p密码 -e &ldquo;flush logs&rdquo;
]# mysqldump -uroot -p密码 &ndash;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命令执行的时间

指定偏移量范围选项 
&ndash;start-position=偏移量的值 
&ndash;stop-position=偏移量的值

指定时间范围选项
&ndash;start-datetime=&rdquo;yyyy-mm-dd hh:mm:ss&rdquo; 
&ndash;stop-position=&rdquo;yyyy-mm-dd hh:mm:ss&rdquo; 
++++++++++++++++++++++++++++
读取日志文件指定范围内的sql命令恢复数据。
]# mysqlbinlog &ndash;start-position=293 &ndash;stop-position=1450 lijun.000001 | mysql -uroot -p654321

++++++++++++++++++++++++++++

使用binlog日志恢复数据

]#mysqldump -uroot -p654321 &ndash;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 &ndash;start-position=偏移量的值 &ndash;stop-position=偏移量的值 日志文件名 | mysql -uroot -p654321   //&ndash;stop-positon的偏移量只要写到delete from db3.users之前就可以恢复新插入的数据;  采用binlog日志和mysql搭配使用不需要指定库名,因为binlog会记录所有除查询之外的命令,包括建库的命令。

mysql> select * from db3.user3;

3.1.7 删除已有的binlog日志文件

mysql> purge master logs to &ldquo;binlog文件名;     //删除指定日志文件之前的日志文件
mysql> purge master logs to &ldquo;lijun.000005&rdquo;;

mysql> reset master ;                                       //删除所有的日志文件重新生成第一个日志文件
mysql> show master status; 显示当前正在使用的binlog日志信息

]#mysqlbinlog 日志文件名;   //mysqlbinlog是一个查看lijun.000001的工具

 

四、innobackupex备份/恢复

常用的 MySQL 备份工具
&bull; 物理备份缺点
&ndash; 跨平台性差
&ndash; 备份时间长、冗余备份、浪费存储空间
&bull; mysqldump 备份缺点
&ndash; 效率较低,备份和还原速度慢
&ndash; 备份过程中,数据插入和更新操作会被挂起

XtraBackup 工具
&bull; 一款强大的在线热备份工具
&ndash; 备份过程中不锁库表,适合生产环境
&ndash; 由专业组织 Percona 提供(改进 MySQL 分支)
&bull; 主要含两个组件
&ndash; xtrabackup : C 程序,支持 InnoDB/XtraDB
&ndash; 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 基本选项
常用选项
&ndash;host 主机名
&ndash;user 用户名
&ndash;port 端口号
&ndash;password 密 码
&ndash;databases 数据库名
&ndash;no-timestamp 不用日期命名备份文件存储的子目录名
&ndash;databases=&rdquo; 库名 &rdquo; // 单个库
- -databases=&rdquo; 库 1 库 2&rdquo; // 多个库
&ndash;databases=&rdquo; 库 . 表 &rdquo; // 单个表

&ndash;redo-only 日志合并
&ndash;apply-log 准备还原 ( 回滚日志 )
&ndash;copy-back 恢复数据
&ndash;incremental 目录名 增量备份
&ndash;incremental&ndash;basedir=目录名 增量备份时,指定上一次备份数据存储的目录名
&ndash;incremental-dir= 目录名 准备恢复数据时,指定增量备份数据存储的目录名
&ndash;export 导出表信息
import 导入表空间

4.2 完全备份和恢复

]# innobackupex &ndash;user root &ndash;password 123456 &ndash;databases=&rdquo;mysql performance_schema sys gamedb &rdquo; /allbak &ndash;no-timestamp
]# ls /allbak (备份目录下既有备份的数据又有日志文件)
cat xtrabackup_checkpoints

完全恢复
&ndash;apply-log     //准备恢复数据
&ndash;copy-back    //恢复数据
(要求数据库目录必须是空的)

      1.准备恢复数据 (回滚备份目录下日志信息)

 ]#innobackupex &ndash;user root &ndash;password 123456 &ndash;databases=&rdquo;mysql performance_schema sys gamedb &rdquo;  &ndash;apply-log /allbak

]# systemctl stop mysqld
]# rm -rf /var/lib/mysql/*      //恢复时要求是空目录

      2. 把备份目录下数据库拷贝回数据库目录下

]#innobackupex &ndash;databases=&rdquo;mysql performance_schema sys lijunDB &rdquo; &ndash;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 增量备份与恢复

 &bull; 应用示例
&ndash; 必须先有一次完全备份 , 备份到 /allbak
&ndash; 第 1 次增量备份到 /new1bak
&ndash; 第 2 次增量备份到 /new2bak

LSN (log sequence  number)日志序列号/事务日志文件
增量备份的工作过程:
每一个备份目录下,都有记录当前目录备份信息的配置文件。innobackupex组件会检查备份目录下的/xtrabackup_checkpoints的结束位置(结束lsn)和mysql工作目录下的事物日志文件(ib_logfile0、ib_logfile1)结束位置的lsn进行对比。如果不一样就进行增量备份,一样则不要要进行增量备份。

]# cat 备份目录名/xtrabackup_checkpoints (记录备份类型 和lsn范围)

4.3.1 innobackupex增量备份

&ndash;incremental 目录名 #增量备份
&ndash;incremental-basedir=目录名 #增量备份时,指定上一次备份文件存储的目录名

     1.先要有一次完全备份 存放目录 /fullbak

]# innobackupex &ndash;user root &ndash;password 654321 \
&ndash;databases=&rdquo;gamedb mysql performance_schema sys&rdquo; \
/fullbak &ndash;no-timestamp

     2.插入新记录,执行增量备份 存放目录 /new1bak

mysql> insert into gamedb.t1 values(8080),(8080);
]# innobackupex &ndash;user root &ndash;password 654321 \
&ndash;databases=&rdquo;gamedb mysql performance_schema sys&rdquo; \
&ndash;incremental /new1dir &ndash;incremental-basedir=/fullbak &ndash;no-timestamp

     3.插入新记录,执行增量备份 存放目录 /new2bak

mysql> insert into gamedb.t1 values(8099),(8099);
]# innobackupex &ndash;user root &ndash;password 654321 \
&ndash;databases=&rdquo;gamedb mysql performance_schema sys&rdquo; \
&ndash;incremental /new2dir &ndash;incremental-basedir=/new1dir &ndash;no-timestamp

4.3.2 innobackupex增量恢复

&ndash;apply-log 准备恢复数据
&ndash;redo-only 合并日志
&ndash;incremental-dir=目录名 #增量恢复数据时,指定备份目录名称
&ndash;copy-back 恢复数据

1.清空数据库目录
]# rm -rf /var/lib/mysql
]# mkdir /var/lib/mysql

2.准备恢复数据
]# innobackupex &ndash;user root &ndash;password 654321 \
&ndash;databases=&rdquo;gamedb mysql performance_schema sys&rdquo; \
&ndash;apply-log &ndash;redo-only /fullbak

3.合并日志
]# innobackupex &ndash;user root &ndash;password 654321 \
&ndash;databases=&rdquo;gamedb mysql performance_schema sys&rdquo; \
&ndash;apply-log &ndash;redo-only /fullbak &ndash;incremental-dir=/new1dir

]# innobackupex &ndash;user root &ndash;password 654321 \
&ndash;databases=&rdquo;gamedb mysql performance_schema sys&rdquo; \
&ndash;apply-log &ndash;redo-only /fullbak &ndash;incremental-dir=/new2dir

4.拷贝备份文件到数据库目录
]# innobackupex &ndash;user root &ndash;password 654321 \
&ndash;databases=&rdquo;gamedb mysql performance_schema sys&rdquo; \
&ndash;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 使用完全备份文件恢复单个表

&ndash;export 导出表信息
mysql> alter table 库.表 discard tablespace; 删除表空间
mysql> alter table 库.表 import tablespace; 导入表空间

1完全备份
]# innobackupex &ndash;user root &ndash;password 123456 &ndash;databases=&rdquo;lijunDB&rdquo; /allbaklijunDB &ndash;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 &ndash;user root &ndash;password 654321 \
&ndash;databases=&rdquo;studb&rdquo; &ndash;apply-log &ndash;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;

  • 标签:
  • 技术分享

您可能感兴趣的新闻 换一批

现在下载,可享30天免费试用

立即下载