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




#!/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”;
DBA05 - mysqldump 实时增量备份、innobackupex
三种记录格式 :
1. statement :每一条修改数据的 sql 命令都会记录在 binlog 日志中。
2. row: 不记录 sql 语句上下文相关信息,仅保存哪条记录被修改。
3. mixed: 是以上两种格式的混合使用。

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




[mysqld]



....



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;

相关标签： DBA05 - mysqldump 实时增量备份、innob 从零开始学数据库Mysql

上一篇： MySQL 学习 - Replication集群 - 搭建 - GTID模式

下一篇： MySQL之间数据同步

DBA05 - mysqldump 实时增量备份、innobackupex

一、数据备份相关概念

1.1 数据备份的目的

1.2 数据备份方式

1.3数据备份策略

1.4如何对数据做备份

二、完全备份与完全恢复

2.1 完全备份数据

2.2 完全恢复数据

三、实时增量备份/恢复

3.1 启动mysql数据库服务的binlog日志文件实现实时增量备份

3.1.1 binlog日志介绍

3.1.2 启用binlog日志

3.1.3 改变binlog日志文件存储路径及文件名

3.1.4 分析binlog日志

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

3.1.6 使用binlog日志恢复数据（通常搭配mysql使用）

3.1.7 binlog日志记录sql命令方式

3.1.7 删除已有的binlog日志文件

四、innobackupex备份/恢复

4.1 安装 xtrabackup

4.2 完全备份和恢复

4.3 增量备份与恢复

4.3.1 innobackupex增量备份

4.3.2 innobackupex增量恢复

4.3.3 使用完全备份文件恢复单个表