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

Logging into MariaDB

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

Replace user_name with your database username. Replace ip_address with the host name or address of your server. If you're accessing MariaDB from the same server you're logged into, then don't include -h and the

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}

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

can be used to support the strategy.

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

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

The following files exist for each partitioned tables:

Syntax

Description

LINEAR KEY

The table in the database contains information about partitions.

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

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

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

Connecting to MariaDB

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

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

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

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

Creating a Structure

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

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

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

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

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

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

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

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

Minor Items

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

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

Entering Data

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

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

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

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

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

Retrieving Data

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

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

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

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

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

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

Changing & Deleting Data

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

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

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

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

Conclusion

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

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

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?

Recovery Objectives

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

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

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

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

Achieving RPO

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

Techniques for achieving RPO include:

• Frequent incremental backups and less frequent full backups.

• Performing backups in conjunction with replication and clustering to eliminate impact on production workloads, allowing a higher backup frequency.

• Automated monitoring of backup status.

• Automated testing of backups.

Achieving RTO

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

Techniques for achieving RTO include:

• Leveraging information produced during incident response, which can reduce the set of data to restore from backups, or identify specific data validation requirements dependent on the nature of the incident.

• Having fast access to backup data. Performance requirements of backup infrastructure should be understood for both backup and restoration workloads.

• Using delayed replication, either within the same data center or to a different data center, can provide shorter path to recovery. This is particularly true when coupled with robust application monitoring, which allows intervention before the window of delay elapses.

Replication Considerations

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

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

Encryption Considerations

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

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

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

Backup Storage Considerations

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

• Backup data should always be stored separately from the system being backed up, and separate from the system used for recovery.

• Backup data should be subject to equal or more controls than data in production databases. For example, backup data should generally be encrypted even where a decision has bee made that a production database will not use data-at-rest encryption.

• Business requirements may define a need for offsite storage of backups as a means of guaranteeing delivery on RPO. In these cases you should also consider onsite storage of backups as a means of guaranteeing delivery on RTO.

Backup Testing

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

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

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

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}

Syntax

Description

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

IF EXISTS

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

For dropping a (UDF), see .

Examples

See Also

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

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}

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}

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.

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

See Also

• contains information about existing partitions.

• for suggestions on using partitions

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

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

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

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

• ; you can use instead.

• and .

Syntax

Description

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

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

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

Sometimes the WHERE clause does not contain the necessary information to use partition pruning, or the optimizer cannot infer this information. However, we may know which partitions are relevant for the query. 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:

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

Creating Stored Routines

• To create a stored routine, the privilege is needed. The privilege is required if a DEFINER

Designing Queries

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

Using Whitespace

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

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

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

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

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

Table and Field Aliases

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

Bad:

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

Better:

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

Placing JOIN conditions

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

Bad:

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

Better:

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

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

Finding Syntax Errors

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

Interpreting the Empty Error

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

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

Check for missing closers, such as ' and ):

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

Checking for keywords

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

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

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

• DESC is a common abbreviation for "description" fields. It means "descending" in a MariaDB ORDER clause.

• DATE, TIME, and TIMESTAMP are all common field names. They are also field types.

• ORDER appears in sales applications. MariaDB uses it to specify sorting for results.

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

Version specific syntax

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

will now fail.

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

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

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

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

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

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

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}

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.

Restore the backup file on MariaDB

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

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

When the process is complete, click Finish.

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

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

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

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 article briefly discusses the main ways to backup MariaDB. For detailed descriptions and syntax, see the individual pages. More detail is in the process of being added.

Logical vs Physical Backups

Logical backups consist of the SQL statements necessary to restore the data, such as CREATE DATABASE, CREATE TABLE and INSERT.

Physical backups are performed by copying the individual data files or directories.

The main differences are as follows:

• logical backups are more flexible, as the data can be restored on other hardware configurations, MariaDB versions or even on another DBMS, while physical backups cannot be imported on significantly different hardware, a different DBMS, or potentially even a different MariaDB version.

• logical backups can be performed at the level of database and table, while physical databases are the level of directories and files. In the and storage engines, each table has an equivalent set of files.

• logical backups are larger in size than the equivalent physical backup.

• logical backups takes more time to both backup and restore than the equivalent physical backup.

