Monthly backup table based on MySQL events, scrolling, 6 backups

Requirements:

1. • 0 per month 1st: Backs up the data for an entire month without impacting the business, reserving 6 backups.

Ideas:

1. 1. Based on MySQL event function, complete the operation on time every month
   2. The rename statement is atomic, and the old and new tables seamlessly switch
   3. Rename statement only modifies table definition, large table instantaneous completion
   4. Based on the above three points, the table data is cut by the month

Steps:

1. Create a new empty table to replace
2. Rename the original table as a date backup table, empty table as the original table
3. Delete the table that was backed up 6 times ago

Refer to the official article

Https://dev.mysql.com/doc/refman/5.6/en/rename-table.html

Prerequisites: Turn on the MySQL event feature

turn on event features Set =  on  like'event_scheduler';

MySQL Event implementation

DELIMITER;;CREATEEVENTBACKUP onSCHEDULE every1 MONTHStarts Date_add (Date_add (Date_sub (Curdate (), INTERVAL Day(Curdate ())-1  Day), INTERVAL1 MONTH), INTERVAL1HOUR) oncompletion PRESERVE ENABLE doBEGIN    SelectNow ();END;;D Elimiter;

return Date curdate () returns the number of the day before Day(Curdate ()-1Date Subtraction Function: Calculates the day minus the beginning of the month to yesterday, equal to the number of Date_sub (Curdate (), INTERVAL Day(Curdate ())-1  Day) Date addition function: Gets the date of the next month number 1th Date_adddate_add (Date_sub (Curdate (), INTERVAL Day(Curdate ())-1  DayDate addition function: Gets the time of 1 o'clock in the morning next month 1th Date_add (Date_add (Date_sub (Curdate (), INTERVAL Day(Curdate ())-1  Day), INTERVAL1 MONTH), INTERVAL1HOUR)

Implementation Details Creating a Table implementation

CREATE TABLE' version_replace ' (' table_name ' )varchar( +) not NULL, ' table_version 'int(Ten) unsigned not NULL DEFAULT '0',  UNIQUE KEY' table_name_idx ' (' table_name ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

Dynamic SQL statement Implementation change table name to date variable

SET @command =concat ('RENAME TABLE ' version ' to ' Version_backup_', Date_format (now (),'  %y%m'),', ' version_replace ' to ' version'); PREPARE  from @command ; EXECUTE Modify;

Dynamic SQL statement Implementation deletes 6 previous backups

 set   @command  =  concat ( "  DROP TABLE version_backup_   ", Date_format (Date_sub (now (), Interval 6  month ",  " Span style= "COLOR: #ff0000" >%y%m   "),   prepare  modify from   @command   execute  modify; 

get formatted year and month Select date_format (now (),'%y%m'); Gets the first 6 months of the current date Select6  month); Get the first 6 months of the current date, and format the display, prepare for the name of the splicing table select6month),  '%y%m');

Implementation Details

Final implementation

DELIMITER;;CREATEEVENTBACKUP onSCHEDULE every1 MONTHStarts Date_add (Date_add (Date_sub (Curdate (), INTERVAL Day(Curdate ())-1  Day), INTERVAL1 MONTH), INTERVAL0HOUR) oncompletion PRESERVE ENABLE doBEGIN    /*Create an empty table to be replaced*/    CREATE TABLE' version_replace ' (' table_name ' )varchar( +) not NULL, ' table_version 'int(Ten) unsigned not NULL DEFAULT '0',    UNIQUE KEY' table_name_idx ' (' table_name ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8; The/*rename statement is atomic,Rename the original table to a date backup table, and rename the new table you created to the original table*/    SET @command=Concat'RENAME TABLE ' version ' to ' Version_backup_', Date_format (now (),'%y%m'),'' , ' version_replace ' to ' version ''); PREPAREModify from @command; EXECUTEModify; /*Delete that backup 6 months ago*/    SET @command=Concat'DROP TABLE Version_backup_', Date_format (Date_sub (now (), interval6 Month),'%y%m'),"'); PREPAREModify from @command; EXECUTEModify;END;;D Elimiter;

Did you write a good article? Please sweep the following author's begging code, sponsorship.

Monthly backup table based on MySQL events, scrolling, 6 backups