首页服务器教程MySQL备份和恢复数据库命令

MySQL备份和恢复数据库命令

服务器教程
2024-09-11 687

在数据库管理中，备份和恢复是比较常见的操作。本文将介绍MySQL备份和恢复的基本命令和方法，有效地管理数据库备份，以应对数据丢失或损坏的风险。

一、备份

1、数据备份的重要性

备份的主要目的是用于灾难恢复。在生产环境中，数据的安全性至关重要，任何数据丢失都可能导致严重后果。造成数据丢失的原因包括程序错误、人为操作失误、运算错误、磁盘故障、自然灾害（如火灾、地震）以及盗窃等。

2、数据库备份分类

（1）物理备份

  • 物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）进行的备份。物理备份的方式包括：
  • 冷备份（脱机备份）：在数据库关闭状态下进行的备份；
  • 热备份（联机备份）：在数据库运行状态时依赖于数据库日志文件进行的备份；
  • 温备份：在数据库锁定表格（不可写入但可读取）状态下进行备份操作。

（2）逻辑备份

对数据库的表或其他对象进行备份。

3、数据库备份策略

  • 完全备份：每次备份都包含完整的数据库；
  • 差异备份：仅备份上一次完全备份后更新的数据；
  • 增量备份：每次备份只备份上一次完全备份或增量备份后更新的数据。

4、常用备份方法和工具

（1）物理冷备

在数据库关闭状态下进行的备份，打包数据库文件，备份速度快，恢复过程也相对简单。

（2）专用备份工具

  • mysqldump：常用的逻辑备份工具（逻辑热备），但备份速度较慢，通常不建议用于超过100G的数据库；
  • mysqlhotcopy：仅支持备份MyISAM和ARCHIVE表。

（3）启用二进制日志进行增量备份

进行增量备份时，需要刷新二进制日志。

（4）第三方备份工具

如免费的MySQL热备份软件Percona、XtraBackup、borgbackup，其中XtraBackup支持物理热备。

5、数据库上云迁移

  • 冷迁移
  • 热迁移

二、数据库完全备份

1、简介

完全备份是对整个数据库、数据库结构和文件结构的备份，保留的是备份完成时刻的数据库状态。完全备份是差异备份和增量备份的基础。

  • 优点：备份与恢复操作简单方便。
  • 缺点：可能存在大量数据重复，占用大量备份空间，备份和恢复所需时间较长。

2、物理备份与恢复

（1）物理备份

关闭MySQL数据库：”systemctl stop mysqld”。

创建备份文件存放路径：”mkdir /opt/backup”。

使用tar命令直接打包数据库文件夹：

tar zcvf /opt/backup/mysql_full_$(date -d "-1 day" +%Y%m%d).tar.gz /usr/local/mysql/data/

（2）备份恢复

  • 主机1：192.168.210.103
  • 主机2：192.168.210.106

将主机1的备份包远程传输到主机2：

scp /opt/backup/mysql_full_20230922.tar.gz 192.168.210.106:/opt

关闭数据库：”systemctl stop mysql”

先备份当前数据库以防恢复失败：

mv /usr/local/mysql/data /usr/local/mysql/data.bak

解压备份文件：

tar xf mysql_full_20230922.tar.gz

将解压后的data目录移动到/usr/local/mysql/目录下：

cp -a /opt/mysql/data /usr/local/mysql/

登录到数据库：

systemctl start mysqld
mysql -u root -p

（3）补充知识（date命令用法）

显示完整日期格式：”date +%F”

  • 显示年月日：”date +%Y%m%d”
  • 显示年/月/日：”date +%Y/%m/%d”
  • 当前月第一天：”date +%Y%m01″
  • 显示明天：”date -d “+1 day” +%Y%m%d”
  • 显示前一天：”date -d “-1 day” +%Y%m%d”
  • 当前月第一天的前三天：”date -d “$(date +%Y%m01) -3 day” +%Y%m%d”
  • 上个月最后一天：”date -d “$(date +%Y%m01) -1 day” +%Y%m%d”
  • 当前月最后一天：”date -d “$(date -d “1 month” +%Y%m01) -1 day” +%Y%m%d”
  • 下个月第一天：”date -d “1 month” +%Y%m01″
  • 当前月第一天前一天：”date -d “-1 day” +%Y

3、MySQL dump备份与恢复

MySQL自带的备份工具，可方便实现对MySQL的备份；可以将指定的库、表导出为SQL脚本；使用命令mysql导入备份的数据。

