Introduction

When designing database-based applications, one aspect that might always be considered at first is how you are supposed to maintain these applications; here, we mean maintenance in the sense of application code and database schemas being upgraded and how this can be done with minimum downtime and effort.

This document aims to look at some important aspects of this, from database schema design to application code. Most of these are not strict rules, but rather aspects to consider when working with applications. The document does not cover general application code aspects, only the aspects that deal with the database part of applications.

Background to Relational Database Applications

Relational database systems, more or less all of them using the SQL query language, have been around since the early 1980s, and things have changed a lot over that time. One aspect that applications using relational databases introduced was the separation of the application, dealing with logic, presentation, user interaction and similar aspects on one hand and the database structure and design on the other.

With relational databases came the relational database modelling and eventually the different normal forms of database design. Much of this has relaxed these days, but there are still things to consider here.

The introduction of logic in the database layer, such as stored procedures, triggers and other aspects, is somewhat blurring the line between code and data, but the general rules still hold.

This guide provides step-by-step instructions for installing MariaDB Server on various operating systems, including package updates and security settings.

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

Steps:

1. Update Package List:

   Before installing, it's a good practice to update your package index.

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

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

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

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

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

For example, an index on Last_Name:

and an index on Position

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

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

See and for more information.

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

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

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

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

For canary testing new versions of an application, there are some tools to work with, but it has to be said that this is hardly ever 100% clear. If we assume that the advice above has been followed to some extent, then the following are some tools to work with.

In a MariaDB instance, there is the concept of a database, which is similar to a schema. A database is a kind of namespace, which means that an object, say a table, in one database may have the same name as an object in some other database in the same instance. Given this, databases are a key to allowing somewhat different schemas to coexist, which in turn means that it is good practice not to hard-code the database name in applications or schema objects, unless this makes sense.

Views are an excellent way of hiding complexities, and a good way of dealing with this is to have a separate database for a new version with VIEWs referencing the actual data in some other database.

When adding a new version of the schema/application, it is sometimes necessary to migrate the data to the new schema and have a VIEW in the "old" schema referencing the new one. A view is not always necessary in this case, but any added columns have to be handled by a trigger or by a sensible default.

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

• — an alternative PITR procedure that replays archived InnoDB write-ahead logs instead of binary logs. Available from MariaDB 13.0.

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

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

For example, you might execute the following command:

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

Note

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

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.

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.

To restore individual partitions or partitioned tables from a backup, find the .ibd and .cfg files for the partitions in the backup, and then import them using the process.

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

Files Included in Backup

mariadb-backup backs up the files listed below.

InnoDB Data Files

mariadb-backup backs up the following InnoDB data files:

• InnoDB system tablespace

• InnoDB file-per-table tablespaces

MyRocks Data Files

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.

• InnoDB Temporary Tablespaces

• Binary logs

• Relay logs

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

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":

mariadb-backup --user=root --backup --stream=xbstream  | openssl  enc -aes-256-cbc -k mypass > backup.xb.enc

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

openssl  enc -d -aes-256-cbc -k mypass -in backup.xb.enc | mbstream -x

Compressing and Decompressing Backup With gzip

This example compresses the backup without encrypting:

mariadb-backup --user=root --backup --stream=xbstream | gzip > backupstream.gz

We can decompress and unpack the backup as follows:

gunzip -c backupstream.gz | mbstream -x

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):

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.

Compress

Decompress, unpack

Encryption

Decrypt, unpack

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.

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}

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.

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

EXPLAIN PARTITIONS SELECT * FROM orders WHERE id < 15000000;
+------+-------------+--------+------------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+--------+------------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | orders | p0,p1      | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+------+-------------+--------+------------+-------+---------------+---------+---------+------+------+-------------+

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. We can force MariaDB to only access the specified partitions by adding a PARTITION clause. This feature is called partition selection. For example:

SELECT * FROM orders PARTITION (p3) WHERE user_id = 50;
SELECT * FROM orders PARTITION (p2,p3) WHERE user_id >= 40;

The PARTITION clause is supported for all DML statements:

• SELECT

• INSERT

• UPDATE

• DELETE

