1 of 100

Administrative Statements

Explore administrative SQL statements for MariaDB Server. This section covers commands for server management, maintenance, and diagnostics, including BINLOG, KILL, SHUTDOWN, and SHOW.

BINLOG

Executes binary log events directly using base64-encoded data. Primarily used by the mariadb-binlog utility to re-apply events to the server.

Syntax

BINLOG 'str'

Description

BINLOG is an internal-use statement. It is generated by the program as the printable representation of certain events in files. The 'str' value is a base 64-encoded string that the server decodes to determine the data change indicated by the corresponding event.

This statement requires the privilege.

CACHE INDEX

Assigns specific table indices to a named key cache. Optimizes server performance by preloading or dedicating memory to frequently accessed keys.

Syntax

Description

The CACHE INDEX statement assigns table indexes to a specific key cache. It is used only for tables.

A default key cache exists and cannot be destroyed. To create more key caches, the server system variable.

The associations between tables indexes and key caches are lost on server restart. To recreate them automatically, it is necessary to configure caches in a and include some CACHE INDEX (and optionally ) statements in the init file.

Examples

The following statement assigns indexes from the tables t1, t2, and t3 to the key cache named hot_cache:

Implementation (for MyISAM)

Normally CACHE INDEX should not take a long time to execute. Internally it's implemented the following way:

Find the right key cache (under LOCK_global_system_variables)
Open the table with a TL_READ_NO_INSERT lock.
Flush the original key cache for the given file (under key cache lock)
Flush the new key cache for the given file (safety)

The only possible long operations are getting the locks for the table and flushing the original key cache, if there were many key blocks for the file in it.

We plan to also add CACHE INDEX for Aria tables if there is a need for this.

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

DESCRIBE

Provides information about a table's columns. Acts as a shortcut for SHOW COLUMNS, displaying field names, types, and other attributes.

Syntax

{DESCRIBE | DESC} tbl_name [col_name | wild]

Description

DESCRIBE provides information about the columns in a table.It is a shortcut for .These statements also display information for .

col_name can be a column name, or a string containing theSQL "%" and "_" wildcard characters toobtain output only for the columns with names matching the string. There is noneed to enclose the string within quotes unless it contains spaces or otherspecial characters.

The description for providesmore information about the output columns.

HELP Command

Displays help information from the server's help tables. Useful for looking up SQL syntax and command descriptions directly from the client.

Syntax

Description

The HELP

KILL

Terminates a specific connection or query. Allows administrators to stop runaway threads or disconnect users to free up server resources.

Syntax

Description

Each connection to mariadbd runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST

PURGE BINARY LOGS

Deletes binary log files from the server. Removes logs based on a specific filename or date timestamp to reclaim disk space and manage storage.

Syntax

Description

The PURGE BINARY LOGS

RESET

Clears internal server buffers, caches, and status variables. Resets state information like the query cache or replication status without a restart.

Syntax

RESET reset_option [, reset_option] ...

Description

The RESET statement is used to clear the state of various server operations. You must have the to executeRESET.

RESET acts as a stronger version of the statement.

The different RESET options are:

Option

Description

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

SHUTDOWN

Stops the MariaDB server process. Allows a client with the SHUTDOWN privilege to cleanly power down the database instance.

Syntax

Description

The SHUTDOWN command shuts the server down.

USE [DATABASE]

Selects the default database for the current session. Subsequent queries execute against this database unless explicitly qualified by name.

Syntax

Description

The 'USE db_name'

ANALYZE and EXPLAIN Statements

Learn commands for query analysis. This section covers ANALYZE TABLE and EXPLAIN, used to view execution plans and optimize query performance.

ANALYZE FORMAT=JSON

Gain deep insight into query execution with JSON-formatted analysis. This command combines optimizer estimates with actual runtime statistics for precise performance tuning.

ANALYZE FORMAT=JSON is a mix of the and statement features. The ANALYZE FORMAT=JSON $statement will execute $statement, and then print the output of EXPLAIN FORMAT=JSON, amended with data from the query execution.

Basic Execution Data

EXPLAIN ANALYZE

Understand the historical context of EXPLAIN ANALYZE in MariaDB. Learn how this syntax maps to the modern ANALYZE statement for profiling query execution.

Outdated syntax

The syntax for the EXPLAIN ANALYZE feature was changed to ANALYZE statement, available since . See ANALYZE statement.

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

EXPLAIN FORMAT=JSON

Get comprehensive query plans in JSON format. This output provides detailed optimizer data, including costs and attached conditions, not found in the tabular view.

Synopsis

EXPLAIN FORMAT=JSON is a variant of command that produces output in JSON form. The output always has one row which has only one column titled "JSON". The contents are a JSON representation of the query plan:

Using Buffer UPDATE Algorithm

Understand the 'Using buffer' strategy for UPDATE operations. Learn how MariaDB prevents infinite update loops when modifying indexed columns during a range scan.

This article explains the statement's Using Buffer algorithm.

Consider the following table and query:

Name

Salary

BACKUP Statements

Learn about backup statements for MariaDB Server. This section details SQL statements and utilities for creating consistent database backups, essential for disaster recovery and data protection.

Storage Snapshots and BACKUP STAGE Statements

Combine database commands with storage-level snapshots. Learn the correct sequence of BACKUP STAGE commands to freeze writes safely while taking a disk snapshot.

The statements are a set of statements to make it possible to make an efficient external backup tool. These commands could even be used by tools that perform backups by taking a snapshot of a file system, SAN, or some other kind of storage device.

Generic Backup Process with Storage Snapshots

A tool that backs up MariaDB by taking a snapshot of a file system, SAN, or some other kind of storage device could use each BACKUP STAGE command in the following way:

FLUSH Statements

Explore commands to clear internal caches. Learn to use FLUSH to reload privileges, clear the query cache, or close open tables.

FLUSH QUERY CACHE

Defragment the query cache to optimize memory usage. This command reorganizes the cache to eliminate fragmentation without removing existing cached queries.

Description

You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.

The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.

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

Plugin Statements

Manage server plugins. This section covers INSTALL PLUGIN, UNINSTALL PLUGIN, and SHOW PLUGINS for extending server functionality.

Replication Statements

Control replication topologies. Learn statements like CHANGE MASTER TO and START SLAVE to configure primaries and replicas.

The terms master and slave have historically been used in replication, and MariaDB has begun the process of adding primary and replica synonyms. The old terms will continue to be used to maintain backward compatibility - see MDEV-18777 to follow progress on this effort.

CHANGE MASTER TO RESET MASTER RESET REPLICA SET GLOBAL SQL_SLAVE_SKIP_COUNTER START REPLICA STOP REPLICA Legacy Replication Statements

RESET MASTER

RESET MASTER [TO #]

Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file with a suffix of .000001.

If TO # is given, then the first new binary log file will start from number #.

This statement is for use only when the master is started for the first time and should never be used if any slaves are actively replicating from the binary log.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER

Syntax

Description

This statement skips the next N events from the primary. This is useful for recovering from stops caused by a statement.

Legacy Replication Statements

Category for old replication statements

RESET SLAVE

Old name for RESET REPLICA

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

SHOW SLAVE HOSTS

Old name for SHOW REPLICA HOSTS

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

SHOW SLAVE STATUS

Old name for SHOW REPLICA STATUS

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

START SLAVE

Old name for START REPLICA

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

STOP SLAVE

Old name for STOP REPLICA

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

SET Statements

Assign values to system variables. Learn to use the SET statement to configure GLOBAL and SESSION variables for tuning server behavior.

SET SET CHARACTER SET SET GLOBAL SQL_SLAVE_SKIP_COUNTER SET NAMES SET PASSWORD SET ROLE SET SQL_LOG_BIN SET STATEMENT SET TRANSACTION SET Variable

SET SQL_LOG_BIN

Enable or disable binary logging for the current session. This statement allows administrators to perform operations without replicating them to replicas.

Syntax

Description

Sets the system variable, which disables or enables for the current connection, if the client has the

SHOW Statements

View server metadata and status. This section lists SHOW statements for inspecting databases, tables, variables, and performance metrics.

About SHOW

Overview of the SHOW statement syntax. Learn how to use patterns and WHERE clauses to filter output from various SHOW statements.

SHOW provides information about various aspects of MariaDB Server. A list of the various SHOW statements is here.

The general syntax is:

SHOW LIKE 'pattern' | WHERE expr

The LIKE and WHERE clauses make sense only for particular SHOW statements, though. See Extended SHOW for what SHOW statements benefit from using those clauses.

If the syntax for a given SHOW statement includes LIKE 'pattern' , 'pattern' is a string that can contain the SQL % and _ wildcard characters. The pattern is useful for restricting statement output to matching values.

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

SHOW BINARY LOGS

List the binary log files on the server. View the file names and sizes to monitor replication logs and manage disk usage.

Syntax

Description

Lists the files on the server. This statement is used as part of the procedure described in , that shows how to determine which logs can be purged.

SHOW BINLOG EVENTS

Inspect the contents of a binary log file. This statement displays the events within a specific binlog, useful for debugging replication issues.

Syntax

Description

Shows the events in the . If you do not specify log_name

SHOW CHARACTER SET

List available character sets. View the default collation, maximum length, and description for each supported character set.

Syntax

Description

The SHOW CHARACTER SET

SHOW CLIENT_STATISTICS

View statistics on client connections. This statement displays metrics like bytes sent/received and busy time for connected clients.

Syntax

Description

The SHOW CLIENT_STATISTICS

SHOW CONTRIBUTORS

List financial contributors to the MariaDB Foundation. View the names and contributions of companies and individuals supporting the project.

Syntax

Description

The SHOW CONTRIBUTORS

SHOW CREATE DATABASE

View the CREATE DATABASE statement. This statement returns the SQL syntax required to recreate a specific database with its current options.

Syntax

Description

Shows the statement that creates the given database. SHOW CREATE SCHEMA

SHOW CREATE FUNCTION

Retrieve the CREATE FUNCTION statement. This statement shows the exact SQL used to define a stored function, including its body and characteristics.

Syntax

Description

This statement is similar to but for .

SHOW CREATE SERVER

Display the CREATE SERVER statement. This statement shows the configuration details for a defined federated server connection.

Syntax

Description

Shows the statement that created the given server definition.

Example

The SHOW CREATE SERVER statement is not available.

SHOW FUNCTION CODE

Display the internal instruction representation of a stored function. This debug statement shows the low-level opcodes of the routine.

Syntax

Description

SHOW FUNCTION CODE

CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

files listed in the log index file prior to the specified log file name ordate. BINARY and MASTER are synonyms.Deleted log files also are removed from the list recorded in the index file, sothat the given log file becomes the first in the list.

db_name

USE

HELP contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Sequences
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

HELP drop
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER TABLE
   DROP DATABASE
   DROP EVENT
   DROP FUNCTION
   DROP FUNCTION UDF
   DROP INDEX
   DROP PACKAGE
   DROP PACKAGE BODY
   DROP PROCEDURE
   DROP ROLE
   DROP SEQUENCE
   DROP SERVER
   DROP TABLE
   DROP TRIGGER
   DROP USER
   DROP VIEW

DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | YES  |     | NULL    |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

KILL thread_id

KILL

CONNECTION

QUERY

ANALYZE

SUPER

. The LIKE clause, if present on its own, indicates which character set names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in

Name

Location

Comment

latin1

SHOW CREATE DATABASE

SHOW CREATE SERVER srv1\G
*************************** 1. row ***************************
       Server: srv1
Create Server: CREATE SERVER `srv1` FOREIGN DATA WRAPPER mysql 
  OPTIONS (HOST '172.30.0.58', DATABASE 'db1', USER 'maxscale', PASSWORD 'password');

ANALYZE FORMAT=JSON Examples

Review practical examples of ANALYZE FORMAT=JSON output. Learn to identify performance bottlenecks by comparing estimated costs against actual execution metrics.

Example #1

Customers who have ordered more than 1M goods.

ANALYZE FORMAT=JSON
SELECT COUNT(*)
FROM customer
WHERE
  (SELECT SUM(o_totalprice) FROM orders WHERE o_custkey=c_custkey) > 1000*1000;

The query takes 40 seconds over cold cache.

ANALYZE shows that 39.208 seconds were spent in the subquery, which was executed 150K times (for every row of outer table).

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

SET CHARACTER SET

Map strings to a specific character set. This command updates the character set for the client, results, and connection to ensure correct data encoding.

Syntax

SET {CHARACTER SET | CHARSET}
    {charset_name | DEFAULT}

Description

Sets the character_set_client and character_set_results session system variables to the specified character set and collation_connection to the value of , which implicitly sets to the value of .

This maps all strings sent between the current client and the server with the given mapping.

Example

SHOW MASTER STATUS

View the current status of the primary server's binary log. This statement returns the current log file name and position for replication synchronization.

Syntax

SHOW [MASTER | BINLOG] STATUS

SHOW MASTER STATUS

Description

Provides status information about the files of the primary.

This statement requires the privilege.

This statement requires the privilege and the privilege.

To see information about the current in the binary log, use the variable.

Example

SHOW LOCALES

List available error message locales. View the language IDs, names, and descriptions for supported localization settings.

Syntax

SHOW LOCALES

Description

SHOW LOCALES was introduced as part of the .

SHOW LOCALES is used to return information as part of the plugin. It requires this plugin to be installed to work.

While the table has 8 columns, the SHOW LOCALES statement will only display 4 of them:

Example

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

SET Variable

Assign values to user-defined variables. This guide explains how to store data in session-specific variables for reuse in subsequent SQL statements.

Syntax

SET var_name = expr [, var_name = expr] ...

Description

The SET statement in is an extended version of the general statement. Referenced variables may be ones declared inside a stored program, global system variables, or user-defined variables.

The SET statement in stored programs is implemented as part of the pre-existing syntax. This allows an extended syntax of SET a=x, b=y, ... where different variable types (locally declared variables, global and session server variables, user-defined variables) can be mixed. This also allows combinations of local variables and some options that make sense only for system variables; in that case, the options are recognized but ignored.

SET can be used with both and .

When setting several variables using the columns returned by a query, should be preferred.

To set many variables to the same value, the function can be used.

Below is an example of how a user-defined variable may be set:

SHOW INNODB STATUS (removed)

Documentation for the deprecated SHOW INNODB STATUS statement. This statement was removed in favor of SHOW ENGINE INNODB STATUS.

Syntax

SHOW INNODB STATUS

Description

This is a deprecated synonym for SHOW ENGINE INNODB STATUS. It was removed in MariaDB and MySQL 5.5.

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

SHOW PLUGINS SONAME

List plugins and their library files. View information about installed plugins and their associated shared object names.

Syntax

SHOW PLUGINS SONAME { library | LIKE 'pattern' | WHERE expr };

Description

SHOW PLUGINS SONAME displays information about compiled-in and all server plugins in the directory, including plugins that haven't been installed.

Examples

There is also a corresponding table, called , which contains more complete information.

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

"r_engine_stats": {
        "pages_accessed":  integer,
        "pages_updated": integer,
        "pages_read_count": integer,
        "pages_prefetch_read_count": integer,
        "pages_read_time_ms": double,
        "old_rows_read": integer
      }

*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 1000,
      "filtered": 100,
      "attached_condition": "(t1.col1 = 1)"
    }
  }
}

