Linux下MySQL语句废除_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 ‘’’

;

标签：name,备份,Linux,infile,mysql,table,backup,into

来源： https://blog.csdn.net/qq_38520400/article/details/100063682