MySQL备份和恢复

^sakura

已于 2023-03-03 10:04:57 修改

阅读量322

点赞数

分类专栏：数据库文章标签：数据库 mysql Powered by 金山文档

于 2023-02-27 10:45:08 首次发布

本文链接：https://blog.csdn.net/m0_63013378/article/details/129237322

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

备份介绍与分类

数据库备份方案一般分为冷备份和热备份

冷备份：也被称为物理备份，拷贝数据库目录下的文件进行备份

热备份：数据库服务正常运行情况，直接对数据库的数据进行备份。备份方案有全量备份、增量备份和差异备份

全量备份

一般称为：全备，每次都将所有数据备份一遍，优点是安全，即使数据丢失也不怕，将数据导回去即可，缺点是耗资源、备份时间长

增量备份

增备，第一次备份时将所有数据备份一遍（也就是进行一次全备），第二次开始每次备份都只备份基于上一次备份的基础上改变的部分。但是在恢复数据的时候，需要按照备份的顺序，先恢复全量备份时的数据，再恢复第一次增量的数据，再恢复第二次增量的数据。但是如果中途某一次的备份数据丢了，想要恢复很难，一般不使用

差异备份

差备，第一次也是全备，第二次只备份了的数据，没有修改的不动，也就是二次备份对于第三次备份来说是一次全备。差异备份是利用二进制日志来记录执行的操作，通过操作来恢复，所以进行差异备份之前需要开启MySQL的二进制日志功能

备份操作

备份之前先创建一些数据


mysql> select * from test.test1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   21 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql>

全量备份

热备份

有密备份

使用mysqldump进行备份。-u指定用户，-p指定密码，-P指定端口，-h指定主机IP

#备份单张表
[root@test1 ~]# mysqldump -uroot -psakura test test1 > test1.sql  //将test库中的test1表导出，保存为test1.sql
#备份多张表
[root@test1 ~]# mysqldump -uroot -psakura test test1 test2 > test1.sql  //备份test库中的test1和test2表

#备份单个数据库
[root@test1 ~]# mysqldump -uroot -psakura --databases test > test.sql 
#备份多个数据库
[root@test1 ~]# mysqldump -uroot -psakura --databases test abc > test.sql  //备份test和abc两个库

#备份整个MySQL数据库
[root@test1 ~]# mysqldump -uroot -psakura --all-databases > mysqldump.sql

#备份命名时一般会以日期时间来命名，方便区分
例如：
[root@test1 ~]# mysqldump -uroot -psakura --all-databases > mysqlall-$(date '+%Y%m%d%H%M%S')
//备份MySQL整个数据库，命名时会自动带上当前年月日和时分秒

免密备份

#与免密登录一样需要先写存放用户名密码的文件
[root@test1 ~]# cd
[root@test1 ~]# vim .my.cnf
[root@test1 ~]# cat .my.cnf 
[mysqldump]   //做备份，所以要改为mysqldump
user=root
password=sakura
[root@test1 ~]# mysqldump  --all-databases > mysqlall-$(date '+%Y%m%d%H%M%S')  //再备份就不需要输入密码

冷备份

#进入数据存放目录
[root@test1 ~]# cd /opt/data/
[root@test1 data]# ls
auto.cnf         client-key.pem  ib_logfile1         public_key.pem   test
ca-key.pem       ib_buffer_pool  mysql               server-cert.pem  test1.err
ca.pem           ibdata1         performance_schema  server-key.pem
client-cert.pem  ib_logfile0     private_key.pem     sys
[root@test1 data]# mkdir /tmp/mysql.bak
[root@test1 data]# mv * /tmp/mysql.bak/
[root@test1 data]# ls
#数据目录中的内容全部移走了，此时数据库服务仍然开启，但是进入数据库里面不会有仁和数据
[root@test1 data]# ss -anlt | grep 3306
LISTEN 0      80                 *:3306            *:*          
[root@test1 data]# mysql -uroot -psakura

........

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> 

#将文件移回data目录，数据库中也可以正常看到之前创建的库和表了
[root@test1 data]# mv /tmp/mysql.bak/* /opt/data/
[root@test1 data]# mysql -uroot -psakura
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

......

mysql> select * from test.test1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   21 |
+----+-------+------+
2 rows in set (0.01 sec)

mysql>

全备恢复

恢复表：

#在命令行界面恢复，需要指定恢复到test数据库
[root@test1 ~]# mysql test < test.bak-20230223

