mysql 备份脚本

最新推荐文章于 2024-11-05 11:19:17 发布

木华思流年

最新推荐文章于 2024-11-05 11:19:17 发布

阅读量153

点赞数

分类专栏： bash 文章标签： mysql bash

原文链接：https://blog.csdn.net/wanchaopeng

版权

bash 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

日常常用mysql建库备份shell

1.不登陆数据库，批量创建数据库

#!/bin/bash
PATH="/usr/local/mysql/bin:$PATH"
MYUSER=root
MYPASS=123456
SOCKET=/tmp/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
for dbname in shunmai langwenke guirentong
  do 
    $MYCMD -e "create dabases dbname"
  done

2.不登录数据库备份

#!/bin/bash
PATH="/usr/local/mysql/bin:$PATH"
DAPATH=/data/backup
MYUSER=root
MYPASS=123456
SOCKET=/tmp/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP="mysqldump -u$MYUSER -pMYPASS -S $SOCKET"
[ ! -d "$DAPATH" ] && mkdir $DAPATH
for dbname in `$MYCMD -e "show databases;"|sed '1,2d'`|egrep -v "mysql|test"
  do
    $MYDUMP $dbname |gzip >$DAPATH/${dbname}_$(date +%F).sql.gz
  done

3.不登录数据库批量建表，添加数据

#!/bin/bash
PATH="/usr/local/mysql/bin:$PATH"
DBPATH=/data/backup
MYUSER=root
MYPASS=123456
SOCKET=/tmp/mysql.sock
MYCMD="mysql -u$MYUSER -pMYPASS -S $SOCKET"
MYDUMP="mysqldump -u$MYUSER -pMYPASS -S $SOCKET"
for dbname in shunmai langwenke haibei guirentong
  do
    $MYCMD -e "use $dbname;create  table test (id int,name varchar(16)); insert into test values(1,'testdata');"
  done

4.不登陆数据库查表

#!/bin/bash
PATH="/usr/local/mysql/bin:$PATH"
MYUSER=root
MYPASS=123456
SOCKET=/tmp/mysql.sock
MYCMD="mysql -u$MYUSER -pMYPASS -S $SOCKET"
for dbname in shunmai langwenke haibei guirentong
  do
    echo =================${dbname}.test================
      $MYCMD -e "use $dbname;select * from ${dbname}.test;"
  done

5.不登陆数据库，批量备份

#!/bin/bash
PATH="/usr/local/mysql/bin:$PATH"
DBPATH=/data/backup
MYUSER=root
MYPASS=123456
SOCKET=/tmp/mysql.sock
MYCMD="mysql -u$MYUSER -pMYPASS -S $SOCKET"
MYDUMP="mysqldump -u$MYUSER -pMYPASS -S $SOCKET"
  [ ! -d "$DAPATH" ] && mkdir $DBPATH
  for dbname in `$MYCMD -e "show databases;"|sed '1,2d'|egrep -v "mysql|schema"`
    do
      mkdir $DBPATH/${dbname}_$(date +%F) -p
      for table in `$MYCMD -e "show tables from $dbname;"|sed '1d'`
        do
          $MYCMD $dbname $table|gzip >$DBPATH/${dbname}_$(date +%F)/${dbname}_$(table).sql.gz
        done
    done