阅读量:0
目录
一、逻辑备份
备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
本质：导出的是SQL语句文件
优点：不论是什么存储引擎，都可以用mysqldump备成SQL语句
缺点：速度较慢，导入时可能会出现格式不兼容的突发情况，无法做增量备份和累计增量备份。
提供三种级别的备份，表级，库级和全库级
逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。
数据一致，服务可用。:如何保证数据一致，在备份的时候进行锁表会自动锁表。锁住之后在备份。
本身为客户端工具: 远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql 本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql常用备份选项：
-A --all-databases \ 备份所有库
-B --databases bbs test mysql 备份多个数据库
--no-data -d\ 不导出任何数据，只导出数据库表结构。
mysqldump -u用户名 -p密码 -h主机地址 -P端口 -S套接字文件
1、备份数据
1.全库备份
[root@localhost ~]# mysqldump -p密码 [-A|--all-databases] >alldb.sql
2.库级备份
[root@localhost ~]# mysqldump -p密码 [-B|--databases] dbname1 dbname2 >dbname.sql 
3.表级备份
[root@localhost ~]# mysqldump -p密码 dbname tablename1 tablename2 | gzip >db_t1_t2.sql.gz
备份并压缩：
4.备份表结构
[root@localhost ~]# mysqldump -p密码 dbname tablename -d > tablename_jiegou.sql
5.导出数据,不导出表结构
1.查看安全路径：
mysql> show variables like "secure%"; 
2.导出数据
mysql> select * from school.stu into outfile '/var/lib/mysql-files/stu.xlsx';
3.导入数据
把表里数据先清空，注意：不要把表结构也删了，
mysql> load data infile '/var/lib/mysql-files/stu.xlsx' into table stu;然后看效果
6.恢复数据
1.恢复数据库
第一种方法：
[root@localhost ~]# mysql -p密码 < dbname.sql第二种方法：
[root@localhost ~]# cat dbname.sql | mysql -p密码第三种方法：
mysql> source /opt/backup/dbname.sql; 2.恢复数据表
第一种方法：
[root@localhost ~]# mysql -p密码 dbname < table.sql第二种方法：
[root@localhost ~]# cat table.sql | mysql -p密码 dbname第三种方法：
mysql> use dbname mysql> source /opt/backup/table.sql;7.更改数据库导出安全目录
查看数据库导出安全目录：
mysql> show variables like "secure%";
修改：
[root@localhost ~]# mkdir /data [root@localhost ~]# chown mysql.mysql /data [root@localhost ~]# vim /etc/my.cnf在my.cnf中添加：
secure_file_priv=/data/ 
新开一个窗口，重启数据库，检查更改后的安全目录是否生效：
[root@localhost ~]# systemctl restart mysqld
二、根据二进制日志文件恢复数据
1、开启binlog日志功能
[root@localhost ~]# mkdir /data [root@localhost ~]# chown mysql.mysql /data [root@localhost ~]# vim /etc/my.cnf添加以下两行：
2、重启数据库
[root@localhost ~]# systemctl restart mysqld3、模仿数据库数据丢失
4、查看二进制日志
[root@localhost ~]# mysqlbinlog /data/mysql-bin.000001 --base64-output=decode-rows -vv
5、恢复数据
第一种方法：根据position位置点
[root@localhost ~]# mysqlbinlog --start-position 796 --stop-position 961 /data/mysql-bin.000001 | mysql -pQianfeng@123 school
查看是否恢复：
第二种方法：根据时间恢复
我们这次再模仿数据丢失，把school库里面的stu表给删了
还是查看二进制日志文件，找到建表跟插入数据的信息
执行恢复操作:
[root@localhost ~]# mysqlbinlog --start-datetime="2023-12-23 15:06-13" --stop-datetime="2023-12-23 15:09:41" /data/mysql-bin.000001 | mysql -pQianfeng@123
三、物理备份
物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。
安装xtrabackup ：选择适合的版本即可
官网Software Downloads - Percona
1、全量备份
1.建立备份文件所需存放的目录
[root@localhost ~]# mkdir -p /data/{full,intr,xebf} [root@localhost ~]# chown -R mysql.mysql /data full 存放全量备份 intr 存放增量备份 xebf 存放差异备份2.开始备份
[root@localhost xtrabackup]# innobackupex --user=root --password='Qianfeng@123' /data/full
3.恢复数据
先停止数据库，然后清理数据目录（生产环境建议用mv）
[root@localhost xtrabackup]# systemctl stop mysqld [root@localhost xtrabackup]# rm -rf /var/lib/mysql/*重演：
[root@localhost full]# innobackupex --user=root --password='Qiangfeng@123' --apply-log --redo-only /data/full/2023-12-23_16-49-08/回滚：
[root@localhost full]# innobackupex --user=root --password="Qianfeng@123" --copy-back /data/full/2023-12-23_16-49-08/此时数据已恢复，修改权限然后启动数据库：
[root@localhost mysql]# chown -R mysql.mysql * [root@localhost mysql]# systemctl start mysqld查看数据是否恢复：
2、增量备份
在之前备份基础上再添加一条数据做增量备份
[root@localhost full]# innobackupex --user=root --password='Qianfeng@123' --incremental /data/intr --incremental-basedir=/data/full/2023-12-23_16-49-08/清理环境同上
依次重演：先重演第一次全量备份，再在全量备份基础上重演增量备份数据
[root@localhost full]# innobackupex --user=root --password='Qianfeng@123' --apply-log --redo-only /data/full/2023-12-23_16-49-08/[root@localhost full]# innobackupex --user=root --password='Qianfeng@123' --apply-log --redo-only /data/full/2023-12-23_16-49-08/ --incremental-dir=/data/intr/2023-12-23_17-10-22/回滚：
[root@localhost full]# innobackupex --user=root --password=Qianfeng@123 --copy-back /data/full/datetime修改权限，启动数据库，查看数据是否恢复
3、差异备份
如果后面星期三星期四还有数据，可以基于星期一的全量备份做差异备份，相当于把数个增量备份合并在一起，恢复时还是基于全量备份依次重演，然后回滚即可，操作步骤同增量备份一致
