mysql的全量备份和增量备份

Mysql  

全量备份

1 创建mysqldump脚本

先了解一下mysqldump的基本参数

  1. # 全备数据库(innodb Engine)
  2. mysqldump -uroot -pmysql123 --single-transaction --master-data=2  --flush-logs --flush-privileges --routines --events --all-databases > /backup/db_bak01.sql
  3. # 全备(Myisam Engine)
  4. mysqldump -uroot -pmysql123 --lock-all-tables    --master-data=2  --flush-logs --flush-privileges --routines --events --all-databases > /backup/db_bak01.sql
  5. # 只备份数据库db1和db2
  6. mysqldump -uroot -pmysql123 --single-transaction --master-data=2  --flush-logs --routines --events --databases db1 db2 > /backup/db_bak01.sql
  1. #innodb事务
  2. --single-transaction
  3. #在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
  4. #0: 不记录
  5. #1：记录CHANGE MASTER语句
  6. #2：记录但注释CHANGE MASTER语句
  7. #例子 CHANGE MASTER TO MASTER_LOG_FILE=’MySQL-bin.000002′, MASTER_LOG_POS=106;
  8. --master-data=[0][1][2]
  9. #刷新binlog日志,比如之前是mysql-bin.000004执行完flush-logs后创建mysql-bin.000005,重启数据库也会执行flush-logs
  10. -F 或 --flush-logs 
  11. #指定数据库,加了这个会生成建库的语句
  12. -B
  13. #备份存储过程等
  14. -R
  15. #锁表
  16. -x

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

  1. vim /data/mysqlbackup/mysqlBackuoShell.sh

1.2 添加内容

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

1.3给脚本赋予执行权限

  1. chmod +x  mysqlBackuoShell.sh

1.4设置cron脚本

  1. #编辑cron
  2. crontab -e
  3. #新增一行，每天凌晨3点执行一次
  4. 0 3 * * * /data/mysqlbackup/mysqlBackuoShell.sh

2.全量备份数据恢复

2.1删除旧数据库

  1. mysql> drop database 数据库名

2.2 解压备份的文件

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

2.3 恢复数据

  1. #在系统命令行中，输入如下实现还原：
  2. mysql -uroot -p12345678 < /data/mysqlbackup/city_challenge-2022-03-05.sql
  4. #在登录进入mysql系统中,通过source指令找到对应系统中的文件进行还原：
  5. mysql> use city_challenge
  6. mysql> source /data/mysqlbackup/city_challenge-2022-03-05.sql
  8. 注意一个细节:
  9. 若是mysqldump导出一个库的数据，导出文件为a.sql，然后mysql导入这个数据到新的空库下。
  10. 如果新库名和老库名不一致，那么需要将a.sql文件里的老库名改为新库名，
  11. 这样才能顺利使用mysql命令导入数据（如果使用source命令导入就不需要修改a.sql文件了）。

增量备份

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

1.1 查看binlog是否开启

  1. mysql> show variables like '%log_bin%';
  2. +---------------------------------+-------+
  3. | Variable_name                   | Value |
  4. +---------------------------------+-------+
  5. | log_bin                         | OFF   |
  6. | log_bin_basename                |       |
  7. | log_bin_index                   |       |
  8. | log_bin_trust_function_creators | OFF   |
  9. | log_bin_use_v1_row_events       | OFF   |
  10. | sql_log_bin                     | ON    |
  11. +---------------------------------+-------+
  12. 6 rows in set (0.01 sec)

如果没开启

1.2 编辑my.cnf

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

