linux中数据库备份

2023-12-21 20:35:55

数据库备份

热备份,数据库启动同时给客户端提供服务的情况下 ,例如逻辑备份

冷备份,数据库要关掉或者不能给客户端提供服务,例如物理备份

逻辑备份

定义

备份的是建表、建库、插入等操作所执行SQL语句,适用于中小型数据库,效率相对较低。
本质:导出的是SQL语句文件
优点:不论是什么存储引擎,都可以用mysqldump备成SQL语句
缺点:速度较慢,导入时可能会出现格式不兼容的突发情况,无法做增量备份和累计增量备份。
提供三种级别的备份,表级,库级和全库级
逻辑备份:备份的是建表、建库、插入等操作所执行SQL语句(DDL DML DCL),适用于中小型数据库,效率相对较低。
数据一致,服务可用:在备份的时候进行锁表会自动锁表。锁住之后在备份。
常用备份选项
-A, --all-databases    //备份所有库
-B, --databases bbs test mysql      //备份多个数据库
--no-data,-d          //不导出任何数据,只导出数据库表结构。

备份语句

mysqldump -u用户名 -p密码 -h主机地址 -P端口 -S套接字文件 

套接字文件:my.cnf文件中指定的socket文件
例:socket=/usr/local/tmp/mysql.sock   //指定socket文件的位置

全库备份

mysqldump -p密码 [-A|--all-databases]  >文件名       //备份所有库到某一个文件

库级备份

mysqldump -p密码 [-B|--databases] 数据库1 数据库2 >文件名 //备份数据库1、数据库2到某个文件

mysqldump -p密码 数据库1 数据库2 >文件名    //备份数据库到某个文件,备份的语句中没有建库语句

表级备份

mysqldump -p密码 数据库 表1 表2 >文件      //备份数据库中的表到某个文件

备份表结构

mysqldump -p密码 数据库 表 -d > 文件     //备份数据库的表文件到某个文件

数据库数据操作

导出表数据

mysql>show variables like "secure%";    //查看数据库安全路径

mysql>select * from 数据库.into outfile '安全路径/文件'    //导出数据库的表的数据

导入表数据

mysql> load data infile '安全路径/文件' into table 表名;    //导入文件的数据到表中

逻辑恢复

恢复库

1:mysql -p密码 < 文件

2:cat 文件 | mysql -p密码

3:mysql>source 文件路径/文件名;

恢复表

1:mysql -p密码 数据库名  < 文件

2:cat 文件 | mysql -p密码 数据库名

3:mysql> use 数据库;
  mysql> source 文件路径/文件名;

恢复表结构

1:mysql  -p密码 -D 表名 < 文件

2:cat 文件 | mysql -p密码 -D 表名

更改数据库导出安全目录

mysql> show variables like "secure%";    //查询数据导出安全目录
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_auth      | ON                    |
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+


修改方法
mkdir /data                 //创建文件
chown mysql.mysql /data     //设置文件所有者和属组
vim /etc/my.cnf             //mysql的配置文件
secure_file_priv=/data/     //添加更改数据导出目录的语句
systemctl restart mysqld    //重启数据库  


mysql> show variables like "secure%";    //查询更改后数据导出安全目录
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| secure_auth      | ON     |
| secure_file_priv | /data/ |
+------------------+--------+

BINLOG恢复数据

开启binlog服务

vim /etc/my.cof    //mysql配置文件

server-id = 1
log-bin = /data/mysql-bin    //binlog二进制保存目录

systemctl restart mysqld    //重启数据库

查看日志文件

mysqlbinlog /binlog日志文件路径 --base64-output=decode-rows -vv

寻找数据开始-结束的范围
BEGIN
	at 123      //开始的at值
	……
COMMIT
	at 789      //结束的at值

恢复数据

mysqlbinlog --start-position 123 --stop-position 789 /binlog日志文件路径 | mysql -p密码 数据库       //将binlog日志文件中的语句发送到mysql里面,进行数据恢复

查看binlog文件

show master status\G        //查看写入到binlog日志文件

flush logs        //binlog日志文件轮转

物理备份

物理备份: 直接复制数据库文件,适用于大型数据库环境,不受存储引擎的限制,但不能恢复到不同的MySQL版本。

安装xtrabackup

官网:www.percona.com
官网一直下滑找到Downloads
寻找xtrabackup
选择版本下载,下载yum仓库
yum -y install percona-xtrabackup-24.x86_64     //安装工具

上传xtravackup的rpm包
yum -y localinstall *       //安装本地本目录所有rpm包

完全备份

定义
完全备份-----完整备份:
每次都将所有数据(不管自第一次备份以来有没有修改过),进行一次完整的复制,备份后会清除文件的存档属性,方便日后增量备份或者差异备份进行版本比较。