• log files and configuration files are not part of a logical backup

Backup Tools

mariadb-backup

The program is a fork of with added support for and .

mariadb-dump

(previously mysqldump) performs a logical backup. It is the most flexible way to perform a backup and restore, and a good choice when the data size is relatively small.

For large datasets, the backup file can be large, and the restore time lengthy.

mariadb-dump dumps the data into SQL format (it can also dump into other formats, such as CSV or XML) which can then easily be imported into another database. The data can be imported into other versions of MariaDB, MySQL, or even another DBMS entirely, assuming there are no version or DBMS-specific statements in the dump.

mariadb-dump dumps triggers along with tables, as these are part of the table definition. However, , , and are not, and need extra parameters to be recreated explicitly (for example, --routines and --events). and are however also part of the system tables (for example ).

InnoDB Logical Backups

InnoDB uses the , which stores data and indexes from its tables in memory. This buffer is very important for performance. If InnoDB data doesn't fit the memory, it is important that the buffer contains the most frequently accessed data. However, last accessed data is candidate for insertion into the buffer pool. If not properly configured, when a table scan happens, InnoDB may copy the whole contents of a table into the buffer pool. The problem with logical backups is that they always imply full table scans.

An easy way to avoid this is by increasing the value of the system variable. It represents the number of milliseconds that must pass before a recently accessed page can be put into the "new" sublist in the buffer pool. Data which is accessed only once should remain in the "old" sublist. This means that they will soon be evicted from the buffer pool. Since during the backup process the "old" sublist is likely to store data that is not useful, one could also consider resizing it by changing the value of the system variable.

It is also possible to explicitly dump the buffer pool on disk before starting a logical backup, and restore it after the process. This will undo any negative change to the buffer pool which happens during the backup. To dump the buffer pool, the system variable can be set to ON. To restore it, the system variable can be set to ON.

mariadb-dump Examples

Backing up a single database

Restoring or loading the database

See the page for detailed syntax and examples.

mariadb-hotcopy

performs a physical backup, and works only for backing up and tables. It can only be run on the same machine as the location of the database directories.

mariadb-hotcopy Examples

Percona XtraBackup

Percona XtraBackup is not supported in MariaDB. is the recommended backup method to use instead of Percona XtraBackup. See for more information.

is a tool for performing fast, hot backups. It was designed specifically for databases, but can be used with any storage engine (although not with and ). It is not included with MariaDB.

Filesystem Snapshots

Some filesystems, like Veritas, support snapshots. During the snapshot, the table must be locked. The proper steps to obtain a snapshot are:

• From the client, execute . The client must remain open.

• From a shell, execute mount vxfs snapshot

• The client can execute .

• Copy the snapshot files.

LVM

Widely-used physical backup method, using a Perl script as a wrapper. See for more information.

Percona TokuBackup

For details, see:

dbForge Studio for MySQL

Besides the system utilities, it is possible to use third-party GUI tools to perform backup and restore operations. In this context, it is worth mentioning dbForge Studio for MySQL, a feature-rich database IDE that is fully compatible with MariaDB and delivers extensive backup functionality.

The backup and restore module of the Studio allows precise up to particular database objects. The feature of scheduling regular backups offers specific settings to handle errors and keep a log of them. Additionally, settings and configurations can be saved for later reuse.

These operations are wizard-aided allowing users to set up all tasks in a visual mode.

See Also

• (mariadb.com blog)

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

A Stored Procedure is a routine invoked with a CALL 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 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 for full syntax details.

Why use Stored Procedures?

Security is a key reason. Banks commonly use stored procedures so that applications and users don't have direct access to the tables. Stored procedures are also useful in an environment where multiple languages and clients are all used to perform the same operations.

Stored Procedure listings and definitions

To find which stored functions are running on the server, use .

or query the in the INFORMATION_SCHEMA database directly:

To find out what the stored procedure does, use .

Dropping and Updating a Stored Procedure

To drop a stored procedure, use the statement.

To change the characteristics of a stored procedure, use . 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 (which retains existing privileges), or DROP PROCEDURE followed CREATE PROCEDURE .

Permissions in Stored Procedures

See the article .

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

How MariaDB Handles Statement-Based Binary Logging of Routines

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.

Examples

A deterministic function:

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

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

