mysql5-备份

夏威夷果陆胖胖
100 阅读6分钟

数据备份

为什么要数据备份

  • 在任何生产环境中，数据的安全性很重要
  • 任何数据丢失都会产生严重的后果
  • 备份的主要目的是灾难恢复

造成数据丢失的原因：

  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难（如火灾、地震）和盗窃

备份的主要目的

是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。

数据备份分类

从物理与逻辑机角度，备份分为：

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

物理备份方法（不常用）：

  • 冷备份（脱机备份）：是在关闭数据库的时候进行的（数据不会丢失），备份在data目录下
  • 特备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
  • 温备份（数据库锁定表格）的状态下进行备份操作（不可写入但可读）。

逻辑备份：对于数据库逻辑组件（如：表等数据库对象）的备份（常用）

从数据库备份策略角度：

  • 完全备份：每次对数据库进行完整的备份。
    • 每次都进行完全的备份的话，会导致备份文件占用空间大小，有大量的重复数据。
    • 恢复时，直接恢复完全备份的文件即可
  • 差异备份：备份自从上次完全备份之后被修改过得文件（备份完全备份之后的数据）。
    • 可能会出现备份重复数据，导致占用额外的磁盘空间。
    • 恢复时，先恢复完全备份的数据，再次恢复差异备份的数据（用的少）。
  • 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会备份（每次增量备份都会在上一次完全备份或者增量备份之后的数据）。
    • 不会出现重复的数据，也不会占用额外的磁盘空间。
    • 恢复时，按照次序恢复完全备份和增量备份的数据

image.png

常见备份法

  • 物理冷备
    • 备份时数据库处于关闭状态，直接打包数据库文件；备份速度快，恢复也是最简单。
    • 专用备份工具mydump或mysqlhotcopy
    • mysqldump常用的逻辑备份工具（用的多）
    • mysqlhotcopy仅拥有备份myisam和archive表
  • 启用二进制日志进行增量备份
    • 进行增量备份，需要刷新二进制日志（刷新之后是第二天的日志）

mysql完全备份

优点

  • 备份与恢复操作简单方便

缺点

  • 数据存在大量的重复
  • 占用大量的备份空间
  • 备份与恢复时间长

数据库完全备份

物理冷备份与恢复

  • 关闭mysql数据库
  • 使用tar命令直接打包数据库文件夹
  • 直接替换现有的mysql目录即可

mysqldump备份与恢复

  • mysql自带的备份工具，方便实现对mysql的备份
  • 可以将指定的库、表导出sql脚本
  • 使用命令mysql导入备份的数据

日志

mysql的日志默认保存位置：/usr/local/mysql/data

  • 配置文件
    • vim /etc/my.cnf
    • [mysqld]
  • 错误日志
    • 用来记录当mysql启动、停止或运行时发生的错误信息，默认已开启
    • log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名
  • 二进制日志
    • 用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
    • log-bin=mysql-bin或log-bin=mysql-bin
  • 中继日志
    • 一般情况下它在mysql主从复制、读写分离集群的从节点开启，主节点一般不需要这个日志
  • 慢查询日志
    • 用来记录所有执行时间超过long_query_time秒的语句，可以找到那些查询语句执行时间长，以便提醒优化，默认是关闭的
    • s1ow_query_log=ON
    • slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
    • long_query_time=5 #设置超过5秒执行语句被记录，缺省时为10秒

xxx字段

字段含义
xxx%以xxx为开头的字段
%xxx以xxx结尾的字段
%xxx%只要出现xxx字段的都会显示出来
xxx精确查询

索引文件

记录更新语句

索引文件刷新方式：

  1. 重启mysql的时候会更新索引文件，用于记录新的更新语句
  2. 刷新二进制语句

实验

环境

mysql> create database shiyan;
mysql> use shiyan;
mysql> create table if not exists shiyan1(id int(10) not null auto_increment,name varchar(10) not null,age char(10) not null,hobby varchar(50),primary key(id));
mysql> insert into shiyan1 values(1,'yh1','10','sing');
#像这样插入用户，多插入几个
创建好，退出数据库
mysql> exit

image.png

物理冷备份

完全备份

关闭数据库，之后再打包备份

[root@localhost ~]# systemctl stop mysqld
#关闭服务
[root@localhost ~]# mkdir /backup/
#创建备份目录
[root@localhost ~]# rpm -q xz
#使用xz工具进行压缩，检查xz工具是否安装
#如果没有安装就安装一下
#[root@localhost ~]# yum install xz -y
[root@localhost ~]# tar Jcf /backup/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
#打包数据库文件。/usr/local/mysql/data 为数据库文件存放目录
[root@localhost ~]# cd /backup/
[root@localhost backup]# ls
#查看压缩的mysql数据
mysql_all_2022-11-29.tar.xz  mysql_all_.tar.xz
#查看tar包内的文件
[root@localhost backup]# tar tf mysql_all_2022-11-29.tar.xz

