Remote MySQL Database Backup/Dump

Once a MySQL server is set up, the first task on any database administrator’s list is to prepare for backups. To execute backups, sysadmins can run them directly on the server. In most instances, though, they will access the server remotely.

🧑‍💻 All the code in this article is bundled in this Gist.

Table of Contents

DBAs can do this using the MySQL Workbench or an application like phpMyAdmin. But beforehand, sysadmins must install and configure the applications for use. This adds new maintenance and support work. It also introduces another potential point of failure in the MySQL stack. Third-party applications require extra software. These requirements increase the possibility of introducing a security bug.

A simpler approach is to use command-line tools to access the MySQL server to execute backup/restore command. MySQL comes with a suite of command-line tools specifically for that purpose. They have no dependency on external providers. When a security bug is discovered, it will be patched automatically as part of regular updates. Command-line tools also have an added benefit: they can be added to a script and executed without human intervention.

In most situations, administrators don’t have physical access to database servers. Sometimes, they might not be allowed to login to the server directly. Instead, they must execute all commands remotely from their desktop computer. They need an approach that allows them to connect to the server remotely and securely.

In this post, we will look at such an approach to back up and restore a MySQL database: SSH tunneling.

Connect to a remote MySQL via SSH tunnel

What is SSH Tunneling?

As companies rely on a hybrid model for work, the use of secure connections becomes essential. The more privileges the user has, the more critical a secure connection becomes. As the workforce becomes more mobile, businesses cannot ensure that sysadmins are in a secure location (for example, inside the office). A secure tunnel for connecting to sensitive servers becomes essential. SSH is an excellent tool to establish such secure connections.

Secure shell (SSH) tunneling allows for secure remote access to resources without exposing them to the world. It provides end-to-end data encryption, meaning that an unwanted party cannot intercept data. This makes it ideal for applications where security is required.

An SSH tunnel is a way of connecting to a remote computer and sending data across an insecure network via a secure connection. It is primarily used to connect to a remote server and access data on it as if connected directly. SSH does this by creating a secure connection between the local computer and the server to access remotely.

The encrypted connection provides privacy and security to the application that uses it. The application can transmit data securely over an insecure or untrusted network such as a home network or a public Wi-Fi network.

With SSH tunneling, sysadmins and DBAs can connect to a private network or computer without sharing files and settings. Once they have established an SSH connection with a remote server, they can use commands as if accessing the server locally.

What is local port forwarding?

Local port forwarding is a technique that allows a user to redirect traffic from their computer to a remote server. The server can be on the same network or on the internet. Users to set up a connection between two devices without exposing the connection information. It works by opening ports between the client (e.g., a laptop) and a remote server (e.g., a MySQL database server).

Once the two devices establish a connection, a user can execute commands locally, but those commands execute on the remote server. Local port forwarding simplifies system and database administration.

Without local port forwarding, a system administrator has to login remotely to a server to execute commands. By using local port forwarding, they can behave as if they logged onto the remote server. Server maintenance agencies often utilize local port forwarding to securely manage and maintain their clients' remote servers without requiring direct internet exposure.

How to connect to mysql via ssh tunnel

To back up a MySQL database using local commands, you first need to set up secure local port forwarding to the MySQL server. This will prevent other from intercepting the data flowing between the system administrator’s machine and the MySQL server.

Local port forwarding can be set up interactively (on the command line or in a script) or with a specific configuration file. With an interactive setup, users control the SSH tunnel from the command line. They can easily cut the connection between machines.

If sysadmins often reuse the same tunnel, a configuration file can be a better option. Configuration files can also be useful for executing the same backup and restore commands on different servers. The commands don’t change, only the SSH tunnel configuration does.

Setting up an SSH tunnel interactively

To begin, local port forwarding requires a valid SSH server installation on the remote server. The examples below use the Linux/Mac command-line tool, but Windows users can use Cygwin or PuTTY to obtain the same behavior.

Open a terminal and execute the following command:

ssh -L 3306:mysql-server:3306 username@mysql-server

where:

• username is the SSH username on the remote MySQL server
• mysql-server is the host name or the IP of the remote MySQL server
• 3336 is the port that msqldump will use to connect to the remote MySQL server (TIP: to simplify, use 3306, which is the MySQL default. We use 3306 to avoid confusion between the local port and the remote port.)
• 3306 is the port on which the MySQL server is listening. This is the default port and should be adjusted to the MySQL server configuration settings.