Partition Pruning and Triggers

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

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

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

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

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

Partitioning Types

MariaDB supports the following partitioning types:

• RANGE

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

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.

Syntax

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

Description

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

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

Example

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

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

Syntax

PARTITION BY LINEAR KEY [ALGORITHM={MYSQL51|MYSQL55|BASE31|CRC32C|XXH32|XXH3}]
([column_names])
[PARTITIONS (number_of_partitions)]

PARTITION BY LINEAR 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 ; however, data is less likely to be evenly distributed over the partitions.

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

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 as follows:

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

PARTITION BY LIST indicates that the partitioning type is LIST.

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

partition_name is the name of a partition.

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

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

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.

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

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

The following limitations apply to partitioning in MariaDB:

• Each table can contain a maximum of 8192 partitions.

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

• A partitioned table cannot contain, or be referenced by, .

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

• contains information about existing partitions.

• for suggestions on using partitions

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

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 are stored in the system tablespace.

The following files exist for each partitioned tables:

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

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

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

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}

Syntax

ALTER PROCEDURE proc_name [characteristic ...]

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

Description

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

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

Example

ALTER PROCEDURE simpleproc SQL SECURITY INVOKER;

See Also

• Stored Procedure Overview

_{This page is licensed: GPLv2, originally from}

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

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 .

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

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 .

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

The following restrictions apply to stored functions.

• All of the restrictions listed in Stored Routine Limitations.

• Any statements that return a result set are not permitted. For example, a regular SELECTs is not permitted, but a SELECT INTO is. A cursor and FETCH 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}

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

• ; you can use instead.

• and .

The includes a feature to group commits to speed up concurrent threads doing many inserts into the same or different Aria tables.

By default, group commit for Aria is turned off. It is controlled by the and system variables.

Information on setting server variables can be found on the page.

• A commit is flush of logs followed by a sync.

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

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

Calculating Time Across Midnight

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

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

• TIME_TO_SEC(time) converts a time value to seconds.

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

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

Modulo Arithmetic for Time Rollover:

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

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

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

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

1. Adding new DATETIME columns.

2. Populating them by combining the old date and time columns (e.g., using CONCAT(ticket_date, ' ', entered_time)).

3. Dropping the old separate date and time columns. (Always back up your data before such structural changes.)

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

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

• date: A DATE, DATETIME, or TIME value.

• expr: The value of the interval to add.

Adding Hours (handles date change):

If entered and promised are DATETIME columns:

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

Adding Combined Hours and Minutes:

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

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

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

Adding Days:

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

Adding Combined Days and Hours:

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

Adding Combined Years and Months:

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

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

Using DATE_ADD with a Negative Interval:

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

Using DATE_SUB(date, INTERVAL expr unit):

This function is specifically for subtracting durations.

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

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

About mariadb-dump

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

Key Advantages:

• No Server Shutdown: Backups can be performed while the MariaDB server is running.

• SQL Output: It generates a .sql file (a "dump file") containing SQL statements (CREATE TABLE, INSERT, etc.) necessary to reconstruct the databases and data.

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

To export all databases managed by your MariaDB server:

• --user=admin_backup: Specifies the MariaDB user performing the backup (this user needs appropriate privileges, typically at least SELECT and LOCK TABLES).

• --password: Prompts for the user's password. For use in scripts where prompting is not possible, you can use --password=yourpassword (note the absence of a space and the security implication of having the password in a script or command history).

Commonly Used Option for Efficiency:

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

Example with long options and password in script:

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

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

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

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

• First, specify the database name (your_database_name).

• Then, list one or more table names (table_name1, table_name2) separated by spaces.

• Note that the --databases

• User Privileges: The MariaDB user specified with --user needs at least SELECT privileges for the tables being dumped. LOCK TABLES privilege is needed if using --lock-tables. RELOAD or FLUSH_TABLES might be needed for options like --flush-logs or --master-data. For --single-transaction

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

By Anders Karlsson, Principal Sales Engineer at MariaDB Plc — 24 minutes read

This document offers guidance on creating and maintaining database applications with minimal downtime and effort, covering aspects from database schema design to application code.

