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