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
|Category||Requirements, Conventions or Software Version Used|
|System||Any Linux distro|
|Software||MySQL Server or MariaDB|
|Other||Privileged access to your Linux system as root or via the
# – requires given linux commands to be executed with root privileges either directly as a root user or by use of
$ – requires given linux commands to be executed as a regular non-privileged user
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.
- 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
- 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
$ mysqldump -u root --password="mypassword" mydata > mydata-backup.sql
- 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
- to back up more than one database at once, you can list them separately in your command after the
--databasesoption. In this example, we will backup the databases
$ mysqldump -u root -p --databases mydata accounting > mydata-backup.sql
- You can also make a backup of every MySQL or MariaDB database at once by specifying the
$ 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.
- This command will restore our database data to our
mydatadatabase from previous examples.
$ mysql -u root -p mydata < mydata-backup.sql
- If your backup file contains multiple databases, you can select which ones to restore by using the
--one-databaseflag in your command.
$ mysql --one-database mydata < mydata-backup.sql
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.