Linux下mysql使用动态语句备份和恢复

备份指定数据库下的所有表（使用sql脚本，不是在命令行下import)

为了防止文件同名，需要使用事件戳作为文件名的前缀，把时间戳存到数据库里是为了恢复数据时使用
存储位置选择了linux下默认位置/var/lib/mysql-files/,可以在mysql命令行下使用show variables like %secure%查看默认路径

– auto-generated definition
create table last_backup_file_timestamp
(
fname varchar(100) default ’ ’ not null
primary key
)
comment ‘上一次备份的动态文件名’;

备份

drop procedure if exists backup_all;
create procedure backup_all(in schema_name varchar(100))
begin
  declare tabname varchar(100);
  declare done tinyint;
  declare cs cursor for select table_name
                        from information_schema.tables
                        where table_schema = schema_name
                          and table_type = 'base table';
  # 溢出处理，垃圾mysql
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  # 插入时间戳
  insert into last_backup_file_timestamp value (date_format(now(), '%Y%m%d%H%I%S'));

  open cs;
  l:
    loop
      fetch cs into tabname;
      if done = 1 then
        leave l;
      end if;
      set @table_name = tabname;
      set @file =
          concat('/var/lib/mysql-files/', date_format(now(), '%Y%m%d%H%I%S'), '-', schema_name, '-', @table_name,
                 '-.backup');
      set @_sql = concat('select * from ', @table_name, ' into outfile ', '\'', @file, '\'');
      prepare back_up from @_sql;
      execute back_up;
    end loop;
  close cs;
end;

如何调用：

call backup_all(‘你的数据库名’);
备份后的效果：

恢复

drop procedure if exists restore_all;
create procedure restore_all(in schema_name varchar(100))
begin
  declare timestamp varchar(30);
  declare tabname varchar(100);
  declare done tinyint;
  declare cs cursor for select table_name
                        from information_schema.tables
                        where table_schema = schema_name
                          and table_type = 'base table';
  # 溢出处理，垃圾mysql,不如oracle简单
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  # 从last_backup_file_timestamp表中选择最新的一条注入timestamp
  select * from last_backup_file_timestamp l order by l.fname desc limit 1 into timestamp;
  open cs;
  o:
    loop
      fetch cs into tabname;
      if done = 1
      then
        leave o;
      end if;
      set @infile_name = concat('/var/lib/mysql-files/', timestamp, '-', schema_name, '-', tabname, '-.backup');
      set @_sql = concat('load data infile ', '\'', '@infile_name', '\'',
        'replace into table ', tabname,
                         ' fields terminated by \'\\t\'',
                         ' optionally enclosed by \'\\t\'');
      prepare stmt from @_sql;
      execute stmt;
    end loop;
  close cs;
end;

如何调用：call restore_all(‘emp’);
存在的问题：调用时
在stackoverflow中看到了有人和我一样的问题，prepare 协议不支持 load data ，只支持outfile into ,目前只能一个一个写了（注意outfile和infile文件名无法使用变量替代，无法使用concat函数，只能写死）；如下
load data infile ‘/var/lib/mysql-files/backup_net.log’
replace into table tacconet
fields terminated by ‘\t’
optionally enclosed by ‘’’
escaped by ‘’’
;

load data infile ‘/var/lib/mysql-files/backup.log’
replace into table ta_tcustomerinfo
fields terminated by ‘\t’
optionally enclosed by ‘’’
escaped by ‘’’
;

load data infile ‘/var/lib/mysql-files/backup_dictdir.log’
replace into table ta_dict_dir
fields terminated by ‘\t’
optionally enclosed by ‘’’
escaped by ‘’’
;

load data infile ‘/var/lib/mysql-files/dictdata.log’
replace into table ta_dict_data
fields terminated by ‘\t’
optionally enclosed by ‘’’
escaped by ‘’’
;

load data infile ‘/var/lib/mysql-files/backup_request.log’
replace into table taccorequest
fields terminated by ‘\t’
optionally enclosed by ‘’’
escaped by ‘’’
;

load data infile ‘/var/lib/mysql-files/backup_confirm.log’
replace into table taccoconfirm
fields terminated by ‘\t’
optionally enclosed by ‘’’
escaped by ‘’’
;