Making Backups with mysqldump
One of the best utilities to use to make a backup copy of a server's MariaDB's data is mysqldump. It comes with MariaDB, so it costs you nothing more. Best of all it doesn't require you to shut down MariaDB services to make a backup. It works very simply: it retrieves the data and schema from each database and table and builds a data text file outside of MariaDB. This data text file (known as a dump file) will contain the SQL statements necessary to reconstruct the databases and data. If you were to open a dump file generated by mysqldump, you would see CREATE TABLE statements and a multitude of INSERT statements, one for each row of data.
Backing Up Everything
To export all of the databases in MariaDB using mysqldump, the following would be entered from the filesystem command-line:
mysqldump -u admin_backup -p -x -A > /data/backup/dbs.sql
The first set of options here (-u admin_backup -p) tell MariaDB that this utility is to be executed by the user admin_backup and that the user needs to be prompted for a password, which will have to be typed in on the next line when asked. Incidentally, although you might be tempted to just use the root user, you should create a special administrative user as we're using here. If the dump is to be executed by cron by way of a shell script, this option can be changed to -pmypwd, where mypwd is the password—there's no space between the -p and the password. The -x option has MariaDB lock all of the tables before performing the backup. The lock won't be released until the process is finished. To bundle INSERT statements together for each table, we've added the -e option. This extended insert option will cause the dump file to be smaller and allow any possible future restores to be executed faster. The -A option specifies that all databases are to be exported. Finally, the greater-than sign is a shell redirect of the standard output (STDOUT) to the path and file named after it.
The example given for backing up all database is the short hand version. The convention is migrating to longer options, not the single letter options. In fact, some are being deprecated and won't be available in the future. So, the above could and should be entered like this:
mysqldump --user=admin_backup --password --lock-tables --all-databases > /data/backup/dbs.sql
The longer option names are easier to follow and to remember. Again, if the backup is to be executed by a shell script, the user's password should be listed: --password=mypwd. Notice that the equal-sign is added when the password is given with the long option name.
Just One Database
Backing up all of the databases at once with mysqldump may result in one large dump file. This could be take longer to complete the backup and make restoration a bit cumbersome later. Therefore, it might be more useful to stagger backups based on databases, making for possbily several smaller files. You could then backup larger databases during slower traffic times. You might also backup critical databases or ones that are changed much during slower times of the day so that you don't dimish user interaction.
To export only one database and not all, enter something like the following from the command-line:
mysqldump --user=admin_backup --password --lock-tables --databases db1 > /data/backup/db1.sql
The only significant difference in this line is that the -A option has been replaced with -B and the database to be exported has been given. To export multiple databases, just enter them after the -B option, separated by spaces (e.g., -B db1 db2).
Dumping Tables
For very large databases, you may want to backup the data based on tables rather than the whole database. You could backup weekly an entire database and then only backup daily individual tables for which data changes often. To backup just one table, the following could be entered from the command line:
mysqldump --user=admin_backup --password --lock-tables db1 table1 > /data/backup/db1_table1.sql
First notice that the --databases option has not been included in the line above. The utility assumes that the first name given is a database and the second name is a table name and not another database. To backup multiple tables from a database, just list them after the database name, separated by spaces (e.g., db1 table1 table2).
Conclusion
As you can see from this article, mysqldump is easy to use and very powerful. In fact, it can clobber your data if you're not careful. Therefore, you should practice using it on a test database—a test server even—a few times until you're comfortable with making backups and restoring them. Don't wait until you've lost your data and in a panic to restore your data to find out that you haven't been backing up your data properly or that you don't know how to fine tune data restoration. Develop some skills in advance and in a safe and controled way. To learn how to restore dump files, see our article on Restoring Data.
