每天按ID区间备份表数据
#!/bin/bash
date -d yesterday +”DATE %Y-%m-%d %H:%M:%S” >> /Mysql_Data/t_collection/ID.log
/usr/bin/mysql -uroot –password=”admin” -D userbehavior -e “select max(ID) from t_collection;”|grep -v ‘max(ID)’ >> /Mysql_Data/t_collection/ID.log
today=`tail -n 1 /Mysql_Data/t_collection/ID.log`
yesterday=`tail -n 3 /Mysql_Data/t_collection/ID.log|grep -v ‘DATE’|grep -v $today`
sql=”use userbehavior;/n
select * from t_collection where ID>$yesterday and ID<=$today into outfile ‘/Mysql_Data/t_collection/`date -d yesterday +”%Y-%m-%d”`.csv’/n
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘/”‘ ESCAPED BY ” LINES TERMINATED BY ‘/n'”
/usr/bin/mysql -uroot –password=”admin” -D userbehavior -e “$sql”;
cd /Mysql_Data/t_collection
tar czvf `date -d yesterday +”%Y-%m-%d”`.tgz `date -d yesterday +”%Y-%m-%d”`.csv
rm -f /Mysql_Data/t_collection/`date -d yesterday +”%Y-%m-%d”`.csv
备份某个具体区间的表数据
mysql -p’password’ -D userbehavior -e “select * from t_collection where ID>3899999999 and ID<4000000000 into outfile ‘/mnt/backup/t_c/40WW.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘/”‘ ESCAPED BY ” LINES TERMINATED BY ‘/n’;”
程序修改为分表后的表数据备份脚本如下:
#!/bin/bash
table=t_collection`date -d yesterday +”%Y%m%d”`
sql=”use userbehavior;/n
select ID,REPLACE(REPLACE(Path,’/n’,’@’),’/r’,’@’),REPLACE(REPLACE(Content,’/n’,’@’),’/r’,’@’) ,CreateTime from $table into outfile ‘/data/backup/Mysql_Data/new_collection/`date -d yesterday +”%Y-%m-%d”`.csv’/n
FIELDS TERMINATED BY ‘|’ LINES TERMINATED BY ‘/n'”
/usr/bin/mysql -uroot –password=”password” -D userbehavior -e “$sql”;
cd /data/backup/Mysql_Data/new_collection/
tar czvf `date -d yesterday +”%Y-%m-%d”`.tgz `date -d yesterday +”%Y-%m-%d”`.csv
rm -f `date -d yesterday +”%Y-%m-%d”`.csv
echo “$(date +%Y%m%d_%H:%M:%S) $table is bak finish”>>/data/backup/Mysql_Data/new_collection/t_collection_bak.log
cp `date -d yesterday +”%Y-%m-%d”`.tgz /mnt/GZ_GuangGao_29/Collection/
加入crontab
35 0 * * * root sh /data/backup/scriptshell/t_collectionnew.sh
#!/bin/bash
sql=”use userbehavior;/n
select a.* from coocaaDaoHang a/n
where a.createtime like ‘$1%’/n
into outfile ‘/data/backup/Mysql_Data/DaoHang/$1.csv’/n
CHARACTER SET gbk /n
fields terminated by ‘,’ /n
optionally enclosed by ‘/”‘ escaped by ‘///’ /n
lines terminated by ‘/r/n’;”
/usr/bin/mysql -uroot –password=”password” -D userbehavior -e “${sql}”;
加入crontab
01 0 * * * root /data/backup/scriptshell/coocaaDaoHang.sh `date -d yesterday +”/%Y-/%m-/%d”`
原创文章,作者:carmelaweatherly,如若转载,请注明出处:https://blog.ytso.com/192726.html