1 of 100

Server

MariaDB Server Documentation

Explore MariaDB Server, the powerful open-source relational database. This comprehensive documentation covers installation, deployment, usage, security, and advanced topics to help you master MariaDB.

Quickstart Guides

Get started quickly with MariaDB Server using these quickstart guides. Follow step-by-step instructions to install, configure, and begin using MariaDB for your projects.

Installing MariaDB Server Guide

Quickstart Guide: Installing MariaDB Server

This guide provides a quick overview of how to install MariaDB Server on common operating systems. The specific steps may vary slightly depending on your Linux distribution or if you are installing on Windows.

For Linux (Ubuntu/Debian/Red Hat-based distributions)

The most common way to install MariaDB on Linux is through your system's package manager.

Steps:

Update Package List:
Before installing, it's a good practice to update your package index.
- For Debian/Ubuntu:Bash
- For Red Hat/CentOS/Fedora:Bash
Install MariaDB Server:
Install the MariaDB server and client packages.
- For Debian/Ubuntu:Bash
- For Red Hat/CentOS/Fedora:Bash
Secure the Installation:
After installation, run the security script to set a root password, remove anonymous users, and disable remote root login.
Follow the prompts to configure your security settings.
Start and Verify the Service:
MariaDB typically starts automatically after installation. You can check its status and manually start it if needed.
- Check status:
- Start service (if not running):Bash
- Verify installation by connecting as root:Bash
  Enter the root password you set during the secure installation.

For Windows

For Windows, MariaDB provides an .msi installer for a straightforward graphical installation.

Steps:

Download MariaDB:
Visit the MariaDB downloads page to get the latest .msi installer.
Run the Installer:
Double-click the downloaded .msi file to start the installation wizard.
Follow On-Screen Instructions:
The installer will guide you through the process, including:
- Accepting the end-user license agreement.
- Selecting features and the installation directory.
- Setting a password for the root user.
- Configuring MariaDB as a service and setting the port (default is 3306).
- Optionally, enabling UTF8 as the default server character set.

Important Notes:

Firewall: Ensure your firewall is configured to allow connections to MariaDB on the appropriate port (default 3306) if you need remote access.
Root Password: Always set a strong root password during the secure installation step.
Further Configuration: For production environments, you may need to adjust further settings in the MariaDB configuration files (e.g., my.cnf on Linux).

Additional Resources:

Essentials of an Index Guide

An index on Last_Name organizes the records by surname, enhancing search efficiency without altering the original table order. Indices can be created for any column, such as ID or first name, to enable quick lookups based on different criteria.

Imagine you've created a table with the following rows:

+----+------------+-----------+-------------------------+---------------------------+--------------+
| ID | First_Name | Last_Name | Position                | Home_Address              | Home_Phone   |
+----+------------+-----------+-------------------------+---------------------------+--------------+
|  1 | Mustapha   | Mond      | Chief Executive Officer | 692 Promiscuous Plaza     | 326-555-3492 |
|  2 | Henry      | Foster    | Store Manager           | 314 Savage Circle         | 326-555-3847 |
|  3 | Bernard    | Marx      | Cashier                 | 1240 Ambient Avenue       | 326-555-8456 |
|  4 | Lenina     | Crowne    | Cashier                 | 281 Bumblepuppy Boulevard | 328-555-2349 |
|  5 | Fanny      | Crowne    | Restocker               | 1023 Bokanovsky Lane      | 326-555-6329 |
|  6 | Helmholtz  | Watson    | Janitor                 | 944 Soma Court            | 329-555-2478 |
+----+------------+-----------+-------------------------+---------------------------+--------------+

Now, imagine you've been asked to return the home phone of Fanny Crowne. Without indexes, the only way to do it is to go through every row until you find the matching first name and surname. Now imagine there are millions of records and you can see that, even for a speedy database server, this is highly inefficient.

The answer is to sort the records. If they were stored in alphabetical order by surname, even a human could quickly find a record amongst a large number. But we can't sort the entire record by surname. What if we want to also look a record by ID, or by first name? The answer is to create separate indexes for each column we wish to sort by. An index simply contains the sorted data (such as surname), and a link to the original record.

For example, an index on Last_Name:

+-----------+----+
| Last_Name | ID |
+-----------+----+
| Crowne    |  4 |
| Crowne    |  5 |
| Foster    |  2 |
| Marx      |  3 |
| Mond      |  1 |
| Watson    |  6 |
+-----------+----+

and an index on Position

+-------------------------+----+
| Position                | ID |
+-------------------------+----+
| Cashier                 |  3 |
| Cashier                 |  4 |
| Chief Executive Officer |  1 |
| Janitor                 |  6 |
| Restocker               |  5 |
| Store Manager           |  2 |
+-------------------------+----+

would allow you to quickly find the phone numbers of all the cashiers, or the phone number of the employee with the surname Marx, very quickly.

Where possible, you should create an index for each column that you search for records by, to avoid having the server read every row of a table.

See CREATE INDEX and Getting Started with Indexes for more information.

_{This page is licensed: CC BY-SA / Gnu FDL}

Basic SQL Statements Guide

Core SQL Statements Guide

This guide provides a quick overview of essential SQL statements in MariaDB, categorized by their function in data definition, data manipulation, and transaction control. Find brief descriptions and links to detailed documentation for each statement, along with a simple illustrative example sequence.

(If you need a basic tutorial on how to use the MariaDB database server and execute simple commands, see A MariaDB Primer. Also see Essential Queries Guide for examples of commonly-used queries.)

Defining How Your Data Is Stored (Data Definition Language - DDL)

CREATE DATABASE: Used to create a new, empty database.
DROP DATABASE: Used to completely destroy an existing database.
USE: Used to select a default database for subsequent statements.
CREATE TABLE: Used to create a new table, which is where your data is actually stored.
ALTER TABLE: Used to modify an existing table's definition (e.g., add/remove columns, change types).
DROP TABLE: Used to completely destroy an existing table and all its data.
DESCRIBE (or DESC): Shows the structure of a table (columns, data types, etc.).

Manipulating Your Data (Data Manipulation Language - DML)

SELECT: Used when you want to read (or select) your data from one or more tables.
INSERT: Used when you want to add (or insert) new rows of data into a table.
UPDATE: Used when you want to change (or update) existing data in a table.
DELETE: Used when you want to remove (or delete) existing rows of data from a table.
REPLACE: Works like INSERT, but if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
TRUNCATE TABLE: Used to quickly remove all data from a table, resetting any AUTO_INCREMENT values. It is faster than DELETE without a WHERE clause for emptying a table.

Transactions (Transaction Control Language - TCL)

START TRANSACTION (or BEGIN): Used to begin a new transaction, allowing multiple SQL statements to be treated as a single atomic unit.
COMMIT: Used to save all changes made during the current transaction, making them permanent.
ROLLBACK: Used to discard all changes made during the current transaction, reverting the database to its state before the transaction began.

A Simple Example Sequence

This example demonstrates several of the statements in action:

-- Create a new database
CREATE DATABASE mydb;

-- Select the new database to use
USE mydb;

-- Create a new table
CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- Insert some data
INSERT INTO mytable VALUES (1, 'Will');
INSERT INTO mytable VALUES (2, 'Marry');
INSERT INTO mytable VALUES (3, 'Dean');

-- Select specific data
SELECT id, name FROM mytable WHERE id = 1;

-- Update existing data
UPDATE mytable SET name = 'Willy' WHERE id = 1;

-- Select all data to see changes
SELECT id, name FROM mytable;

-- Delete specific data
DELETE FROM mytable WHERE id = 1;

-- Select all data again
SELECT id, name FROM mytable;

-- Drop the database (removes the database and its tables)
DROP DATABASE mydb;

Common Query: Counting Rows

To count the number of records in a table:

SELECT COUNT(*) FROM mytable; -- Or SELECT COUNT(1) FROM mytable;

(Note: This query would typically be run on an existing table, for example, before it or its database is dropped.)

_{This page is licensed: CC BY-SA / Gnu FDL}

Restoring Data from Dump Files Guide

Data Restoration Guide

This guide explains how to restore your MariaDB data from backup files created with mariadb-dump. Learn the basic restoration process using the mariadb client and a specific technique for selectively restoring a single table while minimizing data loss on other tables.

It's important to understand that mariadb-dump is used for creating backup (dump) files, while the mariadb client utility is used for restoring data from these files. The dump file contains SQL statements that, when executed, recreate the database structure and/or data.

Basic Restoration Process

To restore a dump file, you direct the mariadb client to execute the SQL statements contained within the file.

mariadb --user your_username --password < /path/to/your/backupfile.sql

Replace your_username with your MariaDB username and /path/to/your/backupfile.sql with the actual path to your dump file.
You will be prompted for the password for your_username.
The < symbol is a standard input (STDIN) redirect, feeding the contents of backupfile.sql to the mariadb client.
Often, the dump file itself contains CREATE DATABASE IF NOT EXISTS and USE database_name; statements, so a specific database doesn't always need to be named on the command line during restore. If your dump file restores to a specific database, ensure that user has permissions to it. If the dump file does not specify a database, you might need to create the database first and then run:
```
mariadb --user your_username --password your_database_name < /path/to/your/backupfile.sql
```

Important Considerations Before Restoring

Data Overwriting: Restoring a dump file will execute the SQL statements within it. If the dump file contains DROP TABLE and CREATE TABLE statements (common for full backups), existing tables with the same names will be dropped and recreated, leading to loss of any data added or changed since the backup was made.
Backup Age: If your dump file is several days old, restoring it entirely could revert all data in the affected tables/databases to that older state. This can be disastrous if only a small portion of data was lost and the rest has been actively updated.

Always ensure you understand the contents of the dump file and the potential impact before initiating a restore, especially on a production system. Consider testing the restore on a non-production environment first if possible.

Restoring a Single Table Selectively

If only one table has been lost or corrupted and your backup file contains an entire database (or multiple tables), a full restore might overwrite recent, valid data in other tables. Here’s a method to restore only a specific table using a temporary user with restricted privileges:

Create a Temporary User: Create a MariaDB user specifically for this restore operation.

Grant Limited Privileges:

Grant this temporary user the minimal privileges needed for the dump file to execute up to the point of restoring your target table. This might be SELECT on all tables in the database if the dump file checks other tables, or simply the ability to USE the database.
Then, grant ALL PRIVILEGES (or specific necessary privileges like CREATE, DROP, INSERT, SELECT) only on the specific table you want to restore.

Example SQL to create a temporary user and grant permissions (replace placeholders):

-- Connect to MariaDB as an administrative user (e.g., root)
CREATE USER 'admin_restore_temp'@'localhost' IDENTIFIED BY 'its_very_secure_pwd';

-- Grant general SELECT on the database (might be needed if dump file structure requires it)
-- Or, if not needed, ensure the user can at least USE the database.
GRANT SELECT ON your_database_name.* TO 'admin_restore_temp'@'localhost';

-- Grant full privileges ONLY on the table to be restored
GRANT ALL PRIVILEGES ON your_database_name.table_to_restore TO 'admin_restore_temp'@'localhost';

FLUSH PRIVILEGES;

Restore Using the Temporary User and --force:
Use the mariadb client with the temporary user and the --force option. The --force option tells MariaDB to continue executing statements in the dump file even if some SQL errors occur. Errors will occur for operations on tables where admin_restore_temp lacks permissions, but operations on table_to_restore (where permissions were granted) should succeed.
Bash
```
mariadb --user admin_restore_temp --password --force your_database_name < /path/to/your/fulldumpfile.sql
```
You will be prompted for the password of admin_restore_temp.
Verify Restoration: Check that table_to_restore has been correctly restored.
Clean Up: Drop the temporary user once the restoration is confirmed:
```
DROP USER 'admin_restore_temp'@'localhost';
```

This method helps to isolate the restore operation to the intended table, protecting other data from being inadvertently reverted to an older state.

Changing Times in MariaDB

Modifying Dates and Times Guide

This guide explores MariaDB functions for performing calculations and modifications on date and time values. Learn to use functions like DATE_ADD, DATE_SUB, TIME_TO_SEC, and SEC_TO_TIME to accurately add or subtract intervals and manage date/time changes that cross midnight or month/year boundaries.

(For foundational knowledge on date and time data types and basic retrieval, please refer to the "Date and Time Handling Guide".)

Calculating Time Across Midnight

When adding hours to a TIME value, calculations might exceed 24 hours. For example, if a task is entered at 23:00 and is promised 2 hours later, a simple addition can be problematic.

Consider an INSERT statement for a tickets table with entered and promised TIME columns:

-- Example: Calculating a promised time 2 hours (7200 seconds) from current time
INSERT INTO tickets (client_id, urgency, trouble, ticket_date, entered, promised)
VALUES ('some_client', 'ASAP', 'Issue details',
        CURDATE(), CURTIME(),
        SEC_TO_TIME(TIME_TO_SEC(CURTIME()) + 7200));

TIME_TO_SEC(time) converts a time value to seconds.
SEC_TO_TIME(seconds) converts seconds back to a time format (HHH:MM:SS).

If CURTIME() is 23:00:00 (82,800 seconds), 82800 + 7200 = 90000 seconds. SEC_TO_TIME(90000) would result in 25:00:00. While MariaDB can store this, it doesn't represent a standard clock time for the next day.

Modulo Arithmetic for Time Rollover:

To handle time wrapping around the 24-hour clock (86,400 seconds in a day) for TIME columns, use the modulo operator (%):

-- Corrected calculation for 'promised' TIME, wraps around 24 hours
SEC_TO_TIME((TIME_TO_SEC(CURTIME()) + 7200) % 86400)

If current time is 23:00, (82800 + 7200) % 86400 becomes 90000 % 86400, which is 3600 seconds. SEC_TO_TIME(3600) correctly results in 01:00:00.

Tracking Date Changes with Time: Using `DATETIME`

The modulo arithmetic above gives the correct time of day but doesn't indicate if the promised time falls on the next calendar day. For calculations where the date might change, it's essential to use DATETIME (or TIMESTAMP) data types.

If your table initially used separate DATE and TIME columns (e.g., ticket_date, entered_time, promised_time), you would typically alter the table to use DATETIME columns (e.g., entered_datetime, promised_datetime) to store both date and time information accurately. This often involves:

Adding new DATETIME columns.
Populating them by combining the old date and time columns (e.g., using CONCAT(ticket_date, ' ', entered_time)).
Dropping the old separate date and time columns. (Always back up your data before such structural changes.)

With DATETIME columns, NOW() can be used to get the current date and time.

Adding Durations with `DATE_ADD`

The DATE_ADD(date, INTERVAL expr unit) function is the most robust way to add a duration to a date, time, or datetime value. It correctly handles rollovers across days, months, and years.

date: A DATE, DATETIME, or TIME value.
expr: The value of the interval to add.
unit: The unit of the interval (e.g., HOUR, MINUTE, DAY, MONTH, YEAR, etc.).

Adding Hours (handles date change):

If entered and promised are DATETIME columns:

INSERT INTO tickets (client_id, urgency, trouble, entered, promised)
VALUES ('some_client', 'ASAP', 'Issue details',
        NOW(),
        DATE_ADD(NOW(), INTERVAL 2 HOUR));

If NOW() is 2025-06-03 23:00:00, promised will correctly be 2025-06-04 01:00:00.

Adding Combined Hours and Minutes:

Use HOUR_MINUTE as the unit. The expr is a string 'hours:minutes'.

-- Add 2 hours and 30 minutes
DATE_ADD(NOW(), INTERVAL '2:30' HOUR_MINUTE)

If NOW() is 2025-06-03 23:00:00, this results in 2025-06-04 01:30:00.

Date Calculations Across Months and Years with `DATE_ADD`

DATE_ADD also correctly handles date changes across month and year boundaries, including leap years.

Adding Days:

-- Add 5 days
DATE_ADD(NOW(), INTERVAL 5 DAY)

If NOW() is 2025-02-27, this would result in 2025-03-04 (assuming 2025 is not a leap year).

Adding Combined Days and Hours:

Use DAY_HOUR as the unit. The expr is a string 'days hours'.

-- Add 2 days and 6 hours
DATE_ADD(NOW(), INTERVAL '2 6' DAY_HOUR)

Adding Combined Years and Months:

Use YEAR_MONTH as the unit. The expr is a string 'years-months'.

-- Add 1 year and 2 months
DATE_ADD(NOW(), INTERVAL '1-2' YEAR_MONTH) -- Note: Original text used '1 2', '1-2' is common for YEAR_MONTH

If NOW() is 2025-09-15 23:00:00, this results in 2026-11-15 23:00:00. This type of interval typically does not affect the day or time components directly, only the year and month.

Subtracting Durations

Using DATE_ADD with a Negative Interval:

You can subtract durations by providing a negative value for expr.

-- Subtract 5 days
DATE_ADD(NOW(), INTERVAL -5 DAY)

Using DATE_SUB(date, INTERVAL expr unit):

This function is specifically for subtracting durations.

-- Subtract 5 days
DATE_SUB(NOW(), INTERVAL 5 DAY)

Note: With DATE_SUB, expr is positive for subtraction. A negative expr would result in addition.

Making Backups with mariadb-dump Guide

Data Backup with mariadb-dump Guide

This guide explains how to use the mariadb-dump utility to create essential backup (dump) files of your MariaDB data. Learn to effectively back up all databases, specific databases, or individual tables, ensuring your data is protected and can be restored when needed.

About `mariadb-dump`

mariadb-dump is a command-line utility included with MariaDB for creating logical backups of your databases. It was previously known as mysqldump, which often still works as a symbolic link.

Key Advantages:

No Server Shutdown: Backups can be performed while the MariaDB server is running.
SQL Output: It generates a .sql file (a "dump file") containing SQL statements (CREATE TABLE, INSERT, etc.) necessary to reconstruct the databases and data.

All mariadb-dump commands are executed from your system's command-line shell, not within the mariadb client.

Backing Up All Databases

To export all databases managed by your MariaDB server:

mariadb-dump --user=admin_backup --password --lock-tables --all-databases > /data/backup/dbs_alldatabases.sql

--user=admin_backup: Specifies the MariaDB user performing the backup (this user needs appropriate privileges, typically at least SELECT and LOCK TABLES).
--password: Prompts for the user's password. For use in scripts where prompting is not possible, you can use --password=yourpassword (note the absence of a space and the security implication of having the password in a script or command history).
--lock-tables (or -x): Locks all tables across all databases before starting the backup to ensure data consistency. The lock is released once the dump is complete for each table. For transactional tables like InnoDB, using --single-transaction is often preferred as it provides a consistent snapshot without prolonged locking of all tables.
--all-databases (or -A): Specifies that all databases should be dumped.
> /data/backup/dbs_alldatabases.sql: Redirects the output (the SQL statements) to the specified file. Ensure the path exists and the user running the command has write permissions.

Commonly Used Option for Efficiency:

--extended-insert (or -e): Creates INSERT statements that include multiple rows per statement. This generally results in a smaller dump file and faster restores. This option is often enabled by default but can be explicitly stated.

Example with long options and password in script:

mariadb-dump --user=admin_backup --password=yoursecurepassword --lock-tables --extended-insert --all-databases > /data/backup/dbs_alldatabases.sql

Backing Up a Single Database

Backing up databases individually can result in smaller, more manageable dump files and allow for more flexible backup schedules.

mariadb-dump --user=admin_backup --password --lock-tables --extended-insert --databases your_database_name > /data/backup/your_database_name.sql

--databases (or -B): Followed by the name of the database to dump.

To back up multiple specific databases, list their names separated by spaces after the --databases option:

mariadb-dump --user=admin_backup --password --lock-tables --extended-insert --databases db1_name db2_name > /data/backup/selected_databases.sql

Backing Up Specific Tables

For very large databases, or if only certain tables change frequently, you might back up individual tables.

mariadb-dump --user=admin_backup --password --lock-tables --extended-insert your_database_name table_name1 table_name2 > /data/backup/your_database_name_selected_tables.sql

First, specify the database name (your_database_name).
Then, list one or more table names (table_name1, table_name2) separated by spaces.
Note that the --databases option is not used when dumping specific tables in this manner.

Important Considerations and Best Practices

User Privileges: The MariaDB user specified with --user needs at least SELECT privileges for the tables being dumped. LOCK TABLES privilege is needed if using --lock-tables. RELOAD or FLUSH_TABLES might be needed for options like --flush-logs or --master-data. For --single-transaction, PROCESS and RELOAD might be required. A user with global SELECT, LOCK TABLES, SHOW VIEW, EVENT, and TRIGGER privileges is often used for backups.
Consistency with InnoDB: For databases primarily using InnoDB tables, consider using the --single-transaction option instead of --lock-tables. This option starts a transaction before dumping and reads data from a consistent snapshot without locking the tables for extended periods, allowing concurrent reads and writes.Bash
```
mariadb-dump --user=admin_backup --password --single-transaction --extended-insert --databases your_innodb_database > /data/backup/your_innodb_database.sql
```
Practice Makes Perfect: mariadb-dump is powerful but can have many options. Practice using it on a test database or server to become comfortable with its usage and to verify that your backup strategy works as expected.
Test Your Backups: Regularly test your backup files by restoring them to a non-production environment to ensure they are valid and can be used for recovery.
Restoration: To learn how to restore data from these dump files, see the "Data Restoration Guide".
Security: Store backup files in a secure location. If passwords are included in scripts, ensure the script files have restricted permissions.

