1 of 100

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.

A MariaDB Primer

This primer is designed to teach you the basics of getting information into and out of an existing MariaDB database using the command-line client program. It's not a complete reference and will not touch on any advanced topics. It is just a quick jump-start into using MariaDB.

Logging into MariaDB

Log into your MariaDB server from the command-line like so:

Replace user_name with your database username. Replace ip_address with the host name or address of your server. If you're accessing MariaDB from the same server you're logged into, then don't include -h and the ip_address. Replace db_name with the name of the database you want to access (such as test, which sometimes comes already created for testing purposes - note that Windows does not create this database, and some setups may also have removed the test database by running , in which case you can leave the db_name out).

When prompted to enter your password, enter it. If your login is successful you should see something that looks similar to this:

This is where you will enter in all of your SQL statements. More about those later. For now, let's look at the components of the prompt: The "MariaDB" part means you that you are connected to a MariaDB database server. The word between the brackets is the name of your default database, the test database in this example.

The Basics of a Database

To make changes to a database or to retrieve data, you will need to enter an SQL statement. SQL stands for Structured Query Language. An SQL statement that requests data is called a query. Databases store information in tables. They're are similar to spreadsheets, but much more efficient at managing data.

Note that the test database may not contain any data yet. If you want to follow along with the primer, copy and paste the following into the client. This will create the tables we will use and add some data to them. Don't worry about understanding them yet; we'll get to that later.

Notice the semi-colons used above. The client lets you enter very complex SQL statements over multiple lines. It won't send an SQL statement until you type a semi-colon and hit [Enter].

Let's look at what you've done so far. Enter the following:

Notice that this displays a list of the tables in the database. If you didn't already have tables in your test database, your results should look the same as above. Let's now enter the following to get information about one of these tables:

The main bit of information of interest to us is the Field column. The other columns provide useful information about the structure and type of data in the database, but the Field column gives us the names, which is needed to retrieve data from the table.

Let's retrieve data from the books table. We'll do so by executing a statement like so:

This SQL statement or query asks the database to show us all of the data in the books table. The wildcard ('*') character indicates to select all columns.

Inserting Data

Suppose now that we want to add another book to this table. We'll add the book, Lair of Bones. To insert data into a table, you would use the statement. To insert information on a book, we would enter something like this:

Notice that we put a list of columns in parentheses after the name of the table, then we enter the keyword VALUES followed by a list of values in parentheses--in the same order as the columns were listed. We could put the columns in a different order, as long as the values are in the same order as we list the columns. Notice the message that was returned indicates that the execution of the SQL statement went fine and one row was entered.

Execute the following SQL statement again and see what results are returned:

You should see the data you just entered on the last row of the results. In looking at the data for the other books, suppose we notice that the title of the seventh book is spelled wrong. It should be spelled The Hobbit, not The Hobbbit. We will need to update the data for that row.

Modifying Data

To change data in a table, you will use the statement. Let's change the spelling of the book mentioned above. To do this, enter the following:

Notice the syntax of this SQL statement. The SET clause is where you list the columns and the values to set them. The WHERE clause says that you want to update only rows in which the BookID column has a value of 7, of which there are only one. You can see from the message it returned that one row matched the WHERE clause and one row was changed. There are no warnings because everything went fine. Execute the from earlier to see that the data changed.

As you can see, using MariaDB isn't very difficult. You just have to understand the syntax of SQL since it doesn't allow for typing mistakes or things in the wrong order or other deviations.

Basic Queries

Connecting to MariaDB

MariaDB is a database system, a database server. To interface with the MariaDB server, you can use a client program, or you can write a program or script with one of the popular programming languages (e.g., PHP) using an API (Application Programming Interface) to interface with the MariaDB server. For the purposes of this article, we will focus on using the default client that comes with MariaDB called mariadb. With this client, you can either enter queries from the command-line, or you can switch to a terminal, that is to say, monitor mode. To start, we'll use the latter.

From the Linux command-line, you would enter the following to log in as the root user and to enter monitor mode:

mariadb -u root -p -h localhost

The -u option is for specifying the user name. You would replace root here if you want to use a different user name. This is the MariaDB user name, not the Linux user name. The password for the MariaDB user root will probably be different from the Linux user root. Incidentally, it's not a good security practice to use the root user unless you have a specific administrative task to perform for which only root has the needed privileges.

The -p option above instructs the mariadb client to prompt you for the password. If the password for the root user hasn't been set yet, then the password is blank and you would just hit [Enter] when prompted. The -h option is for specifying the host name or the IP address of the server. This would be necessary if the client is running on a different machine than the server. If you've secure-shelled into the server machine, you probably won't need to use the host option. In fact, if you're logged into Linux as root, you won't need the user option—the -p is all you'll need. Once you've entered the line above along with the password when prompted, you will be logged into MariaDB through the client. To exit, type quit or exit and press [Enter].

Creating a Structure

In order to be able to add and to manipulate data, you first have to create a database structure. Creating a database is simple. You would enter something like the following from within the mariadb client:

CREATE DATABASE bookstore;

USE bookstore;

This very minimal, first SQL statement will create a sub-directory called bookstore on the Linux filesystem in the directory which holds your MariaDB data files. It won't create any data, obviously. It'll just set up a place to add tables, which will in turn hold data. The second SQL statement above will set this new database as the default database. It will remain your default until you change it to a different one or until you log out of MariaDB.

The next step is to begin creating tables. This is only a little more complicated. To create a simple table that will hold basic data on books, we could enter something like the following:

CREATE TABLE books (
isbn CHAR(20) PRIMARY KEY, 
title VARCHAR(50),
author_id INT,
publisher_id INT,
year_pub CHAR(4),
description TEXT );

This SQL statement creates the table books with six fields, or rather columns. The first column (isbn) is an identification number for each row—this name relates to the unique identifier used in the book publishing business. It has a fixed-width character type of 20 characters. It will be the primary key column on which data will be indexed. The column data type for the book title is a variable width character column of fifty characters at most. The third and fourth columns will be used for identification numbers for the author and the publisher. They are integer data types. The fifth column is used for the publication year of each book. The last column is for entering a description of each book. It's a TEXT data type, which means that it's a variable width column and it can hold up to 65535 bytes of data for each row. There are several other data types that may be used for columns, but this gives you a good sampling.

