By default, the mysqldump client backup utility generates SQL backups. These backups are in a text format and contain all the necessary SQL commands to recreate tables and restore the data in those tables.

There are many options, but the basic syntax is as follows:

mysqldump [-u username] [-p] database_name [table_name]

If table_name is not given, mysqldump will back up all the tables in the named database. For example, the following command will back up the entire test database:

mysqldump -u root -p test > test.sql

The output of mysqldump goes to standard out. When running the command from a terminal, it will be echoed directly to the screen. So in the preceding example command, we use the > redirect character to direct the output into a file named test.sql (overwriting the file if it already exists).

To restore the preceding backup, we can use the mysql command-line client as follows:

mysql -u root -p test < test.sql

As with the mysqldump example, we use a redirect character, but this time it is redirecting in the opposite direction (<), that is, from the test.sql file to the mysql client. The mysql client reads the file and executes all of the SQL commands in turn, restoring the backed up tables and their data.

We can also use mysqldump to create tab-delimited files. This is done using the --tab option. When using this option, mysqldump will create two files. A tablename.sql file with the SQL commands to recreate the table, and a tablename.txt file with the actual data in tab-delimited format. The following is an example of using mysqldump and --tab to backup up the employees table in our test database:

mysqldump --tab /tmp/ -u root -p test employees

The --tab option needs a directory after it where it can write the files. The SQL file is owned by whichever user we used to run the mysqldump command. The TXT file, on the other hand, is owned by the mysql user, so whatever directory we specify needs to have permissions so that both users can write to it. The /tmp/ directory in Linux is used in the example because, by default, this directory can be written to by anyone.

So why would a tab-delimited file of our data be useful? Well, for starters, the mysqlimport program reads tab-delimited files. Popular spreadsheets also read and write to tab-delimited files. So, for example, if we've been keeping our data in a spreadsheet, and have decided to move it to a MariaDB database, we can export our spreadsheet data as a tab-delimited file, create the tables in MariaDB, and then use mysqlimport to import our data. At a later point, we could use mysqldump to dump the data or a subset of the data to a file and then open it with our spreadsheet program, and create some nice pie charts or other graphs.

There are scores of other options that we can use to tweak and customize what and how mysqldump backs up our data. It's well worth your time to learn all of these many options.

We talked briefly about mysqlimport in the previous section. In short, this command is used to import data into MariaDB. This data could be a backup that we made previously or completely new data. There are several options for this, but the basic syntax is as follows:

mysqlimport [--local] [-u username] [-p] database_name filename

The filename attribute must be the name of the table we want to import into. The --local option tells mysqlimport to read from the local filesystem instead of from the data directory of the server.

The following example imports the employees.txt file that we generated earlier:

mysqlimport --local -u root -p test /tmp/employees.txt

Any records that cannot be imported will be skipped, and mysqlimport will report this and generate a warning. An example would be a situation where our file has a column in it that contains values that must be unique in our table, but some of them match existing records in the table.

The mysqlhotcopy backup program is actually a Perl script. It can take backups quickly, but only if our tables use the MyISAM or ARCHIVE storage engines.

An easy way to show the storage engines being used by the tables in our database is with the following SELECT statement:

SELECT TABLE_NAME,ENGINE
   FROM information_schema.tables
   WHERE TABLE_SCHEMA="test";

We can change test to the name of whichever database we want to check.

The default storage engine for MariaDB is InnoDB, so this script is less useful than it used to be several years ago when MyISAM was the default storage engine. If we do have MyISAM tables, however, it remains a useful tool.

The basic syntax of the mysqlhotcopy command is as follows:

mysqlhotcopy db_name [/path/to/new_directory]

If the path to a new directory is not given, mysqlhotcopy will write the backup to the MariaDB data directory. Writing a backup to the data directory is not recommended, so be sure to always specify a path.

Other limitations are that the command must be run by a user who can read the data files in the data directory, and if we use a password when connecting to MariaDB, we must specify it on the command line or in a my.cnf file as mysqlhotcopy does not prompt us for the password.

The xtrabackup backup program is made specifically for use with XtraDB and InnoDB tables. It can take quick, full backups of our databases while MariaDB is running.

Creating a backup with xtrabackup is a multi-step process. First we take a backup and then we prepare the backup so that it is ready to be restored when necessary. To take a backup, we do the following:

xtrabackup --backup 
  --datadir=/var/lib/mysql/ --target-dir=/path/to/backup/

The --datadir option should point at the location of our MariaDB data files; on Linux the default data directory location is /var/lib/mysql/. On Linux, we will also need to preface the command with sudo.

InnoDB and XtraDB tables are stored across several files, and backups made with xtrabackup are the same. This is why while taking a backup with xtrabackup, we specify a directory and not a file name with the --target-dir option.

While a backup is being made, xtrabackup will print various bits of information to let us know how the backup is progressing. Backups may take a long time if we have a lot of data and/or if our server is very busy.

After making a raw backup, we need to prepare the backup so that it can be restored if necessary. The reason we need to do this is because of the way that xtrabackup and InnoDB and XtraDB tables work. If we try to restore using a raw backup that hasn't been prepared, it is very likely that MariaDB will refuse to start.

To prepare the backup we just made so that it is ready for restoring, we run the following command twice:

xtrabackup --prepare --target-dir=/path/to/backup/

The first time that we run xtrabackup with the --prepare option, our backed up data will be cleaned up and put into a logical order. The second time that the --prepare option is used, xtrabackup will create some log files that help speed up restoring our data, if it turns out that we need to do that. Running --prepare a third, fourth, or any more number of times won't do anything, but is safe to do so in case we can't remember if we've run it for the second time.

The easiest way to restore from a backup made with xtrabackup is to use a utility, such as rsync or the cp command, to copy all the files in the backup directory to our MariaDB data directory. Before doing so, we must stop MariaDB and then run the rsync or cp command. Here's an example rsync command:

rsync -avP /path/to/backup/ /var/lib/mysql/

After the files are copied back to the MariaDB data directory, and before we start MariaDB, it's a good idea to make sure that the ownership of the files is correct. By default in most Linux distributions, the default user and group are called mysql, so this can be done with something similar to the following:

chown -R mysql:mysql /var/lib/mysql/

Another option for backing up MariaDB is to just copy the entire data directory. This is called a cold backup. As mentioned at the beginning of this section, problems can arise if we try to do this while MariaDB is running. But if we stop MariaDB briefly, and are using a filesystem that supports snapshots (called shadow volume copies on Windows), we can stop MariaDB briefly, make a snapshot, and then restart MariaDB. Total downtime for an operation such as this, depending on various factors, might be only a few seconds. The snapshotted directory may then be backed up in a simialr way to any other filesystem directory backup.

This is obviously not an ideal way to take backups in all situations, especially when stopping the database server, even for a few seconds, is not an option. But it can work very well in some cases.