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.


mysqldump -u [username] -p [databasename] > [filename]-$(date +%F).sql

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.


mysqldump -u username -p database-name --single-transaction --quick --lock-tables=false > backup-filename-$(date +%F).sql

Example:


mysqldump -u root -p softaox --single-transaction --quick --lock-tables=false > backup-softaox-$(date +%F).sql

Single Database Table Backup with Timestamp

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


mysqldump -u username -p --single-transaction --quick --lock-tables=false database-name table-name > backup-filename-$(date +%F).sql

Example:


mysqldump -u root -p --single-transaction --quick --lock-tables=false softaox wp_function > softaox-wp-function-$(date +%F).sql

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.


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

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.


mysql -u [username] -p [databasename] < [filename].sql

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.


mysql -u username -p database-name < backup_filename.sql

Example:


mysql -u root -p softaox < backup-softaox.sql

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.


mysql -u table-name -p database-name < back_filename.sql

Example:


mysql -u wp_function -p softaox < softaox-wp-function.sql

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.


mysql -u root -p < complete_backup.sql

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.


sudo nano ~/.secure.cnf

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


[client]
user = Database User
password = Database User Password

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.


chmod 600 ~/.secure.cnf

Create a new directory named db_backup to store the backups files


mkdir ~/db_backups

Open your user crontab file


crontab -e

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


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

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

Was this article helpful?

Yes No
×

How can we improve it?

×

We appreciate your helpul feedback!

Your answer will be used to improve our content. The more feedback you give us, the better our pages can be.

Follow us on social media:

Facebook Pinterest
Share via:

Mraj

Creative Designer & Developer specialist by the spirit and a loving blogger by thoughts.

Leave a Reply