MySQL备份与恢复

最新推荐文章于 2024-11-10 22:27:03 发布

weixin_49226813

最新推荐文章于 2024-11-10 22:27:03 发布

阅读量147

点赞数

分类专栏： mysql数据库文章标签： mysql 服务器 linux

本文链接：https://blog.csdn.net/weixin_49226813/article/details/109446608

版权

mysql数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

项目目录

数据库备份的重要性
数据库备份的分类
myql物理冷备份及恢复
mysqldump备份和恢复
msyql增量备份与恢复

数据库备份的重要性

1、在生产环境中，数据的安全性至关重要
2、任何数据的丢失都可能产生严重的后果
3、造成数据丢失的原因：
程序错误
人为操作错误
运算错误
磁盘故障
灾难（如火灾、地震)和盗窃

数据库备份的分类

1、从物理与逻辑的角度,备份可分为
物理备份:对数据库操作系统的物理文件（如数据文件、日志文件等)的备份
物理备份方法：

冷备份（脱机备份)︰是在关闭数据库的时候进行的
热备份(联机备份)︰数据库处于运行状态，依赖于数据库的日志文件
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作

2、逻辑备份：对数据库逻辑组件(如:表等数据库对象)的备份
3、从数据库的备份策略角度备份可分为

完全备份:每次对数据库进行完整的备份
差异备份:备份自从上次完全备份之后被修改过的文件
增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

myql物理冷备份及恢复

1、关闭数据库,对数据库进行整体备份

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

2、登录数据库进行破坏

systemctl start mysqld.service
mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
mysql> drop database mysql;
systemctl stop mysqld.service

3、恢复数据库

mkdir back
mv /usr/local/mysql/data/ /backup/
mkdir restore
tar zxf /backup/mysql_all-.tar.gz -C restore/
mv restore/usr/local/mysql/data/ /usr/local/mysql/
systemctl start mysqld.service

mysqldump备份和恢复

1、备份所用的数据

mysqldump -uroot -p123456 --all-databses > /root/all-data-$(date +%F).sql

2、对指定表进行备份(如下面mysql表)

mysqldump -uroot -p123456 -databases mysql > /root/mysql.sql

3、对数据库指定的表进行备份(如mysql数据库的user表)

mysqldump -uroot -p123456 msyql user > /root/mysql_user-$(date +%F).sql

4、对指定表的结构进行备份

mysqldump -uroot -p123456 -d msyql user > /root/user.sql

5、恢复数据库

方法一：
mysqldump -u -p123456  edu  > edu-$(date +%F).sql
mysql -uroot -p123456
mysql> drop database edu;
mysql> create database mysql;
mysql -uroot -p123456 edu < edu-2020-11-02.sql 
方法二：
mysqldump -u -p123456  edu  > edu-$(date +%F).sql
mysql -uroot -p123456
mysql> drop database edu;
mysql> create database mysql;
mysql> source /root/edu-2020-11-02.sql

msyql增量备份与恢复

1、基于时间点进行恢复

mysqldump -u -p123456  edu  > edu-$(date +%F).sql
vim /etc/my.cnf
log_bin=/usr/local/mysql/data/mysql_bin
systemctl restart mysqld.service
mysqladmin -uroot -p123456 flush-logs
cd /usr/local/mysql/data/
[root@server1 data]# ll

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 

mysql> use edu
mysql> insert into class values(30,'liming');
mysql> insert into class values(40,'a1');
mysql> delete from class where name='liming';
mysql> insert into class values(50,'a2');
mysql> select * from class;
+------+----------+
| id   | name     |
+------+----------+
|   10 | lisi     |
|   20 | zhangsan |
|   40 | a1       |
|   50 | a2       |
+------+----------+
4 rows in set (0.00 sec)
误将liming删除现在对此进行恢复
mysqladmin -uroot -p123456 flush-logs;
mysql> use edu
mysql> drop table class;
mysql -uroot -p123456 edu < /root/edu-2020-11-02.sql
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 
BEGIN
/*!*/;
 at 814
