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

Transferring a database between virtual private servers can be accomplished using a SCP (Secure Copy), a method of copying files derived from the SSH Shell. Keep in mind, you will need to know the passwords for both virtual servers.

In order to migrate the database, there are two steps:

The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.

Introduction

Importing and exporting databases is a common task in software development. You can use data dumps to back up and restore your information. You can also use them to migrate data to a new server or development environment.

In this tutorial, you will work with database dumps in MySQL or MariaDB (the commands are interchangeable). Specifically, you will export a database and then import that database from the dump file.

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.

Table of Contents

В этом руководстве объясняется, как создавать резервные копии и восстанавливать базы данных MySQL или MariaDB из командной строки с помощью утилиты mysqldump.

Файлы резервных копий, созданные утилитой mysqldump, в основном представляют собой набор операторов SQL, которые можно использовать для воссоздания исходной базы данных. Команда mysqldump также может создавать файлы в форматах CSV и XML.

Вы также можете использовать утилиту mysqldump для переноса вашей базы данных MySQL на другой сервер MySQL.

Если вы не сделаете резервную копию своих баз данных, программная ошибка или отказ жесткого диска могут иметь катастрофические последствия. Чтобы сэкономить время и нервы, настоятельно рекомендуется принять меры предосторожности и регулярно создавать резервные копии баз данных MySQL.

When you are running a MySQL server with critical information, you probably want to set up a protection mechanism such as replication or backup to prevent loss of data in the server due to unforseen events. While there are number of ways to back up MySQL databases, the simpliest solution is Linux command line. In particular, a Linux command-line tool called mysqldump allows you to back up MySQL databases without needing to shut down a MySQL server. mysqldump generates as output a text file containing a series of MySQL commands that represent a current snapshot of MySQL databases being backed up. The mysqldump output file can easily be compressed and/or encrypted as needed.

In this tutorial, I will describe how to back up a MySQL server with mysqldump.

The mysqldump program is contained in MySQL client package. So you will need to install MySQL client package first.

Step Three—Import the Database

Once the data has been transferred to the new server, you can import the database into MySQL:

mysql -u root -p newdatabase < /path/to/newdatabase.sql

With that, your transfer via SCP will be complete.

By Etel Sverdlov

Backup MyISAM or InnoDB/MyISAM Databases

The MySQL flush statement in the above closes all open tables in the MySQL server, and obtains read locks to all the tables in all existing databases, thereby preventing any write to the databases. This ensures a consistent snapshot of a running system. Now proceed to perform the backup.

Finally, release the global lock to the databases.

Резервное копирование всех баз данных MySQL в отдельные файлы

Утилита mysqldump не предоставляет возможности резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла FOR bash :

Приведенная выше команда создаст отдельный файл дампа для каждой базы данных, используя имя базы данных в качестве имени файла.

Restoring a single MySQL database from a full MySQL dump

If you backed up all your databases using the -all-databases option and want to restore one database from a backup file containing multiple databases, use the —one-database option as shown below:

mysql —one-database database_name < all_databases.sql

Step 1 — Exporting a MySQL or MariaDB Database

The mysqldump console utility exports databases to SQL text files. This makes it easier to transfer and move databases. You will need your database’s name and credentials for an account whose privileges allow at least full read-only access to the database.

Use mysqldump to export your database:

The command will produce no visual output, but you can inspect the contents of data-dump.sql to check if it’s a legitimate SQL dump file.

The top of the file should look similar to this, showing a MySQL dump for a database named database_name.

SQL dump fragment— MySQL dump 10.13 Distrib 5.7.16, for Linux (x86_64)
—
— Host: localhost Database: database_name
————————————————— ——
— Server version 5.7.16-0ubuntu0.16.04.1

If any errors occur during the export process, mysqldump will print them to the screen.

Mysqldump Command Syntax

Mysqldump utility expressions have the following form:

To use the mysqldump command, the MySQL server must be available and running.

MySQL dump recovery

You can restore a MySQL dump using the mysql tool. The general command syntax is as follows:

mysql database_name < file.sql

In most cases, you will need to create an import database. If the database already exists, it must first be deleted.

In the following example, the first command will create a database named database_name and then import the database_name.sql dump into it:

mysql -u root -p -e «create database database_name»;mysql -u root -p database_name < database_name.sql

Create backup with timestamp

If you want to store more than one backup in the same location, you can add the current date to the backup file name:

The above command will create a file in the following format database_name-20180617.sql

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.

apt install mysql-server

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; 52 minutes 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

systemctl enable mysql

systemctl start mysql

systemctl stop mysql

The output should look similar to this:

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

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.

The output should look like this:

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

The output should look like this:

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

Deprecated

This article covers a version of Ubuntu that is no longer supported. If you are currently operating a server running Ubuntu 12.04, we highly recommend upgrading or migrating to a supported version of Ubuntu:

