Backup and Restore MySQL/MariaDB Databases

In this article we will show you the  first and most important task every database administrator / sysadmins must master “Backup and Restore your databases”. We divided this article into two parts. In part 1, We will show you how to perform backup for both MySQL and MariaDB databases for a single database, several databases, and all the databases. We also will compress your backups and only backup certain table/tables from a database. In part 2, We will show you how to restore the different types of backups for both MySQL and MariaDB databases. We gives you some examples in each part of this article.

If you’re storing anything in MySQL/MariaDB databases that you do not want to lose, it is very important to make regular backups of your data to protect it from loss.

For performing a database’s backup, we’ll use mysqldump – a database backup program. And for performing a database’s restore, we’ll use mysql  – the MySQL command-line tool.

Introduction

The mysqldump can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format. Using mysqldump, you can backup either a local database or a remote database.

Hint:

1. If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores.

The mysql is used to restore a database backup of all types. It supports interactive and non-interactive use.

Part 1: Performing a MySQL/MariaDB database backup.

There are three general ways to invoke mysqldump:

$ mysqldump [options] db_name [tbl_name ...]
$ mysqldump [options] --databases db_name ...
$ mysqldump [options] --all-databases

If you do not name any tables following db_name or if you use the ‐‐databases or ‐‐all-databases option, entire databases are dumped.

Here is the above syntax with all options you may need:

$ mysqldump -u[uname] -p[pass] [dbname] > [backupfile.sql]

• [uname] Your database user-name
• [pass] The password for your database (note there is no space between -p and the password)
• [dbname] The name of your database
• [backupfile.sql] The file-name for your database backup

Hints:

1. If you are backing-up a remote database, add [-h Remote_server_IP] to the above option.
2. For backing-up a remote database, you must allow the database's user to connected remotely "which is a security thread".
3. I STRONGLY NOT RECOMMEND ALLOWING THE DATABASE'S USERS TO CONNECT REMOTELY.

Before starting with performing different types of backups, here’s all my database I’ll work on, I ran the following command to connect to your database server:

$ mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Then show all your databases with the following command:

MariaDB [(none)]> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| tutorials |
| articles |
| mimastech_db |
| mysql |
| performance_schema |
+--------------------+
6 rows in set (0.50 sec)

These are all my database I’ll work on, so let’s start.

• Backup a single database

To backup a single database “mimastech_db” and dumps the output to mimastech_db.sql

$ mysqldump -uroot -ptmppassword mimastech_db > mimastech_db.sql

The mimastech_db.sql will contain drop table, create table and insert command for all the tables in the mimastech_db database. You can go and check the dump file yourself by simply running this command:

$ more mimastech_db.sql

• Backup multiple databases

To backup multiple databases, we’ll use ‐‐databases option and specify the databases we want to backup. Here’s I’ll backup both tutorials and articles databases and dump them into one file tutorials_articles.sql, execute the mysqldump as shown below:

$ mysqldump -uroot -ptmppassword --databases tutorials articles > tutorials_articles.sql

Verify the tutorials_articles.sql dump-file contains both the database backup.

$ grep -i "Current database:" tutorials_articles.sql
-- Current Database: `tutorials`
-- Current Database: `articles`

• Backup all the databases

The following example takes a backup of  all the database of the MySQL instance. This will backup all databases you created and all databases MySQL/MariaDB instance are using “information_schema, mysql, and performance_schema”. This type of backup will restore your MySQL/MariaDB instance with All customization you made for users privileges, access, etc..

$ mysqldump -uroot -ptmppassword --all-databases > all-databases.sql

• Backup a specific table from a database
  

In this example, we backup only the mysql_tutorials table from tutorials database.

$ mysqldump -uroot -ptmppassword tutorials mysql_tutorials > tutorials-mysql_tutorials.sql

• Back up MySQL/MariaDB Database with Compress

If your MySQL/MariaDB database is very big, you might want to compress the output of mysqldump. Just use the mysqldump backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u[uname] -p[pass] [dbname] | gzip > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Now, before moving to the database restore part, the mysqldump command has also some other useful options and group of options. Here I’ll list some of them you may use in your backups:

Some useful mysqldump options:

• ‐‐opt is a group option, which is Same as ‐‐add-drop-table, ‐‐add-locks, ‐‐create-options, ‐‐quick, ‐‐extended-insert, ‐‐lock-tables, ‐‐set-charset, and ‐‐disable-keys. Enabled by default, disable with ‐‐skip-opt.
• ‐‐compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options    ‐‐skip-add-drop-table ‐‐skip-add-locks ‐‐skip-comments ‐‐skip-disable-keys    ‐‐skip-set-charset.
• ‐‐add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.
• ‐‐no-data or -d : Dumps only the database structure, not the contents.
• ‐‐add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

Hints:

1. For all mysqldump options see the man pages or run on your terminal "mysqldump --help".
2. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you.
3. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.
  

Part 2: Performing a MySQL/MariaDB database restore.

Above we backed up the mimastech_db database into mimastech_db.sql file. To re-create the mimastech_db database you should follow two steps:

• Create an appropriately named database on the target machine.
• Load the file using the mysql command:

$ mysql -u[uname] -p[pass] [db_to_restore] < [backupfile.sql]

Here, we’ll create the database first, run the following commands:

$ mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database mimastech_db;
Query OK, 1 row affected (0.07 sec)

MariaDB [(none)]>

Now, we can restore the mimastech_db.sql file to the mimastech_db database.

$ mysql -uroot -ptmppassword  mimastech_db < mimastech_db.sql

To restore compressed backup files you can do the following:

$ gunzip < [backupfile.sql.gz] | mysql -u[uname] -p[pass] [dbname]

Or simply, unzip it first then use mysql to restore it as in the above example.

Summary

In this article we have explained the first and most important task every database administrator / sysadmins must master “Backup and Restore your MySQL/MariaDB databases”. Backup and restore process is too important in the IT world. It keeps you and your business save from any disaster that might happen. We showed you how to backup and restore a single database, multiple databases, and all existing databases. We showed you how to compress your backups.

I hope this article is good enough for you.
See you in other articles.