MySQL数据备份与恢复的常见方法及实践经验总结

物理备份&＃xff1a; 直接复制数据库文件&＃xff0c;适用于大型数据库环境。但不能恢复到异构系统中如Windows。
逻辑备份&＃xff1a;备份的是建表、建库、插入等操作所执行SQL语句&＃xff0c;适用于中小型数据库&＃xff0c;效率相对较低。
导出表&＃xff1a; 将表导入到文本文件中。

一、使用mysqldump实现逻辑备份

1、语法

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql


2、示例&＃xff1a;

单库备份

mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql


多库备份

mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql


备份所有库

mysqldump -uroot -p123 --all-databases > all.sql


二、恢复逻辑备份

方法一&＃xff1a;

mysql -uroot -p123

方法二&＃xff1a;

mysql> use db1;
mysql> SET SQL_LOG_BIN&＃61;0;
mysql> source /root/db1.sql


#注&＃xff1a;如果备份/恢复单个库时&＃xff0c;可以修改sql文件

DROP database if exists school;
create database school;
use school;


三、备份/恢复案例

1、数据库备份/恢复实验一&＃xff1a;数据库损坏

备份&＃xff1a;

mysqldump -uroot -p123 --all-databases > /backup/&＃96;date &＃43;%F&＃96;_all.sql
mysql -uroot -p123 -e &＃39;flush logs&＃39; #截断并产生新的binlog


插入数据 //模拟服务器正常运行

mysql> set sql_log_bin&＃61;0; //模拟服务器损坏
mysql> drop database db;


恢复&＃xff1a;

mysqlbinlog 最后一个binlog > /backup/last_bin.log
mysql> set sql_log_bin&＃61;0;
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份
mysql> source /backup/last_bin.log //恢复最后个binlog文件


数据库备份/恢复实验二&＃xff1a;如果有误删除

备份&＃xff1a;

mysqldump -uroot -p123 --all-databases > /backup/&＃96;date &＃43;%F&＃96;_all.sql
mysql -uroot -p123 -e &＃39;flush logs&＃39; //截断并产生新的binlog
插入数据 //模拟服务器正常运行
drop table db1.t1 //模拟误删除
插入数据 //模拟服务器正常运行


恢复&＃xff1a;

mysqlbinlog 最后一个binlog --stop-position&＃61;260 > /tmp/1.sql
mysqlbinlog 最后一个binlog --start-position&＃61;900 > /tmp/2.sql
mysql> set sql_log_bin&＃61;0;
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份
mysql> source /tmp/1.log //恢复最后个binlog文件
mysql> source /tmp/2.log //恢复最后个binlog文件


注意事项&＃xff1a;

1. 完全恢复到一个干净的环境&＃xff08;例如新的数据库或删除原有的数据库&＃xff09;
2. 恢复期间所有SQL语句不应该记录到binlog中