分类: bash, Linux, Misc, MySQL

MySQL导出结构和部分数据的脚本

N年不碰这个捡起来好痛苦
为了让远程数据库结构与配置数据与本地同步,写了个脚本,可能是重复劳动,但还蛮实用

#! /bin/env bash

dumpdir=./dumpdata
rows=1000
user=root
pass=root

#########################################

mysql=$(which mysql 2> /dev/null)
if [ "xx" = "xx"$mysql ]; then
    echo 'mysql command not found'
fi

mysqldump=$(which mysqldump 2> /dev/null)
if [ "xx" = "xx"$mysqldump ]; then
    echo 'mysqldump command not found'
fi

mysql="$mysql -u$user -p$pass"

databases=$($mysql -e "show databases;" | grep -i -v Database| grep -i -v information_schema| grep -i -v mysql| grep -i -v performance_schema| grep -i -v test)

for d in $databases; do
    mkdir -p $dumpdir/$d
    tables=$($mysql -e "use $d; show full tables;" | grep BASE | sed 's/\sBASE TABLE//')
    #echo $tables
    for t in $tables; do
        echo "$d.$t ..."
        $mysqldump $d $t --where "true limit $rows" > $dumpdir/$d/$t.sql
    done
done

发表评论

评论