最近愈近年底,客户业务涉及对账操作,所以同事提出需求需要单独备份某张表数据,并且每日备份;思考之后解决办法如下:
- mysqldump 导出单张表的表结构及数据
- 新增数据库,专做临时每日数据备份库
- 导入该表数据到新增数据库内
- 重命名导入的表名(预防计划任务下次导入时数据覆盖)
- 编写脚本,添加 Linux 计划任务
1. mysqldump 导出但张表的表结构及数据
mysqldump -h dbServer -PdbServerPort -u db_user -p -d dbName tbName > tbName.sql
2. 新增数据库,专做临时每日数据备份库
# loginTo your DBserver
mysql> create database bakdbName character set utf8;
3. 导入该表数据到新增数据库内
mysql -h dbServer -P dbServerPort -u db_user -p bakdbName < tbName.sql
4 . 重命名导入的表名
mysql -u db_user -p -e "rename table bakdbName.tbName to bakdbName.tbName_$DATE"
5. 编写脚本
[root@testServer01 ~]# cat /usr/local/bin/bakTable.sh
#!/bin/bash
#Create by SAMZONG
DATE=`date +%Y%m%d`
TMPDIR=/tmp/baksql
# modify your DB configure
DBSERVER1=localhost
DBSERVER1_PORT=3306
DBSERVER1_USER=root
DBSERVER1_PASSWORD=password
MASTER_DBNAME=zabbix
MASTER_TBNAME=users
DBSERVER2=localhost
DBSERVER2_PORT=3306
DBSERVER2_USER=root
DBSERVER2_PASSWORD=password
BAKDBNAME=z3
# creat tmp folder
if [ ! -d $TMPDIR ]; then
mkdir $TMPDIR
fi
# dump tbName
mysqldump -h $DBSERVER1 -P $DBSERVER1_PORT -u $DBSERVER1_USER -p"$DBSERVER1_PASSWORD" -d $MASTER_DBNAME $MASTER_TBNAME > $TMPDIR/$MASTER_TBNAME.sql
# insert tbNAME to bakdbName
if [ $? -eq 0 ]; then
mysql -h $DBSERVER2 -P $DBSERVER2_PORT -u $DBSERVER2_USER -p"$DBSERVER2_PASSWORD" $BAKDBNAME < $TMPDIR/$MASTER_TBNAME.sql
if [ $? -eq 0 ]; then
# rename tbName
mysql -h $DBSERVER2 -P $DBSERVER2_PORT -u $DBSERVER2_USER -p"$DBSERVER2_PASSWORD" -e "rename table "$BAKDBNAME"."$MASTER_TBNAME" to "$BAKDBNAME"."$MASTER_TBNAME"_"$DATE";"
fi
fi
6. 添加到 Linux 计划任务
00 00 * * * /usr/local/bin/bakTable.sh