首页 > 数据库 >MySQL数据备份

MySQL数据备份

时间：2023-10-08 16:01:50浏览次数：44  
标签：-- root 备份 mysql 数据备份 MySQL db01 backup

目录

MySQL数据备份

binlog存在问题

1）企业中的binlog很大，位置点不好找 grep

2）企业中所有数据不会只在一个binlog中 配合全备（mysqldump）

3）想要恢复的库，中途有其他库表操作，如何只截取指定库的

binlog作用

永远只是为了恢复增量数据产生的

为什么要备份

1）备份就是为了恢复。

2）尽量减少数据的丢失（公司的损失）

备份类型

rsync：

1）冷备

​ 停机，停服务进行备份

2）热备

​ 不停机，不停服务，直接备份

MySQL：

1）冷备

​ 停库，维护页，进行备份

2）温备

​ 不停库，但是备份时候，会锁表

3）热备

​ 不停库，不锁表，直接备份数据

备份方式

1）逻辑备份

基于SQL语句进行备份

  • binlog
  • mysqldump
  • into outfile
mysql> select * from world.city into outfile '/tmp/world_city.data';
这个SQL查询语句用于将world数据库中city表的所有行数据导出到指定路径的文件中。在该示例中，数据将被导出到/tmp/world_city.data文件中。
  • replication
数据库主从复制

2）物理备份

基于数据文件进行备份

  • 直接打包datadir
  • Xtrabackup（percona）

备份策略（每天一次全备，每小时一次增备）

全量备份

增量备份

差异备份

MySQL逻辑备份工具mysqldump

-u：指定用户
-p：指定密码
-h：指定主机域
-S：指定socket文件
-P：指定端口

所有库备份

-A：备份所有库
--all-databases：备份所有库

[root@db01 data]# mysqldump -uroot -p123 -A > /tmp/full.sql

## 备份文件中注释
-- 注释内容
/* 注释内容 */

## MySQL恢复数据，是覆盖形式的
drop table
create table
insert

备份单个库

# -B：备份单个库，指定库名（备份库和表）
[root@db01 data]# mysqldump -uroot -p123 -B hht > /tmp/hht.sql
mysql -uroot -p123 < /tmp/hht.sql

# -B：备份多个库
[root@db01 data]# mysqldump -uroot -p123 -B 库名1 库名2 > /tmp/1.sql

# 不加选项，直接指定库名（备份表）
[root@db01 data]# mysqldump -uroot -p123 hht > /tmp/hht1.sql
mysql -uroot -p123 zls < /tmp/hht.sql

# 单表备份
[root@db01 data]# mysqldump -uroot -p123 mysql user > /tmp/mysql.sql
[root@db01 data]# mysqldump -uroot -p123 库名 表名1 表名2 > /tmp/mysql.sql

备份时刷新binlog

-F：备份数据，并且刷新binlog
[root@db01 data]# mysqldump -uroot -p123 -B mysql -F > /tmp/1.sql

## 缺陷：有多少数据库，刷新出来多少binlog

打点备份参数

--master-data=0|1|2

0：关闭
1：备份一个change master语句，不注释
2：备份一个change master语句，被注释

# 配合快照备份一起使用，可以在运行的数据库内做从库
change master to
master_user='rep',
master_host='172.16.1.51',
master_password='123',
master_log_file='mysql-bin.000013',
master_log_pos=13138;

快照备份

--single-transaction：不锁表备份，需要指定一个快照，否则永远备份不完
mysqldump -uroot -p123 -A --master-data=2 --single-transaction > /tmp/full_hot.sql

实用扩展选项

-R：备份MySQL的函数
--triggers：备份MySQL的触发器

额外扩展选项（很好用的）

-d：只备份表结构 
-t：只备份数据

完整备份语句

## 完整备份语句
mysqldump -uroot -p123 -A --master-data=2 --single-transaction > /tmp/full_hot.sql

## 完整备份语句v2.0，
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full_hot.sql

## 究极完整备份语句v3.0，压缩备份
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_hot.sql.gz

## 最后版本，优化备份目录
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F-%H).sql.gz

mysqldump的恢复(binlog日志临时停止)

