Physical backups

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.

Which files should be copied?

A complete backup consists of all the following groups of files: table files, trigger files, logs, and configuration files.

Table 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

Aria

.MAD

.MAI

MyISAM

.MYD

.MYI

ARCHIVE

.ARZ

 

CONNECT

User-defined

.dnx

CSV

.CSV

 

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.

Logfiles

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

@@log_error

General query log

@@general_log

Slow query log

@@slow_query_log

Binary log

@@log_bin

Keeping the logfiles in the data directory should simplify the backup procedure.

Configuration files

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:

  • The /etc path
  • The /etc/mysql path
  • The SYSCONFDIR path
  • The $MYSQL_HOME path
  • The file indicated with the --defaults-extra-file option
  • The ~/ path

On Windows systems, the paths are different:

  • The %PROGRAMDATA%MariaDBMariaDB Server 10.0 path
  • The %WINDIR% path
  • The C: path
  • The installation directory
  • The file indicated with the --defaults-extra-file option

Hot physical backups

When 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.

Note

Each table locked with one of these statements is removed from the query cache. The reason is that the server knows that the data will probably be replaced, and it will need to read the new contents from files.

The most convenient procedure is as follows:

  1. We open a mysql client.
  2. We execute FLUSH TABLES … FOR EXPORT or FLUSH TABLES … WITH READ LOCK and leave the client open.
  3. We copy the file using the system console or any other program we like.
  4. In the client, we execute 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.

Filesystem snapshots

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:

  1. We open a mysql client.
  2. We execute FLUSH TABLES … WITH READ LOCK and leave the client open.
  3. In a system console, we execute a command similar to the following: mount vxfs snapshot.
  4. In the client, we execute UNLOCK TABLES to release the lock.

The mylvmbackup utility, included in most Linux distribution repositories, automates this procedure.

Incremental physical backups with the rsync command

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.

Copying files when the server is running

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:

  • The InnoDB file-per-table mode must be on. The system tablespace is not transportable.
  • A table cannot be copied this way if it has foreign keys and the 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.
  • This feature cannot be used to move tablespaces between different versions of MariaDB. A minor server upgrade (that is, when only the third version number changes) should not invalidate the backup tablespaces, as long as the server version is stable.
  • When copying data between servers, the table must exist on the destination server.

To create a backup copy of a tablespace, follow the given steps:

  1. Run FLUSH TABLES table_list FOR EXPORT;. A table-shared lock is acquired.
  2. This creates a .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.
  3. The tables are now consistent and locked. Set the foreign_key_checks server variable to OFF if necessary.
  4. Copy the .ibd and .cfg files into a backup directory.
  5. Set the foreign_key_checks server variable to ON if it was previously disabled.
  6. Run UNLOCK TABLES to release the lock.

To restore a backup tablespace on a running server:

  1. Run ALTER TABLE table_name DISCARD TABLESPACE;. An exclusive table lock is acquired.
  2. Copy the .ibd and .cfg files into the data directory.
  3. Run ALTER TABLE table_name IMPORT TABLESPACE;.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.182.62