mysqldump的全备及分库分表备份记录–数据库

发表评论
1,107 views

A+

所属分类：数据库

mysqldump全备带记录点

if [ ! -d $HOME/backup ];then mkdir -p $HOME/backup
cd $HOME/backup
fi
mysqldump -uroot -p'onlinenongyi&*()' -S /var/lib/mysql/mysql.sock -A --events -B -x --master-data=2|gzip >/server/backup/full/$(date +%F).sql.gz

if [ ! -d $HOME/backup ];then mkdir -p $HOME/backup

cd $HOME/backup

mysqldump -uroot -p'onlinenongyi&*()' -S /var/lib/mysql/mysql.sock -A --events -B -x --master-data=2|gzip >/server/backup/full/$(date +%F).sql.gz

分库

#!/bin/bash
USER=root
PASSWD=''
PROT=3306
SOCKET=/var/lib/mysql/mysql.sock
LOGIN="mysql -u$USER -p$PASSWD -S $SOCKET"
DUMP="mysqldump -u$USER -p$PASSWD -S $SOCKET"
SHOWDATABASES=$($LOGIN -e "show databases;"|egrep -v "*chema|mysql|test"|sed '1d')
DATABASE=${SHOWDATABASES}
for da in ${DATABASE}
do
     [ -d /server/backup/$da ] || mkdir -pv /server/backup/$da;
     $DUMP $da |gzip >/server/backup/${da}/${da}_$(date +%F).sql.gz;
done

#!/bin/bash

USER=root

PASSWD=''

PROT=3306

SOCKET=/var/lib/mysql/mysql.sock

DUMP="mysqldump -u$USER -p$PASSWD -S $SOCKET"

SHOWDATABASES=$($LOGIN -e "show databases;"|egrep -v "*chema|mysql|test"|sed '1d')

DATABASE=${SHOWDATABASES}

for da in ${DATABASE}

[ -d /server/backup/$da ] || mkdir -pv /server/backup/$da;

$DUMP $da |gzip >/server/backup/${da}/${da}_$(date +%F).sql.gz;

done

分表

#!/bin/bash
USER=root
PASSWD=''
PROT=3306
SOCKET=/var/lib/mysql/mysql.sock
MYLOGIN="mysql -u$USER -p$PASSWD -S$SOCKET"
MYDUMP="mysqldump -u$USER -p$PASSWD -S$SOCKET"
DATEBASE="$($MYLOGIN -e "show databases;"|egrep -vi "Data|_schema|mysql")"
 
for dbname in $DATEBASE
do
 TABLE="$($MYLOGIN -e "use $dbname;show tables;"|sed '1d')"
  for tname in $TABLE
   do
MYDIR=/server/backup/$dbname/${dbname}_$(date +%F)
     [ ! -d $MYDIR ] && mkdir -p $MYDIR
 $MYDUMP $dbname $tname |gzip >$MYDIR/${dbname}_${tname}_$(date +%F).sql.gz
    done
done