Detailed introduction to the sample code for regular backup of Mysql database tables

This article mainly introduces the relevant information on the implementation of regular backup of Mysql database tables. Friends in need can refer to

The implementation of regular backup of Mysql database tables

0. Background

In the actual development environment, the front-end program needs to update/insert data into the database table specified by MySQL within a given time period . As the amount of data increases and the base number of the basic database table increases, there will be a lag of about 5 seconds every time it is updated.

Improvement plan one: Batch update, the cumulative number of 10 or 100 items will be updated and stored once;

Improvement plan Two: Back up the data 1 month before the current date, and delete the data 1 month ago in the current database table. It has been determined that this method improves access efficiency to a certain extent. Root cause: The basic table has a small cardinality, and query efficiency is relatively improved.

1. Summary of regular backup of database tables

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

Use mysqldump and set the period to 30 days.

Step 2: Delete files and compressed packages that have been backed up 60 days ago.

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

Step 4: Set the timing.

crontab settings.

[root@mysql_bak]# cat mysql_bak.sh
#!/bin/sh
#DATABASE INFO
DB_NAME="ppdb"
DB_USER="root"
DB_PASS="password"
DISPOSE_TABLE="dispose_ticles"
RST_TABLE="match_rst"
DB_IP=100.55.1.129

BIN_DIR="/usr/bin"
BAK_DIR="/home/mysql_bak/data"
DATE=`date +%Y%m%d_%H%M%S`

#mkdir -p $BAK_DIR
#备份包 形成压缩包
$BIN_DIR/mysqldump $DB_NAME $DISPOSE_TABLE > $BAK_DIR/$DISPOSE_TABLE.dump_$DATE.sql
$BIN_DIR/mysqldump $DB_NAME $DISPOSE_TABLE | gzip > $BAK_DIR/$DISPOSE_TABLE.dump_$DATE.sql.gz

$BIN_DIR/mysqldump $DB_NAME $RST_TABLE > $BAK_DIR/$RST_TABLE.dump_$DATE.sql
$BIN_DIR/mysqldump $DB_NAME $RST_TABLE | gzip > $BAK_DIR/$RST_TABLE.dump_$DATE.sql.gz

#定期删除60天的备份包
find $BAK_DIR -name "name_*.sql.gz" -type f -mtime +60 -exec rm {} \; > /dev/null 2>&1

#30天前的指定库表数据删除操作 (当前时间减去30天）
delete_date=`date --date='30 day ago' +%Y-%m-%d`
echo "delete_date=$delete_date"

#删除rst表信息
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

#删除dispose表信息
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. Schedule setting: Backup at 1 o'clock every 30 days.

[root@mysql_bak]# cat /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# 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 executed
0 1 */30 * * /home/mysql_bak/mysql_bak.sh > /dev/null 2>&1

[root@mysql_bak]# crontab -e
0 1 */30 * * /home/mysql_bak/mysql_bak.sh > /dev/null 2>&1

Restart crontab service

service crond restart

The above is the detailed content of Detailed introduction to the sample code for regular backup of Mysql database tables. For more information, please follow other related articles on the PHP Chinese website!