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

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

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

Steps:

1. Update Package List:

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

   • For Debian/Ubuntu:Bash

   • For Red Hat/CentOS/Fedora:Bash

2. Install MariaDB Server:

   Install the MariaDB server and client packages.

   • For Debian/Ubuntu:Bash

   • For Red Hat/CentOS/Fedora:Bash

3. Secure the Installation:

   After installation, run the security script to set a root password, remove anonymous users, and disable remote root login.

   Follow the prompts to configure your security settings.

4. Start and Verify the Service:

   MariaDB typically starts automatically after installation. You can check its status and manually start it if needed.

   • Check status:

   • Start service (if not running):Bash

   • Verify installation by connecting as root:Bash

For Windows

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

Steps:

1. Download MariaDB:

   Visit the MariaDB downloads page to get the latest .msi installer.

2. Run the Installer:

   Double-click the downloaded .msi file to start the installation wizard.

3. Follow On-Screen Instructions:

Important Notes:

• Firewall: Ensure your firewall is configured to allow connections to MariaDB on the appropriate port (default 3306) if you need remote access.

• Root Password: Always set a strong root password during the secure installation step.

• Further Configuration: For production environments, you may need to adjust further settings in the MariaDB configuration files (e.g., my.cnf on Linux).

Additional Resources:

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.

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) is generally preferred for character sets, and consistency in collations simplifies maintenance.

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

  • Stored Procedures and Functions: Isolate database logic from application logic, making maintenance easier by centralizing complex SQL operations in the database layer.

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

  • Replication: Use MariaDB replication, particularly statement-based replication (SBR), for canary testing by replicating from the production system to a new server with the updated schema.

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.

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

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 partitions in the backup, and then import them using the process.

_{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:

The PARTITION clause is supported for all DML statements:

Partition Pruning and Triggers

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

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

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

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}

Syntax

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

Description

LINEAR KEY partitioning is a form of , similar to .

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.

Example

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

Syntax

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

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}

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}

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.

can be used to support the strategy.

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

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

Basic Restoration Process

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

• Replace your_username with your MariaDB username and /path/to/your/backupfile.sql with the actual path to your dump file.

• You will be prompted for the password for your_username.

• The < symbol is a standard input (STDIN) redirect, feeding the contents of backupfile.sql

Important Considerations Before Restoring

• Data Overwriting: Restoring a dump file will execute the SQL statements within it. If the dump file contains DROP TABLE and CREATE TABLE statements (common for full backups), existing tables with the same names will be dropped and recreated, leading to loss of any data added or changed since the backup was made.

• Backup Age: If your dump file is several days old, restoring it entirely could revert all data in the affected tables/databases to that older state. This can be disastrous if only a small portion of data was lost and the rest has been actively updated.

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

Restoring a Single Table Selectively

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

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

2. Grant Limited Privileges:

   • Grant this temporary user the minimal privileges needed for the dump file to execute up to the point of restoring your target table. This might be SELECT on all tables in the database if the dump file checks other tables, or simply the ability to USE the database.

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

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

Other Data Files

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

• frm

• isl

• MYD

• MYI

Files Excluded From Backup

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

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

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}

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 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 indicates that the partitioning type is LIST.

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

partition_name is the name of a partition.

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

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

Use Cases

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

Example

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

Syntax

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

Examples

IF EXISTS:

See Also

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

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

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

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

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

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

For example, an index on Last_Name:

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

and an index on Position

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}

RANGE COLUMNS and LIST COLUMNS are variants of, respectively, RANGE and LIST. 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 integer, string, DATE, and DATETIME 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 as follows:

The syntax for LIST COLUMNS is as follows:

partition_name is the name of a partition.

Comparisons

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

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

With RANGE COLUMNS, a row matches a partition if it is less than the specified value tuple in lexicographic order. The first partition that matches the row values are used.

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

Examples

RANGE COLUMNS partition:

LIST COLUMNS partition:

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

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, perform the following calculation:

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

See Also

• for suggestions on using 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:

See Also

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

Syntax

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 .

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

Example

See Also

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

Syntax

Description

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

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 , considering the , and considering . Also critical is a and .

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.

Database Naming

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.

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.

Syntax

Description

The DROP FUNCTION

This guide offers conventions and practical tips for designing SQL queries that are easier to read, understand, and debug. Learn about effectively using whitespace, choosing meaningful aliases, correctly placing JOIN conditions, and strategies for identifying and resolving common syntax errors.

Following a few conventions makes finding errors in queries a lot easier, especially when asking 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.

Using Whitespace

A query that is hard to read is hard to debug. Whitespace is free; use new lines and indentation to make queries easy to read, particularly when constructing a query inside a scripting language where variables might be interspersed.