Reason:
Ubuntu 12.04 reached end of life (EOL) on April 28, 2017 and no longer receives security patches or updates. This guide is no longer maintained.

See Instead:
This guide might still be useful as a reference, but may not work on other Ubuntu releases. If available, we strongly recommend using a guide written for the version of Ubuntu you are using. You can use the search functionality at the top of the page to find a more recent version.

What is MySQL?

MySQL is a popular database management solution that uses the SQL querying language to access and manipulate data. It can easily be used to manage the data from websites or applications.

Backups are important with any kind of data, and this is especially relevant when talking about databases. MySQL can be backed up in a few different ways that we will discuss in this article.

For this tutorial, we will be using an Ubuntu 12.04 VPS with MySQL 5.5 installed. Most modern distributions and recent versions of MySQL should operate in a similar manner.

How to Backup When Using Replication

It is possible to use MySQL replication to backup data with the above techniques.

Replication is a process of mirroring the data from one server to another server (master-slave) or mirroring changes made to either server to the other (master-master).

While replication allows for data mirroring, it suffers when you are trying to save a specific point in time. This is because it is constantly replicating the changes of a dynamic system.

To avoid this problem, we can either:

Disabling Replication Temporarily

You can disable replication for the slave temporarily by issuing:

Another option, which doesn’t completely stop replication, but puts it on pause, so to speak, can be accomplished by typing:

mysql -u -p -e ‘STOP SLAVE SQL_THREAD;’

After replication is halted, you can backup using one of the methods above. This allows you to keep the master MySQL database online while the slave is backed up.

When this is complete, restart replication by typing:

Making the Backup Machine Read-Only Temporarily

You can also ensure a consistent set of data within the server by making the data read-only temporarily.

You can perform these steps on either the master or the slave systems.

First, log into MySQL with enough privileges to manipulate the data:

Next, we can write all of the cached changes to the disk and set the system read-only by typing:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

Now, perform your backup using mysqldump.

Once the backup is complete, return the system to its original working order by typing:

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

How to Backup a MySQL Database with mysqldump

One of the most common ways of backing up with MySQL is to use a command called «mysqldump».

Backing Up

There is an article on how to export databases using mysqldump here. The basic syntax of the command is:

Restoring

To restore a database dump created with mysqldump, you simply have to redirect the file into MySQL again.

We need to create a blank database to house the imported data. First, log into MySQL by typing:

Create a new database which will hold all of the data from the data dump and then exit out of the MySQL prompt:

CREATE DATABASE ;
exit

mysql -u -p < .sql

Your information should now be restored to the database you’ve created.

Экспорт и импорт базы данных MySQL одной командой

Вместо создания файла дампа из одной базы данных и последующего импорта резервной копии в другую базу данных MySQL вы можете использовать следующий однострочный файл:

Приведенная выше команда передаст вывод клиенту mysql на удаленном хосте и импортирует его в базу данных с именем remote_database_name . Перед запуском команды убедитесь, что база данных уже существует на удаленном сервере.

Install mysqldump on Linux

$ sudo apt-get install mysql-client

Install mysqldump on CentOS, Fedora or RHEL

$ sudo yum install mysql

In order to generate an online snapshot for a live MySQL server, you need to prevent any update to its databases while backup is being processed. How to achieve that depends on what storage engine you are using for the MySQL tables inside. So first find out which storage engine (e.g., MyISAM, Innodb) you are using. This guide will tell you how.

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:

Backup InnoDB Databases

If all your MySQL tables use InnoDB, you can use —single-transaction option with mysqldump to make an online backup:

Step 2 — Importing a MySQL or MariaDB Database

To import an existing dump file into MySQL or MariaDB, you will have to create a new database. This database will hold the imported data.

mysql root

CREATE DATABASE new_database

You’ll see this output confirming the database creation.

Query OK, 1 row affected (0.00 sec)

If the command runs successfully, it won’t produce any output. If any errors occur during the process, mysql will print them to the terminal instead. To check if the import was successful, log in to the MySQL shell and inspect the data. Selecting the new database with USE new_database and then use SHOW TABLES; or a similar command to look at some of the data.

Используйте параметр —all-databases для резервного копирования всех баз данных MySQL:

Как и в предыдущем примере, приведенная выше команда создаст один файл дампа, содержащий все базы данных.

Step Two—Copy the Database

SCP helps you copy the database. If you used the previous command, you exported your database to your home folder.

A sample transfer might look like this:

After you connect, the database will be transferred to the new virtual private server.

Создание сжатой резервной копии базы данных MySQL

Если размер базы данных очень велик, рекомендуется сжать вывод. Для этого просто направьте вывод в утилиту gzip и перенаправьте его в файл, как показано ниже:

Prerequisites

To import or export a MySQL or MariaDB database, you will need:

Note: As an alternative to manual installation, you can explore the DigitalOcean Marketplace’s MySQL One-Click Application.

