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.178linux.com/18008
