6：7 题一起MySQL数据库分库备份

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

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

解答：

 

[root@db01 scripts]# cat fenbiao.sh
#!/bin/bash
#. /etc/rc.d/init.d/functions

start_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 0
else
    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
        

    done
done
}

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).sql

done

}


#按照数据库的表为不同文件进行备份
#
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
        

    done
done
}




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