（1）完全备份一个或多个数据库

导出的就是数据库脚本文件：

mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql

（如果不加 –databases ,只备份库中的表，不会备份库）

举个例子：

  • 单个数据库：mysqldump -u root -p –databases www > /opt/backup/www.sql
  • 多个数据库：mysqldump -u root -p –databases www www > /opt/backup/3w.sql

（2）完全备份mysql中的所有库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

举个例子：

mysqldump -u root -pabc123 --all-databases > /opt/backup/all.sql
cat /opt/all.sql | grep "^CERATE DATABASES"

（3）完全备份指定库中的部分表

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql

例如：备份www的kkk1表

mysqldump -u root -p [-d] www kkk1 > /opt/backup/www-kkk1.sql

（使用“-d”选项，说明只保存数据库的表结构，不使用“-d”选项，说明表数据也进行备份）

（4）查看备份文件

cat xx.sql|grep -v "^--"| grep -v "^/" | grep -v "^$"
egrep -v "^--|^/\*|^$"

举个例子：

cat www-kkk1.sql|grep -v "^--" | grep -v "^/" | grep -v "^$"

三、MySQL完全恢复

1、恢复数据库

当备份文件中仅包含表的备份，而不包含创建数据库的语句时，必须指定目标数据库名，并确保目标数据库已经存在。在生产环境中，可以使用 Shell 脚本自动实现定时备份。

（1）通过 “source” 命令恢复

首先使用 “mysqldump” 工具生成备份 SQL 脚本；然后登录到 MySQL 数据库，再执行 “source” 命令，指定备份 SQL 脚本的路径。

示例：

MySQL[(none)]> source /backup/all-data.sql

以 “www” 数据库为例，首先备份目标数据库：

mysqldump -u root -pabc123 --databases www > /opt/backup/www.sql

假设数据库 “www” 被删除：

DROP DATABASE www;
SHOW DATABASES;

使用 “source” 命令执行 SQL 脚本以恢复数据库：

# 先登录到数据库
mysql -u root -p
# 执行脚本
source /opt/backup/www.sql
# 查看恢复后的数据库
SHOW DATABASES;

（2）通过 “mysql” 命令恢复

使用以下命令恢复数据库：

mysql -u 用户名 -p[密码] < 库备份脚本的路径

示例：

# 模拟删除数据库
mysql -u root -pabc123 -e 'DROP DATABASE www;'
mysql -u root -pabc123 -e 'SHOW DATABASES;'
# 恢复数据库
mysql -u root -pabc123 < /opt/backup/www.sql
mysql -u root -pabc123 -e 'SHOW DATABASES;'

通过管道符恢复：

mysql -u root -pabc123 -e 'DROP DATABASE www;'
mysql -u root -pabc123 -e 'SHOW DATABASES;'
cat www.sql | mysql -u root -pabc123
mysql -u root -pabc123 -e 'SHOW DATABASES;'

2、恢复数据库表

当备份文件中仅包含表的备份，而不包含创建数据库的语句时，导入操作时必须指定数据库名，并确保目标数据库已存在。

# 备份 www 数据库中的 kkk1 表
mysqldump -u root -pabc123 [-d] www kkk1 > /opt/backup/www-kkk1.sql

模拟删除数据表：

mysql -u root -pabc123 -e 'DROP TABLE www.kkk1;'
mysql -u root -pabc123 -e 'SHOW TABLES FROM www;'
# 恢复表数据
mysql -u root -pabc123 www < /opt/backup/www-kkk1.sql
# 验证恢复是否成功
mysql -u root -pabc123 -e 'SELECT * FROM www.kkk1;'

四、MySQL增量备份与恢复

使用 “mysqldump” 进行完全备份可能会出现数据重复，备份时间和恢复时间较长。增量备份的特点是没有重复数据，备份量小且时间短；恢复时需要上次的完全备份以及完全备份之后的所有增量备份，并需逐个反向恢复所有增量备份。

MySQL 没有直接的增量备份方法，但可以通过 MySQL 提供的二进制日志间接实现增量备份。
二进制日志的作用在于保存所有更新或者可能更新数据库的操作，并在启动 MySQL 服务器后开始记录。当文件达到 “max_binlog_size” 设置大小，或接收到 “flush logs” 命令后，将创建新的日志文件。只需定期执行 “flush logs” 方法即可生成并保存新的二进制日志，从而形成一个时间段的增量备份。

