shell 批量创建_备份 mysql 数据库表

最新推荐文章于 2025-09-10 20:20:06 发布

weixin_30859423

最新推荐文章于 2025-09-10 20:20:06 发布

阅读量91

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 shell

原文链接：http://www.cnblogs.com/vincenshen/p/6610447.html

本文介绍使用Shell脚本创建MySQL数据库及数据表备份的方法。包括创建数据库、整体数据库备份和单独的数据表备份等实用脚本。这些脚本适用于root用户，并指定了数据库的socket路径。

#!/bin/bash

user=root
password=123456
socket=/var/lib/mysql/mysql.sock
mycmd="mysql -u$user -p$password -S $socket"
for dbname in oldboy oldgirl vincen;do
        $mycmd -e "create database $dbname"
done

备份数据库

#!/bin/bash

DBPATH=/tmp/backup
USER=root
PASS=123456
SOCK=/var/lib/mysql/mysql.sock
DUMP="mysqldump -u$USER -p$PASS -S $SOCK"
[ ! -d $DBPATH ] && mkdir -p $DBPATH

for dbname in oldboy oldgirl vincen;do
        $DUMP $dbname | gzip >$DBPATH/${dbname}_$(date +%F).sql.gz
done

备份数据表

#!/bin/bash
. /etc/init.d/functions

BKPATH=/tmp/bakcup
USER=root
PASS=123456
SOCK=/var/lib/mysql/mysql.sock
CMD="mysql -u$USER -p$PASS -S $SOCK"

[ ! -d $BKPATH ] && mkdir -p $BKPATH

for dbname in oldboy oldgirl vincen;do
        echo "starting backup $dbname"
        mkdir $BKPATH/${dbname}_$(date +%F) -p
        for table in `$CMD -e "show tables from $dbname;"|sed '1d'`;do
                $CMD $dbname $table|gzip >$BKPATH/${dbname}_$(date +%F)/${dbname}_${table}.sql.gz
        done
        action "bakcup $dbname" /bin/true
done