逻辑备份

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

常用备份选项

库级备份

全库备份
#mysqldump -p密码 [-A|--all-databases]  >alldb.sql

库级备份
#mysqldump -p密码 [-B|--databases] dbname1 dbname2 >dbname.sql

表级备份

表级备份
#mysqldump -p密码 dbname tablename1 tablename2 >db_t1_t2.sql

备份表结构
#mysqldump -p密码 dbname tablename -d > tablename_jiegou.sql

导出数据和导入数据

导出数据
首先查看安全路径:

mysql> show variables like "secure%";



导出数据到安全路径下
mysql> select * from mysql.user into outfile '/var/lib/mysql-files/user.xlsx'

导入数据
mysql> load data infile '/var/lib/mysql-files/user.xlsx' into table user;

注:修改安全路径的方法

#mkdir /data
#chown mysql.mysql /data
#vim /etc/my.cnf
secure_file_priv=/data/

重启数据库生效
#systemctl restart msyqld

查看安全路径显示:

mysql> show variables like "secure%";

MYSQL 全备 方案_MYSQL 全备 方案

恢复数据

恢复数据库

第一种方法:
    mysql -p密码   < db.sql

第二种方法:
#cat db.sql | mysql -p密码

第三种方法:
    mysql> source  db.sql的绝对路径;

恢复数据表

第一种方法:
    mysql -p密码 dbname  < table.sql

第二种方法:
 #cat db.sql | mysql -p密码 dbname

第三种方法:
    mysql> use dbname
    mysql> source  table.sql的绝对路径;

根据二进制日志文件恢复数据

开启binlog日志功能

创建二进制文件存放位置 例/data
#mkdir /data
修改权限
#chown mysql.mysql /data

编辑mysql配置文件添加以下内容
#vim /etc/my.cnf 

server-id = 1
log-bin = /data/mysql-bin

重启数据库
#systemctl restart msyqld

查看二进制日志:
#mysqlbinlog /data/mysql-bin.00001 --base64-output=decode-rows -vv

恢复数据

找到要恢复的sql语句的起始位置、结束位置
修复语句
#mysqlbinlog --start-position  起始位置 --stop-position 结束位置 /data/mysql-bin.00001 | mysql -p密码 dbname

根据binlog日志的时间点恢复
找到要恢复sql语句的起始时间、结束时间
修复语句
mysqlbinlog --start-datetime='2023-12-26 23:55:54' --stop-datetime='2023-12-26 23:56:541' mylog.000001 | mysql -p密码 dbname

物理备份

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

1.完全备份-----完整备份:

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


特点:占用空间大,备份速度慢,但恢复时一次恢复到位,恢复速度快。

完全备份流程:

mkdir /xtrabackup/full -p

innobackupex --user=root --password='密码' /xtrabackup/full

可以查看一下:
# cd /xtrabackup/full/
# ls
2023-12-26_11-47-49

完全备份恢复流程

1.关闭数据库:
# systemctl stop mysqld
# rm -rf /var/lib/mysql/ *
# rm -rf /var/log/mysqld.log			#可选操作
# rm -rf /var/log/mysql-slow/slow.log  #可选操作

2.恢复之前的验证恢复:
# innobackupex --apply-log /xtrabackup/full/2023-12-26-20_11-47-49

3.确认数据库目录:
恢复之前需要确认配置文件内有数据库目录指定,不然xtrabackup不知道恢复到哪里
# cat /etc/my.cnf
datadir=/var/lib/mysql

4.恢复数据:
# innobackupex --copy-back /xtrabackup/full/2019-08-20_11-47-49

5.修改权限:
# chown mysql.mysql  /var/lib/mysql -R

6.启动数据库:
# systemctl start mysqld

2.增量备份: 每次备份上一次备份到现在产生的新数据

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


特点:因每次仅备份自上一次备份(注意是上一次,不是第一次)以来有变化的文件,所 以备份体积小,备份速度快,但是恢复的时候,需要按备份时间顺序,逐个备份版本进行恢 复,恢复时间长。

3.差异备份:只备份跟完整备份不一样的

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


特点:占用空间比增量备份大,比完整备份小,恢复时仅需要恢复第一个完整版本和最后一次的差异版本,恢复速度介于完整备份和增量备份之间。

MYSQL 全备 方案_运维_02

感谢观看!希望能够帮助到大家!