May 16, 2021 MariaDB
Data is the foundation of business and operations, and with a variety of possible threats (e.g., attackers, system failures, poor upgrades, and maintenance errors), backup remains critical. T hese backups take many forms, and there are many options for creating them using broader options during these procedures. T he important things to keep in mind are database types, key information, and the structure involved. T his information determines your best choice.
The primary options for backups include logical and physical backups. T he logical backup holds the SQL statement used to recover the data. T he physical backup contains a copy of the data.
Logical backups provide the flexibility to recover data on another machine with different configurations than physical backups, which are typically limited to the same machine and database types. L ogical backups occur at the database and table levels, and physically at the directory and file levels.
Physical backups are smaller than logical backups and take less time to execute and recover. P hysical backups also include logs and profiles, but logical backups do not.
The main tool for MariaDB backup is mysqldump. I t provides logical backup and flexibility. I t also proved to be a good choice for small databases. M ysqldump dumps data into SQL, CSV, XML, and many other formats. I ts output does not preserve stored procedures, views, and events, and there are no explicit instructions.
There are three options for mysqldump backup -
Raw data - The table is dumped as the original data file via the --tab option, which also specifies the target of the file -
$ mysqldump -u root -p --no-create-info --tab=/tmp PRODUCTS products_tbl
Data/Definition Express - This option allows you to export a single or multiple tables to a file and supports all existing databases on the backup host. Check the example of exporting content or definitions to a file
$ mysqldump -u root -p PRODUCTS products_tbl > export_file.txt
Transport - You can also output the database and table to another host
$ mysqldump -u root -p database_name | mysql -h other-host.com database_name
Another option for exporting data is to use SELECT ... I NTO OUTFILE statement. T his simple option outputs the table to a simple formatted text file -
mysql> SELECT * FROM products_tbl -> INTO OUTFILE '/tmp/products.txt';
Its properties allow the file to be formatted as your preferred specification.
Please note the following quality of this statement -
The file name must specify the desired location for the output.
You need MariaDB file permissions to execute the statement.
The output file name must be unique.
You need login credentials on the host.
In a UNIX environment, the output file is readable around the world, but server ownership affects your ability to delete it. M ake sure you have permission.
The CONNECT handler allows the data to be exported. T his proves that mainly in SELECT ... I NTO OUTFILE operations are useful when file formats are not supported.
See 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 options for backup are as follows -
XtraBackup - This option is for the XtraDB/InnoDB database and works with any storage engine. L earn more about this tool from Percona's official website.
Snapshots - Some file systems allow snapshots. T he process involves refreshing the table with a read lock, loading the snapshot, unlocking the table, copying the snapshot, and then unloading the snapshot.
LVM - This popular method uses Perl scripts. I t gets the read lock on each table and flushes the cache to disk. I t then takes a snapshot and unlocks the table. F or more information, please contact the official mylvmbackup website.
TokuBackup - Percona provides this solution for hot backups, taking into account problems and limitations with InnoDB backup options. I t generates transactional sound copies of files while the application continues to manipulate them. F or more information, please contact percona.com.
InnoDB uses buffer pools to improve performance. I n a backup, innoDB is configured to avoid copying the entire table into a buffer pool, because logical backups typically perform full-table scans.