MySQL运维

约 1839 字大约 6 分钟

2025-04-18

1. 备份与恢复

1.1 概念

备份：是把数据库复制到转储设备的过程。

恢复：是发生故障后，利用已备份的数据文件或控制文件，重新建立一个完整的数据库

1.2 为什么要备份

程序错误
人为操作错误
运算错误
磁盘故障
突然断电
病毒入侵
灾难（如火灾、地震）和盗窃

1.3 数据库备份类型

根据备份方式的不同，数据库备份可以分为以下两种类型：

逻辑备份：仅备份数据，不备份数据库结构和相关的配置文件。
- 使用 mysqldump 工具将数据库对象和数据导出为 SQL 脚本文件。
- 优点：跨平台、可恢复单个表或数据库。
- 缺点：备份和恢复速度较慢，占用资源较多。
物理备份：备份整个数据库，包括数据、数据库结构和相关的配置文件。
- 直接复制 MySQL 的数据目录（如 InnoDB 表空间文件、MyISAM 的 .MYD 和 .MYI 文件）。
- 优点：备份和恢复速度快，适合大型数据库。
- 缺点：依赖特定存储引擎（如 InnoDB），且备份文件不可读。

1.4 备份策略

定时备份：定期将数据库备份到磁盘，以便发生灾难时恢复。
事件驱动备份：根据业务需要，在特定时间点或事件发生时，自动备份数据库。
增量备份：只备份自上次备份以来发生的变化。

1.5 逻辑备份方法

1.5.1 mysqldump

mysqldump 是 MySQL 提供的命令行工具，用于备份 MySQL 数据库。

语法：mysqldump [选项] 数据库名 > 输出文件名
选项：
- -h 服务器主机名
- -u 用户名
- -p 密码
- -P 端口号
- -add-drop-table：在每个CREATE TABLE语句前添加DROP TABLE语句，默认是打开的，可以用-skip-add-drop-table来取消
- -add-locks：该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句，可以防止再次导入记录时，其他用户对表进行的操作
- -t 或 -no-create-info：只导出数据，而不添加CREATE TABLE语句
- -c 或 --complete-insert：在每个INSERT语句的列上加上列名，在数据导入另一个数据库时有用
- -d 或 --no-data：不导出数据，只导出数据库结构
示例：用root账号访问系统数据库mysql，将生成的备份文件保存在目录D:/backup中
```
mysqldump -u root -p mysql > D:/backup/mysql_backup.sql
```
注：为保证账户密码安全，命令中可不写密码，但必须有参数“-p”，回车后根据提示写密码例如：
```
mysqldump -u root -p > D:/backup/mysql_backup.sql
Enter password: ********
```
案例2：导出数据库test中的表test1和test2，并将生成的备份文件保存在目录D:/backup中
```
mysqldump -u root -p test test1 test2 > D:/backup/test_backup.sql
```
全库备份：

# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases.sql

# 指定数据库备份
mysqldump -u root -p --databases dbname1 dbname2 > specific_databases.sql

单表备份：

# 备份单个表
mysqldump -u root -p dbname tablename > table_backup.sql

部分数据备份

# 备份表中满足条件的数据
mysqldump -u root -p dbname tablename --where="id < 100" > partial_data.sql

1.6 物理备份方法

适用场景：大型数据库、热备份（备份期间不影响数据库读写）。

1.6.1 直接复制

直接复制 MySQL 的数据目录（如 InnoDB 表空间文件、MyISAM 的 .MYD 和 .MYI 文件）。

优点：备份和恢复速度快，适合大型数据库。
缺点：依赖特定存储引擎（如 InnoDB），且备份文件不可读。

1.6.2 备份工具

XtraBackup 是 MySQL 官方提供的备份工具，支持多种存储引擎，包括 InnoDB、MyISAM、Archive、CSV、Blackhole。

1.7 备份恢复

1.7.1 使用mysql命令

语法：mysql [选项] 数据库名 < 备份文件名
选项：
案例1：使用mysql命令将备份的文本文件mysql_20200720.sql还原到新的数据库test中

mysql –u root –p test<D:\backup\mysql_20200720.sql

注意

在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在恢复数据库过程将会出错