Example: Hard-to-read query with a syntax error

Can you find the error quickly in this?

Same query with better whitespace:

Code snippet

The missing closing parenthesis ) after team.teamId in the second JOIN condition is much easier to spot with clear formatting. The exact style (commas before or after selected items, tabs vs. spaces) is less important than overall legibility.

Table and Field Aliases

Aliases rename tables and fields within a query, useful for long names or when required (e.g., self-joins, some subqueries). Poorly chosen aliases, however, can hinder debugging. Aliases should ideally reflect the original table name.

Bad Example (arbitrary aliases):

Code snippet

As the query grows, it's hard to remember what a, b, or c refer to without looking back.

Better Example (meaningful aliases):

To correct the SQL code, it should be properly formatted and structured:

This query selects all data from financial_report_Q_1 and joins sales_renderings and sales_agents using specified conditions. It filters for total sales greater than 10,000 and excludes records where the sales agent's ID matches

It's unclear how family and relationships are linked without parsing the entire WHERE clause.

Better Example (clear separation):

The table relationship is obvious in the JOIN ... ON clause. The WHERE clause is reserved for filtering the result set. Always use explicit JOIN keywords (INNER JOIN, LEFT JOIN, etc.) instead of commas for joining tables, and do not mix these styles.

Finding Syntax Errors

MariaDB's error messages usually point to where the parser got confused. Check the query around the indicated point.

Interpreting the "Empty Error"

An error like ERROR 1064: ... syntax to use near '' at line 1 can be tricky. The empty ' ' often means the parser reached the end of the statement while expecting more tokens.

• Check for missing closing characters like quotes ' or parentheses ).SQL

• Look for incomplete clauses, often indicated by a trailing comma.SQL

Checking for Reserved Keywords

