Shell脚本实现分库分表操作

目录

一，分库备份

二，分库操作

三，分库分表备份

四，备份还原

---

一，分库备份

#!/bin/bash
mysql_cmd='-uroot -pzly666666'
bak_path=/backup/db
[ -d ${bak_path} ] || mkdir -p ${bak_path}mysql ${mysql_cmd} -e 'show databases' -N | egrep -v 'sys|mysql|information_schema|performance_schema' > dbnamewhile read line
domysqldump ${mysql_cmd} -B $line | gzip > ${bak_path}/${line}_$(date +%F).sql.gzdone < dbname
rm -rf dbname

二，分库操作

#!/bin/bash
mysql_cmd='-uroot -pzly666666'
bak_path=/backup/db
mysql -uroot -pzly666666 -e 'show tables from homework;' -N > tbname#mysql ${mysql_cmd} -e 'show databases' | egrep -v 'sys|mysql|information_schema|performance_schema' > dbnamewhile read line
do[ -d ${bak_path}/homework ] || mkdir -p ${bak_path}/homeworkmysqldump ${mysql_cmd} homework $line | gzip > ${bak_path}/homework/homework_${line}_$(date +%F).sql.gzdone < tbname
rm -rf tbname

三，分库分表备份

#!/bin/bash
mysql_cmd='-uroot -pzly666666'
bak_path=/backup/db
exclude_db='sys|mysql|information_schema|performance_schema'mysql ${mysql_cmd} -e 'show databases' -N | egrep -v ${exclude_db} > dbnamewhile read db
do[ -d ${bak_path}/$db ] || mkdir -p ${bak_path}/$dbmysqldump ${mysql_cmd} -B $db | gzip > ${bak_path}/${db}/${db}_$(date +%F).sql.gzmysql ${mysql_cmd} -e "show tables from ${db};" -N > tbnamewhile read tbdomysqldump ${mysql_cmd} $db $tb | gzip > ${bak_path}/${db}/${db}_${tb}_$(date +%F).sql.gz
done < tbname
done < dbname
rm -rf tbname
rm -rf dbname

 

四，备份还原

 zcat /backup/db/homework/homework_emp_2023-07-30.sql.gz | mysql -uroot -pzly666666 homework