案例2：恢复整个数据库：

# 恢复整个数据库
mysql -u root -p < all_databases.sql

1.7.2 使用source命令

CREATE DATABASE test;   #创建数据库 
Use test;          #选择数据库
SOURCE C:\backup\ mysql _20200720.sql   #恢复数据库

注意

登录MySQL服务后使用。执行该命令前，先创建并选择恢复后的目标数据库

1.8 导入导出数据

使用SELECT...INTO OUTFILE语句将表数据导出到文本文件

USE mysql;
SELECT `Host`,`User` FROM `user`
INTO OUTFILE 
'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/mysql_user_20200720.txt';

使用LOAD DATA INFILE语句将文本文件导入到表中

USE mysql;
--创建数据库，数据表
#省略
-- 导入数据
Load DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.7 /Uploads/mysql_user_20200720.txt' INTO TABLE test.user;
SELECT * FROM `user`;

注意

导入数据前，必须先创建数据库和数据表，导入数据时，文件名和数据库名要对应

1.9 自动备份

数据备份一般选择在凌晨访问量少的时候完成

按周期（如：一天、一周、一个月）进行备份

需求说明：

每天凌晨2:00对系统数据库mysql进行自动备份，将备份文件保存到d:\mysql_backup文件夹中
每次备份前要求先删除一周之前的备份数据
备份文件以mysql_backup开头，并包含日期时间信息
- 格式：mysql_backup_yyyymmdd-hhmmss.sql

1.9.1 Windows中自动备份

mysql.bat文件内容如下：

rem ******MySQL backup start******
    @echo off
    
    ::删除一周前的备份数据
    forfiles /p "d:\mysql_backup" /m backup_*.sql -d -7 /c "cmd /c del /f @path"
    
    ::设置时间变量
    set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%"
    
    ::进入mysql安装目录的bin目录下，如已配置全局变量则省略
    cd C:\Program Files\MySQL\MySQL Server 5.7\bin\
    ::执行备份操作
    mysqldump --opt --single-transaction=TRUE --user=root --password=root --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "mysql" > d:\mysql_backup\backup_%Ymd%.sql
    
    @echo on
    rem ******MySQL backup end******

1.9.2 Linux中自动备份

mysql.sh文件内容如下：

#!/bin/bash

## This is backup mysql shell script.
## Writen by zhunzhun.zeng 2013-06-18

export LANG=zh_CN.UTF-8
db_user=root
db_passwd=bdqn
db_bak_dir=/data/backup/dbbak
d=`date +%y-%m-%d`
pid=`ps -ef | grep mysqldump | grep -v grep | wc -l`
pid1=`ps -ef | grep mysqldump | grep -v grep | awk '{print $2}'`
time=`date +%H:%M:%S`
echo "开始检查mysql备份进程是否存在？"
echo "1)如果mysql备份进程存在，显示强制结束当前mysql备份进程"
echo "2)如果mysql备份进程不存在，则显示mysql备份进程不存在"
echo "======================================================"
echo "正在检查中......"
echo "======================================================"
if [ $pid -ne 0 ];then
echo "mysql备份进程存在"
echo "======================================================"
echo "强制结束当前mysql备份进程"
kill -9 $pid1
else
echo "mysql备份进程不存在"
fi
echo "======================================================"
echo "开始备份mysql"
[ -d $db_bak_dir/$d ] || mkdir -p $db_bak_dir/$d
echo "======================================================"
echo 当前服务器时间是:$d $time
echo "开始备份mysql"
all_db="$(mysql -u$db_user -p$db_passwd -Bse 'show databases' | grep -v mysql | grep -v performance_schema | grep -v information_schema | sed '/^test/'d)"
  for db in $all_db
do
     mysqldump -u$db_user -p$db_passwd --hex-blob -q $db | gzip > $db_bak_dir/$d/$db.sql.gz
done
echo "======================================================"
echo "mysql备份结束"
echo "======================================================"
time1=`date +%H:%M:%S`
echo "当前服务器时间是:"$d $time1
echo "======================================================"
echo "删除一周前的数据库备份文件"
find /data/backup/dbbak  -mtime +6 | xargs rm -rf
echo "======================================================"
echo "删除完成"