Chinaunix首页 | 论坛 | 博客
  • 博客访问: 179450
  • 博文数量: 111
  • 博客积分: 10
  • 博客等级: 民兵
  • 技术积分: 790
  • 用 户 组: 普通用户
  • 注册时间: 2010-12-29 13:41
  • 认证徽章:
文章分类

全部博文(111)

文章存档

2018年(10)

2017年(27)

2016年(18)

2015年(31)

2014年(25)

分类: Mysql/postgreSQL

2015-01-15 18:04:39

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

阅读(547) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册