mysql数据备份与恢复

发布时间：2020-12-12 00:03:01 所属栏目：MySql教程来源：网络整理

导读：MySQL数据备份与恢复一、使用mysqldump实现逻辑备份备份文件.sql （本地使用可以省略 -h 服务器名） -h 服务器 -u用户名数据库名 > 备份文件.sql （本地使用可以省略 - span style="color: #008000;"# span style="color: #008000;"示例（以下是有密码的

MySQL数据备份与恢复

一、使用mysqldump实现逻辑备份

备份文件.sql （本地使用可以省略 -h 服务器名） -h 服务器 -u用户名数据库名 > 备份文件.sql （本地使用可以省略 -#示例（以下是有密码的时候的操作）：

单库备份

备份库中所有的表
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程序，直接在终端输入

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;

>mysql -uroot -e +------------------+
| Tables_in_school |
+------------------+
|             |
| course           |
| score            |
| student          |
| teacher          |
+------------------+

三、备份/恢复案例

数据库备份/1. /backup/`date +%F`_all.sql 2. 3. 插入数据 //4. mysql> set sql_log_bin=0; //>恢复：
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

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中

四、实现自动化备份

1. 什么时间 2:00 23备份脚本：
[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

五、表的导出和导入

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

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';

六、数据库迁移

括号内的可以不写，因为源IP就是自己，不需要指定

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

mysql数据备份与恢复

MySQL数据备份与恢复

一、使用mysqldump实现逻辑备份

<span style="color: #008000;">单库备份

二、恢复逻辑备份

<span style="color: #008000;">方法一：不需要进入mysql程序，直接在终端输入

三、备份/恢复案例

<span style="color: #008000;"> mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql

四、实现自动化备份

<span style="color: #008000;">!/bin/bash

五、表的导出和导入

<span style="color: #008000;"> mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml<span style="color: #008000;">

<span style="color: #008000;"> mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html

六、数据库迁移