本文详细介绍了MySQL的备份和还原的重要性，包括完全备份、部分备份、增量备份和差异备份的类型，以及热备和冷备的区别。重点讨论了mysqldump工具的使用，包括全库、单库、单表及存储过程的备份方法，并提供了备份脚本的编写示例。文章还提到了备份的时间选择和注意事项，以及如何通过LVM快照和xtrabackup进行物理备份。
目录
MySQL的备份和还原
1.为什么要备份？
Backup备份 recovery恢复（还原）
(1)天灾人祸、误操作、安全机制等
(2)付出代价（费用、人力）
停止业务–》会损失收入
备份的时间需要注意：一般选择在服务器不忙的时候
- 什么时候备份
一般选择在服务器不忙的时候
3点–》写脚本去备份
2.备份类型
完全备份、部分备份、增量备份、差异备份
- 完全备份：整个数据集
- 部分备份：只备份数据子集，如部分库或表
- 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
- 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
//注意：二进制日志文件不应该与数据文件放在同一磁盘
3.备份种类
- 热备（online）：mysqld服务是运行的情况下去备份。mysqldump、SQLyog、mysql
workbench等工具来备份、xtrabackup - 冷备（offline）：mysql服务需要停止，然后去拷贝数据
恢复：mysql、SQLyog等工具来备份
物理和逻辑的备份
- 物理：备份数据库的文件或者磁盘–》在linux系统里的文件系统上 （例如cp， scp，tar， rsync)
- 逻辑：备份数据库里的表的结构和执行的语句（insert、create）–>进入到MySQL的内部 Mysqldump
Mysql的数据目录在哪里？
/var/lib/mysql
/data/mysql
每个数据库对应一个目录，里面存放表的数据
[root@MYSQL aliresearch_db]# ls
article.frm article.ibd catagory.frm catagory.ibd db.opt user.frm user.ibd
- .frm 是存放表结构的文件，表里的字段和字段的类型等信息
- .ibd 是存放数据和索引的文件
4.mysql备份工具
mysqldump+复制binlog：
- mysqldump：完全备份复制binlog中指定时间范围的event：增量备份
LVM快照+复制binlog：
- LVM快照：使用cp或tar等做物理备份；完全备份复制binlog中指定时间范围的event：增量备份
其他工具
- xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
- mysqlbackup：热备份， MySQL Enterprise Edition组件
5. Mysqldump说明
- mysqldump客户端是一个逻辑备份工具,备份出数据库中表和数据的SQL语句。
5.1 注意事项
- 在对数据库进行完全备份前，需要收集数据库相关信息，确保备份内容完整，以下为收集语句
\-- 查看表相关信息
select table_schema,table_name,table_collation,engine,table_rows
from information_schema.tables
where table_schema NOT IN ('information_schema' , 'sys', 'mysql',
'performance_schema');
\-- 查看是否存在存储过程、触发器、调度事件等
select count(*) from information_schema.events;
select count(*) from information_schema.routines;
select count(*) from information_schema.triggers;
\-- 查看字符集信息
show variables like 'character%';
5.2 备份命令示例
全库备份
mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4
--single-transaction --set-gtid-purged=off \
\--master-data=2 --hex-blob --triggers --routines --events --all-databases >
all_db_with_data.sql
单库备份
\-- 备份表结构和数据
mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4
--single-transaction --set-gtid-purged=off \
\--hex-blob employees > employees_all.sql
\-- 只备份表结构
mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4
--single-transaction --set-gtid-purged=off \
\--hex-blob --no-data employees > employees_schema.sql
\-- 只备份数据
mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4
--single-transaction --set-gtid-purged=off \
\--hex-blob --no-create-info employees > employees_only_data.sql
单表备份
\-- 只备份employees数据库中的departments表
mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4
--single-transaction --set-gtid-purged=off \
\--hex-blob --tables employees departments > departments.sql
\-- 排除指定数据库的指定表
mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4
--single-transaction --set-gtid-purged=off \
\--hex-blob --ignore-table=employees.departments --ignore-table=employees.employees employees > employees_exclude_dept_emp.sql
注意
如果备份时要排除某个数据库中多个表，要使用多个–ignore-table语句，不能在后面加逗号做分割.
只备份存储过程&函数&EVENTS&触发器
\-- 只导出存储过程&函数
mysqldump -R -ndt dbname1 \--default-character-set=utf8mb4 --single-transaction --set-gtid-purged=off -u root -p > xxx.sql
\--只导出事件
mysql -E -ndt dbname1 --default-character-set=utf8mb4 --single-transaction
--set-gtid-purged=off -u root -p
> xxx.sql
–不导出触发器(触发器是默认导出的)使用 --skip-triggers 屏蔽导出触发器
相关参数说明
| 参数 | 含义 |
|---|---|
| –add-drop-database | 在备份中create database语句前增加一个drop database 的判断语句 |
| –add-drop-table | 在备份中create table语句前增加一个drop table的判断语句 |
| –add–drop-trigger | 在备份中create trigger语句前增加一个drop trigger的判断语句 |
| –all-database | 备份所有数据库的所有表 |
| –campact | 使用紧凑型数据,类似于压缩，可以减少空间占用 |
| –default-character-set | 备份生成的默认字符集 |
| –event | dump出events数据 |
| –extended-insert | 使用多行插入的语句导出 |
| –flush-logs | 在dump前将内存中修改的数据刷到磁盘 |
| –flush-privileges | 在dump后执行更新权限操作 |
| –ingore-table= | 指定哪些表不导出 |
| –no-autocimmit | 插入时不自动提交 |
| –routines | dump存储过程和函数 |
| –single-transaction | 备份是否在一个事务中进行 |
| –master-data | 生成change master to语句，等于1时语句不注释，等于2语句注释保证备份时数据和GTID是一致的 |
| –trigger | dump触发器 |
| –no-data | 只导出结构，不导出数据 |
| –set-gtid-purged | 是默认情况下, 导出的库中含有 GTID 信息, 如果不想导出包含有 GTID 信息的数据库, 需要显示地添加–set-gtid-purged=OFF参数 |
| -d | 结构(–no-data:不导出任何数据，只导出数据库表结构) |
| -t | 数据(–no-create-info:只导出数据，而不添加CREATE TABLE 语句) |
| -n | (–no-create-db:只导出数据，而不添加CREATE DATABASE 语句） |
| -R | (–routines:导出存储过程以及自定义函数) |
| -E | (–events:导出事件) |
同时不导出结构和数据可使用-ntd
备份一个表 单条insert导出
mysqldump -uroot -pxbsafe -h127.0.0.1 -P3307 --skip-opt --databases shoperate
–tables send_list >send_list3.sql
6. 备份脚本
编写脚本：
- 每天晚上3:30开始备份student这个库
- 备份文件存放到/backup目录下，要求备份的文件名里包含当天的日期（精确到天）
- 保留最近15天的备份文件
- 本地备份完成后，将备份文件上传到另外一台linux服务器的/backup_mysql目录下
先配置ssh免密通道
创建密钥对：[root@mysql~]# ssh-keygen -t ecdsa # -t 指定 密钥类型
上传公钥：[root@mysql.ssh]# ssh-copy-id -i id_ecdsa.pub root@192.168.209.100 # -i 指定公钥
脚本
[root@mysql mysql]# cat backup_db.sh
#!/bin/bash
#得到时间
ctime=$(date +%F_%H%M%S)
#在本地新建存放目录/backup_db
/usr/bin/mkdir -p /backup_db
#用which mysqldump查看mysqldump命令的绝对路径
#备份student库到/backup_db叫tennis.sql
/usr/local/mysql/bin/mysqldump -uroot -pSanchuang1234# student > /backup_db/${ctime}_student.sql
#备份mysql库里user表
/usr/local/mysql/bin/mysqldump -uroot -pSanchuang1234# mysql user >/backup_db/${ctime}_mysql_user.sql
#备份服务器上新建文件夹/backup_db
/usr/bin/ssh root@192.168.209.100 mkdir -p /backup_db
#上传当天备份的文件到备份服务器里192.168.209.100
/usr/bin/scp /backup_db/${ctime}*.sql root@192.168.209.100:/backup_db
#本地保留最近30天的备份文件
/usr/bin/find /backup_db -mtime +30 -type f -name "*.sql" -exec rm -rf {} \;
注意：计划任务中的PATH环境变量不一定与本机相同，命令尽量用绝对路径，避免crontab找不到命令
计划任务
[root@mysql~]# crontab -l
30 3 * * * /usr/bin/bash /root/backup_db.sh
扫一扫
2435
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