1、MySQL增量备份

（1）开启二进制日志功能：

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1
#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

例如：

systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

（2）通过刷新二进制日志文件间接实现增量备份

（3）查看二进制日志文件内容：

mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件（绝对路径）
（--base64-output=decode-rows：使用64位编码机制去解码并按行读取）

举个例子：

cp /usr/local/mysql/data/mysql-bin.000001 /opt
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001

2、Mysql增量恢复

MySQL没有提供直接的增量备份方法，可通过MySQL提供的二进制日志间接实现增量备份。

（1）一般恢复

mysqlbinlog --no-defaults 二进制日志文件 | mysql -uXXX -pXXX
mkdir mysql-backup
mkdir /mysql-backup/full（full文件夹做完全备份）
mkdir /mysql-backup/bin （bin文件夹做增量备份）

在www数据库中，有一个kkk1的数据表,对表中的初始数据进行完全备份。

#对kkk1表做完全备份
mysqldump -uroot -pabc123 www kkk1 > /root/mysql-backup/full/www_kkk1-$(date +%Y%m%d).sql
#二进制日志文件刷新
mysqladmin -uroot -pabc123 flush-logs
#生成新的二进制日志文件
ls /usr/local/mysql/data/

录入新的数据，进行增量备份。

insert into kkk1 values (5,'aaa',23,'男','football');
insert into kkk1 values (6,'bbb',24,'女','dancing');
insert into kkk1 values (7,'ccc',25,'女','singing');
#二进制日志文件刷新
mysqladmin -uroot -pabc123 flush-logs
#生成新的二进制日志文件
ls /usr/local/mysql/data/
#进行增量备份，备份的不是最新的那一个，备份的是倒数第二个
mv -f /usr/local/mysql/data/mysql-bin.000002 /root/mysql-backup/bin/mysql-bin.00000 -$(date -d '1 day' +%Y%m%d)
ls /mysql-backup/bin

模拟删除库，先进行表的完全恢复。

show databases;
drop database www;
#因为只对表做了备份，没有对库做备份，所以要先建一个数据库
create database www;
show databases;
quit
#先完全恢复数据表里的初始数据
mysql -uroot -pabc123 www < /mysql-backup/full/www_kkk1-20230923.sql

进行增量恢复：

#通过管道符导入的方式
mysqlbinlog --no-defaults /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123

（2）断点恢复

#查看二进制文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql-backup/bin/mysql-bin.000002-20230924
#重定向输出到文件里方便查看
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002-20230924 > mysql-bin-20230924
#删除id>=5的数据
delete from kkk1 where id>=5;
select * from kkk1;

基于位置恢复：

#仅恢复id=5和6的数据，即不恢复“id=7”的数据
mysqlbinlog --no-defaults --start-position='296' --stop-position='891' /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123
#仅恢复“id=7”的数据
mysqlbinlog --no-defaults --start-position='891' /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123

基于时间点恢复：

#仅恢复id=5和6的数据
mysqlbinlog --no-defaults --start-datetime='2023-09-23 14:24:05' --stop-datetime='2023-09-23 14:24:32' /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123
#仅恢复id=6和7的数据
mysqlbinlog --no-defaults --start-datetime='2023-09-23 14:24:27' /mysql-backup/bin/mysql-bin.000002-20230924 | mysql -uroot -pabc123
如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

五、MySQL日志管理

MySQL 的日志默认保存位置为 “/usr/local/mysql/data”。

1、编辑MySQL配置文件

使用命令进入 MySQL 配置文件：

vim /etc/my.cnf

在 “[mysqld]” 部分添加或修改以下配置项：

ini
## 错误日志，用来记录 MySQL 启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log
# 指定日志的保存位置和文件名
## 通用查询日志，用来记录 MySQL 的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
## 二进制日志 (binlog)，用来记录所有更新了数据或者潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin
# 或者使用 log_bin=mysql-bin
## 慢查询日志，用来记录所有执行时间超过 long_query_time 秒的语句，帮助找到执行时间长的查询，以便进行优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
# 设置超过 5 秒执行的语句被记录，缺省时为 10 秒

2、重启MySQL服务

在修改配置后，重启 MySQL 服务以使更改生效：

systemctl restart mysqld

检查日志功能是否开启**

登录 MySQL：

mysql -u root -p

使用以下命令检查日志设置：