ERROR 1966 (HY000): When using parallel replication and GTID with multiple 
 replication domains, @@sql_slave_skip_counter can not be used. Instead, 
 setting @@gtid_slave_pos explicitly can be  used to skip to after a given GTID 
 position.

SHOW BINARY LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     19039 |
| mariadb-bin.000002 |    717389 |
| mariadb-bin.000003 |       300 |
| mariadb-bin.000004 |       333 |
| mariadb-bin.000005 |       899 |
| mariadb-bin.000006 |       125 |
| mariadb-bin.000007 |     18907 |
| mariadb-bin.000008 |     19530 |
| mariadb-bin.000009 |       151 |
| mariadb-bin.000010 |       151 |
| mariadb-bin.000011 |       125 |
| mariadb-bin.000012 |       151 |
| mariadb-bin.000013 |       151 |
| mariadb-bin.000014 |       125 |
| mariadb-bin.000015 |       151 |
| mariadb-bin.000016 |       314 |
+--------------------+-----------+

SHOW BINLOG EVENTS IN 'mysql_sandbox10019-bin.000002';
+-------------------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name                      | Pos | Event_type        | Server_id | End_log_pos | Info                                           |
+-------------------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql_sandbox10019-bin.000002 |   4 | Format_desc       |         1 |         248 | Server ver: 10.0.19-MariaDB-log, Binlog ver: 4 |
| mysql_sandbox10019-bin.000002 | 248 | Gtid_list         |         1 |         273 | []                                             |
| mysql_sandbox10019-bin.000002 | 273 | Binlog_checkpoint |         1 |         325 | mysql_sandbox10019-bin.000002                  |
| mysql_sandbox10019-bin.000002 | 325 | Gtid              |         1 |         363 | GTID 0-1-1                                     |
| mysql_sandbox10019-bin.000002 | 363 | Query             |         1 |         446 | CREATE DATABASE blog                           |
| mysql_sandbox10019-bin.000002 | 446 | Gtid              |         1 |         484 | GTID 0-1-2                                     |
| mysql_sandbox10019-bin.000002 | 484 | Query             |         1 |         571 | use `blog`; CREATE TABLE bb (id INT)           |
+-------------------------------+-----+-------------------+-----------+-------------+------------------------------------------------+

SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | cp1252 West European        | latin1_swedish_ci |      1 |
| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
+---------+-----------------------------+-------------------+--------+

SHOW CHARACTER SET WHERE Maxlen LIKE '2';
+---------+---------------------------+-------------------+--------+
| Charset | Description               | Default collation | Maxlen |
+---------+---------------------------+-------------------+--------+
| big5    | Big5 Traditional Chinese  | big5_chinese_ci   |      2 |
| sjis    | Shift-JIS Japanese        | sjis_japanese_ci  |      2 |
| euckr   | EUC-KR Korean             | euckr_korean_ci   |      2 |
| gb2312  | GB2312 Simplified Chinese | gb2312_chinese_ci |      2 |
| gbk     | GBK Simplified Chinese    | gbk_chinese_ci    |      2 |
| ucs2    | UCS-2 Unicode             | ucs2_general_ci   |      2 |
| cp932   | SJIS for Windows Japanese | cp932_japanese_ci |      2 |
+---------+---------------------------+-------------------+--------+

SHOW CLIENT_STATISTICS\G
*************************** 1. row ***************************
                Client: localhost
     Total_connections: 35
Concurrent_connections: 0
        Connected_time: 708
             Busy_time: 2.5557979999999985
              Cpu_time: 0.04123740000000002
        Bytes_received: 3883
            Bytes_sent: 21595
  Binlog_bytes_written: 0
             Rows_read: 18
             Rows_sent: 115
          Rows_deleted: 0
         Rows_inserted: 0
          Rows_updated: 0
       Select_commands: 70
       Update_commands: 0
        Other_commands: 0
   Commit_transactions: 1
 Rollback_transactions: 0
    Denied_connections: 0
      Lost_connections: 0
         Access_denied: 0
         Empty_queries: 35

SHOW CONTRIBUTORS;
+---------------------+-------------------------------+-------------------------------------------------------------+
| Name                | Location                      | Comment                                                     |
+---------------------+-------------------------------+-------------------------------------------------------------+
| Alibaba Cloud       | https://www.alibabacloud.com/ | Platinum Sponsor of the MariaDB Foundation                  |
| Tencent Cloud       | https://cloud.tencent.com     | Platinum Sponsor of the MariaDB Foundation                  |
| Microsoft           | https://microsoft.com/        | Platinum Sponsor of the MariaDB Foundation                  |
| MariaDB Corporation | https://mariadb.com           | Founding member, Platinum Sponsor of the MariaDB Foundation |
| ServiceNow          | https://servicenow.com        | Platinum Sponsor of the MariaDB Foundation                  |
| Intel               | https://www.intel.com         | Platinum Sponsor of the MariaDB Foundation                  |
| SIT                 | https://sit.org               | Platinum Sponsor of the MariaDB Foundation                  |
| Visma               | https://visma.com             | Gold Sponsor of the MariaDB Foundation                      |
| DBS                 | https://dbs.com               | Gold Sponsor of the MariaDB Foundation                      |
| IBM                 | https://www.ibm.com           | Gold Sponsor of the MariaDB Foundation                      |
| Automattic          | https://automattic.com        | Silver Sponsor of the MariaDB Foundation                    |
| Percona             | https://www.percona.com/      | Sponsor of the MariaDB Foundation                           |
| Galera Cluster      | https://galeracluster.com     | Sponsor of the MariaDB Foundation                           |
| Google              | USA                           | Sponsoring encryption, parallel replication and GTID        |
| Facebook            | USA                           | Sponsoring non-blocking API, LIMIT ROWS EXAMINED etc        |
| Ronald Bradford     | Brisbane, Australia           | EFF contribution for UC2006 Auction                         |
| Sheeri Kritzer      | Boston, Mass. USA             | EFF contribution for UC2006 Auction                         |
| Mark Shuttleworth   | London, UK.                   | EFF contribution for UC2006 Auction                         |
+---------------------+-------------------------------+-------------------------------------------------------------+

SHOW CREATE DATABASE test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+

SHOW CREATE SCHEMA test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+

SHOW CREATE DATABASE test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE test /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+

SHOW CREATE FUNCTION VatCents\G
*************************** 1. row ***************************
            Function: VatCents
            sql_mode: 
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `VatCents`(price DECIMAL(10,2)) RETURNS int(11)
    DETERMINISTIC
BEGIN
 DECLARE x INT;
 SET x = price * 114;
 RETURN x;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 39872,
    "table": {
      "table_name": "customer",
      "access_type": "index",
      "key": "i_c_nationkey",
      "key_length": "5",
      "used_key_parts": ["c_nationkey"],
      "r_loops": 1,
      "rows": 150303,
      "r_rows": 150000,
      "r_total_time_ms": 270.3,
      "filtered": 100,
      "r_filtered": 60.691,
      "attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
      "using_index": true
    },
    "subqueries": [
      {
        "query_block": {
          "select_id": 2,
          "r_loops": 150000,
          "r_total_time_ms": 39531,
          "table": {
            "table_name": "orders",
            "access_type": "ref",
            "possible_keys": ["i_o_custkey"],
            "key": "i_o_custkey",
            "key_length": "5",
            "used_key_parts": ["o_custkey"],
            "ref": ["dbt3sf1.customer.c_custkey"],
            "r_loops": 150000,
            "rows": 7,
            "r_rows": 10,
            "r_total_time_ms": 39208,
            "filtered": 100,
            "r_filtered": 100
          }
        }
      }
    ]
  }
}

SHOW LOCALES;
+-----+-------+-------------------------------------+------------------------+
| Id  | Name  | Description                         | Error_Message_Language |
+-----+-------+-------------------------------------+------------------------+
|   0 | en_US | English - United States             | english                |
|   1 | en_GB | English - United Kingdom            | english                |
|   2 | ja_JP | Japanese - Japan                    | japanese               |
|   3 | sv_SE | Swedish - Sweden                    | swedish                |
...

