MySQL备份与恢复

原创

小哥 2年前 (2023-05-18) 阅读数 80 #大杂烩

数据库备份的概述

备份是灾难恢复的主要目的。

数据备份的重要性

企业数据的价值是至关重要的,因为它保证了企业业务的正常运行。

程序错误,人类操作错误,算术错误,磁盘失败,灾害(火灾、地震)和盗窃。

数据库备份类型

（1从物理和逻辑的角度

物理备份 (备份数据文件)

冷备份:在数据库关闭时执行备份操作。

热备份:数据库运行时执行备份操作,这依赖于数据库的日志文件。

热备份:执行备份操作数据库锁表状态,不是可写的,而是可读。

逻辑备份 (备份库、表)

（2)从数据库备份策略的角度

完全备份:每次执行一个完整的备份的数据,也就是说,一个备份整个数据库的数据结构和文件结构。差异备份:备份所有文件修改自上次完全备份。增量备份:只有文件修改自上次完全将备份或增量备份。

常见的备份方法

（1)物理冷备份当数据库关闭时,使用tar命令来备份。

（2)专用的备份工具 mysqldump这是一个常用的逻辑备份程序为客户,可以生成一组执行复制原始的数据库对象的定义和表数据SQL句子。MySQL或转移到远程数据库,备份SQL服务器。

（3二进制日志(增量备份)

二进制日志必须启用增量备份,恢复和二进制日志文件为用户提供复制所需的信息执行数据库备份后进行的修改点。

（4)第三方工具备份 Percona XtraBackup Xtrabackup、innobackupex、xbstream。

数据库备份操作

物理冷备份和恢复

tar命令直接包数据库文件夹,需要关闭之前备份MySQL服务。

备份数据库

