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

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

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

Steps:

1. Update Package List:

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

   • For Debian/Ubuntu:Bash

   • For Red Hat/CentOS/Fedora:Bash

2. Install MariaDB Server:

   Install the MariaDB server and client packages.

   • For Debian/Ubuntu:Bash

   • For Red Hat/CentOS/Fedora:Bash

3. Secure the Installation:

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

   Follow the prompts to configure your security settings.

4. Start and Verify the Service:

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

   • Check status:

   • Start service (if not running):Bash

   • Verify installation by connecting as root:Bash

For Windows

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

Steps:

1. Download MariaDB:

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

2. Run the Installer:

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

3. Follow On-Screen Instructions:

Important Notes:

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

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

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

Additional Resources:

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

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

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

Further Data Modification Methods

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

• : For importing data from text files.

• : A command-line tool that uses LOAD DATA INFILE. These are covered in "").

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.

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

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

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

Basic Restoration Process

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

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

• You will be prompted for the password for your_username.

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

Important Considerations Before Restoring

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

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

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

Restoring a Single Table Selectively

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

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

2. Grant Limited Privileges:

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

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

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

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

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

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

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

For example, an index on Last_Name:

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

and an index on Position

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

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

See and for more information.

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

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

Database Naming

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

Views

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

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

Replication

A good way of dealing with canary testing with MariaDB is to use replication, which works even in cases with schemas that are different, to an extent, if statement-based replication (SBR) is used. Using this, a new version is built on one server and a new schema is installed there. This is then set to replicate from the current production system. This is not likely to work for all cases, but in many cases, this is a useful tool to allow for canary testing.

Invisible Columns

Hidden or invisible columns in MariaDB are a feature that allows a column to exist in a table without being exposed by default, for instance a SELECT * or an INSERT without column names will not touch this column. If the advice given in the previous sections of the document is followed, then this should not be necessary much, but in some cases, it is still a useful feature.

For example, in the example above with the orders_t table, if it is the case that the current application actually does INSERT without specifying the columns to insert into, then a new column, say customer_id, cannot be added. We can then use an invisible column to support this application, including a new version that does use column names in the INSERT and also use the customer_id column:

See Also

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

Object Relational Mappers (ORM)

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

Stored Procedures and Functions

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

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

Views

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

Application Code

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

SELECT *

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

INSERT Without Column Names

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

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

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

Processing of Column Data

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

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

Use of Reserved Words in the Schema

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

And here:

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

Relying on Nonexplicit Assumptions

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

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

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

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

Code and Schema Standardization

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

Complex SQL

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

See Also

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

About mariadb-dump

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

Key Advantages:

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

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

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

Backing Up All Databases

To export all databases managed by your MariaDB server:

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

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

Commonly Used Option for Efficiency:

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

Example with long options and password in script:

Backing Up a Single Database

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

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

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

Backing Up Specific Tables

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

• First, specify the database name (your_database_name).

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

• Note that the --databases option is not used when dumping specific tables in this manner.

Important Considerations and Best Practices

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

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

This 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

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.

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

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

Defining How Your Data Is Stored

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

The quickstart guide walks you through connecting to a MariaDB server, creating your initial database and table structures, and performing fundamental data operations. It's designed for new users or anyone needing a quick refresher on essential MariaDB commands and basic syntax.

Connecting to MariaDB Server

To interact with the MariaDB server, use a client program. The default command-line client is mariadb.

Connect to MariaDB in monitor mode from the Linux command-line:

Common options:

This primer offers a quick jump-start for beginners using an existing MariaDB database via the mariadb command-line client. Learn how to log in, understand basic database concepts, and perform essential SQL operations like creating tables, inserting data, and retrieving or modifying records.

Logging into MariaDB

To begin, log into your MariaDB server from your system's command-line:

Overview

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

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.

