mysql数据备份与恢复

1. 完全备份

  • 完全备份是对整个数据库的备份、数据库结构和文件结构的备份

  • 完全备份保存的是备份完成时刻的数据库

  • 完全备份是增量备份的基础

1.1 物理冷备份与恢复

1.1.1 关闭数据库，打包备份

systemctl stop mysqld
mkdir /backup
tar zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

1.1.2 恢复数据库

将备份数据mv成线上库文件夹

mkdir /bak
mv /usr/local/mysql/data/ /bak/
mkdir restore
tar zxvf /backup/mysql_all-2020-12-24.tar.gz -C restore/
mv restore/usr/local/mysql/data/ /usr/local/mysql/
systemctl start mysqld

1.2 mysqldump逻辑备份

1.2.1 备份数据库

复制代码
#备份所有库
mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
#mysqldump -u root -p --opt --all-databases > /backup/all.sql
#--opt是mysqldump命令的一个选项，表示使用优化选项进行备份。具体来说，它等价于以下几个选项的组合：

  • --add-drop-table: 在每个表的创建语句之前添加 DROP TABLE IF EXISTS 语句，确保在恢复数据时可以先删除已存在的表。

  • --add-locks: 在备份期间为每个表添加 LOCK TABLES 语句，确保备份的数据一致性。

  • --create-options: 在备份期间包含表的创建选项，例如表的字符集、存储引擎等。

  • --quick: 使用快速插入方式，将每个表的数据记录一条 SQL 语句，而不是每行记录生成一条 SQL 语句。

  • --extended-insert: 使用扩展插入方式，将多个记录组合成一条 INSERT 语句，减少了语句本身的开销。

  • --lock-tables: 在备份期间锁定所有的表，确保备份的数据一致性。

 

#备份多个库
mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2] ...  > /备份路径/备份文件名
#mysqldump -uroot -pabc123 --databases school name > /opt/school-name.sql     //同时备份school和name库

#备份单个库
mysqldump -u 用户名 -p [密码] [选项] [库名] > /备份路径/备份文件名
#mysqldump -uroot -pabc123 school > /opt/school.sql
复制代码

1.2.2 备份表

mysqldump -u 用户名 -p [密码] [选项] 数据库名 表名 > /备份路径/备份文件名
#mysqldump -uroot -pabc123 school cd > /backup/school-cd.sql

1.2.3 -F参数和--set-gtid-purged=OFF

不建议使用-F

mysqldump -u username -p -F --databases database1 database2 > backup.sql

使用mysqldump命令备份数据时，如果加上 -F选项，会导致 MySQL Server 刷新所有的日志文件，包括 binlog 日志。这意味着，在备份过程中，发生的所有数据变更都将被记录在 binlog 中。

具体来说，当使用 -F 选项时，MySQL Server 会执行以下操作：

  1. 执行 FLUSH TABLES WITH READ LOCK 命令，阻塞所有的写操作，确保备份期间不会有新的数据写入。

  2. 执行 FLUSH LOGS 命令，刷新所有的日志文件，包括 binlog、redo log 和 error log 等。

  3. 执行 UNLOCK TABLES 命令，释放表锁，允许其他用户进行写操作。

--set-gtid-purged=OFF可以在导出sql中没有指定gtid语句（SET @@GLOBAL.GTID_PURGED=），避免后续恢复binlog中uuid与数据库uuid不一致造成的问题。（查看本机uuid:select @@server_uuid;）

如果备份没用此参数，参考2.3.1也可以解决后续问题

1.2.3 数据恢复

sql备份文件里没有判断库的语句,所以要先创建库,再导入表。

但是备份多个库的时候,备份的sql语句包含了库的判断,所以不用提前创建库

mysql> source /backup/school-cd.sql;     //使用绝对路径
'//source命令在mysql库中使用'

或者

mysql -u 用户名 -p [密码] < 表备份脚本的路径
#mysql -u root -p mysql < /backup/school-cd.sql

1.2.4 恢复故障处理

故障1：ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

复制代码
这个错误通常是由于 GTID（全局事务标识）配置不一致导致的。GTID 是用于在主从复制环境中跟踪事务的全局唯一标识符。在进行数据恢复时，MySQL 会检查新的 GTID 集合是否与已经执行的 GTID 集合有重叠。

解决此问题的方法取决于你的具体情况：

如果你在执行备份时使用了 --single-transaction 或 --master-data 选项，那么备份文件中应该包含了 GTID 相关的信息。在恢复数据时，你需要确保 MySQL 实例的 GTID 模式和备份文件中的 GTID 配置一致。

如果你的 MySQL 实例的 GTID 模式为 OFF，则可以在连接 MySQL 后执行 SET GLOBAL GTID_MODE = OFF_PERMISSIVE;，然后再执行恢复命令。

