【数据库】数据库管理（下）存储过程 触发器 慢查询日志 备份与恢复-备份 & 恢复

时间：2024-10-30 08:43:55

数据库备份是任何数据管理系统中至关重要的组成部分，其主要目的是在发生数据丢失、损坏或系统故障时能够恢复数据。MySQL 提供了多种备份方法：mysqldump 备份工具、数据库管理工具，如SQLyog、直接拷贝数据库文件和相关配置文件，可以根据具体需求选择合适的备份策略。

备份方法

mysqldump 是一个命令行工具，可以用来导出数据库为 SQL 脚本文件。这种方法适用于小到中型数据库。

作用：转储数据库；搜集数据库进行备份；将数据转移到另一个SQL服务器(不一定是MySQL服务器)

# 预存文件目录，须有该目录读写权限
mysqldump -h 主机名 –u 用户名 –p [options] 数据库名 [table1 table2 table3] > path/filename.sql
  • -h 主机名：指定 MySQL 服务器的主机名或 IP 地址。如果 MySQL 服务器运行在同一台机器上，可以省略此选项。
  • -u 用户名：指定连接到 MySQL 服务器的用户名。
  • -p：提示输入密码。您可以直接在命令中指定密码（不推荐），例如 -p密码，但出于安全考虑，通常建议使用交互式输入。
  • [options]：可选参数，可以根据需要添加其他选项。例如：
    • --routines：导出存储过程和函数。
    • --triggers：导出触发器。
    • --single-transaction：对于 InnoDB 表，在备份过程中使用单个事务，以确保一致性。
  • 数据库名：要备份的数据库名称。
  • [table1 table2 table3]：可选参数，指定要备份的表。如果不指定表名，则备份整个数据库。
  • > path/filename.sql：将导出的 SQL 脚本重定向到指定路径和文件名。path 是文件的保存目录，filename.sql 是备份文件的名称。
# 全库备份
mysqldump -u username -p database_name > backup.sql
# 单表备份
mysqldump -u username -p database_name table_name > backup_table.sql
# 所有数据库备份
mysqldump -u username -p --all-databases > all_databases_backup.sql
# 包含存储过程和触发器
mysqldump -u username -p --routines --triggers database_name > backup_with_routines.sql
# 压缩备份
mysqldump -u username -p database_name | gzip > backup.sql.gz

其他常用选项

常用选项 描述 示例
--all-databases 备份所有数据库 mysqldump -u root -p --all-databases > /backup/all_databases_backup.sql
--no-data 仅备份表结构，不备份数据 mysqldump -u myuser -p mydatabase --no-data > /backup/mydatabase_structure.sql
--single-transaction 对于 InnoDB 表，使用单个事务进行备份，以确保数据一致性 mysqldump -u myuser -p mydatabase --single-transaction > /backup/mydatabase_backup.sql
--routines --triggers 导出存储过程、函数和触发器 mysqldump -u myuser -p mydatabase --routines --triggers > /backup/mydatabase_backup.sql
压缩备份文件 使用 gzip 或 bzip2 压缩备份文件，以节省空间 mysqldump -u myuser -p mydatabase

备份 MySchool 数据库的 subject 课程表，保存为 subject.sql 脚本文件

要求：在每个 INERT 语句的列上加上字段名

详细步骤

  1. 打开终端或命令提示符

  2. 运行命令

    # 默认情况下mysqldump 生成的 INSERT 语句包含字段名，为确保可使用 --complete-insert 选项
mysqldump -u myuser -p --complete-insert MySchool subject > /backup/subject.sql

  3. 输入密码

  4. 确认备份：检查 /backup/ 目录下是否生成了 subject.sql 文件。

恢复方法

方法一：用 SOURCE 语法

  • /path/ 是一个绝对路径，并且必须是 mysql 运行用户有权限读取的文件

  • SOURCE 命令允许在 MySQL 命令行中直接执行 SQL 脚本文件。这个方法适用于已经连接到 MySQL 服务器的情况。

# 打开 MySQL 命令行
mysql -u 用户名 -p
# 输入密码 选择要导入数据的数据库（如果脚本中没有指定数据库）
USE 数据库名;
# 执行 SOURCE 命令
SOURCE /path/db_name.sql;

方法二：用 mysql 客户端

  • 这种方法通过命令行直接将 SQL 脚本文件导入到指定的数据库中，而不需要先手动连接到 MySQL 服务器。
# 运行命令
mysql –u 用户名 –p 数据库名 < /path/db_name.sql
# 输入密码

导入导出数据

使用 SELECT ... INTO OUTFILE 语句可以将查询结果导出到一个文件中。这个文件通常是 CSV 格式，但也可以是其他格式。

# 导出数据
SELECT * INTO OUTFILE 'file_name'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM tbl_name;
  • file_name：要输出的文件名，必须是绝对路径，并且文件不能已经存在
  • FIELDS TERMINATED BY：指定字段之间的分隔符，默认为逗号 ,
  • OPTIONALLY ENCLOSED BY：指定字段是否用引号包围，默认为空，即不包围
  • LINES TERMINATED BY：指定行之间的分隔符，默认为换行符 \n

使用 LOAD DATA INFILE 语句可以从一个文件中读取数据并将其插入到表中

# 导入数据
LOAD DATA INFILE 'file_name'
INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 如果文件包含标题行
  • file_name：要导入的文件名，必须是绝对路径
  • FIELDS TERMINATED BY：指定字段之间的分隔符，默认为逗号 ,
  • OPTIONALLY ENCLOSED BY：指定字段是否用引号包围，默认为空，即不包围
  • LINES TERMINATED BY：指定行之间的分隔符，默认为换行符 \n
  • IGNORE 1 ROWS：如果文件的第一行是列标题，可以忽略这一行

输出的文件不能先存在，否则报错

相关文章