Mysql備份II
V.II.I單臺或共用機器,數據量和訪問量小50G<
1 Mysqldump(全導出,導庫,導表) 鎖表 如果這時有些入會鎖住或者超時
2 至少停止寫入 防止innodb配置還沒刷到磁盤里 先flash tables
/usr/local/mysql/bin/mysqladmin -S /tmp/mysql.sock -u root -p password shutdown Service msqld stop && 清除binlog resetmaster tar or rsync && service mysqld start
for MyISAM
mysqldump --user=root –all-databases –flush-privileges –lock-all-tables --master-data=1 –flush-log –trigger –routines –events –hex-blob > $backup_dir/full_dump_$date.sql
for InnoDB
mysqldump –user=root –all-databases –flush-privileges -- single-transaction --master-data=1 --flush -logs -–triggers – -routines --events –hex-blob> $backup_dir/$full_dump_$date.sql --hex-blob 使用十六進制格式導出二進制字符串字段.有二進制類型數據就必須使用 .binary .varybinary .blob --single-transaction 先提交begin sql,能保證導出數據庫的一致性狀態 ,只適用于 InnoDB 和BDB TABLES 會使任何掛起的失誤隱含提交 導出 大表應結合使用—quick選項 Masterdata=1 mysqldump 會包括change master to file & position Masterdata=2 mysqldump 會包括change master to file & position 但是是被注釋掉的 這個值主要用于主從復制時
Tar:
ScriptS:
#!/bin/bash
#bakup mysql dbTables; usefree; not responsible;
#
#author lethe 6/15/2016
#QQ:914576241
#www.lethe.com.cn
#
#echo -n "inputdbname："
#read dbname
#echo "$dbname"
echo -n "inputdbuser："
read user
echo “$user”
echo -n "inputpassword："
read password
echo -n "inputportNO.："
read port
echo “$port”
echo -n "inputdeldata："
read passday
echo “$passday”
user=“$user”
password=“$password”
#dbname=“$dbname”
cm= “mysql -S /tmp/mysql.sock -u “$user” -p “$password””
dm=” /usr/local/mysql/bin/mysqldump -S /tpm/mysql.sock -u $user -p $password”
em=” /usr/local/mysql/bin/mysqladmin -S /tmp/mysql.sock -u $user -p $password shutdown”
date=`date +%Y_%m_%d`
before=` date +%Y-%m-%d 00:00:00 `
ddate=` date +"%Y_%m_%d" -d "-${passday} day" `
rm -rf *_$ddate*
if [ ! -d /mysql/backup ];then
mkdir -p /mysql/backup
fi
cd /mysql/backup
$cm -e “PUGE MASTER LOGS BEFORE ‘$before’”
#binlog too large
task=`ps waux | grep mysql | grep -v grep| awk’{print $2}’| wc -l`
if [ $task -gt 0 ];then
$em
Service mysqld stop
fi
#make sure mysql closed
$em
tar zcf /mysql/backup/mysql_$port_$date.tar.gz /var/lib/mysql/
/usr/local/mysql/bin/mysql_safe –defaults-file=/var/lib/mysql/my.cnf &
Service mysqld start &> /dev/null
#make sure mysql start
原創文章，作者：雙慶 李，如若轉載，請注明出處：http://www.www58058.com/18008