If an identifier (table name, column name, alias) is a MariaDB reserved word, it must be enclosed in backticks (`) to avoid ambiguity.

A text editor with SQL syntax highlighting can help spot these. Common identifiers that are also keywords include:

• DESC (often for "description", but means "descending" in ORDER BY)

• DATE, TIME, TIMESTAMP (data types)

• ORDER

It's a good practice to quote any identifier that is also a keyword, even if MariaDB might allow some unquoted in certain contexts.

Version-Specific Syntax

SQL syntax evolves. Features and syntax available in newer MariaDB versions might not work in older ones, and vice-versa (though less common).

• New syntax in old versions: Web hosts may run older MariaDB versions. A query working on your newer local setup might fail in an older production environment.

  • Subqueries (e.g., WHERE someId IN (SELECT id FROM ...)) were added in MySQL 4.1.

  • Early JOIN syntax did not always allow an ON clause.

Always check the MariaDB server version you are targeting and consult the manual for that version to ensure syntax compatibility. The manual usually indicates when specific syntax features became available.

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

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.

Object Relational Mappers (ORM)

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.

Stored Procedures and Functions

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.

Views

Views are useful to separate complex processing, such as advanced JOIN operations, from the application. In some cases, VIEWs introduce performance issues, but these cases are rare.

Application Code

As for database code / SQL in applications, there are several best practices to stick to.

SELECT *

Avoid SELECT * in applications; these have several bad effects, such as assuming in the application what columns are in a table, no more and no less, and in what order. This is not a good idea. In addition, selecting more columns than necessary does affect performance and may cause the optimizer to use a non-optimal path. Note that SELECT * makes two assumptions: first that it assumes which columns exist in a table, and secondly, the order in which these columns are defined. Getting any of these wrong can break things unnecessarily, and using this construct in application code makes schema changes more difficult.

INSERT Without Column Names

Just as in the case of SELECT *, an INSERT without column names, such as this ...

... is a bad idea. The proper way to write an SQL statement like this is:

Not doing this may cause errors in the application if the table is changed, such as when columns are added or the order of the columns is changed.

Processing of Column Data

When data is returned from a SELECT statement, it is sometimes tempting to put processing of values in the SQL statement, like this:

This is not incorrect in any way and is perfectly valid, but sometimes it is better to do this processing in the application. The best is to be consistent and the issue with this kind of construct is that it sometimes makes the SQL less readable than necessary.

Use of Reserved Words in the Schema

It is possible to use reserved words in names of schema objects and in the SQL itself, as here:

And here:

Both of these are valid constructs, but are not really recommended. The keyword order used above is likely among the most likely reserved words to use in a schema, but there are more. It is best to avoid them completely, even though quoting them makes the schema and SQL syntax valid.

Relying on Nonexplicit Assumptions

This is an issue that sometimes comes into play, where an application assumes data is processed in a particular order or in a particular way. This really should never be done in an application. One of the best examples is the ordering of rows retrieved; one should not even rely on data being returned in the order of a PRIMARY KEY or some index. Example:

In the example, the order of the rows returned changed after the index was created, as the index can be used to fetch the data, and when the optimizer does that, it processes the index in order, which means the data is returned in the order of the index. Relying on this ordering in the application is not a good idea, though. If you require data to be returned in a particular order, then you have to use an ORDER BY clause; if you don’t, the row ordering should be treated as being undetermined.

Another example is the LIMIT clause. When using LIMIT with a SELECT, the rows that are returned are undetermined unless an ORDER BY clause is provided. When using a LIMIT clause with an UPDATE or DELETE statement, it is even more important to understand that ordering is not fixed unless an ORDER BY statement is used. In general, I’d be careful with using the LIMIT clause for UPDATE and DELETE unless there is a good reason to do so.

There are other assumptions than row ordering, but it is one of the most common issues.

Code and Schema Standardization

Following some internal standards is really helpful when it comes to building applications that can be maintained. Standardizing how to determine the data type, the column name and how to interact with the database schema is a good first step in making an application easy to maintain over time. This also helps in making application code easy to read, which is also helpful.

Complex SQL

There are situations where the application-level SQL gets really complex, sometimes too complex, which in turn makes the code hard to maintain. In those cases, it is sometimes useful to break up a very complex SQL SELECT into multiple statements. In particular, complex SELECT JOIN queries are troublesome in this respect. If a JOIN is not a straight equi-join but a more complex one, for example, joining of a part of a database column, say the YEAR part of a DATETIME field, then things get really complex. An alternative is sometimes to use temporary tables, which are very efficient in MariaDB, instead of a single very complex SELECT.

See Also

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.

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:

Using --history with Partial Backups

You can use the --history option with a partial backup to log the operation in the history table for auditing purposes.

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 partitions in the backup, and then import them using the process.

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

If you are completely new to MariaDB and relational databases, you may want to start with the MariaDB Primer. Also, make sure you understand the connection parameters discussed in the Connecting to MariaDB article.

There are a number of common problems that can occur when connecting to MariaDB.

Server Not Running in Specified Location

If the error you get is something like:

or

the server is either not running, or not running on the specified port, socket or pipe. Make sure you are using the correct host, port, pipe, socket and protocol options, or alternatively, see , or .

The socket file can be in a non-standard path. In this case, the socket option is probably written in the my.cnf file. Check that its value is identical in the [mysqld] and [client] sections; if not, the client will look for a socket in a wrong place.

If unsure where the Unix socket file is running, it's possible to find this out, for example:

Unable to Connect from a Remote Location

Usually, the MariaDB server does not by default accept connections from a remote client or connecting with tcp and a hostname and has to be configured to permit these.

To solve this, see

Authentication Problems

The is enabled by default on Unix-like systems. This uses operating system credentials when connecting to MariaDB via the local Unix socket file. See for instructions on connecting and on switching to password-based authentication as well as for an overview.

Authentication is granted to a particular username/host combination. user1'@'localhost', for example, is not the same as user1'@'166.78.144.191'. See the article for details on granting permissions.

Passwords are hashed with function. If you have set a password with the statement, the PASSWORD function must be used at the same time. For example, SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass') rather than just SET PASSWORD FOR 'bob'@'%.loc.gov' = 'newpass' .

Problems Exporting Query Results

If you can run regular queries but get an authentication error when running the , or statements, you do not have permission to write files to the server. This requires the FILE privilege. See the article.

Access to the Server, but not to a Database

If you can connect to the server, but not to a database, for example:

or can connect to a particular database, but not another, for examplemariadb -uname -p -u name db1 works but not mariadb -uname -p -u name db2, you have not been granted permission for the particular database. See the article.

Option Files and Environment Variables

It's possible that option files or environment variables may be providing incorrect connection parameters. Check the values provided in any option files read by the client you are using (see and the documentation for the particular client you're using - see ).

Option files can usually be suppressed with no-defaults option, for example:

Unable to Connect to a Running Server / Lost root Password

If you are unable to connect to a server, for example because you have lost the root password, you can start the server without using the privilege tables by running the option, which gives users full access to all tables. You can then run to resume using the grant tables, followed by to change the password for an account.

localhost and %

You may have created a user with something like:

This creates a user with the '%' wildcard host.

However, you may still be failing to login from localhost. Some setups create anonymous users, including localhost. So, the following records exist in the user table:

Since you are connecting from localhost, the anonymous credentials, rather than those for the 'melisa' user, are used. The solution is either to add a new user specific to localhost, or to remove the anonymous localhost user.

See Also

CC BY-SA / Gnu FDL

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

Backing up the Database Server

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

To take a backup, run the following command:

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

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

Here is an example backup directory:

Using the Backup History Feature

You can optionally use the --history option to record metadata about your full backup in the database. This creates a centralized log and allows future incremental backups to reference this full backup by name instead of by directory path.

• Privileges: The backup user requires INSERT, CREATE, and ALTER privileges on the history table (mysql.mariadb_backup_history in MariaDB 10.11+, or PERCONA_SCHEMA.xtrabackup_history in older versions).

• Failure Case: If the user lacks privileges, the backup will complete the file copy process but will fail at the final step with an INSERT command denied error.

Preparing the Backup for Restoration

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

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

Backup Preparation Steps

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

2. 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 datadir is empty.

• Then, run mariadb-backup with one of the options mentioned above:

• Then, you may need to fix the file permissions.

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

• Finally, start the MariaDB Server process.

Restoring with Other Tools

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

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

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

About mariadb-dump

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

Key Advantages:

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

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

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

Backing Up All Databases

To export all databases managed by your MariaDB server:

• --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 a Single Database

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:

Backing Up Specific Tables

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 option is not used when dumping specific tables in this manner.

Important Considerations and Best Practices

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

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

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

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:

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

Redo Log Handling

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

• This is needed to record all changes done while the backup is running. (The redo log logically is a single circular file, split into innodb_log_files_in_group files.)

• The log is also used to see detect if any truncate or online alter tables are used.

• The assumption is that the copy thread are able to keep up with server. It should always be able keep up, if the redo log is big enough.

Copy-phase for InnoDB Tablespaces

• Copy all selected tablespaces, file by file, in dedicated threads in mariadb-backup without involving the mysqld server.

• This is special “careful” copy, it looks for page-level consistency by checking the checksum.

• The files are not point-in-time consistent as data may change during copy.

• The idea is that InnoDB recovery would make it point-in-time consistent.

Create a Consistent Backup Point

• Execute FLUSH TABLE WITH READ LOCK. This is default, but may be omitted with the -–no-lock parameter. The reason why FLUSH is needed is to ensure that all tables are in a consistent state at the exact same point in time, independent of storage engine.

• If --lock-ddl-per-table is used and there is a user query waiting for MDL, the user query are killed to resolve a deadlock. Note that these are only queries of type ALTER, DROP, TRUNCATE

Last Copy Phase

• Copy .frm, MyISAM, Aria and other storage engine files.

• If MyRocks is used, create rocksdb checkpoint via the 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.

Release Locks

• If FLUSH TABLE WITH READ LOCK was done:

  • execute: UNLOCK TABLES

• If --lock-ddl-per-table was done:

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, only InnoDB tables are consistent (not the privilege tables in the mysql database or the binary log). The backup point depends on the content of the redo log within the backup itself.

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

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:

Multithreading

Technical challenge:: CPU bottlenecks

Trade-off: Increased workload during backups

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

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.

Taking Snapshots

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

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 below). The SUPER privilege is also required if statement-based binary logging is used. See 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.

Running Stored Routines

• 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 DEFINER, the function body is always evaluated using the privileges of the definer account.

DEFINER Clause

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

SQL SECURITY Clause

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

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

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

Dropping Stored Routines

All privileges that are specific to a stored routine are dropped when a 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.

See Also

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

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, RANGE COLUMNS, allows us to use multiple columns and more datatypes.

Syntax

The last part of a CREATE TABLE 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.

• 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 indicates the upper bound for that partition. The values must be ascending. For the first partition, the lower limit is NULL

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 ; instead, 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 are 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

Partitioning a log table by year:

Partitioning the table by both year and month:

In the last example, the function is used 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:

To avoid the error, use the IGNORE keyword:

An alternative definition with MAXVALUE as a catchall:

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

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

See Also

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

This guide offers a simple, hands-on introduction to three basic JOIN types in MariaDB: INNER JOIN, CROSS JOIN, and LEFT JOIN. Use these examples to understand how different joins combine data from multiple tables based on specified conditions.

Setup: Example Tables and Data

First, create and populate two simple tables,

This article is a continuation of the . If you're getting started with JOIN statements, review that page first.

The Employee Database

Let us begin by using an example employee database of a fairly small family business, which does not anticipate expanding in the future.

First, we create the table that will hold all of the employees and their contact information:

Next, we add a few employees to the table:

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

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:

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

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

Defining How Your Data Is Stored

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

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.

Creating a Stored Procedure

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

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

Creating Stored Functions

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

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