SHOW PLUGINS SONAME 'ha_example.so';
+----------+---------------+----------------+---------------+---------+
| Name     | Status        | Type           | Library       | License |
+----------+---------------+----------------+---------------+---------+
| EXAMPLE  | NOT INSTALLED | STORAGE ENGINE | ha_example.so | GPL     |
| UNUSABLE | NOT INSTALLED | DAEMON         | ha_example.so | GPL     |
+----------+---------------+----------------+---------------+---------+

The LIMIT clause has the same syntax as for the SELECT statement.

SHOW EVENTS

UNINSTALL PLUGIN IF EXISTS example;
Query OK, 0 rows affected (0.099 sec)

UNINSTALL PLUGIN IF EXISTS example;
Query OK, 0 rows affected, 1 warning (0.000 sec)

SHOW WARNINGS;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1305 | PLUGIN example does not exist |
+-------+------+-------------------------------+

SHOW VARIABLES LIKE 'character_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+

SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

SET CHARACTER SET utf8mb4;

SHOW VARIABLES LIKE 'character_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | latin1  |
| character_set_database   | latin1  |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | latin1  |
| character_set_system     | utf8    |
+--------------------------+---------+

SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

SHOW CREATE EVENT test.e_daily\G
*************************** 1. row ***************************
               Event: e_daily
            sql_mode: 
           time_zone: SYSTEM
        Create Event: CREATE EVENT `e_daily`
                        ON SCHEDULE EVERY 1 DAY
                        STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR
                        ON COMPLETION NOT PRESERVE
                        ENABLE
                        COMMENT 'Saves total number of sessions then
                                clears the table each day'
                        DO BEGIN
                          INSERT INTO site_activity.totals (time, total)
                            SELECT CURRENT_TIMESTAMP, COUNT(*) 
                            FROM site_activity.sessions;
                          DELETE FROM site_activity.sessions;
                        END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

SHOW BINLOG STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000016 |      475 |              |                  |
+--------------------+----------+--------------+------------------+
SELECT @@global.gtid_binlog_pos;
+--------------------------+
| @@global.gtid_binlog_pos |
+--------------------------+
| 0-1-2                    |
+--------------------------+

CREATE USER foo4@test require cipher 'text' 
  issuer 'foo_issuer' subject 'foo_subject';

SHOW CREATE USER foo4@test\G
*************************** 1. row ***************************
CREATE USER 'foo4'@'test' 
  REQUIRE ISSUER 'foo_issuer' 
  SUBJECT 'foo_subject' 
  CIPHER 'text'

CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;

SHOW CREATE USER 'monty'@'localhost';
+------------------------------------------------------------------+
| CREATE USER for monty@localhost                                  |
+------------------------------------------------------------------+
| CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY |
+------------------------------------------------------------------+

SELECT f();
ERROR 1305 (42000): FUNCTION f does not exist

SHOW COUNT(*) ERRORS;
+-----------------------+
| @@session.error_count |
+-----------------------+
|                     1 |
+-----------------------+

SHOW ERRORS;
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Error | 1305 | FUNCTION f does not exist |
+-------+------+---------------------------+

SHOW CREATE VIEW example\G
*************************** 1. row ***************************
                View: example
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `example` AS (select `t`.`id` AS `id`,`t`.`s` AS `s` from
`t`)
character_set_client: cp850
collation_connection: cp850_general_ci

SHOW CREATE VIEW example\G
*************************** 1. row ***************************
                View: example
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECU
RITY DEFINER VIEW example AS (select t.id AS id,t.s AS s from t)
character_set_client: cp850
collation_connection: cp850_general_ci

SELECT CURRENT_USER(), SCHEMA();
+----------------+----------+
| CURRENT_USER() | SCHEMA() |
+----------------+----------+
| jon@ghidora    | myschema |
+----------------+----------+

SHOW EVENTS\G
*************************** 1. row ***************************
                  Db: myschema
                Name: e_daily
             Definer: jon@ghidora
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2006-02-09 10:41:23
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
                  Db: test
                Name: VatCents
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2013-06-01 12:40:31
             Created: 2013-06-01 12:40:31
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SHOW FUNCTION STATUS LIKE 'V%' \G
*************************** 1. row ***************************
                  Db: test
                Name: VatCents
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2013-06-01 12:40:31
             Created: 2013-06-01 12:40:31
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SHOW FUNCTION STATUS WHERE Security_type LIKE 'DEFINER' \G
*************************** 1. row ***************************
                  Db: test
                Name: VatCents
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2013-06-01 12:40:31
             Created: 2013-06-01 12:40:31
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

plugin_library

SHOW SCHEMAS

SHOW DATABASES

LIKE

WHERE

LIKE

TEMPORARY

is set. It shows the CREATE statement that creates the given package specification.

INSTALL PLUGIN

Install a specific plugin from a shared library. This statement adds the plugin to the mysql.plugin table and loads its code into the server memory.

Syntax

Description

This statement installs an individual plugin from the specified library. To install the whole library (which could be required), use INSTALL SONAME. See also .

plugin_name is the name of the plugin as defined in theplugin declaration structure contained in the library file. Plugin names arenot case sensitive. For maximal compatibility, plugin names should be limitedto ASCII letters, digits, and underscore, because they are used in C sourcefiles, shell command lines, M4 and Bourne shell scripts, and SQL environments.

plugin_library is the name of the shared library thatcontains the plugin code. The file name extension can be omitted (which makes the statement look the same on all architectures).

The shared library must be located in the plugin directory (that is,the directory named by the system variable). The library must be in the plugin directory itself, not in a subdirectory. Bydefault, plugin_dir is plugin directory under the directory named bythe pkglibdir configuration variable, but it can be changed by settingthe value of plugin_dir at server startup. For example, setits value in a my.cnf file:

If the value of is a relative path name, it istaken to be relative to the base directory (the value of the system variable).

INSTALL PLUGIN adds a line to the mysql.plugin table thatdescribes the plugin. This table contains the plugin name and library filename.

INSTALL PLUGIN causes the server to readoption (my.cnf) files just as during server startup. This enables the plugin topick up any relevant options from those files. It is possible to add pluginoptions to an option file even before loading a plugin (if the loose prefix isused). It is also possible to uninstall a plugin, edit my.cnf, and install theplugin again. Restarting the plugin this way enables it to the new optionvalues without a server restart.

INSTALL PLUGIN also loads and initializes the plugin code tomake the plugin available for use. A plugin is initialized by executing itsinitialization function, which handles any setup that the plugin must performbefore it can be used.

To use INSTALL PLUGIN, you must have the for the mysql.plugin table.

At server startup, the server loads and initializes any plugin that islisted in the mysql.plugin table. This means that a plugin is installedwith INSTALL PLUGIN only once, not every time the serverstarts. Plugin loading at startup does not occur if the server is started withthe --skip-grant-tables option.

When the server shuts down, it executes the de-initialization functionfor each plugin that is loaded so that the plugin has a chance toperform any final cleanup.

If you need to load plugins for a single server startup when the--skip-grant-tables option is given (which tells the servernot to read system tables), use the--plugin-load .

IF NOT EXISTS

When the IF NOT EXISTS clause is used, MariaDB will return a note instead of an error if the specified plugin already exists. See .

Examples

The extension can also be omitted:

START REPLICA

Syntax

Description

START REPLICA is a synonym for START SLAVE, which is considered deprecated.

Only START SLAVE can be used.

START SLAVE or START REPLICA with no thread_type options starts both of the replica threads (see ) needed to connect with a master setup with The I/O thread reads events from the primary server and stores them in the . The SQL thread reads events from the relay log and executes them.

START REPLICA requires the privilege.

If START REPLICA succeeds in starting the replica threads, it returns without any error. However, even in that case, it might be that the replica threads start and then later stop (for example, because they do not manage to connect to the primary or read its , or some other problem). START REPLICA does not warn you about this. You must check the replica's for error messages generated by the replica threads or check that they are running satisfactorily with ().

START REPLICA UNTIL

START REPLICA UNTIL refers to the SQL_THREAD replica position at which the SQL_THREAD replication will halt. If SQL_THREAD isn't specified, both threads are started.

START REPLICA UNTIL master_gtid_pos=xxx is also supported. See for more details.

MariaDB starting with

SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS

The START REPLICA UNTIL statement contains the options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS to allow control of whether the replica stops before or after a provided GTID state. Its syntax is:

See for details.

SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS are not available.

connection_name

If there is only one nameless primary, or the default primary (as specified by the system variable) is intended, connection_name can be omitted. If provided, the START REPLICA statement will apply to the specified primary. connection_name is case-insensitive.

The FOR CHANNEL keyword is available for MySQL compatibility. This is identical to using the channel_name directly after START REPLICA.

The FOR CHANNEL keyword is not available.

START ALL REPLICAS

START ALL REPLICAS starts all configured replicas (replicas with master_host not empty) that were not started before. It will give a note for all started connections. You can check the notes with .

SHOW PROCEDURE CODE

Display the internal instruction representation of a stored procedure. This debug statement shows the low-level opcodes of the routine.

Syntax

SHOW PROCEDURE CODE proc_name

Description

This statement is a MariaDB extension that is available only for servers that have been built with debugging support. It displays a representation of the internal implementation of the named . A similar statement, , displays information about .

Both statements require that you be the owner of the routine or have access to the table.

If the named routine is available, each statement produces a result set. Each row in the result set corresponds to one "instruction" in the routine. The first column is Pos, which is an ordinal number beginning with 0. The second column is Instruction, which contains an SQL statement (usually changed from the original source), or a directive which has meaning only to the stored-routine handler.

Examples

SHOW INDEX_STATISTICS

View usage statistics for table indexes. This statement displays how often specific indexes are used, helping optimize query performance.

Syntax

SHOW INDEX_STATISTICS

Description

The information_schema.INDEX_STATISTICS table shows statistics on index usage and makes it possible to do such things as locating unused indexes and generating the commands to remove them.

SHOW INDEX_STATISTICS is replaced by the generic statement.

The SHOW INDEX_STATISTICS statement was introduced in as part of the feature. It was removed as a separate statement in , but effectively replaced by the generic statement.

The system variable must be set to 1 to activate this feature. See the and table for more information.

Example

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

UNINSTALL SONAME IF EXISTS 'ha_example';
Query OK, 0 rows affected (0.099 sec)

UNINSTALL SONAME IF EXISTS 'ha_example';
Query OK, 0 rows affected, 1 warning (0.000 sec)

SHOW WARNINGS;
+-------+------+-------------------------------------+
| Level | Code | Message                             |
+-------+------+-------------------------------------+
| Note  | 1305 | SONAME ha_example.so does not exist |
+-------+------+-------------------------------------+

SHOW CREATE TRIGGER example\G
*************************** 1. row ***************************
               Trigger: example
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES
,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_
ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER example BEFORE
 INSERT ON t FOR EACH ROW
BEGIN
        SET NEW.c = NEW.c * 2;
END
  character_set_client: cp850
  collation_connection: cp850_general_ci
  Database Collation: utf8_general_ci
  Created: 2016-09-29 13:53:34.35

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

SHOW OPEN TABLES;
+----------+---------------------------+--------+-------------+
| Database | Table                     | In_use | Name_locked |
+----------+---------------------------+--------+-------------+
...
| test     | xjson                     |      0 |           0 |
| test     | jauthor                   |      0 |           0 |
| test     | locks                     |      1 |           0 |
...
+----------+---------------------------+--------+-------------+