This page documents status variables related to the Aria storage engine. See Server Status Variables for a complete list of status variables that can be viewed with SHOW STATUS.

See also the Full list of MariaDB options, system and status variables.

Aria_pagecache_blocks_not_flushed

• Description: The number of dirty blocks in the Aria page cache. The global value can be flushed by .

• Scope: Global

• Data Type: numeric

Aria_pagecache_blocks_unused

• Description: Free blocks in the Aria page cache. The global value can be flushed by .

• Scope: Global

• Data Type: numeric

Aria_pagecache_blocks_used

• Description: Blocks used in the Aria page cache. The global value can be flushed by .

• Scope: Global

• Data Type: numeric

Aria_pagecache_read_requests

• Description: The number of requests to read something from the Aria page cache.

• Scope: Global

• Data Type: numeric

Aria_pagecache_reads

• Description: The number of Aria page cache read requests that caused a block to be read from the disk.

• Scope: Global

• Data Type: numeric

Aria_pagecache_write_requests

• Description: The number of requests to write a block to the Aria page cache.

• Scope: Global

• Data Type: numeric

Aria_pagecache_writes

• Description: The number of blocks written to disk from the Aria page cache.

• Scope: Global

• Data Type: numeric

Aria_transaction_log_syncs

• Description: The number of Aria log fsyncs.

• Scope: Global

• Data Type: numeric

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

CONNECT is not just a new “YASE” (Yet another Storage Engine) that provides another way to store data with additional features. It brings a new dimension to MariaDB, already one of the best products to deal with traditional database transactional applications, further into the world of business intelligence and data analysis, including NoSQL facilities. Indeed, BI is the set of techniques and tools for the transformation of raw data into meaningful and useful information. And where is this data?

"It's amazing in an age where relational databases reign supreme when it comes to managing data that so much information still exists outside RDBMS engines in the form of flat files and other such constructs. In most enterprises, data is passed back and forth between disparate systems in a fashion and speed that would rival the busiest expressways in the world, with much of this data existing in common, delimited files. Target systems intercept these source files and then typically proceed to load them via ETL (extract, transform, load) processes into databases that then utilize the information for business intelligence, transactional functions, or other standard operations. ETL tasks and data movement jobs can consume quite a bit of time and resources, especially if large volumes of data are present that require loading into a database. This being the case, many DBAs welcome alternative means of accessing and managing data that exists in file format."

• Robin Schumacher[]

What he describes is known as MED (Management of External Data) enabling the handling of data not stored in a DBMS database as if it were stored in tables. An ISO standard exists that describes one way to implement and use MED in SQL by defining foreign tables for which an external FDW (Foreign Data Wrapper) has been developed in C.

However, since this was written, a new source of data was developed as the “cloud”. Data are existing worldwide and, in particular, can be obtained in JSON or XML format in answer to REST queries. From , it is possible to create JSON, XML or CSV tables based on data retrieved from such REST queries.

MED as described above is a rather complex way to achieve this goal and MariaDB does not support the ISO SQL/MED standard. But, to cover the need, possibly in transactional but mostly in decision support applications, the CONNECT storage engine supports MED in a much simpler way.

The main features of CONNECT are:

1. No need for additional SQL language extensions.

2. Embedded wrappers for many external data types (files, data sources, virtual).

3. NoSQL query facilities for , , HTML files and using JSON UDFs.

4. NoSQL data obtained from REST queries (requires cpprestsdk).

With CONNECT, MariaDB has one of the most advanced implementations of MED and NoSQL, without the need for complex additions to the SQL syntax (foreign tables are "normal" tables using the CONNECT engine).

Giving MariaDB easy and natural access to external data enables the use of all of its powerful functions and SQL-handling abilities for developing business intelligence applications.

With version 1.07 of CONNECT, retrieving data from REST queries is available in all binary distributed version of MariaDB, and, from 1.07.002, CONNECT allows workspaces greater than 4GB.

1. Robin Schumacher is Vice President Products at DataStax and former Director of Product Management at MySQL. He has over 13 years of database experience in DB2, MySQL, Oracle, SQL Server and other database engines.

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}