Here's a summary of key areas and advice covered; find the full guide on the subsequent pages.

• Database Design: A well-designed database is crucial. Standardization in naming conventions (e.g., orders_t), data types, character sets (preferably UTF-8 for full Unicode support, utf8mb4), and collations is highly recommended to ensure ease of maintenance.

• Data Types:

  • Choosing appropriate types: Consider if a number will be computed; if not, a string might be better (e.g., VARCHAR for product codes with leading zeros).

  • Text/String: Use VARCHAR and be generous with sizing, as schema upgrades for length extensions are undesirable. UTF-8 (specifically utf8mb4

• Schema Objects:

  • Views: Excellent for hiding complexity and supporting different schema versions. Naming views with version strings (orders_v_1) can be beneficial. They can also be used to reference data in newer schemas during migration.

• Application Code:

  • Separation of Concerns: While complete separation of application logic and database logic is difficult, practices like using ORMs and stored procedures can help.

  • Object Relational Mappers (ORM): Tools like Hibernate allow applications to be less reliant on specific database schema details, aiding maintenance, though performance and complex operations might still require attention.

• Code and Schema Standardization: Adhering to internal standards for data types, column names, and database interaction improves maintainability and code readability.

• Complex SQL: Break down very complex SQL (especially SELECT JOINs) into multiple statements or use temporary tables to improve readability and maintainability.

• Canary Testing:

  • Database Naming: Utilize the MariaDB database concept (similar to schema) as a namespace to allow different schemas to coexist, avoiding hard-coding database names.

  • Views: Create separate databases for new versions with views referencing data in older or newer schemas to manage transitions.

Overview

The design of the database is a key here; a well-designed database with appropriate relationships, naming, etc., is still a solid foundation to build a structure on, but as things evolve, it will change with the application. The task is to implement necessary changes, but also to consider future enhancements in the design of the database.

Standardization

To make a database schema easy to maintain, it is best to adhere to some kind of naming standard. What this standard is has less importance than ensuring that it is adhered to. Some like to prefix a name with the type indicator, some like to suffix it, and others ignore this; whichever is your preference, stick to it.

CREATE TABLE `orders_t`(`order_id` BIGINT NOT NULL PRIMARY KEY,
   `order_date` DATETIME NOT NULL,
   `customer_id` BIGINT NOT NULL,
   FOREIGN KEY(`customer_id`) REFERENCES `customer_t`(`customer_id`));

In addition, sticking to a standard for data types to use, including character sets and collations, is a good practice.

Database Data Types

The data types used really need careful consideration. There are performance aspects here, but in addition, the ease of upgrading the schema should also be considered. Any data type has limitations in and of themselves, and in many cases, limits are introduced as part of a column definition, such as the maximum length of string types and the precision of numeric datatypes.

Choosing an Appropriate Data Type

In some cases, a suitable data type is obvious; in other cases, this is not the case. Take, for example, a product code consisting of 8 digits; is it best stored as an INTEGER or as a VARCHAR(8)? The former will be more compact in storage and will be faster, the latter less so. Also, are leading zeros significant? If this is the case, then an INTEGER is likely not a good option. Actually, in general, if you are not computing a number, it is likely better to be stored as a string.

The most common text data types are variable length, so in general, there is no need to be conservative when sizing a VARCHAR, for example. In MariaDB, there is a limit on the total row size of a table, and in this calculation, the maximum size of VARCHAR is used, so this might be a reason not to extend this too much. In some cases, you know the maximum length of a VARCHAR column, and in that case, it should be used, of course, but be careful, as things might change over time, say some code of some kind might be extended in the future.

In other situations, you cannot really tell what the maximum might be, say a name, then make sure that there is ample space available, having to upgrade a schema just because you have to extend the size of a VARCHAR column is unnecessary.

One issue that needs to be considered is what character set to use for text strings. In most cases, it is recommended that UTF-8 be used, but note that MariaDB has a few options here; either you can use 3-byte UTF-8, called utf8mb3, which allows for 2-byte Unicode or 4-byte, called utf8mb4, which allows full Unicode support. When you use UTF-8, though, remember that storage of strings might be longer. MariaDB ensures that space is allocated as appropriate, but the max potential length of a VARCHAR(8) string using utf8mb3 will be 24 bytes, and when it comes to calculating the maximum row size, this is calculated as 24, not 8, bytes.

Collations determine how a string is sorted, for example, when an ORDER BY is used and when indexes are created. You really do want to avoid upgrading a schema to change the collation of a column in a table, so be careful here.

It is recommended that you allow as much space as possible when you don’t know the maximum length of a column in a table. It is recommended to use UTF-8 for string data; it does have some drawbacks, but in the end, it is the best general-use character set. It might be useful to use a single-byte character set in some cases, say when using some alphanumeric code item such as a product code, but mostly you are best off with UTF-8 even here, as mixing different data types makes things unnecessarily complicated.

Similar things can be said for collations; there are few reasons to deviate from using just a single collation for a specific application database from the point of view of creating a schema that can be easily maintained at least. From a performance point of view, there are things to consider when determining which collation to use.

Numeric data types come in several flavors, from a high level we are looking at integer types, floating point types and fixed-point types. In MariaDB, all numeric types are fixed-size storage. From the point of view of schema maintenance, the first thing to look for when it comes to creating a database schema that will need less maintenance is to ensure that values fit in the types used. BIGINT instead of INTEGER is often a good idea, in particular when used for auto-generated primary keys.

Another thing to watch out for is FLOAT and DOUBLE, including aliases for these, as they are floating-point, which means there might be rounding issues. Using these types for monetary values might not always be a good idea.

An alternative to FLOAT and DOUBLE is to use the fixed-point DECIMAL type, which is exact. Note also that in SQL_MODE=Oracle in MariaDB, the Oracle type NUMERIC is an alias for DECIMAL.

MariaDB has a range of temporal types, from the standard DATETIME and TIMESTAMP to the more specialised DATE, YEAR and TIME. The by far most used ones are DATETIME and TIMESTAMP, and there are some things to consider when it comes to maintenance. DATETIME and TIMESTAMP both store similar values, but there is a difference in that DATETIME stores the time as it is, whereas TIMESTAMP takes the time zone on the client side into effect.

From a schema maintenance point of view, make sure that you understand how these two types work before using them.

Most database systems on the market have types that are specific to that system in particular, in MariaDB, which includes ENUM and SET types. Some types are little used, like BIT and smaller variations of INTEGER types. As for ENUM and SET, these have some useful attributes in that they, on the one hand, require limited storage, but to extend them with additional values, the schema has to be altered, which might be an issue in many cases.

Beyond this, there are other objects to handle when it comes to schema and application maintenance. Here, we will look at some of them from the point of view of creating a schema that allows itself to be maintained with limited effort.

Using views has several advantages, and although using views might have performance issues, they provide many advantages when it comes to maintaining a database schema. When considering that, this assumes that the views are created with performance and maintenance in mind.

Very complex queries that might need to be maintained can well be put in views. It may also be advantageous to use views to support different versions of a schema over time; adding a version string to VIEW names might well be a good idea.

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:

1. What data is housed in the databases?

2. What business purpose does this data serve?

3. How long does the data needed to be retained in order to meet this business purpose?

4. Are there any legal or regulatory requirements, which would limit the length of data retention?

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 .

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.

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.

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.

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.

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:

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.

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.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

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:

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:

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.

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.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

When InnoDB log archiving is enabled, the server retains a continuous history of write-ahead log records across multiple ib_lsn.log files. You can use this history to perform point-in-time recovery (PITR) to a specific Log Sequence Number (LSN), by replaying the archived InnoDB write-ahead logs at server startup.

This is an alternative to PITR via mariadb-backup and binary logs, useful in InnoDB-only deployments or in recovery scenarios where binary logs are not available.

Recovery Parameters

Two startup parameters define the range of the log replay:

• — the LSN at which recovery begins. Set this to the end LSN of the previous restore when applying an incremental restore. The default 0 starts from the latest completed checkpoint, which is guaranteed to live in one of the last two ib_lsn.log files.

• — the LSN at which recovery ends (the recovery point objective). When this is non-zero, persistent InnoDB tables become read-only and no log writes are allowed during the recovery session. The default 0 replays the log to its end.

You can identify candidate LSNs from the status variable on the source server, and from the file names in the data directory (each ib_lsn.log file name encodes the LSN at file offset 0x3000).

The data files in the restore must correspond to an LSN that lies between Innodb_lsn_archived (the first checkpoint in the first archived log file) and the end LSN of the last archived log file. To extend the available recovery range, copy additional archived log files in from the source server — no further copying of data files is required.

• — feature overview, file format, monitoring, and managing archived log files.

• , , .

• — the binary-log-based PITR procedure.

Replication can be used to support the backup strategy.

Replication is most commonly used to support backups as follows:

• A primary server replicates to a replica

• Backups are then run off the replica without any impact on the primary.

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

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

To set up a replica specifically for backup purposes, you will need to configure outbound replication from your primary server to the replica. Here is a step-by-step guide to establishing this connection securely:

On the primary server, create a dedicated user for replication and grant it the necessary privileges:

(Optional: Confirm the grants by executing SHOW GRANTS FOR 'backup_replica'@'replica_ip_or_hostname';)

On the primary server, obtain the current GTID position from which the replica should start replicating. If you want to start from the most recent transaction, query the gtid_current_pos:

On the replica server, configure the starting GTID position using the value obtained in the previous step:

SQL

On the replica server, configure the connection to the primary server using the CHANGE MASTER TO statement:

(Note: Adjust the MASTER_PORT, MASTER_SSL, and add MASTER_SSL_CA parameters as necessary depending on your network and security configuration).

On the replica server, start the replication process:

On the replica server, verify that replication is running smoothly:

Ensure that both Slave_IO_Running and Slave_SQL_Running are Yes. Once the replica is fully synced, you can safely pause it or run your backup tools (mariadb-backup or mariadb-dump) directly against this replica without affecting the primary server's performance.

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

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 GUI client for MariaDB 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.

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}