This guide explores a variety of MariaDB's built-in string functions essential for effective data manipulation. Learn how to format text for display, extract specific substrings, replace content, and utilize various expression aids to enhance your string operations in SQL queries.

Formatting Strings

Several functions are available for formatting text and numbers for display or processing.

Concatenating Strings:

• CONCAT(str1, str2, ...): Joins two or more strings together.

  SQL

  This displays a full name by combining name_first, a space, and name_last.

• CONCAT_WS(separator, str1, str2, ...): Joins strings with a specified separator between each.

  SQL

  This creates a pipe-delimited string from col1, col2, and col3.

Formatting Numbers:

• FORMAT(number, decimal_places): Formats a number with commas every three digits and a specified number of decimal places.SQL

  This prepends a dollar sign to a number formatted with commas and two decimal places (e.g., $100,000.00).

Changing Case:

• UCASE(str) or UPPER(str): Converts a string to all upper-case letters.

• LCASE(str) or LOWER(str): Converts a string to all lower-case letters.SQL

Padding Strings:

• LPAD(str, len, padstr): Left-pads str with padstr until it is len characters long.

• RPAD(str, len, padstr): Right-pads str with padstr until it is len characters long.SQL

Trimming Strings:

• LTRIM(str): Removes leading spaces.

• RTRIM(str): Removes trailing spaces.

• TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str): Removes leading, trailing, or both occurrences of remstr (or spaces if remstr is not given). BOTH

Extracting Substrings

These functions help extract specific parts of a string.

• LEFT(str, len): Returns the leftmost len characters from str.

• RIGHT(str, len): Returns the rightmost len characters from str.

  This extracts the first 3 characters as area_code and the last 7 as tel_nbr

Manipulating Strings

Functions for changing or generating strings.

• REPLACE(str, from_str, to_str): Replaces all occurrences of from_str within str with to_str.

  This replaces "Mrs." with "Ms." in the title column.

• INSERT(str, pos, len, newstr): Replaces the substring in str starting at

String Expression Aids

Functions that provide information about strings or assist in specific comparisons/conversions.

• CHAR_LENGTH(str) or CHARACTER_LENGTH(str): Returns the length of str in characters.

  This counts rows where school_id has exactly 8 characters.

• INET_ATON(ip_address_str): Converts an IPv4 address string (e.g., '10.0.1.1') into a numeric representation suitable for numeric sorting.

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

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

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

This guide explains how to configure your MariaDB server to accept connections from remote hosts. Learn to adjust crucial network settings like bind-address, grant appropriate user privileges for remote connections, and configure essential firewall rules.

Understanding Key Network Directives

Two main configuration directives control MariaDB's network accessibility:

• skip-networking: If this directive is enabled, MariaDB will not listen for TCP/IP connections at all. All interaction must be through local mechanisms like Unix sockets or named pipes.

• bind-address: This directive specifies the IP address the server listens on.

  • By default, for security, many MariaDB packages bind to 127.0.0.1 (localhost). This means the server will only accept connections originating from the server machine itself via the loopback interface. Remote connections will fail.

  • If bind-address is set to 127.0.0.1, attempting to connect from another host, or even from the same host using a non-loopback IP address, will result in errors like:

Connecting via localhost typically works even if bind-address is 127.0.0.1 (using the loopback interface):

Locating the MariaDB Configuration File

To change these network settings, you need to edit MariaDB's configuration file (often named my.cnf or my.ini).

• See for comprehensive details.

• Common Locations:

  • /etc/my.cnf (Unix/Linux/BSD)

  • /etc/mysql/my.cnf

Modifying the Configuration File for Remote Access

1. Open the File: Use a text editor to open the primary configuration file identified (e.g., /etc/mysql/my.cnf).

2. Locate [mysqld] Section: Find the section starting with [mysqld].

3. Adjust Directives:

Granting User Privileges for Remote Connections

Configuring the server to listen for remote connections is only the first step. You must also grant privileges to user accounts to connect from specific remote hosts. MariaDB user accounts are defined as 'username'@'hostname'.