SHOW VARIABLES LIKE 'general%'; -- 查看通用查询日志是否开启
SHOW VARIABLES LIKE 'log_bin%'; -- 查看二进制日志是否开启
SHOW VARIABLES LIKE '%slow%'; -- 查看慢查询日志功能是否开启
SHOW VARIABLES LIKE 'long_query_time'; -- 查看慢查询时间设置

若需动态启用慢查询日志，可以在 MySQL 中运行：

SET GLOBAL slow_query_log=ON; -- 在数据库中设置开启慢查询日志

3、增量备份MySQL数据库的Bash脚本

以下 Bash 脚本用于增量备份 MySQL 数据库的二进制日志：

#!/bin/bash
# 设置日期变量
TODAY=$(date +%Y%m%d)
YESTERDAY=$(date -d "yesterday" +%Y%m%d)
# MySQL 数据库位置和凭证
MYSQL_HOME=/usr/local/mysql/data
USERNAME=root
PASSWORD=abc123
# 备份二进制日志索引文件
/usr/bin/cp -f $MYSQL_HOME/mysql-bin.index /opt/backup/mysql-bin-$TODAY.index
# 判断前一天的日志索引文件是否存在，并根据情况获取今天生成的二进制日志文件
if [ -f /opt/backup/mysql-bin-$YESTERDAY.index ]; then
COUNT=$(comm -13 <(sort /opt/backup/mysql-bin-$YESTERDAY.index) <(sort /opt/backup/mysql-bin-$TODAY.index))
else
COUNT=$(cat /opt/backup/mysql-bin-$TODAY.index)
fi
# 刷新二进制日志文件
$MYSQL_HOME/bin/mysqladmin -u"$USERNAME" -p"$PASSWORD" flush-logs &> /dev/null
# 循环备份当天的二进制日志
for BINLOG in $COUNT; do
/usr/bin/mv -f "$MYSQL_HOME/$BINLOG" "/opt/backup/$BINLOG-$TODAY"
done

说明：

  • 脚本使用 “date” 命令生成当前和前一天的日期，并定义 MySQL 数据库的位置及登录凭证。
  • 先备份当前的二进制日志索引文件。
  • 检查前一天的索引文件是否存在，以获取新增的二进制日志文件。
  • 刷新二进制日志文件，以确保新的日志可以生成。
  • 最后，脚本循环将新生成的二进制日志移动到备份目录，并附加日期。

  • 广告合作

  • QQ群号：707632017

点赞 (0)
温馨提示：
1、本网站发布的内容(图片、视频和文字)以原创、转载和分享网络内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。邮箱：2942802716#qq.com（#改为@）。 2、本站原创内容未经允许不得转裁，转载请注明出处“站长百科”和原文地址。
上一篇： Amazon ECS部署Docker容器教程
MySQL如何优化慢查询
下一篇： MySQL如何优化慢查询

相关文章

如何将Fedora36升级到Fedora37
如何将Fedora36升级到Fedora37
1天前 14
使用Screenshots安装Fedora 40版本详细教程
使用Screenshots安装Fedora 40版本详细教程
2天前 15
Ubuntu如何安装Postgres主从备份
Ubuntu如何安装Postgres主从备份
2周前 27
Kali Linux系统全方位介绍
Kali Linux系统全方位介绍
3周前 30
猜你喜欢
热门文章
3个月免费VPS

亚马逊云科技

科灯跨境

热门教程
  • Z-Blog教程

    Z-Blog教程

    ZBlog教程分享ZBlog安装教程、ZBlog建站教程...
  • WordPress教程

    WordPress教程

    WordPress教程提供了关于WordPress的基础...
  • 宝塔面板教程

    宝塔面板教程

    宝塔面板教程是一个致力于向用户传授宝塔面板的使用技巧和知...
  • Shopify教程

    Shopify教程

    Shopify教程提供了关于Shopify平台的基本知识...
  • PHP教程

    PHP教程

    PHP教程提供了关于PHP语法、变量、函数、流程控制等概...
阿里云

阿里云

热门标签
360 cPanel Facebook Godaddy Google Kubernetes Linux php SEO SEO Shopline TeamViewer WordPress 业界 域名 微信 微软 技术 搜索引擎 杂谈 淘宝 电子商务 百度 网站 网站建设 网赚 腾讯 虚拟主机 观点评论 谷歌
首页 电子书 菜单 开发教程 数字化百科