1、备份/恢复策略：
2、逻辑备份和恢复：
对于各种存储引擎，都可以用同样的方法来备份；
2.1、备份：
将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑；
例：
mysqldump -uroot -p'密码' --all-database > all.sql #备份所有数据库；
mysqldump -uroot -p'密码' test > test.sql #备份数据库test；
mysqldump -uroot -p'密码' test emp > emp.sql #备份数据库test下的表emp；
mysqldump -uroot -p'密码' test emp dept > emp_dept.sql #备份数据库test下的表emp和dept；
mysqldump -uroot -T /tmp test emp --fileds-terminated-by',' #备份数据库test下的所有表为逗号分割 的文本，备份到/tmp;
为保证数据一致性:
myisam存储引擎在备份的时候需要加上-l参数，将所有表加上读锁，在备份期间， 所有表将只能读而不能进行数据更新；
innodb和BDB存储引擎，采用选项--single-transaction使innodb存储引擎得到一个 快照(snapshot),使得备份数据能够保证一致性。
2.2、完全恢复：
mysql -uroot -p'密码' dbname < bakfile #将备份作为输入执行
mysqlbinlog binlog-file |mysql -uroot -p'密码' #将备份恢复后数据并不完整，还需要将备份 后执行的日志进行重做；
例：mysqldump -uroot -ppwd -l -F minmin >minmin.dmp #备份数据库minmin，-l给所有 表加读锁，-F生成一个新的日志文件；
insert into num values(21,'min') #插入新数据；
mysql -uroot -ppwd minmin
select * from num; #恢复原来数据，之前插入的数据没了；
mysqlbinlog mysql-bin.000014 |mysql -uroot -ppwd minmin; #使用mysqlbinlog恢复 自mysqldump备份以来的binlog；
2.3、基于时间点恢复(不完全恢复)：分为基于时间点的恢复和基于位置的恢复。
例：mysqlbinlog --stop-date="2011-05-20 09:59:59" /home/data/mysql/mysql-bin.000015|mysql -uroot -ppwd #如果10点发生了误操作，用备份和binlog将数据恢复到鼓掌前；
mysqlbinlog --start-date="2011-05-20 10:01:00" /home/data/mysql/mysql-bin.000015|mysql -uroot -ppwd #跳过故障时间点，继续执行后面的binlog完成恢复。
2.4、基于位置恢复：相对时间恢复更精确，因为同一个时间点可能有很多条SQL语句同时执行。
例： mysqlbinlog --start-date="2011-05-20 9:55:00" --stop-date="2011-05-20 10:05:00" /home/data/mysql/mysql-bin.000015 > /tmp/mysql_restore.sql #在/tmp目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号；
mysqlbinlog --stop-position="123456" /var/log/mysql/bin.123456|mysql -uroot -ppwd
mysqlbinlog --start-pasition="123459" /var/log/mysql/bin.123456|mysql -uroot -ppwd #通过前后位置号（“123456”“123459”）将恢复从给定的起始位置直到二进制日志结束的所有事务。
3、物理备份和恢复：
分为冷备份和热备份两种，相比逻辑备份，备份和恢复的速度快，物理备份的原理都是基于文件的cp。
3.1、冷备份：
停掉数据库服务，cp数据文件。对Myisam和innoDB存储引擎都适合。
3.2、热备份：
3.2.1、Myisam存储引擎
例：mysqlhotcopy dbname PATH(备份路径) #mysqlhotcopy是mysql自带的一个热备份工具；
mysql>flush tables for read; #手工锁表copy，然后cp数据文件到备份目录。
3.2.2、innoDB存储引擎
ibbackup工具是收费的，可免费用1个月。
4、表的导入导出：
4.1、导出：
4.1.1、 select * from tablename into outfile ‘target_file’ [option];
option参数选项:
fields terminated by 'string' (字段分隔符，默认为制表符'\t')；
fields [optionally] enclosed by 'char' (字段引用符，如果加optionally选项则只用在char、varchar和text等字符型字段上。默认不使用引用符)；
fields escaped by 'char' (转义字符，默认为'\');
lines starting by 'string' (每行前都加此字符串，默认'');
lines terminated by 'string' (行结束符，默认为'\n');
注：其中char表示此符号只能是单个字符，string表示可以是字符串。
例：select * from pro_file into outfile '/tmp/pro.txt' fields terminated by "," enclosed by '"'; #将pro表中数据导出为数据文本，字段分隔符为“,”，字段引用符为“"”(双引号)，记录结束符为回车符；
mysql>system more /tmp/pro.txt;
select * from pro_file into outfile '/tmp/pro.txt' fields terminated by "," optionally enclosed by '"'; #optionally参数取消了数值型的引用符“；
mysql导出的数据中需要转义的字符，主要包括三类：转义字符本身、字段分隔符、记录分隔符；
例1：mysql>update emp set ename='\\"#!a' where deptno=5; #更新一条数据，条件deptno=5；
mysql>select * from emp where deptno=5 into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"'; #将满足条件deptno=5的数据导出到emp.txt文件，字段分隔符为，字段引用符为“；
mysql>system cat /tmp/emp.txt #通过system 使用系统命令来查看导出的文件内容"\\\"#!a",22,\N,5,\N(输出的文件中\和"两种字符都加上了转移字符\)。
例2：mysql>update emp set ename='\\"#,#,!a' where sal is null; #更新数据，条件为sal为空；
mysql>select * from emp where sal is null into outfile '/tmp/emp1.txt' fields terminated by "," optionally enclosed by '"'; #将满足条件sal为空的数据导出到emp1.txt文件，字段分隔符为，字段引用符为";
mysql>system cat /tmp/emp1.txt # 导出的数据内容"\\\"#,#,!a",44,\N,2,\N ","没有被转义；
例3：mysql>select * from emp where sal is null into outfile '/tmp/emp2.txt' fields terminated by ","; #只定义了分隔符，；
mysql>system cat /tmp/emp2.txt #内容为\\"#\,#\,!a,44,\N,2,\N输出的数据中,被转义；
注：当导出命令中包含字段引用符时，数据中含有转义字符本身和字段引用符的字符需要被转义；
当导出命令中不包含字段引用符时，数据中含有转义字符本身和字段分隔符的字符需要被转义。
4.1.2、mysqldump导出数据为文本
mysqldump -uusername -T target_dir dbname tablename [option]
option参数：
--fields-terminated-by=name (字段分隔符)；
--fields-enclosed-by=name (字段引用符)；
--fields-optionally-enclosed-by=name (字段引用符，只用在char、varchar和text等字符型字段上)；
--fields-escaped-by=name (转义字符)；
--lines-terminated-by=name (记录结束符)。
例：mysqldump -uroot -T /tmp test emp --fields-terminated-by ',' --fields-optionally-enclosed-by '"' #到出的数据为文本格式，字段分隔符为,字段引用符为"同时还生成一个emp.sql文件;
4.2、导入
4.2.1、使用load data infile 命令：
mysql>load data [local] infile 'filename' into table tablename [option]
option选项：
fields terminated by 'string' (字段分隔符，默认为制表符'\t');
fields [optionally] enclosed by 'char' (字段引用符，如果加optionally选项则只用在char、varchar和text等字符型字段上。默认不实用引用符)；
fields escaped by 'char' (转义字符，默认为'\');
lines starting by 'string' (每行前都加此字符串，默认'');
lines terminated by 'string' (行结束符，默认为'\n');
ignore number lines (忽略输入文件中的前n行数据);
(col_name_or_user_var,...) (按照列出的字段顺序和字段数量加载数据);
set col_name=expr,... 将列做一定的数值转换后再加载。
例：mysql>delete from num1; #先清除表中所有数据；
mysql>load data infile '/tmp/num1.txt' into table num1 fields terminated by ',' enclosed by '"'; #将/tmp/num1.txt中数据加载到表num1中；
mysql>select * from num1; #数据都恢复到导出前；
mysql>load data infile '/tmp/num1.txt' into table num1 fields terminated by ',' enclosed by '"' ignore 2 lines; #导入不包括前两行的数据；
mysql>load data infile '/tmp/num1.txt' into table num1 fields terminated by ',' enclosed by '"' ignore 2 lines(id,content,name); #修改列的前后顺序；
mysql>load data infile '/tmp/num1.txt' into table num1 fields terminated by ',' enclosed by '"' ignore 2 lines (id); #仅加载第一列(id);
mysql>load data infile '/tmp/num1.txt' into table num1 fields terminated by ',' enclosed by '"' set id=id+10; #将id列的内容+10后再加载到表中；
4.2.2、mysqlimport导入：
shell>mysqlimport -uroot -ppwd [--local] dbname order_tab.txt [option]
option参数选项：
--fields-terminated-by=name (字段分隔符)；
--fields-enclosed-by=name (字段引用符)；
--fields-optionally-enclosed-by=name (字段引用符，只用在char、varchar和text等字符型字段上)；
--fields-escaped-by=name (转义字符)；
--lines-terminated-by=name (记录结束符)；
--ignore-lines=number (或略前几行)。
例：mysqlimport -uroot minmin /tmp/num1.txt --fields-terminated-by=',' --fields-enclosed-by='"' #mysqlimport导入数据到数据库minmin表num1。
mysql minmin -e 'select count(10) from num1' #查看导入数据列；
mysql minmin -e 'select * from num1' #查看导入数据内容；
注：跨平台导入和导出，windows上设置为line-terminated-by='\r\n',linux上设置为line-terminated-by='\n'。
阅读(1074) | 评论(0) | 转发(0) |
