MySQL学习19_数据导入导出备份恢复

表复制

• 对数据库表的结构、数据等进行复制
• 复制方式：

# 1、只复制表结构，包括主键、索引，但不会复制表数据
# 将otherTablename表结构复制到tableName表
create table tableName like otherTablename;

# 2、只关注表数据，复制表的大体结构及全部数据，不会复制主键、索引等
create table tableName select * from otherTablename;

# 3、完整复制，表结构+数据
# 分两步完成，先复制表结构，再插入数据
create table tableName like otherTablename;
insert into tableName select * from otherTablename;

————————————————————————————————————————————

导出数据

select…into outfile

• 在sql命令行中执行该命令，将结果导出到指定文件

select * from table_name into outfile 'file_path'
fields terminated by ','     # 字段之间的分隔符
enclosed by ""               # 字段值使用双引号包含
lines terminated by '\r\n';   # 行间分隔符

• 导出文件的路径file_path需要参考secure_file_priv取值

secure_file_priv取值	描述
null	表示不允许导入导出
空	表示没有任何限制
指定路径	表示导入导出只能在指定路径下完成

mysql命令结果重定向

1. 使用mysql命令连接数据库;
2. 添加-D选项，指定数据库；
3. 添加-e选项，执行指定的SQL语句；
4. 结合DOS的重定向操作符”>”可以将查询结果导出到文件。

mysql -h localhost -u root -p -D database_name -e "select * from table_name" > file_path

使用mysqldump导

• mysqldump是MySQL用于转存储数据库的实用程序，它主要产生一个SQL脚本，其中包含创建数
  据库、创建数据表、插入数据所必需的SQL语句。

# 导出指定数据库（含数据）
mysqldump -h localhost -u root -p database_name > file_path/filename.sql

# 导出指定数据库（不含数据）
mysqldump -h localhost -u root -p database_name --no-data > file_path/filename.sql

# 导出指定数据库中的指定数据表
mysqldump -h localhost -u root -p database_name table_name > file_path/filename.sql

# 导出指定数据库，忽略指定数据表
mysqldump -h localhost -u root -p database_name --ignore-table database_name.table_name > file_path/filename.sql

————————————————————————————————————————————

数据导入

load data

• 使用load data语句将文本文件数据导入到对应的数据库表中，可以将load data语句看成是select…into outfile的反操作。

load data infile 'file_path' into table table_name character set utf8
fields terminated by ','     # 字段之间的分隔符
enclosed by ""               # 字段值使用双引号包含
lines terminated by '\r\n';   # 行间分隔符

• 与select…into outfile导出数据一样，导入文件的路径file_path需要参考secure_file_priv取值
  

source命令

• 使用source命令导入较大的SQL文件；
• source命令可以导入使用mysqldump备份的sql文件。

source sql_file_path;

---

数据定时备份

windows

• schtasks.exe用于安排命令和程序在指定时间内运行或定期运行，它可以从计划表中添加和删除任务、按需要启动和停止任务、显示和更改计划任务。

# 备份数据库的脚本mysql_mydb_backup.bat填写下面一行内容
mysqldump -h localhost -u root -p passwd database_name > file_path/file_name.sql

# 创建计划任务（每隔指定时间备份一次MySQL）, backup_task_name：定时任务名称
schtasks /create /sc minute /mo 1 /tn backup_task_name /tr file_path\mysql_mydb_backup.bat

# 删除计划任务
schtasks /delete /tn backup_task_name 

linux

1. 编写mysql备份脚本mysql_mydb_backup.sh

#!/bin/bash

#备份目录
backup_dir=/home/mysql/backup

#备份文件名
backup_filename="mydb-`date +%Y%m%d`.sql"

#进入备份目录
cd $backup_dir

#备份数据库, passwd和database_name需要替换掉
mysqldump -h localhost -u root -p passwd database_name > ${backup_dir}/${backup_filename}

#删除7天以前的备份
find ${backup_dir} -mtime +7 -name "*.sql" -exec rm -rf {} \;

2. crontab命令用于周期性执行任务

————————————————————————————————————————————

数据恢复

bin log日志

• MySQL的二进制日志，记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的；
• 如果遇到数据丢失的紧急情况下，可以使用binlog日志进行数据恢复（定时全备份+binlog日志恢
  复增量数据部分）。

#查看所有二进制日志列表
show master logs;

#查看正在使用的二进制日志
show master status;

#刷新日志（重新开始新的binlog日志文件）
flush logs;

#查询指定的binlog
show binlog events in 'binlog_name' from 10668\G;

#导出恢复数据用的sql
mysqlbinlog "binlog_file_path" --start-position 528 --stop-position 1191 > file_path\file_anme.sql

---

知识总结，交流学习，不当之处敬请指正，谢谢！