How to backup a MySQL or MariaDB database with MySQL

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-transaction to issue a single BEGIN SQL statement before backing up the data
• --quick to dump database tables row by row, which increases stability on systems with low-RAM or a large database.
• --lock-tables=false ensures 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:

nano /yourusername/mydetails.cnf

[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 /etc/cron.daily/mysqldump:

nano /etc/cron.daily/mysqldump

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.sql to restore a full Database Management System backup
• mysql -u username -p datbasename1 < databasename1-backup.sql  to 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.sql to restore a single table into an existing destination database.

Sign up for BitLaunch now and create a mysqldump backup in minutes.