As long as the window remains open, the SSH tunnel will remain functional. It’s also possible to keep the tunnel running in the background by using the -f option. If the terminal is closed by accident, the SSH tunnel will remain active until the ssh process is stopped.

Setting up an SSH tunnel configuration

The SSH configuration file setup for local port forwarding looks as follows:

## Sample local port forwarding options
LocalForward 3336 mysql-server:3306

To use this configuration automatically, save it in /etc/ssh/ssh_config or ~/ssh/config. Alternatively, using -F config_file allows the use of a specific configuration file for a command. When the SSH configuration becomes more complex, this approach becomes more useful.

When using a configuration file, the SSH tunnel can be started as follows:

ssh username@mysql-server [-F config_file]

How use mysqldump via SSH tunnel

Once the SSH tunnel has been configured correctly, it is possible to execute a backup of the remote MySQL server by using local commands.

• Open a new terminal (or use the same one if using the -f option).
• Type the command below

mysqldump -P 3336  -u dbuser -p password database_name > dumpfile.sql

where

• 3336 is the local port used to configure ssh in the previous section. (TIP: If using the default port, this is not required.)
• dbuser is the MySQL database user on the remote server (TIP: This is the MySQL user, not the remote user used when starting the SSH tunnel.)
• password is the password of dbuser. If this is not provided on the command line, mysqldump will prompt for it.
• database_name is the name of the MySQL database to backup.
• dumpfile.sql is the name of the file that will contain the backup data. (TIP: put a timestamp on the file or put the file in a time-stamped folder.)

The resulting file will contain all the information needed to recreate the database and populate it with the existing data at the time of the backup. Backup time depends on the amount of data in the database.

How to restore a MySQL backup via the command line through an SSH tunnel

A backup using mysqldump creates a standard text file with a series of SQL statements that reinsert the data into a database. Therefore, restoring data consists of executing these statements.

The process to restore a MySQL backup is like the one used to create the backup. First, set up the SSH tunnel, then execute the restore command for MySQL, as follows:

Open a new terminal (or use the same one if using the -f option).

Type the command below:

mysql -P 3336 -u dbuser -p password database_name < dumpfile.sql

where

• 3336 is the local port used to configure the SSH tunnel (not needed if using the default port).
• dbuser is the MySQL database user on the remote server.
• password is the password of dbuser.
• database_name is the name of the MySQL database to restore.
• dumpfile.sql is the name of the file that contains the data to restore.

If the database does not exist and dumpfile.sql does not contain the appropriate SQL CREATE command, then database_name must be created first.

How to dump a remote MySQL database (without SSH tunneling)

The utility mysqldump provides the flexibility to export databases from a remote server. Below, we'll guide you through the process for exporting a specific database, multiple databases, or all databases from a remote host.

Before proceeding, ensure you have the necessary information: MySQL username, password, host IP address, and port number of the remote database server. For demonstration, we will use the following sample details:

• host: 12.345.678.91
• user: sb_user
• port: 3306 (the default port for MySQL)

Remember to substitute these placeholders with your actual credentials.

Export a Single Database

To export a single database, you can use the following command, which will prompt you for the password:

mysqldump -h 12.345.678.91 -u sb_user -P 3306 -p database_name > database_backup.sql

Export Multiple Databases

For exporting several databases at once, the command slightly alters to include the --databases option followed by the list of databases:

mysqldump -h 12.345.678.91 -u sb_user -P 3306 -p --databases database_one database_two > databases_backup.sql

Export All Databases
To backup all databases from the remote server in one go:

mysqldump -h 12.345.678.91 -u db_user -P 3306 -p --all-databases > all_databases_backup.sql

In all instances, after initiating the command, you'll be prompted to enter the user's password for security reasons. These commands are straightforward yet powerful tools for backing up your MySQL databases from a remote location.

Final words

It’s important to back up MySQL databases regularly to ensure that it is possible to recover in case of an emergency. While there are many methods to back up databases, one of the easiest options is to use SSH tunneling. By creating an SSH tunnel and using local port forwarding, it is easy to back up MySQL databases regularly, using a schedular.

Remote backup of MySQL databases using SSH tunneling allows the backup client to connect securely to the remote server. The remote server appears like a local machine. This makes it easier to create and maintain backup scripts.

SSH tunneling for backups easy to put in place and can expand as a company’s database backup needs grow.

NOTE: We focused on command-line Linux/Mac commands. Some of the links provided were specific to Linux.