To see how the table we created looks, enter the following SQL statement:

DESCRIBE books;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| isbn         | char(20)    | NO   | PRI | NULL    |       |
| title        | varchar(50) | YES  |     | NULL    |       |
| author_id    | int(11)     | YES  |     | NULL    |       |
| publisher_id | int(11)     | YES  |     | NULL    |       |
| year_pub     | char(4)     | YES  |     | NULL    |       |
| description  | text        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

To change the settings of a table, you can use the ALTER TABLE statement. I'll cover that statement in another article. To delete a table completely (including its data), you can use the DROP TABLE statement, followed by the table name. Be careful with this statement since it's not reversible.

The next table we'll create for our examples is the authors table to hold author information. This table will save us from having to enter the author's name and other related data for each book written by each author. It also helps to ensure consistency of data: there's less chance of inadvertent spelling deviations.

CREATE TABLE authors
(author_id INT AUTO_INCREMENT PRIMARY KEY,
name_last VARCHAR(50),
name_first VARCHAR(50),
country VARCHAR(50) );

We'll join this table to the books table as needed. For instance, we would use it when we want a list of books along with their corresponding authors' names. For a real bookstore's database, both of these tables would probably have more columns. There would also be several more tables. For the examples that follow, these two tables as they are will be enough.

Minor Items

Before moving on to the next step of adding data to the tables, let me point out a few minor items that I've omitted mentioning. SQL statements end with a semi-colon (or a \G). You can spread an SQL statement over multiple lines. However, it won't be passed to the server by the client until you terminate it with a semi-colon and hit [Enter]. To cancel an SQL statement once you've started typing it, enter \c and press [Enter].

As a basic convention, reserved words are printed in all capital letters. This isn't necessary, though. MariaDB is case-insensitive with regards to reserved words. Database and table names, however, are case-sensitive on Linux. This is because they reference the related directories and files on the filesystem. Column names aren't case sensitive since they're not affected by the filesystem, per se. As another convention, we use lower-case letters for structural names (e.g., table names). It's a matter of preference for deciding on names.

Entering Data

The primary method for entering data into a table is to use the INSERT statement. As an example, let's enter some information about an author into the authors table. We'll do that like so:

INSERT INTO authors
(name_last, name_first, country)
VALUES('Kafka', 'Franz', 'Czech Republic');

This will add the name and country of the author Franz Kafka to the authors table. We don't need to give a value for the author_id since that column was created with the AUTO_INCREMENT option. MariaDB will automatically assign an identification number. You can manually assign one, especially if you want to start the count at a higher number than 1 (e.g., 1000). Since we are not providing data for all of the columns in the table, we have to list the columns for which we are giving data and in the order that the data is given in the set following the VALUES keyword. This means that we could give the data in a different order.

For an actual database, we would probably enter data for many authors. We'll assume that we've done that and move on to entering data for some books. Below is an entry for one of Kafka's books:

INSERT INTO books
(title, author_id, isbn, year_pub)
VALUES('The Castle', '1', '0805211063', '1998');

This adds a record for Kafka's book, The Castle. Notice that we mixed up the order of the columns, but it still works because both sets agree. We indicate that the author is Kafka by giving a value of 1 for the author_id. This is the value that was assigned by MariaDB when we entered the row for Kafka earlier. Let's enter a few more books for Kafka, but by a different method:

INSERT INTO books
(title, author_id, isbn, year_pub)
VALUES('The Trial', '1', '0805210407', '1995'),
('The Metamorphosis', '1', '0553213695', '1995'),
('America', '1', '0805210644', '1995');

In this example, we've added three books in one statement. This allows us to give the list of column names once. We also give the keyword VALUES only once, followed by a separate set of values for each book, each contained in parentheses and separated by commas. This cuts down on typing and speeds up the process. Either method is fine and both have their advantages. To be able to continue with our examples, let's assume that data on thousands of books has been entered. With that behind us, let's look at how to retrieve data from tables.

Retrieving Data

The primary method of retrieving data from tables is to use a SELECT statement. There are many options available with the SELECT statement, but you can start simply. As an example, let's retrieve a list of book titles from the books table:

SELECT title 
FROM books;

This will display all of the rows of books in the table. If the table has thousands of rows, MariaDB will display thousands. To limit the number of rows retrieved, we could add a LIMIT clause to the SELECT statement like so:

SELECT title 
FROM books
LIMIT 5;

This will limit the number of rows displayed to five. To be able to list the author's name for each book along with the title, you will have to join the books table with the authors table. To do this, we can use the JOIN clause like so:

SELECT title, name_last 
FROM books 
JOIN authors USING (author_id);

Notice that the primary table from which we're drawing data is given in the FROM clause. The table to which we're joining is given in the JOIN clause along with the commonly named column (i.e., author_id) that we're using for the join.

To retrieve the titles of only books written by Kafka based on his name (not the author_id), we would use the WHERE clause with the SELECT statement. This would be entered like the following:

SELECT title AS 'Kafka Books'
FROM books 
JOIN authors USING (author_id)
WHERE name_last = 'Kafka';

+-------------------+
| Kafka Books       |
+-------------------+
| The Castle        |
| The Trial         |
| The Metamorphosis |
| America           |
+-------------------+

This statement will list the titles of Kafka books stored in the database. Notice that I've added the AS parameter next to the column name title to change the column heading in the results set to Kafka Books. This is known as an alias. Looking at the results here, we can see that the title for one of Kafka's books is incorrect. His book Amerika is spelled above with a "c" in the table instead of a "k". This leads to the next section on changing data.

Changing & Deleting Data

In order to change existing data, a common method is to use the UPDATE statement. When changing data, though, we need to be sure that we change the correct rows. In our example, there could be another book with the title America written by a different author. Since the key column isbn has only unique numbers and we know the ISBN number for the book that we want to change, we can use it to specify the row.

UPDATE books
SET title = 'Amerika'
WHERE isbn = '0805210644';

This will change the value of the title column for the row specified. We could change the value of other columns for the same row by giving the column = value for each, separated by commas.

