L007-oldboy-mysql-dba-lesson07
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了L007-oldboy-mysql-dba-lesson07,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3216字,纯文字阅读大概需要5分钟。
内容图文
![L007-oldboy-mysql-dba-lesson07](/upload/InfoBanner/zyjiaocheng/467/03683a267f99420d963a43776c7d6c37.jpg)
[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
来自为知笔记(Wiz)
L007-oldboy-mysql-dba-lesson07
标签:
本文系统来源:http://www.cnblogs.com/bolinzhihua/p/5650808.html
内容总结
以上是互联网集市为您收集整理的L007-oldboy-mysql-dba-lesson07全部内容,希望文章能够帮你解决L007-oldboy-mysql-dba-lesson07所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。