A physical backup is a copy of all the files in which MariaDB stores the database definitions, the data and index files, the configuration files, and the logs. Since data is usually stored in a compact way, a physical backup is usually the most convenient form of backup. Also, only physical backups include the configuration files and the logs.
However, while performing a physical backup, a lock must be acquired so that the server does not use the files. As an alternative, we can stop the server.
A complete backup consists of all the following groups of files: table files, trigger files, logs, and configuration files.
Table files are stored in the data
directory. The storage engine, and sometimes its configuration, determines which files contain the table data and indexes. The data
directory contains a directory for each database. The name of the directory matches the database name, as long as no special characters are used (which is not a good practice, anyway). The table files are stored in the proper database directories. They have a base name, the same as the table name, as long as no special characters are used. They also have an extension that depends on the file type.
If a table is partitioned, of course, it consists of several data and index files. The names of these files follow this pattern:
table_name#P#partition_name.file_extension
A .par
file is also used to store partitions metadata.
The data
directory's path is defined in the @@datadir
server variable.
The InnoDB system tablespace can be located in a different path specified in the @@innodb_data_home_dir
system variable. Other tablespaces are located in the path specified in the @@innodb_data_file_path
system variable. This can be a relative path starting from the @@innodb_data_home_dir
path.
Some storage engines (including recent versions of InnoDB) allow using a different path for table files. This path is defined using the DATA_DIRECTORY
and INDEX_DIRECTORY
table options. The value of these options can be seen, for example, with a SHOW CREATE TABLE
statement.
Knowing this, we are able to selectively back up only some tables or even only some partitions.
The server creates a .frm
file that contains the definition of the table. Some storage engines are able to work if this file is not found for some reason, but the file should always exist regardless of which storage engine is used for the table.
InnoDB has a file-per-table mode that affects the creation of table files. The details have been explained in Chapter 7, InnoDB Compressed Tables. A system tablespace always exists and is stored in the files whose names start with ibdata
. When a table is created and the file-per-table mode is enabled, for each new table, an .ibd
file exists that contains both data and indexes. When the file-per-table mode is not enabled, new tables are created in the system tablespace.
Many storage engines use separate files for indexes and data. The following table shows their extensions:
Storage engine |
Data file |
Index file |
---|---|---|
|
|
|
|
|
|
|
| |
|
|
|
|
|
The MERGE
storage engine does not create data or index files, but it uses a MRG
file that contains the list of the underlying MyISAM tables. Aria also uses logs, whose base name is aria_log
, and whose extensions are progressive numbers. A filename aria_log_control
is also necessary. The ARCHIVE
storage engine has a very limited index support, while CSV
does not support indexes at all; thus, these engines do not use index files. For the CONNECT
engine, the SEP_INDEX
table option allows us to store each index in a separate file. In this case, their names will be in the following format: tablename_indexname.dnx
. The index name for the primary key is PRIMARY
.
For example, a MyISAM table called myisam1
with three partitions called p0
, p1
, and p2
, will use the following files:
myisam1.frm myisam1.par myisam1#P#p0.MYD myisam1#P#p0.MYI myisam1#P#p1.MYD myisam1#P#p1.MYI myisam1#P#p2.MYD myisam1#P#p2.MYIBackuping stored programs
Stored routines, triggers, and events are collectively called stored programs. They are meant to implement the logic of the database in simple SQL scripts. These objects are not likely to change often, just like table structures. However, a backup of these programs is still necessary to restore the correct behavior of the server. Their definition is stored in system tables, contained in the mysql
database. A backup of this database contains all existing stored programs.
However, for each trigger, the following files are created in the data
directory:
trigger_name.TRG
trigger_name.TRN
To correctly obtain a backup of triggers, it is necessary to include these files.
The server's log paths and filenames are defined in some server variables. This has been discussed in Chapter 2, Debugging, and Chapter 3, Optimizing Queries. However, a summary table with the server logs and the variables that control their path is probably useful and is shown as follows:
Log |
Server variable |
---|---|
Error log |
|
General query log |
|
Slow query log |
|
Binary log |
|
Keeping the logfiles in the data
directory should simplify the backup procedure.
If there is only one MariaDB version on the machine, only one instance is executed. It is always executed using the same system user, and only one configuration file is used. It is generally located in the MariaDB installation directory and called my.cnf
. On Windows, my.ini
is also a valid name.
If several MariaDB versions exist on the same machine, or if more than one instance can be executed at the same time, the user can take advantage of the MariaDB modular configuration, with some configuration files containing general settings and more specific files that override certain settings for one or more instances. This usually happens on machines used to test; however, a DBA should be aware that, on any machine, multiple configuration files could exist.
On Linux systems, configuration files can be placed in any of the following paths:
/etc
path/etc/mysql
pathSYSCONFDIR
path$MYSQL_HOME
path--defaults-extra-file
option~/
pathOn Windows systems, the paths are different:
%PROGRAMDATA%MariaDBMariaDB Server 10.0
path%WINDIR%
pathC:
path--defaults-extra-file
optionWhen the server is stopped, copying the files is easy. But when the server is running, we have a problem: we must be sure that the server does not try to modify the files until the backup process is finished.
To do this, we flush the last changes to disks and lock the tables. This can be done with the FLUSH TABLES … FOR EXPORT
statement or with the FLUSH TABLES … WITH READ LOCK
statement.
Their syntaxes are:
FLUSH TABLES <table_list> FOR EXPORT FLUSH TABLES [table_list] WITH READ LOCK
The table list is mandatory with FOR EXPORT
but is optional with WITH READ LOCK
. If omitted, FLUSH TABLES WITH READ LOCK
locks all tables. This is called a global read lock. A table-shared lock is acquired on all named tables.
The most convenient procedure is as follows:
FLUSH TABLES … FOR EXPORT
or FLUSH TABLES … WITH READ LOCK
and leave the client open.UNLOCK TABLES
to release the lock.The FLUSH TABLES … FOR EXPORT
locks the tables and asks the storage engines to flush all changes to disks. This is the only safe way to back up InnoDB tables on a running server. However, the FOR EXPORT
clause is not available on MariaDB versions older than 10.0. Also, some storage engines may not support it.
With FLUSH TABLES … WITH READ
, the flush is done by the server. This means that it works even with storage engines that do not support this statement. However, as mentioned earlier, this method is not safe to back up InnoDB tables on a running server.
For most storage engines, there is no practical difference between these two statements. But, since InnoDB backups require FOR EXPORT
, this command is more convenient.
Some filesystems or volume managers support snapshots. For example, the Veritas filesystem support them; other filesystems, like XFS, can create snapshots via a volume manager like LVM. Snapshots are a very fast way to take physical backups.
Stopping the server is not usually required to create a snapshot. Instead, it is necessary to acquire a global read lock with FLUSH TABLES … WITH READ LOCK
. The procedure is as follows:
FLUSH TABLES … WITH READ LOCK
and leave the client open.mount vxfs snapshot
.UNLOCK TABLES
to release the lock.The mylvmbackup
utility, included in most Linux distribution repositories, automates this procedure.
The
rsync
command is a Linux command that copies files in an incremental way. When it is invoked on a file that it has never copied before, it copies it. But when it is called again on that file, it checks whether the file has been modified since the time of the last copy. If so, rsync
copies the modified part of the file, which makes it very fast to copy these backups over a network. When called on a directory, rsync
performs this check for each individual file contained in the directory. The rsync
command can also delete a file from the target directory if it has been deleted from the source directory. However, for backups this is not a good idea: if a file is missing, maybe we will need to restore it.
The rsync
command is usually not helpful for OLTP databases. However, OLAP databases typically contain very large tables that are not often updated. When performing a backup of those databases, we may want to save time by only copying the tables that have been modified. If later we need to restore a table, we will use the most recent backup we have for that table.
The following is a typical rsync
invocation to take a backup:
root@this:/usr/local/mysql# rsync --progress --stats --compress -rtl data /tmp/rsync_bkp … data/mysql/db.MYD 1264 100% 2.07kB/s 0:00:00 (xfer#133, to-check=238/379) data/mysql/db.MYI 9216 100% 14.63kB/s 0:00:00 (xfer#134, to-check=237/379) data/mysql/db.frm 2677 100% 4.13kB/s 0:00:00 (xfer#135, to-check=236/379) … Number of files: 379 Number of files transferred: 371 Total file size: 946950619 bytes Total transferred file size: 946950619 bytes Literal data: 946950619 bytes Matched data: 0 bytes File list size: 8498 File list generation time: 0.001 seconds File list transfer time: 0.000 seconds Total bytes sent: 103777427 Total bytes received: 7093 sent 103777427 bytes received 7093 bytes 1356660.39 bytes/sec total size is 946950619 speedup is 9.12
The output of this invocation is usually very long; in this case, it has been manually edited to make it short.
We use the following options:
--progress
: This option show progress information. This makes the output very long but is useful if a problem occurs.--stats
: This option prints the final statistics on transferred files.--compress
: This option compresses a copy with zlib. This is usually a good idea, because rsync
is most useful for copying big files. However, we may want to make the lock time as short as possible. To do this, we may prefer to compress the files after releasing the locks, probably with gzip or similar tools.-r
: This option copies recursively.-t
: This option transfers the information of the file's most recent modifications so that an incremental backup will be possible next time.-l
: This option follows the symbolic links, if any. We generally do not want rsync
to delete files in the target directory that have been deleted from the source directory. For this reason, we did not use the --delete
option.To restore a backup, most storage engines only require that the tables are locked with FLUSH TABLES WITH READ LOCK
before copying the backup files into the data
directory. However, this was not possible with InnoDB before MariaDB 10.0. Since MariaDB 10.0, InnoDB supports a feature called
transportable tablespaces. This means that it is possible to copy the .ibd
files from a running server and restore those files into the same (or another) running server later using a special SQL statement. This feature can be used for backups or to copy data between running servers.
This feature has some important limitations:
foreign_key_checks
server variable is set to ON
. If the table contains a foreign key, the checks must be temporarily disabled by setting it to OFF
before the copy. If the file is later restored, the foreign key constraint will not be applied during the restore.To create a backup copy of a tablespace, follow the given steps:
FLUSH TABLES table_list FOR EXPORT;
. A table-shared lock is acquired..cfg
file for each InnoDB table. We did not mention those files before. They are only created for InnoDB tables and are only useful when copying a table into a running server.foreign_key_checks
server variable to OFF
if necessary..ibd
and .cfg
files into a backup directory.foreign_key_checks
server variable to ON
if it was previously disabled.UNLOCK TABLES
to release the lock.To restore a backup tablespace on a running server:
ALTER TABLE table_name DISCARD TABLESPACE;
. An exclusive table lock is acquired..ibd
and .cfg
files into the data
directory.ALTER TABLE table_name IMPORT TABLESPACE;
.