If we want to delete a row of data, we can use the DELETE statement. For instance, suppose that our fictitious bookstore has decided no longer to carry books by John Grisham. By first running a SELECT statement, we determine the identification number for the author to be 2034. Using this author identification number, we could enter the following:

DELETE FROM books
WHERE author_id = '2034';

This statement will delete all rows from the table books for the author_id given. To do a clean job of it, we'll have to do the same for the authors table. We would just replace the table name in the statement above; everything else would be the same.

Conclusion

This is a very basic primer for using MariaDB. Hopefully, it gives you the idea of how to get started with MariaDB. Each of the SQL statements mentioned here have several more options and clauses each. We will cover these statements and others in greater detail in other articles. For now, though, you can learn more about them from experimenting and by further reading of the documentation online documentation.

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

Basic SQL Debugging

Designing Queries

Following a few conventions makes finding errors in queries a lot easier, especially when you ask for help from people who might know SQL, but know nothing about your particular schema. A query easy to read is a query easy to debug. Use whitespace to group clauses within the query. Choose good table and field aliases to add clarity, not confusion. Choose the syntax that supports the query's meaning.

Using Whitespace

A query hard to read is a query hard to debug. White space is free. New lines and indentation make queries easy to read, particularly when constructing a query inside a scripting language, where variables are interspersed throughout the query.

There is a syntax error in the following. How fast can you find it?

