Breaking News: Grepper is joining You.com. Read the official announcement!

mysql backup script

Nervous Nightingale answered on September 20, 2022 Popularity 8/10 Helpfulness 3/10

answer mysql backup script

related mysql backup bash script

mysql backup script

Comment

#!/bin/bash

# 以下配置資訊請自己修改
mysql_user="USER" #MySQL備份使用者
mysql_password="PASSWORD" #MySQL備份使用者的密碼
mysql_host="localhost"
mysql_port="3306"
mysql_charset="utf8" #MySQL編碼
backup_db_arr=("db1" "db2") #要備份的資料庫名稱，多個用空格分開隔開 如("db1" "db2" "db3")
backup_location=/opt/mysql  #備份資料存放位置，末尾請不要帶"/",此項可以保持預設，程式會自動建立資料夾
expire_backup_delete="ON" #是否開啟過期備份刪除 ON為開啟 OFF為關閉
expire_days=3 #過期時間天數 預設為三天，此項只有在expire_backup_delete開啟時有效

# 本行開始以下不需要修改
backup_time=`date +%Y%m%d%H%M`  #定義備份詳細時間
backup_Ymd=`date +%Y-%m-%d` #定義備份目錄中的年月日時間
backup_dir=$backup_location/$backup_Ymd  #備份資料夾全路徑
welcome_msg="Welcome to use MySQL backup tools!" #歡迎語

# 判斷MYSQL是否啟動,mysql沒有啟動則備份退出
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
        echo "ERROR:MySQL is not running! backup stop!"
        exit
else
        echo $welcome_msg
fi

# 連線到mysql資料庫，無法連線則備份退出
mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end
use mysql;
select host,user from user where user='root' and host='localhost';
exit
end

flag=`echo $?`
if [ $flag != "0" ]; then
        echo "ERROR:Can't connect mysql server! backup stop!"
        exit
else
        echo "MySQL connect ok! Please wait......"
        # 判斷有沒有定義備份的資料庫，如果定義則開始備份，否則退出備份
        if [ "$backup_db_arr" != "" ];then
                #dbnames=$(cut -d ',' -f1-5 $backup_database)
                #echo "arr is (${backup_db_arr[@]})"
                for dbname in ${backup_db_arr[@]}
                do
                        echo "database $dbname backup start..."
                        `mkdir -p $backup_dir`
                        `mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --default-character-set=$mysql_charset | gzip > $backup_dir/$dbname-$backup_time.sql.gz`
                        flag=`echo $?`
                        if [ $flag == "0" ];then
                                echo "database $dbname success backup to $backup_dir/$dbname-$backup_time.sql.gz"
                        else
                                echo "database $dbname backup fail!"
                        fi
                        
                done
        else
                echo "ERROR:No database to backup! backup stop"
                exit
        fi
        # 如果開啟了刪除過期備份，則進行刪除操作
        if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
                 #`find $backup_location/ -type d -o -type f -ctime +$expire_days -exec rm -rf {} \;`
                 `find $backup_location/ -type d -mtime +$expire_days | xargs rm -rf`
                 echo "Expired backup data delete complete!"
        fi
        echo "All database backup success! Thank you!"
        exit
fi

Popularity 8/10 Helpfulness 3/10 Language sql

Source: www.gushiciku.cn

Tags: mysql-backup script sql

Link to this answer
Share Copy Link

Contributed on Sep 20 2022

Nervous Nightingale

0 Answers Avg Quality 2/10

Closely Related Answers

mysql backup bash script

Comment

//MYSQL backup shell, create config.txt in the path using sample. 
//bin/config.txt.sample file


# Configurations for MySQL backup script
# Replace with the desired backup folder path
backup_folder="/path/to/backup/folder"
# Replace with the number of days to retain backups
backup_retention_days=1
# Replace with your actual database credentials
username="your_mysql_username"
password="your_mysql_password"
database="your_database_name"


//bin/mysql_backup.sh file

#!/bin/bash
# Load configuration from config.txt local
source "$(dirname "${BASH_SOURCE[0]}")/config.txt"

#load config in server
#CWD=$(pwd)
#source "${CWD}/config.txt"

# Function to perform MySQL dump
perform_backup() {
    timestamp=$(date +%Y%m%d_%H%M%S)
    backup_file="${backup_folder}/${database}_${timestamp}.sql"
    env MYSQL_PWD="${password}" mysqldump -u "${username}" "${database}" > "${backup_file}"
    echo "Backup created: ${backup_file}"
}
# Perform backup
perform_backup
# Clean up old backup files
find "${backup_folder}" -name "${database}_*.sql" -mtime "+${backup_retention_days}" -exec rm {} \;
echo "Old backups cleaned up"

Popularity 5/10 Helpfulness 1/10 Language shell

Source: Grepper

Tags: mysql-backup script bash sql

Link to this answer
Share Copy Link

Contributed on Sep 18 2023

its_me_dev

0 Answers Avg Quality 2/10

mysql backup script

Contents

More Related Answers

mysql backup script

Closely Related Answers

mysql backup bash script

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.