How to back up MySQL databases from the command line in Linux

The current technological evolution of every aspect of life has made data more precious than gold and silver. If you can acquire, grow, and protect data, you are one step away from being a data god. Yet, big companies that control life aspects like e-commerce, fuel, transport, and food landscapes rely on data protection to shield themselves from an inevitable collapse.   

Right now, losing data is like losing your life insurance. So the database management system you are using should have a backup orientation. If you are a MySQL administrator or a user dealing with growing data, you should consider implementing a more than frequent backup automation plan. Reason? You might end up being a victim of a data hack or even alter your data by accident.

Such circumstances can lead to unforgiving data breaches, especially when you don’t have a database backup plan. If you are an invested MySQL user or administrator, this article is here to fix your database backup worries. We will meet two objectives. First, you will understand the implementation of exporting databases through “mysqldump”. Then, finally, we will touch on how using “crontab” can make this entire process easier through automation.

Prepare a data backup directory

Since Linux does not make a user recommendation for a MySQL data backup destination, It is up to you to choose a suitable backup location. For example, in this tutorial guide, we will be working under a backup directory in “/var/www_my_backups/”. We are only considering this approach to understand MySQL data backup mechanisms. Ideally, it is recommended that all-important data backups take place on an offside server. 

You can create your preferred backup directory on your local machine through a terminal command similar to the following:

$ sudo mkdir /var/www_my_backups/

Ensure that the Linux-operated backup machine you are using has privileged you with root access or sudo privileges. If you do not have owner access to the created backup directory, you will face permissions errors while running mysqldump tests. The following command should list the currently active system user and detail if you have ownership privileges on the created backup folder.

$ sudo chown $(whoami):$(whoami) /var/www_my_backups/

The mysqldump client utility

This MySQL tool carries out logical backups. It results in several SQL statement sets, which recreates the original database table data and object definitions when executed. In addition, one or several MySQL database dumps are backed up or transferred to a secondary SQL database server. 

A standard mysqldump command is represented by the following command syntax.

$ mysqldump -u [mysql_username] -p[mysql_password] [mysql_database_name] > /path/to/[mysql_dump_file_name].sql

• -u [mysql_username]: represents a privileged user of the MySQL database. This user should be able to execute database dump operations.
• -p[mysql_password]: represents the user password of the MySQL database. DO not add a space between “-p” and “[mysql_password]”.
• [mysql_dump_file_name]: represents the name of your MySQL database.
• >: points to the destination of the output dump
• /path/to/[mysql_dump_file_name].sql:  points to the path location of the associated dump file. You can give this dump file [mysql_dump_file_name] a custom name if you like. 

Before we proceed with this tutorial guide, there is something worth mentioning about “-p[mysql_password]”. While this article guide will focus on associating its usage with several MySQL dump examples, you should avoid using it directly when handling your real MySQL backup dumps, especially in a shared network. 

A  running dump can be hijacked with a two-dimensional command like “ps ax,” revealing the associated database username and password. However, using the location “~/.my.cnf” to store your MySQL database password makes the use of “-p[mysql_password]” in the stated dump command unnecessary. If this dump command is executing through a cron job, the command option “–defaults-extra-file=/path/to/.my.cnf” should point the mysqldump command to the location of the database password.   

Some MySQL database backup examples

Let us consider several user scenarios where we can use the mysqldump command to backup MySQL database data.  

Backing up all databases

Using the command option “–all-databases” in your mysqldump command will take care of all the MySQL database dumps on your Linux system. For example, the following command demonstrates how to dump all your MySQL databases to the already existing “/var/www_my_backups/” file. The user of this Linux system should be root or have sudo privileges. 

In our case, and for your understanding, we named our dump file “all-databases.sql,” but you can use any other name of your preference. Since we are dealing with all databases, being a root MySQL account user is necessary. 

$ mysqldump -u root -p[mysql_password] --all-databases > /var/www_my_backups/all-databases.sql

Backing up one database

