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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Linux下mysql使用动态语句备份和恢复，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3521字，纯文字阅读大概需要6分钟。

内容图文

备份指定数据库下的所有表（使用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 ‘’’
;

内容总结

以上是互联网集市为您收集整理的Linux下mysql使用动态语句备份和恢复全部内容，希望文章能够帮你解决Linux下mysql使用动态语句备份和恢复所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---