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

July 17, 2020 by Jeff Wilson


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.