If only one MySQL database is significant to you, creating its backup with the mysqldump command requires replacing the “[mysql_database]” command option with the actual name. The dump file name can take the name of this database “[mysql_database].sql” so that it becomes easy to trace and restore it later. You can also go with another custom dump file name if you wish. 

This example command is implemented using the root user, but any other user with access to the targeted database is a viable option. 

$ mysqldump -u root -p[mysql_password] [mysql_database_name] > /var/www_my_backups/[mysql_database_name].sql

Backing up multiple databases

Maybe you have specific MySQL databases selection that you wish to backup. In this case, the “[mysql_database_name]” command option will appear more than once, and each case is associated with the name of the database you wish to backup. Remember to space these databases’ names on the mysqldump command. The dump file “[mysql_database_name].sql” should also be associated with a unique name that you will remember.

$ mysqldump -u root -p[mysql_password] [mysql_database_1_name] [mysql_database_2_name] > /var/www_my_backups/[mysql_databases_1_2_names].sql

Backing up a single table

When your backup routine is only after a specific database table, creating its backup should have both the database name and the database table name as command options of the mysqldump command. You can give your dump file the same name as the targeted database table, e.g. [mysql_database_table_name].sql.     

$ mysqldump -u root -p[mysql_password] [mysql_database_name] [mysql_database_table_name] > /var/www_my_backups/[mysql_databases_table_name].sql

Backing up multiple tables

When you want to backup many specific MySQL database tables, a mention of all your selected database table names should come after the database name hosting these tables. The targeted dump file could take a name like [mysql_database_tables_1_2_names].sql

$ mysqldump -u root -p[mysql_password] [mysql_database_name] [mysql_database_table_1_name] [mysql_database_table_2_name] > /var/www_my_backups/[mysql_databases_tables_1_2_names].sql

Backing up a remote database(s)

This example implementation is also straightforward. The MySQL database dump command will need to include the “-h” command option followed by the remote machine’s hostname or associated IP address. All the other usual database backup command syntaxes should then follow. 

$ mysqldump -h [remote_computer_ip_or_hostname] -u root -p[mysql_password] [mysql_database_name] > /var/www_my_backups/[remote_mysql_database_name].sql

You can adjust this mysqldump command to deal with the other database backup cases already discussed, e.g., MySQL backups with multiple databases or tables.  

Backing up a database associated with compressions

If you want to associate your data backups with compressions, the “| gzip -c >” mysqldump command option can be used to pipe a gzip output. 

$ mysqldump -u root -p[mysql_password] [mysql_database_name] | gzip -c > /var/www_my_backups/[mysql_database_name].sql.gz

If your MySQL database is huge and you want to keep track of the compression progress, always consider implementing the verbose option as depicted in the following example.  

$ mysqldump -u root -p[mysql_password] [mysql_database_name] | gzip -c --verbose > /var/www_my_backups/[mysql_database_name].sql.gz

Restoring MySQL database

Once you are done with your MySQL database backup, what next? How do you access the data that you so carefully secured? Restoring your data requires adherence to the following MySQL restore syntax. 

$ mysql -u [mysql_username] -p[mysql_password] [mysql_database_name] < /path/to/[mysql_database_name].sql 

As you might not have noted, the only difference between this database restoration command and the database backup command is that we use the “mysql” option instead of the “mysqldump” option and the “<” option instead of the “>” option.  

Automating MySQL backups

The Linux operating system is equipped with several useful services that are priceless to a database administrator like the one under the MySQL RDBMS. One of these services is the cron service. It is effective in scheduling automated commands. These commands, once created, are allocated to the crontab cron table. You can access crontab through the following command. 

$ sudo crontab -e

If prompted, this command might want to associate its execution to a text editor to select the nano text editor.

selecting a crontab editor

A file with a name like “/tmp/crontab.LVY6A9/crontab” will open. At the bottom of this crontab file, input a viable cron schedule together with an applicable MySQL dump command. The example illustrated below implements the use of gzip compression for daily database backups. Sometimes you might have large .sql files scheduled for backup. Using gzip reduces such files to reasonable sizes before backup storage. It helps with backup memory management.    

