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.
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.
Automate MySQL backups, via SSH tunnels, in a couple of clicks.
Try SimpleBackups →
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.
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.
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.
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.
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
usernameis the SSH username on the remote MySQL server
mysql-serveris the host name or the IP of the remote MySQL server
3336is the port that
msqldumpwill 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.)
3306is 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.
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
~/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]
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
Type the command below
mysqldump -P 3336 -u dbuser -p password database_name > dumpfile.sql
3336is the local port used to configure ssh in the previous section. (TIP: If using the default port, this is not required.)
dbuseris the MySQL database user on the remote server (TIP: This is the MySQL user, not the remote user used when starting the SSH tunnel.)
passwordis the password of
dbuser. If this is not provided on the command line,
mysqldumpwill prompt for it.
database_nameis the name of the MySQL database to backup.
dumpfile.sqlis 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.
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
Type the command below:
mysql -P 3336 -u dbuser -p password database_name < dumpfile.sql
3336is the local port used to configure the SSH tunnel (not needed if using the default port).
dbuseris the MySQL database user on the remote server.
passwordis the password of
database_nameis the name of the MySQL database to restore.
dumpfile.sqlis 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.
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.
Free 7-day trial. No credit card required.
Have a question? Need help getting started?
Get in touch via chat or at [email protected]