_{This page is licensed: CC BY-SA / Gnu FDL}

Server Usage

Learn how to effectively use MariaDB Server. This section covers SQL statements, built-in functions, client utilities, and best practices for daily database operations.

Basics

Grasp the basics of using MariaDB Server. This section introduces fundamental concepts, common SQL commands, and essential operations to get you started with your database.

Connecting

Learn how to connect to MariaDB Server. This section details various methods and tools for establishing secure and efficient connections to your database from different applications and environments.

Data Handling

Learn effective data handling in MariaDB Server. This section covers data types, storage engines, data manipulation, and best practices for managing your information efficiently.

Backup & Restore

Learn to back up and restore MariaDB Server databases. This section covers essential strategies and tools to ensure data safety and quick recovery from potential data loss.

Forming a Backup Strategy

Overview

The strategy applied when implementing data backups depends on business needs.

Data backup strategy depends on business needs. Business needs can be evaluated by performing a data inventory, determining data recovery objectives, considering the replication environment, and considering encryption requirements. Also critical is a backup storage strategy and testing backup and recovery procedures.

Data Inventory

Backup strategy requirements flow from the understanding you build by performing a data inventory. A data inventory is established by asking questions such as:

What data is housed in the databases?
What business purpose does this data serve?
How long does the data needed to be retained in order to meet this business purpose?
Are there any legal or regulatory requirements, which would limit the length of data retention?

Recovery Objectives

Data recovery requirements are often defined in terms of Recovery Point Objective (RPO) and Recovery Time Objective (RTO). RTO and RPO are considered in the context of the data identified in the data inventory.

Recovery Point Objective (RPO) defines the maximum amount of data a business is willing to lose. For example, a business can define a RPO of 24 hours.

Recovery Time Objective (RTO) defines how quickly a business needs to restore service in the event of a fault. For example, a business can define a RTO of 8 hours.

Backup strategy plays a substantial role in achieving RPO and RTO.

Achieving RPO

RPO depends on completion of backups, which provide a viable recovery point. Since RPO is measured at backup completion, not backup initiation, backup jobs must be scheduled at an interval smaller than the RPO.

Techniques for achieving RPO include:

Frequent incremental backups and less frequent full backups.
Performing backups in conjunction with replication and clustering to eliminate impact on production workloads, allowing a higher backup frequency.
Automated monitoring of backup status.
Automated testing of backups.

Achieving RTO

The RTO window typically commences at the point when a decision is made by the business to recover from backups, not at the start of an incident.

Techniques for achieving RTO include:

Leveraging information produced during incident response, which can reduce the set of data to restore from backups, or identify specific data validation requirements dependent on the nature of the incident.
Having fast access to backup data. Performance requirements of backup infrastructure should be understood for both backup and restoration workloads.
Using delayed replication, either within the same data center or to a different data center, can provide shorter path to recovery. This is particularly true when coupled with robust application monitoring, which allows intervention before the window of delay elapses.
Applying written and tested recovery procedures, which designate the systems and commands to be used during recovery.
Performing drills and exercises that periodically test recovery procedures to confirm readiness.

Replication Considerations

MariaDB Enterprise Server supports several implementations of replication, which accurately duplicates data from one Server to one or more other Servers. The use of a dedicated replica as a source for backups can minimize workload impact.

MariaDB Enterprise Cluster implements virtually synchronous replication, where each Server instance contains a replica of all of the data for the Cluster. Backups can be performed from any node in the Cluster.

Encryption Considerations

MariaDB Enterprise Server supports encryption on disk (data-at-rest encryption) and on the network (data-in-transit encryption).

MariaDB Enterprise Backup copies tablespaces from disk. When data-at-rest encryption is enabled, backups contain encrypted data.

MariaDB Enterprise Backup supports TLS encryption for communications with MariaDB Enterprise Server. To enable TLS encryption, set TLS options from the command-line or in the configuration file:

mariadb-backup --backup \
      --target-dir=/data/backups/full \
      --user=mariadb-backup \
      --password=mbu_passwd \
      --ssl-ca=/etc/my.cnf.d/certs/ca.pem \
      --ssl-cert=/etc/my.cnf.d/certs/client-cert.pem \
      --ssl-key=/etc/my.cnf.d/certs/client-key.pem

Backup Storage Considerations

How backups are stored can impact backup viability. Backup storage also presents separate risks. These risks need to be carefully considered:

Backup data should always be stored separately from the system being backed up, and separate from the system used for recovery.
Backup data should be subject to equal or more controls than data in production databases. For example, backup data should generally be encrypted even where a decision has bee made that a production database will not use data-at-rest encryption.
Business requirements may define a need for offsite storage of backups as a means of guaranteeing delivery on RPO. In these cases you should also consider onsite storage of backups as a means of guaranteeing delivery on RTO.
Retention requirements and the run-rate of new data production can aid in capacity planning.

Backup Testing

Testing has been identified as a critical success factor for the successful operation of data systems.

Backups should be tested. Recovery using backups and recovery procedures should be tested.

Backup Optimization

Overview

Backup and restore implementations can help overcome specific technical challenges that would otherwise pose a barrier to meeting business requirements.

Each of these practices represents a trade-off. Understand risks before implementing any of these practices.

Scheduling of Restore Preparation

Technical challenge: restore time

Trade-off: increased ongoing overhead for backup processing

Backup data can be prepared for restore any time after it is produced and before it is used for restore. To expedite recovery, incremental backups can be pre-applied to the prior full backup to enable faster recovery. This may be done at the expense of recovery points, or at the expense of storage by maintaining copies of unmerged full and incremental backup directories.

Moving Restored Data

Technical challenge: disk space limitations

Trade-off: modification of backup directory contents

Suggested method for moving restored data is to use --copy-back as this method provides added safety. Where you might instead optimize for disk space savings, system resources, and time you may choose to instead use MariaDB Enterprise Backup's --move-back option. Speed benefits are only present when backup files are on the same disk partition as the destination data directory.

The --move-back option will result in the removal of all data files from the backup directory, so it is best to use this option only when you have an additional copy of your backup data in another location.

To restore from a backup by moving files, use the --move-back option:

mariadb-backup --move-back --target-dir=/data/backups/full

Multithreading

Technical challenge:: CPU bottlenecks

Trade-off: Increased workload during backups

MariaDB Enterprise Backup is a multi-threaded application that by default runs on a single thread. In cases where you have a host with multiple cores available, you can specify the number of threads you want it to use for parallel data file transfers using the --parallel option:

mariadb-backup --backup \
      --target-dir=/data/backups/full \
      --user=mariadb-backup \
      --password=mbu_passwd \
      --parallel=12

Incrementing an Incremental Backup

Technical challenge: Backup resource overhead, backup duration

Trade-off: Increased restore complexity, restore process duration

Under normal operation an incremental backup is taken against an existing full backup. This allows you to further shorten the amount of time MariaDB Enterprise Backup locks MariaDB Enterprise Server while copying tablespaces. You can then apply the changes in the increment to the full backup with a --prepare operation at leisure, without disrupting database operations.

MariaDB Enterprise Backup also supports incrementing from an incremental backup. In this operation, the --incremental-basedir option points not to the full backup directory but rather to the previous incremental backup.

mariadb-backup --backup \
      --incremental-basedir=/data/backups/inc1 \
      --target-dir=/data/backups/inc2 \
      --user=mariadb-backup \
      --password=mbu_passwd

In preparing a backup to restore the data directory, apply the chain of incremental backups to the full backup in order. That is, first inc1/, then inc2/, and so on:

mariadb-backup --prepare \
      --target-dir=/data/backups/full \
      --incremental-dir=/data/backups/inc1

mariadb-backup --prepare \
      --target-dir=/data/backups/full \
      --incremental-dir=/data/backups/inc2

Continue to apply all the incremental changes until you have applied all available to the backup. Then restore as usual:

mariadb-backup --copy-back --target-dir=/data/backups/full
chown -R mysql:mysql /var/lib/mysql

Start MariaDB Enterprise Server on the restored data directory.

Storage Snapshots

Technical challenge: Backup resource overhead, backup duration.

Trade-off: Limited to platforms with volume-level snapshots, may require crash recovery.

While MariaDB Enterprise Backups produces file-level backups, users on storage solutions may prefer to instead perform volume-level snapshots to minimize resource impact. This storage capability exists with some SAN, NAS, and volume manager platforms.

Snapshots occur point-in-time, so no preparation step is needed to ensure data is internally consistent. Snapshots occur while tablespaces are open, and a restored snapshot may need to undergo crash recovery.

Just as traditional full, incremental, and partial backups should be tested, so too should recovery from snapshots be tested on an ongoing basis.

Taking Snapshots

MariaDB Server includes advanced backup functionality to reduce the impact of backup operations:

Connect with a client and issue a BACKUP STAGE START statement and then a BACKUP STAGE BLOCK_COMMIT statement.
Take the snapshot.
Issue a BACKUP STAGE END statement.
Once the backup has been completed, remove all files which begin with the #sql prefix. These files are generated when ALTER TABLE occurs during a staged backup.
Retrieve, copy, or store the snapshot as is typical for your storage platform and as per business requirements to make the backup durable. This may require mounting the snapshot in some manner.

It is recommended to briefly prevent writes while snapshotting. Specific commands vary depending on storage platform, business requirements, and setup, but a general approach is to:

Connect with a client and issue a FLUSH TABLES WITH READ LOCK statement, leaving the client connected.
Take the snapshot.
Issue an UNLOCK TABLES statement, to remove the read lock.
Retrieve, copy, or store the snapshot as is typical for your storage platform and as per business requirements to make the backup durable. This may require mounting the snapshot in some manner.

mariadb-backup

Get an overview of MariaDB Backup. This section introduces the hot physical backup tool, explaining its capabilities for efficient and consistent backups of your MariaDB Server.

Full Backup and Restore with mariadb-backup

When using mariadb-backup, you have the option of performing a full or an incremental backup. Full backups create a complete backup of the database server in an empty directory while incremental backups update a previous backup with whatever changes to the data have occurred since the backup. This page documents how to perform full backups.

Backing up the Database Server

In order to back up the database, you need to run mariadb-backup with the --backup option to tell it to perform a backup and with the --target-dir option to tell it where to place the backup files. When taking a full backup, the target directory must be empty or it must not exist.

To take a backup, run the following command:

The time the backup takes depends on the size of the databases or tables you're backing up. You can cancel the backup if you need to, as the backup process does not modify the database.

Mariadb-backup writes the backup files the target directory. If the target directory doesn't exist, then it creates it. If the target directory exists and contains files, then it raises an error and aborts.

Here is an example backup directory:

Preparing the Backup for Restoration

The data files that mariadb-backup creates in the target directory are not point-in-time consistent, given that the data files are copied at different times during the backup operation. If you try to restore from these files, InnoDB notices the inconsistencies and crashes to protect you from corruption

Before you can restore from a backup, you first need to prepare it to make the data files consistent. You can do so with the --prepare option.

Backup Preparation Steps

Run mariadb-backup --backup. You must use a version of mariadb-backup that is compatible with the server version you are planning to upgrade from. For instance, when upgrading from MariaDB 10.4 to 10.5, you must use the 10.4 version of mariadb-backup, Another example: When upgrading from MariaDB 10.6 to 10.11, you must use the 10.6 version of mariadb-backup.
Run mariadb-backup --prepare, again using a compatible version of mariadb-backup, as described in the previous step.

Restoring the Backup

Once the backup is complete and you have prepared the backup for restoration (previous step), you can restore the backup using either the --copy-back or the --move-back options. The --copy-back option allows you to keep the original backup files. The --move-back option actually moves the backup files to the datadir, so the original backup files are lost.

First, stop the MariaDB Server process.
Then, ensure that the datadir is empty.
Then, run mariadb-backup with one of the options mentioned above:

Then, you may need to fix the file permissions.

When mariadb-backup restores a database, it preserves the file and directory privileges of the backup. However, it writes the files to disk as the user and group restoring the database. As such, after restoring a backup, you may need to adjust the owner of the data directory to match the user and group for the MariaDB Server, typically mysql for both. For example, to recursively change ownership of the files to the mysql user and group, you could execute:

Finally, start the MariaDB Server process.

Restoring with Other Tools

Once a full backup is prepared, it is a fully functional MariaDB data directory. Therefore, as long as the MariaDB Server process is stopped on the target server, you can technically restore the backup using any file copying tool, such as cp or rysnc. For example, you could also execute the following to restore the backup:

_{This page is licensed: CC BY-SA / Gnu FDL}

Partial Backup and Restore with mariadb-backup

When using mariadb-backup, you have the option of performing partial backups. Partial backups allow you to choose which databases or tables to backup, as long as the table or partition involved is in an InnoDB file-per-table tablespace.This page documents how to perform partial backups.

Backing up the Database Server

Just like with full backups, in order to back up the database, you need to run mariadb-backup with the --backup option to tell it to perform a backup and with the --target-dir option to tell it where to place the backup files. The target directory must be empty or not exist.

For a partial backup, there are a few other arguments that you can provide as well:

To tell it which databases to backup, you can provide the --databases option.
To tell it which databases to exclude from the backup, you can provide the --databases-exclude option.
To tell it to check a file for the databases to backup, you can provide the --databases-file option.
To tell it which tables to backup, you can use the --tables option.
To tell it which tables to exclude from the backup, you can provide the --tables-exclude option.
To tell it to check a file for specific tables to backup, you can provide the --tables-file option.

The non-file partial backup options support regex in the database and table names.

For example, to take a backup of any database that starts with the string app1_ and any table in those databases that start with the string tab_, run the following command:

$ mariadb-backup --backup \
   --target-dir=/var/mariadb/backup/ \
   --databases='app1_*' --tables='tab_*' \
   --user=mariadb-backup --password=mypassword

mariadb-backup cannot currently backup a subset of partitions from a partitioned table. Backing up a partitioned table is currently an all-or-nothing selection. See MDEV-17132 about that. If you need to backup a subset of partitions, then one possibility is that instead of using mariadb-backup, you can export the file-per-table tablespaces of the partitions.

The time the backup takes depends on the size of the databases or tables you're backing up. You can cancel the backup if you need to, as the backup process does not modify the database.

mariadb-backup writes the backup files to the target directory. If the target directory doesn't exist, then it creates it. If the target directory exists and contains files, then it raises an error and aborts.

Preparing the Backup

Just like with full backups, the data files that mariadb-backup creates in the target directory are not point-in-time consistent, given that the data files are copied at different times during the backup operation. If you try to restore from these files, InnoDB notices the inconsistencies and crashes to protect you from corruption. In fact, for partial backups, the backup is not even a completely functional MariaDB data directory, so InnoDB would raise more errors than it would for full backups. This point will also be very important to keep in mind during the restore process.

Before you can restore from a backup, you first need to prepare it to make the data files consistent. You can do so with the --prepare command option.

Partial backups rely on InnoDB's transportable tablespaces. For MariaDB to import tablespaces like these, InnoDB looks for a file with a .cfg extension. For mariadb-backup to create these files, you also need to add the --export option during the prepare step.

For example, you might execute the following command:

$ mariadb-backup --prepare --export \
   --target-dir=/var/mariadb/backup/

If this operation completes without error, then the backup is ready to be restored.

mariadb-backup did not support the --export option. See MDEV-13466 about that. This means that mariadb-backup could not create .cfg files for InnoDB file-per-table tablespaces during the --prepare stage. You can still import file-per-table tablespaces without the .cfg files in many cases, so it may still be possible in those versions to restore partial backups or to restore individual tables and partitions with just the .ibd files. If you have a full backup and you need to create .cfg files for InnoDB file-per-table tablespaces, then you can do so by preparing the backup as usual without the --export option, and then restoring the backup, and then starting the server. At that point, you can use the server's built-in features to copy the transportable tablespaces.

Restoring the Backup

The restore process for partial backups is quite different than the process for full backups. A partial backup is not a completely functional data directory. The data dictionary in the InnoDB system tablespace will still contain entries for the databases and tables that were not included in the backup.

Rather than using the --copy-back or the --move-back, each individual InnoDB file-per-table tablespace file will have to be manually imported into the target server. The process that is used to import the file will depend on whether partitioning is involved.

Restoring Individual Non-Partitioned Tables

To restore individual non-partitioned tables from a backup, find the .ibd and .cfg files for the table in the backup, and then import them using the Importing Transportable Tablespaces for Non-partitioned Tables process.

Restoring Individual Partitions and Partitioned Tables

To restore individual partitions or partitioned tables from a backup, find the .ibd and .cfg files for the partition(s) in the backup, and then import them using the Importing Transportable Tablespaces for Partitioned Tables process.

_{This page is licensed: CC BY-SA / Gnu FDL}

Restoring Individual Tables and Partitions with mariadb-backup

When using mariadb-backup, you don't necessarily need to restore every table and/or partition that was backed up. Even if you're starting from a full backup, it is certainly possible to restore only certain tables and/or partitions from the backup, as long as the table or partition involved is in an InnoDB file-per-table tablespace. This page documents how to restore individual tables and partitions.

Preparing the Backup

Before you can restore from a backup, you first need to prepare it to make the data files consistent. You can do so with the --prepare command option.

The ability to restore individual tables and partitions relies on InnoDB's transportable tablespaces. For MariaDB to import tablespaces like these, InnoDB looks for a file with a .cfg extension. For mariadb-backup to create these files, you also need to add the --export option during the prepare step.

For example, you might execute the following command:

If this operation completes without error, then the backup is ready to be restored.

Note

mariadb-backup did not support the --export option to begin with. See about that. In earlier versions of MariaDB, this means that mariadb-backup could not create .cfg files for InnoDB file-per-table tablespaces during the --prepare stage. You can still import file-per-table tablespaces without the .cfg files in many cases, so it may still be possible in those versions to restore partial backups or to restore individual tables and partitions with just the .ibd files. If you have a full backup and you need to create .cfg files for InnoDB file-per-table tablespaces, then you can do so by preparing the backup as usual without the --export option, and then restoring the backup, and then starting the server. At that point, you can use the server's built-in features to copy the transportable tablespaces.

Restoring the Backup

The restore process for restoring individual tables and/or partitions is quite different than the process for full backups.

Rather than using the --copy-back or the --move-back, each individual InnoDB file-per-table tablespace file will have to be manually imported into the target server. The process that is used to restore the backup will depend on whether partitioning is involved.

Restoring Individual Non-Partitioned Tables

Restoring Individual Partitions and Partitioned Tables

_{This page is licensed: CC BY-SA / Gnu FDL}

Setting up a Replica with mariadb-backup

The terms master and slave have historically been used in replication, and MariaDB has begun the process of adding primary and replica synonyms. The old terms will continue to be used to maintain backward compatibility - see to follow progress on this effort.

mariadb-backup makes it very easy to set up a replica using a full backup. This page documents how to set up a replica from a backup.

If you are using MariaDB Galera Cluster, then you may want to try one of the following pages instead:

Backup the Database and Prepare It

The first step is to simply take and prepare a fresh full backup of a database server in the replication topology. If the source database server is the desired replication primary, then we do not need to add any additional options when taking the full backup. For example:

If the source database server is a replica of the desired primary, then we should add the --slave-info option, and possibly the --safe-slave-backup option. For example:

And then we would prepare the backup as you normally would. For example:

Copy the Backup to the New Replica

Once the backup is done and prepared, we can copy it to the new replica. For example:

Restore the Backup on the New Replica

At this point, we can restore the backup to the datadir, as you normally would. For example:

And adjusting file permissions, if necessary:

Create a Replication User on the Primary

Before the new replica can begin replicating from the primary, we need to create a user account on the primary that the replica can use to connect, and we need to grant the user account the REPLICATION SLAVE privilege. For example:

Configure the New Replica

Before we start the server on the new replica, we need to configure it. At the very least, we need to ensure that it has a unique server_id value. We also need to make sure other replication settings are what we want them to be, such as the various GTID system variables, if those apply in the specific environment.

Once configuration is done, we can on the new replica.

Start Replication on the New Replica

At this point, we need to get the replication coordinates of the primary from the original backup directory.

If we took the backup on the primary, then the coordinates are in the xtrabackup_binlog_info file. If we took the backup on another replica and if we provided the --slave-info option, then the coordinates are in the file xtrabackup_slave_info file.

mariadb-backup dumps replication coordinates in two forms: GTID coordinates and binary log file and position coordinates, like the ones you would normally see from SHOW MASTER STATUS output. We can choose which set of coordinates we would like to use to set up replication.

