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

     Enter the root password you set during the secure installation.

For Windows

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

Steps:

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:

   The installer will guide you through the process, including:

   • Accepting the end-user license agreement.

   • Selecting features and the installation directory.

   • Setting a password for the root user.

   • Configuring MariaDB as a service and setting the port (default is 3306).

   • Optionally, enabling UTF8 as the default server character set.

Important Notes:

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

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

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

Additional Resources:

• Get Started with MariaDB

• How To Install MariaDB on Ubuntu 22.04 - DigitalOcean

• Install MariaDB - MariaDBTutorial.com

This guide explains how to add new data and modify existing data in MariaDB using INSERT, REPLACE, and UPDATE statements. Learn about various options for handling duplicates, managing statement priorities, inserting data from other tables, and performing conditional updates.

Adding Data with INSERT

The INSERT statement is used to add new rows to a table.

Basic Syntax:

If providing values for all columns in their defined order:

The number of values must match the number of columns in table1.

Specifying Columns:

It's good practice to specify the columns you are inserting data into, which also allows you to insert columns in any order or omit columns that have default values or allow NULL.

• The INTO keyword is optional but commonly used for readability.

• If a column is not listed and is an AUTO_INCREMENT key, its value will be generated. For other omitted columns, their DEFAULT value will be used, or NULL if allowed. You can explicitly insert a default value using the DEFAULT keyword in the VALUES list for a specific column.

Multiple Row Inserts:

Insert multiple rows in a single statement for efficiency:

The VALUES keyword is used only once, with each row's values enclosed in parentheses and separated by commas.

Handling Duplicates with INSERT IGNORE:

If you attempt to insert a row that would cause a duplicate value in a PRIMARY KEY or UNIQUE index, an error normally occurs, and the row (and potentially subsequent rows in a multi-row insert) might not be inserted.

Using IGNORE tells MariaDB to discard the duplicate row(s) and continue inserting any other valid rows without generating an error.

Managing INSERT Priority and Behavior

LOW_PRIORITY:

An INSERT statement normally takes priority over SELECT statements, potentially locking the table and making other clients wait. LOW_PRIORITY makes the INSERT wait until no other clients are reading from the table.

• Once the LOW_PRIORITY insert begins, it will lock the table as usual. New read requests that arrive while it's waiting will be processed before it.

DELAYED:

(Note: INSERT DELAYED is a feature that was primarily associated with the MyISAM storage engine. It is deprecated in older MariaDB/MySQL versions and removed in modern MariaDB versions (e.g., from MariaDB 10.5). Check your MariaDB version for support and consider alternatives if using a recent version.)

INSERT DELAYED allowed the server to queue the insert request and return control to the client immediately. Data was written when the table was not in use. Multiple DELAYED inserts were batched.

Flaws included no confirmation of successful insertion and potential data loss if the server crashed before data was written from memory.

Inserting Data from Another Table (INSERT...SELECT)

You can insert rows into a table based on data retrieved from another table (or tables) using INSERT ... SELECT.

• The columns in the INSERT INTO softball_team (...) list must correspond in number and general data type compatibility to the columns in the SELECT list.

• INSERT...SELECT statements generally cannot operate on the exact same table as both the target and the source directly without mechanisms like temporary tables or certain subquery structures.

Replacing Data with REPLACE

The REPLACE statement works like INSERT, but if a new row has the same value as an existing row for a PRIMARY KEY or a UNIQUE index, the existing row is deleted before the new row is inserted. If no such conflict exists, it acts like a normal INSERT.

• Flags like LOW_PRIORITY work similarly to INSERT.

• REPLACE also supports the REPLACE ... SELECT syntax.

• Because REPLACE performs a delete then an insert, any columns in the table not specified in the REPLACE statement will receive their default values for the newly inserted row, not values from the old row.

Modifying Data with UPDATE

Use the UPDATE statement to change data in existing rows.

Basic Syntax:

• The SET clause specifies which columns to modify and their new values.

• The WHERE clause is crucial; it determines which rows are updated. Without a WHERE clause, all rows in the table will be updated.

• LOW_PRIORITY and IGNORE (to ignore errors like unique key violations during update, allowing other valid row updates to proceed) can also be used with UPDATE.

Using Current Column Values in an Update:

You can use a column's current value in the calculation for its new value.

ORDER BY and LIMIT with UPDATE:

You can control the order in which rows are updated and limit the number of rows affected (for single-table updates).

This updates the 10 most recently created 'pending' rows.

Multi-Table UPDATE:

You can update rows in one table based on values from another table by joining them.

• Here, products.stock_count is updated using values from stock_levels.

• ORDER BY and LIMIT are generally not allowed with multi-table UPDATE statements in this direct join form.

Conditional Inserts or Updates (INSERT ... ON DUPLICATE KEY UPDATE)

This powerful feature allows you to INSERT a new row, but if a duplicate key (Primary or Unique) conflict occurs, it performs an UPDATE on the existing row instead.

• If id '1012' does not exist, the row is inserted with status_column = 'new'.

• If id '1012' already exists, the existing row is updated: status_column is set to 'old', and col2 is updated with the value that would have been inserted for col2 (using VALUES(col2)).

• The IGNORE keyword can be used with INSERT ... ON DUPLICATE KEY UPDATE to ignore errors that might occur during the UPDATE part if the update itself causes a problem (though this is less common). If IGNORE is used with INSERT and ON DUPLICATE KEY UPDATE is also present, IGNORE only applies to the INSERT part, not the UPDATE part.

Further Data Modification Methods

Beyond these SQL statements, MariaDB offers bulk methods for adding data, such as:

• LOAD DATA INFILE: For importing data from text files.

• mariadb-import utility: A command-line tool that uses LOAD DATA INFILE. These are covered in "Bulk Data Importing Guide").

CC BY-SA / Gnu FDL

The Essential Queries Guide offers a concise collection of commonly-used SQL queries. It's designed to help developers and database administrators quickly find syntax and examples for typical database operations, from table creation and data insertion to effective data retrieval and manipulation.

Creating a Table

To create new tables:

For more details, see the official CREATE TABLE documentation.

Inserting Records

To add data into your tables:

For more information, see the official INSERT documentation.

Using AUTO_INCREMENT

The AUTO_INCREMENT attribute automatically generates a unique identity for new rows.

Create a table with an AUTO_INCREMENT column:

When inserting, omit the id field; it will be automatically generated:

Verify the inserted records:

For more details, see the AUTO_INCREMENT documentation.

Querying from two tables on a common value (JOIN)

To combine rows from two tables based on a related column:

This type of query is a join. For more details, consult the documentation on JOINS.

Finding the Maximum Value

To find the maximum value in a column:

See the MAX() function documentation. For a grouped example, refer to Finding the Maximum Value and Grouping the Results below.

Finding the Minimum Value

To find the minimum value in a column:

See the MIN() function documentation.

Finding the Average Value

To calculate the average value of a column:

See the AVG() function documentation.

Finding the Maximum Value and Grouping the Results

To find the maximum value within groups:

Further details are available in the MAX() function documentation.

Ordering Results

To sort your query results (e.g., in descending order):

For more options, see the ORDER BY documentation.

Finding the Row with the Minimum of a Particular Column

To find the entire row containing the minimum value of a specific column across all records:

Finding Rows with the Maximum Value of a Column by Group

To retrieve the full record for the maximum value within each group (e.g., highest score per student):

Calculating Age

Use the TIMESTAMPDIFF function to calculate age from a birth date.

To see the current date (optional, for reference):

To calculate age as of a specific date (e.g., '2014-08-02'):

To calculate current age, replace the specific date string (e.g., '2014-08-02') with CURDATE().

See the TIMESTAMPDIFF() documentation for more.

Using User-defined Variables

User-defined variables can store values for use in subsequent queries within the same session.

Example: Set a variable for the average score and use it to filter results.

Example: Add an incremental counter to a result set.

See User-defined Variables for more.

View Tables in Order of Size

To list all tables in the current database, ordered by their size (data + index) in megabytes:

Removing Duplicates

To remove duplicate rows based on specific column values, while keeping one instance (e.g., the instance with the highest id).

This example assumes id is a unique primary key and duplicates are identified by the values in column f1. It keeps the row with the maximum id for each distinct f1 value.

Setup sample table and data:

To delete duplicate rows, keeping the one with the highest id for each group of f1 values:

This query targets rows for deletion (t_del) where their f1 value matches an f1 in a subquery (t_keep) that has duplicates, and their id is less than the maximum id found for that f1 group.

Verify results after deletion:

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

This guide provides essential instructions for modifying existing table structures. Learn how to add, drop, and change columns, manage indexes and default values, and rename tables, along with key precautions for these operations when working with your database.

Before You Begin: Backup Your Tables

Before making any structural changes to a table, especially if it contains data, always create a backup. The mariadb-dump utility is a common and effective tool for this.

Example: Backing up a single table

Suppose you have a database db1 and a table clients. Its initial structure is:

To back up the clients table from the command-line:

• Replace 'your_username' and 'your_password' with your actual MariaDB credentials.

• --add-locks: Locks the table during the backup and unlocks it afterward.

• db1 clients: Specifies the database and then the table.

• > clients.sql: Redirects the output to a file named clients.sql.

Restoring from a backup

If you need to restore the table:

This command uses the mariadb client to execute the SQL in clients.sql, which will typically drop the existing table (if it exists) and recreate it from the backup. Ensure no critical data has been added to the live table since the backup if you intend to overwrite it.

For the examples that follow, we'll assume structural changes are being made, sometimes on an empty table for simplicity, but the backup step is always recommended for tables with data.

Adding Columns

Use the ALTER TABLE statement with the ADD COLUMN clause.

Add a column to the end of the table:

To add a status column with a fixed width of two characters:

Add a column after a specific existing column:

To add address2 (varchar 25) after the address column:

Add a column to the beginning of the table:

(Assuming new_first_column is the one to be added at the beginning).

After additions, the table structure might look like (excluding new_first_column for consistency with original example flow):

Changing Column Definitions

Use ALTER TABLE with CHANGE or MODIFY.

Change column type (e.g., to ENUM):

The status column name is specified twice even if not changing the name itself when using CHANGE.

Change column name and keep type:

To change status to active while keeping the ENUM definition:

When using CHANGE, the current column name is followed by the new column name and the complete type definition.

Modify column type or attributes without renaming:

Use MODIFY if you are only changing the data type or attributes, not the name.

Complex Changes (e.g., ENUM migration with data):

Changing ENUM values in a table with existing data requires careful steps to prevent data loss. This typically involves:

1. Temporarily modifying the ENUM to include both old and new values.

2. Updating existing rows to use the new values.

3. Modifying the ENUM again to remove the old values.

