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 sudo command. |
Conventions |
# – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command$ – 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
--password
option.$ 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
--databases
option. In this example, we will backup the databasesmydata
andaccounting
.$ 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
--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.
- This command will restore our database data to our
mydata
database 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-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.