For example:

Regardless of the coordinates we use, we will have to set up the primary connection using CHANGE MASTER TO and then start the replication threads with START SLAVE.

GTIDs

If we want to use GTIDs, then we will have to first set gtid_slave_pos to the GTID coordinates that we pulled from either the xtrabackup_binlog_info file or the xtrabackup_slave_info file in the backup directory. For example:

And then we would set MASTER_USE_GTID=slave_pos in the CHANGE MASTER TO command. For example:

File and Position

If we want to use the binary log file and position coordinates, then we would set MASTER_LOG_FILE and MASTER_LOG_POS in the CHANGE MASTER TO command to the file and position coordinates that we pulled; either the xtrabackup_binlog_info file or the xtrabackup_slave_info file in the backup directory, depending on whether the backup was taken from the primary or from a replica of the primary. For example:

Check the Status of the New Replica

We should be done setting up the replica now, so we should check its status with SHOW SLAVE STATUS. For example:

_{This page is licensed: CC BY-SA / Gnu FDL}

Files Backed Up By mariadb-backup

Files Included in Backup

mariadb-backup backs up the files listed below.

InnoDB Data Files

mariadb-backup backs up the following InnoDB data files:

MyRocks Data Files

mariadb-backup will back up tables that use the storage engine. This data is located in the directory defined by the system variable. mariadb-backup backs this data up by performing a checkpoint using the system variable.

mariadb-backup will back up tables that use the MyRocks storage engine.

Other Data Files

mariadb-backup also backs up files with the following extensions:

frm
isl
MYD
MYI
MAD
MAI
MRG
TRG
TRN
ARM
ARZ
CSM
CSV
opt
par

Files Excluded From Backup

mariadb-backup does not back up the files listed below.

_{This page is licensed: CC BY-SA / Gnu FDL}

Using Encryption and Compression Tools With mariadb-backup

mariadb-backup supports streaming to stdout with the --stream=xbstream option. This option allows easy integration with popular encryption and compression tools. Below are several examples.

Encrypting and Decrypting Backup With openssl

The following example creates an AES-encrypted backup, protected with the password "mypass" and stores it in a file "backup.xb.enc":

To decrypt and unpack this backup into the current directory, the following command can be used:

Compressing and Decompressing Backup With gzip

This example compresses the backup without encrypting:

We can decompress and unpack the backup as follows:

Compressing and Encrypting Backup, Using gzip and openssl

This example adds a compression step before the encryption, otherwise looks almost identical to the previous example:

We can decrypt, decompress and unpack the backup as follow (note gzip -d in the pipeline):

Compressing and Encrypting with 7Zip

7zip archiver is a popular utility (especially on Windows) that supports reading from standard output, with the --si option, and writing to stdout with the -so option, and can thus be used together with mariadb-backup.

Compressing backup with the 7z command line utility works as follows:

Uncompress and unpack the archive with

7z also has builtin AES-256 encryption. To encrypt the backup from the previous example using password SECRET, add -pSECRET to the 7z command line.

Compressing with zstd

Compress

Decompress , unpack

Encrypting With GPG

Encryption

Decrypt, unpack

Interactive Input for Passphrases

Most of the described tools also provide a way to enter a passphrase interactively (although 7zip does not seem to work well when reading input from stdin). Please consult documentation of the tools for more info.

Writing extra status files

By default files like xtrabackup_checkpoints are also written to the output stream only, and so would not be available for taking further incremental backups without prior extraction from the compressed or encrypted stream output file.

To avoid this these files can additionally be written to a directory that can then be used as input for further incremental backups using the --extra-lsndir=... option.

See also e.g: Combining incremental backups with streaming output

_{This page is licensed: CC BY-SA / Gnu FDL}

How mariadb-backup Works

This is a description of the different stages in mariadb-backup, what they do and why they are needed.

Note that a few items are marked with TODO; these are things we are working on and are in next version of mariadb-backup.

Execution Stages

Initialization Phase

Connect to mysqld instance, find out important variables (datadir ,InnoDB pagesize, encryption keys, encryption plugin etc)
Scan the database directory, datadir, looking for InnoDB tablespaces, load the tablespaces (basically, it is an “open” in InnoDB sense)
If --lock-ddl-per-table is used:
- Do MDL locks, for InnoDB tablespaces that we want to copy. This is to ensure that there are no ALTER, RENAME , TRUNCATE or DROP TABLE on any of the tables that we want to copy.
- This is implemented with:

If lock-ddl-per-table is not done, then mariadb-backup would have to know all tables that were created or altered during the backup. See .

Redo Log Handling

Start a dedicated thread in mariadb-backup to copy InnoDB redo log (ib_logfile*).

This is needed to record all changes done while the backup is running. (The redo log logically is a single circular file, split into innodb_log_files_in_group files.)
The log is also used to see detect if any truncate or online alter tables are used.
The assumption is that the copy thread are able to keep up with server. It should always be able keep up, if the redo log is big enough.

Copy-phase for InnoDB Tablespaces

Copy all selected tablespaces, file by file, in dedicated threads in mariadb-backup without involving the mysqld server.
This is special “careful” copy, it looks for page-level consistency by checking the checksum.
The files are not point-in-time consistent as data may change during copy.
The idea is that InnoDB recovery would make it point-in-time consistent.
Copy Aria log files (TODO)

Create a Consistent Backup Point

Execute FLUSH TABLE WITH READ LOCK. This is default, but may be omitted with the -–no-lock parameter. The reason why FLUSH is needed is to ensure that all tables are in a consistent state at the exact same point in time, independent of storage engine.
If --lock-ddl-per-table is used and there is a user query waiting for MDL, the user query are killed to resolve a deadlock. Note that these are only queries of type ALTER, DROP, TRUNCATE or RENAME TABLE. ()

Last Copy Phase

Copy .frm, MyISAM, Aria and other storage engine files
If MyRocks is used, create rocksdb checkpoint via "set rocksdb_create_checkpoint=$rocksdb_data_dir/mariadb-backup_rocksdb_checkpoint " command. The result of it is a directory with hardlinks to MyRocks files. Copy the checkpoint directory to the backup (or create hardlinks in backup directory is on the same partition as data directory). Remove the checkpoint directory.
Copy tables that were created while the backup was running and do rename files that were changed during backup (since )
Copy the rest of InnoDB redo log, stop redo-log-copy thread
Copy changes to Aria log files (They are append only, so this is easy to do) (TODO)
Write some metadata info (binlog position)

Release Locks

If FLUSH TABLE WITH READ LOCK was done:
- execute: UNLOCK TABLES
If --lock-ddl-per-table was done:
- execute COMMIT

Handle Log Tables (TODO)

If log tables exists:
- Take MDL lock for log tables
- Copy part of log tables that wasn't copied before
- Unlock log tables

Notes

If FLUSH TABLE WITH READ LOCK is not used, then only InnoDB tables are consistent (not the privilege tables in the mysql database or the binary log). The backup point depends on the content of the redo log within the backup itself.

_{This page is licensed: CC BY-SA / Gnu FDL}

Replication as a Backup Solution

can be used to support the strategy.

Replication alone is not sufficient for backup. It assists in protecting against hardware failure on the primary server, but does not protect against data loss. An accidental or malicious DROP DATABASE or TRUNCATE TABLE statement are replicated onto the replica as well. Care needs to be taken to prevent data getting out of sync between the primary and the replica.

Replication is most commonly used to support backups as follows:

A primary server replicates to a replica
Backups are then run off the replica without any impact on the primary.

Backups can have a significant effect on a server, and a high-availability primary may not be able to be stopped, locked or simply handle the extra load of a backup. Running the backup from a replica has the advantage of being able to shutdown or lock the replica and perform a backup without any impact on the primary server.

Note that when backing up off a replica server, it is important to ensure that the servers keep the data in sync. See for example for a situation when identical statements can result in different data on a replica and a primary.

Tables

Manage tables in MariaDB Server. This section details creating, altering, and dropping tables, along with understanding data types and storage engines for optimal database design.

Partitioning Tables

Optimize large tables in MariaDB Server with partitioning. Learn how to divide tables into smaller, manageable parts for improved performance, easier maintenance, and scalability.

Partition Pruning and Selection

When a WHERE clause is related to the partitioning expression, the optimizer knows which partitions are relevant for the query. Other partitions will not be read. This optimization is called partition pruning.

can be used to know which partitions are read for a given query. A column called partitions will contain a comma-separated list of the accessed partitions. For example:

Sometimes the WHERE clause does not contain the necessary information to use partition pruning, or the optimizer cannot infer this information. However, we may know which partitions are relevant for the query. Since , we can force MariaDB to only access the specified partitions by adding a PARTITION clause. This feature is called partition selection. For example:

The PARTITION clause is supported for all DML statements:

Partition Pruning and Triggers

In general, partition pruning is applied to statements contained in .

However, note that if a BEFORE INSERT or BEFORE UPDATE trigger is defined on a table, MariaDB doesn't know in advance if the columns used in the partitioning expression are changed. For this reason, it is forced to lock all partitions.

_{This page is licensed: CC BY-SA / Gnu FDL}

Partitioning Types

Explore different partitioning types for MariaDB Server tables. Understand range, list, hash, and key partitioning to optimize data management and improve query performance.

Partitioning Types Overview

A partitioning type determines how a partitioned table's rows are distributed across partitions. Some partition types require the user to specify a partitioning expression that determines in which partition a row are stored.

The size of individual partitions depends on the partitioning type. Read and write performance are affected by the partitioning expression. Therefore, these choices should be made carefully.

Partitioning Types

MariaDB supports the following partitioning types:

RANGE
LIST
RANGE COLUMNS and LIST COLUMNS
HASH
LINEAR HASH
KEY
LINEAR KEY
SYSTEM_TIME

HASH Partitioning Type

Syntax

PARTITION BY HASH (partitioning_expression)
[PARTITIONS(number_of_partitions)]

Description

HASH partitioning is a form of partitioning in which the server takes care of the partition in which to place the data, ensuring an even distribution among the partitions.

It requires a column value, or an expression based on a column value, which is hashed, as well as the number of partitions into which to divide the table.

partitioning_expression needs to return a non-constant, deterministic integer. It is evaluated for each insert and update, so overly complex expressions can lead to performance issues. A hashing function operating on a single column, and where the value changes consistently with the column value, allows for easy pruning on ranges of partitions, and is usually a better choice. For this reason, using multiple columns in a hashing expression is not usually recommended.

number_of_partitions is a positive integer specifying the number of partitions into which to divide the table. If the PARTITIONS clause is omitted, the default number of partitions is one.

Determining the Partition

To determine which partition to use, the following calculation is performed: MOD(partitioning_expression, number_of_partitions)

For example, if the expression is TO_DAYS(datetime_column) and the number of partitions is 5, inserting a datetime value of '2023-11-15' would determine the partition as follows:

TO_DAYS('2023-11-15') gives a value of 739204
MOD(739204,5) returns 4 so the 4th partition is used.

HASH partitioning making use of the modulus of the hashing function's value. The LINEAR HASH partitioning type is similar, using a powers-of-two algorithm. Data is more likely to be evenly distributed over the partitions than with the LINEAR HASH partitioning type, however, adding, dropping, merging and splitting partitions is much slower.

Examples

CREATE OR REPLACE TABLE t1 (c1 INT, c2 DATETIME) 
  PARTITION BY HASH(TO_DAYS(c2)) 
  PARTITIONS 5;

Using the Information Schema PARTITIONS Table for more information:

INSERT INTO t1 VALUES (1,'2023-11-15');

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS 
  WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          0 |
| p4             |          1 |
+----------------+------------+

KEY Partitioning Type

Syntax

PARTITION BY KEY ([column_names])
[PARTITIONS (number_of_partitions)]

Description

Partitioning by key is a type of partitioning that is similar to and can be used in a similar way as partitioning by hash.

KEY takes an optional list of column_names, and the hashing function is given by the server.

Just like HASH partitioning, in KEY partitioning the server takes care of the partition and ensures an even distribution among the partitions. However, the largest difference is that KEY partitioning makes use of column_names, and cannot accept a partitioning_expression which is based on column_names, in contrast to HASH partitioning, which can.

If no column_names are specified, the table's primary key is used if present, or not null unique key if no primary key is present. If neither of these keys are present, not specifying any column_names will result in ERROR 1488 (HY000): Field in list of fields for partition function not found in table

Unlike other partitioning types, columns used for partitioning by KEY are not limited to integer or NULL values.

KEY partitions do not support column index prefixes. Any columns in the partitioning key that make use of column prefixes are not used (see also MDEV-32727).

Example

CREATE OR REPLACE TABLE t1 (v1 INT)
  PARTITION BY KEY (v1)
  PARTITIONS 2;

CREATE OR REPLACE TABLE t1 (v1 INT, v2 INT)
  PARTITION BY KEY (v1,v2)
  PARTITIONS 2;

CREATE OR REPLACE TABLE t1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;