SHOW PACKAGE STATUS LIKE 'pkg1'\G
*************************** 1. row ***************************
                  Db: test
                Name: pkg1
                Type: PACKAGE
             Definer: root@localhost
            Modified: 2018-02-27 14:38:15
             Created: 2018-02-27 14:38:15
       Security_type: DEFINER
             Comment: This is my first package
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SHOW CREATE PACKAGE employee_tools\G
*************************** 1. row ***************************
             Package: employee_tools
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
      Create Package: CREATE DEFINER="root"@"localhost" PACKAGE "employee_tools" AS
  FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2);
  PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2));
  PROCEDURE raiseSalaryStd(eid INT);
  PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2));
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

START { SLAVE | REPLICA } ["connection_name"] [thread_type [, thread_type] ... ]
START { SLAVE | REPLICA } ["connection_name"] [SQL_THREAD] UNTIL                
    MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos 
START { SLAVE | REPLICA } ["connection_name"] [SQL_THREAD] UNTIL
    RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos 
START { SLAVE | REPLICA } ["connection_name"] [SQL_THREAD] UNTIL
    MASTER_GTID_POS = <GTID position> 
START ALL { SLAVES | REPLICAS } [thread_type [, thread_type]] 

thread_type: IO_THREAD | SQL_THREAD

LOCK TABLES ... WRITE

SHOW COLLATION LIKE 'utf8mb4_bin'; 
+-------------+---------+------+---------+----------+---------+---------------+ 
| Collation   | Charset | Id   | Default | Compiled | Sortlen | Pad_attribute | 
+-------------+---------+------+---------+----------+---------+---------------+ 
| utf8mb4_bin | utf8mb4 | 46   |         | Yes      | 1       | PAD SPACE     | 
+-------------+---------+------+---------+----------+---------+---------------+

INSTALL PLUGIN IF NOT EXISTS example SONAME 'ha_example';
Query OK, 0 rows affected (0.104 sec)

INSTALL PLUGIN IF NOT EXISTS example SONAME 'ha_example';
Query OK, 0 rows affected, 1 warning (0.000 sec)

SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message                            |
+-------+------+------------------------------------+
| Note  | 1968 | Plugin 'example' already installed |
+-------+------+------------------------------------+

SHOW COLLATION LIKE 'latin1%';
+-------------------------+---------+------+---------+----------+---------+
| Collation               | Charset | Id   | Default | Compiled | Sortlen |
+-------------------------+---------+------+---------+----------+---------+
| latin1_german1_ci       | latin1  |    5 |         | Yes      |       1 |
| latin1_swedish_ci       | latin1  |    8 | Yes     | Yes      |       1 |
| latin1_danish_ci        | latin1  |   15 |         | Yes      |       1 |
| latin1_german2_ci       | latin1  |   31 |         | Yes      |       2 |
| latin1_bin              | latin1  |   47 |         | Yes      |       1 |
| latin1_general_ci       | latin1  |   48 |         | Yes      |       1 |
| latin1_general_cs       | latin1  |   49 |         | Yes      |       1 |
| latin1_spanish_ci       | latin1  |   94 |         | Yes      |       1 |
| latin1_swedish_nopad_ci | latin1  | 1032 |         | Yes      |       1 |
| latin1_nopad_bin        | latin1  | 1071 |         | Yes      |       1 |
+-------------------------+---------+------+---------+----------+---------+

SHOW COLLATION WHERE Sortlen LIKE '8' AND Charset LIKE 'utf8mb4';
+------------------------------+---------+------+---------+----------+---------+
| Collation                    | Charset | Id   | Default | Compiled | Sortlen |
+------------------------------+---------+------+---------+----------+---------+
| utf8mb4_unicode_ci           | utf8mb4 |  224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci         | utf8mb4 |  225 |         | Yes      |       8 |
| utf8mb4_latvian_ci           | utf8mb4 |  226 |         | Yes      |       8 |
| utf8mb4_romanian_ci          | utf8mb4 |  227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci         | utf8mb4 |  228 |         | Yes      |       8 |
| utf8mb4_polish_ci            | utf8mb4 |  229 |         | Yes      |       8 |
| utf8mb4_estonian_ci          | utf8mb4 |  230 |         | Yes      |       8 |
| utf8mb4_spanish_ci           | utf8mb4 |  231 |         | Yes      |       8 |
| utf8mb4_swedish_ci           | utf8mb4 |  232 |         | Yes      |       8 |
| utf8mb4_turkish_ci           | utf8mb4 |  233 |         | Yes      |       8 |
| utf8mb4_czech_ci             | utf8mb4 |  234 |         | Yes      |       8 |
| utf8mb4_danish_ci            | utf8mb4 |  235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci        | utf8mb4 |  236 |         | Yes      |       8 |
| utf8mb4_slovak_ci            | utf8mb4 |  237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci          | utf8mb4 |  238 |         | Yes      |       8 |
| utf8mb4_roman_ci             | utf8mb4 |  239 |         | Yes      |       8 |
| utf8mb4_persian_ci           | utf8mb4 |  240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci         | utf8mb4 |  241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci         | utf8mb4 |  242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci           | utf8mb4 |  243 |         | Yes      |       8 |
| utf8mb4_german2_ci           | utf8mb4 |  244 |         | Yes      |       8 |
| utf8mb4_croatian_mysql561_ci | utf8mb4 |  245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci       | utf8mb4 |  246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci        | utf8mb4 |  247 |         | Yes      |       8 |
| utf8mb4_croatian_ci          | utf8mb4 |  608 |         | Yes      |       8 |
| utf8mb4_myanmar_ci           | utf8mb4 |  609 |         | Yes      |       8 |
| utf8mb4_unicode_nopad_ci     | utf8mb4 | 1248 |         | Yes      |       8 |
| utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 |         | Yes      |       8 |
+------------------------------+---------+------+---------+----------+---------+

SHOW CREATE PACKAGE BODY employee_tools\G
*************************** 1. row ***************************
        Package body: employee_tools
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
 Create Package Body: CREATE DEFINER="root"@"localhost" PACKAGE BODY "employee_tools" AS
  
  stdRaiseAmount DECIMAL(10,2):=500;
  
  PROCEDURE log (eid INT, ecmnt TEXT) AS
  BEGIN
    INSERT INTO employee_log (id, cmnt) VALUES (eid, ecmnt);
  END;
  
  PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2)) AS
    eid INT;
  BEGIN
    INSERT INTO employee (name, salary) VALUES (ename, esalary);
    eid:= last_insert_id();
    log(eid, 'hire ' || ename);
  END;

  FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2) AS
    nSalary DECIMAL(10,2);
  BEGIN
    SELECT salary INTO nSalary FROM employee WHERE id=eid;
    log(eid, 'getSalary id=' || eid || ' salary=' || nSalary);
    RETURN nSalary;
  END;

  PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2)) AS
  BEGIN
    UPDATE employee SET salary=salary+amount WHERE id=eid;
    log(eid, 'raiseSalary id=' || eid || ' amount=' || amount);
  END;

  PROCEDURE raiseSalaryStd(eid INT) AS
  BEGIN
    raiseSalary(eid, stdRaiseAmount);
    log(eid, 'raiseSalaryStd id=' || eid);
  END;

BEGIN  
  log(0, 'Session ' || connection_id() || ' ' || current_user || ' started');
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SHOW TABLES;
+----------------------+
| Tables_in_test       |
+----------------------+
| animal_count         |
| animals              |
| are_the_mooses_loose |
| aria_test2           |
| t1                   |
| view1                |
+----------------------+

SHOW TABLES WHERE Tables_in_test LIKE 'a%';
+----------------------+
| Tables_in_test       |
+----------------------+
| animal_count         |
| animals              |
| are_the_mooses_loose |
| aria_test2           |
+----------------------+

SHOW VARIABLES WHERE Variable_name LIKE 'aria%' AND Value >8192;
+------------------------------+---------------------+
| Variable_name                | Value               |
+------------------------------+---------------------+
| aria_checkpoint_log_activity | 1048576             |
| aria_log_file_size           | 1073741824          |
| aria_max_sort_file_size      | 9223372036853727232 |
| aria_pagecache_buffer_size   | 134217728           |
| aria_sort_buffer_size        | 134217728           |
+------------------------------+---------------------+

CREATE SEQUENCE s1 START WITH 50;
SHOW CREATE SEQUENCE s1\G;
*************************** 1. row ***************************
       Table: s1
Create Table: CREATE SEQUENCE `s1` start with 50 minvalue 1 maxvalue 9223372036854775806 
  increment by 1 cache 1000 nocycle ENGINE=InnoDB

SHOW CREATE TABLE s1\G
*************************** 1. row ***************************
       Table: s1
