MySQL, MariaDB Backup and Restore Step by Step

MySQL and MariaDB include a built-in backup utility that simplifies the process to create a backup of a database. With mysqldump, you can create a logical backup, only if your database is accessible and running.

This guide will show you how to create a backup of MySQL and MariaDB database using mysqldump utility.

Prerequisites

This tutorial assumes that you have a working MySQL or MariaDB installation, including a non-root user with sudo privileges.

Manual Database Backup

Login into your database server, type below command to create a backup of an entire Database Management System (DBMS):

sudo mysqldump --all-databases --single-transaction --quick --lock-tables=false > full_backup_$(date +%F).sql -u root -p

You can create a specific database backup with below command:

sudo mysqldump -u dbadmin -p exampleDB --single-transaction --quick --lock-tables=false > exampleDB_backup_$(date +%F).sql

Make sure you replace username with a user that has access to the database and exampleDB with the name of the database you intend to back up:

You can even create a single table backup from any database:

sudo mysqldump -u dbadmin -p --single-transaction --quick --lock-tables=false exampleDB table_name > exampleDB_table_name_$(date +%F).sql

Automate Database Backup

This section will show you how to schedule a backup task using cronjob to regularly create database backups.

Create a file .mylogin.cnf under your user's home directory to hold the login credentials of the MySQL or MariaDB root user. Note that the system user whose home directory this file is stored in can be unrelated to any MySQL users.

sudo nano /home/your_system_user/.mylogin.cnf

Add the MySQL or MariaDB database root credentials, make sure you replace the password field with your database root user's password:

[client]
user = root
password = Type MySQL root user's password here

Save and close the editor.

Make .mylogin.cnf file read-only with below command:

sudo chmod 600 /home/your_system_user/.mylogin.cnf

Next, create the cron job file to back up the entire database management system every day at 2:00AM:

sudo nano /etc/cron.daily/mysqldump

Add the following line, make sure you replace highlighted text with yours:

0 2 * * * /usr/bin/mysqldump --defaults-extra-file=/home/your_system_user/.mylogin.cnf -u root --single-transaction --quick --lock-tables=false --all-databases > full_backup_$(date +\%F).sql

Save and close the editor.

Restore Database Backup

This section demonstrates, how to restore an entire database management system (DBMS) backup.

Type below command to restore entire DBMS from the backup:

sudo mysql -u root -p < full_backup.sql

This will prompt you for the MySQL root user’s password:

To restore a single database dump, make sure an empty or old destination database must already exist to import the data into it, and the MySQL user you’re running the command must have write access to that database:

sudo mysql -u dbadmin -p exampleDB < exampleDB_table_name.sql

Wrapping up

These are just a few examples to create MySQL or MariaDB database backups. If you wish, go through the following official documentation for additional information on this topic.

• MySQL Database Backup Methods page
• mysqldump - A Database Backup Program, MySQL Reference Manual