特点:占用空间大,备份速度慢,但恢复时一次恢复到位,恢复速度快。
完全备份流程
mkdir -p /data/full               //创建完全备份目录

innobackupex --user=root --password='0' /data/full    //创建备份
完全备份恢复流程
systemctl stop mysqld       //停止数据库

rm -rf /var/lib/mysql/*     //清理环境

innobackupex --user --password --apply-log --redo-only /完全备份目录路径    //重演

innobackupex --user --password --copy-back /完全备份重演路径                //回滚

vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql          //确认数据库目录

chown mysql.mysql  /var/lib/mysql -R     //修改mysql数据库目录权限

systemctl start mysqld                   //启动数据库

增量备份

定义
增量备份: 每次备份上一次备份到现在产生的新数据
在第一次完整备份之后,第二次开始每次都将添加了存档属性的文件进行备份,并且在备份之后再把这些存档属性清除。为什么要清除存档属性呢?这就是为了下一次备份的时候判断是否有文件变化,因为用户在每次备份以后修改这些被清除存档属性的文件,存档属性就会自动加上,相当于用户告诉系统,这些文件有变化,你下一次就备份这些文件,其他没有存档属性的就不需要备份,这就是增量备份的工作机制。

特点:因每次仅备份自上一次备份(注意是上一次,不是第一次)以来有变化的文件,所 以备份体积小,备份速度快,但是恢复的时候,需要按备份时间顺序,逐个备份版本进行恢复,恢复时间长。
增量备份流程
mkdir -p /data/full               //创建完全备份目录
mkdir -p /data/intr              //创建增量备份目录

innobackupex --user=root --password='0' /data/full        //创建完整备份
innobackupex --user=root --password='0' --incremental /data/intr --incremental-basedir=/data/full/datatime              //基于上一次备份目录,进行增量备份
增量备份恢复流程
systemctl stop mysqld        //停止数据库

rm -rf /var/lib/mysql/*      //清理环境

innobackupex --user --password --apply-log --redo-only /完全备份目录路径      //重演完整备份目录

innobackupex --user --password --apply-log --redo-only /完整备份重演目录 --incremental-dir=/增量备份目录 
     //增量备份加入完整备份重演目录

innobackupex --copy-back /完整+增量备份重演目录   //回滚

vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql          //确认数据库目录

chown mysql.mysql  /var/lib/mysql -R     //修改mysql数据库目录权限

systemctl start mysqld                   //启动数据库

差异备份

定义
差异备份:只备份跟上一次完整备份后更新的数据
在第一次完整备份之后,第二次开始每次都将所有文件与第一次完整备份的文件做比较,把自第一次完整备份以来所有修改过的文件进行备份,且以后每次备份都是和第一次完整备份进行比较(注意是第一次,不是上一次),备份自第一次完整备份以来所有的修改过的文件。因此,差异备份在备份完毕之后不需要清除文件的存档属性,因为这些文件和下一次备份没有什么关系,它仅仅和第一次完整备份的数据进行比较(第一次完整备份之后是清除存档属性的)。

特点:占用空间比增量备份大,比完整备份小,恢复时仅需要恢复第一个完整版本和最后一次的差异版本,恢复速度介于完整备份和增量备份之间。
差异备份流程
mkdir -p /data/full               //创建完全备份目录
mkdir -p /data/diff               //创建差异备份目录

innobackupex --user=root --password='0' /data/full    //创建完整备份

innobackupex --user=root --password='0' --incremental /data/diff --incremental-basedir=/data/full             //基于上一次完全备份进行差异备份
差异备份恢复流程
systemctl stop mysqld          //停止数据库

rm -rf /var/lib/mysql/*        //清理环境

innobackupex --user --password --apply-log --redo-only /完整备份路径       //重演完整备份目录

innobackupex --user --password --apply-log --redo-only /完整备份目录 --incremental-dir=/差异备份目录              //差异备份加入完整备份重演目录

innobackupex --copy-back /完整+差异备份重演目录   //回滚

vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql          //确认数据库目录

chown mysql.mysql  /var/lib/mysql -R     //修改mysql数据库目录权限

systemctl start mysqld                   //启动数据库

差异备份和增量备份的区别

备份语句相同,基于创建备份的目录不同
差异备份是基于上次完整备份目录进行的
增量备份是基于上次备份目录进行的

完整备份是不管三七二十一,每次都把指定的备份目录完整的复制一遍,不管目录下的文件有没有变化
增量备份就是每次将之前(第一次、第二次、直到前一次)做过备份之后有变化的文件进行备份
差异备份就是每次都将第一次完整备份以来有变化的文件进行备份

文章来源:https://blog.csdn.net/qq_59207739/article/details/135138891
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。