mysql笔记六之备份与恢复

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'。