SELECT u.id, u.name, alliance.ally FROM users u JOIN alliance ON
(u.id=alliance.userId) JOIN team ON (alliance.teamId=team.teamId
WHERE team.teamName='Legionnaires' AND u.online=1 AND ((u.subscription='paid'
AND u.paymentStatus='current') OR u.subscription='free') ORDER BY u.name;

Here's the same query, with correct use of whitespace. Can you find the error faster?

SELECT
    u.id
    , u.name
    , alliance.ally
FROM
    users u
    JOIN alliance ON (u.id = alliance.userId)
    JOIN team ON (alliance.teamId = team.teamId
WHERE
    team.teamName = 'Legionnaires'
    AND u.online = 1
    AND (
        (u.subscription = 'paid' AND u.paymentStatus = 'current')
        OR
        u.subscription = 'free'
    )
ORDER BY
    u.name;

Even if you don't know SQL, you might still have caught the missing ')' following team.teamId.

The exact formatting style you use isn't so important. You might like commas in the select list to follow expressions, rather than precede them. You might indent with tabs or with spaces. Adherence to some particular form is not important. Legibility is the only goal.

Table and Field Aliases

Aliases allow you to rename tables and fields for use within a query. This can be handy when the original names are very long, and is required for self joins and certain subqueries. However, poorly chosen aliases can make a query harder to debug, rather than easier. Aliases should reflect the original table name, not an arbitrary string.

Bad:

SELECT *
FROM
    financial_reportQ_1 AS a
    JOIN sales_renderings AS b ON (a.salesGroup = b.groupId)
    JOIN sales_agents AS c ON (b.groupId = c.group)
WHERE
    b.totalSales > 10000
    AND c.id != a.clientId

As the list of joined tables and the WHERE clause grow, it becomes necessary to repeatedly look back to the top of the query to see to which table any given alias refers.

Better:

SELECT *
FROM
    financial_report_Q_1 AS frq1
    JOIN sales_renderings AS sr ON (frq1.salesGroup = sr.groupId)
    JOIN sales_agents AS sa ON (sr.groupId = sa.group)
WHERE
    sr.totalSales > 10000
    AND sa.id != frq1.clientId

Each alias is just a little longer, but the table initials give enough clues that anyone familiar with the database only need see the full table name once, and can generally remember which table goes with which alias while reading the rest of the query.

Placing JOIN conditions

The manual warns against using the JOIN condition (that is, the ON clause) for restricting rows. Some queries, particularly those using implicit joins, take the opposite extreme - all join conditions are moved to the WHERE clause. In consequence, the table relationships are mixed with the business logic.

Bad:

SELECT *
FROM
    family,
    relationships
WHERE
    family.personId = relationships.personId
    AND relationships.relation = 'father'

Without digging through the WHERE clause, it is impossible to say what links the two tables.

Better:

SELECT *
FROM
    family
    JOIN relationships ON (family.personId = relationships.personId)
WHERE
    relationships.relation = 'father'

The relation between the tables is immediately obvious. The WHERE clause is left to limit rows in the result set.

Compliance with such a restriction negates the use of the comma operator to join tables. It is a small price to pay. Queries should be written using the explicit JOIN keyword anyway, and the two should never be mixed (unless you like rewriting all your queries every time a new version changes operator precedence).

Finding Syntax Errors

Syntax errors are among the easiest problems to solve. MariaDB provides an error message showing the exact point where the parser became confused. Check the query, including a few words before the phrase shown in the error message. Most syntax and parsing errors are obvious after a second look, but some are more elusive, especially when the error text seems empty, points to a valid keyword, or seems to error on syntax that appears exactly correct.

Interpreting the Empty Error

Most syntax errors are easy to interpret. The error generally details the exact source of the trouble. A careful look at the query, with the error message in mind, often reveals an obvious mistake, such as misspelled field names, a missing 'AND', or an extra closing parenthesis. Sometimes the error is a little less helpful. A frequent, less-than-helpful message:

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your
MariaDB server version for the right syntax to use near ' ' at line 1

The empty ' ' can be disheartening. Clearly there is an error, but where? A good place to look is at the end of the query. The ' ' suggests that the parser reached the end of the statement while still expecting some syntax token to appear.

Check for missing closers, such as ' and ):

SELECT * FROM someTable WHERE field = 'value

Look for incomplete clauses, often indicated by an exposed comma:

SELECT * FROM someTable WHERE field = 1 GROUP BY id,

Checking for keywords

MariaDB allows table and field names and aliases that are also reserved words. To prevent ambiguity, such names must be enclosed in backticks (`):

SELECT * FROM actionTable WHERE `DELETE` = 1;

If the syntax error is shown near one of your identifiers, check if it appears on the reserved word list.

A text editor with color highlighting for SQL syntax helps to find these errors. When you enter a field name, and it shows up in the same color as the SELECT keyword, you know something is amiss. Some common culprits:

DESC is a common abbreviation for "description" fields. It means "descending" in a MariaDB ORDER clause.
DATE, TIME, and TIMESTAMP are all common field names. They are also field types.
ORDER appears in sales applications. MariaDB uses it to specify sorting for results.

Some keywords are so common that MariaDB makes a special allowance to use them unquoted. My advice: don't. If it's a keyword, quote it.

Version specific syntax

As MariaDB adds new features, the syntax must change to support them. Most of the time, old syntax will work in newer versions of MariaDB. One notable exception is the change in precedence of the comma operator relative to the JOIN keyword in version 5.0. A query that used to work, such as

SELECT * FROM a, b JOIN c ON a.x = c.x;

will now fail.

More common, however, is an attempt to use new syntax in an old version. Web hosting companies are notoriously slow to upgrade MariaDB, and you may find yourself using a version several years out of date. The result can be very frustrating when a query that executes flawlessly on your own workstation, running a recent installation, fails completely in your production environment.

This query fails in any version of MySQL prior to 4.1, when subqueries were added to the server:

SELECT * FROM someTable WHERE someId IN (SELECT id FROM someLookupTable);

This query fails in some early versions of MySQL, because the JOIN syntax did not originally allow an ON clause:

SELECT * FROM tableA JOIN tableB ON tableA.x = tableB.y;

Always check the installed version of MariaDB, and read the section of the manual relevant for that version. The manual usually indicates exactly when particular syntax became available for use.

_{The initial version of this article was copied, with permission, from}_{Basic_Debugging}_{on 2012-10-05.}

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

Basic SQL Statements

This page lists the most important SQL statements and contains links to their documentation pages. If you need a basic tutorial on how to use the MariaDB database server and how to execute simple commands, see .

Also see for examples of commonly-used queries.

Defining How Your Data Is Stored

is used to create a new, empty database.
is used to completely destroy an existing database.
is used to select a default database.
is used to create a new table, which is where your data is actually stored.
is used to modify an existing table's definition.
is used to completely destroy an existing table.
shows the structure of a table.

Manipulating Your Data

is used when you want to read (or select) your data.
is used when you want to add (or insert) new data.
is used when you want to change (or update) existing data.
is used when you want to remove (or delete) existing data.
is used when you want to add or change (or replace) new or existing data.
is used when you want to empty (or delete) all data from the template.

Transactions

is used to begin a transaction.
is used to apply changes and end transaction.
is used to discard changes and end transaction.

A Simple Example

The first version of this article was copied, with permission, from on 2012-10-05.

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

Commonly Used Queries

This page is intended to be a quick reference of commonly-used and/or useful queries in MariaDB.

Creating a Table

See for more.

Inserting Records

See for more.

Using AUTO_INCREMENT

The AUTO_INCREMENT attribute is used to automatically generate a unique identity for new rows.

When inserting, the id field can be omitted, and is automatically created.

See for more.

Querying from two tables on a common value

This kind of query is called a join - see for more.

Finding the Maximum Value

See the for more, as well as below for a more practical example.

Finding the Minimum Value

See the for more.

Finding the Average Value

See the for more.

Finding the Maximum Value and Grouping the Results

See the for more.

Ordering Results

See for more.

Finding the Row with the Minimum of a Particular Column

In this example, we want to find the lowest test score for any student.

Finding Rows with the Maximum Value of a Column by Group

This example returns the best test results of each student:

Calculating Age

The function can be used to calculate someone's age:

See for more.

Using User-defined Variables

This example sets a with the average test score, and then uses it in a later query to return all results above the average.

User-defined variables can also be used to add an incremental counter to a resultset:

See for more.

View Tables in Order of Size

Returns a list of all tables in the database, ordered by size:

Removing Duplicates

This example assumes there's a unique ID, but that all other fields are identical. In the example below, there are 4 records, 3 of which are duplicates, so two of the three duplicates need to be removed. The intermediate SELECT is not necessary, but demonstrates what is being returned.

CC BY-SA / Gnu FDL

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.

Backup and Restore with MariaDB Enterprise Server

Learn to back up and restore MariaDB Enterprise Server. This section details specialized tools and methods for ensuring robust data protection and efficient recovery in enterprise environments.

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:

Multi-threading

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:

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.

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:

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

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.

Snapshotting with MariaDB Enterprise Server

MariaDB Enterprise Server includes 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.

Snapshotting with MariaDB Community Server

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.

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.

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:

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

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:

$ ls /var/mariadb/backup/

aria_log.0000001  mysql                   xtrabackup_checkpoints
aria_log_control  performance_schema      xtrabackup_info
backup-my.cnf     test                    xtrabackup_logfile
ibdata1           xtrabackup_binlog_info

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.

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

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:

$ mariadb-backup --copy-back \
   --target-dir=/var/mariadb/backup/

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:

$ chown -R mysql:mysql /var/lib/mysql/

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:

$ rsync -avrP /var/mariadb/backup /var/lib/mysql/
$ chown -R mysql:mysql /var/lib/mysql/

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

Incremental Backup and Restore with mariadb-backup

When using mariadb-backup, you have the option of performing a full or incremental backup. Full backups create a complete copy in an empty directory while incremental backups update a previous backup with new data. This page documents incremental backups.

InnoDB pages contain log sequence numbers, or LSN's. Whenever you modify a row on any InnoDB table on the database, the storage engine increments this number. When performing an incremental backup, mariadb-backup checks the most recent LSN for the backup against the LSN's contained in the database. It then updates any of the backup files that have fallen behind.

Backing up the Database Server

In order to take an incremental backup, you first need to take a full backup. 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:

This backs up all databases into the target directory /var/mariadb/backup. If you look in that directory at the xtrabackup_checkpoints file, you can see the LSN data provided by InnoDB.

For example:

Backing up the Incremental Changes

Once you have created a full backup on your system, you can also back up the incremental changes as often as you would like.

In order to perform an incremental backup, 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 incremental changes. The target directory must be empty. You also need to run it with the --incremental-basedir option to tell it the path to the full backup taken above. For example:

This command creates a series of delta files that store the incremental changes in /var/mariadb/inc1. You can find a similar xtrabackup_checkpoints file in this directory, with the updated LSN values.

For example:

To perform additional incremental backups, you can then use the target directory of the previous incremental backup as the incremental base directory of the next incremental backup. For example:

Combining with `--stream` output

When using --stream, e.g for compression or encryption using external tools, the xtrabackup_checkpoints file containing the information where to continue from on the next incremental backup will also be part of the compressed/encrypted backup file, and so not directly accessible by default.

A directory containing an extra copy of the file can be created using the --extra-lsndir=... option though, and this directory can then be passed to the next incremental backup --incremental-basedir=..., for example:

Preparing the Backup

Following the above steps, you have three backups in /var/mariadb: The first is a full backup, the others are increments on this first backup. In order to restore a backup to the database, you first need to apply the incremental backups to the base full backup. This is done using the --prepare command option. In , you would also have to use the --apply-log-only option.

In and later, perform the following process:

First, prepare the base backup:

Running this command brings the base full backup, that is, /var/mariadb/backup, into sync with the changes contained in the InnoDB redo log collected while the backup was taken.

Then, apply the incremental changes to the base full backup:

Running this command brings the base full backup, that is, /var/mariadb/backup, into sync with the changes contained in the first incremental backup.

For each remaining incremental backup, repeat the last step to bring the base full backup into sync with the changes contained in that incremental backup.

Restoring the Backup

Once you've applied all incremental backups to the base, 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, .
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.

Finally, .

_{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 cannot currently backup a subset of partitions from a partitioned table. Backing up a partitioned table is currently an all-or-nothing selection. See 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:

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

mariadb-backup did not support the --export option. See 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:

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

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

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 MDEV-18777 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:

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

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:

$ mariadb-backup --backup \
   --slave-info --safe-slave-backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariadb-backup --password=mypassword

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

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

Copy the Backup to the New Replica

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

$ rsync -avP /var/mariadb/backup dbserver2:/var/mariadb/backup

Restore the Backup on the New Replica

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

$ mariadb-backup --copy-back \
   --target-dir=/var/mariadb/backup/

And adjusting file permissions, if necessary:

$ chown -R mysql:mysql /var/lib/mysql/

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:

CREATE USER 'repl'@'dbserver2' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'dbserver2';

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 start the MariaDB Server process 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 will be 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 will be 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:

mariadb-bin.000096 568 0-1-2

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:

$ cat xtrabackup_binlog_info
mariadb-bin.000096 568 0-1-2

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

SET GLOBAL gtid_slave_pos = "0-1-2";
CHANGE MASTER TO 
   MASTER_HOST="dbserver1", 
   MASTER_PORT=3306, 
   MASTER_USER="repl",  
   MASTER_PASSWORD="password", 
   MASTER_USE_GTID=slave_pos;
START SLAVE;

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:

CHANGE MASTER TO 
   MASTER_HOST="dbserver1", 
   MASTER_PORT=3306, 
   MASTER_USER="repl",  
   MASTER_PASSWORD="password", 
   MASTER_LOG_FILE='mariadb-bin.000096',
   MASTER_LOG_POS=568;
START SLAVE;

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:

SHOW SLAVE STATUS\G

_{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 MyRocks storage engine. This data is located in the directory defined by the rocksdb_datadir system variable. mariadb-backup backs this data up by performing a checkpoint using the rocksdb_create_checkpoint 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 will be 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 will be 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 will be 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 will be 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

Replication can be used to support the backup 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 will be 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 Replication and Foreign Keys for a situation when identical statements can result in different data on a replica and a primary.

Backup and Restore via dbForge Studio

dbForge Studio is a proprietary third-party tool, not included with MariaDB Server. Content contributed by devart.

In the modern world, data importance is non-negotiable, and keeping data integrity and consistency is the top priority. Data stored in databases is vulnerable to system crashes, hardware problems, security breaches, and other failures causing data loss or corruption. To prevent database damage, it is important to back the data up regularly and implement the data restore policies. MariaDB, one of the most popular database management systems, provides several methods to configure routines for backing up and recovering data. The current guideline illustrates both processes performed with the help of dbForge Studio for MySQL which is also a fully-functional that has everything you need to accomplish the database-related tasks on MariaDB.

Create the backup on MariaDB

dbForge Studio for MySQL and MariaDB has a separate module dedicated to the data backing up and recovering jobs. Let us first look at how set the tool to create a MariaDB backup. Launch the Studio and go to Database > Backup and Restore > Backup Database. The Database Backup Wizard with several pages will appear. On the General page, specify the database in question and how to connect to it, then choose where to save the created backup file, and specify its name. There are additional optional settings – you can select to delete old files automatically, zip the output backup file, etc. When done, click Next.

On the Backup content page, select the objects to back up. Click Next.

The Options page. Here you can specify the details of the data backing up process. Plenty of available options allow you to configure this task precisely to meet the specific requirements. When done, click Next.

The Errors handling page. Here you configure how the Studio should handle the errors that might occur during the backing up process. Also, you can set the Studio to write the information about the errors it encountered into the log file.

You can save the project settings to apply them in the future. For this, in the left bottom corner of the Wizard, select one of the saving options: Save Project or Save Command Line. The latter allows saving settings as a backup script which you can execute from the command line at any time later.

The configuration process is complete. Click Backup to launch the data backing up.

Note: It is not obligatory to go through all the pages of the Wizard. The Backup button is available no matter on which page you are. Thus, you can launch the process of backing the data up whenever you have set everything you needed.

After you have clicked Backup, dbForge Studio for MySQL starts to create a MariaDB backup.

When this is done, you will see the confirmation message. Click Finish.

Backup and restore policies suggest creating regular backups on a daily, weekly, monthly, quarterly, and yearly basis. Besides, to minimize the consequences of possible data loss, it is highly recommended make a backup before making any changes to a database, such as upgrading, modifying data, redesigning the structure, etc. Simply speaking, you always need a fresh backup to restore the most up-to-date database version. To ensure regular backups on schedule, you can use a batch file created with the help of the Studio and Windows Task Scheduler, where you need to create and schedule the backup task.

Restore the backup file on MariaDB

This is an even faster task, done in half as many steps.

The process of data recovery from the backup file is simple. It only takes several clicks: Launch dbForge Studio for MySQL and go to Database > Backup and Restore > Restore Database. The Database Restore Wizard will appear. Specify the database name, its connection parameters, and the path to the backup file you want to restore. Then click Restore, and the process will start immediately.

When the process is complete, click Finish.

More information about this essential feature is available on the – it explores the routines performed on MySQL, but they fully apply to MariaDB backups. You can use the same IDE and the same workflow.

To test-drive this and other features of the Studio (the IDE includes all the tools necessary for the development, management, and administration of databases on MariaDB), . dbForge Studio for MySQL and MariaDB boasts truly advanced functionality that will help your teams deliver more value.

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

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.

Joining Tables with JOIN

In the absence of a more tutorial-level document, here is a simple example of three basic JOIN types, which you can experiment with in order to see what the different joins accomplish:

The first two SELECTs are (unfortunately) commonly written with an older form:

What you can see from this is that an INNER JOIN produces a result set containing only rows that have a match, in both tables (t1 and t2), for the specified join condition(s).

A CROSS JOIN produces a result set in which every row in each table is joined to every row in the other table; this is also called a cartesian product. In MariaDB the CROSS keyword can be omitted, as it does nothing. Any JOIN without an ON clause is a CROSS JOIN.

The LEFT JOIN is an outer join, which produces a result set with all rows from the table on the "left" (t1); the values for the columns in the other table (t2) depend on whether or not a match was found. If no match is found, all columns from that table are set to NULL for that row.

The RIGHT JOIN is similar to the LEFT JOIN, though its resultset contains all rows from the right table, and the left table's columns will be filled with NULLs when needed.

JOINs can be concatenated to read results from three or more tables.

Here is the output of the various SELECT statements listed above:

That should give you a bit more understanding of how JOINS work!

Other References

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 will be 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 will be 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 will be 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:

HASH Partitioning Type

Syntax

Description

HASH partitioning is a form of 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 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

Using the for more information:

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

Description

LINEAR HASH partitioning is a form of , similar to , 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 , however, data is less likely to be evenly distributed over the partitions.

Example

_{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 will be stored in this partition. If we try to insert something that does not belong to any of these value lists, the row will be 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 will be 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 will be 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}

RANGE Partitioning Type

The RANGE partitioning type is used to assign each partition a range of values generated by the partitioning expression. Ranges must be ordered, contiguous and non-overlapping. The minimum value is always included in the first range. The highest value may or may not be included in the last range.

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

Syntax

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

PARTITION BY RANGE indicates that the partitioning type is RANGE.

partition_name is the name of a partition.

value indicates the upper bound for that partition. The values must be ascending. For the first partition, the lower limit is NULL. When trying to insert a row, if its value is higher than the upper limit of the last partition, the row will be rejected (with an error, if the keyword is not used).

As a catchall, MAXVALUE can be specified as a value for the last partition. Note however that in order to append a new partition, it is not possible to use ; rather must be used.

Use Cases

A typical use case is when we want to partition a table whose rows refer to a moment or period in time; for example commercial transactions, blog posts, or events of some kind. We can partition the table by year, to keep all recent data in one partition and distribute historical data in big partitions that are stored on slower disks. Or, if our queries always read rows which refer to the same month or week, we can partition the table by month or year week (in this case, historical data and recent data will be stored together).

values also represent a chronological order. So, these values can be used to store old data in separate partitions. However, partitioning by id is not the best choice if we usually query a table by date.

Examples

In the following example, we will partition a log table by year.

As an alternative, we can partition the table by both year and month:

As you can see, we used the function to accomplish the purpose. Also, the first two partitions cover longer periods of time (probably because the logged activities were less intensive).

In both cases, when our tables become huge and we don't need to store all historical data any more, we can drop the oldest partitions in this way:

We will still be able to drop a partition that does not contain the oldest data, but all rows stored in it will disappear.

Example of an error when inserting outside a defined partition range:

Unless the IGNORE keyword is used:

An alternative definition with MAXVALUE as a catchall:

_{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 DATA_DIRECTORY and INDEX_DIRECTORY table options. This is useful to store different partitions on different devices.

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

The following files exist for each partitioned tables:

File name

Notes

File name

Notes

table_name.frm

Contains the table definition. Non-partitioned tables have this file, too.

table_name.par

Contains the partitions definitions.

table_name#P#partition_name.ext

Normal files created by the storage engine use this pattern for names. The extension depends on the storage engine.

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

orders.frm
orders.par
orders#P#p0.ibd
orders#P#p1.ibd
orders#P#p2.ibd
orders#P#p3.ibd

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

orders.frm
orders.par
orders#P#p0.MYD
orders#P#p0.MYI
orders#P#p1.MYD
orders#P#p1.MYI
orders#P#p2.MYD
orders#P#p2.MYI
orders#P#p3.MYD
orders#P#p3.MYI

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

Partitions Metadata

The PARTITIONS table in the INFORMATION_SCHEMA database contains information about partitions.

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

The SHOW CREATE TABLE statement returns the CREATE TABLE 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

Description

This statement can be used to change the characteristics of a . 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 (since ) or and ( and before).

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

Example

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 Aggregate Functions

are functions that are computed over a sequence of rows and return one result for the sequence of rows.

Creating a custom aggregate function is done using the statement with two main differences:

The addition of the AGGREGATE keyword, so CREATE AGGREGATE FUNCTION
The FETCH GROUP NEXT ROW instruction inside the loop
Oracle PL/SQL compatibility using SQL/PL is provided

Standard Syntax

Stored aggregate functions were a project by Varun Gupta.

Using SQL/PL

Examples

First a simplified example:

A non-trivial example that cannot easily be rewritten using existing functions:

SQL/PL Example

This uses the same marks table as created above.

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 will be 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, will be 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 will be 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 stored routines (stored functions, stored procedures, events or triggers).

ALTER VIEW; you can use CREATE OR REPLACE VIEW instead.
LOAD DATA and LOAD TABLE.
CHANGE MASTER TO
INSERT DELAYED is permitted, but the statement is handled as a regular INSERT.
LOCK TABLES and UNLOCK TABLES.
References to local variables within prepared statements inside a stored routine (use user-defined variables instead).
BEGIN (WORK) is treated as the beginning of a BEGIN END block, not a transaction, so START TRANSACTION needs to be used instead.
The number of permitted recursive calls is limited to max_sp_recursion_depth. 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 Prepare Statement:Permitted statements for a list of statements that can be used. SIGNAL, RESIGNAL and GET DIAGNOSTICS 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.

InnoDB

Discover InnoDB, the default storage engine for MariaDB Server. Learn about its transaction-safe capabilities, foreign key support, and high performance for demanding workloads.

Binary Log Group Commit and InnoDB Flushing Performance

introduced a performance improvement related to group commit that affects the performance of flushing InnoDB transactions when the binary log is enabled.

Overview

In and above, when both innodb_flush_log_at_trx_commit=1 (the default) is set and the binary log is enabled, there is now one less sync to disk inside InnoDB during commit (2 syncs shared between a group of transactions instead of 3).

Durability of commits is not decreased — this is because even if the server crashes before the commit is written to disk by InnoDB, it will be recovered from the binary log at next server startup (and it is guaranteed that sufficient information is synced to disk so that such a recovery is always possible).

Switching to Old Flushing Behavior

The old behavior, with 3 syncs to disk per (group) commit (and consequently lower performance), can be selected with the new innodb_flush_log_at_trx_commit=3 option. There is normally no benefit to doing this, however there are a couple of edge cases to be aware of.

Non-durable Binary Log Settings

If innodb_flush_log_at_trx_commit=1 is set and the binary log is enabled, but sync_binlog=0 is set, then commits are not guaranteed durable inside InnoDB after commit. This is because if sync_binlog=0 is set and if the server crashes, then transactions that were not flushed to the binary log prior to the crash will be missing from the binary log.

In this specific scenario, innodb_flush_log_at_trx_commit=3 can be set to ensure that transactions will be durable in InnoDB, even if they are not necessarily durable from the perspective of the binary log.

One should be aware that if sync_binlog=0 is set, then a crash is nevertheless likely to cause transactions to be missing from the binary log. This will cause the binary log and InnoDB to be inconsistent with each other. This is also likely to cause any replication slaves to become inconsistent, since transactions are replicated through the binary log. Thus it is recommended to set sync_binlog=1. With the group commit improvements introduced in , this setting has much less penalty in recent versions compared to older versions of MariaDB and MySQL.

Recent Transactions Missing from Backups

mariadb-backup and Percona XtraBackup only see transactions that have been flushed to the redo log. With the group commit improvements, there may be a small delay (defined by the binlog_commit_wait_usec system variable) between when a commit happens and when the commit will be included in a backup.

Note that the backup will still be fully consistent with itself and the binary log. This problem is normally not an issue in practice. A backup usually takes a long time to complete (relative to the 1 second or so that binlog_commit_wait_usec is normally set to), and a backup usually includes a lot of transactions that were committed during the backup. With this in mind, it is not generally noticeable if the backup does not include transactions that were committed during the last 1 second or so of the backup process. It is just mentioned here for completeness.

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

InnoDB Asynchronous I/O

From , uses asynchronous I/O to read from and write to disk asynchronously. This forms part of the InnoDB Background Thread Pool.

Stages

Each asynchronous IO operation goes through multiple stages:

SUBMITTED – The IO operation is initiated.

For asynchronous writes, this typically occurs in the buffer pool flushing code.
For asynchronous reads, this may happen during buffer pool loading at startup or in prefetching logic.

COMPLETED_IN_OS – The operating system notifies InnoDB that the I/O operation is complete.

If using libaio or io_uring, a dedicated thread handles this notification.
The completed IO operation is then submitted to InnoDB’s internal thread pool (tpool).

EXECUTING_COMPLETION_TASK – A tpool thread processes the completion task for the IO operation.
COMPLETED – The IO operation is fully handled.

Resource Constraints and Queuing Mechanisms

Waiting for IO Slots

The total number of pending asynchronous IO operations is limited by:

where number_of_IO_threads refers to either or .

Each IO operation is associated with an IO slot, which contains necessary metadata such as the file handle, operation type, offset, length, and any OS error codes. Initially, all total_count slots are free, but as pending IO requests accumulate, slots get occupied. If all slots are in use, additional IO requests must wait for a free slot.

Queuing Mechanism

The number of completion tasks (EXECUTING_COMPLETION_TASK stage) that can run in parallel is also limited by or . If too many IO operations complete simultaneously, they cannot all be processed in parallel and must be queued, respecting the thread limit.

Variables

From , a number of status variables were added to give insight into the above operations:

– Number of read IO operations currently in progress (from SUBMITTED to COMPLETED).
– Number of read IO operations currently in the EXECUTING_COMPLETION_TASK state.
– Total number of read completion tasks that have finished execution.
– Current size of the queue (see ).
– Total wait time for a free IO slot (see Waiting for IO Slots).
– Total number of read operations that were queued (see ). Includes those still waiting and making up innodb_async_reads_queue_size.

Similar variables exist for write operations:

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

InnoDB Doublewrite Buffer

The doublewrite buffer was implemented to recover from half-written pages. This can happen when there's a power failure while InnoDB is writing a page to disk. On reading that page, InnoDB can discover the corruption from the mismatch of the page checksum. However, in order to recover, an intact copy of the page would be needed.

The double write buffer provides such a copy.

Whenever InnoDB flushes a page to disk, it is first written to the double write buffer. Only when the buffer is safely flushed to disk will InnoDB write the page to the final destination. When recovering, InnoDB scans the double write buffer and for each valid page in the buffer checks if the page in the data file is valid too.

Doublewrite Buffer Settings

To turn off the doublewrite buffer, set the system variable to 0. This is safe on filesystems that write pages atomically - that is, a page write fully succeeds or fails. But with other filesystems, it is not recommended for production systems. An alternative option is atomic writes. See for more details.

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

InnoDB Lock Modes

Locks are acquired by a transaction to prevent concurrent transactions from modifying, or even reading, some rows or ranges of rows. This is done to make sure that concurrent write operations never collide.

supports a number of lock modes.

Shared and Exclusive Locks

The two standard row-level locks are share locks(S) and exclusive locks(X).

A shared lock is obtained to read a row, and allows other transactions to read the locked row, but not to write to the locked row. Other transactions may also acquire their own shared locks.

An exclusive lock is obtained to write to a row, and stops other transactions from locking the same row. It's specific behavior depends on the ; the default (REPEATABLE READ), allows other transactions to read from the exclusively locked row.

Intention Locks

InnoDB also permits table locking, and to allow locking at both table and row level to co-exist gracefully, a series of locks called intention locks exist.

An intention shared lock(IS) indicates that a transaction intends to set a shared lock.

An intention exclusive lock(IX) indicates that a transaction intends to set an exclusive lock.

Whether a lock is granted or not can be summarised as follows:

An X lock is not granted if any other lock (X, S, IX, IS) is held.
An S lock is not granted if an X or IX lock is held. It is granted if an S or IS lock is held.
An IX lock is not granted if in X or S lock is held. It is granted if an IX or IS lock is held.
An IS lock is not granted if an X lock is held. It is granted if an S, IX or IS lock is held.

AUTO_INCREMENT Locks

Locks are also required for auto-increments - see .

Gap Locks

With the default , REPEATABLE READ, and, until , the default setting of the variable, a method called gap locking is used. When InnoDB sets a shared or exclusive lock on a record, it's actually on the index record. Records will have an internal InnoDB index even if they don't have a unique index defined. At the same time, a lock is held on the gap before the index record, so that another transaction cannot insert a new index record in the gap between the record and the preceding record.

The gap can be a single index value, multiple index values, or not exist at all depending on the contents of the index.

If a statement uses all the columns of a unique index to search for unique row, gap locking is not used.

Similar to the shared and exclusive intention locks described above, there can be a number of types of gap locks. These include the shared gap lock, exclusive gap lock, intention shared gap lock and intention exclusive gap lock.

Gap locks are disabled if the system variable is set (until ), or the is set to READ COMMITTED.

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

InnoDB Monitors

The InnoDB Monitor refers to particular kinds of monitors included in MariaDB and since the early versions of MySQL.

There are four types: the standard InnoDB monitor, the InnoDB Lock Monitor, InnoDB Tablespace Monitor and the InnoDB Table Monitor.

Standard InnoDB Monitor

The standard InnoDB Monitor returns extensive InnoDB information, particularly lock, semaphore, I/O and buffer activity:

To enable the standard InnoDB Monitor, from , set the innodb_status_output system variable to 1. Before , running the following statement was the method used:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

To disable the standard InnoDB monitor, either set the system variable to zero, or, before , drop the table

DROP TABLE innodb_monitor;

The CREATE TABLE and DROP TABLE method of enabling and disabling the InnoDB Monitor has been deprecated, and may be removed in a future version of MariaDB.

For a description of the output, see SHOW ENGINE INNODB STATUS.

InnoDB Lock Monitor

The InnoDB Lock Monitor displays additional lock information.

To enable the InnoDB Lock Monitor, the standard InnoDB monitor must be enabled. Then, from , set the innodb_status_output_locks system variable to 1. Before , running the following statement was the method used:

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

To disable the standard InnoDB monitor, either set the system variable to zero, or, before , drop the table

DROP TABLE innodb_lock_monitor;

The CREATE TABLE and DROP TABLE method of enabling and disabling the InnoDB Lock Monitor has been deprecated, and may be removed in a future version of MariaDB.

InnoDB Tablespace Monitor

The InnoDB Tablespace Monitor is deprecated, and may be removed in a future version of MariaDB.

Enabling the Tablespace Monitor outputs a list of file segments in the shared tablespace to the error log, and validates the tablespace allocation data structures.

To enable the Tablespace Monitor, run the following statement:

CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;

To disable it, drop the table:

DROP TABLE innodb_tablespace_monitor;

InnoDB Table Monitor

The InnoDB Table Monitor is deprecated, and may be removed in a future version of MariaDB.

Enabling the Table Monitor outputs the contents of the InnoDB internal data dictionary to the error log every fifteen seconds.

To enable the Table Monitor, run the following statement:

CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

To disable it, drop the table:

DROP TABLE innodb_table_monitor;

SHOW ENGINE INNODB STATUS

The SHOW ENGINE INNODB STATUS statement can be used to obtain the standard InnoDB Monitor output when required, rather than sending it to the error log. It will also display the InnoDB Lock Monitor information if the innodb_status_output_locks system variable is set to 1.

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

InnoDB Architecture for MariaDB Enterprise Server

Understand InnoDB's architecture for MariaDB Enterprise Server. This section details its components and their interactions, focusing on performance, scalability, and reliability for enterprise workloa

Server Usage

Basics

A MariaDB Primer

Logging into MariaDB

The Basics of a Database

Inserting Data

Modifying Data

See Also

Basic Queries

Connecting to MariaDB

Creating a Structure

Minor Items

Entering Data

Retrieving Data

Changing & Deleting Data

Conclusion

Basic SQL Debugging

Designing Queries

Using Whitespace

Table and Field Aliases

Placing JOIN conditions

Finding Syntax Errors

Interpreting the Empty Error

Checking for keywords

Version specific syntax

Basic SQL Statements

Defining How Your Data Is Stored

Manipulating Your Data

Transactions

A Simple Example

Commonly Used Queries

Creating a Table

Inserting Records

Using AUTO_INCREMENT

Querying from two tables on a common value

Finding the Maximum Value

Finding the Minimum Value

Finding the Average Value

Finding the Maximum Value and Grouping the Results

Ordering Results

Finding the Row with the Minimum of a Particular Column

Finding Rows with the Maximum Value of a Column by Group

Calculating Age

Using User-defined Variables

View Tables in Order of Size

Removing Duplicates

Connecting

Data Handling

Backup & Restore

Backup and Restore with MariaDB Enterprise Server

Backup Optimization

Overview

Scheduling of Restore Preparation

Moving Restored Data

Multi-threading

Incrementing an Incremental Backup

Storage Snapshots

Snapshotting with MariaDB Enterprise Server

Snapshotting with MariaDB Community Server

Forming a Backup Strategy

Overview

Data Inventory

Recovery Objectives

Achieving RPO

Achieving RTO

Replication Considerations

Encryption Considerations

Backup Storage Considerations

Backup Testing

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

Incremental Backup and Restore with mariadb-backup

Backing up the Database Server

Backing up the Incremental Changes

Combining with --stream output

Combining with `--stream` output