数据备份

1 IDE工具介绍

生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用IDE工具

Navicat下载地址https://www.cr173.com/soft/126934.html

Navicat能够充当多个数据库的客户端，内部封装了所有的操作数据库的命令，可视化操作数据库

掌握：
#1. 测试+链接数据库  
文件-->新建链接-->选择对应的数据库软件
链接名不写，默认localhost+port
输入用户、密码-->测试链接
双击链接，查看所有库 show databases;
单击选中某个库 use db;
双击选中某个库 show tables;
双击选中某个表 select * from t1;
鼠标右键某表-->设计表可以查看表结构 desc t1;

#2. 新建库
右键链接名-->新建数据库-->只用选择字符集  create database db1;

#3. 新建表，新增字段+类型+约束
进入一个库-->右键表-->新建表
    -设置字段名、字段类型、宽度
    -id设置为主键(空格)，勾选自动递增
    
#4. 设计表：外键
右键表-->设计表-->添加字段
如: dep_id-->点击外键-->字段选择dep_id-->被引用表(dep_id要关联的表)选择dep表-->被引用字段选择id-->级联更新与删除选择cascade

#5. 新建查询
手动写SQL语句，自动补全功能、选择单行运行功能、快速注释功能、美化SQL功能

#6. 备份（与还原）库/表
转储SQL文件：右键库-->转储SQL文件-->结构与数据-->选择文件保存地址
运行SQL文件：右键库-->运行SQL文件-->选择之前保存的文件

#7. 逆向数据库模型
右键库-->逆向数据库模型
生成一个图形化界面，把所有有关系表连在一起，可快速查看表的关系与关系字段
可以用建模型的方式创建表

#注意：
批量加注释：ctrl+？键
批量去注释：ctrl+shift+？键

 

2 MySQL数据备份

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

2.1 使用mysqldump实现逻辑备份

#语法：
# 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

2.2 恢复逻辑备份

#方法一：
[root@egon 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;

2.3 备份/恢复案例

#数据库备份/恢复实验一：数据库损坏
备份：
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中

2.4 实现自动化备份

备份计划：
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
2 * * * /mysql_back.sql

2.5、表的导出和导入

SELECT... INTO OUTFILE 导出文本文件
示例：
mysql> SELECT * FROM school.student1
INTO OUTFILE 'student1.txt'
FIELDS TERMINATED BY ',' //定义字段分隔符
OPTIONALLY ENCLOSED BY '”' //定义字符串使用什么符号括起来
LINES TERMINATED BY '\n' ; //定义换行符


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> DELETE FROM student1;
mysql> LOAD DATA INFILE '/tmp/student1.txt'
INTO TABLE school.student1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '”'
LINES TERMINATED BY '\n';

报错：Variable 'secure_file_priv' is a read only

#可能会报错
mysql> select * from db1.emp into outfile 'C:\\db1.emp.txt' fields terminated by ',' lines terminated by '\r\n';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable


#数据库最关键的是数据，一旦数据库权限泄露，那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走，因而mysql对此作了限制，只能将文件导出到指定目录
在配置文件中
[mysqld]
secure_file_priv='C:\\' #只能将数据导出到C:\\下

重启mysql
重新执行上述语句

2.6 数据库迁移

务必保证在相同版本之间迁移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456