No database is safe without a backup, and thankfully MySQL and MariaDB have a utility called mysqldump built in to make this far easier. When a database is running and accessible, a web or server admin can use mysqldump to quickly create a logical backup.
These sets of SQL statements can be executed later to re-form the original databases’ table data and object definitions. You can also use them to transfer SQL databases to another SQL server, with the option to additionally output in XML or CSV format.
Today we’re going to show you how to backup MySQL or MariaDB, with the process identical for reach. First, though, you’ll need some pre-requisites, including the following:
- A Linux machine, preferably a Virtual Private Server (VPS)
- Your VPS login credentials and an account with root access to the system and sudo privileges.
- An existing MySQL or MariaDB installation
Once everything is in place, you’ll want to connect to your VPS.
How to back up a MySQL database with mysqldump
Before we start, it’s going to be beneficial to explain the mysqldump backup command a little and its syntax. A typical mysqldump command looks like this:
mysqldump -u username -p databasename > filename-$(date +%F).sql
mysqldump requires a username and password before it can start the backup process, hence the -u username -p arguments. Databasename is self-explanatory, while filename should be the intended name for the outgoing .sql file, and - $(date +%F) automatically appends the current date and time to the end of the file.
Bear in mind that the MySQL backup will be performed in the directory it is run, so you may want to navigate to or create a specific backup folder first. The backup may take some time depending on how large your database is.
You can additionally use the following command options to match your preferences:
--single-transactionto issue a single BEGIN SQL statement before backing up the data
--quickto dump database tables row by row, which increases stability on systems with low-RAM or a large database.
--lock-tables=falseensures the tables aren’t locked for the backup session
--all-databases will dump an entire database management system, like so:
mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p
For a specific database, you can run the following command, swapping database for your database name:
mysqldump -u username -p database --single-transaction --quick --lock-tables=false > database-backup-$(date +%F).sql
mysqldump also lets you back up or move an individual table, which can be useful in some circumstances:
mysqldump -u username -p --single-transaction --quick --lock-tables=false database table1 > database-table1-$(date +%F).sql
How to create an automatic backup of a MySQL database
You can perform a regular, automatic backups of your Maria or MySQL database through the use of a cron job file in
/etc/crontab. However, you may remember that mysqldump asks for the username and password before performing a job. You’ll have to create a file with the username and password to fully automate the process. On Ubuntu 20.04, you can type the following:
[client] user = root password = your MySQL root user’s password
Press Ctrl + O to write the changes, then Ctrl + X to exit. Now you must restrict access to the file. You don’t want anybody to be able to access your database’s username and password. Type:
chmod 600 /yourusername/mydetails.cnf
We can now create the cron job file in
0 1 * * * /usr/bin/mysqldump --defaults-extra-file=/yourusername/.mydetails.cnf -u root --single-transaction --quick --lock-tables=false --all-databases > full-backup-$(date +\%F).sql
Change yourusername to your account’s username, then press Ctrl+O and Ctrl+X. The backup will run every day at 1am.
How to restore a mysqldump backup
In the unfortunate event that you need your database backup, you can run a modified version of one of the following commands to restore mysqldump files. Just bear in mind that they’ll everything in the current database:
mysql -u root -p < full-backup.sqlto restore a full Database Management System backup
mysql -u username -p datbasename1 < databasename1-backup.sqlto restore one database dump. You must have an empty or old database to import the data into and write access to it.
mysql - u username -p databasename1 < databasename1-table1.sqlto restore a single table into an existing destination database.
Sign up for BitLaunch now and create a mysqldump backup in minutes.