[大数据] mysql的全量备份和增量备份

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> mysql的全量备份和增量备份 -> 正文阅读

[大数据]mysql的全量备份和增量备份

文章目录

全量备份

1 创建mysqldump脚本

先了解一下mysqldump的基本参数

1. 全备数据库(innodb Engine)
mysqldump -uroot -pmysql123 --single-transaction --master-data=2  --flush-logs --flush-privileges --routines --events --all-databases > /backup/db_bak01.sql
2. 全备(Myisam Engine)
mysqldump -uroot -pmysql123 --lock-all-tables    --master-data=2  --flush-logs --flush-privileges --routines --events --all-databases > /backup/db_bak01.sql
3. 只备份数据库db1和db2
mysqldump -uroot -pmysql123 --single-transaction --master-data=2  --flush-logs --routines --events --databases db1 db2 > /backup/db_bak01.sql

#innodb事务
--single-transaction
#在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
#0: 不记录
#1：记录CHANGE MASTER语句
#2：记录但注释CHANGE MASTER语句
#例子 CHANGE MASTER TO MASTER_LOG_FILE=’MySQL-bin.000002′, MASTER_LOG_POS=106;
--master-data=[0][1][2]
#刷新binlog日志,比如之前是mysql-bin.000004执行完flush-logs后创建mysql-bin.000005,重启数据库也会执行flush-logs
-F 或 --flush-logs 
#指定数据库,加了这个会生成建库的语句
-B
#备份存储过程等
-R
#锁表
-x

1.1 找一个目录,这里选择放到/data/mysqlbackup下,创建shell脚本

vim /data/mysqlbackup/mysqlBackuoShell.sh

1.2 添加内容

#!/bin/bash 
   
#保存备份个数,31条 
number=31 
#备份保存路径 
backup_dir=/data/mysqlbackup/mysqlbackupdata 
#日期 
dd=`date +%Y-%m-%d`
#备份工具 
tool=mysqldump 
#用户名 
username=用户名 
#密码 
password=密码
#将要备份的数据库 
database_name=city_challenge
   
#运行内容,结果用gzip压缩下
$tool -u $username -p$password --single-transaction --master-data=2  $database_name|gzip > $backup_dir/$database_name-$dd.sql.gz 
   
#写创建备份日志 
echo "create $backup_dir/$database_name-$dd.sql.gz" >> $backup_dir/log.txt 
   
#找出需要删除的备份 
delfile=`ls -l -crt  $backup_dir/*.sql.gz | awk '{print $9 }' | head -1` 
   
#判断现在的备份数量是否大于$number 
count=`ls -l -crt  $backup_dir/*.sql.gz | awk '{print $9 }' | wc -l` 
   
if [ $count -gt $number ] 
then 
  rm $delfile  //删除最早生成的备份，只保留number数量的备份 
  #写删除文件日志 
  echo "delete $delfile" >> $backup_dir/log.txt 
fi

1.3给脚本赋予执行权限

chmod +x  mysqlBackuoShell.sh

2.crontab

crontab是一个 Liunx 下的定时执行工具，可以在无需人工干预的情况下运行作业。

//启动服务
service crond start 
//关闭服务
 service crond stop  
//重启服务
service crond restart  
//重新载入配置
service crond reload  
//查看服务状态
service crond status

crontab语法

crontab命令用于安装、删除或者列出用于驱动cron后台进程的表格。用户把需要执行的命令序列放到crontab文件中以获得执行。每个用户都可以有自己的crontab文件。/var/spool/cron下的crontab文件不可以直接创建或者直接修改。该crontab文件是通过crontab命令创建的。

在crontab文件中如何输入需要执行的命令和时间。该文件中每行都包括六个域，其中前五个域是指定命令被执行的时间，最后一个域是要被执行的命令。每个域之间使用空格或者制表符分隔。

2.1查看cron状态

service crond status

没启动就

service crond start

2.1创建crontab脚本

位置随便,这里和shell脚本放在一块了

vim /data/mysqlbackup/mysqlRollBack.cron

2.2 编辑,每天凌晨3点执行一次

crontab在线工具~~注意和java的cron不一样

0 3 * * * /data/mysqlbackup/mysqlBackuoShell.sh

3.3 执行定时任务命令

crontab mysqlRollBack.cron

2.4 查看定时任务是否成功或者检测/var/spool/cron下是否生成对应cron脚本

crontab -l

[root@node-test01 /data 18:04:43]#crontab -l
0 3 * * * /data/mysqlbackup/mysqlBackuoShell.sh

