Mysql Database Backup and Dump

MysqlDatabaseBackupandDump

FixthereplicaerrorontheslaveforRDSonAWS

>CALLmysql.rds_skip_repl_error;

Forcleanonehugehistorytable.

Iamdoing

>deletefromtable_namewhereclick_time>=‘2015-12’;

3dayswehaveabout1millionrecordsinthistable.AndthishugequeryblocktheMySQLresourcesevenwerunthisqueryintheback.

>mysql-uroot-ppassword-h127.0.0.1-P3306databasename-e"optimizetableclicks_history_2016_12;">/tmp/optimize_table.log&

Backupthetablefileins3

>sudotarzcftablename.tar.gztablename.*

>awss3cp--content-encodinggziptablename.tar.gzs3://production/db_backups/db_2016_12_14/tablename.tar.gz

WelearnedMySQLstoreprocedurerecently,soweusethistodeletethedatadaybyday

dropproceduredelete_data;

delimiter#

createproceduredelete_data(INv_maxINT)

begin

declarev_counterintunsigneddefault0;

whilev_counter<v_maxdo

deletefromclick_history_2016_12whereclick_time>=DATE(NOW())-INTERVALv_counterDAY;

selectsleep(1);

setv_counter=v_counter+1;

endwhile;

end#

delimiter;

delete80daysdata.

>mysql-uroot-ppassword-h127.0.0.1-P3306databasename-e"calldelete_data(80);">/tmp/procedure_delete.log&

References:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_skip_repl_error.html