Mysql数据库的备份与恢复
数据库备份与恢复
备份方案对比
| 方案 | 特点 | 优点 | 缺点 |
|---|---|---|---|
| 全量备份 | 对某一时间点所有数据的完全拷贝 | 数据恢复快 | 备份时间长，占用空间大 |
| 增量备份 | 备份上一次备份后变化的数据 | 备份时间短，占用空间小 | 恢复时需要按顺序，步骤繁琐 |
| 差异备份 | 备份上一次全量备份后变化的数据 | 恢复时只需全备和最后一次差异备份 | 备份时间与空间介于全量和增量之间 |
备份工具 mysqldump
常用命令
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替
全库备份
mysqldump -u用户名 -p密码 --all-databases > all-backup.sql
[root@xieyuhui2 ~]# mysqldump -uroot -predhat --all-databases > /opt/backupall.sql
备份指定库
mysqldump -u用户名 -p密码 --databases 库名 > db-backup.sql
备份xyh库
[root@xieyuhui2 ~]# mysqldump -uroot -predhat databases xyh > /opt/xyhdata20250917190322.sql
备份xyh库下的course表和student123表
[root@xieyuhui2 ~]# mysqldump -uroot -predhat xyh course student123 >
xyh-table-20250917190933.sql
删除xyh数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xyh |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database xyh;
Query OK, 3 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
数据恢复
恢复整个库/全库
mysql -u用户名 -p密码 < backup-file.sql
[root@xieyuhui2 ~]# mysql -uroot -predhat < /opt/backupall.sql
删除xyh库下的course表和student123表
mysql> drop table student123;
mysql> drop table course;
在MySQL命令行中恢复
source /path/to/backup.sql;
恢复表course和student123
mysql> source xyh-table-20250917190933.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
..........
mysql> show tables;
+---------------+
| Tables_in_xyh |
+---------------+
| course |
| student |
| student123 |
+---------------+
3 rows in set (0.00 sec)
差异备份与恢复（基于二进制日志）
开启二进制日志
[root@xieyuhui2 ~]# vim /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
server-id=1 #设置标识符
log-bin=mysql_bin #开启二进制日志
[root@xieyuhui2 ~]# systemctl restart mysqld
进行全量备份（需记录备份时刻的日志位置）
mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > full-backup.sql
[root@xieyuhui2 ~]# mysqldump -uroot -predhat --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > /opt/databases-20250917193822.sql
--single-transaction：
为 InnoDB 表创建一致性备份
通过开启一个事务来确保备份数据的一致性
不会锁定表，适合生产环境
--all-databases：
备份所有数据库（而不仅仅是单个数据库）
> /opt/databases-20250917193822.sql：
将备份输出重定向到指定文件
--flush-logs：
在开始备份前刷新日志，创建新的二进制日志文件
--master-data=:
将主服务器的二进制日志信息以注释形式包含在备份文件中
值为 2 表示注释掉这些信息（不会在执行恢复时自动应用）
这对于设置复制或基于时间点的恢复很重要
--delete-master-logs:
备份完成后删除所有旧的二进制日志文件
只保留最新的二进制日志文件（由 --flush-logs 创建的新文件）
恢复：
先恢复全量备份
mysql -u root -p < full-backup.sql
再使用 mysqlbinlog 恢复从全量备份点到故障点之间的二进制日志
mysqlbinlog --stop-position=794 mysql_bin.000001 | mysql -u root -p
增加新内容
mysql> insert into student(name,age) values('wutiaowu',25);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | wutiaowu | 25 |
+----+-------------+------+
删除student数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xyh |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database xyh;
Query OK, 3 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
刷新创建新的二进制日志
[root@xieyuhui2 data]# mysqladmin -uroot -predhat flush-logs
恢复全量备份
[root@xieyuhui2 ~]# mysql -uroot -predhat < /opt/databases-20250917193822.sql
[root@xieyuhui2 ~]# mysql -uroot -predhat -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xyh |
+--------------------+
[root@xieyuhui2 ~]# mysql -uroot -predhat -e 'show tables from xyh;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_xyh |
+---------------+
| course |
| student |
| student123 |
+---------------+
[root@xieyuhui2 ~]# mysql -uroot -predhat -e 'select * from xyh.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
#可以看到并没有增加新内容后的数据
检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000002'
-> ;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 1 | 290 | BEGIN |
| mysql_bin.000002 | 290 | Table_map | 1 | 343 | table_id: 141 (xyh.student) |
| mysql_bin.000002 | 343 | Write_rows | 1 | 393 | table_id: 141 flags: STMT_END_F |
| mysql_bin.000002 | 393 | Xid | 1 | 424 | COMMIT /* xid=498 */ |
| mysql_bin.000002 | 424 | Anonymous_Gtid | 1 | 489 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 489 | Query | 1 | 578 | drop database xyh |
| mysql_bin.000002 | 578 | Rotate | 1 | 625 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)
删除数据库前的pos为489
使用mysqlbinlog恢复差异备份
[root@xieyuhui2 ~]# mysqlbinlog --stop-position=489 /opt/data/mysql_bin.000002 |mysql -uroot -predhat
[root@xieyuhui2 ~]# mysql -uroot -predhat -e 'select * from xyh.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | wutiaowu | 25 |
+----+-------------+------+
使用shell脚本做到mysql数据库每月自动备份
[root@xieyuhui2 ~]# vim mysqlshell.sh
#！/bin/bash
BACKUP=/opt/backup/db
DATETIME=$(date +%Y_%m_%d_%H%M%S)
HOST=localhost
DB_USER=root
DB_PWD=redhat
DATABASE=xyh
[ ! -d "$BACKUP/$DATETIME" ] && mkdir -p "$BACKUP/$DATETIME"
mysqldump -u${DB_USER} -p${DB_PWD} --host=$HOST $DATABASE | gzip > $BACKUP/$DATETIME/$DATETIME.sql.gz
cd $BACKUP
tar -zcvf $DATETIME.tar.gz $DATETIME
rm -rf $BACKUP/$DATETIME
find $BACKUP -mtime +30 -name "*.tar.gz" -exec rm -rf {} \;
创建计划任务，在每个月1号的凌晨2点自动运行脚本
[root@xieyuhui2 ~]# crontab -e
0 2 1 * * mysqlshell.sh
这样就能做到自动备份mysql数据库
扫一扫
1529
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