CREATE OR REPLACE TABLE t1 (
    id INT NOT NULL UNIQUE KEY,
    name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;

The unique key must be NOT NULL:

CREATE OR REPLACE TABLE t1 (
    id INT NULL UNIQUE KEY,
    name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table

KEY requires column_values if no primary key or not null unique key is present:

CREATE OR REPLACE TABLE t1 (
    id INT NULL UNIQUE KEY,
    name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table

CREATE OR REPLACE TABLE t1 (
    id INT NULL UNIQUE KEY,
    name VARCHAR(5)
)
PARTITION BY KEY(name)
PARTITIONS 2;

Primary key columns with index prefixes are silently ignored, so the following two queries are equivalent:

CREATE OR REPLACE TABLE t1 (
    a VARCHAR(10),
    b VARCHAR(10),
    c VARCHAR(10),
    PRIMARY KEY (a(5), b, c(5))
) PARTITION BY KEY() PARTITIONS 2;

CREATE OR REPLACE TABLE t1 (
    a VARCHAR(10),
    b VARCHAR(10),
    c VARCHAR(10),
    PRIMARY KEY (b)
) PARTITION BY KEY() PARTITIONS 2;

a(5) and c(5) are silently ignored in the former.

If all columns use index prefixes, the statement fails with a slightly misleading error:

CREATE OR REPLACE TABLE t1 (
    a VARCHAR(10),
    b VARCHAR(10),
    c VARCHAR(10),
    PRIMARY KEY (a(5), b(5), c(5))
) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

_{This page is licensed: CC BY-SA / Gnu FDL}

LINEAR HASH Partitioning Type

Syntax

PARTITION BY LINEAR HASH (partitioning_expression)
[PARTITIONS(number_of_partitions)]

Description

LINEAR HASH partitioning is a form of partitioning, similar to HASH partitioning, in which the server takes care of the partition in which to place the data, ensuring a relatively even distribution among the partitions.

LINEAR HASH partitioning makes use of a powers-of-two algorithm, while HASH partitioning uses the modulus of the hashing function's value. Adding, dropping, merging and splitting partitions is much faster than with the HASH partitioning type, however, data is less likely to be evenly distributed over the partitions.

Example

CREATE OR REPLACE TABLE t1 (c1 INT, c2 DATETIME) 
  PARTITION BY LINEAR HASH(TO_DAYS(c2)) 
  PARTITIONS 5;

_{This page is licensed: CC BY-SA / Gnu FDL}

LINEAR KEY Partitioning Type

Syntax

LINEAR PARTITION BY KEY ([column_names])
[PARTITIONS (number_of_partitions)]

Description

LINEAR KEY partitioning is a form of partitioning, similar to KEY partitioning.

LINEAR KEY partitioning makes use of a powers-of-two algorithm, while KEY partitioning uses modulo arithmetic, to determine the partition number.

Adding, dropping, merging and splitting partitions is much faster than with the KEY partitioning type, however, data is less likely to be evenly distributed over the partitions.

Example

CREATE OR REPLACE TABLE t1 (v1 INT)
  PARTITION BY LINEAR KEY (v1)
  PARTITIONS 2;

_{This page is licensed: CC BY-SA / Gnu FDL}

LIST Partitioning Type

LIST partitioning is conceptually similar to RANGE partitioning. In both cases you decide a partitioning expression (a column, or a slightly more complex calculation) and use it to determine which partitions will contain each row. However, with the RANGE type, partitioning is done by assigning a range of values to each partition. With the LIST type, we assign a set of values to each partition. This is usually preferred if the partitioning expression can return a limited set of values.

A variant of this partitioning method, LIST COLUMNS, allows us to use multiple columns and more datatypes.

Syntax

The last part of a CREATE TABLE statement can be the definition of the new table's partitions. In the case of LIST partitioning, the syntax is the following:

PARTITION BY LIST (partitioning_expression)
(
	PARTITION partition_name VALUES IN (value_list),
	[ PARTITION partition_name VALUES IN (value_list), ... ]
        [ PARTITION partition_name DEFAULT ]
)

PARTITION BY LIST indicates that the partitioning type is LIST.

The partitioning_expression is an SQL expression that returns a value from each row. In the simplest cases, it is a column name. This value is used to determine which partition should contain a row.

partition_name is the name of a partition.

value_list is a list of values. If partitioning_expression returns one of these values, the row are stored in this partition. If we try to insert something that does not belong to any of these value lists, the row are rejected with an error.

The DEFAULT partition catches all records which do not fit into other partitions.

Use Cases

LIST partitioning can be useful when we have a column that can only contain a limited set of values. Even in that case, RANGE partitioning could be used instead; but LIST partitioning allows us to equally distribute the rows by assigning a proper set of values to each partition.

Example

CREATE OR REPLACE TABLE t1 (
  num TINYINT(1) NOT NULL
)
  ENGINE = InnoDB
  PARTITION BY LIST (num) (
    PARTITION p0 VALUES IN (0,1),
    PARTITION p1 VALUES IN (2,3),
    PARTITION p2 DEFAULT
  );

_{This page is licensed: CC BY-SA / Gnu FDL}

RANGE COLUMNS and LIST COLUMNS Partitioning Types

RANGE COLUMNS and LIST COLUMNS are variants of, respectively, and . With these partitioning types there is not a single partitioning expression; instead, a list of one or more columns is accepted. The following rules apply:

The list can contain one or more columns.
Columns can be of any , , , and types.
Only bare columns are permitted; no expressions.

All the specified columns are compared to the specified values to determine which partition should contain a specific row. See below for details.

Syntax

The last part of a statement can be definition of the new table's partitions. In the case of RANGE COLUMNS partitioning, the syntax is the following:

The syntax for LIST COLUMNS is the following:

partition_name is the name of a partition.

Comparisons

To determine which partition should contain a row, all specified columns are compared to each partition definition.

With LIST COLUMNS, a row matches a partition if all row values are identical to the specified values. At most one partition can match the row.

With RANGE COLUMNS, a row matches a partition if all row values are less than the specified values. The first partition that matches the row values are used.

The DEFAULT partition catches all records which do not fit in other partitions. Only one DEFAULT partition is allowed.

Examples

RANGE COLUMNS partition:

LIST COLUMNS partition:

_{This page is licensed: CC BY-SA / Gnu FDL}

Partitioning Limitations

The following limitations apply to partitioning in MariaDB:

Each table can contain a maximum of 8192 partitions. Until , the limit was 1024.
Queries are never parallelized, even when they involve multiple partitions.
A table can only be partitioned if the storage engine supports partitioning.
All partitions must use the same storage engine. For a workaround, see Using CONNECT - Partitioning and Sharding.
A partitioned table cannot contain, or be referenced by, foreign keys.
The query cache is not aware of partitioning and partition pruning. Modifying a partition will invalidate the entries related to the whole table.
Updates can run more slowly when binlog_format=ROW and a partitioned table is updated than an equivalent update of a non-partitioned table.
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
In versions prior to , it is not possible to create partitions on tables that contain GEOMETRY types.

Partitions Files

A partitioned table is stored in multiple files. By default, these files are stored in the MariaDB (or InnoDB) data directory. It is possible to keep them in different paths by specifying table options. This is useful to store different partitions on different devices.

Note that, if the server system variable is set to 0 at the time of the table creation, all partitions are stored in the system tablespace.

The following files exist for each partitioned tables:

File name

Notes

For example, an InnoDB table with 4 partitions will have the following files:

If we convert the table to MyISAM, we will have these files:

_{This page is licensed: CC BY-SA / Gnu FDL}

Partitions Metadata

The table in the database contains information about partitions.

The statement contains a Create_options column, that contains the string 'partitioned' for partitioned tables.

The statement returns the statement that can be used to re-create a table, including the partitions definition.

_{This page is licensed: CC BY-SA / Gnu FDL}

Stored Routines

Automate tasks in MariaDB Server with stored routines. Learn to create and manage stored procedures and functions for enhanced database efficiency and code reusability.

Stored Procedures

Master stored procedures in MariaDB Server. This section covers creating, executing, and managing these powerful routines to encapsulate complex logic and improve application performance.

ALTER PROCEDURE

Syntax

ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

Description

This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement. To make such changes, you must drop and re-create the procedure using either CREATE OR REPLACE PROCEDURE (since ) or DROP PROCEDURE and CREATE PROCEDURE (MariaDB 10.1.2 and before).

You must have the ALTER ROUTINE privilege for the procedure. By default, that privilege is granted automatically to the procedure creator. See Stored Routine Privileges.

Example

ALTER PROCEDURE simpleproc SQL SECURITY INVOKER;

DROP PROCEDURE

Syntax

DROP PROCEDURE [IF EXISTS] sp_name

Description

This statement is used to drop a stored procedure. That is, the specified routine is removed from the server along with all privileges specific to the procedure. You must have the ALTER ROUTINE privilege for the routine. If the automatic_sp_privileges server system variable is set, that privilege and EXECUTE are granted automatically to the routine creator - see Stored Routine Privileges.

The IF EXISTS clause is a MySQL/MariaDB extension. It prevents an error from occurring if the procedure or function does not exist. ANOTE is produced that can be viewed with SHOW WARNINGS.

While this statement takes effect immediately, threads which are executing a procedure can continue execution.

Examples

DROP PROCEDURE simpleproc;

IF EXISTS:

DROP PROCEDURE simpleproc;
ERROR 1305 (42000): PROCEDURE test.simpleproc does not exist

DROP PROCEDURE IF EXISTS simpleproc;
Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1305 | PROCEDURE test.simpleproc does not exist |
+-------+------+------------------------------------------+

Stored Functions

Utilize stored functions in MariaDB Server. This section details creating, using, and managing user-defined functions to extend SQL capabilities and streamline data manipulation.

Stored Routine Privileges

It's important to give careful thought to the privileges associated with stored functions and stored procedures. The following is an explanation of how they work.

Creating Stored Routines

To create a stored routine, the CREATE ROUTINE privilege is needed. The SUPER privilege is required if a DEFINER is declared that's not the creator's account (see DEFINER clause below). The SUPER privilege is also required if statement-based binary logging is used. See Binary Logging of Stored Routines for more details.

Altering Stored Routines

To make changes to, or drop, a stored routine, the ALTER ROUTINE privilege is needed. The creator of a routine is temporarily granted this privilege if they attempt to change or drop a routine they created, unless the automatic_sp_privileges variable is set to 0 (it defaults to 1).
The SUPER privilege is also required if statement-based binary logging is used. See Binary Logging of Stored Routines for more details.

Running Stored Routines

To run a stored routine, the EXECUTE privilege is needed. This is also temporarily granted to the creator if they attempt to run their routine unless the automatic_sp_privileges variable is set to 0.
The SQL SECURITY clause (by default DEFINER) specifies what privileges are used when a routine is called. If SQL SECURITY is INVOKER, the function body are evaluated using the privileges of the user calling the function. If SQL SECURITY is DEFINER, the function body is always evaluated using the privileges of the definer account. DEFINER is the default. Thus, by default, users who can access the database associated with the stored routine can also run the routine, and potentially perform operations they wouldn't normally have permissions for.
The creator of a routine is the account that ran the CREATE FUNCTION or CREATE PROCEDURE statement, regardless of whether a DEFINER is provided. The definer is by default the creator unless otherwise specified.
The server automatically changes the privileges in the mysql.proc table as required, but will not look out for manual changes.

DEFINER Clause

If left out, the DEFINER is treated as the account that created the stored routine or view. If the account creating the routine has the SUPER privilege, another account can be specified as the DEFINER.

SQL SECURITY Clause

This clause specifies the context the stored routine or view will run as. It can take two values - DEFINER or INVOKER. DEFINER is the account specified as the DEFINER when the stored routine or view was created (see the section above). INVOKER is the account invoking the routine or view.

As an example, let's assume a routine, created by a superuser who's specified as the DEFINER, deletes all records from a table. If SQL SECURITY=DEFINER, anyone running the routine, regardless of whether they have delete privileges, are able to delete the records. If SQL SECURITY = INVOKER, the routine will only delete the records if the account invoking the routine has permission to do so.

INVOKER is usually less risky, as a user cannot perform any operations they're normally unable to. However, it's not uncommon for accounts to have relatively limited permissions, but be specifically granted access to routines, which are then invoked in the DEFINER context.

Dropping Stored Routines

All privileges that are specific to a stored routine are dropped when a DROP FUNCTION or DROP ROUTINE is run. However, if a CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE is used to drop and replace and the routine, any privileges specific to that routine will not be dropped.

DROP FUNCTION

Syntax

Description

The DROP FUNCTION statement is used to drop a or a user-defined function (UDF). That is, the specified routine is removed from the server, along with all privileges specific to the function. You must have the ALTER ROUTINE for the routine in order to drop it. If the server system variable is set, both the ALTER ROUTINE and EXECUTE privileges are granted automatically to the routine creator - see .

IF EXISTS

The IF EXISTS clause is a MySQL/MariaDB extension. It prevents an error from occurring if the function does not exist. ANOTE is produced that can be viewed with .

For dropping a (UDF), see .

Examples

Stored Function Limitations

The following restrictions apply to .

All of the restrictions listed in .
Any statements that return a result set are not permitted. For example, a regular is not permitted, but a is. A cursor and statement is permitted.
statements are not permitted.
Statements that perform explicit or implicit commits or rollbacks are not permitted
Cannot be used recursively.
Cannot make changes to a table that is already in use (reading or writing) by the statement invoking the stored function.
Cannot refer to a temporary table multiple times under different aliases, even in different statements.
ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT statement which are in a stored function cannot refer to a savepoint which has been defined out of the current function.
Prepared statements (, , ) cannot be used, and therefore nor can statements be constructed as strings and then executed.

_{This page is licensed: CC BY-SA / Gnu FDL}

Stored Routine Limitations

The following SQL statements are not permitted inside any (, , or ).

; you can use instead.
and .
is permitted, but the statement is handled as a regular .
and .
References to within prepared statements inside a stored routine (use instead).
is treated as the beginning of a block, not a transaction, so needs to be used instead.
The number of permitted recursive calls is limited to . If this variable is 0 (default), recursivity is disabled. The limit does not apply to stored functions.
Most statements that are not permitted in prepared statements are not permitted in stored programs. See for a list of statements that can be used. , and are exceptions, and may be used in stored routines.

There are also further limitations specific to the kind of stored routine.

Note that, if a stored program calls another stored program, the latter will inherit the caller's limitations. So, for example, if a stored procedure is called by a stored function, that stored procedure will not be able to produce a result set, because stored functions can't do this.

Storage Engines

Understand MariaDB Server's storage engines. Explore the features and use cases of InnoDB, Aria, MyISAM, and other engines to choose the best option for your specific data needs.

Doing Time with MariaDB

The recording of date and time in a MariaDB database is a very common requirement. For gathering temporal data, one needs to know which type of columns to use in a table. More importantly is knowing how to record chronological data and how to retrieve it in various formats. Although this is a seemingly basic topic, there are many built-in time functions that can be used for more accurate SQL statements and better formatting of data. In this article we will explore these various aspects of how to do time with MariaDB.

About Time

Since date and time are only numeric strings, they can be stored in a regular character column. However, by using temporal data type columns, you can make use of several built-in functions offered by MariaDB. Currently, there are five temporal data types available: DATE, TIME, DATETIME, TIMESTAMP, and YEAR. The DATE column type is for recording the date only and is basically in this format: yyyy-mm-dd. The TIME column type is for recording time in this format: hhh:mm:ss. To record a combination of date and time, there is the DATETIME column type: yyyy-mm-dd hh:mm:ss.

The TIMESTAMP column is similar to DATETIME, but it's a little limited in its range of allowable time. It starts at the Unix epoch time (1970-01-01) and ends on 2106-02-07. Finally, the YEAR data type is for recording only the year in a column: yy or yyyy. For the examples in this article, DATE, TIME, and DATETIME columns are used. The database that are referenced is for a fictitious psychiatry practice that keeps track of its patients and billable hours in MariaDB.

The TIMESTAMP column is similar to DATETIME, but it's a little limited in its range of allowable time. It starts at the Unix epoch time (i.e., 1970-01-01) and ends on 2038-01-19. Finally, the YEAR data type is for recording only the year in a column: yy or yyyy. For the examples in this article, DATE, TIME, and DATETIME columns are used. The database that are referenced is for a fictitious psychiatry practice that keeps track of its patients and billable hours in MariaDB.

Telling Time

To record the current date and time in a MariaDB table, there are a few built-in functions that may be used. First, to record the date there are the functions CURRENT_DATE and CURDATE( ) (depending on your style), which both produce the same results (e.g., 2017-08-01). Notice that CURDATE( ) requires parentheses and the other does not. With many functions a column name or other variables are placed inside of the parentheses to get a result. With functions like CURDATE( ), there is nothing that may go inside the parenthesis. Since these two functions retrieve the current date in the format of the DATE column type, they can be used to fill in a DATE column when inserting a row:

INSERT INTO billable_work
(doctor_id, patient_id, session_date)
VALUES('1021', '1256', CURRENT_DATE);

The column session_date is a DATE column. Notice that there are no quotes around the date function. If there were it would be taken as a literal value rather than a function. Incidentally, I've skipped discussing how the table was set up. If you're not familiar with how to set up a table, you may want to read the MariaDB Basics article. To see what was just recorded by the INSERT statement above, the following may be entered (results follow):

SELECT rec_id, doctor_id, 
patient_id, session_date
FROM billable_work
WHERE rec_id=LAST_INSERT_ID();

+--------+-----------+------------+--------------+
| rec_id | doctor_id | patient_id | session_date |
+--------+-----------+------------+--------------+
|   2462 | 1021      | 1256       | 2017-08-23   |
+--------+-----------+------------+--------------+

Notice in the billable_work table that the primary key column (i.e., rec_id) is an automatically generated and incremental number column (i.e., AUTO_INCREMENT). As long as another record is not created or the user does not exit from the mariadb client or otherwise end the session, the LAST_INSERT_ID( ) function will retrieve the value of the rec_id for the last record entered by the user.

To record the time of an appointment for a patient in a time data type column, CURRENT_TIME or CURTIME( ) are used in the same way to insert the time. The following is entered to update the row created above to mark the starting time of the appointment—another SELECT statement follows with the results:

UPDATE billable_work
SET session_time=CURTIME()
WHERE rec_id='2462';

SELECT patient_id, session_date, session_time
FROM billable_work
WHERE rec_id='2462';

+------------+--------------+--------------+
| patient_id | session_date | session_time |
+------------+--------------+--------------+
| 1256       | 2017-08-23   | 10:30:23     |
+------------+--------------+--------------+

The column session_time is a time column. To record the date and time together in the same column, CURRENT_TIMESTAMP or SYSDATE( ) or NOW( ) can be used. All three functions produce the same time format: yyyy-mm-dd hh:mm:ss. Therefore, the column's data type would have to be DATETIME to use them.

How to get a Date

Although MariaDB records the date in a fairly agreeable format, you may want to present the date when it's retrieved in a different format. Or, you may want to extract part of the date, such as only the day of the month. There are many functions for reformatting and selectively retrieving date and time information. To start off with, let's select a column with a data type of DATE and look at the functions available for retrieving each component. To extract the year, there's the YEAR( ) function. For extracting just the month, the MONTH( ) function could be called upon. And to grab the day of the month, DAYOFMONTH( ) will work. Using the record entered above, here's what an SQL statement and its results would look like in which the session date is broken up into separate parts, but in a different order:

SELECT MONTH(session_date) AS Month,
DAYOFMONTH(session_date) AS Day,
YEAR(session_date) AS Year
FROM billable_work
WHERE rec_id='2462';

+-------+------+------+
| Month | Day  | Year |
+-------+------+------+
|     8 |   23 | 2017 |
+-------+------+------+

For those who aren't familiar with the keyword AS, it's used to label a column's output and may be referenced within an SQL statement. Splitting up the elements of a date can be useful in analyzing a particular element. If the bookkeeper of the fictitious psychiatry office needed to determine if the day of the week of each session was on a Saturday because the billing rate would be higher (time and a half), the DAYOFWEEK( ) function could be used. To spice up the examples, let's wrap the date function up in an IF( ) function that tests for the day of the week and sets the billing rate accordingly.

SELECT patient_id AS 'Patient ID',
session_date AS 'Date of Session',
IF(DAYOFWEEK(session_date)=6, 1.5, 1.0)
  AS 'Billing Rate' 
FROM billable_work
WHERE rec_id='2462';

+-------------+-----------------+--------------+
| Patient ID  | Date of Session | Billing Rate |
+-------------+-----------------+--------------+
|        1256 |    2017-08-23   |          1.5 |
+-------------+-----------------+--------------+

Since we've slipped in the IF( ) function, we should explain it's format. The test condition is listed first within the parentheses. In this case, the test is checking if the session date is the sixth day of the week. Then, what MariaDB should display is given if the test passes, followed by the result if it fails.

Similar to the DAYOFWEEK( ) function, there's also WEEKDAY( ). The only difference is that for DAYOFWEEK( ) the first day of the week is Sunday—with WEEKDAY( ) the first day is Monday. Both functions represent the first day with 0 and the last with 6. Having Saturday and Sunday symbolized by 5 and 6 can be handy in constructing an IF statement that has a test component like "WEEKDAY(session_date) > 4" to determine if a date is a weekend day. This is cleaner than testing for values of 0 and 6.

There is a function for determining the day of the year: DAYOFYEAR( ). It's not used often, but it is available if you ever need it. Occasionally, though, knowing the quarter of a year for a date can be useful for financial accounting. Rather than set up a formula in a script to determine the quarter, the QUARTER( ) function can do this easily. For instance, suppose an accountant wants a list of a doctor's sessions for each patient for the previous quarter. These three SQL statements could be entered in sequence to achieve the results that follow:

SET @LASTQTR:=IF((QUARTER(CURDATE())-1)=0, 
4, QUARTER(CURDATE())-1);

SET @YR:=IF(@LASTQTR=4, 
YEAR(NOW())-1, YEAR(NOW()));

SELECT patient_id AS 'Patient ID', 
COUNT(session_time) 
  AS 'Number of Sessions'
FROM billable_work
WHERE QUARTER(session_date) = @LASTQTR
  AND YEAR(session_date) = @YR
  AND doctor_id='1021'
GROUP BY patient_id
ORDER BY patient_id LIMIT 5;

+------------+--------------------+
| Patient ID | Number of Sessions |
+------------+--------------------+
| 1104       |                 10 |
| 1142       |                  7 |
| 1203       |                 18 |
| 1244       |                  6 |
| 1256       |                 12 |
+------------+--------------------+

This example is the most complicated so far. But it's not too difficult to understand if we pull it apart. The first SQL statement sets up a user variable containing the previous quarter (i.e., 1, 2, 3, or 4). This variable are needed in the other two statements. The IF( ) clause in the first statement checks if the quarter of the current date minus one is zero. It will equal zero when it's run during the first quarter of a year. During a first quarter, of course, the previous quarter is the fourth quarter of the previous year. So, if the equation equals zero, then the variable @LASTQTR is set to 4. Otherwise, @LASTQTR is set to the value of the current quarter minus one. The second statement is necessary to ensure that the records for the correct year are selected. So, if @LASTQTR equals four, then @YR needs to equal last year. If not, @YR is set to the current year. With the user variables set to the correct quarter and year, the SELECT statement can be entered. The COUNT( ) function counts the number of appointments that match the WHERE clause for each patient based on the GROUP BY clause. The WHERE clause looks for sessions with a quarter that equals @LASTQTR and a year that equals @YR, as well as the doctor's identification number. In summary, what we end up with is a set of SQL statements that retrieve the desired information regardless of which quarter or year it's entered.

What is the Time?

The last section covered how to retrieve pieces of a date column. Now let's look at how to do the same with a time column. To extract just the hour of a time saved in MariaDB, the HOUR( ) function could be used. For the minute and second, there's MINUTE( ) and SECOND( ). Let's put them all together in one straightforward SELECT statement:

SELECT HOUR(session_time) AS Hour, 
MINUTE(session_time) AS Minute, 
SECOND(session_time) AS Second
FROM billable_work
WHERE rec_id='2462';

+------+--------+--------+
| Hour | Minute | Second |
+------+--------+--------+
|   10 |     30 |     00 |
+------+--------+--------+

Date & Time Combined

All of the examples given so far have involved separate columns for date and time. The EXTRACT( ) function, however, will allow a particular component to be extracted from a combined column type (i.e., DATETIME or TIMESTAMP). The format is EXTRACT(date_type FROM date_column) where date_type is the component to retrieve and date_column is the name of the column from which to extract data. To extract the year, the date_type would be YEAR; for month, MONTH is used; and for day, there's DAY. To extract time elements, HOUR is used for hour, MINUTE for minute, and SECOND for second. Although that's all pretty simple, let's look at an example. Suppose the table billable_work has a column called appointment (a datetime column) that contains the date and time for which the appointment was scheduled (as opposed to the time it actually started in session_time). To get the hour and minute for a particular date, the following SQL statement will suffice:

SELECT patient_name AS Patient, 
EXTRACT(HOUR FROM appointment) AS Hour, 
EXTRACT(MINUTE FROM appointment) AS Minute
FROM billable_work, patients
WHERE doctor_id='1021' 
  AND EXTRACT(MONTH FROM appointment)='8' 
  AND EXTRACT(DAY FROM appointment)='30'
  AND billable_work.patient_id =
    patients.patient_id;

This statement calls upon another table (patients) which holds patient information such as their names. It requires a connecting point between the tables (i.e., the patient_id from each table). If you're confused on how to form relationships between tables in a SELECT statement, you may want to go back and read the Getting Data from MariaDB article. The SQL statement above would be used to retrieve the appointments for one doctor for one day, giving results like this:

+-------------------+------+--------+
| Patient           | Hour | Minute |
+-------------------+------+--------+
| Michael Zabalaoui |   10 |     00 |
| Jerry Neumeyer    |   11 |     00 |
| Richard Stringer  |   13 |     30 |
| Janice Sogard     |   14 |     30 |
+-------------------+------+--------+

In this example, the time elements are separated and they don't include the date. With the EXTRACT( ) function, however, you can also return combined date and time elements. There is DAY_HOUR for the day and hour; there's DAY_MINUTE for the day, hour, and minute; DAY_SECOND for day, hour, minute, and second; and YEAR_MONTH for year and month. There are also some time only combinations: HOUR_MINUTE for hour and minute; HOUR_SECOND for hour, minute, and second; and MINUTE_SECOND for minute and second. However, there's not a MONTH_DAY to allow the combining of the two extracts in the WHERE clause of the last SELECT statement above. Nevertheless, we'll modify the example above and use the HOUR_MINUTE date_type to retrieve the hour and minute in one resulting column. It would only require the second and third lines to be deleted and replaced with this:

...
EXTRACT(HOUR_MINUTE FROM appointment) 
  AS Appointment 
...

+-------------------+-------------+
| Patient           | Appointment |
+-------------------+-------------+
| Michael Zabalaoui |        1000 |
| Jerry Neumeyer    |        1100 |
| Richard Stringer  |        1330 |
| Janice Sogard     |        1430 |
+-------------------+-------------+

The problem with this output, though, is that the times aren't very pleasing looking. For more natural date and time displays, there are a few simple date formatting functions available and there are the DATE_FORMAT( ) and TIME_FORMAT( ) functions.

Fine Time Pieces

The simple functions that we mentioned are used for reformatting the output of days and months. To get the date of patient sessions for August, but in a more wordier format, MONTHNAME( ) and DAYNAME( ) could be used:

SELECT patient_name AS Patient, 
CONCAT(DAYNAME(appointment), ' - ', 
  MONTHNAME(appointment), ' ', 
  DAYOFMONTH(appointment), ', ', 
  YEAR(appointment)) AS Appointment
FROM billable_work, patients
WHERE doctor_id='1021'
  AND billable_work.patient_id =
    patients.patient_id
  AND appointment>'2017-08-01' 
  AND appointment<'2017-08-31' 
LIMIT 1;

+-------------------+-----------------------------+
| Patient           | Appointment                 |
+-------------------+-----------------------------+
| Michael Zabalaoui | Wednesday - August 30, 2017 |
+-------------------+-----------------------------+

In this statement the CONCAT( ) splices together the results of several date functions along with spaces and other characters. The EXTRACT( ) function was eliminated from the WHERE clause and instead a simple numeric test for sessions in August was given. Although EXTRACT( ) is fairly straightforward, this all can be accomplished with less typing by using the DATE_FORMAT( ) function.

The DATE_FORMAT( ) function has over thirty options for formatting the date to your liking. Plus, you can combine the options and add your own separators and other text. The syntax is DATE_FORMAT(date_column, 'options & characters'). As an example, let's reproduce the last SQL statement by using the DATE_FORMAT( ) function for formatting the date of the appointment and for scanning for appointments in July only:

SELECT patient_name AS Patient, 
DATE_FORMAT(appointment, '%W - %M %e, %Y') 
  AS Appointment
FROM billable_work, patients
WHERE doctor_id='1021'
  AND billable_work.patient_id = 
    patients.patient_id
  AND DATE_FORMAT(appointment, '%c') = 8
LIMIT 1;

This produces the exact same output as above, but with a more succinct statement. The option %W gives the name of the day of the week. The option %M provides the month's name. The option %e displays the day of the month (%d would work, but it left-pads single-digit dates with zeros). Finally, %Y is for the four character year. All other elements within the quotes (i.e., the spaces, the dash, and the comma) are literal characters for a nicer display.

With DATE_FORMAT( ), time elements of a field also can be formatted. For instance, suppose we also wanted the hour and minute of the appointment. We would only need to change the second line of the SQL statement above (to save space, patient_name was eliminated):

SELECT 
DATE_FORMAT(appointment, '%W - %M %e, %Y at %r') 
  AS Appointment
...

+--------------------------------------------+
| Appointment                                |
+--------------------------------------------+
| Wednesday - August 30, 2017 at 02:11:19 AM |
+--------------------------------------------+

The word at was added along with the formatting option %r which gives the time with AM or PM at the end.

Although it may be a little confusing at first, once you've learned some of the common formatting options, DATE_FORMAT( ) is much easier to use than EXTRACT( ). There are many more options to DATE_FORMAT( ) that we haven't mentioned. For a complete list of the options available, see the DATE_FORMAT( ) documentation page.

Clean up Time

In addition to DATE_FORMAT( ), MariaDB has a comparable built-in function for formating only time: TIME_FORMAT( ). The syntax is the same and uses the same options as DATE_FORMAT( ), except only the time related formatting options apply. As an example, here's an SQL statement that a doctor might use at the beginning of each day to get a list of her appointments for the day:

SELECT patient_name AS Patient, 
TIME_FORMAT(appointment, '%l:%i %p') 
  AS Appointment
FROM billable_work, patients
WHERE doctor_id='1021'
  AND billable_work.patient_id = 
    patients.patient_id
  AND DATE_FORMAT(appointment, '%Y-%m-%d') =
    CURDATE();

+-------------------+-------------+
| Patient           | Appointment |
+-------------------+-------------+
| Michael Zabalaoui |    10:00 AM |
| Jerry Neumeyer    |    11:00 AM |
| Richard Stringer  |    01:30 PM |
| Janice Sogard     |    02:30 PM |
+-------------------+-------------+

The option %l provides the hours 01 through 12. The %p at the end indicates (with the AM or PM) whether the time is before or after noon. The %i option gives the minute. The colon and the space are for additional display appeal. Of course, all of this can be done exactly the same way with the DATE_FORMAT( ) function. As for the DATE_FORMAT( ) component in the WHERE clause here, the date is formatted exactly as it are with CURDATE( ) (i.e., 2017-08-30) so that they may be compared properly.

Time to End

Many developers use PHP, Perl, or some other scripting language with MariaDB. Sometimes developers will solve retrieval problems with longer scripts rather than learn precisely how to extract temporal data with MariaDB. As you can see in several of the examples here (particularly the one using the QUARTER( ) function), you can accomplish a great deal within MariaDB. When faced with a potentially complicated SQL statement, try creating it in the mariadb client first. Once you get what you need (under various conditions) and in the format desired, then copy the statement into your script. This practice can greatly help you improve your MariaDB statements and scripting code.

CC BY-SA / Gnu FDL

MariaDB Enterprise Backup

Overview

Regular and reliable backups are essential to successful recovery of mission critical applications. backup and restore operations are performed using MariaDB Enterprise Backup, an enterprise-build of .

MariaDB Enterprise Backup is compatible with MariaDB Enterprise Server.

Storage Engines and Backup Types

MariaDB Backup creates a file-level backup of data from the MariaDB Community Server data directory. This backup includes , and the encrypted and unencrypted tablespaces of supported storage engines (e.g., , , ).

MariaDB Enterprise Server implements:

Full backups, which contain all data in the database.
Incremental backups, which contain modifications since the last backup.
Partial backups, which contain a subset of the tables in the database.

Backup support is specific to storage engines. All supported storage engines enable full backup. The InnoDB storage engine additionally supports incremental backup.

Note: MariaDB Enterprise Backup does not support backups of MariaDB ColumnStore. Backup of MariaDB ColumnStore can be performed using . Backup of data ingested to MariaDB ColumnStore can also occur pre-ingestion, such as in the case of HTAP where backup could occur of transactional data in MariaDB Enterprise Server, and restore of data to MariaDB ColumnStore would then occur through reprocessing..

Nonblocking Backups

A feature of MariaDB Enterprise Backup and MariaDB Enterprise Server, non-blocking backups minimize workload impact during backups. When MariaDB Enterprise Backup connects to MariaDB Enterprise Server, staging operations are initiated to protect data during read.

Non-blocking backup functionality differs from historical backup functionality in the following ways:

MariaDB Enterprise Backup in MariaDB Enterprise Server includes enterprise-only optimizations to backup staging, including DDL statement tracking, which reduces lock-time during backups.
MariaDB Backup in MariaDB Community Server 10.4 and later will block writes, log tables, and statistics.
Older MariaDB Community Server releases used FLUSH TABLES WITH READ LOCK, which closed open tables and only allowed tables to be reopened with a read lock during the duration of backups.

Understanding Recovery

MariaDB Enterprise Backup creates complete or incremental backups of MariaDB Enterprise Server data, and is also used to restore data from backups produced using MariaDB Enterprise Backup.

Preparing Backups for Recovery

Full backups produced using MariaDB Enterprise Server are not initially point-in-time consistent, and an attempt to restore from a raw full backup will cause InnoDB to crash to protect the data.

Incremental backups produced using MariaDB Enterprise Backup contain only the changes since the last backup and cannot be used standalone to perform a restore.

To restore from a backup, you first need to prepare the backup for point-in-time consistency using the --prepare command:

Running the --prepare command on a full backup synchronizes the tablespaces, ensuring that they are point-in-time consistent and ready for use in recovery.
Running the --prepare command on an incremental backup synchronizes the tablespaces and also applies the updated data into the previous full backup, making it a complete backup ready for use in recovery.
Running the --prepare command on data that is to be used for a partial restore (when restoring only one or more selected tables) requires that you also use the --export option to create the necessary .cfg files to use in recovery.

Restore Requires Empty Data Directory

When MariaDB Enterprise Backup restores from a backup, it copies or moves the backup files into the MariaDB Enterprise Server data directory, as defined by the datadir system variable.

For MariaDB Backup to safely restore data from full and incremental backups, the data directory must be empty. One way to achieve this is to move the data directory aside to a unique directory name:

Make sure that the Server is stopped.
Move the data directory to a unique name (e.g., /var/lib/mysql-2020-01-01) OR remove the old data directory (depending on how much space you have available).
Create a new (empty) data directory (e.g., mkdir /var/lib/mysql).
Run MariaDB Backup to restore the databases into that directory.
Change the ownership of all the restored files to the correct system user (e.g., chown -R mysql:mysql /var/lib/mysql).
Start MariaDB Enterprise Server, which now uses the restored data directory.
When ready, and if you have not already done so, delete the old data directory to free disk space.

Creating the Backup User

When MariaDB Backup performs a backup operation, it not only copies files from the data directory but also connects to the running MariaDB Enterprise Server.

This connection to MariaDB Enterprise Server is used to manage locks that prevent the Server from writing to a file while being read for a backup.

MariaDB Backup establishes this connection based on the user credentials specified with the --user and --password options when performing a backup.

It is recommended that a dedicated user be created and authorized to perform backups.

MariaDB Backup requires this user to have the RELOAD, PROCESS, LOCK TABLES, and REPLICATION CLIENT privileges.

In the above example, MariaDB Backup would run on the local system that runs MariaDB Enterprise Server. Where backups may be run against a remote server, the user authentication and authorization should be adjusted.

While MariaDB Backup requires a user for backup operations, no user is required for restore operations since restores occur while MariaDB Enterprise Server is not running.

MariaDB Backup requires this user to have the RELOAD, PROCESS, LOCK TABLES, and REPLICATION CLIENT privileges.

While MariaDB Backup requires a user for backup operations, no user is required for restore operations since restores occur while MariaDB Enterprise Server is not running.

Full Backup and Restore

Full backups performed with MariaDB Backup contain all table data present in the database.

When performing a full backup, MariaDB Backup makes a file-level copy of the MariaDB Enterprise Server data directory. This backup omits log data such as the binary logs (binlog), error logs, general query logs, and slow query logs.

Performing Full Backups

When you perform a full backup, MariaDB Backup writes the backup to the --target-dir path. The directory must be empty or non-existent and the operating system user account must have permission to write to that directory. A database user account is required to perform the backup.

The version of mariadb-backup or mariadb-backup should be the same version as the MariaDB Enterprise Server version. When the version does not match the server version, errors can sometimes occur, or the backup can sometimes be unusable.

To create a backup, execute mariadb-backup or mariadb-backup with the --backup option, and provide the database user account credentials using the --user and --password options:

Subsequent to the above example, the backup is now available in the designated --target-dir path.

Preparing a Full Backup for Recovery

A raw full backup is not and must be prepared before it can be used for a restore. The backup can be prepared any time after the backup is created and before the backup is restored. However, MariaDB recommends preparing a backup immediately after taking the backup to ensure that the backup is consistent.

The backup should be prepared with the same version of MariaDB Backup that was used to create the backup.

To prepare the backup, execute mariadb-backup or mariadb-backup with the --prepare option:

For best performance, the --use-memory option should be set to the server's innodb_buffer_pool_size value.

Restoring from Full Backups

Once a full backup has been prepared to be point-in-time consistent, MariaDB Backup is used to copy backup data to the MariaDB Enterprise Server data directory.

To restore from a full backup:

Stop the MariaDB Enterprise Server
Empty the data directory
Restore from the "full" directory using the --copy-back option:

MariaDB Backup writes to the data directory as the current user, which can be changed using sudo. To confirm that restored files are properly owned by the user that runs MariaDB Enterprise Server, run a command like this (adapted for the correct user/group):

Once this is done, start MariaDB Enterprise Server:

When the Server starts, it works from the restored data directory.

Incremental Backup and Restore

Full backups of large data-sets can be time-consuming and resource-intensive. MariaDB Backup supports the use of incremental backups to minimize this impact.

While full backups are resource-intensive at time of backup, the resource burden around incremental backups occurs when preparing for restore. First, the full backup is prepared for restore, then each incremental backup is applied.

Performing Incremental Backups

When you perform an incremental backup, MariaDB Backup compares a previous full or incremental backup to what it finds on MariaDB Community Server. It then creates a new backup containing the incremental changes.

Incremental backup is supported for InnoDB tables. Tables using other storage engines receive full backups even during incremental backup operations.

To increment a full backup, use the --incremental-basedir option to indicate the path to the full backup and the --target-dir option to indicate where you want to write the incremental backup:

In this example, MariaDB Backup reads the /data/backups/full directory, and MariaDB Enterprise Server then creates an incremental backup in the /data/backups/inc1 directory.

Preparing an Incremental Backup

An incremental backup must be applied to a prepared full backup before it can be used in a restore operation. If you have multiple full backups to choose from, pick the nearest full backup prior to the incremental backup that you want to restore. You may also want to back up your full-backup directory, as it are modified by the updates in the incremental data.

If your full backup directory is not yet prepared, run this to make it consistent:

Then, using the prepared full backup, apply the first incremental backup's data to the full backup in an incremental preparation step:

Once the incremental backup has been applied to the full backup, the full backup directory contains the changes from the incremental backup (that is, the inc1/ directory). Feel free to remove inc1/ to save disk space.

Restoring from Incremental Backups

Once you have prepared the full backup directory with all the incremental changes you need (as described above), stop the MariaDB Community Server, its data directory, and restore from the original full backup directory using the --copy-back option:

MariaDB Backup writes files into the data directory using either the current user or root (in the case of a sudo operation), which may be different from the system user that runs the database. Run the following to recursively update the ownership of the restored files and directories:

Then, start MariaDB Enterprise Server. When the Server starts, it works from the restored data directory.

Partial Backup and Restore

In a partial backup, MariaDB Backup copies a specified subset of tablespaces from the MariaDB Enterprise Server data directory. Partial backups are useful in establishing a higher frequency of backups on specific data, at the expense of increased recovery complexity. In selecting tablespaces for a partial backup, please consider referential integrity.

Performing a Partial Backup

Command-line options can be used to narrow the set of databases or tables to be included within a backup:

Option

Description

For example, you may wish to produce a partial backup, which excludes a specific database:

Partial backups can also be incremental:

Preparing a Backup Before a Partial Restore

As with full and incremental backups, partial backups are not point-in-time consistent. A partial backup must be prepared before it can be used for recovery.

A partial restore can be performed from a full backup or partial backup.

The preparation step for either partial or full backup restoration requires the use of transportable tablespaces for InnoDB. As such, each prepare operation requires the --export option:

When using a partial incremental backup for restore, the incremental data must be applied to its prior partial backup data before its data is complete. If performing partial incremental backups, run the prepare statement again to apply the incremental changes onto the partial backup that served as the base.

Performing a Partial Restore

Unlike full and incremental backups, you cannot restore partial backups directly using MariaDB Backup. Further, as a partial backup does not contain a complete data directory, you cannot restore MariaDB Community Server to a startable state solely with a partial backup.

To restore from a partial backup, you need to prepare a table on the MariaDB Community Server, then manually copy the files into the data directory.

The details of the restore procedure depend on the characteristics of the table:

As partial restores are performed while the server is running, not stopped, care should be taken to prevent production workloads during restore activity.

Note: You can also use data from a full backup in a partial restore operation if you have prepared the data using the --export option as described above.

Partial Restore Non-partitioned Tables

To restore a non-partitioned table from a backup, first create a new table on MariaDB Community Server to receive the restored data. It should match the specifications of the table you're restoring.

Be extra careful if the backup data is from a server with a different version than the restore server, as some differences (such as a differing ROW_FORMAT) can cause an unexpected result.

Create an empty table for the data being restored:

Modify the table to discard the tablespace:

You can copy (or move) the files for the table from the backup to the data directory:

Use a wildcard to include both the .ibd and .cfg files. Then, change the owner to the system user running MariaDB Community Server:

Lastly, import the new tablespace:

MariaDB Community Server looks in the data directory for the tablespace you copied in, then imports it for use. If the table is encrypted, it also looks for the encryption key with the relevant key ID that the table data specifies.

Repeat this step for every table you wish to restore.

Partial Restore Partitioned Tables

Restoring a partitioned table from a backup requires a few extra steps compared to restoring a non-partitioned table.

To restore a partitioned table from a backup, first create a new table on MariaDB Community Server to receive the restored data. It should match the specifications of the table you're restoring, including the partition specification.

Be extra careful if the backup data is from a server with a different version than the restore server, as some differences (such as a differing ROW_FORMAT) can cause an unexpected result.

Create an empty table for the data being restored:

Then create a second empty table matching the column specification, but without partitions. This is your working table:

For each partition you want to restore, discard the working table's tablespace:

Then, copy the table files from the backup, using the new name:

Change the owner to that of the user running MariaDB Community Server:

Import the copied tablespace:

Lastly, exchange the partition, copying the tablespace from the working table into the partition file for the target table:

Repeat the above process for each partition until you have them all exchanged into the target table. Then delete the working table, as it's no longer necessary:

This restores a partitioned table.

Partial Restore of Tables with Full-Text Indexes

When restoring a table with a full-text search (FTS) index, InnoDB may throw a schema mismatch error.

In this case, to restore the table, it is recommended to:

Remove the corresponding .cfg file.
Restore data to a table without any secondary indexes including FTS.
Add the necessary secondary indexes to the restored table.

For example, to restore table t1 with FTS index from database db1:

In the MariaDB shell, drop the table you are going to restore:

Create an empty table for the data being restored:

Modify the table to discard the tablespace:

In the operating system shell, copy the table files from the backup to the data directory of the corresponding database:

Remove the .cfg file from the data directory:

Change the owner of the newly copied files to the system user running MariaDB Community Server:

In the MariaDB shell, import the copied tablespace:

Verify that the data has been successfully restored:

Add the necessary secondary indexes:

The table is now fully restored:

Point-in-Time Recoveries

Recovering from a backup restores the data directory at a specific point-in-time, but it does not restore the binary log. In a point-in-time recovery, you begin by restoring the data directory from a full or incremental backup, then use the mysqlbinlog utility to recover the binary log data to a specific point in time.

First, prepare the backup as you normally would for a or backup:

When MariaDB Backup runs on a MariaDB Community Server where binary logs is enabled, it stores binary log information in the xtrabackup_binlog_info file. Consult this file to find the name of the binary log position to use. In the following example, the log position is 321.

Update the configuration file to use a new data directory.

Using MariaDB Backup, restore from the backup to the new data directory:

Then change the owner to the MariaDB Community Server system user:

Start MariaDB Community Server:

Using the binary log file in the old data directory, the start position in the xtrabackup_binlog_info file, the date and time you want to restore to, and the mysqlbinlog utility to create an SQL file with the binary log changes:

Lastly, run the binary log SQL to restore the databases:

mariadb-backup Overview

mariadb-backup was previously called mariabackup.

mariadb-backup is an open source tool provided by MariaDB for performing physical online backups of InnoDB, Aria and MyISAM tables. For InnoDB, “hot online” backups are possible. It was originally forked from Percona XtraBackup 2.3.8. It is available on Linux and Windows.

This tool provides a production-quality, nearly non-blocking method for performing full backups on running systems. While partial backups with mariadb-backup are technically possible, they require many steps and cannot be restored directly onto existing servers containing other data.

Supported Features

mariadb-backup supports all of the main features of Percona XtraBackup 2.3.8, plus:

Backup/Restore of tables using Data-at-Rest Encryption.
Backup/Restore of tables using InnoDB Page Compression.
mariadb-backup SST method with Galera Cluster.
Microsoft Windows support.
Backup/Restore of tables using the MyRocks storage engine. See Files Backed up by mariadb-backup: MyRocks Data Files for more information.

Supported Features in MariaDB Enterprise Backup

MariaDB Backup supports some additional features, such as:

Minimizes locks during the backup to permit more concurrency and to enable faster backups.
- This relies on the usage of BACKUP STAGE commands and DDL logging.
- This includes no locking during the copy phase of ALTER TABLE statements, which tends to be the longest phase of these statements.
Provides optimal backup support for all storage engines that store things on local disk.

MariaDB Backup does not support some additional features.

Differences Compared to Percona XtraBackup

Percona XtraBackup requires more locks to run than MariaDB. In addition, any running ALTER TABLE will block Percona XtraBackup until it completes.
Percona XtraBackup copies its InnoDB redo log files to the file xtrabackup_logfile, while mariadb-backup uses the file ib_logfile0.
Percona XtraBackup's libgcrypt-based encryption of backups is not supported by mariadb-backup.
There is no symbolic link from mariadb-backup to innobackupex, as there is for xtrabackup. Instead, mariadb-backup has the --innobackupex command-line option to enable innobackupex-compatible options.
The --compact and --rebuild_indexes options are not supported.
Support for --stream=tar was removed from mariadb-backup in .
The xbstream utility has been renamed to mbstream. However, to select this output format when creating a backup, mariadb-backup's --stream option still expects the xbstream value.
mariadb-backup does not support lockless binlog.

Difference in Versioning Schemes

Each Percona XtraBackup release has two version numbers--the Percona XtraBackup version number and the version number of the MySQL Server release that it is based on. For example:

xtrabackup version 2.2.8 based on MySQL server 5.6.22

Each mariadb-backup release only has one version number, and it is the same as the version number of the MariaDB Server release that it is based on. For example:

mariadb-backup based on MariaDB server 10.2.15-MariaDB Linux (x86_64)

Installing `mariadb-backup`

Installing on Linux

The mariadb-backup executable is included in binary tarballs on Linux.

Installing with a Package Manager

mariadb-backup can also be installed via a package manager on Linux. In order to do so, your system needs to be configured to install from one of the MariaDB repositories.

You can configure your package manager to install it from MariaDB Corporation's MariaDB Package Repository by using the MariaDB Package Repository setup script.

You can also configure your package manager to install it from MariaDB Foundation's MariaDB Repository by using the MariaDB Repository Configuration Tool.

Installing with yum/dnf

On RHEL, CentOS, Fedora, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's repository using yum or dnf. Starting with RHEL 8 and Fedora 22, yum has been replaced by dnf, which is the next major version of yum. However, yum commands still work on many systems that use dnf. For example:

sudo yum install MariaDB-backup

Installing with apt-get

On Debian, Ubuntu, and other similar Linux distributions, it is highly recommended to install the relevant DEB package from MariaDB's repository using apt-get. For example:

sudo apt-get install mariadb-backup

Installing with zypper

On SLES, OpenSUSE, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's repository using zypper. For example:

sudo zypper install MariaDB-backup

Installing on Windows

The mariadb-backup executable is included in MSI and ZIP packages on Windows.

When using the Windows MSI installer, mariadb-backup can be installed by selecting Backup utilities:

Using mariadb-backup

The command to use mariadb-backup and the general syntax is:

mariadb-backup <options>

For in-depth explanations on how to use mariadb-backup, see:

Full Backup and Restore with mariadb-backup
Incremental Backup and Restore with mariadb-backup
Partial Backup and Restore with mariadb-backup
Restoring Individual Tables and Partitions with mariadb-backup
Setting up a Replica with mariadb-backup
Using Encryption and Compression Tools With mariadb-backup

Options

Options supported by mariadb-backup can be found on the mariadb-backup Options page.

mariadb-backup will currently silently ignore unknown command-line options, so be extra careful about accidentally including typos in options or accidentally using options from later mariadb-backup versions. The reason for this is that mariadb-backup currently treats command-line options and options from option files equivalently. When it reads from these option files, it has to read a lot of options from the server option groups read by mariadbd. However, mariadb-backup does not know about many of the options that it normally reads in these option groups. If mariadb-backup raised an error or warning when it encountered an unknown option, then this process would generate a large amount of log messages under normal use. Therefore, mariadb-backup is designed to silently ignore the unknown options instead. See MDEV-18215 about that.

Option Files

In addition to reading options from the command-line, mariadb-backup can also read options from option files.

The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:

Option

Description

--print-defaults

Print the program argument list and exit.

--no-defaults

Don't read default options from any option file.

--defaults-file=#

Only read default options from the given option file.

--defaults-extra-file=#

Read this file after the global files are read.

--defaults-group-suffix=#

In addition to the default option groups, also read option groups with this suffix.

Server Option Groups

mariadb-backup reads server options from the following option groups from option files:

Group

Description

[mariadb-backup]

Options read by mariadb-backup. Available starting with and .

[mariadb-backup]

Options read by mariadb-backup. Available starting with and .

[xtrabackup]

Options read by mariadb-backup and Percona XtraBackup.

[server]

Options read by MariaDB Server. Available starting with , , and .

[mysqld]

Options read by mariadbd, which includes both MariaDB Server and MySQL Server (where it is called mysqld).

[mysqld-X.Y]

Options read by a specific version of mysqld, which includes both MariaDB Server and MySQL Server. For example: [mysqld-10.4]. Available starting with , , and .

[mariadb]

Options read by MariaDB Server. Available starting with , , and .

[mariadb-X.Y]

Options read by a specific version of MariaDB Server. For example: [mariadb-10.4]. Available starting with , , and .

[mariadbd]

Options read by MariaDB Server. Available starting with and .

[mariadbd-X.Y]

Options read by a specific version of MariaDB Server. For example: [mariadbd-10.4]. Available starting with and .

[client-server]

Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients. Available starting with , , and .

[galera]

Options read by MariaDB Server, but only if it is compiled with Galera Cluster support. In MariaDB 10.1 and later, all builds on Linux are compiled with Galera Cluster support. When using one of these builds, options from this option group are read even if the Galera Cluster functionality is not enabled. Available starting with , , and on systems compiled with Galera Cluster support.

Client Option Groups

mariadb-backup reads client options from the following option groups from option files:

Group

Description

[mariadb-backup]

Options read by mariadb-backup. Available starting with and .

[mariadb-backup]

Options read by mariadb-backup. Available starting with and

[xtrabackup]

Options read by mariadb-backup and Percona XtraBackup.

[client]

Options read by all MariaDB and MySQL client programs, which includes both MariaDB and MySQL clients. For example, mysqldump.

[client-server]

[client-mariadb]

Options read by all MariaDB client programs. Available starting with , , and .

Authentication and Privileges

mariadb-backup needs to authenticate with the database server when it performs a backup operation (i.e. when the --backup option is specified). For most use cases, the user account that performs the backup needs to have the following global privileges on the database server.

In 10.5 and later the required privileges are:

CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariadb-backup'@'localhost';

Prior to 10.5, the required privileges are:

CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariadb-backup'@'localhost';

If your database server is also using the MyRocks storage engine, then the user account that performs the backup will also need the SUPER global privilege. This is because mariadb-backup creates a checkpoint of this data by setting the rocksdb_create_checkpoint system variable, which requires this privilege. See MDEV-20577 for more information.

CONNECTION ADMIN is also required where --kill-long-queries-timeout is greater than 0, and --no-lock isn't applied in order to KILL queries. Prior to 10.5 a SUPER privilege is required instead of CONNECTION ADMIN.

REPLICA MONITOR (or alias SLAVE MONITOR) is also required where --galera-info or --slave-info is specified.

To use the --history option, the backup user also needs to have the following privileges granted:

GRANT CREATE, ALTER, INSERT ON mysql.mariadb_backup_history TO 'mariadb-backup'@'localhost';

Prior to MariaDB 10.11, the necessary permissions to use --history were:

GRANT CREATE, INSERT ON PERCONA_SCHEMA.* TO 'mariadb-backup'@'localhost';

If you're upgrading from an older version and you want to use the new default table without losing your backup history, you can move and rename the current table in this way:

RENAME TABLE PERCONA_SCHEMA.xtrabackup_history TO mysql.mariadb_backup_history;
ALTER TABLE mysql.mariadb_backup_history MODIFY format ENUM('file', 'tar', 'mbstream') DEFAULT NULL;

The user account information can be specified with the --user and --password command-line options. For example:

mariadb-backup --backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariadb-backup --password=mypassword

The user account information can also be specified in a supported client option group in an option file. For example:

[mariadb-backup]
user=mariadb-backup
password=mypassword

mariadb-backup does not need to authenticate with the database server when preparing or restoring a backup.

File System Permissions

mariadb-backup has to read MariaDB's files from the file system. Therefore, when you run mariadb-backup as a specific operating system user, you should ensure that user account has sufficient permissions to read those files.

If you are using Linux and if you installed MariaDB with a package manager, then MariaDB's files will probably be owned by the mysql user and the mysql group.

Using `mariadb-backup` with Data-at-Rest Encryption

mariadb-backup supports Data-at-Rest Encryption.

mariadb-backup will query the server to determine which key management and encryption plugin is being used, and then it will load that plugin itself, which means that mariadb-backup needs to be able to load the key management and encryption plugin's shared library.

mariadb-backup will also query the server to determine which encryption keys it needs to use.

In other words, mariadb-backup is able to figure out a lot of encryption-related information on its own, so normally one doesn't need to provide any extra options to backup or restore encrypted tables.

mariadb-backup backs up encrypted and unencrypted tables as they are on the original server. If a table is encrypted, then the table will remain encrypted in the backup. Similarly, if a table is unencrypted, then the table will remain unencrypted in the backup.

The primary reason that mariadb-backup needs to be able to encrypt and decrypt data is that it needs to apply InnoDB redo log records to make the data consistent when the backup is prepared. As a consequence, mariadb-backup does not perform many encryption or decryption operations when the backup is initially taken. MariaDB performs more encryption and decryption operations when the backup is prepared. This means that some encryption-related problems (such as using the wrong encryption keys) may not become apparent until the backup is prepared.

Using `mariadb-backup` for Galera SSTs

The mariadb-backup SST method uses the mariadb-backup utility for performing SSTs. See mariadb-backup SST method for more information.

Files Backed up by `mariadb-backup`

mariadb-backup backs up many different files in order to perform its backup operation. See Files Backed up by mariadb-backup for a list of these files.

Files Created by `mariadb-backup`

mariadb-backup creates several different types of files during the backup and prepare phases. See Files Created by mariadb-backup for a list of these files.

Binary logs

mariadb-backup can store the binary log position in the backup. See --binlog-info. This can be used for point-in-time recovery and to use the backup to setup a slave with the correct binlog position.

Known Issues

Unsupported Server Option Groups

Prior to , , and , mariadb-backup doesn't read server options from all option groups supported by the server. In those versions, it only looks for server options in the following server option groups:

Group

Description

[xtrabackup]

Options read by Percona XtraBackup and mariadb-backup.

[mariadb-backup]

Options read by Percona XtraBackup and mariadb-backup. Available starting with and .

[mysqld]

Options read by , which includes both MariaDB Server and MySQL Server (where it is called mysqld).

Those versions do not read server options from the following option groups supported by the server:

Group

Description

[server]

Options read by MariaDB Server. Available starting with , , and .

[mysqld-X.Y]

Options read by a specific version of mysqld, which includes both MariaDB Server and MySQL Server. For example: [mysqld-5.5]. Available starting with , , and .

[mariadb]

Options read by MariaDB Server. Available starting with , , and .

[mariadb-X.Y]

Options read by a specific version of MariaDB Server. For example: [mariadb-10.3]. Available starting with , , and .

[client-server]

[galera]

See MDEV-18347 for more information.

No Default Datadir

Prior to , , and , if you were performing a --copy-back operation, and if you did not explicitly specify a value for the --datadir option either on the command line or one of the supported server option groups in an option file, then mariadb-backup would not default to the server's default datadir. Instead, mariadb-backup would fail with an error. For example:

Error: datadir must be specified.

The solution is to explicitly specify a value for the --datadir option either on the command line or in one of the supported server option groups in an option file. For example:

[mysqld]
datadir=/var/lib/mysql

In , , and and later, mariadb-backup will default to the server's default datadir value.

See MDEV-12956 for more information.

Concurrent DDL and Backup Issues

Prior to and , if concurrent DDL was executed while the backup was taken, then that could cause various kinds of problems to occur.

One example is that if DDL caused any tablespace IDs to change (such as TRUNCATE TABLE or RENAME TABLE), then that could cause the effected tables to be inconsistent in the backup. In this scenario, you might see errors about mismatched tablespace IDs when the backup is prepared.

For example, the errors might look like this:

2018-12-07 07:49:32 7f51b3184820  InnoDB: Error: table 'DB1/TAB_TEMP'
InnoDB: in InnoDB data dictionary has tablespace id 1355633,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name DB1/TAB_TEMP and id 1354713, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

Or they might look like this:

2018-07-12 21:24:14 139666981324672 [Note] InnoDB: Ignoring data file 'db1/tab1.ibd' with space ID 200485, since the redo log references db1/tab1.ibd with space ID 200484.

Some of the problems related to concurrent DDL are described below.

Problems solved by setting --lock-ddl-per-table (mariadb-backup command-line option added in ):

If a table is dropped during the backup, then it might still exists after the backup is prepared.
If a table exists when the backup starts, but it is dropped before the backup copies it, then the tablespace file can't be copied, so the backup would fail.

Problems solved by setting --innodb_log_optimize_ddl=OFF (MariaDB Server system variable added in and removed in 10.6.0):

If the backup noticed concurrent DDL, then it might fail with
```
ALTER TABLE or OPTIMIZE TABLE was executed during backup
```

Problems solved by --innodb_safe_truncate=ON (MariaDB Server system variable in and removed in 10.3.0):

If a table is created during the backup, then it might not exist in the backup after prepare.
If a table is renamed during the backup after the tablespace file was copied, then the table may not exist after the backup is prepared.
If a table is dropped and created under the same name during the backup after the tablespace file was copied, then the table will have the wrong tablespace ID when the backup is prepared.

Note that, with the removal of innodb_log_optimize_ddl and innodb_safe_truncate, the above problems were definitely solved.

Problems solved by other bug fixes:

If --lock-ddl-per-table is used and if a table is concurrently being dropped or renamed, then mariadb-backup can fail to acquire the MDL lock.

These problems are only fixed in MariaDB 10.2 and later, so it is not recommended to execute concurrent DDL when using mariadb-backup with MariaDB 10.1.

See MDEV-13563, MDEV-13564, MDEV-16809, and MDEV-16791 for more information.

Manual Restore with Pre-existing InnoDB Redo Log files

Prior to , mariadb-backup users could run into issues if they restored a backup by manually copying the files from the backup into the datadir while the directory still contained pre-existing InnoDB redo log files. The backup itself did not contain InnoDB redo log files with the traditional ib_logfileN file names, so the pre-existing log files would remain in the datadir. If the server were started with these pre-existing log files, then it could perform crash recovery with them, which could cause the database to become inconsistent or corrupt.

In these MariaDB versions, this problem could be avoided by not restoring the backup by manually copying the files and instead restoring the backup by using mariadb-backup and providing the --copy-back option, since mariadb-backup deletes pre-existing InnoDB redo log files from the datadir during the restore process.

In and later, mariadb-backup prevents this issue by creating an empty InnoDB redo log file called ib_logfile0 as part of the --prepare stage. That way, if the backup is manually restored, any pre-existing InnoDB redo log files would get overwritten by the empty one.

See MDEV-13311 for more information.

Too Many Open Files

If mariadb-backup uses more file descriptors than the system is configured to allow, then users can see errors like the following:

2019-02-12 09:48:38 7ffff7fdb820  InnoDB: Operating system error number 23 in a file operation.
InnoDB: Error number 23 means 'Too many open files in system'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
InnoDB: Error: could not open single-table tablespace file ./db1/tab1.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

Prior to , , and , mariadb-backup would actually ignore the error and continue the backup. In some of those cases, mariadb-backup would even report a successful completion of the backup to the user. In later versions, mariadb-backup will properly throw an error and abort when this error is encountered. See MDEV-19060 for more information.

When this error is encountered, one solution is to explicitly specify a value for the --open-files-limit option either on the command line or in one of the supported server option group s in an option file. For example:

[mariadb-backup]
open_files_limit=65535

An alternative solution is to set the soft and hard limits for the user account that runs mariadb-backup by adding new limits to /etc/security/limits.conf. For example, if mariadb-backup is run by the mysql user, then you could add lines like the following:

mysql soft nofile 65535
mysql hard nofile 65535

After the system is rebooted, the above configuration should set new open file limits for the mysql user, and the user's ulimit output should look like the following:

ulimit -Sn
65535
ulimit -Hn
65535

Versions

mariadb-backup/Server Version

Maturity

+, +

Stable

Beta

Alpha

mariadb-backup Options

mariadb-backup was previously called mariabackup.

List of `mariadb-backup` Options

`--apply-log`

Prepares an existing backup to restore to the MariaDB Server. This is only valid in innobackupex mode, which can be enabled with the --innobackupex option.

Files that mariadb-backup generates during --backup operations in the target directory are not ready for use on the Server. Before you can restore the data to MariaDB, you first need to prepare the backup.

In the case of full backups, the files are not point in time consistent, since they were taken at different times. If you try to restore the database without first preparing the data, InnoDB rejects the new data as corrupt. Running mariadb-backup with the --prepare command readies the data so you can restore it to MariaDB Server. When working with incremental backups, you need to use the --prepare command and the --incremental-dir option to update the base backup with the deltas from an incremental backup.

mariadb-backup --innobackupex --apply-log

Once the backup is ready, you can use the --copy-back or the --move-back commands to restore the backup to the server.

`--apply-log-only`

If this option is used when preparing a backup, then only the redo log apply stage are performed, and other stages of crash recovery are ignored. This option is used with incremental backups.

Note: This option is not needed or supported anymore.

`--backup`

Backs up your databases.

Using this command option, mariadb-backup performs a backup operation on your database or databases. The backups are written to the target directory, as set by the --target-dir option.

mariadb-backup --backup 
      --target-dir /path/to/backup \
      --user user_name --password user_passwd

mariadb-backup can perform full and incremental backups. A full backup creates a snapshot of the database in the target directory. An incremental backup checks the database against a previously taken full backup, (defined by the --incremental-basedir option) and creates delta files for these changes.

In order to restore from a backup, you first need to run mariadb-backup with the --prepare command option, to make a full backup point-in-time consistent or to apply incremental backup deltas to base. Then you can run mariadb-backup again with either the --copy-back or --move-back commands to restore the database.

For more information, see Full Backup and Restore and Incremental Backup and Restore.

`--binlog-info`

Defines how mariadb-backup retrieves the binary log coordinates from the server.

--binlog-info[=OFF | ON | LOCKLESS | AUTO]

The --binlog-info option supports the following retrieval methods. When no retrieval method is provided, it defaults to AUTO.

Option

Description

OFF

Disables the retrieval of binary log information

ON

Enables the retrieval of binary log information, performs locking where available to ensure consistency

LOCKLESS

Unsupported option

AUTO

Enables the retrieval of binary log information using ON or LOCKLESS where supported

Using this option, you can control how mariadb-backup retrieves the server's binary log coordinates corresponding to the backup.

When enabled, whether using ON or AUTO, mariadb-backup retrieves information from the binlog during the backup process. When disabled with OFF, mariadb-backup runs without attempting to retrieve binary log information. You may find this useful when you need to copy data without metadata like the binlog or replication coordinates.

mariadb-backup --binlog-info --backup

Currently, the LOCKLESS option depends on features unsupported by MariaDB Server. See the description of the xtrabackup_binlog_pos_innodb file for more information. If you attempt to run mariadb-backup with this option, then it causes the utility to exit with an error.

`--close-files`

Defines whether you want to close file handles.

Using this option, you can tell mariadb-backup that you want to close file handles. Without this option, mariadb-backup keeps files open in order to manage DDL operations. When working with particularly large tablespaces, closing the file can make the backup more manageable. However, it can also lead to inconsistent backups. Use at your own risk.

mariadb-backup --close-files --prepare

`--compress`

This option was deprecated as it relies on the no longer maintained QuickLZ library. It are removed in a future release - versions supporting this function will not be affected. It is recommended to instead backup to a stream (stdout), and use a 3rd party compression library to compress the stream, as described in Using Encryption and Compression Tools With mariadb-backup.

Defines the compression algorithm for backup files.

--compress[=compression_algorithm]

The --compress option only supports the now deprecated quicklz algorithm.

Option

Description

quicklz

Uses the QuickLZ compression algorithm

mariadb-backup --compress --backup

If a backup is compressed using this option, then mariadb-backup will record that detail in the xtrabackup_info file.

`--compress-chunk-size`

Deprecated, for details see the --compress option.

Defines the working buffer size for compression threads.

--compress-chunk-size=#

mariadb-backup can perform compression operations on the backup files before writing them to disk. It can also use multiple threads for parallel data compression during this process. Using this option, you can set the chunk size each thread uses during compression. It defaults to 64K.

mariadb-backup --backup --compress \
     --compress-threads=12 --compress-chunk-size=5M

To further configure backup compression, see the --compress and --compress-threads options.

`--compress-threads`

Deprecated, for details see the --compress option.

Defines the number of threads to use in compression.

--compress-threads=#

mariadb-backup can perform compression operations on the backup files before writing them to disk. Using this option, you can define the number of threads you want to use for this operation. You may find this useful in speeding up the compression of particularly large databases. It defaults to single-threaded.

mariadb-backup --compress --compress-threads=12 --backup

To further configure backup compression, see the --compress and --compress-chunk-size options.

`--copy-back`

Restores the backup to the data directory.

Using this command, mariadb-backup copies the backup from the target directory to the data directory, as defined by the --datadir option. You must stop the MariaDB Server before running this command. The data directory must be empty. If you want to overwrite the data directory with the backup, use the --force-non-empty-directories option.

Bear in mind, before you can restore a backup, you first need to run mariadb-backup with the --prepare option. In the case of full backups, this makes the files point-in-time consistent. With incremental backups, this applies the deltas to the base backup. Once the backup is prepared, you can run --copy-back to apply it to MariaDB Server.

mariadb-backup --copy-back --force-non-empty-directories

Running the --copy-back command copies the backup files to the data directory. Use this command if you want to save the backup for later. If you don't want to save the backup for later, use the --move-back command.

`--core-file`

Defines whether to write a core file.

Using this option, you can configure mariadb-backup to dump its core to file in the event that it encounters fatal signals. You may find this useful for review and debugging purposes.

mariadb-backup --core-file --backup

`--databases`

Defines the databases and tables you want to back up.

--databases="database[.table][ database[.table] ...]"

Using this option, you can define the specific database or databases you want to back up. In cases where you have a particularly large database or otherwise only want to back up a portion of it, you can optionally also define the tables on the database.

mariadb-backup --backup \
      --databases="example.table1 example.table2"

In cases where you want to back up most databases on a server or tables on a database, but not all, you can set the specific databases or tables you don't want to back up using the --databases-exclude option.

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

In innobackupex mode, which can be enabled with the --innobackupex option, the --databases option can be used as described above, or it can be used to refer to a file, just as the --databases-file option can in the normal mode.

`--databases-exclude`

Defines the databases you don't want to back up.

--databases-exclude="database[.table][ database[.table] ...]"

Using this option, you can define the specific database or databases you want to exclude from the backup process. You may find it useful when you want to back up most databases on the server or tables on a database, but would like to exclude a few from the process.

mariadb-backup --backup \
      --databases="example" \
      --databases-exclude="example.table1 example.table2"

To include databases in the backup, see the --databases option option

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

`--databases-file`

Defines the path to a file listing databases and/or tables you want to back up.

--databases-file="/path/to/database-file"

Format the databases file to list one element per line, with the following syntax:

database[.table]

In cases where you need to back up a number of databases or specific tables in a database, you may find the syntax for the --databases and --databases-exclude options a little cumbersome. Using this option you can set the path to a file listing the databases or databases and tables you want to back up.

For instance, imagine you list the databases and tables for a backup in a file called main-backup.

cat main-backup

example1
example2.table1
example2.table2

mariadb-backup --backup --databases-file=main-backup

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

`-h, --datadir`

Defines the path to the database root.

--datadir=PATH

Using this option, you can define the path to the source directory. This is the directory that mariadb-backup reads for the data it backs up. It should be the same as the MariaDB Server datadir system variable.

mariadb-backup --backup -h /var/lib64/mysql

`--debug-sleep-before-unlock`

This is a debug-only option used by the Xtrabackup test suite.

`--decompress`

Deprecated, for details see the --compress option.

This option requires that you have the qpress utility installed on your system.

Defines whether you want to decompress previously compressed backup files.

When you run mariadb-backup with the --compress option, it compresses the subsequent backup files, using the QuickLZ algorithm. Using this option, mariadb-backup decompresses the compressed files from a previous backup.

For instance, run a backup with compression,

mariadb-backup --compress --backup

Then decompress the backup,

mariadb-backup --decompress

You can enable the decryption of multiple files at a time using the --parallel option. By default, mariadb-backup does not remove the compressed files from the target directory. If you want to delete these files, use the --remove-original option.

`--debug-sync`

Defines the debug sync point. This option is only used by the mariadb-backup test suite.

`--defaults-extra-file`

Defines the path to an extra default option file.

--defaults-extra-file=/path/to/config

Using this option, you can define an extra default option file for mariadb-backup. Unlike --defaults-file, this file is read after the default option files are read, allowing you to only overwrite the existing defaults.

mariadb-backup --backup \
      --defaults-file-extra=addition-config.cnf \
      --defaults-file=config.cnf

`--defaults-file`

Defines the path to the default option file.

--defaults-file=/path/to/config

Using this option, you can define a default option file for mariadb-backup. Unlike the --defaults-extra-file option, when this option is provided, it completely replaces all default option files.

mariadb-backup --backup \
     --defaults-file="config.cnf

`--defaults-group`

Defines the option group to read in the option file.

--defaults-group="name"

In situations where you find yourself using certain mariadb-backup options consistently every time you call it, you can set the options in an option file. The --defaults-group option defines what option group mariadb-backup reads for its options.

Options you define from the command-line can be set in the configuration file using minor formatting changes. For instance, if you find yourself perform compression operations frequently, you might set --compress-threads and --compress-chunk-size options in this way:

[mariadb-backup]
compress_threads = 12
compress_chunk_size = 64K

Now whenever you run a backup with the --compress option, it always performs the compression using 12 threads and 64K chunks.

mariadb-backup --compress --backup

See mariadb-backup Overview: Server Option Groups and mariadb-backup Overview: Client Option Groups for a list of the option groups read by mariadb-backup by default.

`--encrypted-backup`

When this option is used with --backup, if mariadb-backup encounters a page that has a non-zero key_version value, then mariadb-backup assumes that the page is encrypted.

Use --skip-encrypted-backup instead to allow mariadb-backup to copy unencrypted tables that were originally created before MySQL 5.1.48.

`--export`

If this option is provided during the --prepare stage, then it tells mariadb-backup to create .cfg files for each InnoDB file-per-table tablespace. These .cfg files are used to import transportable tablespaces in the process of restoring partial backups and restoring individual tables and partitions.

The --export option could require rolling back incomplete transactions that had modified the table. This will likely create a "new branch of history" that does not correspond to the server that had been backed up, which makes it impossible to apply another incremental backup on top of such additional changes. The option should only be applied when doing a --prepare of the last incremental.

mariadb-backup --prepare --export

mariadb-backup did not support the --export option. See MDEV-13466 about that. In earlier versions of MariaDB, this means that mariadb-backup could not create .cfg files for InnoDB file-per-table tablespaces during the --prepare stage. You can still import file-per-table tablespaces without the .cfg files in many cases, so it may still be possible in those versions to restore partial backups or to restore individual tables and partitions with just the .ibd files. If you have a full backup and you need to create .cfg files for InnoDB file-per-table tablespaces, then you can do so by preparing the backup as usual without the --export option, and then restoring the backup, and then starting the server. At that point, you can use the server's built-in features to copy the transportable tablespaces.

`--extra-lsndir`

Saves an extra copy of the xtrabackup_checkpoints and xtrabackup_info files into the given directory.

--extra-lsndir=PATH

When using the --backup command option, mariadb-backup produces a number of backup files in the target directory. Using this option, you can have mariadb-backup produce additional copies of the xtrabackup_checkpoints and xtrabackup_info files in the given directory.

mariadb-backup --extra-lsndir=extras/ --backup

This is especially usefull when using --stream for streaming output, e.g. for compression and/or encryption using external tools in combination with incremental backups, as the xtrabackup_checkpoints file necessary to determine the LSN to continue the incremental backup from is still accessible without uncompressing / decrypting the backup file first. Simply pass in the --extra-lsndir of the previous backup as--incremental-basedir

`--force-non-empty-directories`

Allows --copy-back or --move-back command options to use non-empty target directories.

When using mariadb-backup with the --copy-back or --move-back command options, they normally require a non-empty target directory to avoid conflicts. Using this option with either of command allows mariadb-backup to use a non-empty directory.

mariadb-backup --force-non-empty-directories --copy-back

Bear in mind that this option does not enable overwrites. When copying or moving files into the target directory, if mariadb-backup finds that the target file already exists, it fails with an error.

`--ftwrl-wait-query-type`

Defines the type of query allowed to complete before mariadb-backup issues the global lock.

--ftwrl-wait-query-type=[ALL | UPDATE | SELECT]

The --ftwrl-wait-query-type option supports the following query types. The default value is ALL.

Option

Description

ALL

Waits until all queries complete before issuing the global lock

SELECT

Waits until SELECT statements complete before issuing the global lock

UPDATE

Waits until UPDATE statements complete before issuing the global lock

When mariadb-backup runs, it issues a global lock to prevent data from changing during the backup process. When it encounters a statement in the process of executing, it waits until the statement is finished before issuing the global lock. Using this option, you can modify this default behavior to ensure that it waits only for certain query types, such as for SELECT and UPDATE statements.

mariadb-backup --backup  \
      --ftwrl-wait-query-type=UPDATE

`--ftwrl-wait-threshold`

Defines the minimum threshold for identifying long-running queries for FTWRL.

--ftwrl-wait-threshold=#

When mariadb-backup runs, it issues a global lock to prevent data from changing during the backup process and ensure a consistent record. If it encounters statements still in the process of executing, it waits until they complete before setting the lock. Using this option, you can set the threshold at which mariadb-backup engages FTWRL. When it --ftwrl-wait-timeout is not 0 and a statement has run for at least the amount of time given this argument, mariadb-backup waits until the statement completes or until the --ftwrl-wait-timeout expires before setting the global lock and starting the backup.

mariadb-backup --backup \
     --ftwrl-wait-timeout=90 \
     --ftwrl-wait-threshold=30

`--ftwrl-wait-timeout`

Defines the timeout to wait for queries before trying to acquire the global lock. The global lock refers to BACKUP STAGE BLOCK_COMMIT. The global lock refers to FLUSH TABLES WITH READ LOCK (FTWRL).

--ftwrl-wait-timeout=#

When mariadb-backup runs, it acquires a global lock to prevent data from changing during the backup process and ensure a consistent record. If it encounters statements still in the process of executing, it can be configured to wait until the statements complete before trying to acquire the global lock.

If the --ftwrl-wait-timeout is set to 0, then mariadb-backup tries to acquire the global lock immediately without waiting. This is the default value.

If the --ftwrl-wait-timeout is set to a non-zero value, then mariadb-backup waits for the configured number of seconds until trying to acquire the global lock.

Starting in MariaDB 10.5.3, mariadb-backup will exit if it can't acquire the global lock after waiting for the configured number of seconds. In earlier versions, it could wait for the global lock indefinitely, even if --ftwrl-wait-timeout was set to a non-zero value.

mariadb-backup --backup \
      --ftwrl-wait-query-type=UPDATE \
      --ftwrl-wait-timeout=5

`--galera-info`

Defines whether you want to back up information about a Galera Cluster node's state.

When this option is used, mariadb-backup creates an additional file called xtrabackup_galera_info, which records information about a Galera Cluster node's state. It records the values of the wsrep_local_state_uuid and wsrep_last_committed status variables.

You should only use this option when backing up a Galera Cluster node. If the server is not a Galera Cluster node, then this option has no effect.

This option, when enabled and used with GTID replication, will rotate the binary logs at backup time.

mariadb-backup --backup --galera-info

`--history`

Defines whether you want to track backup history in the PERCONA_SCHEMA.xtrabackup_history table.

--history[=name]

When using this option, mariadb-backup records its operation in a table on the MariaDB Server. Passing a name to this option allows you group backups under arbitrary terms for later processing and analysis.

mariadb-backup --backup --history=backup_all

Currently, the table it uses by default is named mysql.mariadb_backup_history. Prior to , the default table was PERCONA_SCHEMA.xtrabackup_history.

mariadb-backup will also record this in the xtrabackup_info file.

`-H, --host`

Defines the host for the MariaDB Server you want to backup.

--host=name

Using this option, you can define the host to use when connecting to a MariaDB Server over TCP/IP. By default, mariadb-backup attempts to connect to the local host.

mariadb-backup --backup \
      --host="example.com"

`--include`

This option is a regular expression to be matched against table names in databasename.tablename format. It is equivalent to the --tables option. This is only valid in innobackupex mode, which can be enabled with the --innobackupex option.

`--incremental`

Defines whether you want to take an increment backup, based on another backup. This is only valid in innobackupex mode, which can be enabled with the --innobackupex option.

mariadb-backup --innobackupex --incremental

Using this option with the --backup command option makes the operation incremental rather than a complete overwrite. When this option is specified, either the --incremental-lsn or[--incremental-basedir](mariadb-backup-options.md#-incremental-basedir) options can also be given. If neither option is given, option [--incremental-basedir](mariadb-backup-options.md#-incremental-basedir) is used by default, set to the first timestamped backup directory in the backup base directory.

mariadb-backup --innobackupex --backup --incremental \
     --incremental-basedir=/data/backups \
     --target-dir=/data/backups

If a backup is a incremental backup, then mariadb-backup will record that detail in the xtrabackup_info file.

`--incremental-basedir`

Defines whether you want to take an incremental backup, based on another backup.

--incremental-basedir=PATH

Using this option with the --backup command option makes the operation incremental rather than a complete overwrite. mariadb-backup will only copy pages from .ibd files if they are newer than the backup in the specified directory.

mariadb-backup --backup \
     --incremental-basedir=/data/backups \
     --target-dir=/data/backups

If a backup is a incremental backup, then mariadb-backup will record that detail in the xtrabackup_info file.

`--incremental-dir`

Defines whether you want to take an incremental backup, based on another backup.

--increment-dir=PATH

Using this option with --prepare command option makes the operation incremental rather than a complete overwrite. mariadb-backup will apply .delta files and log files into the target directory.

mariadb-backup --prepare \
      --increment-dir=backups/

If a backup is a incremental backup, then mariadb-backup will record that detail in the xtrabackup_info file.

`--incremental-force-scan`

Defines whether you want to force a full scan for incremental backups.

When using mariadb-backup to perform an incremental backup, this option forces it to also perform a full scan of the data pages being backed up, even when there's bitmap data on the changes. MariaDB does not support changed page bitmaps, so this option is useless in those versions. See MDEV-18985 for more information.

mariadb-backup --backup \
     --incremental-basedir=/path/to/target \
     --incremental-force-scan

`--incremental-history-name`

Defines a logical name for the backup.

--incremental-history-name=name

mariadb-backup can store data about its operations on the MariaDB Server. Using this option, you can define the logical name it uses in identifying the backup.

mariadb-backup --backup \
     --incremental-history-name=morning_backup

Currently, the table it uses by default is named mysql.mariadb_backup_history. Prior to , the default table was PERCONA_SCHEMA.xtrabackup_history.

mariadb-backup will also record this in the xtrabackup_info file.

`--incremental-history-uuid`

Defines a UUID for the backup.

--incremental-history-uuid=name

mariadb-backup can store data about its operations on the MariaDB Server. Using this option, you can define the UUID it uses in identifying a previous backup to increment from. It checks --incremental-history-name, --incremental-basedir, and --incremental-lsn. If mariadb-backup fails to find a valid lsn, it generates an error.

mariadb-backup --backup \
      --incremental-history-uuid=main-backup012345678

Currently, the table it uses is named PERCONA_SCHEMA.xtrabackup_history, but expect that name to change in future releases. See MDEV-19246 for more information.

mariadb-backup will also record this in the xtrabackup_info file.

`--incremental-lsn`

Defines the sequence number for incremental backups.

--incremental-lsn=name

Using this option, you can define the sequence number (LSN) value for --backup operations. During backups, mariadb-backup only copies .ibd pages newer than the specified values.

WARNING: Incorrect LSN values can make the backup unusable. It is impossible to diagnose this issue.

`--innobackupex`

Deprecated

Enables innobackupex mode, which is a compatibility mode.

mariadb-backup --innobackupex

In innobackupex mode, mariadb-backup has the following differences:

To prepare a backup, the --apply-log option is used instead of the --prepare option.
To create an incremental backup, the --incremental option is supported.
The --no-timestamp option is supported.
To create a partial backup, the --include option is used instead of the --tables option.
To create a partial backup, the --databases option can still be used, but it's behavior changes slightly.
The --target-dir option is not used to specify the backup directory. The backup directory should instead be specified as a standalone argument.

The primary purpose of innobackupex mode is to allow scripts and tools to more easily migrate to mariadb-backup if they were originally designed to use the innobackupex utility that is included with Percona XtraBackup. It is not recommended to use this mode in new scripts, since it is not guaranteed to be supported forever. See MDEV-20552 for more information.

`--innodb`

This option has no effect. Set only for MySQL option compatibility.

`--innodb-adaptive-hash-index`

Enables InnoDB Adaptive Hash Index.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option you can explicitly enable the InnoDB Adaptive Hash Index. This feature is enabled by default for mariadb-backup. If you want to disable it, use --skip-innodb-adaptive-hash-index.

mariadb-backup --backup \
      --innodb-adaptive-hash-index

`--innodb-autoextend-increment`

Defines the increment in megabytes for auto-extending the size of tablespace file.

--innodb-autoextend-increment=36

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set the increment in megabytes for automatically extending the size of tablespace data file in InnoDB.

mariadb-backup --backup \
     --innodb-autoextend-increment=35

`--innodb-buffer-pool-filename`

Using this option has no effect. It is available to provide compatibility with the MariaDB Server.

`--innodb-buffer-pool-size`

Defines the memory buffer size InnoDB uses the cache data and indexes of the table.

--innodb-buffer-pool-size=124M

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can configure the buffer pool for InnoDB operations.

mariadb-backup --backup \
      --innodb-buffer-pool-size=124M

`--innodb-checksum-algorithm`

innodb_checksum_algorithm was deprecated in MariaDB 10.5.10 and removed in .

In earlier versions, it is used to define the checksum algorithm.

--innodb-checksum-algorithm=crc32
                           | strict_crc32
                           | innodb
                           | strict_innodb
                           | none
                           | strict_none

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can specify the algorithm mariadb-backup uses when checksumming on InnoDB tables. Currently, MariaDB supports the following algorithms CRC32, STRICT_CRC32, INNODB, STRICT_INNODB, NONE, STRICT_NONE.

mariadb-backup --backup \
      ---innodb-checksum-algorithm=strict_innodb

`--innodb-data-file-path`

Defines the path to individual data files.

--innodb-data-file-path=/path/to/file

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option you can define the path to InnoDB data files. Each path is appended to the --innodb-data-home-dir option.

mariadb-backup --backup \
     --innodb-data-file-path=ibdata1:13M:autoextend \
     --innodb-data-home-dir=/var/dbs/mysql/data

`--innodb-data-home-dir`

Defines the home directory for InnoDB data files.

--innodb-data-home-dir=PATH

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option you can define the path to the directory containing InnoDB data files. You can specific the files using the --innodb-data-file-path option.

mariadb-backup --backup \
     --innodb-data-file-path=ibdata1:13M:autoextend \
     --innodb-data-home-dir=/var/dbs/mysql/data

`--innodb-doublewrite`

Enables doublewrites for InnoDB tables.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. When using this option, mariadb-backup improves fault tolerance on InnoDB tables with a doublewrite buffer. By default, this feature is enabled. Use this option to explicitly enable it. To disable doublewrites, use the --skip-innodb-doublewrite option.

mariadb-backup --backup \
     --innodb-doublewrite

`--innodb-encrypt-log`

Defines whether you want to encrypt InnoDB logs.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can tell mariadb-backup that you want to encrypt logs from its InnoDB activity.

`--innodb-file-io-threads`

Defines the number of file I/O threads in InnoDB.

--innodb-file-io-threads=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the number of file I/O threads mariadb-backup uses on InnoDB tables.

mariadb-backup --backup \
     --innodb-file-io-threads=5

`--innodb-file-per-table`

Defines whether you want to store each InnoDB table as an .ibd file.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option causes mariadb-backup to store each InnoDB table as an .ibd file in the target directory.

`--innodb-flush-method`

Defines the data flush method. Ignored from .

--innodb-flush-method=fdatasync 
                     | O_DSYNC 
                     | O_DIRECT 
                     | O_DIRECT_NO_FSYNC 
                     | ALL_O_DIRECT

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the data flush method mariadb-backup uses with InnoDB tables.

mariadb-backup --backup \
      --innodb-flush-method==_DIRECT_NO_FSYNC

`--innodb-io-capacity`

Defines the number of IOP's the utility can perform.

--innodb-io-capacity=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can limit the I/O activity for InnoDB background tasks. It should be set around the number of I/O operations per second that the system can handle, based on drive or drives being used.

mariadb-backup --backup \
     --innodb-io-capacity=200

`--innodb-log-checksums`

Defines whether to include checksums in the InnoDB logs.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can explicitly set mariadb-backup to include checksums in the InnoDB logs. The feature is enabled by default. To disable it, use the --skip-innodb-log-checksums option.

mariadb-backup --backup \
      --innodb-log-checksums

`--innodb-log-buffer-size`

This option has no functionality in mariadb-backup. It exists for MariaDB Server compatibility.

`--innodb-log-files-in-group`

This option has no functionality in mariadb-backup. It exists for MariaDB Server compatibility.

`--innodb-log-group-home-dir`

Defines the path to InnoDB log files.

--innodb-log-group-home-dir=PATH

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the path to InnoDB log files.

mariadb-backup --backup \
     --innodb-log-group-home-dir=/path/to/logs

`--innodb-max-dirty-pages-pct`

Defines the percentage of dirty pages allowed in the InnoDB buffer pool.

--innodb-max-dirty-pages-pct=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the maximum percentage of dirty, (that is, unwritten) pages that mariadb-backup allows in the InnoDB buffer pool.

mariadb-backup --backup \
     --innodb-max-dirty-pages-pct=80

`--innodb-open-files`

Defines the number of files kept open at a time.

--innodb-open-files=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set the maximum number of files InnoDB keeps open at a given time during backups.

mariadb-backup --backup \
      --innodb-open-files=10

`--innodb-page-size`

Defines the universal page size.

--innodb-page-size=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the universal page size in bytes for mariadb-backup.

mariadb-backup --backup \
     --innodb-page-size=16k

`--innodb-read-io-threads`

Defines the number of background read I/O threads in InnoDB.

--innodb-read-io-threads=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set the number of I/O threads MariaDB uses when reading from InnoDB.

mariadb-backup --backup \
      --innodb-read-io-threads=4

`--innodb-undo-directory`

Defines the directory for the undo tablespace files.

--innodb-undo-directory=PATH

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the path to the directory where you want MariaDB to store the undo tablespace on InnoDB tables. The path can be absolute.

mariadb-backup --backup \
     --innodb-undo-directory=/path/to/innodb_undo

`--innodb-undo-tablespaces`

Defines the number of undo tablespaces to use.

--innodb-undo-tablespaces=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the number of undo tablespaces you want to use during the backup.

mariadb-backup --backup \
      --innodb-undo-tablespaces=10

`--innodb-use-native-aio`

Defines whether you want to use native AI/O.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can enable the use of the native asynchronous I/O subsystem. It is only available on Linux operating systems.

mariadb-backup --backup \
      --innodb-use-native-aio

`--innodb-write-io-threads`

Defines the number of background write I/O threads in InnoDB.

--innodb-write-io-threads=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set the number of background write I/O threads mariadb-backup uses.

mariadb-backup --backup \
     --innodb-write-io-threads=4

`--kill-long-queries-timeout`

Defines the timeout for blocking queries.

--kill-long-queries-timeout=#

When mariadb-backup runs, it issues a FLUSH TABLES WITH READ LOCK statement. It then identifies blocking queries. Using this option you can set a timeout in seconds for these blocking queries. When the time runs out, mariadb-backup kills the queries.

The default value is 0, which causes mariadb-backup to not attempt killing any queries.

mariadb-backup --backup \
      --kill-long-queries-timeout=10

`--kill-long-query-type`

Defines the query type the utility can kill to unblock the global lock.

--kill-long-query-type=ALL | UPDATE | SELECT

When mariadb-backup encounters a query that sets a global lock, it can kill the query in order to free up MariaDB Server for the backup. Using this option, you can choose the types of query it kills: SELECT, UPDATE, or both set with ALL. The default is ALL.

mariadb-backup --backup \
      --kill-long-query-type=UPDATE

`--lock-ddl-per-table`

Prevents DDL for each table to be backed up by acquiring MDL lock on that. NOTE: Unless --no-lock option was also specified, conflicting DDL queries , are killed at the end of backup This is done avoid deadlock between "FLUSH TABLE WITH READ LOCK", user's DDL query (ALTER, RENAME), and MDL lock on table.

`--log`

This option has no functionality. It is set to ensure compatibility with MySQL.

`--log-bin`

Defines the base name for the log sequence.

--log-bin[=name]

Using this option you, you can set the base name for mariadb-backup to use in log sequences.

`--log-copy-interval`

Defines the copy interval between checks done by the log copying thread.

--log-copy-interval=#

Using this option, you can define the copy interval mariadb-backup uses between checks done by the log copying thread. The given value is in milliseconds.

mariadb-backup --backup \
      --log-copy-interval=50

`--log-innodb-page-corruption`

Continue backup if InnoDB corrupted pages are found. The pages are logged in innodb_corrupted_pages and backup is finished with error. --prepare will try to fix corrupted pages. If innodb_corrupted_pages exists after --prepare in base backup directory, backup still contains corrupted pages and can not be considered as consistent.

Added in MariaDB 10.5.9

`--move-back`

Restores the backup to the data directory.

Using this command, mariadb-backup moves the backup from the target directory to the data directory, as defined by the --datadir option. You must stop the MariaDB Server before running this command. The data directory must be empty. If you want to overwrite the data directory with the backup, use the --force-non-empty-directories option.

mariadb-backup --move-back \
      --datadir=/var/mysql

Running the --move-back command moves the backup files to the data directory. Use this command if you don't want to save the backup for later. If you do want to save the backup for later, use the --copy-back command.

`--mysqld`

Used internally to prepare a backup.

`--no-backup-locks`

mariadb-backup locks the database by default when it runs. This option disables support for Percona Server's backup locks.

When backing up Percona Server, mariadb-backup would use backup locks by default. To be specific, backup locks refers to the LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP statements. This option can be used to disable support for Percona Server's backup locks. This option has no effect when the server does not support Percona's backup locks.

Deprecated and has no effect from , , and as MariaDB will now always use backup locks for better performance. See MDEV-32932.

mariadb-backup --backup --no-backup-locks

`--no-lock`

Disables table locks with the FLUSH TABLE WITH READ LOCK statement.

Using this option causes mariadb-backup to disable table locks with the FLUSH TABLE WITH READ LOCK statement. Only use this option if:

You are not executing DML statements on non-InnoDB tables during the backup. This includes the mysql database system tables (which are MyISAM).
You are not executing any DDL statements during the backup.
You are not using the file "xtrabackup_binlog_info", which is not consistent with the data when --no-lock is used. Use the file "xtrabackup_binlog_pos_innodb" [link] instead.
All tables you're backing up use the InnoDB storage engine.

mariadb-backup --backup --no-lock

If you're considering --no-lock due to backups failing to acquire locks, this may be due to incoming replication events preventing the lock. Consider using the --safe-slave-backup option to momentarily stop the replica thread. This alternative may help the backup to succeed without resorting to --no-lock.

The --no-lock option only provides a consistent backup if the user ensures that no DDL or non-transactional table updates occur during the backup. The --no-lock option is not supported by MariaDB plc.

`--no-timestamp`

This option prevents creation of a time-stamped subdirectory of the BACKUP-ROOT-DIR given on the command line. When it is specified, the backup is done in BACKUP-ROOT-DIR instead. This is only valid in innobackupex mode, which can be enabled with the --innobackupex option.

`--no-version-check`

Disables version check.

Using this option, you can disable mariadb-backup version check.

mariadb-backup --backup --no-version-check

`--open-files-limit`

Defines the maximum number of file descriptors.

--open-files-limit=#

Using this option, you can define the maximum number of file descriptors mariadb-backup reserves with setrlimit().

mariadb-backup --backup \
      --open-files-limit=

`--parallel`

Defines the number of threads to use for parallel data file transfer.

--parallel=#

Using this option, you can set the number of threads mariadb-backup uses for parallel data file transfers. By default, it is set to 1.

`-p, --password`

Defines the password to use to connect to MariaDB Server.

--password=passwd

When you run mariadb-backup, it connects to MariaDB Server in order to access and back up the databases and tables. Using this option, you can set the password mariadb-backup uses to access the server. To set the user, use the --user option.

mariadb-backup --backup \
      --user=root \
      --password=root_password

`--plugin-dir`

Defines the directory for server plugins.

--plugin-dir=PATH

Using this option, you can define the path mariadb-backup reads for MariaDB Server plugins. It only uses it during the --prepare phase to load the encryption plugin. It defaults to the plugin_dir server system variable.

mariadb-backup --backup \
      --plugin-dir=/var/mysql/lib/plugin

`--plugin-load`

Defines the encryption plugins to load.

--plugin-load=name

Using this option, you can define the encryption plugin you want to load. It is only used during the --prepare phase to load the encryption plugin. It defaults to the server --plugin-load option.

The option was removed.

`-P, --port`

Defines the server port to connect to.

--port=#

When you run mariadb-backup, it connects to MariaDB Server in order to access and back up your databases and tables. Using this option, you can set the port the utility uses to access the server over TCP/IP. To set the host, see the --host option. Use mysql --help for more details.

mariadb-backup --backup \
      --host=192.168.11.1 \
      --port=3306

`--prepare`

Prepares an existing backup to restore to the MariaDB Server.

Files that mariadb-backup generates during --backup operations in the target directory are not ready for use on the Server. Before you can restore the data to MariaDB, you first need to prepare the backup.

In the case of full backups, the files are not point in time consistent, since they were taken at different times. If you try to restore the database without first preparing the data, InnoDB rejects the new data as corrupt. Running mariadb-backup with the --prepare command readies the data so you can restore it to MariaDB Server. When working with incremental backups, you need to use the --prepare command and the --incremental-dir option to update the base backup with the deltas from an incremental backup.

mariadb-backup --prepare

Once the backup is ready, you can use the --copy-back or the --move-back commands to restore the backup to the server.

`--print-defaults`

Prints the utility argument list, then exits.

Using this argument, MariaDB prints the argument list to stdout and then exits. You may find this useful in debugging to see how the options are set for the utility.

mariadb-backup --print-defaults

`--print-param`

Prints the MariaDB Server options needed for copyback.

Using this option, mariadb-backup prints to stdout the MariaDB Server options that the utility requires to run the --copy-back command option.

mariadb-backup --print-param

`--rollback-xa`

By default, mariadb-backup will not commit or rollback uncommitted XA transactions, and when the backup is restored, any uncommitted XA transactions must be manually committed using XA COMMIT or manually rolled back using XA ROLLBACK.

MariaDB starting with 10.5

mariadb-backup's --rollback-xa option is not present because the server has more robust ways of handling uncommitted XA transactions.

This is an experimental option. Do not use this option in older versions. Older implementation can cause corruption of InnoDB data.

`--rsync`

Defines whether to use rsync.

During normal operation, mariadb-backup transfers local non-InnoDB files using a separate call to cp for each file. Using this option, you can optimize this process by performing this transfer with rsync, instead.

mariadb-backup --backup --rsync

This option is not compatible with the --stream option.

Deprecated and has no effect from , , and as rsync will not work on tables that are in use. See MDEV-32932.

`--safe-slave-backup`

Stops replica SQL threads for backups.

When running mariadb-backup on a server that uses replication, you may occasionally encounter locks that block backups. Using this option, it stops replica SQL threads and waits until the Slave_open_temp_tables in the SHOW STATUS statement is zero. If there are no open temporary tables, the backup runs, otherwise the SQL thread starts and stops until there are no open temporary tables.

mariadb-backup --backup \
      --safe-slave-backup \
      --safe-slave-backup-timeout=500

The backup fails if the Slave_open_temp_tables doesn't reach zero after the timeout period set by the --safe-slave-backup-timeout option.

`--safe-slave-backup-timeout`

Defines the timeout for replica backups.

--safe-slave-backup-timeout=#

When running mariadb-backup on a server that uses replication, you may occasionally encounter locks that block backups. With the --safe-slave-backup option, it waits until the Slave_open_temp_tables in the SHOW STATUS statement reaches zero. Using this option, you set how long it waits. It defaults to 300.

mariadb-backup --backup \
      --safe-slave-backup \
      --safe-slave-backup-timeout=500

`--secure-auth`

Refuses client connections to servers using the older protocol.

Using this option, you can set it explicitly to refuse client connections to the server when using the older protocol, from before 4.1.1. This feature is enabled by default. Use the --skip-secure-auth option to disable it.

mariadb-backup --backup --secure-auth

`--skip-innodb-adaptive-hash-index`

Disables InnoDB Adaptive Hash Index.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option you can explicitly disable the InnoDB Adaptive Hash Index. This feature is enabled by default for mariadb-backup. If you want to explicitly enable it, use --innodb-adaptive-hash-index.

mariadb-backup --backup \
      --skip-innodb-adaptive-hash-index

`--skip-innodb-doublewrite`

Disables doublewrites for InnoDB tables.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. When doublewrites are enabled, InnoDB improves fault tolerance with a doublewrite buffer. By default this feature is turned on. Using this option you can disable it for mariadb-backup. To explicitly enable doublewrites, use the --innodb-doublewrite option.

mariadb-backup --backup \
     --skip-innodb-doublewrite

`--skip-innodb-log-checksums`

Defines whether to exclude checksums in the InnoDB logs.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set mariadb-backup to exclude checksums in the InnoDB logs. The feature is enabled by default. To explicitly enable it, use the --innodb-log-checksums option.

`--skip-secure-auth`

Refuses client connections to servers using the older protocol.

Using this option, you can set it accept client connections to the server when using the older protocol, from before 4.1.1. By default, it refuses these connections. Use the --secure-auth option to explicitly enable it.

mariadb-backup --backup --skip-secure-auth

`--slave-info`

Prints the binary log position and the name of the primary server.

If the server is a replica, then this option causes mariadb-backup to print the hostname of the replica's replication primary and the binary log file and position of the replica's SQL thread to stdout.

This option also causes mariadb-backup to record this information as a CHANGE MASTER command that can be used to set up a new server as a replica of the original server's primary after the backup has been restored. This information are written to the xtrabackup_slave_info file.

mariadb-backup does not check if GTIDs are being used in replication. It takes a shortcut and assumes that if the gtid_slave_pos system variable is non-empty, then it writes the CHANGE MASTER command with the MASTER_USE_GTID option set to slave_pos. Otherwise, it writes the CHANGE MASTER command with the MASTER_LOG_FILE and MASTER_LOG_POS options using the primary's binary log file and position. See MDEV-19264 for more information.

mariadb-backup --slave-info

`-S, --socket`

Defines the socket for connecting to local database.

--socket=name

Using this option, you can define the UNIX domain socket you want to use when connecting to a local database server. The option accepts a string argument. For more information, see the mysql --help command.

mariadb-backup --backup \
      --socket=/var/mysql/mysql.sock

`--ssl`

Enables TLS. By using this option, you can explicitly configure mariadb-backup to encrypt its connection with TLS when communicating with the server. You may find this useful when performing backups in environments where security is extra important or when operating over an insecure network.

TLS is also enabled even without setting this option when certain other TLS options are set. For example, see the descriptions of the following options:

--ssl-ca
--ssl-capath
--ssl-cert
--ssl-cipher
--ssl-key

`--ssl-ca`

Defines a path to a PEM file that should contain one or more X509 certificates for trusted Certificate Authorities (CAs) to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-ca=/etc/my.cnf.d/certificates/ca.pem

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem

See Secure Connections Overview: Certificate Authorities (CAs) for more information.

This option implies the --ssl option.

`--ssl-capath`

Defines a path to a directory that contains one or more PEM files that should each contain one X509 certificate for a trusted Certificate Authority (CA) to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-capath=/etc/my.cnf.d/certificates/ca/

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --ssl-capath=/etc/my.cnf.d/certificates/ca/

The directory specified by this option needs to be run through the openssl rehash command.

See Secure Connections Overview: Certificate Authorities (CAs) for more information

This option implies the --ssl option.

`--ssl-cert`

Defines a path to the X509 certificate file to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem

This option implies the --ssl option.

`--ssl-cipher`

Defines the list of permitted ciphers or cipher suites to use for TLS. For example:

--ssl-cipher=name

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem
   --ssl-cipher=TLSv1.2

To determine if the server restricts clients to specific ciphers, check the ssl_cipher system variable.

This option implies the --ssl option.

`--ssl-crl`

Defines a path to a PEM file that should contain one or more revoked X509 certificates to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-crl=/etc/my.cnf.d/certificates/crl.pem

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --ssl-crl=/etc/my.cnf.d/certificates/crl.pem

See Secure Connections Overview: Certificate Revocation Lists (CRLs) for more information.

This option is only supported if mariadb-backup was built with OpenSSL. If mariadb-backup was built with yaSSL, then this option is not supported. See TLS and Cryptography Libraries Used by MariaDB for more information about which libraries are used on which platforms.

`--ssl-crlpath`

Defines a path to a directory that contains one or more PEM files that should each contain one revoked X509 certificate to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-crlpath=/etc/my.cnf.d/certificates/crl/

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --ssl-crlpath=/etc/my.cnf.d/certificates/crl/

The directory specified by this option needs to be run through the openssl rehash command.

See Secure Connections Overview: Certificate Revocation Lists (CRLs) for more information.

`--ssl-key`

Defines a path to a private key file to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-key=/etc/my.cnf.d/certificates/client-key.pem

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem

This option implies the --ssl option.

`--ssl-verify-server-cert`

Enables server certificate verification. This option is disabled by default.

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --ssl-verify-server-cert

`--stream`

Streams backup files to stdout.

--stream=xbstream

Using this command option, you can set mariadb-backup to stream the backup files to stdout in the given format. Currently, the supported format is xbstream.

mariadb-backup --stream=xbstream > backup.xb

To extract all files from the xbstream archive into a directory use the mbstream utility

mbstream  -x < backup.xb

If a backup is streamed, then mariadb-backup will record the format in the xtrabackup_info file.

`--tables`

Defines the tables you want to include in the backup.

--tables=REGEX

Using this option, you can define what tables you want mariadb-backup to back up from the database. The table values are defined using Regular Expressions. To define the tables you want to exclude from the backup, see the --tables-exclude option.

mariadb-backup --backup \
     --databases=example
     --tables=nodes_* \
     --tables-exclude=nodes_tmp

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

`--tables-exclude`

Defines the tables you want to exclude from the backup.

--tables-exclude=REGEX

Using this option, you can define what tables you want mariadb-backup to exclude from the backup. The table values are defined using Regular Expressions. To define the tables you want to include from the backup, see the --tables option.

mariadb-backup --backup \
     --databases=example
     --tables=nodes_* \
     --tables-exclude=nodes_tmp

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

`--tables-file`

Defines path to file with tables for backups.

--tables-file=/path/to/file

Using this option, you can set a path to a file listing the tables you want to back up. mariadb-backup iterates over each line in the file. The format is database.table.

mariadb-backup --backup \
     --databases=example \
     --tables-file=/etc/mysql/backup-file

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

`--target-dir`

Defines the destination directory.

--target-dir=/path/to/target

Using this option you can define the destination directory for the backup. mariadb-backup writes all backup files to this directory. mariadb-backup will create the directory, if it does not exist (but it will not create the full path recursively, i.e. at least parent directory if the --target-dir must exist=

mariadb-backup --backup \
       --target-dir=/data/backups

`--throttle`

Defines the limit for I/O operations per second in IOS values.

--throttle=#

Using this option, you can set a limit on the I/O operations mariadb-backup performs per second in IOS values. It is only used during the --backup command option.

`--tls-version`

This option accepts a comma-separated list of TLS protocol versions. A TLS protocol version will only be enabled if it is present in this list. All other TLS protocol versions will not be permitted. For example:

--tls-version="TLSv1.2,TLSv1.3"

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --tls-version="TLSv1.2,TLSv1.3"

See Secure Connections Overview: TLS Protocol Versions for more information.

`-t, --tmpdir`

Defines path for temporary files.

--tmpdir=/path/tmp[;/path/tmp...]

Using this option, you can define the path to a directory mariadb-backup uses in writing temporary files. If you want to use more than one, separate the values by a semicolon (that is, ;). When passing multiple temporary directories, it cycles through them using round-robin.

mariadb-backup --backup \
     --tmpdir=/data/tmp;/tmp

`--use-memory`

Defines the buffer pool size that is used during the prepare stage.

--use-memory=124M

Using this option, you can define the buffer pool size for mariadb-backup. Use it instead of buffer_pool_size.

mariadb-backup --prepare \
      --use-memory=124M

`--user`

Defines the username for connecting to the MariaDB Server.

--user=name
-u name

When mariadb-backup runs, it connects to the specified MariaDB Server to get its backups. Using this option, you can define the database user used for authentication. Starting from MariaDB 10.5.24, , , , , , , , if the --user option is ommited, the user name is detected from the OS.

mariadb-backup --backup \
      --user=root \
      --password=root_passwd

`--verbose`

Displays verbose output

mariadb-backup --verbose

`--version`

Prints version information.

Using this option, you can print the mariadb-backup version information to stdout.

mariadb-backup --version

_{This page is licensed: CC BY-SA / Gnu FDL}

Server

MariaDB Server Documentation

Quickstart Guides

Installing MariaDB Server Guide

For Linux (Ubuntu/Debian/Red Hat-based distributions)

For Windows

Important Notes:

Additional Resources:

Essentials of an Index Guide

Basic SQL Statements Guide

Defining How Your Data Is Stored (Data Definition Language - DDL)

Manipulating Your Data (Data Manipulation Language - DML)

Transactions (Transaction Control Language - TCL)

A Simple Example Sequence

Restoring Data from Dump Files Guide

Basic Restoration Process

Important Considerations Before Restoring

Restoring a Single Table Selectively

Changing Times in MariaDB

Calculating Time Across Midnight

Tracking Date Changes with Time: Using DATETIME

Adding Durations with DATE_ADD

Date Calculations Across Months and Years with DATE_ADD

Subtracting Durations

Making Backups with mariadb-dump Guide

About mariadb-dump

Backing Up All Databases

Backing Up a Single Database

Backing Up Specific Tables

Important Considerations and Best Practices

Server Usage

Basics

Connecting

Data Handling

Backup & Restore

Forming a Backup Strategy

Overview

Data Inventory

Recovery Objectives

Achieving RPO

Achieving RTO

Replication Considerations

Encryption Considerations

Backup Storage Considerations

Backup Testing

Backup Optimization

Overview

Scheduling of Restore Preparation

Moving Restored Data

Multithreading

Incrementing an Incremental Backup

Storage Snapshots

Taking Snapshots

mariadb-backup

Full Backup and Restore with mariadb-backup

Backing up the Database Server

Preparing the Backup for Restoration

Backup Preparation Steps

Restoring the Backup

Restoring with Other Tools

Partial Backup and Restore with mariadb-backup

Backing up the Database Server

Preparing the Backup

Restoring the Backup

Restoring Individual Non-Partitioned Tables

Restoring Individual Partitions and Partitioned Tables

Restoring Individual Tables and Partitions with mariadb-backup

Preparing the Backup

Restoring the Backup

Restoring Individual Non-Partitioned Tables

Restoring Individual Partitions and Partitioned Tables

Setting up a Replica with mariadb-backup

Backup the Database and Prepare It

Copy the Backup to the New Replica

Restore the Backup on the New Replica

Create a Replication User on the Primary

Configure the New Replica

Start Replication on the New Replica

GTIDs

File and Position

Tracking Date Changes with Time: Using `DATETIME`

Adding Durations with `DATE_ADD`

Date Calculations Across Months and Years with `DATE_ADD`

About `mariadb-dump`