数据库逻辑备份

本文介绍使用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;)

结束

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值