Import MySQL Databases from another server

import_databases.sh

#!/bin/bash

# Author: Aamnah
# Link: https://aamnah.com

# get and restore all databased from another server on this server

REMOTE_SSH_HOST='123.123.123.123' # Server IP or FQDN of the remote server you're importing from
REMOTE_SSH_USER='root' 
REMOTE_SSH_KEY="~/.ssh/id_rsa"
REMOTE_SSH_PORT='22'

DB_HOST='local.server.com' # Server IP or FQDN of the local host
DB_USER='root'
DB_PASS='password'

REMOTE_DB_USER='root'
REMOTE_DB_PASS='password'
DB_DUMP_FILENAME="TRANSFER_databases"

import_databases() {
  
  # SSH to remote server, 
  # take a compressed mysqldump, 
  # import that dump directly from remote server (without transferring dumped file to this server)
  # the following is a Bash HERE document
  # you need to quote the variables for them to expand, like so "${VAR}"
  ssh -i ${REMOTE_SSH_KEY} -p ${REMOTE_SSH_PORT} ${REMOTE_SSH_USER}@${REMOTE_SSH_HOST} /bin/bash << SSHCOMMANDS
    mysqldump --user="${REMOTE_DB_USER}" --password="${REMOTE_DB_PASS}" --add-drop-database --all-databases | gzip -9 > "${DB_DUMP_FILENAME}".sql.gz
    gunzip < "${DB_DUMP_FILENAME}".sql.gz | mysql --user="${DB_USER}" --password="${DB_PASS}" --host="${DB_HOST}"
SSHCOMMANDS

  # NOTES
  # - You can only directly restore databases if the remote server allows remote connections. run `mysql_secure_installation` again and allow remote connections
  # - Otherwise, you have to transfer the files to the remote server first and then import them
  # - https://stackoverflow.com/questions/14779104/how-to-allow-remote-connection-to-mysql

  #   To allow remote connections: 
  #   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
  #   FLUSH PRIVILEGES;
  #   Comment out `#bind-address = 127.0.0.1` in `/etc/mysql/mysql.conf.d/mysqld.cnf`

  # - To restore all databases created using the --all-databases flag without providing the database names for individual dbs inside, you need to use the `--add-drop-database`
  # - option in conjuntcion with `--all-databases` when dumping databases. Doing so will add CREATE DATABASE queries for all databases, which will then allow us to bulk import
  # - the databases without providing their names
  # - using `--user` and `--password` instead of `-u` and `-p` avoids incorrect mysql command syntax errors

  # LINKS
  # - http://webcheatsheet.com/sql/mysql_backup_restore.php
  # - https://stackoverflow.com/questions/4412238/what-is-the-cleanest-way-to-ssh-and-run-multiple-commands-in-bash
  # - http://www.tldp.org/LDP/abs/html/here-docs.html
}

import_databases