How to Create a Backup of MySQL Databases Using mysqldump on Ubuntu 20.04

In this tutorial, we will show you how to create a backup of MySQL databases on an Ubuntu 20.04 VPS, and create a backup of the entire /var/lib/mysql directory.

We will create the backup of the databases using MySQL’s command, aptly named mysqldump. After that we will then show you how to perform a backup of the /var/lib/mysql directory where MySQL have been located. Performing regular backups of your database and database server is vital to protecting the data that you have on your server. This way, in case something goes wrong on your Ubuntu 20.04 VPS, or if MySQL breaks for some reason (incompatibilities or otherwise), you’ll have a safe backup that you can revert to and prevent the loss of your precious data.

Once you create a backup of MySQL databases and save them on your server or on a remote location, you can then always be able to restore the databases later from this backup, if needed. Creating a backup of MySQL database is essential for any website owner, and it should be done on a regular basis. Just follow the simple steps in this tutorial and your data will be redundant in no time. Let’s begin.

What is MySQL?

MySQL is an open-source relational database management system (RDBMS). MySQL is a component of the LAMP web application software stack (and others), which is an acronym for Linux, Apache, MySQL, and PHP. MySQL is used by many web applications, including WordPress, Drupal, Laravel, and so many more. MySQL is also used by many popular websites, including YouTube, Twitter, and Facebook.

This multi-step guide was written and tested for Ubuntu 20.04, but it should work on other Linux distributions as well. Let’s get started!

First off, we assume that you have SSH access to your server.

Log in to your server via SSH:

ssh root@server_ip -p port_number

You can replace root with the username of an administrator account if needed (our VPSes all have root access by default). Then replace server_ip with the IP address of your server, and port_number with your SSH port, with 22 being the default value.

Step 1. Update OS packages

Let’s make sure that your operating system is up-to-date.

apt-get update

Step 2. Install the MySQL Database server

MySQL is a popular database management system and it can be installed easily on any Linux server. Thanks to its popularity, packages for MySQL exist on almost all modern Linux distributions. The latest version of MySQL is version 8.0, but a newer version may exist after this article has been written.

To install the MySQL service, run the following command:

apt install mysql-server

In order to verify that MySQL is installed, as well as check the status of the MySQL service, we can use the following commands:

qpkg -l | grep -imysql

systemctl status mysql

The output of the systemctl status mysql command should be similar to this one:

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2020-06-07 07:49:38 UTC; 52min ago
   Main PID: 17700 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 2266)
     Memory: 325.7M
     CGroup: /system.slice/mysql.service
             └─17700 /usr/sbin/mysqld

Let’s go over managing the MySQL service. Enable the MySQL service to start on server boot with the following command:

systemctl enable mysql

For starting and stopping the MySQL service, we can use the following commands respectively:

systemctl start mysql

systemctl stop mysql

In order to check the installed MySQL version, execute the following command:

mysql -V

The output should look similar to this:

mysql  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

To improve the security of the MySQL server, it is a good idea to run the mysql_secure_installation script by typing the following command. It allows you to perform some extra configuration, such as setting a root password, deleting the example database, and so on:

mysql_secure_installation

Step 3. Create a MySQL Database

In this step, we will create a MySQL database, MySQL user, and a password for the new user, along with the required permissions for our test database. Later we will make a dump of the newly created database.

Log in to your MySQL console with the following command:

mysql -u root -p

Enter password:  (Enter your MySQL root password if you set it during the mysql_secure_installation process. Leave blank if you didn’t)

Once you are logged in, execute the following commands one-by-one:

mysql> CREATE DATABASE testDB;
mysql> CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'StrongPassword';
mysql> GRANT ALL PRIVILEGES ON testDB.* TO 'admin_user'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> exit;

Do not forget to replace “StrongPassword“ with your own strong generated password.

Once you create a MySQL database, we can list the all the MySQL databases from within the MySQL console using the following command:

show databases;

The output should look like this:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testDB             |
+--------------------+
5 rows in set (0.00 sec)

Step 4. Create a Backup using ‘mysqldump’

This is the main step where we will create a backup of MySQL databases using the mysqldump command-line utility.

Enter in a directory of your choice where you want the backup to be saved.

For the purposes of this tutorial, we will use the /backup directory.

cd /backup

Execute the following command to create a database dump of the test database that we created previously:

mysqldump -u admin_user -p testDB > backup.sql

Enter the “admin_user” password that you set and list the files in the /backup directory to make sure the database backup was created successfully:

ls -al | grep backup

The output should look like this:

-rw-r--r--  1 root root 1267 Jun  7 09:52 backup.sql

If you want to create a database dump of all databases on your server use the following command using the MySQL root user credentials:

mysqldump --all-databases -u root -p > AllDatabaseBackup.sql

Enter the MySQL root password and again, to check that backup was made successfully, run the following command:

ls -al | grep All

The output shoud look like this:

-rw-r--r--  1 root root 1036456 Jun  7 10:15 AllDatabaseBackup.sql

Step 5. Copy the MySQL Database Directory

This step is optional. If you want to copy the entire directory where MySQL databases are located along with its data, first, you have to stop the MySQL service with the following command:

systemctl stop mysql

Verify that the MySQL service is stopped with this command:

systemctl status mysql

The output should look like this:

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Sun 2020-06-07 10:50:19 UTC; 4s ago
    Process: 17700 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 17700 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"

Once this is done, we are ready to copy the MySQL database directory with the rsync command:

rsync -Waq --numeric-ids /var/lib/mysql/ /backup/mysql.raw/

Check the /backup/mysql.raw directory and list the files and directories inside to ensure that the backup of the /var/lib/mysql directory was made successfully.

cd /backup/mysql.raw

ls -alh

After a raw backup has been created, we can start the MySQL service using the following command:

systemctl start mysql

That’s it. Congratulations, you have successfully created a backup with the “mysqldump” command-line utility, and a raw backup of MySQL databases using rsync on your Ubuntu 20.04 VPS. Now you can safely store and protect your data.

If you are one of our Managed Ubuntu Hosting customers, you don’t have to create a MySQL backup by yourself on Ubuntu 20.04 – our expert Linux admins will set up and optimize your MySQL server for you, and even implement database backups to happen automatically. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post on how to Create a Backup of MySQL Using mysqldump on Ubuntu 20.04, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.