本文来自/xubusi/252.html
免费视频讲解见/course/course_id-5064.html
企业Shell实战-MySQL分库分表备份
今天是的最后一天,大家都开心的跨年,而我还在苦逼的呵呵—省略
此处内容来自老男孩教育oldboy以及老男孩26期王续精彩分享整理而来 为表示感谢,特整理此篇博文分享给大家！
项目联系笔者QQ：572891887 也可以加入架构师交流群:471443208 进行交流
[root@db02scripts]# cat /server/scripts/Store_backup.sh #脚本详细内容
#!/bin/sh
MYUSER=root
MYPASS=xuliangwei
SOCKET=/data/3306/mysql.sock
MYLOGIN=”mysql-u$MYUSER -p$MYPASS -S $SOCKET”
MYDUMP=”mysqldump-u$MYUSER -p$MYPASS -S$SOCKET -B”
DATABASE=”$($MYLOGIN-e “show databases;”|egrep -vi “Data|_schema|mysql”)”
fordbname in $DATABASE
do
MYDIR=/server/backup/$dbname
[ ! -d $MYDIR ] && mkdir -p $MYDIR
$MYDUMP $dbname|gzip>$MYDIR/${dbname}_$(date +%F).sql.gz
done
[root@db02/]# tree /server/backup/ #执行后的结果
/server/backup/
├── oldboy26
│ ├──oldboy26_-12-29.sql.gz
│ ├──oldboy26_-12-30.sql.gz
│ └──oldboy26_-12-31.sql.gz
├── oldboy_xuliangwei26
│ ├──oldboy_xuliangwei26_-12-29.sql.gz
│ ├──oldboy_xuliangwei26_-12-30.sql.gz
│ └──oldboy_xuliangwei26_-12-31.sql.gz
└── xuliangwei26
├──xuliangwei26_-12-29.sql.gz
├──xuliangwei26_-12-30.sql.gz
└──
xuliangwei26_-12-31.sql.gz
3directories, 9 files
Shell-MySQL分库分表备份脚本
[root@db02scripts]# catmysql_table.sh #脚本详细内容
#!/bin/sh
USER=root
PASSWD=xuliangwei
SOCKET=/data/3306/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”)”
fordbname 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
[root@db02scripts]# tree/server/backup/ #查看执行后的结果
/server/backup/
├── oldboy26
│ ├──oldboy26_-12-30
│ │├──oldboy26_oldboy_test_-12-30.sql.gz
│ │└──oldboy26_oldboy_test2_-12-30.sql.gz
│ └──oldboy26_-12-31
│ ├── oldboy26_oldboy_test_-12-31.sql.gz
│ └── oldboy26_oldboy_test2_-12-31.sql.gz
├── oldboy_xuliangwei26
│ ├──oldboy_xuliangwei26_-12-30
│ │├──oldboy_xuliangwei26_cloudstack_-12-30.sql.gz
│ │├──oldboy_xuliangwei26_dashborad_-12-30.sql.gz
│ │└── oldboy_xuliangwei26_student_-12-30.sql.gz
│ └──oldboy_xuliangwei26_-12-31
│ ├──oldboy_xuliangwei26_cloudstack_-12-31.sql.gz
│ ├──oldboy_xuliangwei26_dashborad_-12-31.sql.gz
│ └──oldboy_xuliangwei26_student_-12-31.sql.gz
└── xuliangwei26
├──xuliangwei26_-12-30
│├──xuliangwei26_openstack_-12-30.sql.gz
│├──xuliangwei26_saltstack_-12-30.sql.gz
│├──xuliangwei26_server_-12-30.sql.gz
│└──xuliangwei26_zabbix_-12-30.sql.gz
└──xuliangwei26_-12-31
├──xuliangwei26_openstack_-12-31.sql.gz
├──xuliangwei26_saltstack_-12-31.sql.gz
├──xuliangwei26_server_-12-31.sql.gz
└──xuliangwei26_zabbix_-12-31.sql.gz
9 directories, 18 files
项目联系笔者QQ：572891887 也可以加入架构师交流群:471443208 进行交流
