"Lazy shell script" eight--regular backup MySQL database table implementation

0. Background

In a real-world development environment, the front-end program needs to update/insert data into the MySQL-specified library table for a given time period. As the amount of data increases, the base library table cardinality increases, each update will have about 5s lag.
Improvement Scheme one : batch update, the cumulative number of 10 or 100 to make an update to the storage operation;
Improvement Scenario Two : Make a backup operation of data up to 1 months before the current date and delete data from the current library table 1 months ago. The method has been determined to improve the access efficiency to a certain extent. Root cause: The base table has fewer cardinality and the query efficiency is relatively improved.

1. Summary OF scheduled backups of the library table Step 1: Back up the library tables in the MySQL specified database.

Using mysqldump, set the cycle for 30 days.

Step 2: Delete Processing of the backed up files and the compressed package for the date 60 days ago. Step 3: Delete the data in the library table 30 days before the current date. (Step 1 has already done a backup). Step 4: Set the timing.

Crontab settings.

[[Email Protected]_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=100.55.1.129Bin_dir="/usr/bin"Bak_dir="/home/mysql_bak/data"Date= ' Date +%y%m%d_%h%m%s '#mkdir-P $BAK _dir#备份包 form 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/$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 + --exec rm {} \; >/dev/null2>&1#30天前的指定库表数据删除操作 (current time minus 30 days)Delete_date= ' Date--date=' 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. Timing: Back up at 1 points every 30 days.

[[Email protected]_bak]# cat/etc/crontabshell=/bin/bashpath=/sbin:/bin:/usr/sbin:/usr/binmailto=roothome=/# for details see Mans 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*/ -* */home/mysql_bak/mysql_bak.sh >/dev/NULL 2>&1[[Email protected]_bak]# crontab-e0 1*/ -* */home/mysql_bak/mysql_bak.sh >/dev/NULL 2>&1

Restart Crontab Service
service crond restart

20170304 21:10 in front of home bed

Ming Yi World
Reprint please indicate the source, the original address:
http://blog.csdn.net/laoyang360/article/details/60347828
If you feel this article is helpful, please click on the ' top ' support, your support is I insist on writing the most power, thank you!

"Lazy shell script" eight--regular backup MySQL database table implementation