企业Shell面试题6：MySQL数据库分表备份

请实现对MySQL数据库进行分表备份，用脚本实现。

解答：

 

[root@db01 scripts]# cat fenbiao.sh#!/bin/bash#. /etc/rc.d/init.d/functionsstart_db () {if [ -e /etc/init.d/mysqld ]then    M_ST=$(/etc/init.d/mysqld status|awk '{print $3}')    [ "${M_ST}" != "running" ]&&/etc/init.d/mysqld start        sleep 1        M_ST=$(/etc/init.d/mysqld status|awk '{print $3}')        [ "${M_ST}" != "running" ]||exit 0else    echo  "start up file not exits,error"        exit 2  fi}dump_db () {for db in `mysql -u root -poldboy123 -e "show databases;"|sed '1,2d'`do    for tb in `mysql -u root -poldboy123 -e "use $db;show tables;"|sed '1d'`               do         mkdir -p /server/dbbackup/$(date +%F)/$db         /application/mysql/bin/mysqldump  --lock-tables=0  -uroot -poldboy123  $db  $tb  >/server/dbbackup/$(date +%F)/${db}/${db}.${tb}$(date +%F).sql            donedone}main() {  M_ST=$(/etc/init.d/mysqld status|awk '{print $3}')  if [ "${M_ST}" != "running" ]  then    echo "to start db"    start_db        sleep 3  fi   dump_db >/dev/null 2>&1}main

 

方法二:之分库备份:

 

 

[root@db01 scripts]# cat fenku_backup.sh #!/bin/bash#gaobo  40team  614627690@qq.com###对数据库进行启动二次检查function checkdb() {  if [ -f /etc/init.d/mysqld ]    then    db_stat=$(/etc/init.d/mysqld status|awk '{print $3}')    if [ "${db_stat}" != "running" ]        then        /etc/init.d/mysqld start        sleep 10        db_stat=$(/etc/init.d/mysqld status|awk '{print $3}')        echo $db_stat        [ "${db_stat}" != "running" ] && /etc/init.d/mysqld start        sleep 10        db_stat=$(/etc/init.d/mysqld status|awk '{print $3}')                echo $db_stat        [ "${db_stat}" != "running" ] && {            echo "启动失败,请检查数据库"            exit 11;        }                        fi      else    echo "数据库启动文件不存在请检查"  fi}# 按照库进行分库不同的文件备份# -e 不进入数据库执行命令function seperate_db() {for db in  $(mysql -uroot -poldboy123 -e "show databases;"|awk 'NR>1{print $0}') do    /application/mysql/bin/mysqldump  --lock-tables=0 -uroot -poldboy123  $db >/server/dbbackup/$(date +%F)/${db}$(date +%F).sqldone}#按照数据库的表为不同文件进行备份#seperate_tb () {for db in `mysql -u root -poldboy123 -e "show databases;"|sed '1,2d'`do    for tb in `mysql -u root -poldboy123 -e "use $db;show tables;"|sed '1d'`               do         mkdir -p /server/dbbackup/$(date +%F)/$db         /application/mysql/bin/mysqldump  --lock-tables=0  -uroot -poldboy123  $db  $tb  >/server/dbbackup/$(date +%F)/${db}/${db}.${tb}$(date +%F).sql            donedone}function main() {    checkdb        case $1 in         seperate_db)            seperate_db  >/dev/null 2>&1        ;;        seperate_tb)        seperate_tb  >/dev/null 2>&1        ;;        *)        echo "Usage fenku_backup.sh  {seperate_db|seperate_tb}"            ;;    esac    }main $1