Mysql 数据库备份还原

备份类别

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

使用mysqldump实现逻辑备份

1
2
3
4
5
6
7
8
9
10
11
12
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例:
#单库备份
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

恢复逻辑备份

1
2
3
4
5
6
7
8
9
10
11
12
#方法一:
[root@localhost backup]# mysql -uroot -p123 < /backup/all.sql

#方法二:
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;
mysql> source /root/db1.sql

#注:如果备份/恢复单个库时,可以修改sql文件
DROP database if exists school;
create database school;
use school;

备份/恢复案例

#数据库备份/恢复实验一:数据库损坏

备份:

1
2
3
4
5
# mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
# mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
#插入数据 //模拟服务器正常运行
mysql> set sql_log_bin=0; //模拟服务器损坏
mysql> drop database db;

恢复:

1
2
3
4
# mysqlbinlog 最后一个binlog > /backup/last_bin.log
mysql> set sql_log_bin=0;
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份
mysql> source /backup/last_bin.log //恢复最后个binlog文件

#数据库备份/恢复实验二:如果有误删除

备份:

1
2
3
4
5
mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
# 插入数据 //模拟服务器正常运行
drop table db1.t1 //模拟误删除
#插入数据 //模拟服务器正常运行

恢复:

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

注意事项: 1. 完全恢复到一个干净的环境(例如新的数据库或删除原有的数据库) 2. 恢复期间所有SQL语句不应该记录到binlog中

感谢搬砖