Cpanel db migration - Multiple export databases mysqldump - MySQL backup shell script

create-database.md

Old server

run this before multiple-import-mysql.sh

mysqldump -uroot -p --no-data --all-databases  > dump.sql 

New server

mysql -uroot -p > dump.sql 

db_backup.sh

#!/bin/bash

# Shell script to backup MySQL database

# Set these variables
MyUSER="" # DB_USERNAME
MyPASS="" # DB_PASSWORD
MyHOST="localhost" # DB_HOSTNAME

# Backup Dest directory
DEST="/backups" # /home/username/backups/DB

#make dir
if [ -d $DEST ]
then
    echo "Directory $DEST exist"
else
    echo "Directory does not $DEST exist, making dir"
    mkdir -pv $DEST || echo "problemer avslutter" | exit
fi

# Email for notifications
EMAIL=""

# How many days old files must be to be removed
HOURS=24
#Convert hours to min
MIN=$(expr $HOURS \* 60)
# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# Get date in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# Create Backup sub-directories
MBD="$DEST/$NOW/mysql"
install -d $MBD

# DB skip list
SKIP="information_schema
another_one_db"

# Get all databases
DBS="$($MYSQL -h $MyHOST -u $MyUSER -p$MyPASS -Bse 'show databases')"

# Archive database dumps
for db in $DBS
do

    skipdb=-1
    if [ "$SKIP" != "" ];
    then
        for i in $SKIP
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ]; then

        FILE="$MBD/$db.sql"
        if [ -f $FILE ]
        then
            echo "DB $db exist Skip"
        else
            echo "Start dump $db"
            $MYSQLDUMP -h $MyHOST -u $MyUSER -p$MyPASS $db --socket=/var/run/mysqld/mysqld.sock --quick > $FILE
        fi
    fi
done

# Archive the directory, send mail and cleanup
cd $DEST
tar -cf $NOW.tar $NOW
$GZIP -9 $NOW.tar

echo "MySQL backup is completed! Backup name is $NOW.tar.gz" | mail -s "MySQL backup" $EMAIL
rm -rf $NOW

# Remove old files
find $DEST -mmin +$MIN -exec rm -f {} \;

multiple-import-mysql.sh

#!/bin/bash

# Set these variables
MyUSER="root" # DB_USERNAME
MyPASS="" # DB_PASSWORD
MyHOST="localhost" # DB_HOSTNAME

for SQL in *.sql; do
    DB=${SQL/\.sql/};
    echo Importing $DB;
    mysql -u root -h $MyHOST -u $MyUSER -p$MyPASS $DB < $SQL;
done