Syntax

PARTITION BY KEY
[ALGORITHM={MYSQL51|MYSQL55|BASE31|CRC32C|XXH32|XXH3}]
([column_names])
[PARTITIONS (number_of_partitions)]

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

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

Compared to HASH partitioning, KEY partitioning distributes data using a preset hash algorithms on the specified columns, rather than an expression specified by the user.

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 an error:

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.

The unique key must be NOT NULL:

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

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

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:

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

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 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 variable is set to 0 (it defaults to 1).

• The SUPER privilege is also required if statement-based binary logging is used. See for more details.

• To run a stored routine, the privilege is needed. This is also temporarily granted to the creator if they attempt to run their routine unless the variable is set to 0.

• The (by default DEFINER) specifies what privileges are used when a routine is called. If SQL SECURITY is INVOKER, the function body are evaluated using the privileges of the user calling the function. If SQL SECURITY is

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.

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

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

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

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

• - maria.com post on what to do after you've dropped a user, and now want to change the DEFINER on all database objects that currently have it set to this dropped user.

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

Binary logging can be row-based, statement-based, or a mix of the two. See Binary Log Formats for more details on the formats. If logging is statement-based, it is possible that a statement will have different effects on the master and on the slave.

Stored routines are particularly prone to this, for two main reasons:

• stored routines can be non-deterministic, in other words non-repeatable, and therefore have different results each time they are run.

• the slave thread executing the stored routine on the slave holds full privileges, while this may not be the case when the routine was run on the master.

The problems with replication will only occur with statement-based logging. If row-based logging is used, since changes are made to rows based on the master's rows, there is no possibility of the slave and master getting out of sync.

By default, with row-based replication, triggers run on the master, and the effects of their executions are replicated to the slaves. However, it is possible to run triggers on the slaves. See .

If the following criteria are met, then there are some limitations on whether stored routines can be created:

• The is enabled, and the system variable is set to STATEMENT. See for more information.

• The is set to OFF, which is the default value.

If the above criteria are met, then the following limitations apply:

• When a is created, it must be declared as either DETERMINISTIC, NO SQL or READS SQL DATA, or else an error will occur. MariaDB cannot check whether a function is deterministic, and relies on the correct definition being used.

• To create or modify a stored function, a user requires the SUPER privilege as well as the regular privileges. See for these details.

A deterministic function:

A non-deterministic function, since it uses the function:

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

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

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

These statements are part of the SQL Data Definition Language - DDL.

System variables and status variables serve two distinct roles in MariaDB: System variables are the "knobs" you turn to change how the server behaves, while status variables are the "gauges" you watch to see how the server is performing.

