Restore MySQL on Destination Server from Backup

Restore MySQL on Destination Server from Backup

We will show you here how to restore MySQL on the destination server easily from the backup that we synced via Rsync and iNotifyWait utilities by running a simple bash script.


Create Restore Script

We will create a simple script to restore MySQL database quickly and effectively.

1. Open an empty file on the source server inside master_backup directory by running the command below on PuTTY SSH client or any Linux terminal:

root@mail:~# nano /var/master_backup/restore_db.sh

2. Then, add the below script inside it. If you use different name for the backup directory, please change it in the code.

#!/bin/bash

# NOTE: THE FILE HAS TO BE INSIDE THE SAME FOLDER AND YOU SHOULD NOT ADD .ZIP TO THE FILE NAME
# EXAMPLE: if you want to restore the database of sitebase, the backup zip file has to be inside /var/websites_backup/databases/sitebase

# Only change the below if you want to move the backup to another folder
# **********************************************************************
backup_dir="/var/master_backup/"

# Example of inputs from the prompt
# *********************************
# FILE_NAME=sitebase_db-2020-07-03--11:00:01
# ALIAS=all or =sitebase
FILE_NAME=$1
ALIAS=$2

echo ""
read -p "THE CURRENT DATABASE << ${ALIAS}_db >> WILL BE REPLACED, ARE YOU SURE? Type: restore or import, to proceed: "  prompt
if [[  $prompt == "restore" || $prompt == "import" ]]
then
        # Dangerous Stuff Here
        echo "Restoring has started..."
else
        echo ""
        echo "Wrong input, sync with delete hasn't been done"
        echo ""
        exit 0
fi


# Variables required for the MySQL query
# **************************************
echo ""
DB_USER=$ALIAS"_user"
DB_NAME=$ALIAS"_db"
if [[ $ALIAS =~ all ]]
then
        DB_USER=root
        # the database name that will be used in the MySQL query
        DB_NAME=""
fi
backup_folder=$backup_dir"databases/"$ALIAS


# Variables required to output the inputs
# ***************************************
if [[ $ALIAS =~ all ]]
then
        echo "Database Name: All Databases"
else
        echo "Database Name: $DB_NAME"
fi
echo  "Directory: $backup_folder"



# We use printf because it processes new lines, unlike echo
printf "\n\n"

unzip -p $backup_folder/$FILE_NAME.zip > $backup_folder/$FILE_NAME.sql
mysql -u $DB_USER -p $DB_NAME < $backup_folder/$FILE_NAME.sql


printf "\n\n"
echo  "If no errors above, it's successful :)"
printf "\n\n"

3. In order to send the file we’ve just created restore_db.sh from the source to the destination server, we just need run Rsync on the source server to sync the backup directory master_backup between two servers. Any change in the source directory will be reflected in the destination directory. Run the below command. Don’t forget to replace the IP address below with your own IP address.

root@mail:~# flock -n lock_file -c "rsync -vzru -e 'ssh -p 22' --rsync-path='sudo rsync' --delete /var/master_backup/ rsyncuser@137.184.33.153:/var/master_backup/"

Rsync will only send the files that got change. As you see, it sent the resotre_db.sh file as it was the only change in the backup directory.

Sync Between Servers to Send restoredb_file to Restore MySQL

4. Go to destination server’s MySQL console then enter the console by running the command:

root@vpsprof-backup:~# mysql

After that, run the below command to see the current databases:

MariaDB [(none)]> show databases;

As you see, here are the current databases available:

Show Databases on Destination Server

5. Install the unzip utility on the destination server as we need it in our bash script to unzip the zipped backup files by running the below command:

root@vpsprof-backup:~# apt install unzip
Install Unzip on Destination Server

b. Restore All MySQL Databases via restore_db.sh Bash Script

6. Now, let’s test the restore_db.sh script by trying to import (restore) all databases on destination server (backup server) because this server has no databases and no database users other than root user. First, we need to find the zip file to import by running the command:

