mysql数据备份与恢复

时间:2022-09-13 23:44:56

MySQL数据备份与恢复

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

一、使用mysqldump实现逻辑备份

#语法:
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql     (本地使用可以省略 -h 服务器名)
当用户登陆不需要密码时(密码为空或在配置文件中已输入账号密码,登陆时无需再输入密码)
语法为:mysqldump -h 服务器 -u用户名  数据库名 > 备份文件.sql     (本地使用可以省略 -h 服务器名)


#示例(以下是有密码的时候的操作):
#单库备份
备份库中所有的表
mysqldump -uroot -p123 db1 > D:\\db1.sql         #将数据库db1备份到D盘下,文件名称为db1.sql
备份库中部分表
mysqldump -uroot -p123 db1 table1 table2 > D:\\db1-table1-table2.sql  #将数据库db1里的表table1和table2备份到D盘下名称为db1-table1-table2.sql的文件中

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 >D:\\db1_db2_mysql_db3.sql   #将数据库db1,db2,mysql,db3备份到D盘中

#备份所有库
mysqldump -uroot -p123 --all-databases >D:\\all.sql  #将root用户的所有数据库全部备份到D盘中,文件名为all.sql

 

二、恢复逻辑备份

#恢复多个库:(直接指定用户,不需要指定数据库名)
# mysql -uroot -p123 < D:\\all.sql   将备份至D盘中的所有数据库,恢复至root用户下


#恢复单个库:
#方法一:不需要进入mysql程序,直接在终端输入
mysql -uroot -p123 db1 < D:\\db1.sql 


#方法二:
从终端先进入mysql程序,然后输入
mysql> use db1;
mysql> source D:\\db1.sql    


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

 

PS:不进入mysql,直接在终端执行mysql语句的方法:
如:查看root用户school数据库下的所有表
C:\Users\Administrator>mysql -uroot -e "use school;show tables;"   (windows系统下必须是双引号)
+------------------+
| Tables_in_school |
+------------------+
| class            |
| course           |
| score            |
| student          |
| teacher          |
+------------------+

 

三、备份/恢复案例

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

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


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

恢复:
1. # mysqlbinlog 最后一个binlog --stop-position=260 > /tmp/1.sql 
# mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql 
2. 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中
案例

 

四、实现自动化备份

mysql数据备份与恢复mysql数据备份与恢复
备份计划:
1. 什么时间 2:00
2. 对哪些数据库备份
3. 备份文件放的位置

备份脚本:
[root@egon ~]# vim /mysql_back.sql
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123

if [ ! -d /backup ];then
mkdir -p /backup
fi

# 备份并截断日志
mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}
mysql -u${user} -p${pass} -e 'flush logs'

# 只保留最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} \;

手动测试:
[root@egon ~]# chmod a+x /mysql_back.sql 
[root@egon ~]# chattr +i /mysql_back.sql
[root@egon ~]# /mysql_back.sql

配置cron:
[root@egon ~]# crontab -l
0 2 * * * /mysql_back.sql
View Code

 

五、表的导出和导入

SELECT... into outfile 导出文本文件
示例:
mysql> 
select * from school.student1
into outfile 'E:\\student1.txt'   //指定了导出文件的路径和文件名
fields terminated by ','      //定义文本中字段显示的分隔符
(optionally enclosed by '')不一定要写    //定义字符串使用什么符号括起来
lines terminated by '\n' ;    //定义换行符

执行会报错,以前旧版本可以执行,新版为了保护数据安全,不能直接导出
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

  
mysql> show variables like '%secure%';  #查看相关设置
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+

set global secure_auth=OFF;
set  secure_auth=OFF;
以上两种直接更改设置的方法都是行不通的,需要更改配置文件
[mysqld]
secure-file-priv='E:\\'
在配置文件里更改配置后,即可执行导出操作



mysql 命令导出文本文件
示例:
# mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt
# mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml
# mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html

load data infile 导入文本文件(要按照表结构导入)
mysql> load data infile 'E:\\student1.txt'
into table school.student1
fields terminated by ','
(optionally enclosed by '')
lines terminated by '\n';

 

六、数据库迁移

务必保证在相同版本之间迁移
# mysqldump (-h 源IP) -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456
括号内的可以不写,因为源IP就是自己,不需要指定