Example of changing address to address1 (40 chars) and preparing active ENUM for new values 'yes','no' from 'AC','IA':

Then, update the data:

Finally, restrict the ENUM to new values:

Dropping Columns

To remove a column and its data (this action is permanent and irreversible without a backup):

Managing Default Values

Set a default value for a column:

If most clients are in 'LA', set it as the default for the state column:

Remove a default value from a column:

This reverts the default to its standard (e.g., NULL if nullable, or determined by data type).

This DROP DEFAULT does not delete existing data in the column.

Managing Indexes

Indexes are separate objects from columns. Modifying an indexed column often requires managing its index.

View existing indexes on a table:

The \G displays results in a vertical format, which can be easier to read for wide output.

Example output:

Changing an indexed column (e.g., Primary Key):

Attempting to CHANGE a column that is part of a PRIMARY KEY without addressing the key might result in an error like "Multiple primary key defined". The index must be dropped first, then the column changed, and the key re-added.

The order is important: DROP PRIMARY KEY first.

Changing a column with another index type (e.g., UNIQUE):

If cust_id had a UNIQUE index named cust_id_unique_idx (Key_name from SHOW INDEX):

If the Key_name is the same as the Column_name (e.g. for a single column UNIQUE key defined on cust_id where cust_id is also its Key_name):

Changing index type and handling duplicates (e.g., INDEX to UNIQUE):

If changing from an index type that allows duplicates (like a plain INDEX) to one that doesn't (UNIQUE), and duplicate data exists, the operation will fail. To force the change and remove duplicates (use with extreme caution):

The IGNORE keyword causes rows with duplicate key values (for the new UNIQUE key) to be deleted. Only the first encountered row is kept.

Renaming and Shifting Tables

Rename a table:

To change the name of clients to client_addresses:

Move a table to another database (can be combined with renaming):

To move client_addresses to a database named db2:

Re-sort data within a table (MyRocks/Aria, not typically InnoDB):

For some storage engines (excluding InnoDB where tables are ordered by the primary key), you can physically reorder rows. This does not usually apply to InnoDB unless the ORDER BY columns form the primary key.

After this, SELECT * FROM client_addresses (without an ORDER BY clause) might return rows in this new physical order, until further data modifications occur.

Key Considerations

• Backup First: Always back up tables before making structural alterations, especially on production systems.

• Data Integrity: Be mindful of how changes (e.g., type changes, ENUM modifications, dropping columns) can affect existing data. Test changes in a development environment.

• Irreversible Actions: Operations like DROP COLUMN or DROP TABLE are generally irreversible without restoring from a backup. There's typically no confirmation prompt.

• Indexes: Understand that indexes are distinct from columns. Modifying indexed columns often requires separate steps to manage the associated indexes.

• Performance: ALTER TABLE operations on large tables can be time-consuming and resource-intensive, potentially locking the table and impacting application performance. Plan these operations during maintenance windows if possible.

CC BY-SA / Gnu FDL

This guide introduces methods and tools for efficiently importing bulk data into MariaDB. Learn to prepare your data, use LOAD DATA INFILE and the mariadb-import utility, handle common data import challenges, and manage potential constraints.

Preparing Your Data File

The most common approach for bulk importing is to use a delimited text file.

1. Export Source Data: Load your data in its original software (e.g., MS Excel, MS Access) and export it as a delimited text file.

   • Delimiter: Use a character not commonly found in your data to separate fields. The pipe symbol (|) is often a good choice. Tab () is also common.

   • Record Separator: Use line feeds () to separate records.

2. Align Columns (Recommended for Simplicity): Ideally, the order and number of columns in your text file should match the target MariaDB table.

   • If the table has extra columns not in your file, they will be filled with their default values (or NULL).

   • If your file has extra columns not in the table, you'll need to specify which file columns to load (see "Mapping File Columns to Table Columns" below) or remove them from the text file.

3. Clean Data: Remove any header rows or footer information from the text file unless you plan to skip them during import (see IGNORE N LINES below).

4. Upload File: Transfer the text file to a location accessible by the MariaDB server.

   • Use ASCII mode for FTP transfers to ensure correct line endings.

   • For security, upload data files to non-public directories on the server.

Using LOAD DATA INFILE

The LOAD DATA INFILE statement is a powerful SQL command for importing data from text files. Ensure the MariaDB user has the FILE privilege.

Basic Syntax:

First, connect to MariaDB using the mariadb client and select your target database:

Then, load the data:

• Replace /tmp/prospects.txt with the actual path to your data file on the server. On Windows, paths use forward slashes (e.g., 'C:/tmp/prospects.txt').

• prospect_contact is the target table. You can also specify database_name.table_name.

• FIELDS TERMINATED BY '|' specifies the field delimiter. For tab-delimited, use '\t'.

• The default record delimiter is the line feed ().

Specifying Line Terminators and Enclosing Characters:

If your file has custom line endings or fields enclosed by characters (e.g., quotes):

• ENCLOSED BY '"': Specifies that fields are enclosed in double quotes.

• LINES STARTING BY '"': Indicates each line starts with a double quote.

• TERMINATED BY '"\r\n': Indicates each line ends with a double quote followed by a Windows-style carriage return and line feed.

• To specify a single quote as an enclosing character, you can escape it or put it within double quotes: ENCLOSED BY '\'' or ENCLOSED BY "'".

Handling Duplicate Rows

When importing data, you might encounter records with primary key values that already exist in the target table.

• Default Behavior: MariaDB attempts to import all rows. If duplicates are found and the table has a primary or unique key that would be violated, an error occurs, and subsequent rows may not be imported.

• REPLACE: If you want new data from the file to overwrite existing rows with the same primary key:SQL

• IGNORE: If you want to keep existing rows and skip importing duplicate records from the file:SQL

Importing into Live Tables

If the target table is actively being used, importing data can lock it, preventing access.

• LOW_PRIORITY: To allow other users to read from the table while the load operation is pending, use LOW_PRIORITY. The load will wait until no other clients are reading the table.SQL

  Without LOW_PRIORITY or CONCURRENT, the table is typically locked for the duration of the import.

Advanced LOAD DATA INFILE Options

Binary Line Endings:

If your file has Windows CRLF line endings and was uploaded in binary mode, you can specify the hexadecimal value:

Note: No quotes around the hexadecimal value.

Skipping Header Lines:

To ignore a certain number of lines at the beginning of the file (e.g., a header row):

SQL

Handling Escaped Characters:

If fields are enclosed by quotes and contain embedded quotes that are escaped by a special character (e.g., # instead of the default backslash \):

Mapping File Columns to Table Columns:

If the order or number of columns in your text file differs from the target table, you can specify the column mapping at the end of the LOAD DATA INFILE statement.

Assume prospect_contact table has: (row_id INT AUTO_INCREMENT, name_first VARCHAR, name_last VARCHAR, telephone VARCHAR).

And prospects.txt has columns in order: Last Name, First Name, Telephone.

• MariaDB will map data from the file's first column to name_last, second to name_first, and third to telephone.

• The row_id column in the table, not being specified in the list, will be filled by its default mechanism (e.g., AUTO_INCREMENT or DEFAULT value, or NULL).

Using the mariadb-import Utility

The mariadb-import utility (known as mysqlimport before MariaDB 10.5) is a command-line program that acts as a wrapper for LOAD DATA INFILE. It's useful for scripting imports.

Syntax:

• This command is run from the system shell, not within the mariadb client.

• Lines are continued with \ for readability here; it can be a single line.

• --password: If the password value is omitted, you'll be prompted.

• The database name (sales_dept) is specified before the file path.

• File Naming: mariadb-import expects the text file's name (without extension) to match the target table name (e.g., prospect_contact.txt for table prospect_contact). If your file is prospects.txt and table is prospect_contact, you might need to rename the file or import into a temporary table named prospects first.

• --verbose: Shows progress information.

• You can list multiple text files to import into correspondingly named tables.

Dealing with Web Hosting Restraints

Some web hosts disable LOAD DATA INFILE or mariadb-import for security reasons. A workaround involves using mariadb-dump:

1. Prepare Data Locally: Prepare your delimited text file (e.g., prospects.txt).

2. Local Import: If you have a local MariaDB server, import the text file into a local table (e.g., local_db.prospect_contact) using LOAD DATA INFILE as described above.

3. Local Export with mariadb-dump: Export the data from your local table into an SQL file containing INSERT statements.

   • --no-create-info (or -t): Prevents the CREATE TABLE statement from being included, outputting only INSERT statements. This is useful if the table already exists on the remote server.

4. Upload SQL File: Upload the generated .sql file (e.g., prospects.sql) to your web server (in ASCII mode).

5. Remote Import of SQL File: Log into your remote server's shell and import the SQL file using the mariadb client:

Handling Duplicates with mariadb-dump Output:

mariadb-dump does not have a REPLACE flag like LOAD DATA INFILE. If the target table might contain duplicates:

• Open the .sql file generated by mariadb-dump in a text editor.

• Perform a search and replace operation to change all occurrences of INSERT INTO to REPLACE INTO. The syntax for INSERT and REPLACE (for the data values part) is similar enough that this often works. Test thoroughly.

Key Considerations

• Flexibility: MariaDB provides powerful and flexible options for data importing. Understanding the details of LOAD DATA INFILE and mariadb-import can save significant effort.

• Data Validation: While these tools are efficient for bulk loading, they may not perform extensive data validation beyond basic type compatibility. Cleanse and validate your data as much as possible before importing.

• Character Sets: Ensure your data file's character set is compatible with the target table's character set to avoid data corruption. You can specify character sets in LOAD DATA INFILE.

• Other Tools/Methods: For very complex transformations or ETL (Extract, Transform, Load) processes, dedicated ETL tools or scripting languages (e.g., Python, Perl with database modules) might be more suitable, though they are beyond the scope of this guide.

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

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

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

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

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

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

For example, an index on Last_Name:

and an index on Position

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

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

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

_{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, t1 and t2, to use in the JOIN examples:

JOIN Examples and Output

Below are examples of different JOIN types using the tables t1 and t2.

INNER JOIN

An INNER JOIN produces a result set containing only rows that have a match in both tables for the specified join condition(s).

Output:

Explanation: Only the row where t1.a (value 2) matches t2.b (value 2) is returned.

CROSS JOIN

A CROSS JOIN produces a result set in which every row from the first table is joined to every row in the second table. This is also known as a Cartesian product.

Output:

Explanation: Each of the 3 rows in t1 is combined with each of the 2 rows in t2, resulting in 3 * 2 = 6 rows. Note: In MariaDB, the CROSS keyword can often be omitted if no ON clause is present (e.g., SELECT * FROM t1 JOIN t2; or SELECT * FROM t1, t2; would also produce a Cartesian product).

LEFT JOIN (t1 LEFT JOIN t2)

A LEFT JOIN (or LEFT OUTER JOIN) produces a result set with all rows from the "left" table (t1 in this case). If a match is found in the "right" table (t2), the corresponding columns from the right table are included. If no match is found, these columns are filled with NULL.

Output:

Explanation: All rows from t1 are present. For t1.a = 1 and t1.a = 3, there are no matching t2.b values, so b is NULL. For t1.a = 2, a match is found (t2.b = 2), so b is 2.

Starting with MariaDB 12.3, multiple independent LEFT JOIN operations can be reordered by the optimizer for better performance if the reorder_outer_joins flag is enabled in optimizer_switch. It is also recommended to set optimizer_prune_level=0 to prevent heuristic pruning from eliminating optimal join orders.

RIGHT JOIN (t1 RIGHT JOIN t2)

A RIGHT JOIN (or RIGHT OUTER JOIN) produces a result set with all rows from the "right" table (t2 in this case). If a match is found in the "left" table (t1), the corresponding columns from the left table are included. If no match is found, these columns are filled with NULL.

Output:

LEFT JOIN (t2 LEFT JOIN t1) - Simulating a RIGHT JOIN

This example uses a LEFT JOIN but with t2 as the left table. This effectively demonstrates how a RIGHT JOIN would behave if t1 were the left table and t2 the right. A RIGHT JOIN includes all rows from the "right" table and NULLs for non-matching "left" table columns.

Output:

Explanation: All rows from t2 are present. For t2.b = 2, a match is found (t1.a = 2), so a is 2. For t2.b = 4, there is no matching t1.a value, so a is NULL.

Older (Implicit) JOIN Syntax

The first two SELECT statements (INNER JOIN and CROSS JOIN) are sometimes written using an older, implicit join syntax:

• Implicit INNER JOIN:

  This is equivalent to SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;.

• Implicit CROSS JOIN (Cartesian Product):

  This is equivalent to SELECT * FROM t1 CROSS JOIN t2;.

While this syntax works, the explicit JOIN syntax (INNER JOIN, LEFT JOIN, etc.) with an ON clause is generally preferred for clarity and to better distinguish join conditions from filtering conditions (WHERE clause).

Understanding JOIN Types Summary

• INNER JOIN: Returns rows only when there is a match in both tables based on the join condition.

• CROSS JOIN: Returns the Cartesian product of the two tables (all possible combinations of rows).

• LEFT JOIN (Outer Join): Returns all rows from the left table, and the matched rows from the right table. If there is no match, NULL is returned for columns from the right table.

• RIGHT JOIN (Outer Join): Returns all rows from the right table, and the matched rows from the left table. If there is no match, NULL is returned for columns from the left table. (The example SELECT * FROM t2 LEFT JOIN t1 ... shows this behavior from t1's perspective).

Joining Multiple Tables

JOIN clauses can be concatenated (chained) to retrieve results from three or more tables by progressively joining them.

See Also

• More Advanced Joins

• JOIN Syntax

• Comma vs JOIN

• Joins, Subqueries and SET

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

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

This article is a continuation of the Joining Tables with JOIN Clauses Guide. 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:

Now, we create a second table, containing the hours which each employee clocked in and out during the week:

Finally, although it is a lot of information, we add a full week of hours for each of the employees into the second table that we created:

Working with the Employee Database

Now that we have a cleanly structured database to work with, let us begin this tutorial by stepping up one notch from the last tutorial and filtering our information a little.

Filtering by Name

Earlier in the week, an anonymous employee reported that Helmholtz came into work almost four minutes late; to verify this, we will begin our investigation by filtering out employees whose first names are "Helmholtz":

The result looks like this:

This is obviously more information than we care to trudge through, considering we only care about when he arrived past 7:00:59 on any given day within this week; thus, we need to add a couple more conditions to our WHERE clause.

Filtering by Name, Date and Time

In the following example, we will filter out all of the times which Helmholtz clocked in that were before 7:01:00 and during the work week that lasted from the 8th to the 12th of August:

The result looks like this:

By merely adding a few more conditions, we eliminated all of the irrelevant information; Helmholtz was late to work on the 9th and the 12th of August.

Displaying Total Work Hours per Day

Suppose you would like to—based on the information stored in both of our tables in the employee database—develop a quick list of the total hours each employee has worked for each day recorded; a simple way to estimate the time each employee worked per day is exemplified below:

The result (limited to 10 rows) looks like this:

See Also

• Joining Tables with JOIN Clauses Guide

• JOIN Syntax

• Comma vs JOIN

• Joins, Subqueries and SET

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

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

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

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

Defining How Your Data Is Stored

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

Manipulating Your Data

These statements are part of the SQL Data Manipulation Language - DML.

• SELECT: Used when you want to read (or select) your data from one or more tables.

• INSERT: Used when you want to add (or insert) new rows of data into a table.

• UPDATE: Used when you want to change (or update) existing data in a table.

• DELETE: Used when you want to remove (or delete) existing rows of data from a table.

• REPLACE: Works like INSERT, but if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

• TRUNCATE TABLE: Used to quickly remove all data from a table, resetting any AUTO_INCREMENT values. It is faster than DELETE without a WHERE clause for emptying a table.

Transactions

These statements are part of the SQL Transaction Control Language - TCL.

• START TRANSACTION (or BEGIN): Used to begin a new transaction, allowing multiple SQL statements to be treated as a single atomic unit.

• COMMIT: Used to save all changes made during the current transaction, making them permanent.

• ROLLBACK: Used to discard all changes made during the current transaction, reverting the database to its state before the transaction began.

A Simple Example Sequence

This example demonstrates several of the statements in action:

Common Query: Counting Rows

To count the number of records in a table:

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

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

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 (used in sales contexts, but is an SQL clause)

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.

• Old syntax in new versions: Sometimes, changes in operator precedence or syntax deprecation can cause issues. For example, the precedence of the comma operator relative to JOIN changed in MySQL 5.0. A query like SELECT * FROM a, b JOIN c ON a.x = c.x; that worked before might fail or produce different results.

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}

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

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

Calculating Time Across Midnight

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

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

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

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

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

Modulo Arithmetic for Time Rollover:

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

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

Tracking Date Changes with Time: Using DATETIME

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

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

1. Adding new DATETIME columns.

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

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

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

Adding Durations with DATE_ADD

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

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

• expr: The value of the interval to add.

• unit: The unit of the interval (e.g., HOUR, MINUTE, DAY, MONTH, YEAR, etc.).

Adding Hours (handles date change):

If entered and promised are DATETIME columns:

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

Adding Combined Hours and Minutes:

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

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

Date Calculations Across Months and Years with DATE_ADD

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

Adding Days:

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

Adding Combined Days and Hours:

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

Adding Combined Years and Months:

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

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

Subtracting Durations

Using DATE_ADD with a Negative Interval:

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

Using DATE_SUB(date, INTERVAL expr unit):

This function is specifically for subtracting durations.

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

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

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

What you do with System Variables

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

• Configuration & Tuning: You modify these to optimize the server for your workload. For example, you might increase innodb_buffer_pool_size to allow the database to cache more data in memory, reducing disk I/O.

• Controlling Behavior: You use them to toggle features on or off. Setting read_only to 1 ensures no data can be modified (useful for maintenance or replicas).

• Enforcing Constraints: You set limits to prevent resource exhaustion. For example, max_connections prevents the server from being overwhelmed by too many simultaneous users.

• Session Personalization: Many variables can be set at the Session level. This means you can change how the server behaves just for your current connection – like changing the sql_mode to be more or less strict – without affecting other users.

What you use Status Variables for

Status variables are read-only counters and metrics. You use them to monitor the health and activity of the server.

• Performance Monitoring: You check status variables to see if your system tuning is working. If you see high numbers for Select_full_join, it tells you that your queries are missing indexes.

• Health Checks: You use them to identify bottlenecks. If Aborted_connects is high, you might have network issues or a client with the wrong password attempting to connect repeatedly.

• Resource Tracking: They tell you how much of your allocated resources are actually being used. Comparing Max_used_connections against your system variable max_connections helps you decide if you need to scale up.

• Capacity Planning: By monitoring variables like Bytes_sent and Bytes_received over time, you can forecast when you will need to upgrade your hardware or network bandwidth.

Comparison at a Glance

Pages Documenting Variables

Here is the comprehensive list of pages that document variables.

Core Server Variables

• Server System Variables (The main list for general server configuration)

• Server Status Variables (Real-time monitoring metrics)

• Performance Schema System Variables

Storage Engine Specific Variables

• Aria System Variables

• Aria Status Variables

• InnoDB System Variables

• MyISAM System Variables

• CONNECT System Variables

• Spider System Variables

• Spider Status Variables

Replication & Performance Variables

• Replication and Binary Log System Variables

• Replication and Binary Log Status Variables

• Thread Pool System and Status Variables

Plugin Specific Variables

• Audit Plugin Options and System Variables

• Audit Plugin Status Variables

• Encryption Plugin System Variables (for instance, File Key Management)

Index / Master List

• Full list of MariaDB options, system and status variables &#xNAN;Note: This page acts as a directory that links back to the detailed pages above.

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

• --lock-tables (or -x): Locks all tables across all databases before starting the backup to ensure data consistency. The lock is released once the dump is complete for each table. For transactional tables like InnoDB, using --single-transaction is often preferred as it provides a consistent snapshot without prolonged locking of all tables.

• --all-databases (or -A): Specifies that all databases should be dumped.

• > /data/backup/dbs_alldatabases.sql: Redirects the output (the SQL statements) to the specified file. Ensure the path exists and the user running the command has write permissions.

Commonly Used Option for Efficiency:

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

Example with long options and password in script:

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 might be required. A user with global SELECT, LOCK TABLES, SHOW VIEW, EVENT, and TRIGGER privileges is often used for backups.

• Consistency with InnoDB: For databases primarily using InnoDB tables, consider using the --single-transaction option instead of --lock-tables. This option starts a transaction before dumping and reads data from a consistent snapshot without locking the tables for extended periods, allowing concurrent reads and writes.Bash

• Practice Makes Perfect: mariadb-dump is powerful but can have many options. Practice using it on a test database or server to become comfortable with its usage and to verify that your backup strategy works as expected.

• Test Your Backups: Regularly test your backup files by restoring them to a non-production environment to ensure they are valid and can be used for recovery.

• Restoration: To learn how to restore data from these dump files, see the "Data Restoration Guide".

• Security: Store backup files in a secure location. If passwords are included in scripts, ensure the script files have restricted permissions.

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

This guide introduces SQL Views in MariaDB, virtual tables based on the result-set of a stored query. Learn how views simplify complex queries, enhance data security by restricting access, and provide an abstraction layer over your database tables through practical examples.

Prerequisites

• A basic understanding of SQL, particularly JOIN operations. (You may want to refer to guides like "Basic Joins Guide" or "More Advanced Joins" if available.)

• Access to a MariaDB database.

• Privileges to CREATE TABLE and CREATE VIEW.

Setup: Example Employee Database

First, we'll create and populate two tables, Employees and Hours, to use in our examples. If you have already completed a tutorial using this database structure (e.g., from a "More Advanced Joins" guide), you might be able to skip this setup.

Employees Table:

Hours Table:

Building a Complex Query (Example: Employee Tardiness)

Let's say Human Resources needs a report on employees who are late (clock in after 7:00:59 AM) and do not make up the time at the end of their shift (work less than 10 hours and 1 minute).

Initial Query (Helmholtz's Lateness):

This query finds instances where Helmholtz was late within a specific week:

Output:

Refined Query (Policy Violators):

This query identifies all employees who were late and whose shift duration was less than 10 hours and 1 minute (601 minutes).

Output of Refined Query (example structure):

Creating and Using a View

The refined query is becoming complex. Storing this query logic in application code makes it harder to manage and means changes to table structures require application code changes. Views can simplify this.

A view is a virtual table based on the result-set of a stored query.

Creating the Employee_Tardiness View:

We use the refined query to create a view. SQL SECURITY INVOKER means the view runs with the permissions of the user querying it.

Querying the View:

Now, retrieving the tardiness data is much simpler:

This will produce the same results as the complex "Refined Query" above.

You can also apply further conditions when querying the view:

Output (example structure, showing those at least 5 minutes short):

Other Benefits and Uses of Views

• Simplifying Complex Queries: As demonstrated, views hide complex joins and calculations.

• Restricting Data Access (Column-Level Security): Views can expose only a subset of columns from underlying tables, preventing users or applications from seeing sensitive information (e.g., Home_Address, Home_Phone were not included in our Employee_Tardiness view).

• Implementing Row-Level Security: A view can include a WHERE clause that filters rows based on the user querying it or other criteria, effectively providing row-level access control. For updatable views, defining them with WITH CHECK OPTION (or the similar effect of a CASCADE clause mentioned in original text, usually WITH CASCADED CHECK OPTION) can ensure that INSERTs or UPDATEs through the view adhere to the view's WHERE clause conditions.

• Pre-emptive Optimization: Complex, frequently used queries can be defined as views with optimal join strategies and indexing considerations. Other users or applications query the already optimized view, reducing the risk of running inefficient ad-hoc queries.

• Abstracting Table Structures: Views provide a consistent interface to applications even if the underlying table structures change (e.g., tables are normalized, split, or merged). The view definition can be updated to map to the new structure, while applications continue to query the unchanged view.

Summary of View Advantages

Views offer a powerful way to:

• Simplify data access: Make complex queries easier to write and understand.

• Abstract database logic: Separate application code from the complexities of the database schema.

• Enhance security: Control access to specific rows and columns.

• Improve maintainability: Changes to underlying tables can often be managed by updating the view definition without altering application queries.

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

Introduction

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

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

Background to Relational Database Applications

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

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

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

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

Logging into MariaDB

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

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

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

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

The Basics of a Database

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

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

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

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

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

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

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

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

Inserting Data

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

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

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

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

Modifying Data

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

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

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

See Also

• MariaDB Basics

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

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 TEXT data type, which means that it's a variable width column and it can hold up to 65535 bytes of data for each row. There are several other data types that may be used for columns, but this gives you a good sampling.

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

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

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

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 INSERT 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 AUTO_INCREMENT option. MariaDB will automatically assign an identification number. You can manually assign one, especially if you want to start the count at a higher number than 1 (e.g., 1000). Since we are not providing data for all of the columns in the table, we have to list the columns for which we are giving data and in the order that the data is given in the set following the VALUES keyword. This means that we could give the data in a different order.

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

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 SELECT statement. There are many options available with the SELECT statement, but you can start simply. As an example, let's retrieve a list of book titles from the books table:

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

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

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

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

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

Changing & Deleting Data

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

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

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

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}

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 reserved words. 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 reserved word list.

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

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

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

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

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

Version specific syntax

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

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 Basic_Debugging on 2012-10-05.}

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

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

Creating a Table

See CREATE TABLE for more.

Inserting Records

See INSERT for more.

Using AUTO_INCREMENT

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

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

See AUTO_INCREMENT for more.

Querying from two tables on a common value

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

Finding the Maximum Value

See the MAX() function for more, as well as Finding the maximum value and grouping the results below for a more practical example.

Finding the Minimum Value

See the MIN() function for more.

Finding the Average Value

See the AVG() function for more.

Finding the Maximum Value and Grouping the Results

See the MAX() function for more.

Ordering Results

See ORDER BY for more.

Finding the Row with the Minimum of a Particular Column

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

Finding Rows with the Maximum Value of a Column by Group

This example returns the best test results of each student:

Calculating Age

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

See TIMESTAMPDIFF() for more.

Using User-defined Variables

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

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

See User-defined Variables for more.

View Tables in Order of Size

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

Removing Duplicates

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

CC BY-SA / Gnu FDL

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

A less used concatenating function is CONCAT_WS(). It will put together columns with a separator between each. This can be useful when making data available for other programs. For instance, suppose we have a program that will import data, but it requires the fields to be separated by vertical bars. We could just export the data, or we could use a SELECT statement like the one that follows in conjunction with an interface written with an API language like Perl:

The first element above is the separator. The remaining elements are the columns to be strung together.

If we want to format a long number with commas every three digits and a period for the decimal point (e.g., 100,000.00), we can use the function FORMAT() like so:

In this statement, the CONCAT() will place a dollar sign in front of the numbers found in the col5 column, which are formatted with commas by FORMAT(). The 2 within the FORMAT() stipulates two decimal places.

Occasionally, one will want to convert the text from a column to either all upper-case letters or all lower-case letters. In the example that follows, the output of the first column is converted to upper-case and the second to lower-case:

When displaying data in forms, it's sometimes useful to pad the data displayed with zeros or dots or some other filler. This can be necessary when dealing with VARCHAR columns where the width varies to help the user to see the column limits. There are two functions that may be used for padding: LPAD() and RPAD().

In this SQL statement, dots are added to the right end of each part number. So a part number of "H200" will display as "H200....", but without the quotes. Each part's description will have under-scores preceding it. A part with a description of "brass hinge" will display as "brass hinge".

If a column is a CHAR data-type, a fixed width column, then it may be necessary to trim any leading or trailing spaces from displays. There are a few functions to accomplish this task. The LTRIM() function will eliminate any leading spaces to the left. So "H200" becomes "H200". For columns with trailing spaces, spaces on the right, RTRIM() will work: "H500" becomes "H500". A more versatile trimming function, though, is TRIM(). With it one can trim left, right or both. Below are a few examples:

In the first TRIM() clause, the padding component is specified; the leading dots are to be trimmed from the output of col1. The trailing spaces are trimmed off of col2—space is the default. Both leading and trailing under-scores are trimmed from col3 above. Unless specified, BOTH is the default. So leading and trailing spaces are trimmed from col4 in the statement here.

Extracting

When there is a need to extract specific elements from a column, MariaDB has a few functions that can help. Suppose a column in the table contacts contains the telephone numbers of sales contacts, including the area-codes, but without any dashes or parentheses. The area-code of each could be extracted for sorting with the LEFT() and the telephone number with the RIGHT() function.

In the LEFT() function above, the column telephone is given along with the number of characters to extract, starting from the first character on the left in the column. The RIGHT() function is similar, but it starts from the last character on the right, counting left to capture, in this statement, the last seven characters. In the SQL statement above, area_code is reused to order the results set. To reformat the telephone number, it are necessary to use the SUBSTRING() function.

In this SQL statement, the CONCAT() function is employed to assemble some characters and extracted data to produce a common display for telephone numbers (e.g., (504) 555-1234). The first element of the CONCAT() is an opening parenthesis. Next, a LEFT() is used to get the first three characters of telephone, the area-code. After that a closing parenthesis, along with a space is added to the output. The next element uses the SUBSTRING() function to extract the telephone number's prefix, starting at the fourth position, for a total of three characters. Then a dash is inserted into the display. Finally, the function MID() extracts the remainder of the telephone number, starting at the seventh position. The functions MID() and SUBSTRING() are interchangeable and their syntax are the same. By default, for both functions, if the number of characters to capture isn't specified, then it's assumed that the remaining ones are to be extracted.

Manipulating

There are a few functions in MariaDB that can help in manipulating text. One such function is REPLACE(). With it every occurrence of a search parameter in a string can be replaced. For example, suppose we wanted to replace the title Mrs. with Ms. in a column containing the person's title, but only in the output. The following SQL would do the trick:

We're using the ever handy CONCAT() function to put together the contact's name with spaces. The REPLACE() function extracts each title and replaces Mrs. with Ms., where applicable. Otherwise, for all other titles, it displays them unchanged.

If we want to insert or replace certain text from a column (but not all of its contents), we could use the INSERT() function in conjunction with the LOCATE() function. For example, suppose another contacts table has the contact's title and full name in one column. To change the occurrences of Mrs. to Ms., we could not use REPLACE() since the title is embedded in this example. Instead, we would do the following:

The first element of the INSERT() function is the column. The second element which contains the LOCATE() is the position in the string that text is to be inserted. The third element is optional; it states the number of characters to overwrite. In this case, Mrs. which is four characters is overwritten with Ms. (the final element), which is only three. Incidentally, if 0 is specified, then nothing is overwritten, text is inserted only. As for the LOCATE() function, the first element is the column and the second the search text. It returns the position within the column where the text is found. If it's not found, then 0 is returned. A value of 0 for the position in the INSERT() function negates it and returns the value of name unchanged.

On the odd chance that there is a need to reverse the content of a column, there's the REVERSE() function. You would just place the column name within the function. Another minor function is the REPEAT() function. With it a string may be repeated in the display:

The first component of the function above is the string or column to be repeated. The second component states the number of times it's to be repeated.

Expression Aids

The function CHAR_LENGTH() is used to determine the number of characters in a string. This could be useful in a situation where a column contains different types of information of specific lengths. For instance, suppose a column in a table for a college contains identification numbers for students, faculty, and staff. If student identification numbers have eight characters while others have less, the following will count the number of student records:

The COUNT() function above counts the number of rows that meet the condition of the WHERE clause.

In a SELECT statement, an ORDER BY clause can be used to sort a results set by a specific column. However, if the column contains IP addresses, a simple sort may not produce the desired results:

In the limited results above, the IP address 10.0.2.1 should be second. This happens because the column is being sorted lexically and not numerically. The function INET_ATON() will solve this sorting problem.

Basically, the INET_ATON() function will convert IP addresses to regular numbers for numeric sorting. For instance, if we were to use the function in the list of columns in a SELECT statement, instead of the WHERE clause, the address 10.0.1.1 would return 167772417, 10.0.11.1 will return 167774977, and 10.0.2.1 the number 167772673. As a complement to INET_ATON(), the function INET_NTOA() will translate these numbers back to their original IP addresses.

MariaDB is fairly case insensitive, which usually is fine. However, to be able to check by case, the STRCMP() function can be used. It converts the column examined to a string and makes a comparison to the search parameter.

If there is an exact match, the function STRCMP() returns 0. So if col3 here contains "Text", it won't match. Incidentally, if col3 alphabetically is before the string to which it's compared, a -1 are returned. If it's after it, a 1 is returned.

When you have list of items in one string, the SUBSTRING_INDEX() can be used to pull out a sub-string of data. As an example, suppose we have a column which has five elements, but we want to retrieve just the first two elements. This SQL statement will return them:

The first component in the function above is the column or string to be picked apart. The second component is the delimiter. The third is the number of elements to return, counting from the left. If we want to grab the last two elements, we would use a negative two to instruct MariaDB to count from the right end.

Conclusion

There are more string functions available in MariaDB. A few of the functions mentioned here have aliases or close alternatives. There are also functions for converting between ASCII, binary, hexi-decimal, and octal strings. And there are also string functions related to text encryption and decryption that were not mentioned. However, this article has given you a good collection of common string functions that will assist you in building more powerful and accurate SQL statements.

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

There are several ways to add and to change data in MariaDB. There are a few SQL statements that you can use, each with a few options. Additionally, there are twists that you can do by mixing SQL statements together with various clauses. In this article, we will explore the ways in which data can be added and changed in MariaDB.

Adding Data

To add data to a table in MariaDB, you will need to use the INSERT statement. Its basic, minimal syntax is the command INSERT followed by the table name and then the keyword VALUES with a comma separated list of values contained in parentheses:

In this example, text is added to a table called table1, which contains only three columns—the same number of values that we're inserting. The number of columns must match. If you don't want to insert data into all of the columns of a table, though, you could name the columns desired:

Notice that the keyword INTO was added here. This is optional and has no effect on MariaDB. It's only a matter of grammatical preference. In this example we not only name the columns, but we list them in a different order. This is acceptable to MariaDB. Just be sure to list the values in the same order. If you're going to insert data into a table and want to specify all of the values except one (say the key column since it's an auto-incremented one), then you could just give a value of DEFAULT to keep from having to list the columns. Incidentally, you can give the column names even if you're naming all of them. It's just unnecessary unless you're going to reorder them as we did in this last example.

When you have many rows of data to insert into the same table, it can be more efficient to insert all of the rows in one SQL statement. Multiple row insertions can be done like so:

Notice that the keyword VALUES is used only once and each row is contained in its own set of parentheses and each set is separated by commas. We've added an intentional mistake to this example: We are attempting to insert three rows of data into table2 for which the first column happens to be a UNIQUE key field. The third row entered here has the same identification number for the key column as the second row. This would normally result in an error and none of the three rows would be inserted. However, since the statement has an IGNORE flag, duplicates are ignored and not inserted, but the other rows will still be inserted. So, the first and second rows above are inserted and the third one won't.

Priority

An INSERT statement takes priority over read statements (i.e., SELECT statements). An INSERT will lock the table and force other clients to wait until it's finished. On a busy MariaDB server that has many simultaneous requests for data, this could cause users to experience delays when you run a script that performs a series of INSERT statements. If you don't want user requests to be put on hold and you can wait to insert the data, you could use the LOW_PRIORITY flag:

The LOW_PRIORITY flag will put the INSERT statement in queue, waiting for all current and pending requests to be completed before it's performed. If new requests are made while a low priority statement is waiting, then they are put ahead of it in the queue. MariaDB does not begin to execute a low priority statement until there are no other requests waiting. Once the transaction begins, though, the table is locked and any other requests for data from the table that come in after it starts must wait until it's completed. Because it locks the table, low priority statements will prevent simultaneous insertions from other clients even if you're dealing with a MyISAM table. Incidentally, notice that the LOW_PRIORITY flag comes before the INTO.

One potential inconvenience with an INSERT LOW_PRIORITY statement is that the client are tied up waiting for the statement to be completed successfully. So if you're inserting data into a busy server with a low priority setting using the mariadb client, your client could be locked up for minutes, maybe hours depending on how busy your server is at the time. As an alternative either to making other clients with read requests wait or to having your client wait, you can use the DELAYED flag instead of the LOW_PRIORITY flag:

MariaDB will take the request as a low priority one and put it on its list of tasks to perform when it has a break. However, it will immediately release the client so that the client can go on to enter other SQL statements or even exit. Another advantage of this method is that multiple INSERT DELAYED requests are batched together for block insertion when there is a gap, making the process potentially faster than INSERT LOW_PRIORITY. The flaw in this choice, however, is that the client is never told if a delayed insertion is successfully made or not. The client is informed of error messages when the statement is entered—the statement has to be valid before it are queued—but it's not told of problems that occur after it's accepted. This brings up another flaw: delayed insertions are stored in the server's memory. So if the MariaDB daemon (mariadbd) dies or is manually killed, then the transactions are lost and the client is not notified of the failure. So DELAYED is not always a good alternative.

Contingent Additions

As an added twist to INSERT, you can combine it with a SELECT statement. Suppose that you have a table called employees which contains employee information for your company. Suppose further that you have a column to indicate whether an employee is on the company's softball team. However, you one day decide to create a separate database and table for the softball team's data that someone else will administer. To get the database ready for the new administrator, you have to copy some data for team members to the new table. Here's one way you can accomplish this task:

In this SQL statement the columns in which data is to be inserted into are listed, then the complete SELECT statement follows with the appropriate WHERE clause to determine if an employee is on the softball team. Since we're executing this statement from the new database and since the table employees is in a separate database called company, we have to specify it as you see here. By the way, INSERT...SELECT statements cannot be performed on the same table.

Replacement Data

When you're adding massive amounts of data to a table that has a key field, as mentioned earlier, you can use the IGNORE flag to prevent duplicates from being inserted, but still allow unique rows to be entered. However, there may be times when you actually want to replace the rows with the same key fields with the new ones. In such a situation, instead of using INSERT you can use a REPLACE statement:

Notice that the syntax is the same as an INSERT statement. The flags all have the same effect, as well. Also, multiple rows may be inserted, but there's no need for the IGNORE flag since duplicates won't happen—the originals are just overwritten. Actually, when a row is replaced, it's first deleted completely and the new row is then inserted. Any columns without values in the new row are given the default values for the columns. None of the values of the old row are kept. Incidentally, REPLACE will also allow you to combine it with a SELECT statement as we saw with the INSERT statement earlier.

Updating Data

If you want to change the data contained in existing records, but only for certain columns, then you would need to use an UPDATE statement. The syntax for UPDATE is a little bit different from the syntax shown before for INSERT and REPLACE statements:

In the SQL statement here, we are changing the value of the two columns named individually using the SET clause. Incidentally, the SET clause optionally can be used in INSERT and REPLACE statements, but it eliminates the multiple row option. In the statement above, we're also using a WHERE clause to determine which records are changed: only rows with an id that has a value less than 100 are updated. Notice that the LOW_PRIORITY flag can be used with this statement, too. The IGNORE flag can be used, as well.

A useful feature of the UPDATE statement is that it allows the use of the current value of a column to update the same column. For instance, suppose you want to add one day to the value of a date column where the date is a Sunday. You could do the following:

For rows where the day of the week is Sunday, the DATE_ADD() function will take the value of col_date before it's updated and add one day to it. MariaDB will then take this sum and set col_date to it.

There are a couple more twists that you can now do with the UPDATE statement: if you want to update the rows in a specific order, you can add an ORDER BY clause. You can also limit the number of rows that are updated with a LIMIT clause. Below is an example of both of these clauses:

The ordering can be descending as indicated here by the DESC flag, or ascending with either the ASC flag or by just leaving it out, as ascending is the default. The LIMIT clause, of course, limits the number of rows affected to ten here.

If you want to refer to multiple tables in one UPDATE statement, you can do so like this:

Here we see a join between the two tables named. In table3, the value of col1 is set to the value of the same column in table4 where the values of id from each match. We're not updating both tables here; we're just accessing both. We must specify the table name for each column to prevent an ambiguity error. Incidentally, ORDER BY and LIMIT clauses aren't allowed with multiple table updates.

There's another combination that you can do with the INSERT statement that we didn't mention earlier. It involves the UPDATE statement. When inserting multiple rows of data, if you want to note which rows had potentially duplicate entries and which ones are new, you could add a column called status and change it's value accordingly with a statement like this one:

Because of the IGNORE flag, errors will not be generated, duplicates won't be inserted or replaced, but the rest are added. Because of the ON DUPLICATE KEY, the column status of the original row are set to old when there are duplicate entry attempts. The rest are inserted and their status set to new.

Conclusion

As you can see from some of these SQL statements, MariaDB offers you quite a few ways to add and to change data. In addition to these methods, there are also some bulk methods of adding and changing data in a table. You could use the LOAD DATA INFILE statement and the mariadb-dump command-line utility. These methods are covered in another article on Importing Data into MariaDB.

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

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

About Time

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

Telling Time

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

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

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

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

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

How to get a Date

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

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

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

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

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

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

What is the Time?

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

Date & Time Combined

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

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

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

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

Fine Time Pieces

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

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

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

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

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

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

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

Clean up Time

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

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

Time to End

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

CC BY-SA / Gnu FDL

The simplest way to retrieve data from MariaDB is to use the SELECT statement. Since the SELECT statement is an essential SQL statement, it has many options available with it. It's not necessary to know or use them all—you could execute very basic SELECT statements if that satisfies your needs. However, as you use MariaDB more, you may need more powerful SELECT statements. In this article we will go through the basics of SELECT and will progress to more involved SELECT statements;we will move from the beginner level to the more intermediate and hopefully you will find some benefit from this article regardless of your skill level. For absolute beginners who are just starting with MariaDB, you may want to read the MariaDB Basics article.

Creating and Populating the Tables

In order to follow the examples that follow, you can create and pre-populate the tables, as follows:

If you are unclear what these statements do, review the MariaDB Primer and MariaDB Basics tutorials.

Basic Elements

The basic, minimal elements of the SELECT statement call for the keyword SELECT, of course, the columns to select or to retrieve, and the table from which to retrieve rows of data. Actually, for the columns to select, we can use the asterisk as a wildcard to select all columns in a particular table. Using a database from a fictitious bookstore, we might enter the following SQL statement to get a list of all columns and rows in a table containing information on books:

This will retrieve all of the data contained in the books table. As you can see, not all values have been populated. If we want to retrieve only certain columns, we would list them in place of the asterisk in a comma-separated list like so:

This narrows the width of the results set by retrieving only three columns, but it still retrieves all of the rows in the table. If the table contains thousands of rows of data, this may be more data than we want. If we want to limit the results to just a few books, say five, we would include what is known as a LIMIT clause:

This will give us the first five rows found in the table. If we want to get the next ten found, we would add a starting point parameter just before the number of rows to display, separated by a comma:

In this case, there are only three further records.

Selectivity and Order

The previous statements have narrowed the number of columns and rows retrieved, but they haven't been very selective. Suppose that we want only books written by a certain author, say Dostoevsky. Looking in the authors table we find that his author identification number is 2. Using a WHERE clause, we can retrieve a list of books from the database for this particular author like so:

I removed the author_id from the list of columns to select, but left the basic LIMIT clause in because we want to point out that the syntax is fairly strict on ordering of clauses and flags. You can't enter them in any order. You'll get an error in return.

The SQL statements we've looked at thus far will display the titles of books in the order in which they're found in the database. If we want to put the results in alphanumeric order based on the values of the title column, for instance, we would add an ORDER BY clause like this:

Notice that the ORDER BY clause goes after the WHERE clause and before the LIMIT clause. Not only will this statement display the rows in order by book title, but it will retrieve only the first five based on the ordering. That is to say, MariaDB will first retrieve all of the rows based on the WHERE clause, order the data based on the ORDER BY clause, and then display a limited number of rows based on the LIMIT clause. Hence the reason for the order of clauses. You may have noticed that we slipped in the ASC flag. It tells MariaDB to order the rows in ascending order for the column name it follows. It's not necessary, though, since ascending order is the default. However, if we want to display data in descending order, we would replace the flag with DESC. To order by more than one column, additional columns may be given in the ORDER BY clause in a comma separated list, each with the ASC or DESC flags if preferred.

Friendlier and More Complicated

So far we've been working with one table of data containing information on books for a fictitious bookstore. A database will usually have more than one table, of course. In this particular database, there's also one called authors in which the name and other information on authors is contained. To be able to select data from two tables in one SELECT statement, we will have to tell MariaDB that we want to join the tables and will need to provide a join point. This can be done with a JOIN clause as shown in the following SQL statement, with the results following it:

Our SELECT statement is getting hefty, but it's the same one to which we've been adding. Don't let the clutter fluster you. Looking for the new elements, let's focus on the JOIN clause first. There are a few possible ways to construct a join. This method works if both tables contain a column of the same name and value. Otherwise you'll have to redo the JOIN clause to look something like this:

This excerpt is based on the assumption that the key field in the authors table is not called author_id, but row_id instead. There's much more that can be said about joins, but that would make for a much longer article. If you want to learn more on joins, look at MariaDB's documentation page on JOIN syntax.

Looking again at the last full SQL statement above, you must have spotted the CONCAT() function that we added to the on-going example statement. This string function takes the values of the columns and strings given and pastes them together, to give one neat field in the results. We also employed the AS parameter to change the heading of the results set for the field to author. This is much tidier. Since we joined the books and the authors tables together, we were able to search for books based on the author's last name rather than having to look up the author ID first. This is a much friendlier method, albeit more complicated. Incidentally, we can have MariaDB check columns from both tables to narrow our search. We would just add column = value pairs, separated by commas in the WHERE clause. Notice that the string containing the author's name is wrapped in quotes—otherwise, the string would be considered a column name and we'd get an error.

The name Dostoevsky is sometimes spelled Dostoevskii, as well as a few other ways. If we're not sure how it's spelled in the authors table, we could use the LIKE operator instead of the equal sign, along with a wildcard. If we think the author's name is probably spelled either of the two ways mentioned, we could enter something like this:

This will match any author last name starting with Dostoevsk. Notice that the wildcard here is not an asterisk, but a percent-sign.

Some Flags

There are many flags or parameters that can be used in a SELECT statement. To list and explain all of them with examples would make this a very lengthy article. The reality is that most people never use some of them anyway. So, let's take a look at a few that you may find useful as you get more involved with MariaDB or if you work with large tables on very active servers.

The first flag that may be given, it goes immediately after the SELECT keyword, is ALL. By default, all rows that meet the requirements of the various clauses given are selected, so this isn't necessary. If instead we would only want the first occurrence of a particular criteria to be displayed, we could add the DISTINCT option. For instance, for authors like Dostoevsky there are several printings of a particular title. In the results shown earlier you may have noticed that there were two copies of Crime & Punishment listed, however they have different ISBN numbers and different publishers. Suppose that for our search we only want one row displayed for each title. We could do that like so:

We've thinned out the ongoing SQL statement a bit for clarity. This statement will result in only one row displayed for Crime & Punishment and it are the first one found.

If we're retrieving data from an extremely busy database, by default any other SQL statements entered simultaneously which are changing or updating data are executed before a SELECT statement. SELECT statements are considered to be of lower priority. However, if we would like a particular SELECT statement to be given a higher priority, we can add the keyword HIGH_PRIORITY. Modifying the previous SQL statement for this factor, we would enter it like this:

You may have noticed in the one example earlier in which the results are shown, that there's a status line displayed that specifies the number of rows in the results set. This is less than the number of rows that were found in the database that met the statement's criteria. It's less because we used a LIMIT clause. If we add the SQL_CALC_FOUND_ROWS flag just before the column list, MariaDB will calculate the number of columns found even if there is a LIMIT clause.

To retrieve this information, though, we will have to use the FOUND_ROWS() function like so:

This value is temporary and are lost if the connection is terminated. It cannot be retrieved by any other client session. It relates only to the current session and the value for the variable when it was last calculated.

Conclusion

There are several more parameters and possibilities for the SELECT statement that we had to skip to keep this article a reasonable length. A popular one that we left out is the GROUP BY clause for calculating aggregate data for columns (e.g., an average). There are several flags for caching results and a clause for exporting a results set to a text file. If you would like to learn more about SELECT and all of the options available, look at the on-line documentation for SELECT statements.

_{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 MyISAM and InnoDB 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 mariadb-backup program is a physical online backup tool and a fork of Percona XtraBackup with added support for compression and data-at-rest encryption.

Storage Engines and Backup Types

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

MariaDB Server implements:

• Full backups, which contain all data in the database.

• Incremental backups, which contain modifications since the last backup.

• Partial backups, which contain a subset of the tables in the database.

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

Non-Blocking Backups

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

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

• MariaDB Backup includes optimizations to backup staging, including DDL statement tracking, which reduces lock-time during backups.

• MariaDB Backup in MariaDB Community Server 10.4 and later will block writes, log tables, and statistics.

• Older releases used FLUSH TABLES WITH READ LOCK, which closed open tables and only allowed tables to be reopened with a read lock during the duration of backups.

Understanding Recovery

Full backups produced using MariaDB Server are not initially point-in-time consistent, and an attempt to restore from a raw full backup will cause InnoDB to crash to protect the data. Incremental backups contain only the changes since the last backup and cannot be used standalone to perform a restore.

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

• Running --prepare on a full backup synchronizes the tablespaces, ensuring they are point-in-time consistent.

• Running --prepare on an incremental backup synchronizes the tablespaces and applies the updated data into the previous full backup.

• Running --prepare on data used for a partial restore requires the --export option to create the necessary .cfg files.

Restore Requires Empty Data Directory

For MariaDB Backup to safely restore data from full and incremental backups, the data directory must be empty. When MariaDB Backup restores from a backup using --copy-back or --move-back, it copies or moves the backup files into the MariaDB Server data directory.

Creating the Backup User

When MariaDB Backup performs a backup operation, it connects to the running MariaDB Server to manage locks and backup staging that prevent the Server from writing to a file while being read. It is recommended that a dedicated user be created and authorized to perform backups:

mariadb-dump

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, stored procedures, views, and events are not, and need extra parameters to be recreated explicitly (for example, --routines and --events). Procedures and functions are however also part of the system tables (for example mysql.proc).

InnoDB Logical Backups

InnoDB uses the buffer pool, 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 innodb_old_blocks_time 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 innodb_old_blocks_pct 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 innodb_buffer_pool_dump_now system variable can be set to ON. To restore it, the innodb_buffer_pool_load_now system variable can be set to ON.

mariadb-dump Examples

Backing up a single database

Restoring or loading the database

See the mariadb-dump page for detailed syntax and examples.

mariadb-hotcopy

mariadb-hotcopy performs a physical backup, and works only for backing up MyISAM and ARCHIVE 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. mariadb-backup is the recommended backup method to use instead of Percona XtraBackup. See Percona XtraBackup Overview: Compatibility with MariaDB for more information.

Percona XtraBackup is a tool for performing fast, hot backups. It was designed specifically for XtraDB/InnoDB databases, but can be used with any storage engine (although not with encryption and compression). 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 mariadb client, execute FLUSH TABLES WITH READ LOCK. The client must remain open.

• From a shell, execute mount vxfs snapshot

• The client can execute UNLOCK TABLES.

• Copy the snapshot files.

• From a shell, unmount the snapshot with umount snapshot.

LVM

Widely-used physical backup method, using a Perl script as a wrapper.

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 configuration and management of full and partial backups 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

• Streaming MariaDB backups in the cloud • blog post • 2015 • 5 minutes read

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

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}

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

See Also

• Point-In-Time Recovery (InnoDB log archiving) — an alternative PITR procedure that replays archived InnoDB write-ahead logs instead of binary logs. Available from MariaDB 13.0.

This method is to solve a flaw with mariadb-backup; it cannot do single database restores from a full backup easily. There is a blog post that details a way to do this, but it's a manual process which is fine for a few tables but if you have hundreds or even thousands of tables then it would be impossible to do quickly.

We can't just move the data files to the datadir as the tables are not registered in the engines, so the database will error. Currently, the only effective method is to a do full restore in a test database and then dump the database that requires restoring or running a partial backup.

This has only been tested with InnoDB. Also, if you have stored procedures or triggers then these will need to be deleted and recreated.

Some of the issues that this method overcomes:

• Tables not registered in the InnoDB engine so will error when you try to select from a table if you move the data files into the datadir

• Tables with foreign keys need to be created without keys, otherwise it will error when you discard the tablespace

Single Node

Below is the process to perform a single database restore.

Firstly, we will need the table structure from a mariadb-dump backup with the --no-data option. I recommend this is done at least once per day or every six hours via a cronjob. As it is just the structure, it are very fast.

Using SED to return only the table structure we require, then use vim or another text editor to make sure nothing is left.

Prepare the backup with any incremental-backup-and-restores that you have, and then run the following on the full backup folder using the --export option to generate files with .cfg extensions which InnoDB will look for.

Once we have done these steps, we can then import the table structure. If you have used the --all-databases option, then you will need to either use SED or open it in a text editor and export out tables that you require. You will also need to log in to the database and create the database if the dump file doesn't. Run the following command below:

Once the structure is in the database, we have now registered the tables to the engine. Next, we will run the following statements in the information_schema database, to export statements to import/discard table spaces and drop and create foreign keys which we will use later. (edit the CONSTRAINT_SCHEMA and TABLE_SCHEMA WHERE clause to the database you are restoring. Also, add the following lines after your SELECT and before the FROM to have MariaDB export the files to the OS)

The following are the statements that we will need later.

Once we have run those statements, and they have been exported to a Linux directory or copied from a GUI interface.

Run the ALTER DROP KEYS statements in the database.

Once completed, run the DROP TABLE SPACE statements in the database:

Exit out the database and change into the directory of the full backup location. Run the following commands to copy all the .cfg and .ibd files to the datadir such as /var/lib/mysql/testdatabase (change the datadir location if needed). Learn more about files that mariadb-backup generates with files-created-by-mariadb-backup.

After moving the files, it is very important that MySQL is the owner of the files, otherwise it won't have access to them and will error when we import the tablespaces.

Run the import table spaces statements in the database.

Run the add key statements in the database

We have successfully restored a single database. To test that this has worked, we can do a basic check on some tables.

Replica nodes

If you have a primary-replica set up, it would be best to follow the sets above for the primary node and then either take a full mariadb-dump or take a new full mariadb-backup and restore this to the replica. You can find more information about restoring a replica with mariadb-backup in Setting up a Replica with mariadb-backup

After running the below command, copy to the replica and use the LESS linux command to grab the change master statement. Remember to follow this process: Stop replica > restore data > run CHANGE MASTER statement > start replica again.

Please follow Setting up a Replica with mariadb-backup on restoring a replica with mariadb-backup:

Galera Cluster

For this process to work with Galera cluster, we first need to understand that some statements are not replicated across Galera nodes. One of which is the DISCARD and IMPORT for ALTER TABLES statements, and these statements will need to be ran on all nodes. We also need to run the OS level steps on each server as seen below.

Run the ALTER DROP KEYS statements on ONE NODE as these are replicated.

Once completed, run the DROP TABLE SPACE statements on EVERY NODE, as these are not replicated.

Exit out the database and change into the directory of the full backup location. Run the following commands to copy all the .cfg and .ibd files to the datadir such as /var/lib/mysql/testdatabase (change the datadir location if needed). Learn more about files that mariadb-backup generates with files-created-by-mariadb-backup. This step needs to be done on all nodes. You will need to copy the backup files to each node, we can use the same backup on all nodes.

After moving the files, it is very important that MySQL is the owner of the files, otherwise it won't have access to them and will error when we import the tablespaces.

Run the import table spaces statements on EVERY NODE.

Run the add key statements on ONE NODE.

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

Restoring the Backup

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

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

Restoring Individual Non-Partitioned Tables

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 Importing Transportable Tablespaces for Partitioned Tables process.

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

Files Included in Backup

mariadb-backup backs up the files listed below.

InnoDB Data Files

mariadb-backup backs up the following InnoDB data files:

• InnoDB system tablespace

• InnoDB file-per-table tablespaces

MyRocks Data Files

Other Data Files

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

• frm

• isl

• MYD

• MYI

• MAD

• MAI

• MRG

• TRG

• TRN

• ARM

• ARZ

• CSM

• CSV

• opt

• par

Files Excluded From Backup

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

• InnoDB Temporary Tablespaces

• Binary logs

• Relay logs

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

The BACKUP STAGE statements make it possible to make an efficient external backup tool. How mariadb-backup uses these statements 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.

BACKUP STAGE in MariaDB Community Server

The BACKUP STAGE statements are supported. However, the version of mariadb-backup that is bundled with MariaDB Community Server does not yet use the BACKUP STAGE statement in the most efficient way. mariadb-backup simply executes the following BACKUP STAGE statement to lock the database:

When the backup is complete, it executes the following BACKUP STAGE statement to unlock the database:

Tasks Performed Prior to BACKUP STAGE in MariaDB Community Server

• Copy some transactional tables.

  • InnoDB (i.e. ibdataN and file extensions .ibd and .isl)

• Copy the tail of some transaction logs.

  • The tail of the InnoDB redo log (i.e. ib_logfileN files) are copied for InnoDB tables.

BACKUP STAGE START in MariaDB Community Server

mariadb-backup from MariaDB Community Server does not perform any tasks in the START stage.

BACKUP STAGE FLUSH in MariaDB Community Server

mariadb-backup from MariaDB Community Server does not currently perform any tasks in the FLUSH stage.

BACKUP STAGE BLOCK_DDL in MariaDB Community Server

mariadb-backup from MariaDB Community Server does not currently perform any tasks in the BLOCK_DDL stage.

BACKUP STAGE BLOCK_COMMIT in MariaDB Community Server

mariadb-backup from MariaDB Community Server performs the following tasks in the BLOCK_COMMIT stage:

• Copy other files.

  • i.e. file extensions .frm, .isl, .TRG, .TRN, .opt, .par

• Copy some transactional tables.

  • Aria (i.e. aria_log_control and file extensions .MAD and .MAI)

• Copy the non-transactional tables.

  • MyISAM (i.e. file extensions .MYD and .MYI)

  • MERGE (i.e. file extensions .MRG)

  • ARCHIVE (i.e. file extensions .ARM and .ARZ)

  • CSV (i.e. file extensions .CSM and .CSV)

• Create a MyRocks checkpoint using the rocksdb_create_checkpoint system variable.

• Copy the tail of some transaction logs.

  • The tail of the InnoDB redo log (i.e. ib_logfileN files) are copied for InnoDB tables.

• Save the binary log position to xtrabackup_binlog_info.

• Save the Galera Cluster state information to xtrabackup_galera_info.

BACKUP STAGE END in MariaDB Community Server

mariadb-backup from MariaDB Community Server performs the following tasks in the END stage:

• Copy the MyRocks checkpoint into the backup.

BACKUP STAGE in MariaDB Enterprise Server

The following sections describe how the MariaDB Backup version of mariadb-backup that is bundled with MariaDB Enterprise Server uses each BACKUP STAGE statement in an efficient way.

BACKUP STAGE START in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the START stage:

• Copy all transactional tables.

  • InnoDB (i.e. ibdataN and file extensions .ibd and .isl)

  • Aria (i.e. aria_log_control and file extensions .MAD and .MAI)

• Copy the tail of all transaction logs.

  • The tail of the InnoDB redo log (i.e. ib_logfileN files) are copied for InnoDB tables.

  • The tail of the Aria redo log (i.e. aria_log.N files) are copied for Aria tables.

BACKUP STAGE FLUSH in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the FLUSH stage:

• Copy all non-transactional tables that are not in use. This list of used tables is found with SHOW OPEN TABLES.

  • MyISAM (i.e. file extensions .MYD and .MYI)

  • MERGE (i.e. file extensions .MRG)

  • ARCHIVE (i.e. file extensions .ARM and .ARZ)

  • CSV (i.e. file extensions .CSM and .CSV)

• Copy the tail of all transaction logs.

  • The tail of the InnoDB redo log (i.e. ib_logfileN files) are copied for InnoDB tables.

  • The tail of the Aria redo log (i.e. aria_log.N files) are copied for Aria tables.

BACKUP STAGE BLOCK_DDL in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the BLOCK_DDL stage:

• Copy other files.

  • i.e. file extensions .frm, .isl, .TRG, .TRN, .opt, .par

• Copy the non-transactional tables that were in use during BACKUP STAGE FLUSH.

  • MyISAM (i.e. file extensions .MYD and .MYI)

  • MERGE (i.e. file extensions .MRG)

  • ARCHIVE (i.e. file extensions .ARM and .ARZ)

  • CSV (i.e. file extensions .CSM and .CSV)

• Check ddl.log for DDL executed before the BLOCK DDL stage.

  • The file names of newly created tables can be read from ddl.log.

  • The file names of dropped tables can also be read from ddl.log.

  • The file names of renamed tables can also be read from ddl.log, so the files can be renamed instead of re-copying them.

• Copy changes to system log tables.

  • mysql.general_log

  • mysql.slow_log

  • This is easy as these are append only.

• Copy the tail of all transaction logs.

  • The tail of the InnoDB redo log (i.e. ib_logfileN files) are copied for InnoDB tables.

  • The tail of the Aria redo log (i.e. aria_log.N files) are copied for Aria tables.

BACKUP STAGE BLOCK_COMMIT in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the BLOCK_COMMIT stage:

• Create a MyRocks checkpoint using the rocksdb_create_checkpoint system variable.

• Copy changes to system log tables.

  • mysql.general_log

  • mysql.slow_log

  • This is easy as these are append only.

• Copy changes to statistics tables.

  • mysql.table_stats

  • mysql.column_stats

  • mysql.index_stats

• Copy the tail of all transaction logs.

  • The tail of the InnoDB redo log (i.e. ib_logfileN files) are copied for InnoDB tables.

  • The tail of the Aria redo log (i.e. aria_log.N files) are copied for Aria tables.

• Save the binary log position to xtrabackup_binlog_info.

• Save the Galera Cluster state information to xtrabackup_galera_info.

BACKUP STAGE END in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the END stage:

• Copy the MyRocks checkpoint into the backup.

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

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

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

Recovery Parameters

Two startup parameters define the range of the log replay:

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

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

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

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

Procedure

See Also

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

• innodb_log_archive, innodb_log_recovery_start, innodb_log_recovery_target.

• Point-In-Time Recovery (PITR, mariadb-backup) — the binary-log-based PITR procedure.

Despite a MariaDB developer's best planning, occasionally one needs to change the structure or other aspects of tables. This is not very difficult, but some developers are unfamiliar with the syntax for the functions used in MariaDB to accomplish this. And some changes can be very frustrating. In this article we'll explore the ways to alter tables in MariaDB and we'll give some precautions about related potential data problems.

Before Beginning

For the examples in this article, we will refer to a database called db1 containing a table called clients. The clients table is for keeping track of client names and addresses. To start off, we'll enter a DESCRIBE statement to see what the table looks like:

This is a very simple table that will hold very little information. However, it's sufficient for the examples here in which we will change several of its columns. Before doing any structural changes to a table in MariaDB, especially if it contains data, one should make a backup of the table to be changed. There are a few ways to do this, but some choices may not be permitted by your web hosting company. Even if your database is on your own server, though, the mariadb-dump utility is typically the best tool for making and restoring backups in MariaDB, and it's generally permitted by web hosting companies. To backup the clients table with mariadb-dump, we will enter the following from the command-line:

As you can see, the username and password are given on the first line. On the next line, the --add-locks option is used to lock the table before backing up and to unlock automatically it when the backup is finished. There are many other options in mariadb-dump that could be used, but for our purposes this one is all that's necessary. Incidentally, this statement can be entered in one line from the shell (i.e., not from the mariadb client), or it can be entered on multiple lines as shown here by using the back-slash (i.e., /) to let the shell know that more is to follow. On the third line above, the database name is given, followed by the table name. The redirect (i.e., >) tells the shell to send the results of the dump to a text file called clients.sql in the current directory. A directory path could be put in front of the file name to create the file elsewhere. If the table should need to be restored, the following can be run from the shell:

Notice that this line does not use the mariadb-dump utility. It uses the mariadb client from the outside, so to speak. When the dump file (clients.sql) is read into the database, it will delete the clients table and it's data in MariaDB before restoring the backup copy with its data. So be sure that users haven't added data in the interim. In the examples in this article, we are assuming that there isn't any data in the tables yet.

Basic Addition and More

In order to add a column to an existing MariaDB table, one would use the ALTER TABLE statement. To demonstrate, suppose that it has been decided that there should be a column for the client's account status (i.e., active or inactive). To make this change, the following is entered:

This will add the column status to the end with a fixed width of two characters (i.e., AC for active and IA for inactive). In looking over the table again, it's decided that another field for client apartment numbers or the like needs to be added. That data could be stored in the address column, but it would better for it to be in a separate column. An ALTER TABLE statement could be entered like above, but it will look tidier if the new column is located right after the address column. To do this, we'll use the AFTER option:

By the way, to add a column to the first position, you would replace the last line of the SQL statement above to read like this:

Before moving on, let's take a look at the table's structure so far:

Changing One's Mind

After looking over the above table display, it's decided that it might be better if the status column has the choices of 'AC' and 'IA' enumerated. To make this change, we'll enter the following SQL statement:

Notice that the column name status is specified twice. Although the column name isn't being changed, it still must be respecified. To change the column name (from status to active), while leaving the enumerated list the same, we specify the new column name in the second position:

Here we have the current column name and then the new column name, along with the data type specifications (i.e., ENUM), even though the result is only a name change. With the CHANGE clause everything must be stated, even items that are not to be changed.

In checking the table structure again, more changes are decided on: The column address is to be renamed to address1 and changed to forty characters wide. Also, the enumeration of active is to have 'yes' and 'no' choices. The problem with changing enumerations is that data can be clobbered in the change if one isn't careful. We've glossed over this possibility before because we are assuming that clients is empty. Let's take a look at how the modifications suggested could be made with the table containing data:

The first SQL statement above changes address and modifies active in preparation for the transition. Notice the use of a MODIFY clause. It works the same as CHANGE, but it is only used for changing data types and not column names. Therefore, the column name isn't respecified. Notice also that there is a comma after the CHANGE clause. You can string several CHANGE and MODIFY clauses together with comma separators. We've enumerated both the new choices and the old ones to be able to migrate the data. The two UPDATE statements are designed to adjust the data accordingly and the last ALTER TABLE statement is to remove the old enumerated choices for the status column.

In talking to the boss, we find out that the client_type column isn't going to be used. So we enter the following in MariaDB:

This deletes client_type and its data, but not the whole table, obviously. Nevertheless, it is a permanent and non-reversible action; there won't be a confirmation request when using the mariadb client. This is how it is with all MariaDB DROP statements and clauses. So be sure that you want to delete an element and its data before using a DROP. As mentioned earlier, be sure that you have a backup of your tables before doing any structured changes.

The Default

You may have noticed that the results of the DESCRIBE statements shown before have a heading called 'Default' and just about all of the fields have a default value of NULL. This means that there are no default values and a null value is allowed and are used if a value isn't specified when a row is created. To be able to specify a default value other than NULL, an ALTER TABLE statement can be entered with a SET clause. Suppose we're located in Louisiana and we want a default value of 'LA' for state since that's where our clients are usually located. We would enter the following to set the default:

Notice that the second line starts with ALTER and not CHANGE. If we change our mind about having a default value for state, we would enter the following to reset it back to NULL (or whatever the initial default value would be based on the data type):

This particular DROP doesn't delete data, by the way.

Indexes

One of the most irritating tasks in making changes to a table for newcomers is dealing with indexes. If they try to rename a column that is indexed by only using an ALTER TABLE statement like we used earlier, they will get a frustrating and confusing error message:

If they're typing this column change from memory, they will wear themselves out trying different deviations thinking that they remembered the syntax wrong. What most newcomers to MariaDB don't seem to realize is that the index is separate from the indexed column. To illustrate, let's take a look at the index for clients by using the SHOW INDEX statement:

The text above shows that behind the scenes there is an index associated with cust_id. The column cust_id is not the index. Incidentally, the G at the end of the SHOW INDEX statement is to display the results in portrait instead of landscape format. Before the name of an indexed column can be changed, the index related to it must be eliminated. The index is not automatically changed or deleted. Therefore, in the example above, MariaDB thinks that the developer is trying to create another primary key index. So, a DROP clause for the index must be entered first and then a CHANGE for the column name can be made along with the establishing of a new index:

The order of these clauses is necessary. The index must be dropped before the column can be renamed. The syntax here is for a PRIMARY KEY. There are other types of indexes, of course. To change a column that has an index type other than a PRIMARY KEY. Assuming for a moment that cust_id has a UNIQUE index, this is what we would enter to change its name:

Although the index type can be changed easily, MariaDB won't permit you to do so when there are duplicate rows of data and when going from an index that allows duplicates (e.g., INDEX) to one that doesn't (e.g., UNIQUE). If you actually do want to eliminate the duplicates, though, you can add the IGNORE flag to force the duplicates to be deleted:

In this example, we're not only changing the indexed column's name, but we're also changing the index type from INDEX to UNIQUE. And, again, the IGNORE flag tells MariaDB to ignore any records with duplicate values for cust_id.

Renaming & Shifting Tables

The previous sections covered how to make changes to columns in a table. Sometimes you may want to rename a table. To change the name of the clients table to client_addresses we enter this:

The RENAME TABLE statement will also allows a table to be moved to another database just by adding the receiving database's name in front of the new table name, separated by a dot. Of course, you can move a table without renaming it. To move the newly named client_addresses table to the database db2, we enter this:

Finally, with tables that contain data (excluding InnoDB tables), occasionally it's desirable to resort the data within the table. Although the ORDER BY clause in a SELECT statement can do this on the fly as needed, sometimes developers want to do this somewhat permanently to the data within the table based on a particular column or columns. It can be done by entering the following:

Notice that we're sorting by the city first and then by the client's name. Now when the developer enters a SELECT statement without an ORDER BY clause, the results are already ordered by the default of city and then name, at least until more data is added to the table.

This is not applicable to InnoDB tables, the default, which are ordered according to the clustered index, unless the primary key is defined on the specific columns.

Summation

Good planning is certainly important in developing a MariaDB database. However, as you can see, MariaDB is malleable enough that it can be reshaped without much trouble. Just be sure to make a backup before restructuring a table and be sure to check your work and the data when you're finished. With all of this in mind, you should feel comfortable in creating tables since they don't have to be perfect from the beginning.

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

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:

• SELECT

• INSERT

• UPDATE

• DELETE

• REPLACE

Partition Pruning and Triggers

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

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

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

Maintenance Instructions

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

Table-Level Maintenance

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

• CHECK TABLE

• OPTIMIZE TABLE

• ANALYZE TABLE

• REPAIR TABLE

Partition-Specific Operations

To target one or more specific partitions rather than the entire table, use the ALTER TABLE extensions listed below. In the SQL syntaxes below, partition_names is a comma-separated list of partitions, like p0, p1, p2.

Rebuilding Partitions

Use this to defragment a partition. This operation drops all records in the partition and re-inserts them:

Optimizing Partitions

If you have deleted many rows or modified variable-length columns (such as VARCHAR, BLOB, or TEXT), use this statement to reclaim unused space and defragment the data file:

Analyzing Partitions

Use this to read and store the key distributions for specific partitions:

Repairing Partitions

Use this to fix corrupted partitions:

Checking Partitions

You can verify the integrity of data and indexes within a partition:

Truncating Partitions

To remove all rows from specific partitions while keeping the table structure, use the TRUNCATE PARTITION clause:

Reorganizing Partitions

Reorganizing partitions isn't just maintenance, but it can help with making future maintenance easier. Use the following statement to change the structure of existing partitions without losing data. This is particularly useful for splitting a partition that contains a MAXVALUE range into a new specific range and a new MAXVALUE partition. Use the following syntax:

Example: If you have a partition p_future defined as VALUES LESS THAN MAXVALUE, you can split it to add a specific range for the year 2026:

Best Practices and Considerations

When managing partitioned tables, follow these guidelines to ensure optimal performance and maintainability.

Managing Time-Series Data

Partitioning is most effective for tables containing time-series data where you periodically remove old records.

• Efficient Deletion: Use DROP PARTITION instead of DELETE to remove expired data. This is a metadata operation and is significantly faster than row-by-row deletion.

• The Future Partition: When using RANGE partitioning, define a "future" partition using VALUES LESS THAN MAXVALUE. To add a new specific range, use REORGANIZE PARTITION to split the "future" partition into a new range and a new MAXVALUE partition. See this section for the syntax.

• The Start Partition: Consider creating a small, empty "start" partition (for example, VALUES LESS THAN (0)) to catch NULL values or invalid data. Because the partition pruner often scans the first partition by default, keeping it empty improves query efficiency.

Performance and Scale

• Table Size: Partitioning generally provides noticeable benefits only for tables with more than one million rows.

• Partition Limits: Aim to keep the number of partitions below 50. While MariaDB supports up to 8192 partitions, high partition counts can increase the time required for the server to open the table or perform status checks.

• Index Efficiency: Partitioning is not a substitute for proper indexing. Point queries (finding a single row) are often just as fast with a proper index on a non-partitioned table.

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

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

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

Partitioning Types

MariaDB supports the following partitioning types:

• RANGE

• LIST

• RANGE COLUMNS and LIST COLUMNS

• HASH

• LINEAR HASH

• KEY

• LINEAR KEY

• SYSTEM_TIME

See Also

• Partitioning Overview

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

Syntax

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

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

Description

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

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

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

If no column_names are specified, the table's primary key is used if present, or not null unique key if no primary key is present. If neither of these keys are present, not specifying any column_names will result in an error:

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

KEY partitions do not support column index prefixes. Any columns in the partitioning key that make use of column prefixes are not used.

Examples

The unique key must be NOT NULL:

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

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

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

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

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

Syntax

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

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

Description

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

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

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

Example

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