2.5 删除定时任务

如果不想执行定时任务,直接删除就行了

crontab -r

2.6编辑定时任务

如果想要修改执行时间,可以编辑

crontab -e

3.数据恢复

注意,这里只是做了全量备份

3.1删除旧数据库

mysql> drop database 数据库名

3.2 解压备份的文件

gzip -d city_challenge-2022-03-05.sql.gz

3.3 恢复数据

1.在系统命令行中，输入如下实现还原：

mysql -uroot -p12345678 < /data/mysqlbackup/city_challenge-2022-03-05.sql

2.在登录进入mysql系统中,通过source指令找到对应系统中的文件进行还原：

mysql>use city_challenge
mysql> source /data/mysqlbackup/city_challenge-2022-03-05.sql

注意一个细节:
若是mysqldump导出一个库的数据，导出文件为a.sql，然后mysql导入这个数据到新的空库下。
如果新库名和老库名不一致，那么需要将a.sql文件里的老库名改为新库名，
这样才能顺利使用mysql命令导入数据（如果使用source命令导入就不需要修改a.sql文件了）。

增量备份

1.开启mysql的binlog日志(增量备份)

1.1 查看binlog是否开启

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.01 sec)

如果没开启

1.2 编辑my.cnf

vim /etc/my.cnf

增加

#节点Id,注意集群中不能重复,单节点不配置也可以
server-id=123
#开启binlog日志,指定其存放位置
log-bin=/var/lib/mysql/mysql-bin
#开启binlog自动过期
expire_logs_days=3

1.3 重启数据库

service mysqld restart

然后

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

发现已开启binlog

/var/lib/mysql/mysql-bin是日志文件

/var/lib/mysql/mysql-bin.index是索引

1.4 查看日志索引和事件位置

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql-bin是日志文件

000004是索引

pos=154是事件位置也可以叫偏移值

1.5如何实现增量备份

比如上面的每天凌晨3点执行一次全量备份,并且刷新一次binlog(–flush-logs),之前是 mysql-bin.000004,执行

flush-logs后变成 mysql-bin.000005,那么mysql-bin.000005,中之中就是当日新增的数据

2.一次增量备份

2.1凌晨3点进行一次全量备份

查询binlog

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      7083 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       467 |
| mysql-bin.000005 |      1265 |
| mysql-bin.000006 |       201 |
+------------------+-----------+
6 rows in set (0.00 sec)

#全量备份test库,并刷新binlog
mysqldump -u root -p --master-data=2 --single-transaction  -F -B    test > /data/mysqlbackup/test5.sql;

再次查询binlog

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      7083 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       467 |
| mysql-bin.000005 |      1265 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       154 |
+------------------+-----------+
7 rows in set (0.00 sec)

多出了一条

mysql-bin.000007

2.2 早上9点向test.user中插入三条数据

mysql>INSERT INTO user ( id, age,name ) VALUES  ( 5, 5, '5' );
mysql>INSERT INTO user ( id, age,name ) VALUES  ( 6, 6, '6' );
mysql>INSERT INTO user ( id, age,name ) VALUES  ( 7, 7, '7' );

2.3 下午5点把数据库删了_>

mysql> drop database test;

2.4 如何恢复数据库?

查看最新的mysql-bin.00000?*

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      1109 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

日志是mysql-bin.000007,pos是1109

接着刷新日志 flush logs;

mysql> flush logs;

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      7083 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       467 |
| mysql-bin.000005 |      1265 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |      1156 |
| mysql-bin.000008 |       154 |
+------------------+-----------+
8 rows in set (0.00 sec)

接着新的日志就会输出到mysql-bin.000008上,以便我们分析mysql-bin.000007的数据

查看mysql-bin.000007

方式1

#-vvv :查看sql,方便判断pos点
#--base64-output=decode-rows :  base64编码的数据转成row(没看到有啥用)
mysqlbinlog -vvv --base64-output=decode-rows   mysql-bin.000007

部分