如果你的 MySQL 实例的 GTID 模式为 ON，则可以在连接 MySQL 后执行 SET GLOBAL GTID_MODE = ON_PERMISSIVE;，然后再执行恢复命令。

如果你没有使用 GTID 相关的选项进行备份，而备份文件中引起冲突的是 GTID 相关的 SQL 语句，你可以尝试在执行恢复命令之前先禁用 GTID 功能。

连接到 MySQL 后，执行 SET GLOBAL gtid_mode = OFF; 命令来禁用 GTID 功能。

然后再执行恢复命令：mysql -uroot -pAa.1234567 < /tmp/backupall.sql

请注意，禁用 GTID 功能可能会导致主从复制环境的不一致性。如果你是在主从复制环境中进行数据恢复，请谨慎操作，并在完成恢复后重新启用 GTID 功能以确保数据同步的正确性。
复制代码

 

2. 增量备份

增量备份就是备份自上一次备份之后增加或变化的文件或者内容

mysql没有提供直接的增量备份方法，这里就是通过mysql的binlog日志实现增量备份，

当数据库发生变化时，binlog会记录数据库中的所有变化；需要恢复的时候可以根据binlog中的开始位置和结束位置还原本部分操作；结束位置一般是数据被破坏或删除之前的位置。

2.1 确定已开启binlog

[root@localhost ~]# cat /etc/my.cnf

log-bin=mysql-bin            //开启binlog
server-id=1
#默认保存路径是/usr/local/mysql/data
mysql > show variables like ‘%log_bin%’;

2.2 备份binlog

1.2.3步骤中有描述-F参数，不建议使用。最好找个夜间没有变动的时间进行全量备份和flush logs;

在完全备份后执行flush logs;来生成新binlog文件，这样可以保证新log文件是基于完全备份后的增量数据；

在增量备份时执行flush logs;来生成新binlog文件，这样可以保证备份的旧log文件是完整的增量数据。否则可能备份binlog003后还一直向003追加日志

mysqladmin -u 用户名 -p [密码] flush logs
#flush logs作用是关闭当前使用的binary log，然后打开一个新的binary log文件，文件的序号加1。
cp备份文件

2.3 增量备份恢复

先根据全量备份文件恢复，在根据binlog进行增量恢复

2.3.1 查看binlog文件

  • 根据日志来确定恢复的开始位置和结束位置
  • 确定日志中gtid和数据库show master status的gtid差异(查看本机uuid:select @@server_uuid;)

注意：若gtid中uuid不一致，正常会导致恢复失败。因为binlog中指定了gtid与本机记录的gtid中uuid不一致，所以执行忽略。

           若gtid中事务不一致，当mysql事务大于binlog事务时，会判断部分binlog事务已被执行而跳过造成数据缺失

                                              当mysql事务小于binlog事务时，中间缺失部分有可能造成数据缺失

以上问题可以通过--set-gtid-purged=OFF参数或者reset master解决，根据具体情况分析选择解决方法。

增加参数--set-gtid-purged=OFF，binlog解析的语句中将不再有SET @@SESSION.GTID_NEXT=指定具体事务。

                           可以让按顺序执行所有语句，根据本机uuid和事务从1开始记录；

                           并可以忽略binlog事务数和mysql事务数大小对比，执行binlog语句，在Executed_Gtid_Set中对应的uuid事务数记录递增

 