#201102 11:22:53 server id 1  end_log_pos 864 CRC32 0x9c1345b8 	Table_map: `edu`.`class` mapped to number 108
 at 864
#201102 11:22:53 server id 1  end_log_pos 911 CRC32 0xe04e901d 	Delete_rows: table id 108 flags: STMT_END_F
DELETE FROM `edu`.`class`
 WHERE
   @1=30
   @2='liming'
 at 911
#201102 11:22:53 server id 1  end_log_pos 942 CRC32 0x289f9ad8 	Xid = 16
COMMIT/*!*/;
at 942
#201102 11:23:02 server id 1  end_log_pos 1007 CRC32 0xc4b1c008 	Anonymous_GTID	last_committed=3    sequence_number=4	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 at 1007
#201102 11:23:02 server id 1  end_log_pos 1078 CRC32 0x253a5f4f 	Query	thread_id=4	exec_time=0 error_code=0
SET TIMESTAMP=1604287382/*!*/;
BEGIN
/*!*/;
 at 1078
#201102 11:23:02 server id 1  end_log_pos 1128 CRC32 0x11ca0a6f 	Table_map: `edu`.`class` mapped to number 108
 at 1128
#201102 11:23:02 server id 1  end_log_pos 1171 CRC32 0xff078dea 	Write_rows: table id 108 flags: STMT_END_F
 INSERT INTO `edu`.`class`
SET
   @1=50
   @2='a2'
 at 1171
#201102 11:23:02 server id 1  end_log_pos 1202 CRC32 0xfa636143 	Xid = 17
COMMIT/*!*/;

[root@server1 ~]# mysqlbinlog --no-defaults --stop-datetime='2020-11-02 11:22:53 ' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p123456  #停止错误的时间

[root@server1 ~]# mysqlbinlog --no-defaults --start-datetime='2020-11-02 11:23:02' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p123456   #开始正确的时间

2、基于位置点进行恢复

mysql> use edu
mysql> insert into class values(60,'a3');
mysql> delete from class where name='a2';
mysql> insert into class values(70,'a4');
mysqladmin -uroot -p123456 flush-logs;

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 
BEGIN
/*!*/;
 at 6706
#201102 12:07:44 server id 1  end_log_pos 6756 CRC32 0xb01fe861 	Table_map: `edu`.`class` mapped to number 223
 at 6756
#201102 12:07:44 server id 1  end_log_pos 6799 CRC32 0x9535b3bb 	Delete_rows: table id 223 flags: STMT_END_F
 DELETE FROM `edu`.`class`
 WHERE
  @1=50
   @2='a2'
 at 6799
#201102 12:07:44 server id 1  end_log_pos 6830 CRC32 0xbf8dd999 	Xid = 435
COMMIT/*!*/;
 at 6830
#201102 12:07:50 server id 1  end_log_pos 6895 CRC32 0xdd8c6064 	Anonymous_GTID	last_committed=28   sequence_number=29	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
at 6895
#201102 12:07:50 server id 1  end_log_pos 6966 CRC32 0xcbc27fde 	Query	thread_id=25	exec_time=0 error_code=0
SET TIMESTAMP=1604290070/*!*/;
BEGIN
/*!*/;
 at 6966
#201102 12:07:50 server id 1  end_log_pos 7016 CRC32 0x2d2e70f5 	Table_map: `edu`.`class` mapped to number 223
 at 7016
#201102 12:07:50 server id 1  end_log_pos 7059 CRC32 0xbecebabe 	Write_rows: table id 223 flags: STMT_END_F
 INSERT INTO `edu`.`class`
 SET
   @1=70
   @2='a4'
 at 7059
#201102 12:07:50 server id 1  end_log_pos 7090 CRC32 0x98c0f983 	Xid = 436
COMMIT/*!*/;
 at 7090
#201102 12:09:05 server id 1  end_log_pos 7137 CRC32 0x85685dcb 	Rotate to mysql_bin.000004  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

mysqlbinlog --no-defaults --stop-position='6706' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p123456 ###上一次操作正确的位置点停止
 mysqlbinlog --no-defaults --start-position='6799' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p123456 ###下一次操作正确的位置点开始