/*!*/;
# at 823
#220307 14:32:40 server id 1  end_log_pos 874 CRC32 0x6a5153c0 	Table_map: `test`.`user` mapped to number 156
# at 874
#220307 14:32:40 server id 1  end_log_pos 921 CRC32 0x9df18a8f 	Write_rows: table id 156 flags: STMT_END_F
### INSERT INTO `test`.`user`
### SET
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2=7 /* INT meta=0 nullable=1 is_null=0 */
###   @3='7' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
# at 921
#220307 14:32:40 server id 1  end_log_pos 952 CRC32 0xaa3f5c7b 	Xid = 7046
COMMIT/*!*/;
# at 952
#220307 14:37:44 server id 1  end_log_pos 1017 CRC32 0x8914e925 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1017
#220307 14:37:44 server id 1  end_log_pos 1109 CRC32 0xb100a121 	Query	thread_id=124	exec_time=0	error_code=0
SET TIMESTAMP=1646635064/*!*/;
drop database test
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

方式2

mysql>show binlog events in 'mysql-bin.000007'\G;

mysql> show binlog events in 'mysql-bin.000007';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000007 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql-bin.000007 |  123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000007 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000007 |  219 | Query          |         1 |         291 | BEGIN                                 |
| mysql-bin.000007 |  291 | Table_map      |         1 |         342 | table_id: 156 (test.user)             |
| mysql-bin.000007 |  342 | Write_rows     |         1 |         389 | table_id: 156 flags: STMT_END_F       |
| mysql-bin.000007 |  389 | Xid            |         1 |         420 | COMMIT /* xid=7033 */                 |
| mysql-bin.000007 |  420 | Anonymous_Gtid |         1 |         485 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000007 |  485 | Query          |         1 |         557 | BEGIN                                 |
| mysql-bin.000007 |  557 | Table_map      |         1 |         608 | table_id: 156 (test.user)             |
| mysql-bin.000007 |  608 | Write_rows     |         1 |         655 | table_id: 156 flags: STMT_END_F       |
| mysql-bin.000007 |  655 | Xid            |         1 |         686 | COMMIT /* xid=7043 */                 |
| mysql-bin.000007 |  686 | Anonymous_Gtid |         1 |         751 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000007 |  751 | Query          |         1 |         823 | BEGIN                                 |
| mysql-bin.000007 |  823 | Table_map      |         1 |         874 | table_id: 156 (test.user)             |
| mysql-bin.000007 |  874 | Write_rows     |         1 |         921 | table_id: 156 flags: STMT_END_F       |
| mysql-bin.000007 |  921 | Xid            |         1 |         952 | COMMIT /* xid=7046 */                 |
| mysql-bin.000007 |  952 | Anonymous_Gtid |         1 |        1017 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000007 | 1017 | Query          |         1 |        1109 | drop database test                    |
| mysql-bin.000007 | 1109 | Rotate         |         1 |        1156 | mysql-bin.000008;pos=4                |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
20 rows in set (0.00 sec)

找到问题pos在1017~1109之间

所以将数据恢复到1017之前就可以了

1.先将昨天的全量备份恢复

mysql -uroot -p -v  <test5.sql

查看数据库,发现test已经恢复

mysql> show databases;    
+--------------------+
| Database           |
+--------------------+
| information_schema |
| city_challenge     |
| ds_0               |
| ds_1               |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
8 rows in set (0.00 sec)

2.恢复今天删库之前的数据

方式一:

mysqlbinlog --stop-position=1017 -d test  mysql-bin.000007 | mysql -uroot -p12345678

mysqlbinlog恢复数据常用参数:

常用参数选项解释：
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--skip-gtids是忽略GTIDs报错
-d 指定数据库

原理:实际是将读出的binlog日志内容，通过管道符传递给mysql命令

方式二:

mysqlbinlog --stop-position=1017 -d test  mysql-bin.000007 >testa.sql

mysql -uroot -p12345678 < /data/mysqlbackup/testa.sql

原理:参照全量备份

大数据最新文章

实现Kafka至少消费一次

亚马逊云科技：还在苦于ETL？Zero ETL的时代

初探MapReduce

【SpringBoot框架篇】32.基于注解+redis实现

Elasticsearch：如何减少 Elasticsearch 集

Go redis操作

Redis面试题

专题五 Redis高并发场景

基于GBase8s和Calcite的多数据源查询

Redis——底层数据结构原理

加:2022-03-08 22:34:18 更:2022-03-08 22:35:56

360图书馆购物三丰科技阅读网日历万年历 2024年11日历

-2024/11/24 9:08:20-

图片自动播放器
↓图片自动播放器↓

TxT小说阅读器
↓语音阅读,小说下载,古典文学↓

一键清除垃圾
↓轻轻一点,清除系统垃圾↓

图片批量下载器
↓批量下载图片,美女图库↓

网站联系: qq:121756557 email:121756557@qq.com IT数码