MySQL数据的备份恢复与导出导入

一、数据备份与恢复

数据的备份可以有两种方式，逻辑备份和物理备份  

1、逻辑备份

mysql中用mysqldump来完成

备份：

mysqldump -uroot -p  --all-database > all.sql          #所有库

mysqldump -uroot -p  test > test.sql                        #test库

mysqldump -uroot -p  test emp  dept> emp_dept.sql          #test库中emp表和dept表

将test中的所有表保存为以逗号分隔的文本，备份到/tmp下:

myslqdump  -uroot -p -T /tmp  test emp dept --fields-terminated-by ','

注意：一般在备份时加-l 和-F参数，-l为所有表加上读锁，在进行备份时，不能进行数据更新 ，-F 表示生成一个新的日志文件，否则恢复数据可能不完整

恢复：

mysqldump -uroot -p test <test.sql

完全恢复：

场景：如果9：00备份完，又插入了新的数据，但是10：00数据库突然故障，按照以上的语法不能恢复新插入的数据，我们用musqlbinlog来恢复自mysqldump备份以来的binlog，也称为完全恢复

mysqlbinlog mysql-log-000006 | mysql -uroot -p test

不完全恢复：

场景：如果误删除了一张表，采用上述语句也就是完全恢复，是不能恢复这张表的，因为binlog中还存在那条误删除的操作，所以我们要恢复这条误删除操作之前的语句，以达到我们的目的，这种恢复叫做不完全恢复，有基于时间和位置的恢复两种，其原理都是通过关于时间和位置的参数，将这条误删除的操作语句从binlog中剔除出去

基于时间：

假设是10点发生的误删除操作

mysqlbinlog --stop-date="2019-03-03 9:59:59"  /data/mysql-log-12345 | mysql -uroot -p

mysqlbinlog --start-date="2019-03-03 10:01:00"  /data/mysql-log-12345 | mysql -uroot -p

基于位置：

先找出误操作语句前后的位置号,并输出到/tmp下的一个小的文本文件

mysqlbinlog --start-date="2019-03-03 9:55:00"  --stop-date="2019-03-03 10:05:00"  /data/mysql-log-12345  > /tmp/mysql_restore.sql

例如前后的位置号分别是366778和366780

然后根据前后的位置号，恢复在指定日志中除了这个位置号之间的所有语句操作

mysqlbinlog --stop-position="366778"  /data/mysql-log-12345 | mysql -uroot -p

mysqlbinlog --start-position="366780"  /data/mysql-log-12345 | mysql -uroot -p

2、物理备份 

物理备份的优点是备份和恢复的速度更快，因为它是基于文件的copy。物理备份又分为冷备份和热备份

冷备份就是停掉数据库服务，进行数据copy的方法，也是用到mysqlbinlog工具。但一般不会停掉数据库服务

对于热备份，不同的存储引擎有不同的备份方式

MyIsam存储引擎的热备份，用mysqlhotcopy工具

InnoDB存储引擎中常用的热备工具有：ibbackup（收费）和Xtrabackup（innobackupex）

二、数据的导入与导出：

工作中有时需要将数据库中的数据导出为某些符号分割的纯数据的文本，而不是sql语句。在以下需求时用数据的导入导出

**用来以excel显示

**为了节省备份空间

**为了快速的加载数据，（load data的加载速度比普通的sql加载速度要快20倍以上）

导出

导出有两种方式：

1、用select  into  file导出

格式：select * from tablename into outfile "target_file" [option]

option包括：

fields terminated by ","               #字段分隔符

fields optionally enclosed by '"'     #字段引用符 optionally表示只在字符类型的字段上加指定的字段引用符

fields escaped by ‘char’      #转义字符，默认是‘\’

lines starting by 'string'         #指定每一行的最前 面加的字符串。默认为空

lines terminated by ‘string’   #指定行结束符，默认为回车换行  '\n'

例：>select * from emp into outfile "/tmp/emp.text" fields terminated by ',' optionally encolsed by '"';

cat /tmp/emp.text

2、用mysqldump导出

#mysqldump -uroot -p -T /tmp test emp --fields-terminated-b=',' --fields-optionally-enclosed-by='"'

除了在/tmp下生成emp.text之外，还生成一个emp.sql文件，里面记录了emp表的创建脚本

其实，mysqldump只是调用了select into file提供的接口而已，其语法很类似，mysqldump在MySQL外部执行，into file在MySQL内部执行

导入

导入也有两种方式:

1、用load data infile  导入

格式：load data infile  'filename' into table  tablename [option]

option 和select into file相同，又多加了两个选项

ignore number lines （col_name1,col_name2,...）      #忽略导入文件中的前n行数据，括号表示按照指定的字段顺序进行显示，即可以调整字段顺序，也可以指定显示哪几个字段

set col_name=...       #将某个字段的那一列做一定的数值计算后再加载

例1：load data infile '/tmp/emp.txt'  into table emp fields terminated by ',' enclosed by '"';

例2：load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id,name);  #忽略表中的前2行数据，并按照id,name的顺序显示

例3：load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' set id=id+10;                #加载全部数据，并将id字段那一列的所有数据都加10后再显示。

2、用mysqlimport导入

语法、option和mysqldump几乎相同

mysqlimport -uroot -p test /tmp/emp.txt --fields termonated-by=',' --fields-optionally-enclosed-by='"'

 

如有错误，敬请指正~~