1.3 重启数据库

  1. service mysqld restart
  3. #然后重新查看日志状态
  4. mysql> show variables like '%log_bin%';
  5. +---------------------------------+--------------------------------+
  6. | Variable_name                   | Value                          |
  7. +---------------------------------+--------------------------------+
  8. | log_bin                         | ON                             |
  9. | log_bin_basename                | /var/lib/mysql/mysql-bin       |
  10. | log_bin_index                   | /var/lib/mysql/mysql-bin.index |
  11. | log_bin_trust_function_creators | OFF                            |
  12. | log_bin_use_v1_row_events       | OFF                            |
  13. | sql_log_bin                     | ON                             |
  14. +---------------------------------+--------------------------------+
  15. 6 rows in set (0.00 sec)
  17. #发现已开启binlog
  18. #/var/lib/mysql/mysql-bin是日志文件
  19. #/var/lib/mysql/mysql-bin.index是索引

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

  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+-------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +------------------+----------+--------------+------------------+-------------------+
  5. | mysql-bin.000004 |      154 |              |                  |                   |
  6. +------------------+----------+--------------+------------------+-------------------+
  7. 1 row in set (0.00 sec)
  9. #mysql-bin是日志文件
  10. #000004是索引
  11. #pos=154是事件位置也可以叫偏移值

1.5如何实现增量备份

比如上面的每天凌晨3点执行一次全量备份,并且刷新一次binlog(–flush-logs),之前是 mysql-bin.000004,执行
flush-logs后变成 mysql-bin.000005,那么mysql-bin.000005,中之中就是当日新增的数据

2.一次增量备份

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

  1. #查询binlog
  2. mysql> show master logs;
  3. +------------------+-----------+
  4. | Log_name         | File_size |
  5. +------------------+-----------+
  6. | mysql-bin.000001 |      7083 |
  7. | mysql-bin.000002 |       201 |
  8. | mysql-bin.000003 |       201 |
  9. | mysql-bin.000004 |       467 |
  10. | mysql-bin.000005 |      1265 |
  11. | mysql-bin.000006 |       201 |
  12. +------------------+-----------+
  13. 6 rows in set (0.00 sec)
  15. #全量备份test库,并刷新binlog
  16. mysqldump -u root -p --master-data=2 --single-transaction -F -B test > /data/mysqlbackup/test5.sql;
  18. #再次查询binlog
  19. mysql> show master logs;
  20. +------------------+-----------+
  21. | Log_name         | File_size |
  22. +------------------+-----------+
  23. | mysql-bin.000001 |      7083 |
  24. | mysql-bin.000002 |       201 |
  25. | mysql-bin.000003 |       201 |
  26. | mysql-bin.000004 |       467 |
  27. | mysql-bin.000005 |      1265 |
  28. | mysql-bin.000006 |       201 |
  29. | mysql-bin.000007 |       154 |
  30. +------------------+-----------+
  31. 7 rows in set (0.00 sec)
  33. #多出了一条
  34. #mysql-bin.000007

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

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

2.3 下午5点把数据库删了

  1. mysql> drop database test;

2.4 如何恢复数据库?

  1. #查看最新的mysql-bin.00000?*
  2. mysql> show master status;
  3. +------------------+----------+--------------+------------------+-------------------+
  4. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  5. +------------------+----------+--------------+------------------+-------------------+
  6. | mysql-bin.000007 |      1109 |              |                  |                   |
  7. +------------------+----------+--------------+------------------+-------------------+
  8. 1 row in set (0.00 sec)
  10. #日志是mysql-bin.000007,pos是1109
  12. #接着刷新日志 flush logs;
  13. mysql> flush logs;
  15. #查看是否已刷新
  16. mysql> show master logs;
  17. +------------------+-----------+
  18. | Log_name         | File_size |
  19. +------------------+-----------+
  20. | mysql-bin.000001 |      7083 |
  21. | mysql-bin.000002 |       201 |
  22. | mysql-bin.000003 |       201 |
  23. | mysql-bin.000004 |       467 |
  24. | mysql-bin.000005 |      1265 |
  25. | mysql-bin.000006 |       201 |
  26. | mysql-bin.000007 |      1156 |
  27. | mysql-bin.000008 |       154 |
  28. +------------------+-----------+
  29. 8 rows in set (0.00 sec)
  31. #接着新的日志就会输出到mysql-bin.000008上,以便我们分析mysql-bin.000007的数据

2.4.1查看mysql-bin.000007

方式1

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

mysql-bin.000007文件部分内容

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

