Incessant

孟霆(2009-04-24 17:32:08):
字符转换成时间，where 条件中进行比较:如
select concat(year,'-',month,'-',date) times
where  times>subdate(now(),interval 30 day)
/注：year,month,date 为表中的字段，分别代表年，月，日
丁原(2009-04-24 17:41:39):
你这个要搞什么
孟霆(2009-04-24 17:42:43):
数据库中 有number 的 year ,number的month,number的date,三个字段,我想查3天前的怎么搞

      设计上一定要有眼界，数据库中保留year,month,date年月日，不是为了用来查询，而是为了用来展示的，在数据库中增加字段gmt_create时间字段，专门用来查询，这样查询岂不是变的很容易了。

     sync_binlog=1 or N

     This makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction 

     默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表，MySQL服务器处理COMMIT语句，它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃，重启时，事务被InnoDB回滚，但仍然存在binlog中。可以用--innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事务支持，该选项作废了），该选项可以提供更大程度的安全，使每个事务的binlog(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步，该选项的效果是崩溃后重启时，在滚回事务后，MySQL服务器从binlog剪切回滚的InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步(不接收 回滚的语句)。

     开发库和线上表结构有很多不一样，分表个数也不同，我需要重新整理保持开发库和线上一致，之前已经告知开发数据是否允许丢失，开发说数据可以不要了，直接丢掉，等我搞好之后开发告诉我部分数据（ring）是不能删除的，我晕，只好想办法来恢复数据了，没有备份，不过还好有全部的binlog。
1.根据binlog解析出所有ring数据库的所有sql
[mysql@DEV_SNS1 log]$mysqlbinlog --no-defaults --database=ring --start-datetime="2005-04-20 9:55:00" --stop-datetim="2009-04-08 08:05:00" /u01/mysql/log/mysql-bin.000005 > /u01/mysql/log/mysql_restore5.sql

[mysql@DEV_SNS1 log]$ ls -l /u01/mysql/log/mysql_restore5.sql
-rw-r--r--    1 mysql    dba           407 Apr  8 15:33 /u01/mysql/log/mysql_restore5.sql
--start-datetime=datetime 从哪个点开始解析
--stop-datetim=datetime   从哪个点停止解析
--database=ring               指定需要解析哪个数据库，只提取某个数据库的sql语句

--如果有多个binlog的话需要解析多次，需要解析所有的binlog
2.重新在数据库中执行
[mysql@DEV_SNS1 log]$mysql -u root <mysql_restore5.sql
ERROR 1062 (23000) at line 2559580: Duplicate entry '175754263-140' for key 1 --恢复过程中出现主键冲突，导入失败

3.删除已经恢复的数据，重新恢复一遍

[mysql@DEV_SNS1 log]$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7647
Server version: 5.0.67-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@(none) 02:55:41>drop database ring;
Query OK, 8 rows affected (0.04 sec)

[mysql@DEV_SNS1 log]$ mysql -u root -f <mysql_restore5.sql    ---f：忽略错误并继续                    
ERROR 1062 (23000) at line 2559580: Duplicate entry '175754263-140' for key 1
ERROR 1062 (23000) at line 2564671: Duplicate entry '138' for key 1
ERROR 1062 (23000) at line 2566216: Duplicate entry '139' for key 1
ERROR 1062 (23000) at line 2566224: Duplicate entry '140' for key 1
ERROR 1062 (23000) at line 2566232: Duplicate entry '141' for key 1
ERROR 1062 (23000) at line 2566240: Duplicate entry '142' for key 1
ERROR 1062 (23000) at line 2648410: Duplicate entry '143' for key 1
ERROR 1062 (23000) at line 2648418: Duplicate entry '144' for key 1
ERROR 1062 (23000) at line 2648581: Duplicate entry '145' for key 1
ERROR 1062 (23000) at line 2648589: Duplicate entry '146' for key 1
ERROR 1062 (23000) at line 2648597: Duplicate entry '147' for key 1
ERROR 1062 (23000) at line 2648605: Duplicate entry '148' for key 1
ERROR 1062 (23000) at line 2649279: Duplicate entry '149' for key 1
ERROR 1062 (23000) at line 2649287: Duplicate entry '150' for key 1
ERROR 1062 (23000) at line 2649295: Duplicate entry '151' for key 1
ERROR 1062 (23000) at line 2649303: Duplicate entry '152' for key 1
ERROR 1062 (23000) at line 2649311: Duplicate entry '153' for key 1
ERROR 1062 (23000) at line 2649319: Duplicate entry '154' for key 1
ERROR 1062 (23000) at line 2649327: Duplicate entry '155' for key 1
ERROR 1062 (23000) at line 2649335: Duplicate entry '156' for key 1

 

恢复成功，有部分数据冲突需要开发自己去处理了。

总的来说，不管是否重要，因为重要性很多时候我们自己是无法评定的，大操作之前一定先做好备份，备份才是王道。

    从来没有仔细写个Mysql的存储过程，正好花时间研究了一下。

    写任何程序最基本的是先要有详细注释，有log记录，有异常异常等等，当然Mysql的异常处理机制很差，搞了半天都没有满意的结果。功能很简单，自动修改merge表的定义，并清理过期表的数据。

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_rename_mergetable $$
create procedure sp_rename_mergetable(in p_tablename varchar(60),in p_begincount int,in p_endcount int)
BEGIN
/*
 Created by dingyuan
 Dated:2009-03-27
 用途：用来更改merge表的定义，并清理过期表的数据。
 p_tablename指的是你要merge的基础表,如sns_whovisitme。
 p_begincount指的是你需要merge的表区间，比如sns_whovisitme分了64个表,库1分表从0，到31，那p_begincount设置为0，p_endcount设置为31.
 本程序只支持子表为3个，后缀为_01,_02,_03的情况，每次只会merge两张表。
 create table tmp_debug(spname varchar(50),msg varchar(1000),vcount int); --错误记录表
 create table tmp_time(vsql varchar(200),gmt_create datetime,vcount int);--log表
 记录哪些表需要重新merge，这次需要merge哪个子表（thismerge）
 如thismerge=1表示需要清理_01开头的表数据，并merge _02，_03开头的子表
 create table tmp_merge_status(tablename varchar(100),thismerge int,status int,gmt_modified datetime);
 insert into tmp_merge_status values('activity_message',1,1,now());
 调用
 call sp_rename_mergetable('activity_message',0,7);
 用于debug
 insert into tmp_time(gmt_create,vcount) values(now(),1);
 */
  declare v_tablename varchar(60);
  declare v_begincount int;
  declare v_endcount int;
  declare i int ;
  declare v_trunmonth varchar(5);
  declare v_addmonth1 varchar(5);
  declare v_addmonth2 varchar(5);
  declare v_thismerge int;
  declare v_count int default 0;
  declare v_error int DEFAULT 0;
  declare continue handler for not found,sqlexception set v_error=1;
   -- 需要重新merge的表名
  set v_tablename=p_tablename;
   -- 指定你需要merge的表区间
  set v_begincount =p_begincount; set v_endcount =p_endcount;

   -- 判断基础表是否有数据
  select count(*) into v_count from tmp_merge_status where tablename=v_tablename and status=1;

  if v_count=1 then
      -- 取这次需要清除的子表，并merge其他的两张表
     select thismerge into v_thismerge from tmp_merge_status where tablename=v_tablename and status=1;

      -- v_trunmonth表示需要删除的表，v_addmonth表示需要重新merge的子表
     if v_thismerge=1 THEN
        SET v_trunmonth='_01'; SET v_addmonth1='_02'; SET v_addmonth2='_03';
     elseif v_thismerge=2 THEN
        SET v_trunmonth='_02'; SET v_addmonth1='_03'; SET v_addmonth2='_01';
     elseif v_thismerge=3 THEN
        SET v_trunmonth='_03'; SET v_addmonth1='_01'; SET v_addmonth2='_02';
     end if;
    
      -- status置为-1表示正在处理merge中，防止并发，thismerge更新为下次需要清理的子表。
     update tmp_merge_status set status=-1,gmt_modified=now() where tablename=v_tablename and status=1;
   
      -- 开始merge，从begincount表开始merge，一直到endcount为止
     set i=v_begincount;   
      -- 记录log表
     insert into tmp_time(vsql,gmt_create,vcount) values(concat('------------',v_tablename,' begin merge---'),now(),i);  
     error_label:
        WHILE i <= v_endcount DO  
              -- 如sns_whovisitme_0000
      set @vtable=concat(v_tablename,'_',lpad(i,4,'0')) ;
              -- 如alter table sns_whovisitme_0000 union(sns_whovisitme_0000_02,sns_whovisitme_0000_03)
      set @mergesql =concat('alter table ',@vtable,' union(',@vtable,v_addmonth1,',',@vtable,v_addmonth2,')');  
       -- 如truncate table sns_whovisitme_0001_01
             set @trunsql =concat('truncate table ',@vtable,v_trunmonth);
       -- insert into tmp_time(vsql,gmt_create,vcount) values(@vtable,now(),i);
             insert into tmp_time(vsql,gmt_create,vcount) values(@mergesql,now(),i);
             insert into tmp_time(vsql,gmt_create,vcount) values(@trunsql,now(),i);
       -- 执行merge脚本
             prepare stmt1 from @mergesql;
      execute stmt1;
      DEALLOCATE PREPARE stmt1;
              -- 执行truncate 脚本
      prepare stmt2 from @trunsql;
      execute stmt2;
      DEALLOCATE PREPARE stmt2;
            
      set @mergesql=''; 
      set @trunsql=''; 
             set i=i+1;
              -- 异常处理
             if v_error=1 then
                insert into tmp_debug(spname,msg,vcount,vdate)
                values('sp_rename_table','ERROR:not found,sqlexception',i,now());
                leave error_label;
             end if;           
        END WHILE;
     
      -- 记录log表
     delete from tmp_time where gmt_create<date_sub(now(),interval 30 day);
     insert into tmp_time(vsql,gmt_create,vcount) values(concat('------------',v_tablename,' end merge---'),now(),i);  
      -- 更新状态，表示merge成功
     update tmp_merge_status set status=1,thismerge=mod(thismerge,3)+1,gmt_modified=now() where tablename=v_tablename and status=-1;
else
     insert into tmp_debug(spname,msg,vdate)
     values(v_tablename,'ERROR:tmp_merge_status no metadata,please check',now());
end if;
END$$

DELIMITER ;