创建一个/backup目录备份数据存储路径,使用tar创建备份文件。

 [root@mysql ~]# systemctl stop mysqld
 [root@mysql ~]# mkdir /backup
 [root@mysql ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
 tar: Removing leading `/ from member names
 [root@mysql ~]# ll /backup/
 total 728
 -rw-r--r-- 1 root root 741611 May 13 19:48 mysql_all-2022-05-13.tar.gz

mysql_all-$(date +%F).tar.gz是一个日期函数变量

恢复数据库

将数据库文件/usr/local/mysql/data/转移到bak模拟故障在目录中。

 [root@mysql ~]# mkdir /bak
 [root@mysql ~]# mv /usr/local/mysql/data/ /bak/
 [root@mysql ~]# systemctl start mysqld
 Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

数据库损坏,不能打开。

创建restore目录和提取tar包,搬到/usr/local/mysql/ 恢复完成并成功启动。

[root@mysql ~]# mkdir restore
[root@mysql ~]# tar zxf /backup/mysql_all-2022-05-13.tar.gz -C restore/
[root@mysql ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
[root@mysql ~]# systemctl start mysqld
[root@mysql ~]#

mysqldump备份和恢复

通过mysqldump命令可以导出指定的库、表或所有库SQL脚本,以便在不同版本中使用这个命令MySQL在服务器上使用。

备份数据库

命令格式如下:

格式1:备份一些表中指定的库。

 mysqldump [选项] 库名 [表名1] [表名2] ... > /备份路径/备份文件名称

格式2:支持一个或多个完整的库(包括所有表)。

 mysqldump [选项] --databases 库名1 [库名2] ... > /备份路径/备份文件名称

格式3：备份MySQL所有图书馆的服务器。

 mysqldump [选项] --all-databases > /备份路径/备份文件名称

注意:常见的选项包括“-u”，“-p”,分别用于指定数据库用户名和密码。

例1：备份mysql库中的user当前路径名mysql-user.sql。

 [root@mysql ~]# mysqldump -u root -p mysql user > mysql-user.sql
 Enter password: 
 [root@mysql ~]#

例2:备份数据库auth当前路径名auth.sql。

 [root@mysql ~]# mysqldump -u root -p --databases auth > auth.sql
 Enter password: 
 [root@mysql ~]#

例3：备份所有库当前路径名all-data.sql当导出的数据量很大,添加“--opt选择优化的执行速度。

[root@mysql ~]# mysqldump -u root -p --all-databases --opt > all-data.sql
Enter password: 
[root@mysql ~]#

查看备份文件

通过mysqldump导出工具SQL脚本化的文本文件,使用grep,less,cat等待文本工具查看脚本的内容。

恢复数据库

命令格式如下:

 mysql [选项] [库名] [表名] < /备份路径/备份文件名称

使用mysqldump命令输出SQL备份脚本时可以使用恢复是必要的mysql命令执行导出操作。

[root@mysql ~]# mysql -u root -p test < mysql-user.sql
 Enter password:

不进mysql查询库添加"-e"选项。

[root@mysql ~]# mysql -u root -p -e show tables from test;
 Enter password: 
 +----------------+
 | Tables_in_test |
 +----------------+
 | user           |
 +----------------+

如果备份文件已经包括完整的图书馆信息,不需要指定一个数据库名称在执行导入操作。

 [root@mysql ~]# mysql -u root -p -e drop database auth;       //删除auth图书馆,模拟故障
 Enter password: 
 [root@mysql ~]# mysql -u root -p -e show databases;           //查看auth图书馆存在
 Enter password: 
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | test               |
 +--------------------+        
 [root@mysql ~]# mysql -uroot -p < auth.sql                      //执行恢复操作进口
 Enter password: 
 [root@mysql ~]# mysql -u root -p -e show databases;           //确认结果后恢复
 Enter password: 
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | auth               |
 | mysql              |
 | performance_schema |
 | test               |
 +--------------------+

MySQL增量备份和恢复

增量备份的特点

与完全备份、增量备份没有重复数据,备份容量小、短时间; 但它的复苏是繁琐的,需要最后一次完整备份和完整备份之后的所有增量备份恢复,和增量备份需要恢复一个接一个。MySQL没有直接增量备份方法,它可以通过访问MySQL提供的二进制日志介绍实现增量备份。

MySQL二进制日志备份的重要性

二进制日志存储所有可能的操作更新或更新数据库。MySQL服务器启动后记录,当达到设定的最大值二进制的文件日志或接收flush logs 命令后,创建一个新的日志文件,生成一个二进制文件序列,并迅速将这些日志保存到一个安全的存储位置增量备份在一段时间内完成。max_bibnlog_size配置项可以设置最大值的二进制日志文件,如果二进制文件的大小超过max_binlog_size它会自动创建一个新的二进制文件。

要进行MySQL先执行增量备份,启用二进制日志记录功能。mysql-bin.000001。

 [root@mysql ~]# vim /etc/my.cnf 
 文本[mysqld]项下加入
 log-bin=/usr/local/mysql/mysql-bin
 保存退出:wq
 [root@mysql ~]# ll /usr/local/mysql/mysql-bin.*
 -rw-rw---- 1 mysql mysql 120 May 14 10:31 /usr/local/mysql/mysql-bin.000001
 -rw-rw---- 1 mysql mysql  34 May 14 10:31 /usr/local/mysql/mysql-bin.index

MySQL增量备份

增量恢复常用的方法有三种:一般复苏,基于位置的复苏,和时间点恢复。

一般恢复 :恢复所有备份二进制日志内容,使用下面的命令格式。

 mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码

基于位置的复苏 :数据库管理员可能都错了,正确的操作在同一时间点上,当操作数据库。

格式1:恢复到指定的位置。

 mysqlbinlog --stop-psition=操作id 二进制日志 | mysql -u 用户名 -p 密码

格式2从指定的位置开始恢复数据。

 mysqlbinlog --start-psition=操作id 二进制日志 | mysql -u 用户名 -p 密码

两者的区别--stop-psition=操作id从开始到指定位置停止,--start-psition=操作id从指定的位置。

基于时间点的恢复 跳过某个时间点上实现数据恢复出错,而时间点恢复可分为三种情况。

格式1复苏的开始日志到某个时间点。

 mysqlbinlog [--no-defaults] --stop-datetime=年-月-日 小时:分钟:秒 二进制日志 | mysql -u 用户名 -p 密码

格式2恢复从某个时间点的日志。

 mysqlbinlog [--no-defaults] --start-datetime=年-月-日 小时:分钟:秒 二进制日志 | mysql -u 用户名 -p 密码

格式3:恢复从某个时间点到某个时间点。

 mysqlbinlog [--no-defaults] --start-datetime=年-月-日 小时:分钟:秒 --stop-datetime=年-月-日 小时:分钟:秒 二进制日志 | mysql -u 用户名 -p 密码

MySQL企业备份情况

要求:用户信息数据库的通信公司在北京client用户关税数据表为user_info, 该公司每天需要每周一次的全备份和增量备份。

一般恢复

添加数据库、表和输入信息

在执行备份之前,创建用户信息数据库现在是基于给定的要求client用户关税数据表user_info和插入数据之前的三个用户根据需求描述表。

 [root@mysql ~]# mysql -uroot -p
 Enter password: 
 
 mysql> create database client;
 Query OK, 1 row affected (0.00 sec)
 
 mysql> use client;
 Database changed
 mysql> create table user_info (sfz char(20) not null,xingming char(20) not null,xingbie char(4),yh_id char(10) not null,zifei int);
 Query OK, 0 rows affected (0.02 sec)
 
 mysql> insert user_info values (006,z3,nan,016,10);
 Query OK, 1 row affected (0.01 sec)
 
 mysql> insert user_info values (007,l4,nv,017,91);
 Query OK, 1 row affected (0.00 sec)
 
 mysql> insert user_info values (008,w5,nv,018,23);
 Query OK, 1 row affected (0.00 sec)
 
 mysql> select * from user_info;
 +-----+----------+---------+-------+-------+
 | sfz | xingming | xingbie | yh_id | zifei |
 +-----+----------+---------+-------+-------+
 | 006 | z3       | nan     | 016   |    10 |
 | 007 | l4       | nv      | 017   |    91 |
 | 008 | w5       | nv      | 018   |    23 |
 +-----+----------+---------+-------+-------+
 3 rows in set (0.00 sec)

先执行一个完整的备份

为了便于验证二进制日志的增量备份和恢复功能,我们将先插入三个用户数据client数据库的user_info表的执行完整备份。linux从命令行执行系统”mysqladmin -uroot -p123456 flush-logs“命令或mysql>在命令提示符下执行”flush logs;“生成一个新的二进制日志。

 [root@mysql ~]# mkdir /mysql_bak
 [root@mysql ~]# mysqldump -uroot -p client user_info > /mysql_bak/client_userinfo-$(date +%F).sql
 Enter password: 
 [root@mysql ~]# ls /mysql_bak/
 client_userinfo-2022-05-14.sql
 [root@mysql ~]# mysqladmin -uroot -p flush-logs
 Enter password: 
 [root@mysql ~]# ll /usr/local/mysql/mysql-bin.*
 -rw-rw---- 1 mysql mysql 1195 May 14 11:42 /usr/local/mysql/mysql-bin.000001
 -rw-rw---- 1 mysql mysql  120 May 14 11:42 /usr/local/mysql/mysql-bin.000002
 -rw-rw---- 1 mysql mysql   68 May 14 11:42 /usr/local/mysql/mysql-bin.index
 [root@mysql ~]#

继续输入新数据和执行增量备份

继续输入两个用户数据和执行”mysqladmin -uroot -p123456 flush-logs“命令刷新二进制增量备份日志。mysql-bin.000003只保留插入两个用户数据的操作。

 mysql> use client;
 Database changed
 mysql> insert into user_info values(009,z6,nan,019,37);
 Query OK, 1 row affected (0.01 sec)
 
 mysql> insert into user_info values(010,s7,nan,020,36);
 Query OK, 1 row affected (0.00 sec)
 
 mysql> select * from user_info;
 +-----+----------+---------+-------+-------+
 | sfz | xingming | xingbie | yh_id | zifei |
 +-----+----------+---------+-------+-------+
 | 006 | z3       | nan     | 016   |    10 |
 | 007 | l4       | nv      | 017   |    91 |
 | 008 | w5       | nv      | 018   |    23 |
 | 009 | z6       | nan     | 019   |    37 |
 | 010 | s7       | nan     | 020   |    36 |
 +-----+----------+---------+-------+-------+
 5 rows in set (0.00 sec)

刷新二进制日志文件副本000002日志文件/mysql_abk/下。

[root@mysql ~]# mysqladmin -uroot -p flush-logs
 Enter password: 
 [root@mysql ~]# ll /usr/local/mysql/mysql-bin.*
 -rw-rw---- 1 mysql mysql 1195 May 14 11:42 /usr/local/mysql/mysql-bin.000001
 -rw-rw---- 1 mysql mysql  661 May 14 11:50 /usr/local/mysql/mysql-bin.000002
 -rw-rw---- 1 mysql mysql  120 May 14 11:50 /usr/local/mysql/mysql-bin.000003
 -rw-rw---- 1 mysql mysql  102 May 14 11:50 /usr/local/mysql/mysql-bin.index
 [root@mysql ~]# cp /usr/local/mysql/mysql-bin.000002 /mysql_bak/

模拟误操作删除user_info表

mysql> use client Database changed mysql> drop table user_info; Query OK, 0 rows affected (0.01 sec) mysql> select * from user_info; ERROR 1146 (42S02): Table client.user_info doesnt exist mysql>
恢复操作

执行恢复操作时，需要先恢复完全备份，然后增量备份恢复。

 [root@mysql ~]# mysql -uroot -p client < /mysql_bak/client_userinfo-2022-05-14.sql   //恢复完全备份
 Enter password: 
 [root@mysql ~]# mysql -uroot -p123456 -e select * from client.user_info;
 Warning: Using a password on the command line interface can be insecure.
 +-----+----------+---------+-------+-------+
 | sfz | xingming | xingbie | yh_id | zifei |
 +-----+----------+---------+-------+-------+
 | 006 | z3       | nan     | 016   |    10 |
 | 007 | l4       | nv      | 017   |    91 |
 | 008 | w5       | nv      | 018   |    23 |
 +-----+----------+---------+-------+-------+
 [root@mysql ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root -p123456              //增量备份恢复
 Warning: Using a password on the command line interface can be insecure.    //显示明文密码提示,而不是一个错误消息。
 [root@mysql ~]# mysql -uroot -p123456 -e select * from client.user_info;
 Warning: Using a password on the command line interface can be insecure.
 +-----+----------+---------+-------+-------+
 | sfz | xingming | xingbie | yh_id | zifei |
 +-----+----------+---------+-------+-------+
 | 006 | z3       | nan     | 016   |    10 |
 | 007 | l4       | nv      | 017   |    91 |
 | 008 | w5       | nv      | 018   |    23 |
 | 009 | z6       | nan     | 019   |    37 |
 | 010 | s7       | nan     | 020   |    36 |
 +-----+----------+---------+-------+-------+
 [root@mysql ~]#

基于位置的复苏

我们已经做过完整的备份,现在我们将直接模拟断层和数据恢复的操作。

 mysql> drop table user_info;        //模拟误操作删除user_info表
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> select * from user_info;         //查看user_info表是否存在
 ERROR 1146 (42S02): Table client.user_info doesnt exist
 mysql>

恢复完全备份

[root@mysql ~]# mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2022-05-14.sql

基于位置或时间点恢复数据,您必须首先确定复苏的位置或时间点通过查看二进制日志文件。mysqlbinlog --no-defaults 二进制日志文件”的具体内容,您可以查看二进制日志文件。

[root@mysql ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 # at 4
 #220514 11:42:24 server id 1  end_log_pos 120 CRC32 0x23249400  Start: binlog v 4, server v 5.6.36-log created 220514 11:42:24
 BINLOG 
 ICV/Yg8BAAAAdAAAAHgAAAAAAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQCU
 JCM=
 /*!*/;
 # at 120
 #220514 11:48:54 server id 1  end_log_pos 203 CRC32 0xb9d87fde  Query   thread_id=3 exec_time=0 error_code=0
 SET TIMESTAMP=1652500134/*!*/;
 SET @@session.pseudo_thread_id=3/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=1075838976/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!C utf8 *//*!*/;
 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
 SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 # at 203
 #220514 11:48:54 server id 1  end_log_pos 336 CRC32 0x9792a97e  Query   thread_id=3 exec_time=0 error_code=0
 use client/*!*/;
 SET TIMESTAMP=1652500134/*!*/;
 insert into user_info values(009,z6,nan,019,37)
 /*!*/;
 # at 336
 #220514 11:48:54 server id 1  end_log_pos 367 CRC32 0x6d86b97d  Xid = 47
 COMMIT/*!*/;
 # at 367
 #220514 11:49:13 server id 1  end_log_pos 450 CRC32 0x2c737037  Query   thread_id=3 exec_time=0 error_code=0
 SET TIMESTAMP=1652500153/*!*/;
 BEGIN
 /*!*/;
 # at 450
 #220514 11:49:13 server id 1  end_log_pos 583 CRC32 0x3802a753  Query   thread_id=3 exec_time=0 error_code=0
 SET TIMESTAMP=1652500153/*!*/;
 insert into user_info values(010,s7,nan,020,36)
 /*!*/;
 # at 583
 #220514 11:49:13 server id 1  end_log_pos 614 CRC32 0x95a2322d  Xid = 48
 COMMIT/*!*/;
 # at 614
 #220514 11:50:17 server id 1  end_log_pos 661 CRC32 0xe2d21e76  Rotate to mysql-bin.000003  pos: 4
 DELIMITER ;
 # End of log file
 ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

查看日志文件的具体内容,它可以发现,每个操作前有一个惟一的编号,如“# at 450我们叫它操作id。在操作id下一个时间戳,比如“#220514 11:49:13” 实现数据恢复。基于位置或时间点,需要依靠单独的二进制日志文件id或时间戳。

例如:从二进制日志文件,众所周知,在操作期间id为“450“在那个时候,user_info插入用户数据表中的“太阳气”。id为“450”以前的数据,即没有恢复”孙七”信息。这时所恢复的数据是从二进制日志文件的开始位置到指定位置。

[root@mysql ~]# mysqlbinlog --no-defaults --stop-position=450 /mysql_bak/mysql-bin.000002 | mysql -uroot -p
 Enter password: 
 [root@mysql ~]# mysql -uroot -p123456 -e select * from client.user_info;
 Warning: Using a password on the command line interface can be insecure.
 +-----+----------+---------+-------+-------+
 | sfz | xingming | xingbie | yh_id | zifei |
 +-----+----------+---------+-------+-------+
 | 006 | z3       | nan     | 016   |    10 |
 | 007 | l4       | nv      | 017   |    91 |
 | 008 | w5       | nv      | 018   |    23 |
 | 009 | z6       | nan     | 019   |    37 |
 +-----+----------+---------+-------+-------+

在上面的操作命令,"--stop-position"指定的位置停止。"--start-position"选项指定数据的位置开始复苏。

首先,模拟故障和恢复完全备份指定的位置s7开始复苏。

 [root@mysql ~]# mysql -uroot -p123456 -e drop table client.user_info;
 [root@mysql ~]# mysql -uroot -p client < /mysql_bak/client_userinfo-2022-05-14.sql 
 Enter password: 
 [root@mysql ~]# mysqlbinlog --no-defaults --start-position=450 /mysql_bak/mysql-bin.000002 | mysql -uroot -pEnter password: 
 [root@mysql ~]# mysql -uroot -p123456 -e select * from client.user_info;
 Warning: Using a password on the command line interface can be insecure.
 +-----+----------+---------+-------+-------+
 | sfz | xingming | xingbie | yh_id | zifei |
 +-----+----------+---------+-------+-------+
 | 006 | z3       | nan     | 016   |    10 |
 | 007 | l4       | nv      | 017   |    91 |
 | 008 | w5       | nv      | 018   |    23 |
 | 010 | s7       | nan     | 020   |    36 |
 +-----+----------+---------+-------+-------+

时间点恢复

数据”的选项用于时间点恢复--stop-datetime”,在指定的时间也是通过查询来获取二进制日志。s7” 2022-05-14 11:49:13以前的数据,即没有恢复”s7”信息。

首先,模拟故障和恢复完全备份,从起始位置到“s7“之前结束。

[root@mysql ~]# mysql -uroot -p123456 -e drop table client.user_info;
 [root@mysql ~]# mysql -uroot -p client < /mysql_bak/client_userinfo-2022-05-14.sql 
 Enter password: 
 [root@mysql ~]# mysqlbinlog --no-defaults --stop-datetime=2022-05-14 11:49:13 /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456
 Warning: Using a password on the command line interface can be insecure.
 [root@mysql ~]# mysql -uroot -p -e select * from client.user_info;
 Enter password: 
 +-----+----------+---------+-------+-------+
 | sfz | xingming | xingbie | yh_id | zifei |
 +-----+----------+---------+-------+-------+
 | 006 | z3       | nan     | 016   |    10 |
 | 007 | l4       | nv      | 017   |    91 |
 | 008 | w5       | nv      | 018   |    23 |
 | 009 | z6       | nan     | 019   |    37 |
 +-----+----------+---------+-------+-------+

类似地,执行以下操作可以实现复苏只是“s7”的信息,不z6“信息恢复。

mysql> drop table user_info;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select * from user_info;
 ERROR 1146 (42S02): Table client.user_info doesnt exist
 [root@mysql ~]# mysql -uroot -p client < /mysql_bak/client_userinfo-2022-05-14.sql 
 Enter password: 
 [root@mysql ~]# mysqlbinlog --no-defaults --start-datetime=2022-05-14 11:49:13 /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456
 Warning: Using a password on the command line interface can be insecure.
 [root@mysql ~]# mysql -uroot -p123456 -e select * from client.user_info;
 Warning: Using a password on the command line interface can be insecure.
 +-----+----------+---------+-------+-------+
 | sfz | xingming | xingbie | yh_id | zifei |
 +-----+----------+---------+-------+-------+
 | 006 | z3       | nan     | 016   |    10 |
 | 007 | l4       | nv      | 017   |    91 |
 | 008 | w5       | nv      | 018   |    23 |
 | 010 | s7       | nan     | 020   |    36 |
 +-----+----------+---------+-------+-------+