备份与恢复

逻辑备份：

mysqldump [options] db_name [tables]

mysqldump [options] --databases DB1 [DB2  DB3]

mysqldump [options] --all-database

举例：

1）备份所有数据库

mysqldump -uroot -p --all-database > all.sql

2）备份数据库 test

mysqldump -uroot -p test > test.sql

3）备份数据库 test 下的表 emp

mysqldump -uroot -p test emp > emp.sql

4）备份数据库 test 下的表 emp 和 dept

mysqldump -uroot -p test emp dept > emp_dept.sql

5）备份数据库 test 下的所有表为 以逗号 分割的文本，备份到 /tmp

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

myisam 存储引擎在备份的时候需要加上 -l 参数，表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新

innodb 引擎采用选项 --single-transaction ，使得innodb 引擎得到一个快照，使得备份的数据能够保证一致性

恢复：

mysql -uroot -p dbname < bakfile

此时备份恢复后的数据并不完整，需要将备份后执行的日志进行重做

mysqlbinlog binlog-file | mysql -uroot -p test

举例：

1.mysql -uroot -p -l -F test > test.dmp

-l 表示给所有表加读锁，-F 表示生成一个新的日志文件

2.当数据库崩溃的时候恢复数据

mysql -uroot -p test < test.dmp

执行 mysqlbinlog 恢复自 mysqldump 备份以来的 binlog

mysqlbinlog localhost-bin.000015 | mysql -uroot -p test

不完全恢复——————————————————————————

基于时间点恢复：

将数据库恢复到 上一次备份的数据，mysql -uroot -p test < test.emp

再对 binlog 日志进行恢复。。。执行到 10:00:00 之前，然后跳过 10：00:00 的时间点

1.首先将数据恢复到故障前

mysqlbinlog --stop-date="2016-07-14 9:59:59" /data01/mysql/mysql-bin.000012 | mysql -uroot -p

2.跳过故障时的时间点，继续执行后面的 binlog 

mysqlbinlog --start-date="2016-07-14 10:01:00" /data01/mysql/mysql-bin.000012 | mysql -uroot -p

基于位置恢复：（相对于 基于时间点恢复更精确）

1.首先进行 binlog 日志确定位置查找：

mysqlbinlog --start-date="2016-07-14 9:55:00" --stop-date="2016-07-14 10:05:00" /data01/mysql/mysql-bin.000012 > /tmp/mysql_restore.sql

2.记录下错误操作的sql 位置号，假设为268312 和 268315

mysqlbinlog --stop-position="268312" /data01/mysql/mysql-bin.000012 | mysql -uroot -p

mysqlbinlog --start-position="268315" /data01/mysql/mysql-bin.000012 | mysql -uroot -p

物理备份和恢复：

冷备份：

备份：停掉数据库，cp 数据文件和日志文件到备份的目录。对于 myisam 和 innodb 存储引擎都适合，但是不实用

恢复：停掉数据库，cp 数据文件，重启，使用 mysqlbinlog 工具恢复自备份以来所有 binlog

热备份：

myisam:

1.使用 mysqlhotcopy 工具

mysqlhotcopy db_name [/path/to/new_directory]

--allowold  如果备份路径下含有同名备份，则将旧的备份目录 rename 为目录名 _old

--addtodest 如果备份路径下存在同名目录，则仅仅将新的文件加入目录

--noindices  不备份所有的索引文件

--flushlog  表被锁定后刷新日志

也可以使用 mysqlhotcopy -help 查看帮助

2.手工锁表 copy

首先所有数据表加读锁

flush tables for read;

然后cp 数据文件到 备份目录

innodb 存储引擎：

ibbackup  要钱

表的导入导出

导出：

1.select ...into outfile... 命令导出数据

select * from tablename into outfile 'target_file' [option];

fields terminated by 'string' (字段分隔符，默认为 制表符 '\t')

fields optionally enclosed by 'char' (字段引用符)

fields escaped by ‘char’ (转义字符,默认为 '\')

liens starting by 'string' (每行前都加此字符串，默认'')

lines terminated by 'string' (行结束符，默认为 '\n')

1.select * from emp into outfile '/tmp/emp.txt' fields terminated by "," enclosed by '"';

2.select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"';

3.对表 emp 中 的name 更新为含 "\", 字段分隔符，记录分隔符的数据，然后导出

update emp set name='\\"##!aa' where id=1;

select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"';

此时 \ 被转义为 \\ 

4.将字段 id 为1 的name 更新为含有字段分隔符 “,”的字符串

update emp set name='\\"#,#,!aa' where id = 1;

select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"';

此时因为字段被 " " 包含，所以 ',' 没有被转义

5.select * from emp into outfile '/tmp/emp.txt' fields terminated by ",";

导入：

方法一、

load data infile ... 命令

load data [local] infile 'filename' into table tablename [option]

option 选项：

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;

3.文件中的列顺序和表中的列顺序不符，或者只想加载部分列

load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id,content,name);

4.希望将 id 列的内容 +10 后再加载到表中

load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' set id=id+10;

方法2 

mysqlimport -uroot -p [--LOCAL] dbname order_tab.txt [option]

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