#将binlog二进制文件转成sql,方便查询具体位置
[root@mysql data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/bak.txt
[root@mysql data]# vim /opt/bak.txt
复制代码
......
BEGIN
/*!*/;
# at 293
#201224 19:38:38 server id 1  end_log_pos 346 CRC32 0xf7359359  Table_map: `school`.`cd` mapped to number 222
# at 346
#201224 19:38:38 server id 1  end_log_pos 396 CRC32 0xc34a1362  Write_rows: table id 222 flags: STMT_END_F
### INSERT INTO `school`.`cd`
### SET
###   @1=3
###   @2='wangwu'
###   @3='20'
# at 396
#201224 19:38:38 server id 1  end_log_pos 427 CRC32 0xa5b658aa  Xid = 173
COMMIT/*!*/;
# at 427
#201224 19:39:32 server id 1  end_log_pos 492 CRC32 0x812226d5  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 492
#201224 19:39:32 server id 1  end_log_pos 566 CRC32 0x61231674  Query   thread_id=17    exec_time=0     error_code=0
SET TIMESTAMP=1608809972/*!*/;
BEGIN
/*!*/;
# at 566
#201224 19:39:32 server id 1  end_log_pos 619 CRC32 0x7a6e973e  Table_map: `school`.`cd` mapped to number 222
# at 619
#201224 19:39:32 server id 1  end_log_pos 671 CRC32 0x24435a14  Delete_rows: table id 222 flags: STMT_END_F
### DELETE FROM `school`.`cd`
### WHERE
###   @1=1
###   @2='zhangsan'
###   @3='18'
# at 671
#201224 19:39:32 server id 1  end_log_pos 702 CRC32 0xc9db2a9b  Xid = 174
COMMIT/*!*/;
# at 702
#201224 19:40:19 server id 1  end_log_pos 767 CRC32 0x4ae2a49c  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 767
#201224 19:40:19 server id 1  end_log_pos 841 CRC32 0xa41b163a  Query   thread_id=17    exec_time=0     error_code=0
SET TIMESTAMP=1608810019/*!*/;
BEGIN
/*!*/;
# at 841
#201224 19:40:19 server id 1  end_log_pos 894 CRC32 0x2c22cb13  Table_map: `school`.`cd` mapped to number 222
# at 894
#201224 19:40:19 server id 1  end_log_pos 946 CRC32 0xfe387271  Write_rows: table id 222 flags: STMT_END_F
### INSERT INTO `school`.`cd`
### SET
###   @1=5
###   @2='zhangliu'
###   @3='21'
# at 946
#201224 19:40:19 server id 1  end_log_pos 977 CRC32 0x40cd330a  Xid = 175
COMMIT/*!*/;
# at 977
#201224 19:42:30 server id 1  end_log_pos 1024 CRC32 0x7096e131         Rotate to mysql-bin.000003  pos: 4
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*/;
......


复制代码


注意：时间点201224 19:38:38 对应的是 2020-12-24 19:38:38'


          此案例误操作时间是201224 19:39:32 delete语句，位置点是at 671 (已标红)


2.3.2 基于时间点的断点恢复




mysqlbinlog --no-defaults --stop-datetime='2020-12-24 19:38:38' /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -pabc123
'//第一个正常操作的结尾时间是'2020-12-24 19:38:38'，此处用此时间，表示恢复到此时间的操作'
#mysqlbinlog --no-defaults --skip-gtids --stop-datetime='2020-12-24 19:38:38' /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -pabc123
#--skip-gtids是'开启跳过gtids',效果看2.3.1






mysqlbinlog --no-defaults --start-datetime='2020-12-24 19:40:19' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -pabc123
'//跳过中间失误操作失误操作后的正常操作的开始时间是'2020-12-24 19:40:19'，此处用此时间，表示从此时间恢复到结尾的操作'
mysqlbinlog --no-defaults --skip-gtids --start-datetime='2020-12-24 19:40:19' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -pabc123
#--skip-gtids是'开启跳过gtids',效果看2.3.1




语句中start-datetime和stop-datetime可以同时指定，如果未指定就是binlog的最开始或最结尾


2.3.3 基于位置点的断点恢复




mysqlbinlog --no-defaults --stop-position='619' /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -pabc123
//这里619对应误操作671的上一个位置点,这里千万不要写671,写了671这个位置,就说明把这个位置的语句执行一遍;
#mysqlbinlog --no-defaults --skip-gtids --stop-position='619' /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -pabc123
#--skip-gtids是'开启跳过gtids',效果看2.3.1






mysqlbinlog --no-defaults --skip-gtids --start-position='767' /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -pabc123
//767位置点为误操作的下一个位置点
#mysqlbinlog --no-defaults --start-position='767' /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -pabc123
#--skip-gtids是'开启跳过gtids',效果看2.3.1




或者 mysqlbinlog --no-defaults --skip-gtids --start-position='767' /usr/local/mysql/data/mysql-bin.000001 > 1.sql 后source执行也可以


语句中start-position和stop-position可以同时指定，如果未指定就是binlog的最开始或最结尾






            

            
posted @ 
 
阿锋888 
阅读(46) 
评论(0) 
编辑 
收藏 
举报


        

	    
	    
    









    
    

    
    
（评论功能已被禁用）

    

        
    

        

            
                
                
            
        

    



    相关博文：

    

            

                ·
                mgr--mysql-故障恢复
            

            

                ·
                mysql-MGR单主部署-8.0版本
            

            

                ·
                mysql备份实战
            

            

                ·
                mysql数据库备份与恢复
            

            

                ·
                mysql备份与恢复
            

    





    

    阅读排行：
    

 ·          被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
        

 ·          【自荐】一款简洁、开源的在线白板工具 Drawnix
        

 ·          没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
        

 ·          园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
        

 ·          无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
        



    

    



	

	

	
	

	

	

	





    

    
    








  

  
点击右上角即可分享

  微信分享提示