mysql数据表每天全量备份

1.思路：创建存储过程，创建备份表 xxx_%Y%m%d,将数据插入备份表，每天定时调度即可

2.实现

2.1存储过程

CREATE PROCEDURE `auto_copy_table`(
		IN prefix VARCHAR(255),
		IN srcTableName VARCHAR(255)
) 
	COMMENT '根据日期自动建表,并备份数据' 
BEGIN
	SET @suffix = concat('_', date_format(date_sub(curdate(), INTERVAL 1 DAY), '%Y%m%d'));
	SET @sql_create_table_gpstrail = concat('create table if not exists ', prefix, @suffix, 
                                            ' like ', srcTableName, ';'
    );
	SET @sql_insert_data_gpstrail = concat(
                                            ' insert into ' , prefix, @suffix,  
                                            ' select * from ', srcTableName, ' ;'
    );
PREPARE sql_create_table_gpstrail FROM @sql_create_table_gpstrail;
EXECUTE sql_create_table_gpstrail;
PREPARE sql_insert_data_gpstrail FROM @sql_insert_data_gpstrail;
EXECUTE sql_insert_data_gpstrail;
	
END 

　2.2 定时调度

begin

CALL auto_copy_table('XXX', 'XXX');

end

 

定时部门可以参考：https://www.cnblogs.com/jycjy/p/11585335.html