#在MySQL数据库界面恢复，如果恢复的时候不在存放.sql文件的目录下，需要带上绝对路径
[root@test1 ~]# cd
[root@test1 ~]# ls
password  test1.sql
[root@test1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source test1.sql;
Query OK, 0 rows affected (0.00 sec)

...........

mysql> select * from test.test1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   21 |
+----+-------+------+
1 row in set (0.00 sec)

mysql>

恢复数据库

恢复单个数据库和恢复全备数据库方法一样

#在命令行恢复数据库
[root@test1 ~]# mysql < test.sql

#在MySQL命令行恢复，同样如果不在存放备份文件的目录下，在恢复时需要跟上恢复文件的绝对路径
mysql> source test.sql;

.......

mysql>

差异备份

开启二进制日志

#在MySQL配置文件my.cnf中添加：
# server-id=10  //主机标识，数字可以自定义
# log-bin=mysql_bin  //指定日志的名字，可以自定义，不一定要叫‘mysql_bin’
[root@test1 ~]# cat >> /etc/my.cnf <<EOF
> server-id=10
> bin-log=mysql_bin
> EOF
[root@test1 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=10
log-bin=mysql_bin
[root@test1 ~]# systemctl restart mysqld.service

先进行一次全备

#这里的全备与之前的有区别，需要添加额外的参数
#--single-transaction ：开启事务日志
#--flush-logs ：刷新日志
#--master-data=2 ：指定master数据标识符为2
#--all-databases ： 全备
#--delete-master-logs ： 删除master的日志

[root@test1 ~]# mysqldump -uroot -psakura --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-chayi.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@test1 ~]# 

#改变数据库，此处新建了一个class数据库
mysql> create database class;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> 

#查看没有刷新之前的日志
[root@test1 ~]# ll /opt/data/ | grep mysql_bin.000
-rw-r----- 1 mysql mysql      316 Feb 26 15:45 mysql_bin.000003   //现在是000003

#刷新二进制日志
[root@test1 ~]# mysqladmin -uroot -psakura flush-logs 
[root@test1 ~]# ll /opt/data/ | grep mysql_bin.000
-rw-r----- 1 mysql mysql      363 Feb 26 15:57 mysql_bin.000003
-rw-r----- 1 mysql mysql      154 Feb 26 15:57 mysql_bin.000004  //刷新之后多了一个000004，现在如果再对数据库进行操作，这些操作的记录会被记录到000004文件中

#进入数据库，查看000003日志在数据库中的哪个地方
mysql> show binlog events in 'mysql_bin.000003'; //查看二进制日志事件，pod表示事件开始时的操作id号  ，end_log_pod表示结束时的操作id号，pod到end_pod之间，就是执行这次事件的范围。server_id对应的就是之前在配置文件中设置的主机标识
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000003 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 219 | Query          |        10 |         316 | create database class                 |
| mysql_bin.000003 | 316 | Rotate         |        10 |         363 | mysql_bin.000004;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
5 rows in set (0.00 sec)

mysql> 

#将二进制日志转换为文本文件方便查看理解
#--no-defaults ：不要读取任何选项文件
#--base64-output=decode-rows ： 利用64位字符，以行的方式导出核心部分
[root@test1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003 > /opt/mysql000003.txt

具体的mysqlbinlog命令解析可以查看此文档->mysqlbinlog命令详解

差备恢复

注意：差异恢复之前，需要先全量恢复一次

[root@test1 ~]# mysql -uroot -psakura < all-chayi.sql

方法一：通过操作ID来恢复

#--start-position=219 ：恢复 操作ID 219之后的所有操作
#--stop-position=219 ：恢复 操作ID 219之前的所有操作
[root@test1 ~]# mysqlbinlog --stop-position=219 /opt/data/mysql_bin.000003 | mysql -uroot -psakura
[root@test1 ~]#

方法二：通过操作时间恢复

#需要先将binlog日志转换为文本类型方便查看时间
[root@test1 ~]# cd /opt/data/
[root@test1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000008 > /opt/mysql000008.txt
[root@test1 data]# cat /opt/mysql000008.txt 
.....
#230227  9:46:22 server id 10  end_log_pos 154 CRC32 0x9e357117         Previous-GTIDs
.....
[root@test1 ~]# mysqlbinlog --stop-datetime='23-02-27  9:47:33' /opt/data/mysql_bin.000008 | mysql -uroot -psakura //年月日之间要隔开