Step 1. Update OS packages

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

Step 3. Create a MySQL Database

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

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

Резервное копирование единой базы данных MySQL

Наиболее распространенный вариант использования инструмента mysqldump — резервное копирование одной базы данных.

Например, чтобы создать резервную копию базы database_name с именем database_name используя пользовательский root и сохранить ее в файл с именем database_name.sql вы должны выполнить следующую команду:

Вам будет предложено ввести пароль root. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.

Если вы вошли в систему как тот же пользователь, которого используете для экспорта, и этот пользователь не требует пароля, вы можете опустить параметры -u и -p :

How to Backup MySQL Information using automysqlbackup

There is a utility program called «automysqlbackup» that is available in the Ubuntu repositories.

This utility can be scheduled to automatically perform backups at regular intervals.

sudo apt-get install automysqlbackup

Run the command by typing:

sudo nano /etc/default/automysqlbackup

You can see that this file, by default, assigns many variables by the MySQL file located at «/etc/mysql/debian.cnf». This contains maintenance login information

The default location for backups is «/var/lib/automysqlbackup». Search this directory to see the structure of the backups:

daily monthly weekly

If we look into the daily directory, we can see a subdirectory for each database, inside of which is a gzipped sql dump from when the command was run:

ls -R /var/lib/automysqlbackup/daily

.:
database_name information_schema performance_schema

./database_name:
database_name_2013-08-27_23h30m. Tuesday.sql.gz

./information_schema:
information_schema_2013-08-27_23h30m. Tuesday.sql.gz

./performance_schema:
performance_schema_2013-08-27_23h30m. Tuesday.sql.gz

Ubuntu installs a cron script with this program that will run it every day. It will organize the files to the appropriate directory.

Step One—Perform a MySQL Dump

Before transferring the database file to the new VPS, we first need to back it up on the original virtual server by using the mysqldump command.

After the dump is performed, you are ready to transfer the database.

Резервное копирование нескольких баз данных MySQL

Для резервного копирования нескольких баз данных MySQL с помощью одной команды вам нужно использовать параметр —database за которым следует список баз данных, которые вы хотите —database . Каждое имя базы данных должно быть разделено пробелом.

Приведенная выше команда создаст файл дампа, содержащий обе базы данных.

How to Backup a MySQL Table to a Text File

You can save the data from a table directly into a text file by using the select statement within MySQL.

The general syntax for this operation is:

SELECT * INTO OUTFILE » FROM ;

This operation will save the table data to a file on the MySQL server. It will fail if there is already a file with the name chosen.

Note: This option only saves table data. If your table structure is complex and must be preserved, it is best to use another method!

Step 5. Copy the MySQL Database Directory

Verify that the MySQL service is stopped with this command:

Need a fast and easy fix?

✔ Unlimited Managed Support

✔ Supports Your Software

✔ 2 CPU Cores

✔ 2 GB RAM

✔ 50 GB PCIe4 NVMe Disk

✔ 1854 GeekBench Score

✔ Unmetered Data Transfer

● 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

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.

Автоматизируйте резервное копирование с помощью Cron

Автоматизировать процесс резервного копирования баз данных так же просто, как создать задание cron, которое будет запускать команду mysqldump в указанное время.

Чтобы настроить автоматическое резервное копирование базы данных MySQL с помощью cronjob, выполните следующие действия:

Вы также можете создать другое задание cron для удаления любых резервных копий старше 30 дней:

find /path/to/backups -type f -name -mtime +30 -delete

A Note About Techniques That Are No Longer Recommended

MySQL includes a perl script for backing up databases quickly called «mysqlhotcopy». This tool can be used to quickly backup a database on a local machine, but it has limitations that make us avoid recommending it.

The most important reason we won’t cover mysqlhotcopy’s usage here is because it only works for data stored using the «MyISAM» and «Archive» storage engines.

Another limitation of this script is that it can only be run on the same machine that the database storage is kept. This prevents running backups from a remote machine, which can be a major limitation in some circumstances.

Copying Table Files

Another method sometimes suggested is simply copying the table files that MySQL stores its data in.

This approach suffers for one of the same reasons as «mysqlhotcopy».

While it is reasonable to use this technique with storage engines that store their data in files, InnoDB, the new default storage engine, cannot be backed up in this way.

Conclusion

There are many different methods of performing backups in MySQL. All have their benefits and weaknesses, but some are much easier to implement and more broadly useful than others.

The backup scheme you choose to deploy will depend heavily on your individual needs and resources, as well as your production environment. Whatever method you decide on, be sure to validate your backups and practice restoring the data, so that you can be sure that the process is functioning correctly.

By Justin Ellingwood

Conclusions

This tutorial only covers the basics, but should be a good start for anyone who wants to learn how to create and restore MySQL databases from the command line using the mysqldump utility.

You can also check out the guide on how to reset MySQL root password if you forgot it.