Perl Version, 通过slave mysql 做备份 #!/usr/bin/perl -w #This program charge to backup mysql data #It would stop the slave first before backup the data. #Since we have no hot backup tools to backup data #This method is somewhat twisted and confusing #To guarantee the data consistent,the slave must replicated to the master serverstablely,so we choose paytv04 for our purpose. #Author GZ-IT Data 2004/09/02 Version 1.0$ctrl_mysql_file="/home/paytvadmin/script/maintain_mysql/ctrl_mysql_file.sql"; $backup_file_cfg="/home/paytvadmin/script/maintain_mysql/exclude_mysql.cfg"; ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime(time); print "Stop the slave,pls wait...\n"; ctrl_mysql("slave stop;");print "Backuping the data,it takes 5~10 minutes\n"; $backup_file="/home/dbpack/paytv04_wday$wday.tar.gz"; $cmd="tar cvf - --exclude-from $backup_file_cfg /home/mysql/data | gzip > $backup_file"; print "Backup to $backup_file\n"; #print "Backup cmd $cmd\n"; system($cmd);print "Start the slave,backup completed\n"; ctrl_mysql("slave start;");sub ctrl_mysql{ open(f_handle,">",$ctrl_mysql_file); print f_handle $_[0]; close(f_handle); system("mysql -uroot -pxxx < $ctrl_mysql_file"); sleep 10; }
Shell Version,低能版 #This command use for backup mysql data including table structure and binary dat a. DATAPATH=/home/mysql/data/ if [ -d "/tmp/paytvadmin" ];then echo "Folder prepared" else mkdir /tmp/paytvadmin fi TMPFILE=/tmp/paytvadmin/paytvDBGetFile.log #Find the binary data files. find ${DATAPATH} -name "ib_*" > ${TMPFILE} find ${DATAPATH} -name "ibd*" >> ${TMPFILE}BACKUP_DATE="paytv" BACKUP_DIR="/home/itadgz/mysqlbackup/" #cat ${TMPFILE} while read line do CPCMD="cp ${line} ${BACKUP_DIR}" echo "${CPCMD}" ${CPCMD} done < ${TMPFILE}#Copy the table structure TABLE_STR_DIR=/home/mysql/data/paytv CPCMD="cp -R ${TABLE_STR_DIR} ${BACKUP_DIR}" `${CPCMD}` TABLE_STR_DIR=/home/mysql/data/qpi CPCMD="cp -R ${TABLE_STR_DIR} ${BACKUP_DIR}" `${CPCMD}` TABLE_STR_DIR=/home/mysql/data/hkjc CPCMD="cp -R ${TABLE_STR_DIR} ${BACKUP_DIR}" `${CPCMD}` TABLE_STR_DIR=/home/mysql/data/mysql CPCMD="cp -R ${TABLE_STR_DIR} ${BACKUP_DIR}" `${CPCMD}`#We doesn't rcp to paytv01,but we keep the weekly backup pack_date_name=`date +%A` #Tar the file TARCMD="tar -cvf /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.db.tar ${B ACKUP_DIR}" echo "${TARCMD}" ${TARCMD} #Zip the file ZIPCMD="gzip -f /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.db.tar" `${ZIPCMD}`#Since copy the binary package is not reliable,we dump it as the plain text again DUMPCMD=" mysqldump paytv -uroot -pcmV99Tco > /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.paytv.sql" $DUMPCMD ZIPCMD="gzip -f /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.paytv.sql" $ZIPCMD DUMPCMD=" mysqldump qpi -uroot -pcmV99Tco > /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.qpi.sql" $DUMPCMD ZIPCMD="gzip -f /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.qpi.sql" $ZIPCMD DUMPCMD=" mysqldump hkjc -uroot -pxxx> /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.hkjc.sql" $DUMPCMD ZIPCMD="gzip -f /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.hkjc.sql" $ZIPCMDecho "Delete the log and template files" `rm ${TMPFILE}` `rm -fr ${BACKUP_DIR}/paytv` `rm -fr ${BACKUP_DIR}/qpi` `rm -fr ${BACKUP_DIR}/hkjc` `rm -fr ${BACKUP_DIR}/mysql` `rm ${BACKUP_DIR}/*` chown -R itadgz:itadgz /home/itadgz
/usr/local/mysql/bin/mysqldump --add-drop-table my_database -uroot -psg8j2gfs -h61.186.254.xxx >/home/doodoo/cron/mydatabase_`date +%Y-%m-%d`.sql &
并把这个写入crontab。
想不起这里的5000是秒还是毫秒了,你自己试一下吧。
#!/usr/bin/perl -w
#This program charge to backup mysql data
#It would stop the slave first before backup the data.
#Since we have no hot backup tools to backup data
#This method is somewhat twisted and confusing
#To guarantee the data consistent,the slave must replicated to the master serverstablely,so we choose paytv04 for our purpose.
#Author GZ-IT Data 2004/09/02 Version 1.0$ctrl_mysql_file="/home/paytvadmin/script/maintain_mysql/ctrl_mysql_file.sql";
$backup_file_cfg="/home/paytvadmin/script/maintain_mysql/exclude_mysql.cfg";
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime(time);
print "Stop the slave,pls wait...\n";
ctrl_mysql("slave stop;");print "Backuping the data,it takes 5~10 minutes\n";
$backup_file="/home/dbpack/paytv04_wday$wday.tar.gz";
$cmd="tar cvf - --exclude-from $backup_file_cfg /home/mysql/data | gzip > $backup_file";
print "Backup to $backup_file\n";
#print "Backup cmd $cmd\n";
system($cmd);print "Start the slave,backup completed\n";
ctrl_mysql("slave start;");sub ctrl_mysql{
open(f_handle,">",$ctrl_mysql_file);
print f_handle $_[0];
close(f_handle);
system("mysql -uroot -pxxx < $ctrl_mysql_file");
sleep 10;
}
#This command use for backup mysql data including table structure and binary dat
a.
DATAPATH=/home/mysql/data/
if [ -d "/tmp/paytvadmin" ];then
echo "Folder prepared"
else
mkdir /tmp/paytvadmin
fi
TMPFILE=/tmp/paytvadmin/paytvDBGetFile.log
#Find the binary data files.
find ${DATAPATH} -name "ib_*" > ${TMPFILE}
find ${DATAPATH} -name "ibd*" >> ${TMPFILE}BACKUP_DATE="paytv"
BACKUP_DIR="/home/itadgz/mysqlbackup/"
#cat ${TMPFILE}
while read line
do
CPCMD="cp ${line} ${BACKUP_DIR}"
echo "${CPCMD}"
${CPCMD}
done < ${TMPFILE}#Copy the table structure
TABLE_STR_DIR=/home/mysql/data/paytv
CPCMD="cp -R ${TABLE_STR_DIR} ${BACKUP_DIR}"
`${CPCMD}`
TABLE_STR_DIR=/home/mysql/data/qpi
CPCMD="cp -R ${TABLE_STR_DIR} ${BACKUP_DIR}"
`${CPCMD}`
TABLE_STR_DIR=/home/mysql/data/hkjc
CPCMD="cp -R ${TABLE_STR_DIR} ${BACKUP_DIR}"
`${CPCMD}`
TABLE_STR_DIR=/home/mysql/data/mysql
CPCMD="cp -R ${TABLE_STR_DIR} ${BACKUP_DIR}"
`${CPCMD}`#We doesn't rcp to paytv01,but we keep the weekly backup
pack_date_name=`date +%A`
#Tar the file
TARCMD="tar -cvf /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.db.tar ${B
ACKUP_DIR}"
echo "${TARCMD}"
${TARCMD}
#Zip the file
ZIPCMD="gzip -f /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.db.tar"
`${ZIPCMD}`#Since copy the binary package is not reliable,we dump it as the plain text again
DUMPCMD=" mysqldump paytv -uroot -pcmV99Tco > /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.paytv.sql"
$DUMPCMD
ZIPCMD="gzip -f /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.paytv.sql"
$ZIPCMD
DUMPCMD=" mysqldump qpi -uroot -pcmV99Tco > /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.qpi.sql"
$DUMPCMD
ZIPCMD="gzip -f /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.qpi.sql"
$ZIPCMD
DUMPCMD=" mysqldump hkjc -uroot -pxxx> /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.hkjc.sql"
$DUMPCMD
ZIPCMD="gzip -f /home/itadgz/dbpack/${BACKUP_DATE}.${pack_date_name}.hkjc.sql"
$ZIPCMDecho "Delete the log and template files"
`rm ${TMPFILE}`
`rm -fr ${BACKUP_DIR}/paytv`
`rm -fr ${BACKUP_DIR}/qpi`
`rm -fr ${BACKUP_DIR}/hkjc`
`rm -fr ${BACKUP_DIR}/mysql`
`rm ${BACKUP_DIR}/*`
chown -R itadgz:itadgz /home/itadgz