Linux Shell腳本專欄_MySQL資料庫備份_09

文章目錄

• ​​一、MySQL資料庫備份單循環​​

• ​​1. 安裝mysql​​
• ​​2. 配置mysql環境變量​​
• ​​3. 重新整理環境變量​​
• ​​4. 建立資料庫和表lue​​
• ​​5. 腳本制作​​
• ​​6. 運作腳本​​
• ​​7. 檢視備份的sql檔案​​
• ​​7. 腳本更新動态傳參​​
• ​​8. 運作腳本​​
• ​​9. 檢視備份的sql檔案​​

• ​​二、MySQL資料庫表備份多循環​​

• ​​2.1. 腳本制作​​
• ​​2.2. 運作腳本​​
• ​​2.3. 指定目錄檢視指定資料庫的表​​
• ​​2.4. 腳本更新​​
• ​​2.5. 運作腳本​​

• ​​三、添加備份輸出​​

• ​​3.1 備份資料庫​​
• ​​3.2 備份資料庫和表​​

一、MySQL資料庫備份單循環

1. 安裝mysql

​​Linux6、7 系列 安裝、解除安裝mysql​​

2. 配置mysql環境變量

vim /etc/profile
export MYSQL_HOME=/app/mysql-5.7.25
export PATH=$PATH:${JAVA_HOME}/bin:${MYSQL_HOME}/bin      

3. 重新整理環境變量

source /etc/profile      

4. 建立資料庫和表lue

5. 腳本制作

#建立腳本
vim db_backup.sh      

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=root
PASS=root
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

for DB in $DB_LIST; do
    BACKUP_NAME=$BACKUP_DIR/${DB}_${DATE}.sql
    if ! mysqldump -h$HOST -u$USER -p$PASS -B $DB > $BACKUP_NAME 2>/dev/null; then
        echo "$BACKUP_NAME 備份失敗!"
    fi
done      

6. 運作腳本

[root@ly-01 ~]# bash db_backup.sh      

7. 檢視備份的sql檔案

cd /data/db_backup
ll      

[root@ly-01 db_backup]# pwd
/data/db_backup
[root@ly-01 db_backup]# ll
total 300
-rw-r--r-- 1 root root 303483 Feb 25 11:26 ke_2020-02-25_11-26-26.sql
[root@ly-01 db_backup]#      

7. 腳本更新動态傳參

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=$1
USER=$2
PASS=$3
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

for DB in $DB_LIST; do
    BACKUP_NAME=$BACKUP_DIR/${DB}_${DATE}.sql
    if ! mysqldump -h$HOST -u$USER -p$PASS -B $DB > $BACKUP_NAME 2>/dev/null; then
        echo "$BACKUP_NAME 備份失敗!"
    fi
done      

8. 運作腳本

[root@ly-01 ~]# bash db_backup_yh.sh 192.168.43.242 root root      

9. 檢視備份的sql檔案

[root@ly-01 db_backup]# pwd
/data/db_backup
[root@ly-01 db_backup]# ll
total 300
-rw-r--r-- 1 root root 303483 Feb 25 11:26 ke_2020-02-25_11-26-26.sql
[root@ly-01 db_backup]# ll
total 600
-rw-r--r-- 1 root root 303483 Feb 25 11:26 ke_2020-02-25_11-26-26.sql
-rw-r--r-- 1 root root 303488 Feb 25 11:29 ke_2020-02-25_11-29-20.sql
[root@ly-01 db_backup]#      

二、MySQL資料庫表備份多循環

2.1. 腳本制作

#建立腳本
vim db_table_backup.sh      

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=root
PASS=root
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