1. Connect to MariaDB:

2. **View Existing Remote Users (Optional):**SQL

3. Grant Privileges: Use the GRANT statement to allow a user to connect from a remote host or a range of hosts.

Configuring Your Firewall

Even if MariaDB is configured for remote access, a firewall on the server (software or hardware) might block incoming connections on MariaDB's port (default is 3306).

• RHEL/CentOS 7 Example (using firewall-cmd):

  The first command adds the rule, the second makes it persist after reboots and applies the changes. Consult your OS/firewall documentation for specific commands.

Important Considerations and Reverting Changes

• Security: Opening MariaDB to remote connections, especially to the internet, increases security risks. Always use strong passwords, grant minimal necessary privileges, and restrict host access as much as possible. Consider using TLS/SSL for encrypted connections (see ).

• Reverting: To disable remote access and revert to a more secure local-only setup:

  1. Edit your MariaDB configuration file.

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

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

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

Joining Multiple Tables

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

See Also

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

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

The guide helps diagnose and resolve common issues encountered when connecting to a MariaDB server. Identify causes for errors like 'Can't connect to local server' or access denied messages, and learn steps to effectively troubleshoot these connection problems.

If you are completely new to MariaDB and relational databases, you may want to start with . Also, ensure you understand the connection parameters discussed in the .

Server Not Running or Incorrect Location

Symptoms:

You receive errors similar to:

This guide details the parameters for connecting to a MariaDB server using client programs like mariadb. Learn about default connection behaviors and how to use various command-line options to customize your connection, including secure TLS configurations.

While the examples focus on the mariadb command-line client, the concepts apply to other clients like graphical interfaces or backup utilities (e.g., mariadb-dump). If you are completely new to MariaDB, refer to first.

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

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.

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.

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

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

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

This guide explains how to retrieve data from MariaDB using the SELECT statement, progressing from basic syntax to more involved queries. Learn to select specific columns, limit results, filter with WHERE, sort with ORDER BY, join tables, and use various helpful options and functions.

Setup: Creating and Populating Example Tables

To follow the examples, first create and populate the books and authors tables:

Basic Data Retrieval

Selecting All Columns:

Use * to select all columns from a table.

Output (example):

Selecting Specific Columns:

List the column names separated by commas.

Output (example):

Limiting the Number of Rows with LIMIT:

• To get the first N rows:

  Output (example):

• To get N rows starting from an offset (offset is 0-indexed):SQL

  Output (example, assuming only 3 more rows exist after offset 5):

Filtering and Ordering Results

Filtering with WHERE:

Use the WHERE clause to specify conditions for row selection.

Output (example):

Ordering with ORDER BY:

Use ORDER BY column_name [ASC|DESC] to sort the result set.

Output (example):

• ASC (ascending) is the default order. DESC is for descending order.

• You can order by multiple columns: ORDER BY col1 ASC, col2 DESC.

• Clause Order: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT .... MariaDB generally processes WHERE

Working with Multiple Tables (JOINs) and Functions

Joining Tables:

Use JOIN to combine rows from two or more tables based on a related column.

Output (example):

• Alternative JOIN syntax: ... JOIN authors ON books.author_id = authors.author_id .... For more on joins, see the or a "Basic Joins Guide".

• CONCAT(str1, str2, ...): Concatenates strings.

• AS alias_name: Assigns an alias to an output column.

Pattern Matching with LIKE:

Use LIKE in the WHERE clause for pattern matching. % is a wildcard for zero or more characters.

Output (example, same as above if only Dostoevsky matches):

SELECT Statement Modifiers

Place these modifiers immediately after the SELECT keyword.

• ALL vs DISTINCT:

  • ALL (default): Returns all rows that meet the criteria.

  • DISTINCT: Returns only unique rows for the selected columns. If multiple identical rows are found for the specified columns, only the first one is displayed.

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

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.