数据库逻辑备份

最新推荐文章于 2024-05-27 00:45:21 发布

zheng__kai

最新推荐文章于 2024-05-27 00:45:21 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： linux mysql

本文链接：https://blog.youkuaiyun.com/zheng__kai/article/details/81055694

linux 同时被 2 个专栏收录

3 篇文章

订阅专栏

mysql

3 篇文章

订阅专栏

本文介绍使用mysqldump进行逻辑完全备份的方法，包括备份数据库、表结构及数据，并结合binlog确保数据的一致性和可用性。同时，还介绍了如何通过不同参数设置来优化备份过程，以及如何恢复数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysqldump实现逻辑完全备份 + binlog

优势：1 自动记录position位置。（show master status\G;）

2 可用性，一致性 (锁表机制。)

语法：

mysqldump -h 服务器 -u用户名 -p密码数据库名 > 备份文件.sq

关于数据库名：-A, --all-databases 所有库

school 数据库名

school stu_info t1 是指school数据库的表stu_info、t1

-B, --databases bbs test mysql 多个数据库

关于其它参数说明：

--single-transaction #InnoDB 一致性服务可用性

-E, --events #备份事件调度器代码

--opt #同时启动各种高级选项

-R, --routines #备份存储过程和存储函数

-F, --flush-logs #备份之前刷新日志，截断日志。备份之后新binlog。

--triggers #备份触发器

- -master-data=1|2 #该选项将会记录binlog的日志位置与文件名并追加到文件中，或添加注释

帮助：mysqldump --help

例子：

创建一个库

          mysqldump -p'QianFeng@123' \
              --all-databases --single-transaction \          //所有库一致性
              --routines --triggers --master-data=2 \        //存储过程、函数，和主数据库位置
              --flush-logs \                                                   //刷新日志，形成新的日志

> /backup/`date +%F-%H`-mysql-all.sql //大于号，打到哪个备份文件里面去

停库，删库：

                        systemctl stop mysqld
                rm -rf /var/lib/mysql/*
                        systemctl start mysqld
                        grep password /var/log/mysqd.log

mysqladmin -u root -p'23edeswdr23' password 'Qwer@123'

备份实战：

准备两套root密码：密码1 Qwer@123 配置到当前数据库中

密码2 Qwer@1234 备用

准备库1：

testdb1.t1：

mysql> select * from testdb1.t1;
+------+

|id |

                                                            +------+
   | 1 |
   |    2 |
   |    3 |

+------+

执行备份：mysqldump -p'Qwer@123' \
--all-databases --single-transaction \
--routines --triggers --master-data=2 \ //master-data=2 代表注释掉日志记录
--flush-logs \

> /backup/`date +%F-%H`-mysql-all.sql

观察备份细节：

vim /backup/2018-11-25-14-mysql-all.sql

LOCK TABLES `user` WRITE; 观察各种锁机制，用来保证数据一致性

22 CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000004', MASTER_LOG_POS=154; 二进制日志截断位置。第22行

备份后数据变更行为：在testdb2.t2 中插入数据1；创testdb3库（观察二进制日志文件）重启数据库（再次观察二进制日志文件）
在testdb2.t2 中插入数据2；
删testdb3库

在testdb2.t2 中插入数据3；

恢复实战：

1 备份二进制日志文件：cp /var/lib/mysql/*bin* ~

2. 停止数据库：systemctl stop mysqld

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

4.启动数据库：systemctl start mysqld

grep 'password' /var/log/mysqld.log找密码，再改一下密码。

5.mysql恢复数据： mysql -p'Qwer@1234' < /backup/2018-12-08-04-mysql-all.sql

mysql -p'Qwer@1234' -e 'flush privileges'

ll /var/lib/mysql/ （恢复的数据库一个）

6.二进制日志恢复： 观察二进制截取记录

vim /backup/2018-11-25-14-mysql-all.sql

CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000002', MASTER_LOG_POS=154;

注意后续有多少日志，要跟多少日志名字。

mysqlbinlog localhost-bin.000002 localhost-bin.000003 --start-position=154 | mysql -p'Qwer@123'

7.观察数据是否完整

8.请思考误删除的问题

9.关于数据恢复时的多余日志。

方法一：

原因：每次还原数据库都会增加日志的体积。但这些都是还原操作。恢复数据库的日志占用了存储空间。

mysql> set sql_log_bin =0

再mysql> source /backup/*.sql

观察二进制日志并没有发生变化。

方法二：在备份文件中，加入关闭二进制日志。

记录的导出和导入：

SELECT... INTO OUTFILE 导出文本文件：（mysql> SELECT * FROM testdb1.t1 INTO OUTFILE '/backup/testdb1.t1.txt';）（ FIELDS TERMINATED BY '---' //定义字段分隔符）

mysql 命令导出文本文件：

mysql -uroot -p'Qwer@123' -e 'select * from testdb1.t1' > /backup/testdb1.t2.txt

mysql -u root -p'Qwer@123' --xml -e 'select * from testdb1.t1' > /backup/testdb1.t3.txt

mysql -u root -p'Qwer@123' --html -e 'select * from testdb1.t1' > /backup/index.html

LOAD DATA INFILE 导入文本文件：

（注意目录权限）：vim /etc/my.cnf

secure-file-priv=/backup //mysql不信任该目录

chown mysql.mysql /backup //mysql用户没有权限

（DELETE FROM testdb1.t1; ） //删除表的内容，表的导出和导入只备份表记录，不会备分表结构。因此需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。

（LOAD DATA INFILE '/backup/testdb1.t1.txt'

INTO TABLE testdb1.t1;） //读取记录备份文件

（ INTO TABLE testdb1.t1; ） //输入到表中。

（select * from testdb1.t1;）

结束