When a MariaDB developer first creates a MariaDB database for a client, often times the client has already accumulated data in other, simpler applications. Being able to convert data easily to MariaDB is critical. In the previous two articles of this MariaDB series, we explored how to set up a database and how to query one. In this third installment, we will introduce some methods and tools for bulk importing of data into MariaDB. This isn't an overly difficult task, but the processing of large amounts of data can be intimidating for a newcomer and as a result it can be a barrier to getting started with MariaDB. Additionally, for intermediate developers, there are many nuances to consider for a clean import, which is especially important for automating regularly scheduled imports. There are also restraints to deal with that may be imposed on a developer when using a web hosting company.

Foreign Data Basics

Clients sometimes give developers raw data in formats created by simple database programs like MS Access ®. Since non-technical clients don't typically understand database concepts, new clients often give me their initial data in Excel spreadsheets. Let's first look at a simple method for importing data. The simplest way to deal with incompatible data in any format is to load it up in its original software and to export it out to a delimited text file. Most applications have the ability to export data to a text format and will allow the user to set the delimiters. We like to use the bar (i.e.,

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

Execution Stages

If you are completely new to MariaDB and relational databases, you may want to start with the . Also, make sure you understand the connection parameters discussed in the 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

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

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 main characteristic of is to enable accessing data scattered on a machine as if it was a centralized database. This, and the fact that locking is not used by connect (data files are open and closed for each query) makes CONNECT very useful for importing or exporting data into or from a MariaDB database and also for all types of Business Intelligence applications. However, it is not suited for transactional applications.

For instance, the index type used by CONNECT is closer to bitmap indexing than to B-trees. It is very fast for retrieving result but not when updating is done. In fact, even if only one indexed value is modified in a big table, the index is entirely remade (yet this being four to five times faster than for a b-tree index). But normally in Business Intelligence applications, files are not modified so often.

If you are using CONNECT to analyze files that can be modified by an external process, the indexes are of course not modified by it and become outdated. Use the OPTIMIZE TABLE command to update them before using the tables based on them.

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 or a non-unique key. However, it has a limitation: it is not possible to insert a value which is lower than the next AUTO_INCREMENT value.

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

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

• The addition of the AGGREGATE keyword, so CREATE AGGREGATE FUNCTION

• The FETCH GROUP NEXT ROW instruction inside the loop

Syntax

Description

HASH

The storage engine supports three different table storage formats.

These are FIXED, DYNAMIC and PAGE, and they can be set with the ROW FORMAT option in the statement. PAGE is the default format, while FIXED and DYNAMIC are essentially the same as the .

The statement can be used to see the storage format used by a table.

Fixed-length

Fixed-length (or static) tables contain records of a fixed-length. Each column is the same length for all records, regardless of the actual contents. It is the default format if a table has no BLOB, TEXT, VARCHAR or VARBINARY fields, and no ROW FORMAT is provided. You can also specify a fixed table with ROW_FORMAT=FIXED in the table definition.

The BLACKHOLE storage engine accepts data but does not store it and always returns an empty result.

A table using the BLACKHOLE storage engine consists of a single .frm table format file, but no associated data or index files.

This storage engine can be useful, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master. The master can run a BLACKHOLE storage engine, with the data replicated to the slave for processing.

Installing the Plugin

Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.

The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing or :

The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an :

Uninstalling the Plugin

You can uninstall the plugin dynamically by executing or :

If you installed the plugin by providing the or the options in a relevant server in an , then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.

Using the BLACKHOLE Storage Engine

Using with DML

, , and statements all work with the BLACKHOLE storage engine. However, no data changes are actually applied.

Using with Replication

If the binary log is enabled, all SQL statements are logged as usual, and replicated to any slave servers. However, since rows are not stored, it is important to use statement-based rather than the row or mixed format, as and statements are neither logged nor replicated. See .

Using with Triggers

Some work with the BLACKHOLE storage engine.

BEFORE for statements are still activated.

for and statements are not activated.

with the FOR EACH ROW clause do not apply, since the tables have no rows.

Using with Foreign Keys

Foreign keys are not supported. If you convert an table to BLACKHOLE, then the foreign keys will disappear. If you convert the same table back to InnoDB, then you will have to recreate them.

Using with Virtual Columns

If you convert an table which contains to BLACKHOLE, then it produces an error.

Using with AUTO_INCREMENT

Because a BLACKHOLE table does not store data, it will not maintain the value. If you are replicating to a table that can handle AUTO_INCREMENT columns, and are not explicitly setting the primary key auto-increment value in the query, or using the statement, inserts will fail on the slave due to duplicate keys.

Limits

The maximum key size is:

• 3500 bytes (>= , , , and )

• 1000 bytes (<= , , , and ).

Examples

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

Overview

Oracle documentation describing DBMS_OUTPUT can be found here:

The main idea of DBMS_OUTPUT is:

• Messages submitted by DBMS_OUTPUT.PUT_LINE() are not sent to the client until the sending subprogram (or trigger) completes. There is no a way to flush output during the execution of a procedure.

• Therefore, lines are collected into a server side buffer, which, at the end of the current user statement, can be fetched to the client side using another SQL statement. Then, they can be read using a regular MariaDB Connector-C API. No changes in the client-protocol are needed.

• Oracle's SQLPlus uses the procedure DBMS_PACKAGE.GET_LINES() to fetch the output to the client side as an array of strings.

Package Routines

Package Overview

MariaDB implements all routines supported by Oracle, except GET_LINES():

• Procedure ENABLE() - enable the routines.

• Procedure DISABLE() - disable the routines. If the package is disabled, all calls to subprograms, such as PUT() and PUT_LINE(), are ignored (or exit immediately without doing anything).

• Procedure PUT_LINE()

The package starts in disabled mode, so an explicit enabling is needed:

Details

If a call for GET_LINE or GET_LINES did not retrieve all lines, then a subsequent call for PUT, PUT_LINE, or NEW_LINE discards the remaining lines (to avoid confusing with the next message). This script demonstrates the principle:

Data Type for the Buffer

Oracle uses this data type as a storage for the buffer:

Like Oracle, MariaDB uses an associative array as a storage for the buffer.

Data Type Used for GET_LINES()

In Oracle, the function GET_LINES() returns an array of strings of this data type:

MariaDB does not have array data types in the C and C++ connectors, so they can't take advantage of GET_LINES() in a client program.

Fetching all Lines in a PL/SQL Program

Fetching all lines in a PL/SQL program is implemented using a loop of sys.DBMS_OUTPUT.GET_LINE() calls:

Fetching all Lines on the Client Side

Fetching all lines on the client side (for instance, in a C program using Connector/C) is done by using a loop of sys.DBMS_OUTPUT.GET_LINE() queries.

Limits

Oracle has the following limits:

• The maximum individual line length (sent to DBMS_OUTPUT) is 32767 bytes.

• The default buffer size is 20000 bytes. The minimum size is 2000 bytes. The maximum is unlimited.

MariaDB also implements some limits, either using the total size of all rows or using the row count.

Installation

Like other bootstrap scripts, the script creating DBMS_OUTPUT:

• Is put into a new separate /scripts/dbms_ouput.sql file in the source directory;

• Is installed into /share/dbms_ouput.sql of the installation directory.

\

CONNECT supports MariaDB virtual and persistent columns. It is also possible to declare a column as being a CONNECT special column. Let us see on an example how this can be done. The boys table we have seen previously can be recreated as:

We have defined two CONNECT special columns. You can give them any name; it is the field SPECIAL option that specifies the special column functional name.

Note: the default values specified for the special columns do not mean anything. They are specified just to prevent getting warning messages when inserting new rows.

For the definition of the agehired virtual column, no CONNECT options can be specified as it has no offset or length, not being stored in the file.

The command:

will return:

Existing special columns are listed in the following table:

Note: CONNECT does not currently support auto incremented columns. However, a ROWID special column will do the job of a column auto incremented by 1.

_{This page is licensed: GPLv2}

MariaDB has many built-in functions that can be used to manipulate strings of data. With these functions, one can format data, extract certain characters, or use search expressions. Good developers should be aware of the string functions that are available. Therefore, in this article we will go through several string functions, grouping them by similar features, and provide examples of how they might be used.

Formatting

There are several string functions that are used to format text and numbers for nicer display. A popular and very useful function for pasting together the contents of data fields with text is the function. As an example, suppose that a table called contacts has a column for each sales contact's first name and another for the last name. The following SQL statement would put them together:

This statement will display the first name, a space, and then the last name together in one column. The AS clause will change the column heading of the results to Name.

This article covers connecting to MariaDB and the basic connection parameters. If you are completely new to MariaDB, take a look at first.

In order to connect to the MariaDB server, the client software must provide the correct connection parameters. The client software will most often be the , used for entering statements from the command line, but the same concepts apply to any client, such as a , a client to run backups such as , etc. The rest of this article assumes that the mariadb command line client is used.

If a connection parameter is not provided, it will revert to a default value.

For example, to connect to MariaDB using only default values with the mariadb client, enter the following from the command line:

In this case, the following defaults apply:

• The host name is

Some MariaDB packages bind MariaDB to 127.0.0.1 (the loopback IP address) by default as a security measure using the configuration directive. Old MySQL packages sometimes disabled TCP/IP networking altogether using the directive. Before going in to how to configure these, let's explain what each of them actually does:

• is fairly simple. It just tells MariaDB to run without any of the TCP/IP networking options.

• requires a little bit of background information. A given server usually has at least two networking interfaces (although this is not required) and can easily have more. The two most common are a Loopback network device and a physical Network Interface Card (NIC) which allows you to communicate with the network. MariaDB is bound to the loopback interface by default because it makes it impossible to connect to the TCP port on the server from a remote host (the bind-address must refer to a local IP address, or you will receive a fatal error and MariaDB will not start). This of course is not desirable if you want to use the TCP port from a remote host, so you must remove this bind-address directive or replace it either

With MariaDB it's very easy to copy tables between different MariaDB databases and different MariaDB servers. This works for tables created with the , , , , , , and engines.

The normal procedures to copy a table is:

The table files can be found in /databasename (you can executeSELECT @@datadir to find the correct directory). When copying the files, you should copy all files with the same table_name + various extensions. For example, for an Aria table of name foo, you will have files foo.frm, foo.MAI, foo.MAD and possibly foo.TRG if you have .

If one wants to distribute a table to a user that doesn't need write access to the table and one wants to minimize the storage size of the table, the recommended engine to use is Aria or MyISAM as one can pack the table with or respectively to make it notablly smaller. MyISAM is the most portable format as it's not dependent on whether the server settings are different. Aria and InnoDB require the same block size on both servers.

Overview

MariaDB features pluggable storage engines to allow per-table workload optimization.

A storage engine is a type of for MariaDB:

• Different storage engines may be optimized for different workloads, such as transactional workloads, analytical workloads, or high throughput workloads.

The commands are a set of commands to make it possible to make an efficient external backup tool. How mariadb-backup uses these commands depends on whether you are using the version that is bundled with MariaDB Community Server or the version that is bundled with MariaDB Enterprise Server.

mariadb-backup and BACKUP STAGE Commands in MariaDB Community Server

The commands are supported. However, the version of mariadb-backup that is bundled with MariaDB Community Server does not yet use the BACKUP STAGE

A high-level overview of the main reasons for choosing a particular storage engine:

Topic List

General Purpose

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.

The CONNECT storage engine enables MariaDB to access external local or remote data (MED). This is done by defining tables based on different data types, in particular files in various formats, data extracted from other DBMS or products (such as Excel or MongoDB) via ODBC or JDBC, or data retrieved from the environment (for example DIR, WMI, and MAC tables)

This storage engine supports table partitioning, MariaDB virtual columns and permits defining special columns such as ROWID, FILEID, and SERVID.

The storage engine must be installed before it can be used.

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

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

Syntax

The last part of a statement can be definition of the new table's partitions. In the case of RANGE

Directly using external (file) data has many advantages, such as to work on “fresh” data produced for instance by cash registers, telephone switches, or scientific apparatus. However, you may want in some case to import external data into your MariaDB database. This is extremely simple and flexible using the CONNECT handler. For instance, let us suppose you want to import the data of the xsample.xml XML file previously given in example into a table called biblio belonging to the connect database. All you have to do is to create it by:

This last statement creates the table and inserts the original XML data, translated to tabular format by the xsampall2 CONNECT table, into the MariaDB biblio table. Note that further transformation on the data could have been achieved by using a more elaborate Select statement in the Create statement, for instance using filters, alias or applying functions to the data. However, because the Create Table process copies table data, later modifications of the

The storage engine used to be called Maria. This page gives the history and background of how and why this name was changed to Aria.