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
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.
Table of Contents
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.
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:
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:
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:
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
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/
/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.