mysql数据库完全备份和增量备份与恢复

小鱼儿&

已于 2023-02-15 10:46:15 修改

阅读量1k

点赞数

文章标签：数据库 mysql

于 2023-02-14 18:32:17 首次发布

本文链接：https://blog.csdn.net/weixin_45625174/article/details/121159207

版权

本文详细介绍了MySQL的数据备份方法，包括物理备份（冷备、热备）和逻辑备份（mysqldump）。重点讲解了mysqldump的使用，如全量备份、部分备份以及如何恢复数据。还提到了增量备份的概念，利用binlog日志进行增量备份和恢复的步骤，并展示了如何恢复指定范围的数据。

摘要由CSDN通过智能技术生成

mysql数据备份：

数据备份方式

物理备份：

冷备：.冷备份指在数据库关闭后,进行备份,适用于所有模式的数据库
热备：一般用于保证服务正常不间断运行，用两台机器作为服务机器，一台用于实际数据库操作应用,另外一台实时的从前者中获取数据以保持数据一致.如果当前的机器熄火,备份的机器立马取代当前的机器继续提供服务
冷备常用命令：cp tar scp …

逻辑备份：

mysqldump //备份命令
mysql /恢复数据命令

1. 物理备份及恢复：

1.1. 备份操作：

实例1：
mkdir -p /data/mysql
cp -r /var/lib/mysql/ /data/mysql/mysql.bak
实例2：
tar -zcvf /data/mysql/mysql.tar.gz  /var/lib/mysql
实例3：
#在备份数据库创建备份目录：
mkdir /data
#备份数据
scp -r /var/lib/mysql  192.168.2.20:/data/mysql.bak

2. Mysqldump备份：

mysqldump备份完成之前，会把所有的表锁住，导致无法写入。

2.1. 只备份表，不备份数据本身：

备份mysql数据库中的所有表，但是不会自动生成创建mysql数据库的语句：
[root@www ~]# mysqldump -uroot -p1234  mysql  > /root/mysql.sql

2.2. 备份数据库与表：

备份mysql数据库中的所有表，并且会生成创建mysql数据库的SQL语句，也就是导入时不需要先创建数据库：
[root@www ~]# mysqldump -uroot -p1234 --databases mysql  > /root/mysql.sql

2.3. 备份多个数据库：

备份数据库MySQL、ys到/root/ys_mysql.sql
[root@www ~]# mysqldump -uroot -p1234 --databases mysql ys  > /root/ys_mysql.sql

2.4. 备份所有数据库：

[root@www ~]# mysqldump -uroot -p1234 --all-databases >/root/all.sql
或者：
[root@www ~]# mysqldump -uroot -p1234 -A >/root/all.sql

2.5. 备份mysql数据库，并且记录pos点：

[root@www ~]# mysqldump -uroot -p1234 --master-data mysql > /root/mysql.sql

2.6. 备份数据库，并刷新日志：

[root@www ~]# mysqldump -uroot -p1234 --master-data --flush-logs mysql > /root/mysql.sql

2.3. 数据库的恢复：

