L007-oldboy-mysql-dba-lesson07
L007-oldboy-mysql-dba-lesson07
[root@web01 ~]# mysqldump -uroot
-ptestpassword -A >/root/mysql_backup/testbackup20141026.sql #全库备份
[root@web01 ~]# mysqladmin -uroot -ptestpassword
shutdown #关库
[root@web01 ~]#
/usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql/my.cnf & #启动
[root@web01 ~]# mysql -ptestpassword <
/root/mysql_backup/testbackup20141026.sql
#恢复
[root@web01 ~]# mysqldump -uroot -ptestpassword
mydb> /root/mysql_backup/mydb_20141026.sql
#备份单个库
[root@web01 ~]# mysql -uroot -ptestpassword
mydb < /root/mysql_backup/mydb_20141026.sql #恢复单个库
[root@web01 ~]# vi abc.sh #分表备份脚本
#!/bin/bash
#author:DBA
#2016-06-12
#modify
by DBA 2016-06-12
dbname="mydb"
dpath="/mysql_backup/$dbname"
myday=`date
+%Y%m%d`
if
[ ! -d "$dpath" ]
then
mkdir
-p $dpath
fi
mysql
-uroot -ptestpassword -e "use $dbname;show tables;"|grep -v
Tables_in_ > $dpath/table_list.txt
while
read line
do
mysqldump
-uroot -ptestpassword $dbname ${line} > /$dpath/${line}_$myday.sql
done
<$dpath/table_list.txt
rm
-rf $dpath/table_list.txt
[root@web01 ~]# vi backup.sh
#!/bin/bash
dpath="/mysql_backup"
mydays="7"
username="root"
mysql_pwd="testpassword"
xmysql="/usr/local/mysql/bin/mysql -S /tmp/mysql.sock -u$username
-p${mysql_pwd}"
xmysqldump="/usr/local/mysql/bin/mysqldump -S /tmp/mysql.sock
-u$username -p${mysql_pwd}"
today=`date +%Y%m%d`
old_backup_dir=`date -d "-${mydays} day" +%Y%m%d`
if [ -d $old_backup_dir ];then
rm -rf $old_backup_dir
fi
if [ ! -d $today ]; then
mkdir -p $dpath/$today
cp /var/lib/mysql/my.cnf
$dpath/$today
fi
$xmysql -e "show databases;"|grep -vE
"(Database|_schema)" > $dpath/db_list
$xmysql -e "flush tables with read lock;"
while read dbname
do
if [ ! -d $dpath/$today/$dbname
];then
mkdir -p
$dpath/$today/$dbname
fi
#######################
#echo $dbname
$xmysql -e "use $dbname;
show tables;"|grep -vE "(Tables_in|general_log|slow_log)" >
$dpath/table_list
while read tablename
do
#echo "echo
$tablename"
$xmysqldump $dbname
$tablename -R --events --triggers > $dpath/$today/$dbname/$tablename.sql
done < $dpath/table_list
#######################
done < $dpath/db_list
rm -rf $dpath/db_list
rm -rf $dpath/table_list
ls -al /application/mysql/data/mysql-bin* | grep -v mysql-bin.index | tail -1 | awk '{print $5,$9}'| awk -F'/' '{print $1,$5}' > /mysql_backup/$today/position.txt
$xmysql -e "unlock tables;"
[root@web01 ~]# vi backup2.sh
#!/bin/bash
dpath="/mysql_backup2"
if [ ! -d $dpath ]; then
mkdir -p $dpath
fi
today=`date +%Y-%m-%d`
before_day="$today 00:00:00"
mysql -S /tmp/mysql.sock -uroot -ptestpassword -e "PURGE MASTER
LOGS BEFORE '$before_day'"
n=`ps waux | grep mysql |grep -v grep | awk '{print $2}' | wc -l`
if [ $n -gt 0 ];then
/usr/local/mysql/bin/mysqladmin
-S /tmp/mysql.sock -u root -ptestpassword shutdown
fi
tar czf /mysql_backup2/mysql_`date +%w`.tar.gz /var/lib/mysql
/usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql/my.cnf
&
m=`ps waux | grep mysql |grep -v grep | awk '{print $2}' | wc -l`
if [ $m -ge 2 ];then
echo "start fail"
fi