root@vpsprof-backup:~# ls -la /var/master_backup/databases/all
List All Zipped MySQL Databases

We will import the latest zipped file, which is: all_db-2021-08-22--01:13:46.zip which belongs to all databases including all the database users.

Now, run the bash script with two inputs. First input is the zipped database file name without the .zip extension which is: all_db-2021-08-22--01:13:46. Second input is the alias of the website which is: all, because in our case we will import all databases for all projects. If we will import the database of the sitebase project only, the alias will be sitebase. Don’t forget to replace the file name with your own file name because every file has different name as the name is derived from the date and the time.

root@vpsprof-backup:~# bash /var/master_backup/restore_db.sh all_db-2021-08-22--01:13:46 all

You will get a prompt to confirm because it’s dangerous process, to confirm you should type restore or import. After typing: restore, we got another prompt to enter the password of the root.

Run restoredb.sh Script for All Databases Confirm

Table ‘User’ Exists Error

Enter the root password then hit Enter key. Oppps! We got the error below!

Error Table User Already Exists

The error says the table user exists. We don’t know why but to resolve it, simply just delete mysql database which includes the users table by running this MySQL command after logging to MySQL prompt.

MariaDB [(none)]> drop database mysql;
Fix the Error Drop MySQL Database

Now try again running the restore_db.sh script:

root@vpsprof-backup:~# bash /var/master_backup/restore_db.sh all_db-2021-08-22--01:13:46 all

As you see below, no errors after entering the password 🙂 So it worked!

Run restoredb.sh Script for All Databases Success

7. Let’s make sure it worked by listing the current databases on the destination server by running the MySQL command:

MariaDB [(none)]> show databases;
Show All Imported Databasees

As you see, the databases on the destination server are identical to the databases on the source server.

8. Don’t forget to flush privileges on the destination server, because if you don’t flush privileges, you might not be able to login to the imported users like sitebase_user. Run the below command to flush privileges:

MariaDB [(none)]> flush privileges;

After flushing privileges, all the previous passwords for the users on the destination (backup) server will be changed because the import process replaces the mysql database on destination server completely which includes all the users and their passwords. So, to login to MySQL console via root user, you need to use the same MySQL root password on the source server. You can change root password by following the hints here: Create New MySQL Database (Change Database User Password part). In the article, we changed the password of sitebase_user, you can apply the same on root user as below):

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourNewPassword';

Also, to login to MySQL console from root user without entering the password every time, you need to update the CNF file with the new password for the root user, follow the hints here: Install MariaDB on Ubuntu (Use CNF File to Login to MariaDB on Ubuntu Without a Password part).

c. Single Database Restoration via restore_db.sh Bash Script

9. [Bonus] You don’t need to apply this but we will explain how to import and restore MySQL for a single database instead of all databases. Let’s show you how to import the latest backup of the sitebase database project. Run the list command below to list the backup zip files for the sitebase project:

root@vpsprof-backup:~# ls -la /var/master_backup/databases/sitebase
List Imported Zipped Sitebase Databases

As you see, the latest file is: sitebase_db-2021-08-22--01:13:46.zip which belongs to the sitebase project (alias).

Now, run the bash script same as what we did before for all databases with two inputs. First input is the zipped database file name without the .zip extension which is: sitebase_db-2021-08-22--01:13:46. Second input is the alias of the website which is: sitebase. Don’t forget to replace the file name with your own file name because every file has different name as the name is derived from the date and the time.

root@vpsprof-backup:~# bash /var/master_backup/restore_db.sh sitebase_db-2021-08-22--01:13:46 sitebase

You will get a prompt to confirm because it’s dangerous process. In order to confirm, you should type restore or import. After typing: restore, we got another prompt to enter the password of the sitebase_user. Enter the password then hit Enter key and that’s it! No errors, it worked as seen below.

Run restoredb.sh Script for Sitebase Databaeses Success

As the imported database (sitebase_db) doesn’t include users like the mysql database, we don’t need to run MySQL command: flush privileges.

Leave a Comment

Your email address will not be published.