欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

DBA05 - mysqldump 实时增量备份、innobackupex

程序员文章站 2024-03-21 08:59:04
...

一 、数据备份相关概念

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

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

• 二进制日志用途及配置方式

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

  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日志

• 查看日志当前记录格式
mysql> show variables like “binlog_format”;
DBA05 - mysqldump 实时增量备份、innobackupex
三种记录格式 :
1. statement :每一条修改数据的 sql 命令都会记录在 binlog 日志中。
2. row: 不记录 sql 语句上下文相关信息,仅保存哪条记录被修改。
3. mixed: 是以上两种格式的混合使用。

修改日志记录格式
[aaa@qq.com ~]# vim /etc/my.cnf

  1. [mysqld]
  2. ....
  3. binlog_format=mixed

[aaa@qq.com ~]# 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    //binlo*生的日志有建库的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;

DBA05 - mysqldump 实时增量备份、innobackupex

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;

DBA05 - mysqldump 实时增量备份、innobackupex

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的工具

DBA05 - mysqldump 实时增量备份、innobackupex

 

四、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;

[aaa@qq.com ~]# rm -rf /var/lib/mysql/lijunDB/person.cfg 
[aaa@qq.com ~]# rm -rf /var/lib/mysql/lijunDB/person.exp

4.6 查看记录
mysql> select * from lijunDB.person;