How to exclude databases while importing a MySQL backup file

Introduction:

If you need to import a MySQL backup file that contains multiple databases, you may want to exclude some of the existing databases from being overwritten. In this tutorial, we will show you how to exclude specific databases while importing a MySQL backup file using a bash script.

Prerequisites:

Basic knowledge of bash scripting

Access to a command-line interface

A MySQL database backup file (.sql extension)

Steps:

1. Create a new bash script file and open it using a text editor of your choice.

2. Add the following code to the file:

bash
#!/bin/bash 
echo "Enter MySQL username: "

read -s USERNAME

echo "Enter MySQL password: "

read -s PASSWORD

echo "Enter MySQL host (default: localhost): "

read HOST 
if [ -z "$HOST" ]; 
  then
  HOST="localhost"

fi 
echo "Enter path to backup file (e.g. /path/to/backup.sql): "

read BACKUP_PATH
if [ ! -f "$BACKUP_PATH" ]; 
then
  
  echo "File not found: $BACKUP_PATH"
  
  exit 1

fi 
 DATABASES=$(mysql --user="$USERNAME" --password="$PASSWORD" --host="$HOST" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|mysql|mytinerycms|performance_schema|sys)")

for db in $DATABASES

do

 DB_ARGS+=("--database" "$db")

done 
 mysql --user="$USERNAME" --password="$PASSWORD" --host="$HOST" "${DB_ARGS[@]}" < "$BACKUP_PATH"

3. Save the file with a name such as mysql-import.sh.
4. Open a terminal or command prompt and navigate to the directory where you saved the script.
5. Make the script executable by running the following command:

chmod +x mysql-import.sh

7. When prompted, enter your MySQL username and password. If your MySQL server is running on a different host than localhost, enter the hostname as well.

8. Enter the path to the backup file when prompted.

9. The script will exclude the following databases from the import: information_schema, mysql, mytinerycms, performance_schema, sys. All other databases will be included.

10. The script will import the backup file to the specified MySQL server.

Conclusion:

In this tutorial, we showed you how to exclude specific databases while importing a MySQL backup file using a bash script. By modifying the script, you can exclude any database from the import by adding its name to the exclusion list. This is a simple and effective way to prevent overwriting existing databases while importing backups.