Create Table: CREATE TABLE `s1` (
  `next_not_cached_value` bigint(21) NOT NULL,
  `minimum_value` bigint(21) NOT NULL,
  `maximum_value` bigint(21) NOT NULL,
  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created 
     or value if RESTART is used',
  `increment` bigint(21) NOT NULL COMMENT 'increment value',
  `cache_size` bigint(21) unsigned NOT NULL,
  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 
     1 if the sequence should begin a new cycle when maximum_value is passed',
  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=InnoDB SEQUENCE=1

SELECT * FROM s1\G
*************************** 1. row ***************************
next_not_cached_value: 50
        minimum_value: 1
        maximum_value: 9223372036854775806
          start_value: 50
            increment: 1
           cache_size: 1000
         cycle_option: 0
          cycle_count: 0

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES\G
*************************** 1. row ***************************
       SEQUENCE_CATALOG: def
        SEQUENCE_SCHEMA: test
          SEQUENCE_NAME: s1
              DATA_TYPE: bigint
      NUMERIC_PRECISION: 64
NUMERIC_PRECISION_RADIX: 2
          NUMERIC_SCALE: 0
            START_VALUE: 50
          MINIMUM_VALUE: 1
          MAXIMUM_VALUE: 9223372036854775806
              INCREMENT: 1
           CYCLE_OPTION: 0

SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

SHOW GRANTS FOR journalist;
+------------------------------------------+
| Grants for journalist                    |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'journalist'       |
| GRANT DELETE ON `test`.* TO 'journalist' |
+------------------------------------------+

SHOW GRANTS FOR public;
+------------------------------------------------+
| Grants for PUBLIC                              |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON `dev_db`.* TO `PUBLIC` |
+------------------------------------------------+

DELIMITER //

CREATE PROCEDURE p1 ()
  BEGIN
    DECLARE fanta INT DEFAULT 55;
    DROP TABLE t2;
    LOOP
      INSERT INTO t3 VALUES (fanta);
      END LOOP;
  END//
Query OK, 0 rows affected (0.00 sec)

SHOW PROCEDURE CODE p1//
+-----+----------------------------------------+
| Pos | Instruction                            |
+-----+----------------------------------------+
|   0 | set fanta@0 55                         |
|   1 | stmt 9 "DROP TABLE t2"                 |
|   2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" |
|   3 | jump 2                                 |
+-----+----------------------------------------+

SHOW PROCEDURE STATUS LIKE 'p1'\G
*************************** 1. row ***************************
                  Db: test
                Name: p1
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2010-08-23 13:23:03
             Created: 2010-08-23 13:23:03
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

This lets one check how close the optimizer's estimates about the query plan are to the reality. ANALYZE produces an overview, while the ANALYZE FORMAT=JSON command provides a more detailed view of the query plan and the query execution.

LIKE

WHERE

LIKE

SHOW TABLE TYPES

BACKUP STAGE

Control backup phases for external tools. Learn how to cycle through stages like START, BLOCK_DDL, and BLOCK_COMMIT to perform consistent backups with minimal locking.

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool.

Syntax

In the following text, a transactional table means InnoDB, or specifically an InnoDB-like engine with redo log that can lock redo purges and can be copied without locks by an outside process.

Goals with BACKUP STAGE Commands

To be able to do a majority of the backup with the minimum possible server locks. Especially for transactional tables (InnoDB, MyRocks etc) there is only need for a very short block of new commits while copying statistics and log tables.
DDL are only needed to be blocked for a very short duration of the backup while is copying the tables affected by DDL during the initial part of the backup.
Most non transactional tables (those that are not in use) will be copied during BACKUP STAGE START. The exceptions are system statistic and log tables that are not blocked during the backup until BLOCK_COMMIT.

`BACKUP STAGE` Commands

`BACKUP STAGE START`

The START stage is designed for the following tasks:

Blocks purge of redo files for storage engines that needs this (Aria)
Start logging of DDL commands into 'datadir'/ddl.log. This may take a short time as the command has to wait until there are no active DDL commands.

`BACKUP STAGE FLUSH`

The FLUSH stage is designed for the following tasks:

FLUSH all changes for inactive non-transactional tables, except for statistics and log tables.
Close all tables that are not in use, to ensure they are marked as closed for the backup.
BLOCK all new write locks for all non transactional tables (except statistics and log tables). The command will not wait for tables that are in use by read-only transactions.

DDLs don't have to be blocked at this stage as they can't cause the table to be in an inconsistent state. This is true also for non-transactional tables.

`BACKUP STAGE BLOCK_DDL`

The BLOCK_DDL stage is designed for the following tasks:

Wait for all statements using write locked non-transactional tables to end.
Blocks , , , and .
Blocks also start off a new and the final rename phase of . Running ALTER TABLES are not blocked.

`BACKUP STAGE BLOCK_COMMIT`

The BLOCK_COMMIT stage is designed for the following tasks:

Lock the binary log and commit/rollback to ensure that no changes are committed to any tables. If there are active commits or data to be copied to the binary log this will be allowed to finish. Active transactions will not affect BLOCK_COMMIT.
This doesn't lock temporary tables that are not used by replication. However these will be blocked when it's time to write to the binary log.
Lock system log tables and statistics tables, flush them and mark them closed.

When the BLOCK_COMMIT's stages return, this is the 'backup time'. Everything committed will be in the backup and everything not committed will roll back.

Transactional engines will continue to do changes to the redo log during the BLOCK COMMIT stage, but this is not important as all of these will roll back later as the changes will not be committed.

`BACKUP STAGE END`

The END stage is designed for the following tasks:

End DDL logging
Free resources

Using `BACKUP STAGE` Commands with Backup Tools

Using `BACKUP STAGE` Commands with mariadb-backup

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool.

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

See for some examples on how uses these commands.

Using `BACKUP STAGE` Commands with Storage Snapshots

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool. These commands could even be used by tools that perform backups by taking a snapshot of a file system, SAN, or some other kind of storage device. See for some examples on how to use each BACKUP STAGE command in an efficient way.

Privileges

BACKUP STAGE requires the privilege.

Notes

Only one connection can run BACKUP STAGE START. If a second connection tries, it will wait until the first one has executed BACKUP STAGE END.
If the user skips a BACKUP STAGE, then all intermediate backup stages will automatically be run. This will allow us to add new stages within the BACKUP STAGE hierarchy in the future with even more precise locks without causing problems for tools using an earlier version of the BACKUP STAGE implementation.

SHOW AUTHORS

Display a list of major contributors to the MariaDB and MySQL projects. View names, locations, and specific contributions of developers.

Syntax

SHOW AUTHORS

Description

The SHOW AUTHORS statement displays information about the people who work on MariaDB. For each author, it displays Name, Location, and Comment values. All columns are encoded as latin1.

These include:

First the active people in MariaDB are listed.
Then the active people in MySQL.
Last the people that have contributed to MariaDB/MySQL in the past.

The order is somewhat related to importance of the contribution given to the MariaDB project, but this is not 100% accurate. There is still room for improvement and debate...

Example

SHOW CREATE TABLE

Get the SQL statement to recreate a table. This statement shows the complete CREATE TABLE syntax, including column definitions and indexes.

Syntax

Description

Shows the CREATE TABLE statement that creates the given table. The statement requires the SELECT privilege for the table. This statement also works with and .

SHOW CREATE TABLE quotes table and column names according to the value of the server system variable.

Certain values can result in parts of the original CREATE statement not being included in the output. MariaDB-specific table options, column options, and index options are not included in the output of this statement if the , and flags are used. All MariaDB-specific table attributes are also not shown when a non-MariaDB/MySQL emulation mode is used, which includes , , , , or .

Invalid table options, column options and index options are normally commented out (note, that it is possible to create a table with invalid options, by altering a table of a different engine, where these options were valid). To have them uncommented, enable the . Remember that replaying a statement with uncommented invalid options will fail with an error, unless the is in effect.

Note that SHOW CREATE TABLE is not meant to provide metadata about a table. It provides information about how the table was declared, but the real table structure could differ a bit. For example, if an index has been declared as HASH, the CREATE TABLE statement returned by SHOW CREATE TABLE will declare that index as HASH; however, it is possible that the index is in fact a BTREE, because the storage engine does not support HASH.

MariaDB permits and data types to be assigned a value. As a result, SHOW CREATE TABLE will append a DEFAULT NULL to nullable TEXT or BLOB fields if no specific default is provided.

Numbers are quoted in the DEFAULT clause in SHOW CREATE statement.

Numbers are not quoted in the DEFAULT clause in SHOW CREATE statement.

Index Order

Indexes are sorted and displayed in the following order, which may differ from the order of the CREATE TABLE statement.

PRIMARY KEY
UNIQUE keys where all column are NOT NULL
UNIQUE keys that don't contain partial segments
Other UNIQUE keys

See sql/sql_table.cc for details.

Examples

With off:

impacting the output:

"table": {
    "table_name": "t1",
    "access_type": "range",
    "possible_keys": ...,
    "key": "INDEX1",
    ...
    "rowid_filter": {
      ...
      "r_selectivity_pct": n.nnn,
    },
    ...
    "rows": 123,
    "r_rows": 125,
    ...
    "filtered": 8.476269722,
    "r_filtered": 100,
    "index_condition": "cond1",
    "attached_condition": "cond2"
  }

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: range
possible_keys: key1
          key: key1
      key_len: 5
          ref: NULL
         rows: 181
       r_rows: 181
     filtered: 100.00
   r_filtered: 10.50
        Extra: Using index condition; Using where

+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref                | rows   | r_rows | filtered | r_filtered | Extra       |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
|  1 | SIMPLE      | customer | ALL  | PRIMARY,...   | NULL        | NULL    | NULL               | 149095 | 150000 |    18.08 |       9.13 | Using where |
|  1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | customer.c_custkey |      7 |     10 |   100.00 |      30.03 | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+

+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref                | rows   | r_rows | filtered | r_filtered | Extra       |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
|  1 | SIMPLE      | customer | ALL  | PRIMARY,...   | NULL        | NULL    | NULL               | 149095 | 150000 |    18.08 |       0.00 | Using where |
|  1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | customer.c_custkey |      7 |   NULL |   100.00 |       NULL | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+

STOP { SLAVE | REPLICA } ["connection_name"] [thread_type [, thread_type] ... ] 
[FOR CHANNEL "connection_name"]

STOP ALL { SLAVES | REPLICAS } [thread_type [, thread_type]]

STOP { SLAVE | REPLICA } ["connection_name"] [thread_type [, thread_type] ... ]

STOP ALL { SLAVES | REPLICAS } [thread_type [, thread_type]] 

thread_type: IO_THREAD | SQL_THREAD

SHOW COLUMNS FROM city;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

SHOW COLUMNS FROM employees WHERE Type LIKE 'Varchar%';
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| first_name    | varchar(30) | NO   | MUL | NULL    |       |
| last_name     | varchar(40) | NO   |     | NULL    |       |
| position      | varchar(25) | NO   |     | NULL    |       |
| home_address  | varchar(50) | NO   |     | NULL    |       |
| home_phone    | varchar(12) | NO   |     | NULL    |       |
| employee_code | varchar(25) | NO   | UNI | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: FEDERATED
     Support: YES
     Comment: FederatedX pluggable storage engine
Transactions: YES
          XA: NO
  Savepoints: YES
*************************** 6. row ***************************
      Engine: MRG_MyISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 10. row ***************************
      Engine: Aria
     Support: YES
     Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
          XA: NO
  Savepoints: NO
10 rows in set (0.00 sec)

SHOW PACKAGE BODY STATUS LIKE 'pkg1'\G
*************************** 1. row ***************************
                  Db: test
                Name: pkg1
                Type: PACKAGE BODY
             Definer: root@localhost
            Modified: 2018-02-27 14:44:14
             Created: 2018-02-27 14:44:14
       Security_type: DEFINER
             Comment: This is my first package body
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SHOW INDEX_STATISTICS;
+--------------+-------------------+------------+-----------+
| Table_schema | Table_name        | Index_name | Rows_read |
+--------------+-------------------+------------+-----------+
| test         | employees_example | PRIMARY    |         1 |
+--------------+-------------------+------------+-----------+

SHOW AUTHORS\G
*************************** 1. row ***************************
    Name: Michael (Monty) Widenius
Location: Tusby, Finland
 Comment: Lead developer and main author
*************************** 2. row ***************************
    Name: Sergei Golubchik
Location: Kerpen, Germany
 Comment: Architect, Full-text search, precision math, plugin framework, merges etc
*************************** 3. row ***************************
    Name: Igor Babaev
Location: Bellevue, USA
 Comment: Optimizer, keycache, core work
*************************** 4. row ***************************
    Name: Sergey Petrunia
Location: St. Petersburg, Russia
 Comment: Optimizer
*************************** 5. row ***************************
    Name: Oleksandr Byelkin
Location: Lugansk, Ukraine
 Comment: Query Cache (4.0), Subqueries (4.1), Views (5.0)
*************************** 6. row ***************************
    Name: Timour Katchaounov
Location: Sofia , Bulgaria
 Comment: Optimizer
*************************** 7. row ***************************
    Name: Kristian Nielsen
Location: Copenhagen, Denmark
 Comment: Replication, Async client prototocol, General buildbot stuff
*************************** 8. row ***************************
    Name: Alexander (Bar) Barkov
Location: Izhevsk, Russia
 Comment: Unicode and character sets
*************************** 9. row ***************************
    Name: Alexey Botchkov (Holyfoot)
Location: Izhevsk, Russia
 Comment: GIS extensions, embedded server, precision math
*************************** 10. row ***************************
    Name: Daniel Bartholomew
Location: Raleigh, USA
 Comment: MariaDB documentation, Buildbot, releases
*************************** 11. row ***************************
    Name: Colin Charles
Location: Selangor, Malesia
 Comment: MariaDB documentation, talks at a LOT of conferences
*************************** 12. row ***************************
    Name: Sergey Vojtovich
Location: Izhevsk, Russia
 Comment: initial implementation of plugin architecture, maintained native storage engines (MyISAM, MEMORY, ARCHIVE, etc), rewrite of table cache
*************************** 13. row ***************************
    Name: Vladislav Vaintroub
Location: Mannheim, Germany
 Comment: MariaDB Java connector, new thread pool, Windows optimizations
*************************** 14. row ***************************
    Name: Elena Stepanova
Location: Sankt Petersburg, Russia
 Comment: QA, test cases
*************************** 15. row ***************************
    Name: Georg Richter
Location: Heidelberg, Germany
 Comment: New LGPL C connector, PHP connector
*************************** 16. row ***************************
    Name: Jan Lindström
Location: Ylämylly, Finland
 Comment: Working on InnoDB
*************************** 17. row ***************************
    Name: Lixun Peng
Location: Hangzhou, China
 Comment: Multi Source replication
*************************** 18. row ***************************
    Name: Olivier Bertrand
Location: Paris, France
 Comment: CONNECT storage engine
*************************** 19. row ***************************
    Name: Kentoku Shiba
Location: Tokyo, Japan
 Comment: Spider storage engine, metadata_lock_info Information schema
*************************** 20. row ***************************
    Name: Percona
Location: CA, USA
 Comment: XtraDB, microslow patches, extensions to slow log
*************************** 21. row ***************************
    Name: Vicentiu Ciorbaru
Location: Bucharest, Romania
 Comment: Roles
*************************** 22. row ***************************
    Name: Sudheera Palihakkara
Location: 
 Comment: PCRE Regular Expressions
*************************** 23. row ***************************
    Name: Pavel Ivanov
Location: USA
 Comment: Some patches and bug fixes
*************************** 24. row ***************************
    Name: Konstantin Osipov
Location: Moscow, Russia
 Comment: Prepared statements (4.1), Cursors (5.0), GET_LOCK (10.0)
*************************** 25. row ***************************
    Name: Ian Gilfillan
Location: South Africa
 Comment: MariaDB documentation
*************************** 26. row ***************************
    Name: Federico Razolli
Location: Italy
 Comment: MariaDB documentation Italian translation
*************************** 27. row ***************************
    Name: Guilhem Bichot
Location: Bordeaux, France
 Comment: Replication (since 4.0)
*************************** 28. row ***************************
    Name: Andrei Elkin
Location: Espoo, Finland
 Comment: Replication
*************************** 29. row ***************************
    Name: Dmitri Lenev
Location: Moscow, Russia
 Comment: Time zones support (4.1), Triggers (5.0)
*************************** 30. row ***************************
    Name: Marc Alff
Location: Denver, CO, USA
 Comment: Signal, Resignal, Performance schema
*************************** 31. row ***************************
    Name: Mikael Ronström
Location: Stockholm, Sweden
 Comment: NDB Cluster, Partitioning, online alter table
*************************** 32. row ***************************
    Name: Ingo Strüwing
Location: Berlin, Germany
 Comment: Bug fixing in MyISAM, Merge tables etc
*************************** 33. row ***************************
    Name: Marko Mäkelä
Location: Helsinki, Finland
 Comment: InnoDB core developer
...

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s` char(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id int(11) NOT NULL AUTO_INCREMENT,
  s char(60) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+

CREATE TABLE `t1` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `msg` varchar(100) DEFAULT NULL,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SET SQL_MODE=ORACLE;

SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE "t1" (
  "id" int(11) NOT NULL,
  "msg" varchar(100) DEFAULT NULL,
  PRIMARY KEY ("id")

SHOW PLUGINS;
+----------------------------+----------+--------------------+-------------+---------+
| Name                       | Status   | Type               | Library     | License |
+----------------------------+----------+--------------------+-------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL        | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL        | GPL     |
| MRG_MyISAM                 | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| FEDERATED                  | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| Aria                       | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL        | GPL     |
...
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL        | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL        | GPL     |
| SPHINX                     | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| FEEDBACK                   | DISABLED | INFORMATION SCHEMA | NULL        | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL        | GPL     |
| pam                        | ACTIVE   | AUTHENTICATION     | auth_pam.so | GPL     |
+----------------------------+----------+--------------------+-------------+---------+

+------+-------------+----------+------+---------------+---------+---------+------------------+--------+-------------+
| id   | select_type | table    | type | possible_keys | key     | key_len | ref              | rows   | Extra       |
+------+-------------+----------+------+---------------+---------+---------+------------------+--------+-------------+
|    1 | SIMPLE      | customer | ALL  | PRIMARY       | NULL    | NULL    | NULL             | 199786 | Using where |
|    1 | SIMPLE      | orders   | ref  | cust_id       | cust_id | 5       | customer.cust_id | 1      |             |
+------+-------------+----------+------+---------------+---------+---------+------------------+--------+-------------+

"query_block": {
    "select_id": 1,
    "r_loops": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "customer",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "r_loops": 1,
          "rows": 199786,
          "r_rows": 110544,

"filtered": 100,
          "r_filtered": 9.538283398,
          "attached_condition": "customer.`status` = 'GOLD'"
        }
      },
      {
        "table": {
          "table_name": "orders",
          "access_type": "ref",
          "possible_keys": ["cust_id"],
          "key": "cust_id",
          "key_length": "5",
          "used_key_parts": ["cust_id"],
          "ref": ["test.customer.cust_id"],
          "r_loops": 10544,
          "rows": 1,
          "r_rows": 99.99222307,

Connection 2> SHOW ANALYZE FORMAT=JSON FOR <connection_id>;
ANALYZE
{
  "r_query_time_in_progress_ms": 30727,
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "customer",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "r_loops": 1,
          "rows": 199786,
          "r_rows": 109994,
          "r_table_time_ms": 232.699,
          "r_other_time_ms": 46.355,

"filtered": 100,
          "r_filtered": 9.085950143,
          "attached_condition": "customer.`status` = 'GOLD'"
        }
      },
      {
        "table": {
          "table_name": "orders",
          "access_type": "ref",
          "possible_keys": ["cust_id"],
          "key": "cust_id",
          "key_length": "5",
          "used_key_parts": ["cust_id"],
          "ref": ["test.customer.cust_id"],
          "r_loops": 9994,
          "rows": 1,
          "r_rows": 99.99779868,
          "r_table_time_ms": 29460.609,
          "r_other_time_ms": 986.842,

=====================================
2019-09-06 12:44:13 0x7f93cc236700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 83698 srv_idle
srv_master_thread log flush and writes: 83682
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 15
OS WAIT ARRAY INFO: signal count 8
RW-shared spins 0, rounds 20, OS waits 7
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 20.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 236
Purge done for trx's n:o < 236 undo n:o < 0 state: running
History list length 22
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421747401994584, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421747401990328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
286 OS file reads, 171 OS file writes, 22 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 445926
Log flushed up to   445926
Pages flushed up to 445926
Last checkpoint at  445917
0 pending log flushes, 0 pending chkp writes
18 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 167772160
Dictionary memory allocated 50768
Buffer pool size   8012
Free buffers       7611
Database pages     401
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 264, created 137, written 156
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 401, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=4267, Main thread ID=140272021272320, state: sleeping
Number of rows inserted 1, updated 0, deleted 0, read 1
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL | 1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)

SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message                |
+-------+------+------------------------+
| Note  | 1003 | select sum(a) from tbl |
+-------+------+------------------------+
1 row in set (0.00 sec)

utf8mb4 is the default for the affected variables:

SET NAMES DEFAULT;                

SELECT VARIABLE_NAME, SESSION_VALUE 
    FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
    VARIABLE_NAME LIKE 'character_set_con%' OR 
    VARIABLE_NAME LIKE 'character_set_cl%' OR 
    VARIABLE_NAME LIKE

The utf8 character set (and related collations) is an alias for utf8mb3 , rather than the other way around. MariaDB 11.4 added the character_set_collations variable, so the SELECT query is more specific in this example:

SELECT VARIABLE_NAME, SESSION_VALUE 
    FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
    VARIABLE_NAME LIKE 'character_set_con%' OR 
    VARIABLE_NAME LIKE 'character_set_cl%' OR 
    VARIABLE_NAME LIKE 'character_set_re%' OR 
    VARIABLE_NAME

The utf8 character set (and related collation) is the default for the given variables:

SHOW CREATE PROCEDURE test.simpleproc\G
*************************** 1. row ***************************
           Procedure: simpleproc
            sql_mode: 
    Create Procedure: CREATE PROCEDURE `simpleproc`(OUT param1 INT)
                      BEGIN
                      SELECT COUNT(*) INTO param1 FROM t;
                      END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode:
     Create Function: CREATE FUNCTION `hello`(s CHAR(20))
                      RETURNS CHAR(50)
                      RETURN CONCAT('Hello, ',s,'!')
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | [GLOBAL | SESSION] system_var_name = expr
    | [@@global. | @@session. | @@]system_var_name = expr

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 64            | 64           |
| SKIP_PARALLEL_REPLICATION | OFF           | NULL         |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 30           |
+---------------------------+---------------+--------------+

SET max_error_count=128;Query OK, 0 rows affected (0.000 sec)

SET skip_parallel_replication=ON;Query OK, 0 rows affected (0.000 sec)

SET innodb_sync_spin_loops=60;
ERROR 1229 (HY000): Variable 'innodb_sync_spin_loops' is a GLOBAL variable 
  and should be set with SET GLOBAL

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 128           | 64           |
| SKIP_PARALLEL_REPLICATION | ON            | NULL         |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 30           |
+---------------------------+---------------+--------------+

SET GLOBAL max_error_count=256;

SET GLOBAL skip_parallel_replication=ON;
ERROR 1228 (HY000): Variable 'skip_parallel_replication' is a SESSION variable 
  and can't be used with SET GLOBAL

SET GLOBAL innodb_sync_spin_loops=120;

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 128           | 256          |
| SKIP_PARALLEL_REPLICATION | ON            | NULL         |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 120          |
+---------------------------+---------------+--------------+

SHOW VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 128   |
+-----------------+-------+

SHOW VARIABLES LIKE 'skip_parallel_replication';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| skip_parallel_replication | ON    |
+---------------------------+-------+

SHOW VARIABLES LIKE 'innodb_sync_spin_loops';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_sync_spin_loops | 120   |
+------------------------+-------+

SELECT VARIABLE_NAME, SESSION_VALUE 
  FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME LIKE 'character_set_c%' OR 
  VARIABLE_NAME LIKE 'character_set_re%' OR 
  VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+-----------------+
| VARIABLE_NAME            | SESSION_VALUE   |
+--------------------------+-----------------+
| CHARACTER_SET_RESULTS    | utf8            |
| CHARACTER_SET_CONNECTION | utf8            |
| CHARACTER_SET_CLIENT     | utf8            |
| COLLATION_CONNECTION     | utf8_general_ci |
+--------------------------+-----------------+

SET NAMES big5;

SELECT VARIABLE_NAME, SESSION_VALUE 
  FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME LIKE 'character_set_c%' OR 
  VARIABLE_NAME LIKE 'character_set_re%' OR 
  VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+-----------------+
| VARIABLE_NAME            | SESSION_VALUE   |
+--------------------------+-----------------+
| CHARACTER_SET_RESULTS    | big5            |
| CHARACTER_SET_CONNECTION | big5            |
| CHARACTER_SET_CLIENT     | big5            |
| COLLATION_CONNECTION     | big5_chinese_ci |
+--------------------------+-----------------+

SET NAMES 'latin1' COLLATE 'latin1_bin';

SELECT VARIABLE_NAME, SESSION_VALUE 
  FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME LIKE 'character_set_c%' OR 
  VARIABLE_NAME LIKE 'character_set_re%' OR 
  VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+---------------+
| VARIABLE_NAME            | SESSION_VALUE |
+--------------------------+---------------+
| CHARACTER_SET_RESULTS    | latin1        |
| CHARACTER_SET_CONNECTION | latin1        |
| CHARACTER_SET_CLIENT     | latin1        |
| COLLATION_CONNECTION     | latin1_bin    |
+--------------------------+---------------+

SET NAMES DEFAULT;

SELECT VARIABLE_NAME, SESSION_VALUE 
  FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME LIKE 'character_set_c%' OR 
  VARIABLE_NAME LIKE 'character_set_re%' OR 
  VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+-------------------+
| VARIABLE_NAME            | SESSION_VALUE     |
+--------------------------+-------------------+
| CHARACTER_SET_RESULTS    | latin1            |
| CHARACTER_SET_CONNECTION | latin1            |
| CHARACTER_SET_CLIENT     | latin1            |
| COLLATION_CONNECTION     | latin1_swedish_ci |
+--------------------------+-------------------+

SET [GLOBAL | SESSION] TRANSACTION
    transaction_property [, transaction_property] ...

transaction_property:
    ISOLATION LEVEL level
  | READ WRITE
  | READ ONLY

level:
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE

SHOW PRIVILEGES;
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
| Privilege                | Context                               | Comment                                                            |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
| Alter                    | Tables                                | To alter the table                                                 |
| Alter routine            | Functions,Procedures                  | To alter or drop stored functions/procedures                       |
| Create                   | Databases,Tables,Indexes              | To create new databases and tables                                 |
| Create routine           | Databases                             | To use CREATE FUNCTION/PROCEDURE                                   |
| Create temporary tables  | Databases                             | To use CREATE TEMPORARY TABLE                                      |
| Create view              | Tables                                | To create new views                                                |
| Create user              | Server Admin                          | To create new users                                                |
| Delete                   | Tables                                | To delete existing rows                                            |
| Delete history           | Tables                                | To delete versioning table historical rows                         |
| Drop                     | Databases,Tables                      | To drop databases, tables, and views                               |
| Event                    | Server Admin                          | To create, alter, drop and execute events                          |
| Execute                  | Functions,Procedures                  | To execute stored routines                                         |
| File                     | File access on server                 | To read and write files on the server                              |
| Grant option             | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess                |
| Index                    | Tables                                | To create or drop indexes                                          |
| Insert                   | Tables                                | To insert data into tables                                         |
| Lock tables              | Databases                             | To use LOCK TABLES (together with SELECT privilege)                |
| Process                  | Server Admin                          | To view the plain text of currently executing queries              |
| Proxy                    | Server Admin                          | To make proxy user possible                                        |
| References               | Databases,Tables                      | To have references on tables                                       |
| Reload                   | Server Admin                          | To reload or refresh tables, logs and privileges                   |
| Binlog admin             | Server                                | To purge binary logs                                               |
| Binlog monitor           | Server                                | To use SHOW BINLOG STATUS and SHOW BINARY LOG                      |
| Binlog replay            | Server                                | To use BINLOG (generated by mariadb-binlog)                        |
| Replication master admin | Server                                | To monitor connected slaves                                        |
| Replication slave admin  | Server                                | To start/stop slave and apply binlog events                        |
| Slave monitor            | Server                                | To use SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS                  |
| Replication slave        | Server Admin                          | To read binary log events from the master                          |
| Select                   | Tables                                | To retrieve rows from table                                        |
| Show databases           | Server Admin                          | To see all databases with SHOW DATABASES                           |
| Show view                | Tables                                | To see views with SHOW CREATE VIEW                                 |
| Shutdown                 | Server Admin                          | To shut down the server                                            |
| Super                    | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.                |
| Trigger                  | Tables                                | To use triggers                                                    |
| Create tablespace        | Server Admin                          | To create/alter/drop tablespaces                                   |
| Update                   | Tables                                | To update existing rows                                            |
| Set user                 | Server                                | To create views and stored routines with a different definer       |
| Federated admin          | Server                                | To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements |
| Connection admin         | Server                                | To bypass connection limits and kill other users' connections      |
| Read_only admin          | Server                                | To perform write operations even if @@read_only=ON                 |
| Usage                    | Server Admin                          | No privileges - allow connect only                                 |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
41 rows in set (0.000 sec)

CREATE TABLE IF NOT EXISTS `employees_example` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(30) NOT NULL,
  `last_name` VARCHAR(40) NOT NULL,
  `position` VARCHAR(25) NOT NULL,
  `home_address` VARCHAR(50) NOT NULL,
  `home_phone` VARCHAR(12) NOT NULL,
  `employee_code` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_code` (`employee_code`),
  KEY `first_name` (`first_name`,`last_name`)
) ENGINE=Aria;

INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
  VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492', 'MM1'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847', 'HF1'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1'),
  ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349', 'LC1'),
  ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329', 'FC1'),
  ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478', 'HW1');

SHOW INDEXES FROM employees_example\G
*************************** 1. row ***************************
        Table: employees_example
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: NO
*************************** 2. row ***************************
        Table: employees_example
   Non_unique: 0
     Key_name: employee_code
 Seq_in_index: 1
  Column_name: employee_code
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: NO
*************************** 3. row ***************************
        Table: employees_example
   Non_unique: 1
     Key_name: first_name
 Seq_in_index: 1
  Column_name: first_name
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: NO
*************************** 4. row ***************************
        Table: employees_example
   Non_unique: 1
     Key_name: first_name
 Seq_in_index: 2
  Column_name: last_name
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: NO

EXPLAIN

Syntax

Description

The EXPLAIN statement can be used either as a synonym for or as a way to obtain information about how MariaDB executes a SELECT, UPDATE or DELETE statement:

'EXPLAIN tbl_name' is synonymous with'[DESCRIBE](../describe.md) tbl_name' or'[SHOW COLUMNS](../show/show-columns.md) FROM tbl_name'.
When you precede a SELECT, UPDATE or a DELETE statement with the keywordEXPLAIN, MariaDB displays information from the optimizer about the query execution plan. That is, MariaDB explains how it would process the SELECT