for DB in $DB_LIST; do
    BACKUP_DB_DIR=$BACKUP_DIR/${DB}_${DATE}
    [ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/null
    TABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $DB;show tables;" 2>/dev/null)
    for TABLE in $TABLE_LIST; do
        BACKUP_NAME=$BACKUP_DB_DIR/${TABLE}.sql
        if ! mysqldump -h$HOST -u$USER -p$PASS $DB $TABLE > $BACKUP_NAME 2>/dev/null; then
            echo "$BACKUP_NAME 備份失敗!"
        fi
    done
done      

2.2. 運作腳本

bash db_table_backup.sh      

2.3. 指定目錄檢視指定資料庫的表

[root@ly-01 ~]# cd /data/db_backup/
[root@ly-01 db_backup]# ll
total 604
-rw-r--r-- 1 root root 303483 Feb 25 11:26 ke_2020-02-25_11-26-26.sql
-rw-r--r-- 1 root root 303488 Feb 25 11:29 ke_2020-02-25_11-29-20.sql
drwxr-xr-x 2 root root   4096 Feb 25 11:37 ke_2020-02-25_11-37-18
[root@ly-01 db_backup]# cd ke_2020-02-25_11-37-18/
[root@ly-01 ke_2020-02-25_11-37-18]# ll
total 340
-rw-r--r-- 1 root root   2282 Feb 25 11:37 ke_alarm_clusters.sql
-rw-r--r-- 1 root root   2117 Feb 25 11:37 ke_alarm_config.sql
-rw-r--r-- 1 root root   2337 Feb 25 11:37 ke_alarm_consumer.sql
-rw-r--r-- 1 root root   2200 Feb 25 11:37 ke_consumer_bscreen.sql
-rw-r--r-- 1 root root   2068 Feb 25 11:37 ke_consumer.sql
-rw-r--r-- 1 root root   1985 Feb 25 11:37 ke_logsize.sql
-rw-r--r-- 1 root root   2693 Feb 25 11:37 ke_metrics_offline.sql
-rw-r--r-- 1 root root 289927 Feb 25 11:37 ke_metrics.sql
-rw-r--r-- 1 root root   2177 Feb 25 11:37 ke_p_role.sql
-rw-r--r-- 1 root root   2747 Feb 25 11:37 ke_resources.sql
-rw-r--r-- 1 root root   2213 Feb 25 11:37 ke_role_resource.sql
-rw-r--r-- 1 root root   2132 Feb 25 11:37 ke_sql_history.sql
-rw-r--r-- 1 root root   1962 Feb 25 11:37 ke_topic_rank.sql
-rw-r--r-- 1 root root   1965 Feb 25 11:37 ke_user_role.sql
-rw-r--r-- 1 root root   2089 Feb 25 11:37 ke_users.sql
[root@ly-01 ke_2020-02-25_11-37-18]#      

2.4. 腳本更新

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=$1
USER=$2
PASS=$3
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

for DB in $DB_LIST; do
    BACKUP_DB_DIR=$BACKUP_DIR/${DB}_${DATE}
    [ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/null
    TABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $DB;show tables;" 2>/dev/null)
    for TABLE in $TABLE_LIST; do
        BACKUP_NAME=$BACKUP_DB_DIR/${TABLE}.sql
        if ! mysqldump -h$HOST -u$USER -p$PASS $DB $TABLE > $BACKUP_NAME 2>/dev/null; then
            echo "$BACKUP_NAME 備份失敗!"
        fi
    done
done      

2.5. 運作腳本

[root@ly-01 ~]# bash db_table_backup.sh 192.168.43.242 root root      

三、添加備份輸出

3.1 備份資料庫

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=$1
USER=$2
PASS=$3
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

for DB in $DB_LIST; do
    BACKUP_NAME=$BACKUP_DIR/${DB}_${DATE}.sql
        echo "$BACKUP_NAME 備份成功!" 
    if ! mysqldump -h$HOST -u$USER -p$PASS -B $DB > $BACKUP_NAME 2>/dev/null; then
        echo "$BACKUP_NAME 備份失敗!"
    fi
done      

[root@ly-01 ~]# bash db_backup_yh.sh 192.168.43.242 root root
/data/db_backup/ke_2020-02-25_11-49-46.sql 備份成功!
[root@ly-01 ~]#      

3.2 備份資料庫和表

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=$1
USER=$2
PASS=$3
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

for DB in $DB_LIST; do
    BACKUP_DB_DIR=$BACKUP_DIR/${DB}_${DATE}
    [ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/null
    TABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $DB;show tables;" 2>/dev/null)
    for TABLE in $TABLE_LIST; do
        BACKUP_NAME=$BACKUP_DB_DIR/${TABLE}.sql
            echo "$BACKUP_NAME 備份成功!" 
        if ! mysqldump -h$HOST -u$USER -p$PASS $DB $TABLE > $BACKUP_NAME 2>/dev/null; then
            echo "$BACKUP_NAME 備份失敗!"
        fi
    done
done      

[root@ly-01 ~]# bash db_table_backup.sh 192.168.43.242 root root
/data/db_backup/ke_2020-02-25_11-46-50/ke_alarm_clusters.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_alarm_config.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_alarm_consumer.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_consumer.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_consumer_bscreen.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_logsize.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_metrics.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_metrics_offline.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_p_role.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_resources.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_role_resource.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_sql_history.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_topic_rank.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_user_role.sql 備份成功!
/data/db_backup/ke_2020-02-25_11-46-50/ke_users.sql 備份成功!
[root@ly-01 ~]#