Implementation of regular Mysql database table backup

Implementation of regular Mysql database table backup

Implementation of regular Mysql database table backup

0. Background

In the actual development environment, the front-end program needs to update/insert data to the database table specified by MySQL within a specified period of time. As the data volume increases and the base database and Table Base increases, every update may experience a lag of around 5 seconds.

Improvement Solution 1:Batch update: refresh 10 or 100 entries at a time;

Solution 2:Back up the data from the previous month before the current date and delete the data from the current database table one month ago. After measurement, this method improves the access efficiency to a certain extent. Root cause: the basic table base is small, and the query efficiency is relatively improved.

1. Summary of regular backup of database tables

Step 1: Back up the database tables specified in the Mysql database.

Use mysqldump to set the cycle to 30 days.

Step 2: delete the backed up files and compressed packages 60 days ago.

Step 3: delete the data in the database table 30 days before the current date. (Step 1 has been backed up ).

Step 4: Set the timing.

Crontab settings.

[Root @ mysql_bak] # cat mysql_bak.sh #! /Bin/sh # DATABASE INFODB_NAME = "ppdb" DB_USER = "root" DB_PASS = "password" DISPOSE_TABLE = "dispose_ticles" RST_TABLE = "match_rst" DB_IP = export _ DIR = "/usr /bin "BAK_DIR ="/home/mysql_bak/data "DATE = 'date + % Y % m % d _ % H % M % s' # mkdir-p $ BAK_DIR # backup create a compressed package $ BIN_DIR/mysqldump $ DB_NAME $ DISPOSE_TABLE> $ BAK_DIR/$ DISPOSE_TABLE.dump _ $ DATE. SQL $ BIN_DIR/mysqldump $ DB_NAME $ DISPOSE_TABLE | gzip> $ BAK_DIR/empty $ BIN_DIR/mysqldump $ DB_NAME $ RST_TABLE> $ BAK_DIR/$ RST_TABLE.dump _ $ DATE. SQL $ BIN_DIR/mysqldump $ DB_NAME $ RST_TABLE | gzip> $ BAK_DIR/snapshot # regularly Delete the 60-day backup package find $ BAK_DIR-name "name _ *. SQL .gz"-type f-mtime + 60-exec rm {}\;> /dev/null 2> & 1 # delete data in the specified database and Table 30 days ago (current time minus 30 days) delete_date = 'date -- date = '30 day ago '+ % Y-% m-% d 'echo "delete_date = $ delete_date" # delete rst table information rst_ SQL = "delete from $ RST_TABLE where update_time <= $ delete_date order by update_time; "; echo "rst_ SQL = $ rst_ SQL" # ret = $ (mysql-u $ DB_USER-h $ {DB_IP}-p $ {DB_PASS} $ DB_NAME-e "$ SQL "); ret = $ (mysql-h $ {DB_IP} $ DB_NAME-e "$ rst_ SQL "); echo $ ret # delete dispose table information dispose_ SQL = "delete from $ DISPOSE_TABLE where judge_time <= $ delete_date order by judge_time ;"; echo "dispose_ SQL = $ dispose_ SQL" ret = $ (mysql-h $ {DB_IP} $ DB_NAME-e "$ dispose_ SQL"); echo $ ret

2. timed setting: Back up data at every 30 days.

[root@mysql_bak]# cat /etc/crontabSHELL=/bin/bashPATH=/sbin:/bin:/usr/sbin:/usr/binMAILTO=rootHOME=/# For details see man 4 crontabs# Example of job definition:# .---------------- minute (0 - 59)# | .------------- hour (0 - 23)# | | .---------- day of month (1 - 31)# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat# | | | | |# * * * * * user-name command to be executed0 1 */30 * * /home/mysql_bak/mysql_bak.sh > /dev/null 2>&1[root@mysql_bak]# crontab -e0 1 */30 * * /home/mysql_bak/mysql_bak.sh > /dev/null 2>&1

Restart the crontab Service

service crond restart

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!