How To Backup And Restore MySQL/MariaDB Database In A Proper Way

How To Backup And Restore MySQL/MariaDB Database In A Proper Way

This article shows the proper way how to backup and restore MySQL/MariaDB database from command line in Linux.

If you are using MySQL or MariaDB Databases it is very important for you to understand how to take backup and restore data. In addition, you need to keep regular backups of the database and this will help you to restore your data if your application crashes or the data is corrupted by any system failure.

MySQL and MariaDB include command-line tools that you can use to quickly and easily backup and restore databases. The mysqldump tool is used to export the contents of a database to a text file, while the mysql client can be used to import data from a text file into a MySQL/MariaDB database.

Create MySQL/MariaDB backups using mysqldump command

The mysqldump command line utility exports databases to SQL text files. In other words, it is used for taking the MySQL/MariaDB backup.

The syntax for mysqldump command is given below:

mysqldump -u [username] -p [database_name] > [filename].sql

Where:

  • username is your MySQL/MariaDB username.
  • -p prompt for password for this user.
  • database_name is the name of the database you want to backup.
  • filename.sql is the full path to the sql dump file that will contain the backup.

Database procedures, functions, views and events are dumped at the time of backup.

Backup a single MySQL/MariaDB database

In this example, the database is named my_wordpress. Replace this with the name of the database you wish to export. According to the syntax, the backup file will be created at the current location.

mysqldump -u root -p my_wordpress > my_wordpress_backup.sql

After the command is run, enter your MySQL/MariaDB root password.

This creates a file named my_wordpress_backup.sql that contains all of the SQL statements to create tables and restore data into an existing database. If any errors occur during the export process, mysqldump will print them to the screen.

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

head -n 5 my_wordpress_backup.sql
-- MariaDB dump 10.19  Distrib 10.5.9-MariaDB, for Linux (x86_64)
-- Host: localhost    Database: my_wordpress
 
-- Server version    10.3.27-MariaDB-0+deb10u1

Backup multiple MySQL/MariaDB databases

To backup multiple MySQL databases with one command you need to use the --databases option followed by the list of databases you want to backup. In addition, you can put the result file in a location of your choice.

Run the following command to create backup file my_backup.sql in /home/backups directory of two databases named my_wordpress and ftpusers.

mysqldump -u root -p --databases my_wordpress ftpusers > /home/backups/my_backup.sql 

Backup all MySQL/MariaDB databases

Use the --all-databases option to back up all the MySQL/MariaDB databases:

mysqldump -u root -p --all-databases > all_db_backup.sql

Create a compressed MySQL/MariaDB database backup

If the database size is very large it is a good idea to compress the output. To do that simply pipe the output to the gzip command, and redirect it to a file as shown below:

mysqldump -u root -p my_wordpress | gzip > my_wordpress_backup.sql.gz

Restore MySQL/MariaDB from a mysqldump backup using mysql command

To restore a backup created with mysqldump, you will need to use the mysql command.

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

Where

  • username is your MySQL/MariaDB username.
  • -p prompt for password for this user.
  • database_name is the name of the database in which you want to restore.
  • filename.sql is the full path to the sql dump file that contain the backup.

Restore single MySQL/MariaDB database

You can restore all tables from the backup file of any particular database.

The restore is simply performed with the below command, where we are again specifying a user and to be prompted for a password, and passing the my_wordpress_backup.sql file into the database my_wordpress.

mysql -u root -p my_wordpress < my_wordpress_backup.sql

You will be prompted for the password of your MySQL/MariaDB user.

Once this completes the database should be available in the running instance of MySQL/MariaDB. The file that you imported from will also still exist, so you can either store that securely at this point or delete it if it is no longer required.

Restore all MySQL/MariaDB databases

You can restore all databases of any MySQL or MariaDB server to another MySQL or MariaDB server from backup file.

mysql -u root -p < all_db_backup.sql

Conclusion

Now you can keep regular backup of your database files and restore the data when require by following above steps.

This tutorial also applies when you wish to migrate to a new server. Simply back up the database on your old server, and upload the SQL dump file to the new server. Create a destination database on the new server, then execute the uploaded dump file in the new database. In addition to, you should also recreate users and permissions from the old MySQL/MariaDB server to the new one.

If you have any questions or feedback, feel free to leave a comment.

Leave a Reply

Your email address will not be published.