Mysql備份III

Mysqldump和mysqldumper

A備份和恢復所有庫

$ time mysqldumo –all-databases > /mysql/backup/date.time.database.dump.sql
$ echo $?
$ ls -lah /mysql/backup/date.time.database.dump.sql
$ time mysql -u[user] -p -f < date.time.database.dump.sql > 
date.time.database.dump.sql.out 2>&1
$echo $?
$ls -lah date.time.database.dump.sql.out

$/usr/local/mysql/bin/mysqldump -S /tmp/mysql.sock -u root -p password -A
>/mysql/backup/ date.time.database.dump.sql
$mysql -S /tmp/mysql.sock -p password < /mysql/backup/ date.time.database.dump.sql

B備份和恢復一個庫

$/usr/local/mysql/bin/mysqldump -S /tmp/mysql.sock -u root -p password databasename
(這里是庫名)
>/mysql/backup/ date.time.databasename.dump.sql
$Mysql -S /tmp/mysql.sock -u root -p password databasename < /mysql/backup/ date.time.databasename.dump.sql

Or

Mysql>create database databasename;
Mysql>use databasename;
Mysql>system pwd
/mysql/backup
Mysql>source /mysql/backup/ date.time.databasename.dump.sql
Mysql>show tables;

C備份和恢復一個表

$/usr/local/mysql/bin/mysqldump -S /tmp/mysql.sock -u root -p password databasename
(這里是庫名) tablename(表名)
>/mysql/backup/ date.time.databasename.tablename.port.dump.sql

$Mysql -S /tmp/mysql.sock -u root -p password databasename < /mysql/backup/ date.time.databasename.tablename.port.dump.sql

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
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”
#dbname=“datebasename”
dbakpath=“/mysql/backup/$dbname”
#dbakpath=“back up path”
#
#$cm -e “flash tables with read lock;”
#lock
#
#
if ["$dbname" = "" ]
echo “dbname null”
exit 1
fi
if [!-d”$dbakpath”]
then
mkdir -p “$dbakpath”
fi
cd “$dbakpath”
#tables
$cm -e “use  $dbname; show tables;”| grep -v Tables_in_ > ./table_list.txt
date=`date +%Y_%m_%d`
while read line
do
echo xxxx$line
#data
$dm $dbname ${line} > $dbakpath/${line}_$date.sql
done <./table_list.txt
rm -f ./table_list.txt
#del temp file
#ls -a /var/lib/mysql/mysql-bin* |grep -v mysql-bin.index|sort|head -1|awk’{print$5,$9}’|awk-F ‘/’’{printf $1,$5}’> $dbakpath_$date_position.txt
#
#cm -e “unlock tables;”

D備份和恢復表結構

$ mysqldump–all-databases –no-data 
> /mysql/backup/date.time.database.dumpschema.sql
$ echo $?
$ ls -lah /mysql/backup/date.time.database.dumpschema.sql
$ time mysql -u[user] -p -f < date.time.database.dumpschema.sql > 
date.time.database.dumpschema.sql.out 2>&1
$echo $?
$ls -lah date.time.database.dumpschema.sql.out

E備份和恢復存儲過程和觸發器

$ mysqldump–all-databases –no-data –no-create-info –events –routines 
> /mysql/backup/date.time.database.dumpobjects.sql
$ echo $?
$ ls -lah /mysql/backup/date.time.database.dumpobjects.sql
$ time mysql -u[user] -p -f < date.time.database.dumpobjects.sql > 
date.time.database.dumpobjects.sql.out 2>&1
$echo $?
$ls -lah date.time.database.dumpobjects.sql.out


$/usr/local/mysql/bin/mysqldump -S /tmp/mysql.sock -u root -p password -R –triggers -d -E -A > /mysql/backup/time.date.datebasename.triggereventsfunctions.dump.sql
$/usr/local/mysql/bin/mysql -S /tmp/mysql.sock -u root -p password <
/mysql/backup/time.date.datebasename.triggereventsfunctions.dump.sql

數據恢復過程中閃退需要檢查字符集和環境需要手工修改文件修復

ScriptS:

#!/bin/bash
#clean passed days old backup;back up DB; 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 "del back before x days ："
read passday
passday=”$passday”
user=“$user”
password=“$password”
dbname=“$dbname”

dbakpath=“/mysql/backup/$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”
#
#$cm -e “flash tables with read lock;”
#lock
#
#
#
if [!-d ”$dbakpath”]
then
mkdir -p “$dbakpath”
echo “$dbakpath not exsit create already”>>  ./dbak.log 
fi

cd “$dbakpath”
date=`date +%Y_%m_%d`
#lbday=”last back up data”
lbday=` date +"%Y_%m_%d" -d "-${passday} day"`
mkdir ./$lbday
ls ./$lbday/*  >> dbak.log 
cp *_lbday.sql ./$lbday
rm -rf *_$lbday.sql 
rm -rf ./$lbday
echo “del ok” >>  ./dbak.log

if [!-d”$date”]
then
mkdir -p “$date”
cp /var/lib/mysql/my.cnf  $dbakpath/$date/my.cnf.$date
echo “$date not exsit create already”>>  ./dbak.log 
fi

cd $date


#Oldbackupdir=odir

$cm -e “show databases;”|grep -vE “( Databases|_schema)” >  $dbackpath/db_list
$cm -e “use $dbname show tables;”|grep -v Table_in  > $dbackpath/table_list

############################################


while read dbname
do
############################################
echo $dbname
if [! -d”$dbackpath/$date/$dbname”];then
mkdir -p $dbackpath/$date/$dbname
fi

$cm -e “use $dbname show tables;”|grep -vE”(Tables_in|general_log|slow_log)” Table_in  >  $dbackpath/table_list


############################################
while read tablename
do
#echo $tablename
#$dm$dbname$tablename -R –events --triggers >$dbackpath/$date/$dbname/$
{tablename}.sql

$dm$dbname$tablename -R –events -- triggers > $dbackpath/$date/$dbname/$tablename
.sql

done  < $dbackpath/table_list


############################################
done  <  $dbackpath/db_list

rm -rf $dbackpath/db_list
rm -rf $dbackpath/table_list
#ls -a /var/lib/mysql/mysql-bin* |grep -v mysql-bin.index|sort|head -1|awk’{print$5,$9}’|awk-F ‘/’’{printf $1,$5}’> $dbakpath_$date_position.txt
#
#cm -e “unlock tables;”
mysql備份.PartI.pdf