#先不记录二进制日志
mysql> set sql_log_bin=0;
#库内恢复操作
mysql> source /backup/full.sql
#库外恢复操作
[root@db01 ~]# mysql -uroot -p123 < /backup/full.sql
#最后别忘了开去，虽然退出重新登录也可以刷新
mysql> set sql_log_bin=1;

注意：

1）mysqldump在备份和恢复时都需要MySQL实例启动为前提
2）一般数据量级100G以内，大约15-30分钟可以恢复（PB、EB就需要考虑别的方式）
3）mysqldump是以覆盖的形式恢复数据的

mysqldump企业案例

还没有写

物理备份

https://www.percona.com/downloads

安装Xtrabackup

#下载epel源 
wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-6.repo
# 依赖
 yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL 

[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

[root@db01 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm

[root@db01 ~]# yum install -y https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm

xtrabackup是老版本命令：会锁表（温备）

innobackupex是新版本命令：针对innodb存储引擎进行备份，不会锁表（热备）

备份方式（物理备份）
1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3）备份时读取配置文件/etc/my.cnf

inobackupex命令基础选项

innobackupex

--user：指定用户名
--password：指定密码
--socket：指定socket文件
--host：指定主机IP
--port：指定端口

--apply-log：模拟CSR
--copy-back：恢复数据

--incremental：开启增量备份
--incremental-basedir：指定上一次备份的目录
--no-timestamp：不需要时间戳
--redo-only：只做redo

innobackupex全备

# 全备
[root@db01 backup]# innobackupex --user=root --password=123 /backup/

[root@db01 backup]# ll /backup/2023-08-03_15-30-39/
total 77852
-rw-r----- 1 root root      481 Aug  3 15:30 backup-my.cnf             // MySQL配置文件备份  
-rw-r----- 1 root root 79691776 Aug  3 15:30 ibdata1                   // undo
-rw-r----- 1 root root       21 Aug  3 15:30 xtrabackup_binlog_info    // 
-rw-r----- 1 root root      135 Aug  3 15:30 xtrabackup_checkpoints
-rw-r----- 1 root root      501 Aug  3 15:30 xtrabackup_info
-rw-r----- 1 root root     2560 Aug  3 15:30 xtrabackup_logfile         // redo log

# [root@db01 2023-08-03_15-30-39]# cat xtrabackup_binlog_info 
mysql-bin.000022	120
# 和--master-data=2 打点文件，意思一样

undo：里的内容
原数据
commit标签
lsn：日志版本号
txid：事务提交号

# [root@db01 2023-08-03_08-53-33]# cat xtrabackup_checkpoints
backup_type = full-backuped     // 全备
from_lsn = 0                    // 备份开始时的日志序列号
to_lsn = 1730489                // 备份结束时的日志序列号
last_lsn = 1730489              // 备份期间生成的最后一个日志序列号
compact = 0                     // 用于压缩备份文件的标志。此处的值为0，表示备份文件未被压缩
recover_binlog_info = 0          // 刷新到磁盘的最新日志序列号

# [root@db01 2023-08-03_08-53-33]# cat xtrabackup_info
uuid = 2e9db7fd-3198-11ee-9a9b-000c2923970d
name =
tool_name = innobackupex
tool_command = --user=root --password=... /backup/
tool_version = 2.4.4
ibbackup_version = 2.4.4
server_version = 5.6.50-log
start_time = 2023-08-03 08:53:36
end_time = 2023-08-03 08:53:38
lock_time = 0
binlog_pos = filename 'mysql-bin.000004', position '120'
innodb_from_lsn = 0
innodb_to_lsn = 1730489
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

全备恢复

1）前提：被恢复的目录是空的
2）前提：被恢复的数据库的实例是关闭的 停库恢复
## 1.先停库
[root@db01 ~]# /etc/init.d/mysqld stop
## 2.清空被恢复的目录
[root@db01 ~]# rm -fr /app/mysql/data
# 3.1先手动CSR，将redo中的数据，重做一遍，然后将undo中的数据，回滚一遍
[root@db01 ~]# innobackupex --apply-log /backup/2023-08-03_08-53-33/
# 4.恢复数据
[root@db01 backup]# innobackupex --copy-back /backup/2023-08-03_08-53-33/
# 5.授权
[root@db01 backup]# chown -R mysql.mysql /app/mysql/data
---------------------------------------------------------
# 3.2.方法2
### 物理备份被csr后就不能增量备份了，只能恢复备份。
[root@db01 2023-08-03_15-30-39]# ll
total 196640
-rw-r----- 1 root root      481 Aug  3 15:30 backup-my.cnf
-rw-r----- 1 root root 50331648 Aug  3 16:29 ib_logfile0
-rw-r----- 1 root root 50331648 Aug  3 16:29 ib_logfile1
-rw-r----- 1 root root 12582912 Aug  3 16:29 ibtmp1
-rw-r----- 1 root root       21 Aug  3 15:30 xtrabackup_binlog_info
-rw-r--r-- 1 root root       24 Aug  3 16:29 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      135 Aug  3 16:29 xtrabackup_checkpoints
-rw-r----- 1 root root      501 Aug  3 15:30 xtrabackup_info
-rw-r----- 1 root root  8388608 Aug  3 16:29 xtrabackup_logfile
-rw-r--r-- 1 root root        1 Aug  3 16:29 xtrabackup_master_key_id

## 当csr可以直接cp到/data,位置，授权就可以使用了
chown -R mysql.mysql /app/mysql/data

整备和差异备

配置文件优化

### 每次都要输入账号密码sock文件主机域，就很难受，下面这个配置就欧克；
/etc/my.cnf
[client]
user=root
password=123
socket=/app/mysql/tmp/mysql.sock

innobackupex增量备份

## 核心
--incremental-basedir=/backup/full-2023-08-03/
用它指定前一个备份，可衔接上

# 1.全备
[root@db01 backup]# innobackupex --user=root --password=123 --socket=/app/mysql/tmp/mysql.sock --no-timestamp /backup/full-$(date +%F)
[root@db01 backup]# cat full-2023-08-03/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 2532018
last_lsn = 2599068
compact = 0
recover_binlog_info = 0
flushed_lsn = 2598762

# 2.第一次增量备份
[root@db01 backup]# innobackupex --user=root --password=123 --socket=/app/mysql/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/full-2023-08-03/ /backup/inc1-$(date +%F-%H)
[root@db01 backup]# cat inc1-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2532018
to_lsn = 2532018
last_lsn = 2606142
compact = 0
recover_binlog_info = 0
flushed_lsn = 2605562

# 3.第二次增备
[root@db01 backup]# innobackupex --user=root --password=123 --socket=/app/mysql/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc1-2023-08-03-17/ /backup/inc2-$(date +%F-%H)
[root@db01 backup]# cat inc2-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2532018
to_lsn = 2532018
last_lsn = 2621402
compact = 0
recover_binlog_info = 0
flushed_lsn = 2621112

# 4.第三次增备
[root@db01 backup]# innobackupex --user=root --password=123 --socket=/app/mysql/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc2-2023-08-03-17/ /backup/inc3-$(date +%F-%H)
[root@db01 backup]# cat inc3-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2532018
to_lsn = 2532018
last_lsn = 2634996
compact = 0
recover_binlog_info = 0
flushed_lsn = 2634706

xtrabackup增量备份恢复

1）基于上一次备份进行增量

2）增量备份无法单独恢复，必须基于全备进行恢复

3）所有增量必须要按顺序合并到全备当中

使用 Xtrabackup + 增量 + binlog

# 1.停库
[root@db01 backup]# /etc/init.d/mysqld stop

# 2.清空data目录,(把data目录mv或备份一下都可以)
[root@db01 data]# rm -fr /*

# 3.先模拟CSR
1）全备模拟CSR，只做redo，不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full-2023-08-03/

2）第一次增备合并到全备的时候，只做redo，不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1-2023-08-03-17/ /backup/full-2023-08-03/

3）第二次增备合并到全备的时候，只做redo，不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2-2023-08-03-17/ /backup/full-2023-08-03/

4）第三次增备（最后一次增备）合并到全备的时候，redo和undo都做
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3-2023-08-03-17/ /backup/full-2023-08-03/

5）最后全部合并完成后，把全备redo和undo都做一遍
[root@db01 backup]# innobackupex --apply-log /backup/full-2023-08-03/

# 4.恢复数据
[root@db01 backup]# innobackupex --copy-back /backup/full-2023-08-03/

# 5.授权
[root@db01 backup]# chown -R mysql.mysql /app/mysql/data

# 6.截取binlog
1）第一段 全备恢复后 ———— drop database backup
起始位置点：166984
[root@db01 backup]# cat /app/mysql/data/xtrabackup_binlog_pos_innodb
mysql-bin.000001 166984
结束位置点：184648
[root@db01 backup]# mysqlbinlog --base64-output=decode-rows -vvv /tmp/data/mysql-bin.000001 |grep -i 'drop database' -C 5
[root@db01 backup]# mysqlbinlog --start-position=166984 --stop-position=184648
/tmp/data/mysql-bin.000001 > /tmp/inc1.sql

2）第二段 drop database backup ———— drop database prod
起始位置点：184746
[root@db01 backup]# mysqlbinlog --base64-output=decode-rows -vvv /tmp/data/mysql-bin.000001 |grep -i 'drop database' -C 5
结束位置点：187690
[root@db01 backup]# mysqlbinlog --base64-output=decode-rows -vvv /tmp/data/mysql-bin.000001 |grep -i 'drop database' -C 5
[root@db01 backup]# mysqlbinlog --start-position=184746 --stop-position=187690
/tmp/data/mysql-bin.000001 > /tmp/inc2.sql
mysql> set sql_log_bin=0;
mysql> source /tmp/inc1.sql
mysql> source /tmp/inc2.sql

3）第三段 drop database prod ———— binlog 结尾（停库之前）

innobackupex差异备份

基于全备新增数据进行备份

比增量备份占用更多的磁盘空间，适合比较牛 的老板，还有就是恢复数据方便。

# 1.全备
[root@db01 backup]# innobackupex --no-timestamp /backup/full-$(date +%F)
[root@db01 backup]# cat full-2023-08-03/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 3038358
last_lsn = 3138647
compact = 0
recover_binlog_info = 0
flushed_lsn = 3138341

# 2.第一次差异备份
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full-$(date +%F) /backup/chayi-$(date +%F-%H)
[root@db01 backup]# cat chayi-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 3038358
to_lsn = 3038358
last_lsn = 3146567
compact = 0
recover_binlog_info = 0
flushed_lsn = 3146293

# 3.第二次差异备份
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full-$(date +%F) /backup/chayi2-$(date +%F-%H)
[root@db01 backup]# cat chayi2-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 3038358
to_lsn = 3129209
last_lsn = 3199173
compact = 0
recover_binlog_info = 0
flushed_lsn = 3198609

# 4.第三次差异备份
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full-$(date +%F) /backup/chayi3-$(date +%F-%H)
[root@db01 backup]# cat chayi3-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 3038358
to_lsn = 3129209
last_lsn = 3213877
compact = 0
recover_binlog_info = 0
flushed_lsn = 3213571

# 5.第四次差异备份
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full-$(date +%F) /backup/chayi4-$(date +%F-%H)
[root@db01 backup]# cat chayi4-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 3038358
to_lsn = 3129209
last_lsn = 3220661
compact = 0
recover_binlog_info = 0
flushed_lsn = 3220371

### from_lsn = 3038358,to_lsn = 3038358 这俩相同的原因，可能是redo没有写入的

innobackupex差异备份恢复

## 恢复数据
[root@db01 backup]# /etc/init.d/mysqld stop
[root@db01 backup]# mv /app/mysql/data /opt/

## 模拟CSR
1）全备只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full/

2）将最后一次差异备份合并到全备中，redo和undo都做
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/chayi4/ /backup/full/

3）全备redo和undo都做
[root@db01 backup]# innobackupex --apply-log /backup/full/

# 恢复
[root@db01 backup]# innobackupex --copy-back /backup/full/

# 授权
[root@db01 backup]# chown -R mysql.mysql /app/mysql/data

# 截取binlog
[root@db01 backup]# cat /app/mysql/data/xtrabackup_binlog_pos_innodb 
mysql-bin.000001 38530

[root@db01 backup]# mysqlbinlog --base64-output=decode-rows /opt/data/mysql-bin.000001
|grep -i 'drop database prod' -C 5
42308

[root@db01 backup]# mysqlbinlog --start-position=38530 --stop-position=42308 /opt/data/mysql-bin.000001 > /tmp/chayi.sql

## 临时关闭binlog的记录
root@localhost [(none)] >set sql_log_bin=0;

# 导入binlog
source /tmp/shay1.sql

# 继续差异备份
[root@db01 backup]# innobackupex --user=root --passowrd=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi5

标签：--,root,备份,mysql,数据备份,MySQL,db01,backup
From： https://www.cnblogs.com/xiutai/p/17749367.html

相关文章

  • mysql基础
    MySQL基础入门MySQL介绍什么是数据？数据(data)是事实或观察的结果，是对客观事物的逻辑归纳，是用于表示客观事物的未经加工的的原始素材。数据可以是连续的值，比如声音、图像，称为模拟数据。也可以是离散的，如符号、文字，称为数字数据。在计算机系统中，数据以二进制信息单元0,1的形式......
  • mysql三种安装方式
    mysql5.6三种安装方式mysql官网:mysql.com数据库排行目录mysql5.6三种安装方式MySQL安装包下载源码安装mysqlMySQL二进制安装源码安装启动报错报错MySQLyum安装包下载MySQLyum安装MySQL-5.7的版本源码安装二进制安装默认的生成初始密码缺少boost报错版本选择5.6：GA6-1......
  • MySQL用户权限管理
    目录MySQL用户权限管理MySQL用户管理MySQL用户管理：MySQL用户密码管理误删除root用户忘记root密码真正的root权限误删除了所有用户MySQL权限管理企业中权限设置MySQL用户权限管理MySQL用户管理MySQL中的用户不是只看用户名##优化MySQL用户mysql>truncatemysql.user;MySQ......
  • MySQL体系管理结构
    MySQL客户端与服务端模型MySQL是C/S结构的服务MySQL客户端mysqlmysqladminmysqldump图形化SQLyognavicatMySQL服务端mysqldMySQL服务端的连接方式TCP/IP连接mysql-uroot-p123-h10.0.0.51Socket连接mysql-uroot-p123-S/app/mysql/tmp/mysql.sock......
  • MySQL客户端命令
    目录MySQL客户端命令优化命令提示符mysqlmysqladminmysqldumpSQL层SQL语句DDL（DatabaseDefinitionLanguage）数据定义语言数据库create增：建库drop删：删库alter改表操作create增：建表数据类型数字类型字符串类型枚举类型浮点型时间戳类型字段属性（约束）drop删alter改DMLinsert增delete......
  • 资源清单编写MySQL，wordpress
    目录mysqlwordpresshttp://k8s.driverzeng.com/v1.19/mysql[root@master-1mysql]#catmysql.yamlapiVersion:"v1"kind:"Pod"metadata:name:mysql57//资源清单叫mysql57spec:nodeName:node-1......
  • MySQL MHA
    MySQLMHA1．什么是MHAMHA（MasterHighAvailability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。MHA的出现就是解决MySQL单点的问题。MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正......
  • MySQL专题面试题-二叉树、红黑树、B 树、B+树
    演示网址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html所谓的索引，就是帮助MySQL高效获取数据的排好序的数据结构，基本都是按照k-v形式存储。1.二叉树 二叉树的每个节点至多只有2个叶子节点，且左边的叶子节点键值比根节点小，右边的叶子节点键值比根节点大。这......
  • mysqldump 导出来的文件，使用 source还原时报错“ASCII '\0' appeared in the stateme
    导出语句：mysqldump-uroot-pword--databasesdb1--tablestable1>./sqldumps/archive-table1-`date+"%Y%m%d_%H%M%S"`.sql导出后，使用source还原报错：ASCII'\0'appearedinthestatement,butthisisnotallowedunlessoption我开始以为是我导出的编码格式有问题,......
  • mysql中limit后面不能使用运算符
    mysql中limit后面不能使用运算符进行分页查询的时候，如果写成以下sql，语句执行会报错：select*fromuserwhereid=123456andcode=111andcreate_date>=20190101andcreate_date<=20190202limit(1-1)*1,20因为mysql中limit后面不能带运算符，只能是常量。解决方法使......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99