Database Reference
In-Depth Information
29
In this chapter, you learn how to perform common database maintenance tasks.
Like all data, MariaDB data must be backed up regularly. As MariaDB data-
bases are disk-based files, normal backup systems and routines can back up
MariaDB data. However, as those files are always open and in use, normal file
copy backup may not always work as is not recommended.
Here are possible solutions to this problem:
Use the command line
mysqldump
utility to dump all database contents
to an external file. This utility should ideally be run before regular
backups occur so the dumped file will be backed up properly.
■
The command line
mysqlhotcopy
utility can be used to copy all data
from a database (this one is not supported by all database engines).
■
You can also use MariaDB to dump all data to an external file using
BACKUP TABLE
or
SELECT INTO OUTFILE
. Both statements take the
name of a system file to be created, and that file must not already exist
or an error will be generated. Data can be restored using
RESTORE
TABLE
.
■
Tip
Flush Unwritten Data First To ensure that all data is written to disk (including any
index data) you might need to use a
FLUSH TABLES
statement before performing
your backup.
MariaDB features a series of statements that can (and should) be used to ensure
that databases are correct and functioning properly.