image.png

image.png

image.png

完全恢复

将数据库迁移到另一台主机，测试完全恢复。

主机16
[root@localhost ~]# scp /backup/mysql_all_2022-11-29.tar.xz 192.168.42.17:/opt
#将主机16上的包传到主机17上
主机17
[root@localhost ~]# systemctl stop mysqld.service
#关闭防火墙
[root@localhost ~]# cd /opt/
[root@localhost opt]# mkdir /opt/bak/
#创建备份目录
[root@localhost opt]# tar Jxf mysql_all_2022-11-29.tar.xz -C /opt/bak/
#解压到备份目录

[root@localhost opt]# cd /opt/bak/
[root@localhost bak]# \cp -af usr/local/mysql/data/ /usr/local/mysql
#将data目录复制到/usr/local/mysql/目录下，覆盖原有文件

[root@localhost bak]# systemctl start mysqld.service
#开启mysql服务
[root@localhost bak]# mysql -u root -p
#登录数据库查看恢复的数据
mysql> show databases;
mysql> use shiyan;
mysql> show tables;
mysql> select * from shiyan1;

image.png

image.png

image.png

image.png

逻辑备份（使用mysqldump工具）

  • mysqldump是常用的逻辑备份工具。
  • mysqldump可以将指定的库、表导出为SQL脚本。

mysqldump完全备份

完全备份一个或多个完整的库，包括库里面的所有表

mysqldump -uroot -p[密码] --databases 库名1 [库名2].. >/备份路径/备份文件名.sql

首先打开mysql服务
[root@localhost ~]# systemctl start mysqld.service
#将mysql数据库中的shiyan数据库以及他下面的所有的表都备份
[root@localhost ~]# mysqldump -u root -p --databases shiyan > /backup/shiyan.sql
#将shiyan数据库和lianxi数据库以及他们里面所有的表都备份到shiyan-lianxi.sql里（lianxi数据库里面什么都没有！！！）
[root@localhost ~]# mysqldump -u root -p --databases shiyan lianxi > /backup/shiyan-lianxi.sql
#查看
[root@localhost ~]# cd /backup/
[root@localhost backup]# ls

image.png

image.png

image.png

image.png

完全备份mysql服务器中所有的库，包括库中的所有的表

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

例：
[root@localhost ~]# mysqldump -u root -p123456 --all-databases > /backup/all.sql

image.png

完全备份指定库中的部分表

mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql

[root@localhost ~]# mysqldump -u root -p shiyan shiyan1 > /opt/shiyan1.sql
#备份shiyan库中shiyan1表
我没有将密码写在上面，所以要输入密码

#查看备份
[root@localhost ~]# grep -v "^--" /opt/shiyan1.sql | grep -v "^/" | grep -v "^$"

image.png

image.png

完全恢复

恢复数据库

使用mysqldump导出的文件，可使用导入的方法
source命令
步骤：
source+备份sql脚本的路径
模拟数据库里面的表故障
mysql> drop table shiyan1;
#删除表
mysql> show tables;

image.png

恢复
mysql> source /opt/shiyan1.sql;
mysql> show tables;
mysql> select * from shiyan1;

image.png

image.png

增量备份

实验

开启二进制日志功能

[root@localhost ~]# vim /etc/my.cnf
·······
 25 log-bin=mysql-bin
 26 binlog_format = MIXED
·······
#二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

image.png image.png

进行完全备份(增量备份是基于完全备份的，所以我们直接完全备份数据库)

[root@localhost ~]# mysqldump -u root -p shiyan shiyan1 > /opt/shiyan1_$(data + %F).sql

image.png

可每天进行增量备份操作，生成新的二进制日志文件

[root@localhost opt]# mysqladmin -u root -p flush-logs

image.png

插入新数据，以模拟数据的增加或变更

mysql> create database zy;
mysql> use zy;
mysql> create table test(id int(4),name varchar(10));
mysql> insert into test values(1,'one');
mysql> insert into test values(2,'二');
mysql> select * from test;

image.png

生成新的二进制文件

[root@localhost ~]# mysqladmin -u root -p flush-logs

image.png

增量恢复

一般恢复

模拟丢失更改的数据的恢复步骤（直接使用恢复即可）

备份zy数据库里面的test表
[root@localhost ~]# mysqldump -u root -p zy test > /opt/test1.sql
删除表
mysql> drop table test;

恢复备份
[root@localhost ~]# mysql -u root -p zy < /opt/test1.sql
mysql> use zy;
mysql> show tables;
mysql> select * from test;

image.png

image.png

image.png

image.png

模拟丢失所有数据的恢复步骤

删除整个数据库
mysql> show databases;
mysql> drop database zy;

方法一：可以基于断点复
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
方法二：
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

image.png

avatar
文章
阅读
粉丝