00 03 * * * mysqldump -u root -p[mysql_password] [mysql_database_name] | gzip -c > /var/www_my_backups/[mysql_database_name].sql.gz

The command option “00 03 ***” can be interpreted in the following manner. Every 24 hours after 3 AM, the mysqldump command following it is executed to back up a database. The database backup file that currently existed before the initiation of this backup process is overwritten. In your case, you do not need to wait after 24 hours to witness your database backup automation in action through crontab.

You can edit the “00 03 ***” option on the crontab file to something like “02 00 ***”, and in just two minutes, the backup process should self-initialize. Alternatively, if your time is 22:30, editing the file with “34 22 ***” will initialize the database backup process at 22:34. Remember to save (Ctrl+X) this crontab file before closing it for this command to become executable. 

After the minutes you set elapse, the cron job should have been executed. Then, list the created backup folder on your terminal, and the created .sql.gz backup file should be present. 

$ ls -l /var/www_my_backups/

The resulting output should be similar to the following:

-rw-r--r-- 1 root root 36M Jul 29 22:24 [mysql_database_name].sql.gz

If you have problems spotting the .sql.gz MySQL backup file, proofread your crontab time or the entire command. There could be a syntax error, or something could be missing. Alternatively, the system cron log could point to where there is an issue.

$ sudo grep CRON /var/log/syslog 

Remember to reset the crontab entry to your preferred database schedule once you confirm everything is working correctly.   

Using my.cnf to store MySQL database passwords  

We have already mentioned the drawbacks of the “-p[mysql_password]” option on a mysqldump command, especially under a shared network. We need to discuss how to implement password storage in the “~/.my.cnf” file. Users using cron to automate their database backups will need to understand the implementation of the “–defaults-extra-file=/path/to/.my.cnf” command option.   

Editing my.cnf file

The home directory of your Linux system contains this hidden file. The direct system path to it is “/home/your_username/.my.cnf”. Use the nano text editor to open this file. The “~” option points to the home directory.

$ sudo nano ~/.my.cnf

Edit this open file according to the following syntax to successfully store your MySQL database password. The portion “YOUR_DB_PASS” is the only entry you need to change with your actual database password. Enter these information details at the bottom of the file and save them. 

[mysqldump] 
password=YOUR_DB_PASS

Use Ctrl+X to save this file. This “my.cnf” file also needs some permission settings. Implement the following command:

$ sudo chmod 600 ~/.my.cnf

It’s now time to see the recreation of our new mysqldump command with the “-p[mysql_password]” command option eliminated.

$ mysqldump -u root [mysql_database_name] | gzip -c > /var/www_my_backups/[mysql_database_name].sql.gz 

As you can see, we did not add anything. It only seems like the only thing we removed is the “-p[mysql_password]” command option. 

Crontab and –defaults-extrs-file

For users who prefer automating database backups, you will need to fetch the database password in the “~/.my.cnf” file through the “–defaults-extra-file” command option. This approach makes things easy for mysqldump command when it needs to reference the database user and password authenticity. You have to be specific about the path to the my.cnf file and not just use the “~” symbol. Consider the following implementation inside the crontab file:

30 22 * * *   mysqldump --defaults-extra-file=/home/system_username/.my.cnf -u root [mysql_database_name] | gzip -c > /var/www_my_backups/[mysql_database_name].sql.gz 

In this example, crontab executes every day at 22:30 hours to create a backed-up gzip compression of the MySQL database.  

Final note

This article looked at local database backup mechanisms about the “/var/www_my_backups” backup directory. Since you now understand how the backup process takes place, you should scale higher and start thinking about offsite backups. However, a more practical approach is through SFTP access configuration that points to this “/var/www_my_backups” backup directory.

With such a configuration in place, it is possible to create an SFTP cron job through a remote server to fetch a copy of these locally stored database files for insurance storage at night and daily. 

As we conclude this awesome article guide, you are now a proud master of MySQL database backup scenarios, database backup restoration, and database backup automation. You should now leap of faith and be confident in using cron jobs to schedule and handle your MySQL database backup automation. The automation schedules do not have to be daily as they can also be weekly and monthly.