方式2

  1. mysql>show binlog events in 'mysql-bin.000007'\G;
  2. +------------------+------+----------------+-----------+-------------+---------------------------------------+
  3. | Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
  4. +------------------+------+----------------+-----------+-------------+---------------------------------------+
  5. | mysql-bin.000007 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
  6. | mysql-bin.000007 |  123 | Previous_gtids |         1 |         154 |                                       |
  7. | mysql-bin.000007 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
  8. | mysql-bin.000007 |  219 | Query          |         1 |         291 | BEGIN                                 |
  9. | mysql-bin.000007 |  291 | Table_map      |         1 |         342 | table_id: 156 (test.user)             |
  10. | mysql-bin.000007 |  342 | Write_rows     |         1 |         389 | table_id: 156 flags: STMT_END_F       |
  11. | mysql-bin.000007 |  389 | Xid            |         1 |         420 | COMMIT /* xid=7033 */                 |
  12. | mysql-bin.000007 |  420 | Anonymous_Gtid |         1 |         485 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
  13. | mysql-bin.000007 |  485 | Query          |         1 |         557 | BEGIN                                 |
  14. | mysql-bin.000007 |  557 | Table_map      |         1 |         608 | table_id: 156 (test.user)             |
  15. | mysql-bin.000007 |  608 | Write_rows     |         1 |         655 | table_id: 156 flags: STMT_END_F       |
  16. | mysql-bin.000007 |  655 | Xid            |         1 |         686 | COMMIT /* xid=7043 */                 |
  17. | mysql-bin.000007 |  686 | Anonymous_Gtid |         1 |         751 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
  18. | mysql-bin.000007 |  751 | Query          |         1 |         823 | BEGIN                                 |
  19. | mysql-bin.000007 |  823 | Table_map      |         1 |         874 | table_id: 156 (test.user)             |
  20. | mysql-bin.000007 |  874 | Write_rows     |         1 |         921 | table_id: 156 flags: STMT_END_F       |
  21. | mysql-bin.000007 |  921 | Xid            |         1 |         952 | COMMIT /* xid=7046 */                 |
  22. | mysql-bin.000007 |  952 | Anonymous_Gtid |         1 |        1017 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
  23. | mysql-bin.000007 | 1017 | Query          |         1 |        1109 | drop database test                    |
  24. | mysql-bin.000007 | 1109 | Rotate         |         1 |        1156 | mysql-bin.000008;pos=4                |
  25. +------------------+------+----------------+-----------+-------------+---------------------------------------+
  26. 20 rows in set (0.00 sec)

找到问题pos在1017~1109之间

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

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

  1. mysql -uroot -p  < test5.sql

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

  1. mysql> show databases;    
  2. +--------------------+
  3. | Database           |
  4. +--------------------+
  5. | information_schema |
  6. | city_challenge     |
  7. | ds_0               |
  8. | ds_1               |
  9. | mysql              |
  10. | performance_schema |
  11. | sys                |
  12. | test               |
  13. +--------------------+
  14. 8 rows in set (0.00 sec)

2.恢复今天删库之前的数据
方式一:

  1. mysqlbinlog --stop-position=1017 -d test  mysql-bin.000007 | mysql -uroot -p123456
  3. #常用参数选项解释：
  4. #--start-position=875 起始pos点
  5. #--stop-position=954 结束pos点
  6. #--start-datetime="2016-9-25 22:01:08" 起始时间点
  7. #--stop-datetime="2019-9-25 22:09:46" 结束时间点
  8. #--skip-gtids是忽略GTIDs报错
  9. #-d 指定数据库
原理:实际是将读出的binlog日志内容，通过管道符传递给mysql命令

方式二:

  1. mysqlbinlog --stop-position=1017 -d test  mysql-bin.000007 > testa.sql
  3. mysql -uroot -p12345678 < /data/mysqlbackup/testa.sql
原理:参照全量备份

文章转载：mysql的全量备份和增量备份



评论 0

发表评论

点击排行

标签云

友情链接

Top