Mariadb 简明教程

MariaDB - Backup Methods

数据是业务和运营的基础，由于存在各种可能的威胁（例如攻击者、系统故障、不良升级和维护错误），因此备份仍然至关重要。这些备份形式多样，并且有许多选项可以创建它们，并在这些过程中有更广泛的选项集。需要记住的重要事项是数据库类型、关键信息和所涉及的结构。这些信息决定了您的最佳选择。

Data serves as the foundation of business and operations, and with various possible threats (e.g., attackers, system failures, bad upgrades, and maintenance errors) out there, backups remain critical. These backups take many forms, and many options exist for creating them with an even wider set of options within those processes. The important things to remember are the database type, the critical information, and the structure involved. This information determines your best option.

OPTIONS

备份的主要选项包括逻辑备份和物理备份。逻辑备份保存 SQL 语句以恢复数据。物理备份包含数据的副本。

The main options for backups include logical backups and physical backups. Logical backups hold SQL statements for restoring data. Physical backups contain copies of data.

  1. Logical backups offer the flexibility of restoring data on another machine with a different configuration in contrast to physical backups, which are often limited to the same machine and database type. Logical backups occur at database and table level, and physical occur at directory and file level.

  2. Physical backups are smaller in size than logical, and also take less time to perform and restore. Physical backups also include log and configuration files, but logical backups do not.

Backup Tools

用于 MariaDB 备份的主要工具是 mysqldump 。它提供逻辑备份和灵活性。它还被证明是小型数据库的绝佳选择。 Mysqldump 将数据转储到 SQL、CSV、XML 和其他许多格式中。在没有明确指令的情况下，其输出不会保留存储过程、视图和事件。

The main tool used for MariaDB backups is mysqldump. It offers logical backups and flexibility. It also proves an excellent option for small databases. Mysqldump dumps data into SQL, CSV, XML, and many other formats. Its output does not retain stored procedures, views, and events without explicit instruction.

有三个用于 mysqldump 备份的选项 −

There are three options for mysqldump backups −

  1. Raw data − Dump a table as a raw data file through the --tab option, which also specifies the destination of the file −

$ mysqldump -u root -p --no-create-info \
   --tab=/tmp PRODUCTS products_tbl

  1. Data/Definitions export − This option allows a single or multiple tables to be exported to a file, and supports backing up all existing databases on the host machine. Examine an example of exporting contents or definitions to a file

$ mysqldump -u root -p PRODUCTS products_tbl > export_file.txt

  1. Transfer − You can also output databases and tables to another host

$ mysqldump -u root -p database_name \
   | mysql -h other-host.com database_name

Using THE SELECT…​INTO OUTFILE Statement

导出数据的另一个选项采用 SELECT…​INTO OUTFILE 语句。此简单选项将表输出到一个简单的格式化文本文件中 −

Another option for exporting data employs the SELECT…​INTO OUTFILE statement. This simple option outputs the table into a simple formatted text file −

mysql> SELECT * FROM products_tbl
   -> INTO OUTFILE '/tmp/products.txt';

其属性允许您根据自己的首选规范设置文件格式。

Its attributes allow formatting the file to your preferred specifications.

请注意此语句的以下特性 −

Note the following qualities of this statement −

  1. The file name must specify your desired location for the output.

  2. You need MariaDB file privileges to execute the statement.

  3. The output file name must be unique.

  4. You need login credentials on the host.

  5. In a UNIX environment, the output file is world readable, but its server ownership affects your ability to delete it. Ensure you have privileges.

Using CONNECT in Backups

CONNECT 处理程序允许导出数据。当 SELECT…​INTO OUTFILE 操作不支持文件格式时，这主要在情况下很有用。

The CONNECT handler allows exporting of data. This proves useful primarily in situations when the SELECT…​INTO OUTFILE operation does not support the file format.

查看以下示例：

Review the following example −

create table products
engine = CONNECT table_type = XML file_name = 'products.htm' header = yes
option_list = 'name = TABLE,coltype = HTML,attribute = border = 1;cellpadding = 5'

select plugin_name handler, plugin_version version, plugin_author
author, plugin_description description, plugin_maturity maturity
from information_schema.plugins where plugin_type = 'STORAGE ENGINE';

Other Tools

其他备份选项如下 −

Other options for backups are as follows −

  1. XtraBackup − This option targets XtraDB/InnoDB databases and works with any storage engine. Learn more about this tool from Percona’s official site.

  2. Snapshots − Some filesystems allow snapshots. The process consists of flushing the tables with read lock, mounting the snapshot, unlocking the tables, copying the snapshot, and then unmounting the snapshot.

  3. LVM − This popular method employs a Perl script. It gets a read lock on every table and flushes caches to disk. Then it gets a snapshot and unlocks the tables. Consult the official mylvmbackup website for more information.

  4. TokuBackup − This solution provided by Percona provides hot backups taking into account the problems and limitations of InnoDB backup options. It produces a transactional sound copy of files while applications continue to manipulate them.Consult the Percona website for more information..

INNODB Considerations

InnoDB 使用缓冲池来提高性能。在备份中，配置 InnoDB 以避免将整个表复制到缓冲池，因为逻辑备份通常执行全表扫描。

InnoDB uses a buffer pool for performance enhancement. In a backup, configure InnoDB to avoid copying an entire table into the buffer pool because logical backups typically perform full table scans.