[root@www ~]# mysqldump -uroot -p1234 --databases ys > /root/ys.sql
首先把ys数据库删除
mysql> drop database ys;
Query OK, 31 rows affected, 2 warnings (0.06 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| mysql                |
| sys                 |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
## 恢复数据库ys
[root@www ~]# mysql -uroot -p1234 < /root/ys.sql 
[root@www ~]#  mysql -uroot -p1234
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| sys                |
| mysql              |
| ys                 |
+--------------------+
4 rows in set (0.00 sec)
 
 或者是：
 mysql> source /root/ys.sql;   ---source 命令导入数据库需要先登录到数库终端：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| sys                |
| mysql              |
| ys                 |
+--------------------+
4 rows in set (0.00 sec)

3. 增量备份与恢复

增量备份与恢复的特点

增量备份的优点是没有重复数据，备份量不大，时间短。但缺点也很明显，需要建立在上次完全备份及完全备份之后所有的增量才能恢复。

3.1. binlog日志概述：

什么是binlog日志？
也称作二进制日志
mysql服务日志文件的一种
记录除查询外的所有SQL语句
可用于数据备份和恢复
配置mysql主从同步的必要条件

3.2. 启动日志

配置项	用途
server_id=数字	指定id值（1-255）
log_bin[=目录名/文件名]	启用binlog日志
max_binlog_size=数值m	指定binlog日志文件容量(默认1G)

[root@localhost ~]# vim /etc/my.cnf
修改内容如下：
[mysqld]
.....
log_bin
server_id=100
[root@localhost ~]# systemctl restart mysqld
###查看binlog日志是否开启
登录数据库
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000001 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

binlog相关文件默认存放在/var/lib/mysql/

主机名-bin.index 索引文件
主机名-bin.000001 第一个二进制文件
主机名-bin.000002 第二个二进制文件
…

正常情况下binlog二进制文件默认1G容量，超过重新创建新的binlog二进制文件，想生成新的binlog日志文件方法如下：

1.重启mysql服务
systemctl   restart  mysqld
或者：
mysql  -root -p密码  -e 'flush logs'
或者：
mysqldump  --flush-logs

3.3.增量备份

定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些文件保存到一个安全的地方即完成了一个时间段的增量备份

##创建数据库
create database school default charset=utf8mb4;
##创建表格
use school;
create table stuinfo(
                       -> id int primary key auto_increment,
                       -> name char(20) not null,
                       -> sex enum('boy','girl') not null,
                       -> age int unsigned not null,
                       -> likes set('run','go fishing','Listen to the music','Play basketball') default'run,play basketball');
##往stuinfo表格里插入数据
insert into stuinfo values(1,'bob','boy',25,'run,go fishing');
##查看表格数据：
select * from stuinfo;
+----+------+-----+-----+----------------+
| id | name | sex | age | likes          |
+----+------+-----+-----+----------------+
| 1  | bob  | boy | 25  | run,go fishing |
+----+------+-----+-----+----------------+
##退出数据库并使用mysqldump完全备份school数据库并重新创建一个binlog文件。
exit    ---退出数据库
mkdir /data ---创建存放数据的目录
mysqldump -uroot -p1234  --flush-logs -B school > /data/$(date +%Y-%m-%d)-school.sql
ls /data/
2023-02-14-school.sql
##进入数据库继续往表格里插入数据
use school;
insert into stuinfo values(2,'tom','boy',28,'run,play basketball');
select * from stuinfo;
+----+------+-----+-----+---------------------+
| id | name | sex | age | likes               |
+----+------+-----+-----+---------------------+
| 1  | bob  | boy | 25  | run,go fishing      |
| 2  | tom  | boy | 28  | run,Play basketball |
+----+------+-----+-----+---------------------+
##退出数据库生成增量备份文件
exit;    ---退出数据库
mysql -uroot -p1234 -e "show master status;flush logs"    ----查看当前的binlog文件名称，然后创建一个新的binlog文件
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000002 |      436 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
##把localhost-bin.000002文件拷贝到/data下并修改当前日期作为增量备份文件
cp -r /var/lib/mysql/localhost-bin.000002 /data/
mv /data/localhost-bin.000002 /data/$(date +%Y-%m-%d)-localhost-bin.000002
ls /data/
2023-02-14-localhost-bin.000002  2023-02-14-school.sql

3.4. 数据恢复

##删除school的所有表
drop table stuinfo;
##查看还有其他表格
show tables;
+------------------+
| Tables_in_school |
+------------------+
+------------------+
##退出数据库,恢复数据
quit;
mysql -uroot -p1234 < /data/2023-02-14-school.sql  ---先恢复完全备份的文件
mysqlbinlog /data/2023-02-14-localhost-bin.000002 | mysql -uroot -p1234  --恢复增量备份的部分。

3.5.恢复指定范围的数据

语法：

mysqlbinlog  选项  binlog日志名称 | mysql -uroot -p密码

在这里插入图片描述

3.5.1. 查看binlog日志文件

[root@localhost ~]# mysqlbinlog /data/2023-02-14-localhost-bin.000002

可以看的出来这种日志格式不易看懂

3.5.2. 修改日志记录格式

##查看当前日志记录格式
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
三种记录格式：
1.statement    报表模式
2.row          行模式
3.mixed        混合模式

##修改日志记录格式：
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
....
binlog_format=mixed
[root@localhost ~]# systemctl restart mysqld
###查看是否修改成功
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)

3.5.3. 恢复指定范围数据：

##往stuinfo表格中插入数据:
insert into stuinfo values(3,'grace','girl',20,'run,play basketball');
insert into stuinfo values(4,'andy','boy',23,'play basketball');
select * from stuinfo;
+----+-------+------+-----+---------------------+
| id | name  | sex  | age | likes               |
+----+-------+------+-----+---------------------+
| 1  | bob   | boy  | 25  | run,go fishing      |
| 2  | tom   | boy  | 28  | run,Play basketball |
| 3  | grace | girl | 20  | run,Play basketball |
| 4  | andy  | boy  | 23  | Play basketball     |
+----+-------+------+-----+---------------------+
##删除grace这个数据
delete from stuinfo where name='grace';
select * from stuinfo;
+----+------+-----+-----+---------------------+
| id | name | sex | age | likes               |
+----+------+-----+-----+---------------------+
| 1  | bob  | boy | 25  | run,go fishing      |
| 2  | tom  | boy | 28  | run,Play basketball |
| 4  | andy | boy | 23  | Play basketball     |
+----+------+-----+-----+---------------------+
##查看当前的binlog日志文件名称；
show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000006 | 1095     |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
### 恢复grace的数据信息
[root@localhost ~]# mysqlbinlog /var/lib/mysql/localhost-bin.000006   ---查看binlog日志内容
# at 302   ---起始pos值
#230214 17:27:39 server id 100  end_log_pos 449 CRC32 0x2dbf2580        Query   thread_id=3     exec_time=0     error_code=0
use `school`/*!*/;
SET TIMESTAMP=1676366859/*!*/;
insert into stuinfo values(3,'grace','girl',20,'run,play basketball')
/*!*/;
# at 449
#230214 17:27:39 server id 100  end_log_pos 480 CRC32 0x3116b5d3        Xid = 21
COMMIT/*!*/;      ---回车操作
# at 480          ---结束pos值
#230214 17:28:12 server id 100  end_log_pos 545 CRC32 0x0b918b41        Anonymous_GTID  last_committed=1        sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 545
[root@localhost ~]# mysqlbinlog --start-position=302 --stop-position=480 /var/lib/mysql/localhost-bin.000006 | mysql -uroot -p1234
或者
[root@localhost ~]# mysqlbinlog --start-datetime="2023-02-14 17:27:39" --stop-datetime="2023-02-14 17:28:12"  /var/lib/mysql/localhost-bin.000006 | mysql -uroot -p1234
###查看是否恢复：
select * from stuinfo;
+----+-------+------+-----+---------------------+
| id | name  | sex  | age | likes               |
+----+-------+------+-----+---------------------+
| 1  | bob   | boy  | 25  | run,go fishing      |
| 2  | tom   | boy  | 28  | run,Play basketball |
| 3  | grace | girl | 20  | run,Play basketball |
| 4  | andy  | boy  | 23  | Play basketball     |
+----+-------+------+-----+---------------------+