shows the output of a running statement. In some cases, its output can be closer to reality than EXPLAIN.

The runs a statement and returns information about its execution plan. It also shows additional columns, to check how much the optimizer's estimation about filtering and found rows are close to reality.

There is an online that you can use to share EXPLAIN and EXPLAIN EXTENDED output with others.

EXPLAIN can acquire metadata locks in the same way that SELECT does, as it needs to know table metadata and, sometimes, data as well.

Columns in EXPLAIN ... SELECT

Column name

Description

Here are descriptions of the values for some of the more complex columns in EXPLAIN ... SELECT:

"Select_type" Column

The select_type column can have the following values:

Value

Description

Comment

"Type" Column

This column contains information on how the table is accessed.

Value

Description

"Extra" Column

This column consists of one or more of the following values, separated by ';'

Note that some of these values are detected after the optimization phase.

The optimization phase can do the following changes to the WHERE clause:

Add the expressions from the ON and USING clauses to the WHERE clause.
Constant propagation: If there is column=constant, replace all column instances with this constant.
Replace all columns from 'const' tables with their values.

Value

Description

EXPLAIN EXTENDED

The EXTENDED keyword adds another column, filtered, to the output. This is a percentage estimate of the table rows that will be filtered by the condition.

An EXPLAIN EXTENDED will always throw a warning, as it adds extra Message information to a subsequent statement. This includes what the SELECT query would look like after optimizing and rewriting rules are applied and how the optimizer qualifies columns and tables.

Examples

As synonym for DESCRIBE or SHOW COLUMNS FROM:

A simple set of examples to see how EXPLAIN can identify poor index usage:

SELECT on a primary key:

The type is const, which means that only one possible result could be returned. Now, returning the same record but searching by their phone number:

Here, the type is All, which means no index could be used. Looking at the rows count, a full table scan (all six rows) had to be performed in order to retrieve the record. If it's a requirement to search by phone number, an index will have to be created.

example:

Example of `ref_or_null` Optimization

ref_or_null is something that often happens when you use subqueries with NOT IN as then one has to do an extra check for NULL values if the first value didn't have a matching row.

CHANGE MASTER TO

Syntax

Description

CHANGE MASTER is used on a replica to set up or change settings for connecting to the primary.

The FOR CHANNEL keyword was added for MySQL compatibility. This is identical to using the channel_name directly after CHANGE MASTER.

FOR CHANNEL is not available.

Multi-Source Replication

If you are using , then you need to specify a connection name when you execute CHANGE MASTER. There are two ways to do this:

Setting the system variable prior to executing CHANGE MASTER.
Setting the connection_name parameter when executing CHANGE MASTER.

default_master_connection

connection_name

Options

Connection Options

MASTER_USER

The MASTER_USER option for CHANGE MASTER defines the user account that the will use to connect to the .

This user account will need the privilege on the primary.

For example:

The maximum length of the MASTER_USER string is 128 characters.

The maximum length of the MASTER_USER string is 96 characters.

MASTER_PASSWORD

The MASTER_PASSWORD option for CHANGE MASTER defines the password that the will use to connect to the as the user account defined by the option.

For example:

The maximum length of the MASTER_PASSWORD string is 32 characters. The effective maximum length of the string depends on how many bytes are used per character and can be up to 96 characters.

Due to , the password can be silently truncated to 41 characters when MariaDB is restarted. For this reason, it is recommended to use a password that is shorter than this.

MASTER_HOST

The MASTER_HOST option for CHANGE MASTER defines the hostname or IP address of the .

If you set the value of the MASTER_HOST option to the empty string, then that is not the same as not setting the option's value at all. If you set the value of the MASTER_HOST option to the empty string, then the CHANGE MASTER command will fail with an error. In MariaDB 5.3 and before, if you set the value of the MASTER_HOST option to the empty string, then the CHANGE MASTER command would succeed, but the subsequent command would fail.

For example:

If you set the value of the MASTER_HOST option in a CHANGE MASTER command, then the replica assumes that the primary is different from before, even if you set the value of this option to the same value it had previously. In this scenario, the replica will consider the old values for the primary's file name and position to be invalid for the new primary. As a side effect, if you do not explicitly set the values of the and options in the statement, then the statement will be implicitly appended with MASTER_LOG_FILE='' and MASTER_LOG_POS=4. However, if you enable mode for replication by setting the option to some value other than no in the statement, then these values will effectively be ignored anyway.

Replicas cannot connect to primaries using Unix socket files or Windows named pipes. The replica must connect to the primary using TCP/IP.

The maximum length of the MASTER_HOST string is 255 characters.

The maximum length of the MASTER_HOST string is 60 characters.

MASTER_PORT

The MASTER_PORT option for CHANGE MASTER defines the TCP/IP port of the .

For example:

If you set the value of the MASTER_PORT option in a CHANGE MASTER command, then the replica assumes that the primary is different from before, even if you set the value of this option to the same value it had previously. In this scenario, the replica will consider the old values for the primary's file name and position to be invalid for the new primary. As a side effect, if you do not explicitly set the values of the and options in the statement, then the statement will be implicitly appended with MASTER_LOG_FILE='' and MASTER_LOG_POS=4. However, if you enable mode for replication by setting the option to some value other than no in the statement, then these values will effectively be ignored anyway.

Replicas cannot connect to primaries using Unix socket files or Windows named pipes. The replica must connect to the primary using TCP/IP.

MASTER_CONNECT_RETRY

The MASTER_CONNECT_RETRY option for CHANGE MASTER defines how many seconds that the replica will wait between connection retries. The default is 60.

MASTER_RETRY_COUNT

The MASTER_RETRY_COUNT option limits the number of connection attempts (i.e., Connects_Tried in ). For example:

Setting this option resets the Connects_Tried statistic in to 0.

The default is the option, which be set either on the command-line or in a server in an prior to starting up the server. For example:

The MASTER_RETRY_COUNT option for CHANGE MASTER is only supported by MariaDB 12.0.1 and later and by MySQL. Please use the option instead, which be set either on the command-line or in a server in an prior to starting up the server. For example:

MASTER_BIND

The MASTER_BIND option for CHANGE MASTER is only supported by MySQL 5.6.2 and later and by MySQL NDB Cluster 7.3.1 and later. This option is not supported by MariaDB. See for more information.

The MASTER_BIND option for CHANGE MASTER can be used on replicas that have multiple network interfaces to choose which network interface the replica will use to connect to the primary.

MASTER_HEARTBEAT_PERIOD

The MASTER_HEARTBEAT_PERIOD option for CHANGE MASTER can be used to set the interval in seconds between replication heartbeats. Whenever the primary's is updated with an event, the waiting period for the next heartbeat is reset.