Because MariaDB is modular, these variables are often grouped by their specific functional area or plugin.

System variables are configuration settings. You use them to define the environment, set resource limits, and tune performance.

• Configuration & Tuning: You modify these to optimize the server for your workload. For example, you might increase innodb_buffer_pool_size

Although relational database applications strive to separate application code from database data, this is only possible to a limited extent. That said, there are means to work with database and application design that make maintaining database schema and applications easier. The task at hand then, on the application side of things, is to make the application easy to maintain and as flexible as possible when it comes to integrating with the database on one hand, and on the other hand ensure that the measures taken don’t take a toll on features, functionality or performance.

Using an ORM, such as Hibernate for Java applications, makes it possible to build applications that do not rely on the lowest detail of the database schema. On the other hand, performance is sometimes an issue and, in some cases, complex relational operations might still need to be hard-coded. Despite this, hibernate is often a good way to create applications and database schemas that are easy to maintain.

Using database stored procedures is another way of isolating database logic from application logic. One advantage here is that if applications call stored procedures instead of issuing SQL statements, then we can keep a clear differentiator between the database and backend processing and application-level processing. A disadvantage is that the connection between the backend logic and the structure of the data is still there, it is just somewhere else than in the application, but this still makes maintenance easier in many ways.

It is also common not to drive this too far, i.e. complex SQL and code that truly belongs in the backend can be in the database, whereas more basic SQL and simple SELECTs can be kept as they are in the application.

You can perform several maintenance tasks on partitioned tables using standard SQL statements or specific ALTER TABLE extensions.

For general maintenance, MariaDB supports the following statements on partitioned tables just as it does for non-partitioned tables:

• CHECK TABLE

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:

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:

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.

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

To take a backup, run the following command:

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

A Stored Procedure is a routine invoked with a statement. It may have input parameters, output parameters and parameters that are both input parameters and output parameters.

Here's a skeleton example to see a stored procedure in action:

First, the delimiter is changed, since the function definition will contain the regular semicolon delimiter. The procedure is named Reset_animal_count. MODIFIES SQL DATA indicates that the procedure will perform a write action of sorts, and modify data. It's for advisory purposes only. Finally, there's the actual SQL statement - an UPDATE.

A more complex example, with input parameters, from an actual procedure used by banks:

See

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

A Stored Function is a defined function that is called from within an SQL statement like a regular function and returns a single value.

Here's a skeleton example to see a stored function in action:

First, the delimiter is changed, since the function definition will contain the regular semicolon delimiter. See for more. Then the function is named FortyTwo and defined to return a tinyin. The DETERMINISTIC keyword is not necessary in all cases (although if binary logging is on, leaving it out will throw an error), and is to help the query optimizer choose a query plan. A deterministic function is one that, given the same arguments, will always return the same result.

Next, the function body is placed between statements. It declares a tinyint,

The ARCHIVE storage engine is a storage engine that uses gzip to compress rows. It is mainly used for storing large amounts of data, without indexes, with only a very small footprint.

A table using the ARCHIVE storage engine is stored in two files on disk. There's a table definition file with an extension of .frm, and a data file with the extension .ARZ. At times during optimization, a .ARN file will appear.

New rows are inserted into a compression buffer and are flushed to disk when needed. SELECTs cause a flush. Sometimes, rows created by multi-row inserts are not visible until the statement is complete.

ARCHIVE allows a maximum of one key. The key must be on an column, and can be a PRIMARY KEY

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

• Connect to mysqld instance, find out important variables (datadir, InnoDB pagesize, encryption keys, encryption plugin etc)

• Scan the database directory, datadir