Linux commands to back up and restore MySQL database

It’s always a good idea to take frequent backups of your MySQL or MariaDB databases. They can potentially contain thousands of lines of irreplaceable data. Many users may be confused on how to back up their databases at first, as the process differs quite a bit from backing up ordinary files. The process of restoring a backup must also be known, as there’s no point in having a backup if the user cannot reliably restore it.

In this guide, we’ll go over various command line examples to back up and restore MySQL or MariaDB databases on a Linux system. You can then use these commands to make regular backups of your databases, or even add them to a Bash script that can do most of the work for you. Another option is to configure cron to make regularly scheduled backups of your databases.

In this tutorial you will learn:

• How to back up MySQL or MariaDB database (one or multiple)
• How to restore a MySQL or MariaDB database backup

Backing up a MySQL database on Linux

How to back up MySQL or MariaDB database

The following commands will work whether you are running MySQL Server or MariaDB. In our examples, we are backing up a database called mydata with our MySQL root account. You will need to substitute your own values accordingly and remember that the user you choose must have permissions on the database. Or you could always use the MySQL root account like we are in these examples.

1. to back up the database to a file called mydata-backup.sql, use the following command syntax. This will prompt you for a password when entering the command.

   $ mysqldump -u root -p mydata > mydata-backup.sql
   



Backing up a MySQL database on Linux

2. If you need to avoid the password prompt, such as the case in a Bash script, then you can put the password into the command itself by using the --password option.

   $ mysqldump -u root --password="mypassword" mydata > mydata-backup.sql
   

3. to back up the database to some other location than your present working directory, just put the path in your command.

   $ mysqldump -u root -p mydata > /home/linuxconfig/mysql/mydata-backup.sql
   

 

---

---

4. to back up more than one database at once, you can list them separately in your command after the --databases option. In this example, we will backup the databases mydata and accounting.

   $ mysqldump -u root -p --databases mydata accounting > mydata-backup.sql
   

5. You can also make a backup of every MySQL or MariaDB database at once by specifying the --all-databases option.

   $ mysqldump -u root -p --all-databases > mydata-backup.sql
   

For backing up databases, that’s all there is to it. Feel free to adapt these commands as needed, or turn them into a Bash script or insert them into cron for automated backups.

How to restore a MySQL or MariaDB database backup

Your MySQL/MariaDB database backup is stored as a .sql file. Have this file handy and you can use the following command examples to restore a backup.

1. This command will restore our database data to our mydata database from previous examples.

   $ mysql -u root -p mydata < mydata-backup.sql
   

2. If your backup file contains multiple databases, you can select which ones to restore by using the --one-database flag in your command.

   $ mysql --one-database mydata < mydata-backup.sql
   

Closing Thoughts

In this guide, we learned Linux commands to back up and restore MySQL or MariaDB databases. This included the backing up of multiple databases at once, or just a single database. You can use these commands to keep your MySQL data safe, and script regularly scheduled backups so you don’t always have to remember to run the commands.