This option's interval argument has the following characteristics:

It is a decimal value with a range of 0 to 4294967 seconds.
It has a resolution of hundredths of a second.
Its smallest valid non-zero value is 0.001.

Heartbeats are sent by the primary only if there are no unsent events in the binary log file for a period longer than the interval.

If the statement is executed, then the heartbeat interval is reset to the default.

If the system variable is set to a value that is lower than the current heartbeat interval, then a warning will be issued.

TLS Options

The TLS options are used for providing information about . The options can be set even on replicas that are compiled without TLS support. The TLS options are saved to either the default master.info file or the file that is configured by the option, but these TLS options are ignored unless the replica supports TLS.

See for more information.

MASTER_SSL

The MASTER_SSL option for CHANGE MASTER tells the replica whether to force for the connection. The valid values are 0 or 1. Required to be set to 1 for the other MASTER_SSL* options to have any effect.

For example:

MASTER_SSL_CA

The MASTER_SSL_CA option for CHANGE MASTER defines a path to a PEM file that should contain one or more X509 certificates for trusted Certificate Authorities (CAs) to use for . This option requires that you use the absolute path, not a relative path.

For example:

See for more information.

The maximum length of MASTER_SSL_CA string is 511 characters.

MASTER_SSL_CAPATH

The MASTER_SSL_CAPATH option for CHANGE MASTER defines a path to a directory that contains one or more PEM files that should each contain one X509 certificate for a trusted Certificate Authority (CA) to use for . This option requires that you use the absolute path, not a relative path. The directory specified by this option needs to be run through the command.

For example:

See for more information.

The maximum length of MASTER_SSL_CA_PATH string is 511 characters.

MASTER_SSL_CERT

The MASTER_SSL_CERT option for CHANGE MASTER defines a path to the X509 certificate file to use for . This option requires that you use the absolute path, not a relative path.

For example:

The maximum length of MASTER_SSL_CERT string is 511 characters.

MASTER_SSL_CRL

The MASTER_SSL_CRL option for CHANGE MASTER defines a path to a PEM file that should contain one or more revoked X509 certificates to use for . This option requires that you use the absolute path, not a relative path.

This option is only supported if the server was built with OpenSSL. If the server was built with yaSSL, then this option is not supported. See for more information about which libraries are used on which platforms.

For example:

See for more information.

The maximum length of MASTER_SSL_CRL string is 511 characters.

MASTER_SSL_CRLPATH

The MASTER_SSL_CRLPATH option for CHANGE MASTER defines a path to a directory that contains one or more PEM files that should each contain one revoked X509 certificate to use for . This option requires that you use the absolute path, not a relative path. The directory specified by this variable needs to be run through the command.

For example:

See for more information.

The maximum length of MASTER_SSL_CRL_PATH string is 511 characters.

MASTER_SSL_KEY

The MASTER_SSL_KEY option for CHANGE MASTER defines a path to a private key file to use for . This option requires that you use the absolute path, not a relative path.

For example:

The maximum length of MASTER_SSL_KEY string is 511 characters.

MASTER_SSL_CIPHER

The MASTER_SSL_CIPHER option for CHANGE MASTER defines the list of permitted ciphers or cipher suites to use for . Besides cipher names, if MariaDB was compiled with OpenSSL, this option could be set to "SSLv3" or "TLSv1.2" to allow all SSLv3 or all TLSv1.2 ciphers. Note that the TLSv1.3 ciphers cannot be excluded when using OpenSSL, even by using this option. See for details.

For example:

The maximum length of MASTER_SSL_CIPHER string is 511 characters.

MASTER_SSL_VERIFY_SERVER_CERT

The MASTER_SSL_VERIFY_SERVER_CERT option for CHANGE MASTER enables . This option is enabled by default.

The MASTER_SSL_VERIFY_SERVER_CERT option for CHANGE MASTER enables . This option is disabled by default.

For example:

See for more information.

Binary Log Options

These options are related to the position on the primary.

MASTER_LOG_FILE

The MASTER_LOG_FILE option for CHANGE MASTER can be used along with MASTER_LOG_POS to specify the coordinates at which the should begin reading from the primary's the next time the thread starts.

For example:

The and options cannot be specified if the and options were also specified.

The and options are effectively ignored if you enable mode for replication by setting the option to some value other than no in the statement.

MASTER_LOG_POS

The MASTER_LOG_POS option for CHANGE MASTER can be used along with MASTER_LOG_FILE to specify the coordinates at which the should begin reading from the primary's the next time the thread starts.

For example:

The and options cannot be specified if the and options were also specified.

The and options are effectively ignored if you enable mode for replication by setting the option to some value other than no in the statement.

Relay Log Options

These options are related to the position on the replica.

RELAY_LOG_FILE

The RELAY_LOG_FILE option for CHANGE MASTER can be used along with the option to specify the coordinates at which the should begin reading from the the next time the thread starts.

The CHANGE MASTER statement usually deletes all files. However, if the RELAY_LOG_FILE and/or RELAY_LOG_POS options are specified, then existing files are kept.

When you want to change the position, you only need to stop the . The can continue running. The and statements support the SQL_THREAD option for this scenario. For example:

When the value of this option is changed, the metadata about the position in the will also be changed in the relay-log.info file or the file that is configured by the system variable.

The and options cannot be specified if the and options were also specified.

RELAY_LOG_POS

The RELAY_LOG_POS option for CHANGE MASTER can be used along with the option to specify the coordinates at which the should begin reading from the the next time the thread starts.

The CHANGE MASTER statement usually deletes all files. However, if the RELAY_LOG_FILE and/or RELAY_LOG_POS options are specified, then existing files are kept.

When you want to change the position, you only need to stop the . The can continue running. The and statements support the SQL_THREAD option for this scenario. For example:

When the value of this option is changed, the metadata about the position in the will also be changed in the relay-log.info file or the file that is configured by the system variable.

The and options cannot be specified if the and options were also specified.

GTID Options

MASTER_USE_GTID

The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the when connecting to a primary. The possible values are:

current_pos - Replicate in mode and use as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, option instead.
replica_pos - Replicate in

The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the when connecting to a primary. The possible values are:

current_pos - Replicate in mode and use as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, option instead.
slave_pos - Replicate in mode and use as the position to start downloading transactions from the primary. From , replica_pos is an alias for slave_pos.

For example:

Or:

MASTER_DEMOTE_TO_SLAVE

Used to transition a primary to become a replica. Replaces the old with a safe alternative by forcing users to set Using_Gtid=Slave_Pos and merging gtid_binlog_pos into gtid_slave_pos once at CHANGE MASTER TO time. If gtid_slave_pos is morerecent than gtid_binlog_pos (as in the case of chain replication), the replication state should be preserved.

For example:

MASTER_DEMOTE_TO_SLAVE is not available.

Also see .

IGNORE_SERVER_IDS

The IGNORE_SERVER_IDS option for CHANGE MASTER can be used to configure a to ignore events that originated from certain servers. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.

The option's value can be specified by providing a comma-separated list of values. For example:

If you would like to clear a previously set list, then you can set the value to an empty list. For example:

DO_DOMAIN_IDS

The DO_DOMAIN_IDS option for CHANGE MASTER can be used to configure a to only apply events if the transaction's is in a specific value. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.

The option's value can be specified by providing a comma-separated list of values. Duplicate values are automatically ignored. For example:

If you would like to clear a previously set list, then you can set the value to an empty list. For example:

The option and the option cannot both be set to non-empty values at the same time. If you want to set the option, and the option was previously set, then you need to clear the value of the option. For example:

The DO_DOMAIN_IDS option can only be specified if the replica is replicating in mode. Therefore, the option must also be set to some value other than no in order to use this option.

IGNORE_DOMAIN_IDS

The IGNORE_DOMAIN_IDS option for CHANGE MASTER can be used to configure a to ignore events if the transaction's is in a specific value. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.

The option's value can be specified by providing a comma-separated list of values. Duplicate values are automatically ignored. For example:

If you would like to clear a previously set list, then you can set the value to an empty list. For example:

The IGNORE_DOMAIN_IDS option can only be specified if the replica is replicating in mode. Therefore, the option must also be set to some value other than no in order to use this option.

Delayed Replication Options

MASTER_DELAY

The MASTER_DELAY option for CHANGE MASTER can be used to enable . This option specifies the time in seconds (at least) that a replica should lag behind the primary up to a maximum value of 2147483647, or about 68 years. Before executing an event, the replica will first wait, if necessary, until the given time has passed since the event was created on the primary. The result is that the replica will reflect the state of the primary some time back in the past. The default is zero, no delay.

Changing Option Values

If you don't specify a given option when executing the CHANGE MASTER statement, then the option keeps its old value in most cases. Most of the time, there is no need to specify the options that do not need to change. For example, if the password for the user account that the replica uses to connect to its primary has changed, but no other options need to change, then you can just change the option by executing the following commands:

There are some cases where options are implicitly reset, such as when the and options are changed.

Option Persistence

The values of the and options (i.e. the position on the primary) and most other options are written to either the default master.info file or the file that is configured by the option. The keeps this position updated as it downloads events only when optionis set to NO. Otherwise the file is not updated on a per event basis.

The option can be set either on the command-line or in a server in an prior to starting up the server. For example:

The values of the and options (i.e. the position) are written to either the default relay-log.info file or the file that is configured by the system variable. The keeps this position updated as it applies events.

The system variable can be set either on the command-line or in a server in an prior to starting up the server. For example:

GTID Persistence

If the replica is replicating events that contain , then the will write every GTID that it applies to the table. This GTID can be inspected and modified through the system variable.

If the replica has the system variable enabled and if the replica has the enabled, then every write by the will also go into the replica's . This means that of replicated transactions would be reflected in the value of the system variable.

Creating a Replica from a Backup

The CHANGE MASTER statement is useful for setting up a replica when you have a backup of the primary and you also have the position or position corresponding to the backup.

After restoring the backup on the replica, you could execute something like this to use the position:

Or you could execute something like this to use the position:

See for more information on how to do this with .

Example

The following example changes the primary and primary's binary log coordinates. This is used when you want to set up the replica to replicate the primary:

Administrative Statements

BINLOG

Syntax

Description

See Also

CACHE INDEX

Syntax

Description

Examples

Implementation (for MyISAM)

DESCRIBE

Syntax

Description

See Also

HELP Command

Syntax

Description

KILL

Syntax

Description

PURGE BINARY LOGS

Syntax

Description

RESET

Syntax

Description

SHUTDOWN

Syntax

Description

USE [DATABASE]

Syntax

Description

ANALYZE and EXPLAIN Statements

ANALYZE FORMAT=JSON

Basic Execution Data

EXPLAIN ANALYZE

EXPLAIN FORMAT=JSON

Synopsis

Using Buffer UPDATE Algorithm

BACKUP Statements

Storage Snapshots and BACKUP STAGE Statements

Generic Backup Process with Storage Snapshots

FLUSH Statements

FLUSH QUERY CACHE

Description

Plugin Statements

Replication Statements

RESET MASTER

See Also

SET GLOBAL SQL_SLAVE_SKIP_COUNTER

Syntax

Description

Legacy Replication Statements

RESET SLAVE

SHOW SLAVE HOSTS

SHOW SLAVE STATUS

START SLAVE

STOP SLAVE

SET Statements

SET SQL_LOG_BIN

Syntax

Description

SHOW Statements

About SHOW

SHOW BINARY LOGS

Syntax

Description

SHOW BINLOG EVENTS

Syntax

Description

SHOW CHARACTER SET

Syntax

Description

SHOW CLIENT_STATISTICS

Syntax

Description

SHOW CONTRIBUTORS

Syntax

Description

SHOW CREATE DATABASE