Back Up and Restore MySQL or MariaDB Using Mysqldump

In this article, We going to see how to create a backup and restore file for MySQL or MariaDB using mysqldump. One of the main advantage in MySQL and MariaDB it includes the mysqldump feature by default. This helps to simplify the process to create a backup the file of a database. To create a local or remote backup you need to use mysqldump to take a backup in the single flat file.

The following commands apply for both MySQL and MariaDB.

Note: You need to run the command as root or via the sudo.

Backup a MySQL or MariaDB Database

Use the following general mysqldump syntax to download the database.

Single Database Backup with Timestamp

The mysqldump will help to create a single database backup. For example, to create a backup of the database named softaox_1 using the root access and save it to a file named backup_softaox_1.sql with the timestamp. Use the following command to create a backup.

Example:

Single Database Table Backup with Timestamp

To take a single table backup form any database using the following command.

Example:

Here database named as softaox and table named wp_function.

Create a Complete Database Backup with Timestamp

Use the --all-databases option to back up all the databases with Timestamp.

Before dumping data from the server, you need to use --single-transaction.

The --quick will reduce the system RAM and bigger databases by dumping tables row by row.

While taking backup you need to use --lock-tables=false this helps to avoid from lock tables for backup.

Restore a MySQL or MariaDB Backup

Use the following general syntax to restore the database.

Restore a Single Database

Using dump you can restore a single database. To restore the backup you need to create database name if you do not have in the same database name in MySQL. The below command will help you to import a single database.

Example:

Restore a Single Table

Before restoring a single table first you want to know the destination database using the database name you need to restore the database table.

Example:

Restore an Entire Database Backup

To restore an entire database backup, for that you need to import database as root user.

Note: Using below command it overwrites existing data in the MySQL database.

Automate Backups with Cron

If you like to create an automatic database backup process for your database it is simple and easy to configure. Using cron job you can create a backup for a particular time would you like.

To create an automatic backup for your MySQL database using cronjob for that you need to follow below commands.

All the records can be added in the particular path /etc/crontab to frequently schedule database backups.

First, you need to create a new file named .secure.cnf in your home directory for holding the login credentials of the MySQL root user. This only help to deliver the backup files.

Open the .secure.cnf file from your home directory.

Add the below code in your created .secure.cnf file.

Here you need to replace your database user and password.

Restrict Permission

For secure your .secure.cnf file for that, you need to restrict permissions for the file. So this helps to secure your file from accessing unauthorized users. Use the following command to restrict permission.

Create a new directory named db_backup to store the backups files

Open your user crontab file

Add the following command in your cron job that will create an automatic backup of database name softaox every day at 2 am.

Do not forget to replace username with your current user name.

awsBackupDatabaseLinux CommandsMariaDB