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
Backing up a MySQL database on Linux
Software Requirements and Linux Command Line Conventions
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

 

SUBSCRIBE TO NEWSLETTER
Subscribe to our NEWSLETTER and receive latest Linux news, jobs, career advice and tutorials.


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
    
  2. Backing up a MySQL database on Linux
    Backing up a MySQL database on Linux
  3. 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
    
  4. 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
    
  5.  


  6. 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
    
  7. 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.

FIND LATEST LINUX JOBS on LinuxCareers.com
Submit your RESUME, create a JOB ALERT.
LINUX CAREER NEWSLETTER
Subscribe to NEWSLETTER and receive latest news, jobs, career advice and tutorials.
DO YOU NEED ADDITIONAL HELP?
Get extra help by visiting our LINUX FORUM or simply use comments below.