Backup and Restore a MariaDB/MySQL Database

It is critical to regularly take a backup of important data that is stored within a database so that the data can be restored in the event of any problem. Examples of such problems include database server failure, data tampering, corruption and any other number of file system specific problems that are looking to do your database harm.

Here we will cover how to backup and restore an SQL database from MariaDB/MySQL. Our test server is running CentOS 7 however these commands are MariaDB/MySQL specific so the operating system version should not matter.

Checkout our guide covering how to install and configure MariaDB here if you are just getting started.


Red Hat Certified Engineer RHCE Video Course
Studying for your RHCE certification? Checkout our RHCE video course over at Udemy which is 20% off when you use the code ROOTUSERS.


Backup a MariaDB/MySQL Database

The database backup can be performed while the server is online and the database is actively serving queries, there is no down time associated with this process. The backup process will essentially output a dump file containing the full database structure allowing the result to be imported and restored elsewhere.

To create this dump file we can use the ‘mysqldump’ command as shown below.

mysqldump -u root -p database-name > /root/database-backup.sql

To use mysqldump a user with the correct level of permissions (SELECT, SHOW VIEW, TRIGGERS) on the database needs to be specified, in this example we are using the root user as specified by the -u flag. The -p flag specifies the password, however as a best practice it is not recommended to supply the password in the command, as this will mean that anyone who is able to view the command history will be able to view the password in clear text. If you instead specify -p without the password you will be prompted to enter the password once you enter the command which is a much more secure option. The ‘database-name’ is the name of the database that we want to dump, we then save the output to the /root/database-backup.sql file.

Please note that the contents of the database-backup.sql file are in clear text and may contain sensitive data depending on what is stored within your particular database. The contents of the file will contain the SQL queries necessary to recreate the database.

You can optionally specify --all-databases instead of a database-name which will dump all databases rather than a specific one.

Restore a MariaDB/MySQL Database

The database restore can also be performed online. The database can be restored from our previously dumped out file, /root/database-backup.sql as it contains all SQL queries required to rebuild the entire 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 .sql file into the database ‘database-name’.

mysql -u root -p database-name < /root/database-backup.sql

Once this completes the database should be available in the running instance of MariaDB/MySQL, 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.

Summary

As shown with just one command you can take a full database dump which will act as a backup of a specified database, or even all databases that exist. The file that is created from this process contains clear text SQL queries that are capable of rebuilding the entire database. The exported file can then be imported back into the same MariaDB/MySQL instance, or even another on a completely different server.


This post is part of our Red Hat Certified Engineer (RHCE) exam study guide series. For more RHCE related posts and information check out our full RHCE study guide.

Leave a comment ?

12 Comments.

  1. ERROR 1227 (42000) at line 678: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

  2. When we restore the dump to another server the newly created database name should match?? if so can u explain what are the other method used to import with different database name..

    • Are you referring to the name of the file when you export it? I don’t think the name matters, as you specify the name of the database you’re importing into later.

  3. HI Jarrod,

    thanks for your reply.. i am referring the name of the database should be same while importing? if so can u explain me how to import to a database1 dump to database2.

    for example i have extracted the dump like below

    #mysql -uroot -p testmaria > e:\testmariabkup.sql

    now i want to import this testmaria to an another database by name bizopsmaria

    assume that i already created the database bizopsmaria

    • You’d do:

      mysql -u root -p bizopsmaria < e:\testmariabkup.sql

      This will import the previously exported database, testmaria, into the defined bizopsmaria database.

  4. Thank you so much Jarrod for your timely help its started working..

    Also i have few doubts on the export and import does this export and import does all the functions, triggers, sequence & procedures created in maria db?

    • It should export everything that’s stored within the database itself, not actually sure specifically where those things live, but if it’s in the db itself then it should be there.

  5. ya thanks jarrod. i have an another security related to the backup since the backup can be restored to any of the new database name then there is a big questions about the security of the backup.

    for example. if somebody stolen my backup file and he can easily import the same to his system or server database of mariadb and start working on that?– pls clarify..

    • Yes pretty much, you can look at the exported .sql file in a text editor to get an idea of what it contains, it should just be a bunch of SQL queries that will rebuild the database, so if there’s anything sensitive in there like password hashes then you want to also ensure any database dumps are secured and not available to unauthorized users.

  6. thanks jarrod for your prompt reply..

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>