Learn to select data in MariaDB Server using SQL. This section covers various SELECT statement clauses, including WHERE, GROUP BY, and ORDER BY, to retrieve and filter your data effectively.
There are three supported comment styles in MariaDB:
From a '#
' to the end of a line:
SELECT * FROM users;
# This is a comment
From a '--
' to the end of a line. The space after the two dashes is required (as in MySQL).
SELECT * FROM users; -- This is a comment
C style comments from an opening '/*
' to a closing '*/
'. Comments of this form can span multiple lines:
SELECT * FROM users; /* This is a
multi-line
comment */
Nested comments are possible in some situations, but they are not supported or recommended.
As an aid to portability between different databases, MariaDB supports executable comments. These special comments allow you to embed SQL code which will not execute when run on other databases, but will execute when run on MariaDB.
MariaDB supports both MySQL's executable comment format, and a slightly modified version specific to MariaDB. This way, if you have SQL code that works on MySQL and MariaDB, but not other databases, you can wrap it in a MySQL executable comment, and if you have code that specifically takes advantage of features only available in MariaDB you can use the MariaDB specific format to hide the code from MySQL.
MySQL and MariaDB executable comment syntax:
/*! MySQL or MariaDB-specific code */
Code that should be executed only starting from a specific MySQL or MariaDB version:
/*!##### MySQL or MariaDB-specific code */
The numbers, represented by '######
' in the syntax examples above specify the specific the minimum versions of MySQL and MariaDB that should execute the comment. The first number is the major version, the second 2 numbers are the minor version and the last 2 is the patch level.
For example, if you want to embed some code that should only execute on MySQL or MariaDB starting from 5.1.0, you would do the following:
/*!50100 MySQL and MariaDB 5.1.0 (and above) code goes here. */
MariaDB-only executable comment syntax (starting from MariaDB 5.3.1):
/*M! MariaDB-specific code */
/*M!###### MariaDB-specific code */
MariaDB ignores MySQL-style executable comments that have a version number in the range 50700..99999
. This is needed to skip features introduced in MySQL-5.7 that are not ported to MariaDB 10.x yet.
/*!50701 MariaDB-10.x ignores MySQL-5.7 specific code */
Note: comments which have a version number in the range 50700..99999
that use MariaDB-style executable comment syntax are still executed.
/*M!50701 MariaDB-10.x does not ignore this */
Statement delimiters cannot be used within executable comments.
In MySQL all the following will return 2: In MariaDB, the last 2 queries would return 3.
SELECT 2 /* +1 */;
SELECT 1 /*! +1 */;
SELECT 1 /*!50101 +1 */;
SELECT 2 /*M! +1 */;
SELECT 2 /*M!50301 +1 */;
The following executable statement will not work due to the delimiter inside the executable portion:
/*M!100100 select 1 ; */
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
Instead, the delimiter should be placed outside the executable portion:
/*M!100100 select 1 */;
+---+
| 1 |
+---+
| 1 |
+---+
This page is licensed: CC BY-SA / Gnu FDL
Learn account management SQL statements for MariaDB Server. This section covers commands like CREATE USER, GRANT, and REVOKE to securely manage user access and privileges within your database.
ALTER USER [IF EXISTS]
user_specification [,user_specification] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[lock_option] [password_option]
user_specification:
username [authentication_option]
authentication_option:
IDENTIFIED BY 'password'
| IDENTIFIED BY PASSWORD 'password_hash'
| IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule] ...
authentication_rule:
authentication_plugin
| authentication_plugin {USING|AS} 'authentication_string'
| authentication_plugin {USING|AS} PASSWORD('password')
tls_option
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
resource_option
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
| MAX_STATEMENT_TIME time
password_option:
PASSWORD EXPIRE
| PASSWORD EXPIRE DEFAULT
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
lock_option:
ACCOUNT LOCK
| ACCOUNT UNLOCK
}
The ALTER USER
statement modifies existing MariaDB accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. The global SUPER privilege is also required if the read_only system variable is enabled.
If any of the specified user accounts do not yet exist, an error results. If an error occurs, ALTER USER
will still modify the accounts that do not result in an error. Only one error is produced for all users which have not been modified.
For renaming an existing account (user name and/or host), see RENAME USER.
When the IF EXISTS
clause is used, MariaDB will return a warning instead of an error for each specified user that does not exist.
For ALTER USER
statements, account names are specified as the username
argument in the same way as they are for CREATE USER statements. See account names from the CREATE USER
page for details on how account names are specified.
CURRENT_USER or CURRENT_USER()
can also be used to alter the account logged into the current session. For example, to change the current user's password to mariadb
:
ALTER USER CURRENT_USER() IDENTIFIED BY 'mariadb';
From MariaDB 10.4, it is possible to use more than one authentication plugin for each user account. For example, this can be useful to slowly migrate users to the more secure ed25519 authentication plugin over time, while allowing the old mysql_native_password authentication plugin as an alternative for the transitional period. See Authentication from MariaDB 10.4 for more.
When running ALTER USER
, not specifying an authentication option in the IDENTIFIED VIA clause will remove that authentication method. (However this was not the case before MariaDB 10.4.13, see MDEV-21928)
For example, a user is created with the ability to authenticate via both a password and unix_socket:
CREATE USER 'bob'@'localhost'
IDENTIFIED VIA mysql_native_password USING PASSWORD('pwd')
OR unix_socket;
SHOW CREATE USER 'bob'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for bob@localhost: CREATE USER `bob`@`localhost`
IDENTIFIED VIA mysql_native_password
USING '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'
OR unix_socket
If the user's password is updated, but unix_socket authentication is not specified in the IDENTIFIED VIA
clause, unix_socket authentication will no longer be permitted.
ALTER USER 'bob'@'localhost' IDENTIFIED VIA mysql_native_password
USING PASSWORD('pwd2');
SHOW CREATE USER 'bob'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for bob@localhost: CREATE USER `bob`@`localhost`
IDENTIFIED BY PASSWORD '*38366FDA01695B6A5A9DD4E428D9FB8F7EB75512'
The optional IDENTIFIED BY
clause can be used to provide an account with a password. The password should be specified in plain text. It will be hashed by the PASSWORD function prior to being stored in the mysql.user view.
For example, if our password is mariadb
, then we can set the account's password with:
ALTER USER foo2@test IDENTIFIED BY 'mariadb';
If you do not specify a password with the IDENTIFIED BY
clause, the user
will be able to connect without a password. A blank password is not a wildcard
to match any password. The user must connect without providing a password if no
password is set.
The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.
The optional IDENTIFIED BY PASSWORD
clause can be used to provide an account with a password that has already been hashed. The password should be specified as a hash that was provided by the PASSWORD#function. It will be stored in the mysql.user view as-is.
For example, if our password is mariadb
, then we can find the hash with:
SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb') |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
And then we can set an account's password with the hash:
ALTER USER foo2@test
IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';
If you do not specify a password with the IDENTIFIED BY
clause, the user
will be able to connect without a password. A blank password is not a wildcard
to match any password. The user must connect without providing a password if no password is set.
The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.
The optional IDENTIFIED VIA authentication_plugin
allows you to specify that the account should be authenticated by a specific authentication plugin. The plugin name must be an active authentication plugin as per SHOW PLUGINS. If it doesn't show up in that output, then you will need to install it with INSTALL PLUGIN or INSTALL SONAME.
For example, this could be used with the PAM authentication plugin:
ALTER USER foo2@test IDENTIFIED VIA pam;
Some authentication plugins allow additional arguments to be specified after a USING
or AS
keyword. For example, the PAM authentication plugin accepts a service name:
ALTER USER foo2@test IDENTIFIED VIA pam USING 'mariadb';
The exact meaning of the additional argument would depend on the specific authentication plugin.
The USING
or AS
keyword can also be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this:
ALTER USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');
The USING
or AS
keyword cannot be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function.
By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them.
To mitigate this concern, MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_, but internally, MariaDB only supports its secure successors.
See Secure Connections Overview for more information about how to determine whether your MariaDB server has TLS support.
You can set certain TLS-related restrictions for specific user accounts. For instance, you might use this with user accounts that require access to sensitive data while sending it across networks that you do not control. These restrictions can be enabled for a user account with the CREATE USER, ALTER USER, or GRANT statements. The following options are available:
REQUIRE NONE
TLS is not required for this account, but can still be used.
REQUIRE SSL
The account must use TLS, but no valid X509 certificate is required. This option cannot be combined with other TLS options.
REQUIRE X509
The account must use TLS and must have a valid X509 certificate. This option implies REQUIRE SSL. This option cannot be combined with other TLS options.
REQUIRE ISSUER 'issuer'
The account must use TLS and must have a valid X509 certificate. Also, the Certificate Authority must be the one specified via the string issuer. This option implies REQUIRE X509. This option can be combined with the SUBJECT, and CIPHER options in any order.
REQUIRE SUBJECT 'subject'
The account must use TLS and must have a valid X509 certificate. Also, the certificate's Subject must be the one specified via the string subject. This option implies REQUIRE X509. This option can be combined with the ISSUER, and CIPHER options in any order.
REQUIRE CIPHER 'cipher'
The account must use TLS, but no valid X509 certificate is required. Also, the encryption used for the connection must use a specific cipher method specified in the string cipher. This option implies REQUIRE SSL. This option can be combined with the ISSUER, and SUBJECT options in any order.
The REQUIRE
keyword must be used only once for all specified options, and the AND
keyword can be used to separate individual options, but it is not required.
For example, you can alter a user account to require these TLS options with the following:
ALTER USER 'alice'@'%'
REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland' AND
ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com'
AND CIPHER 'SHA-DES-CBC3-EDH-RSA';
If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS.
See Securing Connections for Client and Server for information on how to enable TLS on the client and server.
It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:
MAX_QUERIES_PER_HOUR
Number of statements that the account can issue per hour (including updates)
MAX_UPDATES_PER_HOUR
Number of updates (not queries) that the account can issue per hour
MAX_CONNECTIONS_PER_HOUR
Number of connections that the account can start per hour
MAX_USER_CONNECTIONS
Number of simultaneous connections that can be accepted from the same account; if it is 0, max_connections will be used instead; if max_connections is 0, there is no limit for this account's simultaneous connections.
MAX_STATEMENT_TIME
Timeout, in seconds, for statements executed by the user. See also Aborting Statements that Exceed a Certain Time to Execute.
If any of these limits are set to 0
, then there is no limit for that resource for that user.
Here is an example showing how to set an account's resource limits:
ALTER USER 'someone'@'localhost' WITH
MAX_USER_CONNECTIONS 10
MAX_QUERIES_PER_HOUR 200;
The resources are tracked per account, which means 'user'@'server'
; not per user name or per connection.
The count can be reset for all users using FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mysqladmin reload.
Per account resource limits are stored in the user table, in the mysql database. Columns used for resources limits are named max_questions
, max_updates
, max_connections
(for MAX_CONNECTIONS_PER_HOUR
), and max_user_connections
(for MAX_USER_CONNECTIONS
).
Besides automatic password expiry, as determined by default_password_lifetime, password expiry times can be set on an individual user basis, overriding the global setting, for example:
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT;
See User Password Expiry for more details.
Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected). For example:
ALTER USER 'marijn'@'localhost' ACCOUNT LOCK;
See Account Locking for more details.
The lock_option and password_option clauses can occur in either order.
The lock_option must be placed before the password_option.
Password Validation Plugins - permits the setting of basic criteria for passwords
Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.
This page is licensed: CC BY-SA / Gnu FDL
CREATE [OR REPLACE] ROLE [IF NOT EXISTS] role
[WITH ADMIN
{CURRENT_USER | CURRENT_ROLE | user | role}]
The CREATE ROLE
statement creates one or more MariaDB roles. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE ROLE
creates a new row in themysql.user table that has no privileges, and with the corresponding is_role
field set to Y
. It also creates a record in themysql.roles_mapping table.
If any of the specified roles already exist, ERROR 1396 (HY000)
results. If an error occurs, CREATE ROLE
will still create the roles that do not result in an error. The maximum length for a role is 128 characters. Role names can be quoted, as explained in the Identifier names page. Only one error is produced for all roles which have not been created:
ERROR 1396 (HY000): Operation CREATE ROLE failed for 'a','b','c'
Failed CREATE
or DROP
operations, for both users and roles, produce the same error code.
PUBLIC
and NONE
are reserved, and cannot be used as role names. NONE
is used to unset a role and PUBLIC
has a special use in other systems, such as Oracle, so is reserved for compatibility purposes.
For valid identifiers to use as role names, see Identifier Names.
The optional WITH ADMIN
clause determines whether the current user, the current role or another user or role has use of the newly created role. If the clause is omitted, WITH ADMIN CURRENT_USER
is treated as the default, which means that the current user will be able to GRANT this role to users.
If the optional OR REPLACE
clause is used, it acts as a shortcut for:
DROP ROLE IF EXISTS name;
CREATE ROLE name ...;
When the IF NOT EXISTS
clause is used, MariaDB will return a warning instead of an error if the specified role already exists. Cannot be used together with the OR REPLACE
clause.
CREATE ROLE journalist;
CREATE ROLE developer WITH ADMIN lorinda@localhost;
Granting the role to another user. Only user lorinda@localhost
has permission to grant the developer
role:
SELECT USER();
+-------------------+
| USER() |
+-------------------+
| henning@localhost |
+-------------------+
...
GRANT developer TO ian@localhost;
Access denied for user 'henning'@'localhost'
SELECT USER();
+-------------------+
| USER() |
+-------------------+
| lorinda@localhost |
+-------------------+
GRANT m_role TO ian@localhost;
The OR REPLACE
and IF NOT EXISTS
clauses. The journalist
role already exists:
CREATE ROLE journalist;
ERROR 1396 (HY000): Operation CREATE ROLE failed for 'journalist'
CREATE OR REPLACE ROLE journalist;
Query OK, 0 rows affected (0.00 sec)
CREATE ROLE IF NOT EXISTS journalist;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+---------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------+
| Note | 1975 | Can't create role 'journalist'; it already exists |
+-------+------+---------------------------------------------------+
This page is licensed: CC BY-SA / Gnu FDL
CREATE [OR REPLACE] USER [IF NOT EXISTS]
user_specification [,user_specification ...]
[REQUIRE {NONE | tls_option [[AND] tls_option ...] }]
[WITH resource_option [resource_option ...] ]
[lock_option] [password_option]
user_specification:
username [authentication_option]
authentication_option:
IDENTIFIED BY 'password'
| IDENTIFIED BY PASSWORD 'password_hash'
| IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule ...]
authentication_rule:
authentication_plugin
| authentication_plugin {USING|AS} 'authentication_string'
| authentication_plugin {USING|AS} PASSWORD('password')
tls_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
resource_option:
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
| MAX_STATEMENT_TIME time
password_option:
PASSWORD EXPIRE
| PASSWORD EXPIRE DEFAULT
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
lock_option:
ACCOUNT LOCK
| ACCOUNT UNLOCK
}
The CREATE USER
statement creates new MariaDB accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database.
For each account, CREATE USER
creates a new row in the mysql.user view (and the underlying mysql.global_priv table) that has no privileges.
For each account, CREATE USER
creates a new row in mysql.user table that has no privileges.
If any of the specified accounts, or any permissions for the specified accounts, already exist, then the server returns ERROR 1396 (HY000)
. If an error occurs, CREATE USER
will still create the accounts that do not result in an error. Only one error is produced for all users which have not been created:
ERROR 1396 (HY000):
Operation CREATE USER failed for 'u1'@'%','u2'@'%'
CREATE USER, DROP USER, CREATE ROLE, and DROP ROLE all produce the same error code when they fail.
See Account Names below for details on how account names are specified.
One can also create users with GRANT if SQL_MODE does not have NO_AUTO_CREATE_USER set. NO_AUTO_CREATE_USER
is set by default.
If the optional OR REPLACE
clause is used, it is basically a shortcut for:
DROP USER IF EXISTS name;
CREATE USER name ...;
For example:
CREATE USER foo2@test IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test'
CREATE OR REPLACE USER foo2@test IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
When the IF NOT EXISTS
clause is used, MariaDB will return a warning instead of an error if the specified user already exists.
For example:
CREATE USER foo2@test IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test'
CREATE USER IF NOT EXISTS foo2@test IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+----------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------+
| Note | 1973 | Can't create user 'foo2'@'test'; it already exists |
+-------+------+----------------------------------------------------+
If more than one authentication mechanism is declared using the OR
keyword, the mechanisms are attempted in the order they are declared in the CREATE USER
statement. As soon as one of the authentication mechanisms is successful, authentication is complete. If none of them is successful, the authentication has failed.
The optional IDENTIFIED BY
clause can be used to provide an account with a password. The password should be specified in plain text. It will be hashed by the PASSWORD function prior to being stored in the mysql.user/mysql.global_priv_table table.
For example, if our password is mariadb
, then we can create the user with:
CREATE USER foo2@test IDENTIFIED BY 'mariadb';
If you do not specify a password with the IDENTIFIED BY
clause, the user
will be able to connect without a password. A blank password is not a wildcard
to match any password. The user must connect without providing a password if no
password is set.
The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.
The optional IDENTIFIED BY PASSWORD
clause can be used to provide an account with a password that has already been hashed. The password should be specified as a hash that was provided by the PASSWORD function. It will be stored in the mysql.user/mysql.global_priv_table table as-is.
For example, if our password is mariadb
, then we can find the hash with:
SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb') |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)
And then we can create a user with the hash:
CREATE USER foo2@test IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';
If you do not specify a password with the IDENTIFIED BY
clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set.
The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.
The optional IDENTIFIED VIA authentication_plugin
allows you to specify that the account should be authenticated by a specific authentication plugin. The plugin name must be an active authentication plugin as per SHOW PLUGINS. If it doesn't show up in that output, then you will need to install it with INSTALL PLUGIN or INSTALL SONAME.
VIA
and WITH
are synonyms.
For example, this could be used with the PAM authentication plugin:
CREATE USER foo2@test IDENTIFIED VIA pam;
Some authentication plugins allow additional arguments to be specified after a USING
or AS
keyword. For example, the PAM authentication plugin accepts a service name:
CREATE USER foo2@test IDENTIFIED VIA pam USING 'mariadb';
The exact meaning of the additional argument would depend on the specific authentication plugin.
The USING
or AS
keyword can also be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this:
CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');
One can specify many authentication plugins, they all work as alternatives ways of authenticating a user:
CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;
By default, when you create a user without specifying an authentication plugin, MariaDB uses the mysql_native_password plugin.
MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_, but internally, MariaDB only supports its secure successors.
By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them.
To mitigate this concern, MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_, but internally, MariaDB only supports its secure successors.
See Secure Connections Overview for more information about how to determine whether your MariaDB server has TLS support.
You can set certain TLS-related restrictions for specific user accounts. For instance, you might use this with user accounts that require access to sensitive data while sending it across networks that you do not control. These restrictions can be enabled for a user account with the CREATE USER, ALTER USER, or GRANT statements. The following options are available:
REQUIRE NONE
TLS is not required for this account, but can still be used.
REQUIRE SSL
The account must use TLS, but no valid X509 certificate is required. This option cannot be combined with other TLS options.
REQUIRE X509
The account must use TLS and must have a valid X509 certificate. This option implies REQUIRE SSL. This option cannot be combined with other TLS options.
REQUIRE ISSUER 'issuer'
The account must use TLS and must have a valid X509 certificate. Also, the Certificate Authority must be the one specified via the string issuer. This option implies REQUIRE X509. This option can be combined with the SUBJECT, and CIPHER options in any order.
REQUIRE SUBJECT 'subject'
The account must use TLS and must have a valid X509 certificate. Also, the certificate's Subject must be the one specified via the string subject. This option implies REQUIRE X509. This option can be combined with the ISSUER, and CIPHER options in any order.
REQUIRE CIPHER 'cipher'
The account must use TLS, but no valid X509 certificate is required. Also, the encryption used for the connection must use a specific cipher method specified in the string cipher. This option implies REQUIRE SSL. This option can be combined with the ISSUER, and SUBJECT options in any order.
The REQUIRE
keyword must be used only once for all specified options, and the AND
keyword can be used to separate individual options, but it is not required.
For example, you can create a user account that requires these TLS options with the following:
CREATE USER 'alice'@'%'
REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'
AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com'
AND CIPHER 'SHA-DES-CBC3-EDH-RSA';
If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS.
See Securing Connections for Client and Server for information on how to enable TLS on the client and server.
It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:
MAX_QUERIES_PER_HOUR
Number of statements that the account can issue per hour (including updates)
MAX_UPDATES_PER_HOUR
Number of updates (not queries) that the account can issue per hour
MAX_CONNECTIONS_PER_HOUR
Number of connections that the account can start per hour
MAX_USER_CONNECTIONS
Number of simultaneous connections that can be accepted from the same account; if it is 0, max_connections will be used instead; if max_connections is 0, there is no limit for this account's simultaneous connections.
MAX_STATEMENT_TIME
Timeout, in seconds, for statements executed by the user. See also Aborting Statements that Exceed a Certain Time to Execute.
If any of these limits are set to 0
, then there is no limit for that resource for that user.
Here is an example showing how to create a user with resource limits:
CREATE USER 'someone'@'localhost' WITH
MAX_USER_CONNECTIONS 10
MAX_QUERIES_PER_HOUR 200;
The resources are tracked per account, which means 'user'@'server'
; not per user name or per connection.
The count can be reset for all users using FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mariadb-admin reload.
Per account resource limits are stored in the user table, in the mysql database. Columns used for resources limits are named max_questions
, max_updates
, max_connections
(for MAX_CONNECTIONS_PER_HOUR
), and max_user_connections
(for MAX_USER_CONNECTIONS
).
Account names have both a user name component and a host name component, and are specified as 'user_name'@'host_name'
.
The user name and host name may be unquoted, quoted as strings using double quotes ("
) or
single quotes ('
), or quoted as identifiers using backticks (```). You must use quotes
when using special characters (such as a hyphen) or wildcard characters. If you quote, you
must quote the user name and host name separately (for example 'user_name'@'host_name'
).
If the host name is not provided, it is assumed to be '%'
.
Host names may contain the wildcard characters %
and _
. They are matched as if by
the LIKE clause. If you need to use a wildcard character literally (for example, to
match a domain name with an underscore), prefix the character with a backslash. See LIKE
for more information on escaping wildcard characters.
Host name matches are case-insensitive. Host names can match either domain names or IP
addresses. Use 'localhost'
as the host name to allow only local client connections. On Linux, the loopback interface (127.0.0.1) will not match 'localhost' as it is not considered a local connection: this means that only connections via UNIX-domain sockets will match 'localhost'.
You can use a netmask to match a range of IP addresses using 'base_ip/netmask'
as the
host name. A user with an IP address ip_addr will be allowed to connect if the following
condition is true:
ip_addr & netmask = base_ip
For example, given a user:
CREATE USER 'maria'@'247.150.130.0/255.255.255.0';
the IP addresses satisfying this condition range from 247.150.130.0 to 247.150.130.255.
Using 255.255.255.255
is equivalent to not using a netmask at all. Netmasks cannot be used for IPv6 addresses.
Note that the credentials added when creating a user with the '%'
wildcard host will not grant access in all cases. For example, some systems come with an anonymous localhost user, and when connecting from localhost this will take precedence.
Before MariaDB 10.6, the host name component could be up to 60 characters in length. Starting from MariaDB 10.6, it can be up to 255 characters.
User names must match exactly, including case. A user name that is empty is known as an anonymous account and is allowed to match a login attempt with any user name component. These are described more in the next section.
For valid identifiers to use as user names, see Identifier Names.
It is possible for more than one account to match when a user connects. MariaDB selects the first matching account after sorting according to the following criteria:
Accounts with an exact host name are sorted before accounts using a wildcard in the host name. Host names using a netmask are considered to be exact for sorting.
Accounts with a wildcard in the host name are sorted according to the position of the first wildcard character. Those with a wildcard character later in the host name sort before those with a wildcard character earlier in the host name.
Accounts with a non-empty user name sort before accounts with an empty user name.
Accounts with an empty user name are sorted last. As mentioned previously, these are known as anonymous accounts. These are described more in the next section.
The following table shows a list of example account as sorted by these criteria:
+---------+-------------+
| User | Host |
+---------+-------------+
| joffrey | 192.168.0.3 |
| | 192.168.0.% |
| joffrey | 192.168.% |
| | 192.168.% |
+---------+-------------+
Once connected, you only have the privileges granted to the account that matched, not all accounts that could have matched. For example, consider the following commands:
CREATE USER 'joffrey'@'192.168.0.3';
CREATE USER 'joffrey'@'%';
GRANT SELECT ON test.t1 to 'joffrey'@'192.168.0.3';
GRANT SELECT ON test.t2 to 'joffrey'@'%';
If you connect as joffrey from 192.168.0.3
, you will have the SELECT
privilege on the table test.t1
, but not on the table test.t2
. If you connect as joffrey from any other IP address, you will have the SELECT
privilege on the table test.t2
, but not
on the table test.t1
.
Usernames can be up to 80 characters long before 10.6 and starting from 10.6 it can be 128 characters long.
Anonymous accounts are accounts where the user name portion of the account name is empty. These accounts act as special catch-all accounts. If a user attempts to log into the system from a host, and an anonymous account exists with a host name portion that matches the user's host, then the user will log in as the anonymous account if there is no more specific account match for the user name that the user entered.
For example, here are some anonymous accounts:
CREATE USER ''@'localhost';
CREATE USER ''@'192.168.0.3';
On some systems, the mysql.db table has some entries for the ''@'%'
anonymous account by default. Unfortunately, there is no matching entry in the mysql.user/mysql.global_priv_table table, which means that this anonymous account doesn't exactly exist, but it does have privileges--usually on the default test
database created by mariadb-install-db. These account-less privileges are a legacy that is leftover from a time when MySQL's privilege system was less advanced.
This situation means that you will run into errors if you try to create a ''@'%'
account. For example:
CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
The fix is to DELETE the row in the mysql.db table and then execute FLUSH PRIVILEGES:
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
Note that FLUSH PRIVILEGES
is only needed if one modifies the mysql
tables directly. It is not needed when using CREATE USER
, DROP USER
, GRANT
etc.
And then the account can be created:
CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
See MDEV-13486 for more information.
Besides automatic password expiry, as determined by default_password_lifetime, password expiry times can be set on an individual user basis, overriding the global setting, for example:
CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;
See User Password Expiry for more details.
Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected). For example:
CREATE USER 'marijn'@'localhost' ACCOUNT LOCK;
See Account Locking for more details.
The lock_option and password_option clauses can occur in either order.
Prior to MariaDB 10.4.7 and MariaDB 10.5.8, the lock_option must be placed before the password_option.
From MariaDB 10.4.7 and MariaDB 10.5.8, the lock_option and password_option clauses can occur in either order.
Password Validation Plugins - permits the setting of basic criteria for passwords
Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DROP ROLE [IF EXISTS] role_name [,role_name ...]
The DROP ROLE
statement removes one or more MariaDB roles. To use this statement, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database.
DROP ROLE
does not disable roles for connections which selected them with SET ROLE. If a role has previously been set as a default role, DROP ROLE
does not remove the record of the default role from the mysql.user table. If the role is subsequently recreated and granted, it will again be the user's default. Use SET DEFAULT ROLE NONE to explicitly remove this.
If any of the specified user accounts do not exist, ERROR 1396 (HY000)
results. If an error occurs, DROP ROLE
will still drop the roles that do not result in an error. Only one error is produced for all roles which have not been dropped:
ERROR 1396 (HY000): Operation DROP ROLE failed for 'a','b','c'
Failed CREATE
or DROP
operations, for both users and roles, produce the same error code.
If the IF EXISTS
clause is used, MariaDB will return a warning instead of an error if the role does not exist.
DROP ROLE journalist;
The same thing using the optional IF EXISTS
clause:
DROP ROLE journalist;
ERROR 1396 (HY000): Operation DROP ROLE failed for 'journalist'
DROP ROLE IF EXISTS journalist;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Note (Code 1975): Can't drop role 'journalist'; it doesn't exist
This page is licensed: CC BY-SA / Gnu FDL
DROP USER [IF EXISTS] user_name [, user_name] ...
The DROP USER
statement removes one or more MariaDB accounts. It removes privilege rows for the account from all grant tables. To use this statement, you must have the global CREATE USER privilege
or the DELETE privilege for the mysql database. Each account is named using the same format as for the CREATE USER
statement; for example, 'jeffrey'@'localhost'
. If you specify only the user name part of the account name, a host name part of '%'
is used. For additional information about specifying account names, see CREATE USER.
From MariaDB 12.1, by default, if you specify an account that is currently connected, it will not be deleted until the connection is closed. The connection will not automatically be closed. The statement will complete, and a warning, "Dropped users 'user'@'host[,...]' have active connections. Use KILL CONNECTION if they should not be used anymore" issued.
In Oracle mode, if a user is connected, the DROP USER statement will fail with an error "Operation DROP USER failed for 'foo'@'localhost'".
If you specify an account that is currently connected, it will not be deleted until the connection is closed. The connection will not automatically be closed.
If any of the specified user accounts do not exist, ERROR 1396 (HY000)
results. If an error occurs, DROP USER
will still drop the accounts that do not result in an error. Only one error is produced for all users which have not been dropped:
ERROR 1396 (HY000): Operation DROP USER failed for 'u1'@'%','u2'@'%'
Failed CREATE
or DROP
operations, for both users and roles, produce the same error code.
If the IF EXISTS
clause is used, MariaDB will return a note instead of an error if the user does not exist.
The CREATE USER
statement creates new MariaDB accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database.
If any of the specified accounts, or any permissions f
DROP USER bob;
DROP USER foo2@localhost,foo2@'127.%';
IF EXISTS
:
DROP USER bob;
ERROR 1396 (HY000): Operation DROP USER failed for 'bob'@'%'
DROP USER IF EXISTS bob;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1974 | Can't drop user 'bob'@'%'; it doesn't exist |
+-------+------+---------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [ user_options ...]
user_specification:
username [authentication_option]
| PUBLIC
authentication_option:
IDENTIFIED BY 'password'
| IDENTIFIED BY PASSWORD 'password_hash'
| IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule ...]
authentication_rule:
authentication_plugin
| authentication_plugin {USING|AS} 'authentication_string'
| authentication_plugin {USING|AS} PASSWORD('password')
GRANT PROXY ON username
TO user_specification [, user_specification ...]
[WITH GRANT OPTION]
GRANT rolename TO grantee [, grantee ...]
[WITH ADMIN OPTION]
grantee:
rolename
username [authentication_option]
user_options:
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH with_option [with_option] ...]
object_type:
TABLE
| FUNCTION
| PROCEDURE
| PACKAGE
| PACKAGE BODY
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
with_option:
GRANT OPTION
| resource_option
resource_option:
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
| MAX_STATEMENT_TIME time
tls_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
The GRANT
statement allows you to grant privileges or roles to accounts. To use GRANT
, you must have the GRANT OPTION
privilege, and you must have the privileges that you are granting.
Use the REVOKE statement to revoke privileges granted with the GRANT
statement.
Use the SHOW GRANTS statement to determine what privileges an account has.
For GRANT
statements, account names are specified as the username
argument in the same way as they are for CREATE USER statements. See account names from the CREATE USER
page for details on how account names are specified.
The GRANT
statement also allows you to implicitly create accounts in some cases.
If the account does not yet exist, then GRANT
can implicitly create it. To implicitly create an account with GRANT
, a user is required to have the same privileges that would be required to explicitly create the account with the CREATE USER
statement.
If the NO_AUTO_CREATE_USER
SQL_MODE is set, then accounts can only be created if authentication information is specified, or with a CREATE USER statement. If no authentication information is provided, GRANT
will produce an error when the specified account does not exist, for example:
SHOW VARIABLES LIKE '%sql_mode%' ;
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED BY '';
ERROR 1133 (28000): Can't find any matching row in the user table
GRANT USAGE ON *.* TO 'user123'@'%'
IDENTIFIED VIA PAM using 'mariadb' require ssl ;
Query OK, 0 rows affected (0.00 sec)
SELECT host, user FROM mysql.user WHERE user='user123' ;
+------+----------+
| host | user |
+------+----------+
| % | user123 |
+------+----------+
Privileges can be set globally, for an entire database, for a table or routine, or for individual columns in a table. Certain privileges can only be set at certain levels.
Global privileges do not take effect immediately and are only applied to connections created after the GRANT
statement was executed.
Global privileges priv_type are granted using *.*
for priv_level. Global privileges include privileges to administer the database and manage user accounts, as well as privileges for all tables, functions, and procedures. Global privileges are stored in mysql.global_priv table.
Database privileges priv_type are granted using db_name.*
for priv_level, or using just *
to use default database. Database privileges include privileges to create tables and functions, as well as
privileges for all tables, functions, and procedures in the database. Database privileges are stored in the mysql.db table.
Table privileges priv_type are granted using db_name.tbl_name
for priv_level, or using just tbl_name
to specify a table in the default database. The TABLE
keyword is optional. Table privileges include the ability to select and change data in the table. Certain table privileges can be granted for individual columns.
Column privileges priv_type are granted by specifying a table for priv_level and providing a column list after the privilege type. They allow you to control exactly which columns in a table users can select and change.
Function privileges priv_type are granted using FUNCTION db_name.routine_name
for priv_level, or using just FUNCTION routine_name
to specify a function in the default database.
Procedure privileges priv_type are granted using PROCEDURE db_name.routine_name
for priv_level, or using just PROCEDURE routine_name
to specify a procedure in the default database.
USAGE
PrivilegeThe USAGE
privilege grants no real privileges. The SHOW GRANTS statement will show a global USAGE
privilege for a newly-created user. You can use USAGE
with the GRANT
statement to change options like GRANT OPTION
and MAX_USER_CONNECTIONS
without changing any account privileges.
ALL PRIVILEGES
PrivilegeThe ALL PRIVILEGES
privilege grants all available privileges. Granting all privileges only affects the given privilege level. For example, granting all privileges on a table does not grant any privileges on the database or globally.
Using ALL PRIVILEGES
does not grant the special GRANT OPTION
privilege.
You can use ALL
instead of ALL PRIVILEGES
.
GRANT OPTION
PrivilegeUse the WITH GRANT OPTION
clause to give users the ability to grant privileges to other users at the given privilege level. Users with the GRANT OPTION
privilege can only grant privileges they have. They cannot grant privileges at a higher privilege level than they have the GRANT OPTION
privilege.
The GRANT OPTION
privilege cannot be set for individual columns. If you use WITH GRANT OPTION
when specifying column privileges, the GRANT OPTION
privilege will be granted for the entire table.
Using the WITH GRANT OPTION
clause is equivalent to listing GRANT OPTION
as a privilege.
The following table lists the privileges that can be granted globally. You can also grant all database, table, and function privileges globally. When granted globally, these privileges apply to all databases, tables, or functions, including those created later.
To set a global privilege, use *.*
for priv_level.
Enables administration of the binary log, including the PURGE BINARY LOGS statement and setting the system variables:
sql_log_bin and
BINLOG ADMIN isn't available.
New name for REPLICATION CLIENT. REPLICATION CLIENT
can still be used, though.
Use REPLICATION CLIENT instead. SHOW REPLICA STATUS isn't included in this privilege, and REPLICA MONITOR is required.
Permits running SHOW commands related to the binary log, in particular the SHOW BINLOG STATUS and SHOW BINARY LOGS statements.
Enables replaying the binary log with the BINLOG statement (generated by mariadb-binlog), executing SET timestamp when secure_timestamp is set to replication
, and setting the session values of system variables usually included in BINLOG output, in particular:
BINLOG REPLAY
isn't available.
Enables administering connection resource limit options. This includes ignoring the limits specified by max_user_connections and max_password_errors, and allowing one extra connection over max_connections
The statements specified in init_connect are not executed, killing connections and queries owned by other users is permitted. The following connection-related system variables can be changed:
thread_pool_size, and
Create a user using the CREATE USER statement, or implicitly create a user with the GRANT
statement.
Execute CREATE SERVER, ALTER SERVER, and DROP SERVER statements.
FEDERATED ADMIN
is not available.
Read and write files on the server, using statements like LOAD DATA INFILE or functions like LOAD_FILE(). Also needed to create CONNECT outward tables. MariaDB server must have the permissions to access those files.
Grant global privileges. You can only grant privileges that you have.
Show information about the active processes, for example via SHOW PROCESSLIST or mariadb-admin processlist. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MariaDB account that you are using).
User ignores the read_only system variable, and can perform write operations even when the read_only
option is active.
The READ_ONLY ADMIN
privilege has been removed from SUPER. The benefit of this is that one can remove the READ_ONLY ADMIN privilege from all users and ensure that no one can make any changes on any non-temporary tables. This is useful on replicas when one wants to ensure that the replica is kept identical to the primary.
READ\_ONLY ADMIN
isn't available.
Execute FLUSH statements or equivalent mariadb-admin commands.
Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements. Renamed to BINLOG MONITOR (but still supported as an alias for compatibility reasons).
Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements. SHOW SLAVE STATUS is part of REPLICATION CLIENT.
Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements. Using BINLOG MONITOR instead is still supported as an alias.
Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements. Renamed to BINLOG MONITOR in MariaDB 10.5.2 (but still supported as an alias for compatibility reasons). SHOW SLAVE STATUS was part of REPLICATION CLIENT prior to MariaDB 10.5.
Permits administration of primary servers, including the SHOW REPLICA HOSTS statement, and setting the gtid_binlog_state, gtid_domain_id, master_verify_checksum and server_id system variables.
REPLICATION MASTER ADMIN
is not available.
Permit SHOW REPLICA STATUS and SHOW RELAYLOG EVENTS.
See Reasoning tab as to why this was implemented.
When a user would upgrade from an older major release to a MariaDB 10.5 minor release prior to MariaDB 10.5.9, certain user accounts would lose capabilities. For example, a user account that had the REPLICATION CLIENT privilege in older major releases could run SHOW REPLICA STATUS, but after upgrading to a MariaDB 10.5 minor release prior to MariaDB 10.5.9, they could no longer run SHOW REPLICA STATUS, because that statement was changed to require the REPLICATION REPLICA ADMIN privilege.
This issue is fixed in MariaDB 10.5.9 with this new privilege, which now grants the user the ability to execute SHOW [ALL] (SLAVE | REPLICA) STATUS
.
When a database is upgraded from an older major release to MariaDB Server 10.5.9 or later, any user accounts with the REPLICATION CLIENT
or REPLICATION SLAVE
privileges will automatically be granted the new REPLICA MONITOR
privilege. The privilege fix occurs when the server is started up, not when mariadb-upgrade is performed.
However, when a database is upgraded from an early 10.5 minor release to 10.5.9 and later, the user will have to fix any user account privileges manually.
REPLICA MONITOR
is not available.
Synonym for REPLICATION SLAVE.
REPLICATION REPLICA
is not available.
Accounts used by replica servers on the primary need this privilege. This is needed to get the updates made on the master. REPLICATION REPLICA is an alias for REPLICATION SLAVE
.
Accounts used by replica servers on the primary need this privilege. This is needed to get the updates made on the master.
Permits administering replica servers, including START REPLICA/SLAVE, STOP REPLICA/SLAVE, CHANGE MASTER, SHOW REPLICA/SLAVE STATUS, SHOW RELAYLOG EVENTS statements, replaying the binary log with the BINLOG statement (generated by mariadb-binlog), and setting the system variables:
sync_relay_log, and
REPLICATION SLAVE ADMIN
is not available.
Enables setting the DEFINER
when creating triggers, views, stored functions and stored procedures.
SET USER isn't available.
List all databases using the SHOW DATABASES statement. Without the SHOW DATABASES
privilege, you can still issue the SHOW DATABASES
statement, but it will only list databases containing tables on which you have privileges.
Shut down the server using SHUTDOWN or the mariadb-admin shutdown command.
Execute superuser statements: CHANGE MASTER TO, KILL (users who do not have this privilege can only KILL
their own threads), PURGE LOGS, SET global system variables, or the mariadb-admin debug command. Also, this permission allows the user to write data even if the read_only startup option is set, enable or disable logging, enable or disable replication on replica, specify a DEFINER
for statements that support that clause, connect once reaching the MAX_CONNECTIONS
. If a statement has been specified for the init-connect mariadbd option, that command will not be executed when a user with SUPER
privileges connects to the server.
The SUPER privilege has been split into multiple smaller privileges to allow for more fine-grained privileges (MDEV-21743). The privileges are:
These grants are no longer a part of SUPER and need to be granted separately.
The READ_ONLY ADMIN privilege has been removed from SUPER
. The benefit of this is that one can remove the READ_ONLY ADMIN privilege from all users and ensure that no one can make any changes on any non-temporary tables. This is useful on replicas when one wants to ensure that the replica is kept identical to the primary (MDEV-29596).
The SUPER privilege has been split into multiple smaller privileges to allow for more fine-grained privileges (MDEV-21743). The privileges are:
These grants are part of SUPER and don't need to be granted separately.
Use the SUPER privilege.
The following table lists the privileges that can be granted at the database level. You can also grant all table and function privileges at the database level. Table and function privileges on a database apply to all tables or functions in that database, including those created later.
To set a privilege for a database, specify the database usingdb_name.*
for priv_level, or just use *
to specify the default database.
CREATE
Create a database using the CREATE DATABASE statement, when the privilege is granted for a database. You can grant the CREATE privilege on databases that do not yet exist. This also grants the CREATE privilege on all tables in the database.
CREATE ROUTINE
Create Stored Programs using the CREATE PROCEDURE and CREATE FUNCTION statements.
CREATE TEMPORARY TABLES
Create temporary tables with the CREATE TEMPORARY TABLE statement. This privilege enable writing and dropping those temporary tables
DROP
Drop a database using the DROP DATABASE statement, when the privilege is granted for a database. This also grants the DROP privilege on all tables in the database.
EVENT
Create, drop and alter EVENTs.
GRANT OPTION
Grant database privileges. You can only grant privileges that you have.
LOCK TABLES
Acquire explicit locks using the LOCK TABLES statement; you also need to have the SELECT privilege on a table, in order to lock it.
SHOW CREATE ROUTINE
Permit viewing the SHOW CREATE definition statement of a routine, for example SHOW CREATE FUNCTION, even if not the routine owner. From MariaDB 11.3.0.
ALTER
Change the structure of an existing table using the ALTER TABLE statement.
CREATE
Create a table using the CREATE TABLE statement. You can grant the CREATE privilege on tables that do not yet exist.
CREATE VIEW
Create a view using the CREATE_VIEW statement.
DELETE
Remove rows from a table using the DELETE statement.
DELETE HISTORY
Remove historical rows from a table using the DELETE HISTORY statement. Displays as DELETE VERSIONING ROWS when running SHOW PRIVILEGES until MariaDB 10.5.2 (MDEV-20382). If a user has the SUPER privilege but not this privilege, running mariadb-upgrade will grant this privilege as well.
DROP
Drop a table using the DROP TABLE statement or a view using the DROP VIEW statement. Also required to execute the TRUNCATE TABLE statement.
GRANT OPTION
Grant table privileges. You can only grant privileges that you have.
INDEX
Create an index on a table using the CREATE INDEX statement. Without the INDEX privilege, you can still create indexes when creating a table using the CREATE TABLE statement if the you have the CREATE privilege, and you can create indexes using the ALTER TABLE statement if you have the ALTER privilege.
INSERT
Add rows to a table using the INSERT statement. The INSERT privilege can also be set on individual columns; see Column Privileges below for details.
REFERENCES
Unused.
SELECT
Read data from a table using the SELECT statement. The SELECT privilege can also be set on individual columns; see Column Privileges below for details.
SHOW VIEW
Show the CREATE VIEW statement to create a view using the SHOW CREATE VIEW statement.
TRIGGER
Required to run the CREATE TRIGGER, DROP TRIGGER, and SHOW CREATE TRIGGER statements. When another user activates a trigger (running INSERT, UPDATE, or DELETE statements on the associated table), for the trigger to execute, the user that defined the trigger should have the TRIGGER privilege for the table. The user running the INSERT, UPDATE, or DELETE statements on the table is not required to have the TRIGGER privilege.
UPDATE
Update existing rows in a table using the UPDATE statement. UPDATE statements usually include a WHERE clause to update only certain rows. You must have SELECT privileges on the table or the appropriate columns for the WHERE clause. The UPDATE privilege can also be set on individual columns; see Column Privileges below for details.
Some table privileges can be set for individual columns of a table. To use column privileges, specify the table explicitly and provide a list of column names after the privilege type. For example, the following statement would allow the user to read the names and positions of employees, but not other information from the same table, such as salaries.
GRANT SELECT (name, position) ON Employee TO 'jeffrey'@'localhost';
INSERT (column_list)
Add rows specifying values in columns using the INSERT statement. If you only have column-level INSERT privileges, you must specify the columns you are setting in the INSERT statement. All other columns will be set to their default values, or NULL.
REFERENCES (column_list)
Unused.
SELECT (column_list)
Read values in columns using the SELECT statement. You cannot access or query any columns for which you do not have SELECT privileges, including in WHERE, ON, GROUP BY, and ORDER BY clauses.
UPDATE (column_list)
Update values in columns of existing rows using the UPDATE statement. UPDATE statements usually include a WHERE clause to update only certain rows. You must have SELECT privileges on the table or the appropriate columns for the WHERE clause.
ALTER ROUTINE
Change the characteristics of a stored function using the ALTER FUNCTION statement.
EXECUTE
Use a stored function. You need SELECT privileges for any tables or columns accessed by the function.
GRANT OPTION
Grant function privileges. You can only grant privileges that you have.
ALTER ROUTINE
Change the characteristics of a stored procedure using the ALTER PROCEDURE statement.
EXECUTE
Execute a stored procedure using the CALL statement. The privilege to call a procedure may allow you to perform actions you wouldn't otherwise be able to do, such as insert rows into a table.
GRANT OPTION
Grant procedure privileges. You can only grant privileges that you have.
GRANT EXECUTE ON PROCEDURE mysql.create_db TO maintainer;
PROXY
Permits one user to be a proxy for another.
The PROXY
privilege allows one user to proxy as another user, which means their privileges change to that of the proxy user, and the CURRENT_USER() function returns the user name of the proxy user.
The PROXY
privilege only works with authentication plugins that support it. The default mysql_native_password authentication plugin does not support proxy users.
The pam authentication plugin is the only plugin included with MariaDB that currently supports proxy users. The PROXY
privilege is commonly used with the pam authentication plugin to enable user and group mapping with PAM.
For example, to grant the PROXY
privilege to an anonymous account that authenticates with the pam authentication plugin, you could execute the following:
CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'dba'@'%' TO ''@'%';
A user account can only grant the PROXY
privilege for a specific user account if the granter also has the PROXY
privilege for that specific user account, and if that privilege is defined WITH GRANT OPTION
. For example, the following example fails because the granter does not have the PROXY
privilege for that specific user account at all:
SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER() | CURRENT_USER() |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+
SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+-----------------------------------------------------------------------------------------------------------------------+
GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost';
ERROR 1698 (28000): Access denied for user 'alice'@'localhost'
And the following example fails because the granter does have the PROXY
privilege for that specific user account, but it is not defined WITH GRANT OPTION
:
SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER() | CURRENT_USER() |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+
SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT PROXY ON 'dba'@'localhost' TO 'alice'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------------+
GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost';
ERROR 1698 (28000): Access denied for user 'alice'@'localhost'
But the following example succeeds because the granter does have the PROXY
privilege for that specific user account, and it is defined WITH GRANT OPTION
:
SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER() | CURRENT_USER() |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+
SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION |
| GRANT PROXY ON 'dba'@'localhost' TO 'alice'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost';
A user account can grant the PROXY
privilege for any other user account if the granter has the PROXY
privilege for the ''@'%'
anonymous user account, like this:
GRANT PROXY ON ''@'%' TO 'dba'@'localhost' WITH GRANT OPTION;
For example, the following example succeeds because the user can grant the PROXY
privilege for any other user account:
SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER() | CURRENT_USER() |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+
SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'alice'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
GRANT PROXY ON 'app1_dba'@'localhost' TO 'bob'@'localhost';
Query OK, 0 rows affected (0.004 sec)
GRANT PROXY ON 'app2_dba'@'localhost' TO 'carol'@'localhost';
Query OK, 0 rows affected (0.004 sec)
The default root
user accounts created by mariadb-install-db have this privilege. For example:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION;
This allows the default root
user accounts to grant the PROXY
privilege for any other user account, and it also allows the default root
user accounts to grant others the privilege to do the same.
The authentication options for the GRANT
statement are the same as those for the CREATE USER statement.
The optional IDENTIFIED BY
clause can be used to provide an account with a password. The password should be specified in plain text. It will be hashed by the PASSWORD function prior to being stored.
For example, if our password is mariadb
, then we can create the user with:
GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY 'mariadb';
If you do not specify a password with the IDENTIFIED BY
clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set.
If the user account already exists and if you provide the IDENTIFIED BY
clause, then the user's password will be changed. You must have the privileges needed for the SET PASSWORD statement to change a user's password with GRANT
.
The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.
The optional IDENTIFIED BY PASSWORD
clause can be used to provide an account with a password that has already been hashed. The password should be specified as a hash that was provided by the PASSWORD function. It will be stored as-is.
For example, if our password is mariadb
, then we can find the hash with:
SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb') |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)
And then we can create a user with the hash:
GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY
PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';
If you do not specify a password with the IDENTIFIED BY
clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set.
If the user account already exists and if you provide the IDENTIFIED BY
clause, then the user's password will be changed. You must have the privileges needed for the SET PASSWORD tastatement to change a user's password with GRANT
.
The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.
The optional IDENTIFIED VIA authentication_plugin
allows you to specify that the account should be authenticated by a specific authentication plugin. The plugin name must be an active authentication plugin as per SHOW PLUGINS. If it doesn't show up in that output, then you will need to install it with INSTALL PLUGIN or INSTALL SONAME.
For example, this could be used with the PAM authentication plugin:
GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam;
Some authentication plugins allow additional arguments to be specified after a USING
or AS
keyword. For example, the PAM authentication plugin accepts a service name:
GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam USING 'mariadb';
The exact meaning of the additional argument would depend on the specific authentication plugin.
The USING
or AS
keyword can also be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this:
CREATE USER safe@'%' IDENTIFIED VIA ed25519
USING PASSWORD('secret');
One can specify many authentication plugins, they all work as alternative ways of authenticating a user:
CREATE USER safe@'%' IDENTIFIED VIA ed25519
USING PASSWORD('secret') OR unix_socket;
By default, when you create a user without specifying an authentication plugin, MariaDB uses the mysql_native_password plugin.
It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:
MAX_QUERIES_PER_HOUR
Number of statements that the account can issue per hour (including updates)
MAX_UPDATES_PER_HOUR
Number of updates (not queries) that the account can issue per hour
MAX_CONNECTIONS_PER_HOUR
Number of connections that the account can start per hour
MAX_USER_CONNECTIONS
Number of simultaneous connections that can be accepted from the same account; if it is 0, max_connections will be used instead; if max_connections is 0, there is no limit for this account's simultaneous connections.
MAX_STATEMENT_TIME
Timeout, in seconds, for statements executed by the user. See also Aborting Statements that Exceed a Certain Time to Execute.
If any of these limits are set to 0
, then there is no limit for that resource for that user.
To set resource limits for an account, if you do not want to change that account's privileges, you can issue a GRANT
statement with the USAGE
privilege, which has no meaning. The statement can name some or all limit types, in any order.
Here is an example showing how to set resource limits:
GRANT USAGE ON *.* TO 'someone'@'localhost' WITH
MAX_USER_CONNECTIONS 0
MAX_QUERIES_PER_HOUR 200;
The resources are tracked per account, which means 'user'@'server'
; not per user name or per connection.
The count can be reset for all users using FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mariadb-admin reload.
Users with the CONNECTION ADMIN
privilege or the SUPER
privilege are not restricted by max_user_connections
or max_password_errors
, and they are allowed one additional connection when max_connections
is reached.
Users with the CONNECTION ADMIN
privilege or the SUPER
privilege are restricted by max_user_connections
or max_password_errors
, and they are not allowed one additional connection when max_connections
is reached.
Per account resource limits are stored in the user table, in the mysql database. Columns used for resources limits are named max_questions
, max_updates
, max_connections
(for MAX_CONNECTIONS_PER_HOUR
), and max_user_connections
(for MAX_USER_CONNECTIONS
).
By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them.
To mitigate this concern, MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_, but internally, MariaDB only supports its secure successors.
See Secure Connections Overview for more information about how to determine whether your MariaDB server has TLS support.
You can set certain TLS-related restrictions for specific user accounts. For instance, you might use this with user accounts that require access to sensitive data while sending it across networks that you do not control. These restrictions can be enabled for a user account with the CREATE USER, ALTER USER, or GRANT statements. The following options are available:
REQUIRE NONE
TLS is not required for this account, but can still be used.
REQUIRE SSL
The account must use TLS, but no valid X509 certificate is required. This option cannot be combined with other TLS options.
REQUIRE X509
The account must use TLS and must have a valid X509 certificate. This option implies REQUIRE SSL. This option cannot be combined with other TLS options.
REQUIRE ISSUER 'issuer'
The account must use TLS and must have a valid X509 certificate. Also, the Certificate Authority must be the one specified via the string issuer. This option implies REQUIRE X509. This option can be combined with the SUBJECT, and CIPHER options in any order.
REQUIRE SUBJECT 'subject'
The account must use TLS and must have a valid X509 certificate. Also, the certificate's Subject must be the one specified via the string subject. This option implies REQUIRE X509. This option can be combined with the ISSUER, and CIPHER options in any order.
REQUIRE CIPHER 'cipher'
The account must use TLS, but no valid X509 certificate is required. Also, the encryption used for the connection must use a specific cipher method specified in the string cipher. This option implies REQUIRE SSL. This option can be combined with the ISSUER, and SUBJECT options in any order.
The REQUIRE
keyword must be used only once for all specified options, and the AND
keyword can be used to separate individual options, but it is not required.
For example, you can create a user account that requires these TLS options with the following:
GRANT USAGE ON *.* TO 'alice'@'%'
REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'
AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com'
AND CIPHER 'SHA-DES-CBC3-EDH-RSA';
If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS.
See Securing Connections for Client and Server for information on how to enable TLS on the client and server.
GRANT role TO grantee [, grantee ... ]
[ WITH ADMIN OPTION ]
grantee:
rolename
username [authentication_option]
The GRANT statement is also used to grant the use of a role to one or more users or other roles. In order to be able to grant a role, the grantor doing so must have permission to do so (see WITH ADMIN in the CREATE ROLE article).
Specifying the WITH ADMIN OPTION
permits the grantee to in turn grant the role to another.
For example, the following commands show how to grant the same role to a couple different users.
GRANT journalist TO hulda;
GRANT journalist TO berengar WITH ADMIN OPTION;
If a user has been granted a role, they do not automatically obtain all permissions associated with that role. These permissions are only in use when the user activates the role with the SET ROLE statement.
TO PUBLIC is unavailable.
GRANT <privilege> ON <DATABASE>.<object> TO PUBLIC;
REVOKE <privilege> ON <DATABASE>.<object> FROM PUBLIC;
GRANT ... TO PUBLIC grants privileges to all users with access to the server. The privileges also apply to users created after the privileges are granted. This can be useful when one only wants to state once that all users need to have a certain set of privileges. When running SHOW GRANTS, a user will also see all privileges inherited from PUBLIC. SHOW GRANTS FOR PUBLIC will only show TO PUBLIC grants.
You can create a user that has privileges similar to the default root
accounts by executing the following:
CREATE USER 'alexander'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'alexander'@'localhost' WITH GRANT OPTION;
--skip-grant-tables allows you to start MariaDB without GRANT
. This is useful if you lost your root password.
Password Validation Plugins - permits the setting of basic criteria for passwords
Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.
This page is licensed: GPLv2, originally from fill_help_tables.sql
RENAME USER old_user TO new_user
[, old_user TO new_user] ...
The RENAME USER statement renames existing MariaDB accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql
database. Each account is named using the same format as for the CREATE USER statement; for example, 'jeffrey'@'localhost'
.
If you specify only the user name part of the account name, a host name part of '%'
is used.
If any of the old user accounts do not exist or any of the new user accounts already exist, ERROR 1396 (HY000)
results. If an error occurs, RENAME USER
will still rename the accounts that do not result in an error.
For modifying an existing account, see ALTER USER.
CREATE USER 'donald', 'mickey';
RENAME USER 'donald' TO 'duck'@'localhost', 'mickey' TO 'mouse'@'localhost';
Renaming the host component of a user:
RENAME USER 'foo'@'1.2.3.4' TO 'foo'@'10.20.30.40';
This page is licensed: GPLv2, originally from fill_help_tables.sql
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
The REVOKE
statement enables system administrators to revoke privileges (or roles - see section below) from MariaDB accounts. Each account is named using the same format as for the GRANT
statement; for example, 'jeffrey'@'localhost
'. If you specify only the user name part of the account name, a host name part of '%
' is used. For details on the levels at which privileges exist, the allowablepriv_type
and priv_level
values, and the syntax for specifying users and passwords, see GRANT.
To use the first REVOKE
syntax, you must have theGRANT OPTION
privilege, and you must have the privileges that you are revoking.
To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named user or users:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
To use this REVOKE
syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. See GRANT.
REVOKE SUPER ON *.* FROM 'alexander'@'localhost';
REVOKE role [, role ...]
FROM grantee [, grantee2 ... ]
REVOKE ADMIN OPTION FOR role FROM grantee [, grantee2]
REVOKE
is also used to remove a role from a user or another role that it's previously been assigned to. If a role has previously been set as a default role, REVOKE
does not remove the record of the default role from the mysql.user table. If the role is subsequently granted again, it will again be the user's default. Use SET DEFAULT ROLE NONE to explicitly remove this.
REVOKE role
is also permitted in prepared statements.
REVOKE role
is not permitted in prepared statements.
REVOKE journalist FROM hulda
This page is licensed: GPLv2, originally from fill_help_tables.sql
SET DEFAULT ROLE { role | NONE } [ FOR user@host ]
The SET DEFAULT ROLE
statement sets a default role for a specified (or current) user. A default role is automatically enabled when a user connects (an implicit SET ROLE statement is executed immediately after a connection is established).
To be able to set a role as a default, the role must already have been granted to that user, and one needs the privileges to enable this role (if you cannot do SET ROLE X
, you won't be able to do SET DEFAULT ROLE X
). To set a default role for another user one needs to have write access to the mysql
database.
To remove a user's default role, use SET DEFAULT ROLE NONE [ FOR user@host ]
. The record of the default role is not removed if the role is dropped or revoked, so if the role is subsequently re-created or granted, it will again be the user's default role.
The default role is stored in the default_role
column in the mysql.user table/view, as well as in the Information Schema APPLICABLE_ROLES table, so these can be viewed to see which role has been assigned to a user as the default.
Setting a default role for the current user:
SET DEFAULT ROLE journalist;
Removing a default role from the current user:
SET DEFAULT ROLE NONE;
Setting a default role for another user. The role has to have been granted to the user before it can be set as default:
CREATE ROLE journalist;
CREATE USER taniel;
SET DEFAULT ROLE journalist FOR taniel;
ERROR 1959 (OP000): Invalid role specification `journalist`
GRANT journalist TO taniel;
SET DEFAULT ROLE journalist FOR taniel;
Viewing mysql.user:
SELECT * FROM mysql.user WHERE user='taniel'\G
*************************** 1. row ***************************
Host: %
User: taniel
...
is_role: N
default_role: journalist
...
Removing a default role for another user
SET DEFAULT ROLE NONE FOR taniel;
This page is licensed: CC BY-SA / Gnu FDL
SET PASSWORD [FOR user] =
{
PASSWORD('some password')
| OLD_PASSWORD('some password')
| 'encrypted password'
}
The SET PASSWORD
statement assigns a password to an existing MariaDB user account.
If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the literal text of the password should be given. If the password is specified without using either function, the password should be the already-encrypted password value as returned by PASSWORD().
With no FOR
clause, this statement sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.
With a FOR
clause, this statement sets the password for a specific account on the current server host. Only clients that have the UPDATE
privilege for the mysql
database can do this. The user value should be given in user_name@host_name
format, where user_name
and host_name
are exactly as they are listed in the User and Host columns of the mysql.user table (or view in current versions) entry.
The argument to PASSWORD() and the password given to MariaDB clients can be of arbitrary length.
SET PASSWORD
(with or without PASSWORD()
) works for accounts authenticated via any authentication plugin that supports passwords stored in the mysql.global_priv table.
The ed25519, mysql_native_password, and mysql_old_password authentication plugins store passwords in the mysql.global_priv table.
If you run SET PASSWORD
on an account that authenticates with one of these authentication plugins that stores passwords in the mysql.global_priv table, then the PASSWORD()
function is evaluated by the specific authentication plugin used by the account. The authentication plugin hashes the password with a method that is compatible with that specific authentication plugin.
The unix_socket, named_pipe, gssapi, and pam authentication plugins do not store passwords in the mysql.global_priv table. These authentication plugins rely on other methods to authenticate the user.
If you attempt to run SET PASSWORD
on an account that authenticates with one of these authentication plugins that doesn't store a password in the mysql.global_priv table, then MariaDB Server will issue an error like the following:
SET PASSWORD is ignored for users authenticating via unix_socket plugin
User accounts do not always require passwords to login.
The unix_socket , named_pipe and gssapi authentication plugins do not require a password to authenticate the user.
The pam authentication plugin may or may not require a password to authenticate the user, depending on the specific configuration.
The mysql_native_password and mysql_old_password authentication plugins require passwords for authentication, but the password can be blank. In that case, no password is required.
If you provide a password while attempting to log into the server as an account that doesn't require a password, then MariaDB server will simply ignore the password.
A user account can be defined to use multiple authentication plugins in a specific order of preference. This specific scenario may be more noticeable in these versions, since an account could be associated with some authentication plugins that require a password, and some that do not.
For example, if you had an entry with User and Host column values of 'bob
' and '%.loc.gov
', you would write the statement like this:
SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
If you want to delete a password for a user, you would do:
SET PASSWORD FOR 'bob'@localhost = PASSWORD("");
Password Validation Plugins - permits the setting of basic criteria for passwords
This page is licensed: GPLv2, originally from fill_help_tables.sql
From MariaDB 12.0, certain users can perform server actions as another user:
This is implemented through the SET SESSION AUTHORIZATION statement.
This permits everything that can be done in a stored procedure with an arbitrary definer.
In particular, this bypasses account lock, expired password, authentication, REQUIRE SSL checks, and so on.
Users are required to have the SET USER privilege.
Does not work inside transactions, the Performance Schema and stored procedures.
select user(), current_user(), database();
+--------------------+--------------------+------------+
| user() | current_user() | database() |
+--------------------+--------------------+------------+
| msandbox@localhost | msandbox@localhost | test |
+--------------------+--------------------+------------+
1 row in set (0.000 sec)
set session authorization foo@localhost;
select user(), current_user(), database();
+---------------+----------------+------------+
| user() | current_user() | database() |
+---------------+----------------+------------+
| foo@localhost | foo@% | NULL |
+---------------+----------------+------------+
SET ROLE { role | NONE }
The SET ROLE
statement enables a role, along with all of its associated permissions, for the current session. To unset a role, use NONE
.
If a role that doesn't exist, or to which the user has not been assigned, is specified, an ERROR 1959 (OP000): Invalid role specification
error occurs.
An automatic SET ROLE is implicitly performed when a user connects if that user has been assigned a default role. See SET DEFAULT ROLE.
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL |
+--------------+
SET ROLE staff;
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| staff |
+--------------+
SET ROLE NONE;
SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NULL |
+----------------+
This page is licensed: CC BY-SA / Gnu FDL
BINLOG 'str'
BINLOG
is an internal-use statement. It is generated by themariadb-binlog program as the printable representation of certain events in binary log 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 BINLOG REPLAY privilege.
This statement requires the SUPER privilege.
This page is licensed: GPLv2, originally from fill_help_tables.sql
CACHE INDEX
tbl_index_list [, tbl_index_list] ...
IN key_cache_name
tbl_index_list:
tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]
The CACHE INDEX
statement assigns table indexes to a specific key
cache. It is used only for MyISAM tables.
A default key cache exists and cannot be destroyed. To create more key caches, the key_buffer_size 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 configuration file and include some CACHE INDEX
(and optionally LOAD INDEX) statements in the init file.
The following statement assigns indexes from the tables t1, t2, and t3 to the key cache named hot_cache:
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 |
+---------+--------------------+----------+----------+
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)
Move the file to the new key cache (under file share lock)
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 fill_help_tables.sql
{DESCRIBE | DESC} tbl_name [col_name | wild]
DESCRIBE
provides information about the columns in a table.It is a shortcut for SHOW COLUMNS FROM.These statements also display information for views.
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.
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 | |
+------------+----------+------+-----+---------+----------------+
The description for SHOW COLUMNS providesmore information about the output columns.
This page is licensed: GPLv2, originally from fill_help_tables.sql
HELP search_string
The HELP
command can be used in any MariaDB client, such as the mariadb command-line client, to get basic syntax help and a short description for most commands and functions.
If you provide an argument to the HELP
command, the mariadb client uses it as a search string to access server-side help. The proper operation of this command requires that the help tables in the mysql
database be initialized with help topic information.
If there is no match for the search string, the search fails. Use HELP contents
to see a list of the help categories:
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
If a search string matches multiple items, MariaDB shows a list of matching topics:
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
Then you can enter a topic as the search string to see the help entry for that topic.
The help is provided with the MariaDB server and makes use of four help tables found in the mysql
database: help_relation, help_topic, help_category and help_keyword. These tables are populated by the mariadb-install-db or fill_help_table.sql
scripts.
This page is licensed: GPLv2, originally from fill_help_tables.sql
KILL [HARD | SOFT] { {CONNECTION|QUERY} thread_id | QUERY ID query_id | USER user_name }
Each connection to mariadbd runs in a separate thread. You can see which threadsare running with the SHOW PROCESSLIST
statement and kill athread with the KILL thread_id
statement.KILL
allows the optional CONNECTION
orQUERY
modifier:
KILL CONNECTION
is the same as KILL
with no modifier: It terminates the connection associated with the given thread or query id.
KILL QUERY
terminates the statement that the connection thread_id is currently executing, but leaves the connection itself intact.
KILL QUERY ID
terminates the query by query_id, leaving the connection intact.
If a connection is terminated that has an active transaction, the transaction will be rolled back. If only a query is killed, the current transaction will stay active. See also idle_transaction_timeout.
If you have the PROCESS privilege, you can see all threads.
If you have the CONNECTION ADMIN privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.
If you have the SUPER privilege, the CONNECTION ADMIN privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.
Killing queries that repair or create indexes on MyISAM and Aria tables may result in corrupted tables. Use the SOFT
option to avoid this!
The HARD
option (default) kills a command as soon as possible. If you useSOFT
, then critical operations that may leave a table in an inconsistent state will not be interrupted. Such operations include REPAIR
and INDEX
creation for MyISAM and Aria tables (REPAIR TABLE, OPTIMIZE TABLE).
KILL ... USER username
will kill all connections/queries for agiven user. USER
can be specified one of the following ways:
username (Kill without regard to hostname)
username@hostname
If you specify a thread id and that thread does not exist, you get the following error:
ERROR 1094 (HY000): Unknown thread id: <thread_id>
If you specify a query id that doesn't exist, you get the following error:
ERROR 1957 (HY000): Unknown query id: <query_id>
However, if you specify a user name, no error is issued for non-connected (or even non-existing) users. To check if the connection/query has been killed, you can use the ROW_COUNT() function.
A client whose connection is killed receives the following error:
ERROR 1317 (70100): Query execution was interrupted
To obtain a list of existing sessions, use the SHOW PROCESSLIST statement or query the Information Schema PROCESSLIST table.
Note: You cannot use KILL
with the Embedded MariaDB Serverlibrary because the embedded server merely runs inside the threads of the hostapplication. It does not create any connection threads of its own.
Note: You can also usemariadb-admin kill thread_id [,thread_id...]
to kill connections. To get a list of running queries,use mariadb-admin processlist
. See mariadb-admin.
Percona Toolkit contains a program, pt-kill that can be used to automatically kill connections that match certain criteria. For example, it can be used to terminate idle connections, or connections that have been busy for more than 60 seconds.
This page is licensed: GPLv2, originally from fill_help_tables.sql
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
The PURGE BINARY LOGS
statement deletes all the binary logfiles 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.
The datetime expression is in the format 'YYYY-MM-DD hh:mm:ss'.
If a replica is active but has yet to read from a binary log file you attempt to delete, the statement will fail with an error. However, if the replica is not connected and has yet to read from a log file you delete, the file will be deleted, but the replica will be unable to continue replicating once it connects again.
This statement has no effect if the server was not started with the--log-bin option to enable binary logging.
To list the binary log files on the server, use SHOW BINARY LOGS. To see which files they are reading, use SHOW REPLICA STATUS. You can only delete the files that are older than the oldest file that is used by the slaves.
To list the binary log files on the server, use SHOW BINARY LOGS. To see which files they are reading, use SHOW SLAVE STATUS. You can only delete the files that are older than the oldest file that is used by the slaves.
To delete all binary log files, use RESET MASTER.To move to a new log file (for example if you want to remove the current log file), use FLUSH LOGS before you execute PURGE LOGS
.
If the expire_logs_days server system variable is not set to 0, the server automatically deletes binary log files after the given number of days. From MariaDB 10.6, the binlog_expire_logs_seconds variable allows more precise control over binlog deletion, and takes precedence if both are non-zero.
Requires the BINLOG ADMIN privilege.
Requires the SUPER privilege.
PURGE BINARY LOGS TO 'mariadb-bin.000063';
PURGE BINARY LOGS BEFORE '2013-04-21';
PURGE BINARY LOGS BEFORE '2013-04-22 09:55:22';
This page is licensed: GPLv2, originally from fill_help_tables.sql
RESET reset_option [, reset_option] ...
The RESET
statement is used to clear the state of various server operations. You must have the RELOAD privilege to executeRESET
.
RESET
acts as a stronger version of the FLUSH statement.
The different RESET
options are:
SLAVE ["connection_name"] [ALL]
Deletes all relay logs from the slave and reset the replication position in the master binary log.
Deletes all old binary logs, makes the binary index file (--log-bin-index) empty and creates a new binary log file. This is useful when you want to reset the master to an initial state. If you want to just delete old, not used binary logs, you should use the PURGE BINARY LOGS command.
QUERY CACHE
Removes all queries from the query cache. See also FLUSH QUERY CACHE.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHUTDOWN [WAIT FOR ALL { SLAVES | REPLICAS } ]
The SHUTDOWN
command shuts the server down.
The WAIT FOR ALL REPLICAS
statement can be used throughout.
The WAIT FOR ALL SLAVES
option was first added in MariaDB 10.4.4.
When a primary server is shutdown and it goes through the normal shutdown process, the primary kills client threads in random order. By default, the primary also considers its binary log dump threads to be regular client threads. As a consequence, the binary log dump threads can be killed while client threads still exist, and this means that data can be written on the primary during a normal shutdown that won't be replicated. This is true even if semi-synchronous replication is being used.
This problem can be solved by shutting down the server with the SHUTDOWN command and by providing the WAIT FOR ALL REPLICAS
/WAIT FOR ALL SLAVES
option to the command. For example:
SHUTDOWN WAIT FOR ALL REPLICAS;
When the WAIT FOR ALL REPLICAS
option is provided, the server only kills its binary log dump threads after all client threads have been killed, and it only completes the shutdown after the last binary log has been sent to all connected replicas.
See Replication Threads: Binary Log Dump Threads and the Shutdown Process for more information.
One must have a SHUTDOWN
privilege (see GRANT) to use this command. It is the same privilege one needs to use the mariadb-admin shutdown command.
If you are doing a shutdown to migrate to another major version of MariaDB, please ensure that the innodb_fast_shutdown variable is not 2 (fast crash shutdown). The default of this variable is 1.
The following example shows how to create an event which turns off the server at a certain time:
CREATE EVENT `test`.`shutd`
ON SCHEDULE
EVERY 1 DAY
STARTS '2014-01-01 20:00:00'
COMMENT 'Shutdown Maria when the office is closed'
DO BEGIN
SHUTDOWN;
END;
You can use the mariadb-admin shutdown command to take down mariadbd cleanly.
You can also use the system kill command on Unix with signal SIGTERM (15)
kill -SIGTERM pid-of-mariadbd-process
You can find the process number of the server process in the file that ends with .pid
in your data directory.
The above is identical to mariadb-admin shutdown
.
On windows you should use:
NET STOP MariaDB
This page is licensed: CC BY-SA / Gnu FDL
USE db_name
From MariaDB 11.7, one can also use
USE DATABASE db_name;
The 'USE db_name'
statement tells MariaDB to use thedb_name
database as the default (current) database forsubsequent statements. The database remains the default until the end of thesession or another USE
statement is issued:
USE db1;
SELECT COUNT(*) FROM mytable; # selects FROM db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable; # selects FROM db2.mytable
The DATABASE() function (SCHEMA() is a synonym) returns the default database.
Another way to set the default database is specifying its name at mariadb command line client startup.
One cannot use USE DATABASE
to a database one has no privileges to. The reason is thata user with no privileges to a database should not be able to know if a database exists or not.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Learn about ANALYZE and EXPLAIN statements in MariaDB Server. This section details how to optimize queries by analyzing table distribution statistics and understanding query execution plans.
ANALYZE FORMAT=JSON
is a mix of the EXPLAIN FORMAT=JSON and ANALYZE 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.
You can get the following also from tabular ANALYZE
statement form:
r_rows
is provided for any node that reads rows. It shows how many rows were read, on average.
r_filtered
is provided whenever there is a condition that is checked. It shows the percentage of rows left after checking the condition.
The most important data not available in the regular tabular ANALYZE
statement are:
r_loops
field. This shows how many times the node was executed. Most query plan elements have this field.
r_total_time_ms
field. It shows how much time in total, in milliseconds, was spent executing this node. If the node has subnodes, their execution time is included.
For UPDATE and DELETE statements, top-level query_block.r_total_time_ms
does include the time to make row deletions/updates but does NOT include the time to commit the changes.
r_buffer_size
field. Query plan nodes that make use of buffers report the size of buffer that was used.
Starting from MariaDB 10.6.15, MariaDB 10.8.8, MariaDB 10.9.8, MariaDB 10.10.6, MariaDB 10.11.5, MariaDB 11.0.3, MariaDB 11.1.2 and MariaDB 11.2.1 (MDEV-31577), the following statistics are reported for InnoDB tables:
"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
}
Only non-zero members are printed.
pages_accessed
is the total number of buffer pool pages accessed when reading this table.
pages_updated
is the total number of buffer pool pages that were modified during the execution of the statement.
pages_read_count
is the number of pages that InnoDB had to read from disk for this table. If the query touches "hot" data in the InnoDB buffer pool, this value will be 0 and not present.
pages_prefetch_read_count
Number of pages for which read-ahead was initiated. Not all such pages will necessarily be accessed.
pages_read_time_ms
is the total time spent reading the table.
old_rows_read
is the number of old row versions that InnoDB had to read. Old row version is the version of the row that is not visible to this transaction.
MariaDB starting with 10.9
SHOW ANALYZE FORMAT=JSON for <connection_id>
extends ANALYZE [FORMAT=JSON] <select>
to allow one to analyze a query currently running in another connection.
filesort
node reports whether sorting was done with LIMIT n
parameter, and how many rows were in the sort result.
block-nl-join
node has r_loops
field, which allows to tell whether Using join buffer
was efficient.
range-checked-for-each-record
reports counters that show the result of the check.
expression-cache
is used for subqueries, and it reports how many times the cache was used, and what cache hit ratio was.
union_result
node has r_rows
so one can see how many rows were produced after UNION operation and so forth.
See Examples of ANALYZE FORMAT=JSON.
This page is licensed: CC BY-SA / Gnu FDL
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.
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
}
}
}
]
}
}
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
This article describes how to interpret r_rows
and r_filtered
members in ANALYZE FORMAT=JSON when an index-based access method is used.
Index-based access method may employ some or all of the following:
attached_condition checking
Consider a table access which does all three:
"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"
}
The access is performed as follows:
In MariaDB versions before 11.5, the counters were counted as follows:
that is,
r_rows
is counted after Index Condition Pushdown check and Rowid Filter check.
r_filtered
only counts selectivity of the attached_condition
.
selectivity of the Rowid Filter is in rowid_filter.r_selectivity_pct
.
Starting from MariaDB 11.5 (MDEV-18478), the row counters are:
r_index_rows
counts the number of enumerated index tuples, before any checks are made
r_rows
is the same as before - number of rows after index checks.
The selectivity counters are:
r_icp_filtered
is the percentage of records left after pushed index condition check.
rowid_filter.r_selectivity_pct
shows selectivity of Rowid Filter, as before.
r_filtered
is the selectivity of attached_condition
check, as before.
r_total_filtered
is the combined selectivity of all checks.
in ANALYZE FORMAT=JSON output these members are placed as follows:
"table": {
"table_name": ...,
"rows": 426,
"r_index_rows": 349,
"r_rows": 34,
Whenever applicable, r_index_rows
is shown. It is comparable with rows
- both are numbers of rows to enumerate before any filtering is done.
If r_index_rows
is not shown, r_rows
shows the number of records enumerated.
Then, filtering members:
...
"filtered": 8.476269722,
"r_total_filtered": 9.742120344,
filtered
is comparable with r_total_filtered
: both show total amount of filtering.
...
"index_condition": "lineitem.l_quantity > 47",
"r_icp_filtered": 100,
ICP and its observed filtering. The optimizer doesn't compute an estimate for this currently.
...
"attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'",
"r_filtered": 100
attached_condition
and its observed filtering.
This page is licensed: CC BY-SA / Gnu FDL
The ANALYZE statement
is similar to the EXPLAIN statement
. ANALYZE statement
will invoke the optimizer, execute the statement, and then produce EXPLAIN
output instead of the result set. The EXPLAIN
output will be annotated with statistics from statement execution.
This lets one check how close the optimizer's estimates about the query plan are to the reality. ANALYZE
produces an overview, while theANALYZE FORMAT=JSON command provides a more detailed view of the query plan and the query execution.
The syntax is
ANALYZE explainable_statement;
where the statement is any statement for which one can run EXPLAIN.
Consider an example:
ANALYZE SELECT * FROM tbl1
WHERE key1
BETWEEN 10 AND 200 AND
col1 LIKE 'foo%'\G
*************************** 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
Compared to EXPLAIN
, ANALYZE
produces two extra columns:
r_rows
is an observation-based counterpart of the rows column. It shows how many rows were actually read from the table.
r_filtered
is an observation-based counterpart of the filtered column. It shows which fraction of rows was left after applying the WHERE condition.
Let's consider a more complicated example.
ANALYZE SELECT *
FROM orders, customer
WHERE
customer.c_custkey=orders.o_custkey AND
customer.c_acctbal < 0 AND
orders.o_totalprice > 200*1000
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| 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 |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
Here, one can see that
For table customer, customer.rows=149095, customer.r_rows=150000. The estimate for number of rows we will read was fairly precise
customer.filtered=18.08, customer.r_filtered=9.13. The optimizer somewhat overestimated the number of records that will match selectivity of condition attached to customer
table (in general, when you have a full scan and r_filtered is less than 15%, it's time to consider adding an appropriate index).
For table orders, orders.rows=7, orders.r_rows=10. This means that on average, there are 7 orders for a given c_custkey, but in our case there were 10, which is close to the expectation (when this number is consistently far from the expectation, it may be time to run ANALYZE TABLE, or even edit the table statistics manually to get better query plans).
orders.filtered=100, orders.r_filtered=30.03. The optimizer didn't have any way to estimate which fraction of records will be left after it checks the condition that is attached to table orders (it's orders.o_totalprice > 200*1000). So, it used 100%. In reality, it is 30%. 30% is typically not selective enough to warrant adding new indexes. For joins with many tables, it might be worth to collect and use column statistics for columns in question, this may help the optimizer to pick a better query plan.
Let's modify the previous example slightly
ANALYZE SELECT *
FROM orders, customer
WHERE
customer.c_custkey=orders.o_custkey AND
customer.c_acctbal < -0 AND
customer.c_comment LIKE '%foo%' AND
orders.o_totalprice > 200*1000;
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| 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 |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
The output of orders.r_rows=NULL and orders.r_filtered=NULL shows that the table orders
was never scanned. Indeed, we can also see customer.r_filtered=0.00. This shows that a part of WHERE attached to table customer
was never satisfied (or, satisfied in less than 0.01% of cases).
ANALYZE FORMAT=JSON produces JSON output. It produces much more information than tabular ANALYZE
.
ANALYZE UPDATE
or ANALYZE DELETE
will actually make updates/deletes (ANALYZE SELECT
will perform the select operation and then discard the resultset).
PostgreSQL has a similar command, EXPLAIN ANALYZE
.
The EXPLAIN in the slow query log feature allows MariaDB to have ANALYZE
output of slow queries printed into the slow query log (see MDEV-6388).
JIRA task for ANALYZE statement, MDEV-406
This page is licensed: CC BY-SA / Gnu FDL
Outdated syntax
The syntax for the EXPLAIN ANALYZE
feature was changed to ANALYZE statement
, available since MariaDB 10.1.0. See ANALYZE statement.
This page is licensed: CC BY-SA / Gnu FDL
EXPLAIN FORMAT=JSON
is a variant of EXPLAIN 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:
EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE col1=1\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 1000,
"filtered": 100,
"attached_condition": "(t1.col1 = 1)"
}
}
}
The output of MariaDB's EXPLAIN FORMAT=JSON
is different from EXPLAIN FORMAT=JSON
in MySQL. The reasons for that are:
MySQL's output has deficiencies.
The output of MySQL's EXPLAIN FORMAT=JSON
is not defined. Even MySQL Workbench has trouble parsing it (see this blog post).
MariaDB has query optimizations that MySQL does not have. This means that MariaDB generates query plans that MySQL does not generate.
TODO: MariaDB's output format description.
ANALYZE FORMAT=JSON produces output like EXPLAIN FORMAT=JSON
, but amended with the data from query execution.
This page is licensed: CC BY-SA / Gnu FDL
EXPLAIN tbl_name [col_name | wild]
or
EXPLAIN [EXTENDED | PARTITIONS | FORMAT=JSON]
{SELECT select_options | UPDATE update_options | DELETE delete_options}
or
EXPLAIN [FORMAT=JSON] FOR CONNECTION <connection_id>
The EXPLAIN
statement can be used either as a synonym forDESCRIBE 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
, UPDATE
or DELETE
, including information about how tables are joined and in which order. EXPLAIN EXTENDED
can be used to provide additional information.
EXPLAIN PARTITIONS
is useful only when examining queries involving partitioned tables. For details, see Partition pruning and selection.
ANALYZE statement performs the query as well as producing EXPLAIN output, and provides actual as well as estimated statistics.
EXPLAIN
output can be printed in the slow query log. See EXPLAIN in the Slow Query Log for details.
EXPLAIN FOR CONNECTION
is an alias for SHOW EXPLAIN FOR
.
SHOW EXPLAIN shows the output of a running statement. In some cases, its output can be closer to reality than EXPLAIN
.
The ANALYZE statement 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 EXPLAIN Analyzer 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.
id
Sequence number that shows in which order tables are joined.
select_type
What kind of SELECT the table comes from.
table
Alias name of table. Materialized temporary tables for sub queries are named <subquery#>
type
How rows are found from the table (join type).
possible_keys
keys in table that could be used to find rows in the table
key
The name of the key that is used to retrieve rows. NULL is no key was used.
key_len
How many bytes of the key that was used (shows if we are using only parts of the multi-column key).
ref
The reference that is used as the key value.
rows
An estimate of how many rows we will find in the table for each key lookup.
Extra
Extra information about this join.
Here are descriptions of the values for some of the more complex columns in EXPLAIN ... SELECT
:
The select_type
column can have the following values:
DEPENDENT SUBQUERY
The SUBQUERY is DEPENDENT.
DEPENDENT UNION
The UNION is DEPENDENT.
DERIVED
The SELECT is DERIVED from the PRIMARY.
MATERIALIZED
The SUBQUERY is MATERIALIZED.
Materialized tables will be populated at first access and will be accessed by the primary key (= one key lookup). Number of rows in EXPLAIN shows the cost of populating the table
PRIMARY
The SELECT is in the outermost query, but there is also a SUBQUERY within it.
SIMPLE
It is a simple SELECT query without any SUBQUERY or UNION.
SUBQUERY
The SELECT is a SUBQUERY of the PRIMARY.
UNCACHEABLE SUBQUERY
The SUBQUERY is UNCACHEABLE.
UNCACHEABLE UNION
The UNION is UNCACHEABLE.
UNION
The SELECT is a UNION of the PRIMARY.
UNION RESULT
The result of the UNION.
This column contains information on how the table is accessed.
ALL
A full table scan is done for the table (all rows are read). This is bad if the table is large and the table is joined against a previous table! This happens when the optimizer could not find any usable index to access rows.
const
There is only one possibly matching row in the table. The row is read before the optimization phase and all columns in the table are treated as constants.
eq_ref
A unique index is used to find the rows. This is the best possible plan to find the row.
filter
A second index is being used with the Rowid Filtering Optimization.
fulltext
A fulltext index is used to access the rows.
index_merge
A 'range' access is done for several index and the found rows are merged. The key column shows which keys are used.
index_subquery
This is similar as ref, but used for sub queries that are transformed to key lookups.
index
A full scan over the used index. Better than ALL but still bad if index is large and the table is joined against a previous table.
range
The table will be accessed with a key over one or more value ranges.
ref_or_null
Like 'ref' but in addition another search for the 'null' value is done if the first value was not found. This happens usually with sub queries.
ref
A non unique index or prefix of an unique index is used to find the rows. Good if the prefix doesn't match many rows.
system
The table has 0 or 1 rows.
unique_subquery
This is similar as eq_ref, but used for sub queries that are transformed to key lookups
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.
Remove the used key columns from the WHERE
(as this will be tested as part of the key lookup).
Remove impossible constant sub expressions. For example WHERE '(a=1 and a=2) OR b=1'
becomes 'b=1'
.
Replace columns with other columns that has identical values: Example: WHERE
a=b
and a=c
may be treated as 'WHERE a=b and a=c and b=c'
.
Add extra conditions to detect impossible row conditions earlier. This happens mainly with OUTER JOIN
where we in some cases add detection of NULL
values in the WHERE
(Part of 'Not exists
' optimization). This can cause an unexpected 'Using where
' in the Extra column.
For each table level we remove expressions that have already been tested when we read the previous row. Example: When joining tables t1
with t2
using the following WHERE 't1.a=1 and t1.a=t2.b'
, we don't have to test 't1.a=1'
when checking rows in t2
as we already know that this expression is true.
const row not found
The table was a system table (a table with should exactly one row), but no row was found.
Distinct
If distinct optimization (remove duplicates) was used. This is marked only for the last table in the SELECT.
Full scan on NULL key
The table is a part of the sub query and if the value that is used to match the sub query will be NULL, we will do a full table scan.
Impossible HAVING
The used HAVING clause is always false so the SELECT will return no rows.
Impossible WHERE noticed after reading const tables.
The used WHERE clause is always false so the SELECT will return no rows. This case was detected after we had read all 'const' tables and used the column values as constant in the WHERE clause. For example: WHERE const_column=5 and const_column had a value of 4.
Impossible WHERE
The used WHERE clause is always false so the SELECT will return no rows. For example: WHERE 1=2
No matching min/max row
During early optimization of MIN()/MAX() values it was detected that no row could match the WHERE clause. The MIN()/MAX() function will return NULL.
no matching row in const table
The table was a const table (a table with only one possible matching row), but no row was found.
No tables used
The SELECT was a sub query that did not use any tables. For example a there was no FROM clause or a FROM DUAL clause.
Not exists
Stop searching after more row if we find one single matching row. This optimization is used with LEFT JOIN where one is explicitly searching for rows that doesn't exists in the LEFT JOIN TABLE. Example: SELECT * FROM t1 LEFT JOIN t2 on (...) WHERE t2.not_null_column IS NULL. As t2.not_null_column can only be NULL if there was no matching row for on condition, we can stop searching if we find a single matching row.
Open_frm_only
For information_schema tables. Only the frm (table definition file was opened) was opened for each matching row.
Open_full_table
For information_schema tables. A full table open for each matching row is done to retrieve the requested information. (Slow)
Open_trigger_only
For information_schema tables. Only the trigger file definition was opened for each matching row.
Range checked for each record (index map: ...)
This only happens when there was no good default index to use but there may some index that could be used when we can treat all columns from previous table as constants. For each row combination the optimizer will decide which index to use (if any) to fetch a row from this table. This is not fast, but faster than a full table scan that is the only other choice. The index map is a bitmask that shows which index are considered for each row condition.
Scanned 0/1/all databases
For information_schema tables. Shows how many times we had to do a directory scan.
Select tables optimized away
All tables in the join was optimized away. This happens when we are only using COUNT(*), MIN() and MAX() functions in the SELECT and we where able to replace all of these with constants.
Skip_open_table
For information_schema tables. The queried table didn't need to be opened.
unique row not found
The table was detected to be a const table (a table with only one possible matching row) during the early optimization phase, but no row was found.
Using filesort
Filesort is needed to resolve the query. This means an extra phase where we first collect all columns to sort, sort them with a disk based merge sort and then use the sorted set to retrieve the rows in sorted order. If the column set is small, we store all the columns in the sort file to not have to go to the database to retrieve them again.
Using index
Only the index is used to retrieve the needed information from the table. There is no need to perform an extra seek to retrieve the actual record.
Using index condition
Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the index level.
Using index condition(BKA)
Like 'Using index condition' but in addition we use batch key access to retrieve rows.
Using index for group-by
The index is being used to resolve a GROUP BY or DISTINCT query. The rows are not read. This is very efficient if the table has a lot of identical index entries as duplicates are quickly jumped over.
Using intersect(...)
For index_merge joins. Shows which index are part of the intersect.
Using join buffer
We store previous row combinations in a row buffer to be able to match each row against all of the rows combinations in the join buffer at one go.
Using sort_union(...)
For index_merge joins. Shows which index are part of the union.
Using temporary
A temporary table is created to hold the result. This typically happens if you are using GROUP BY, DISTINCT or ORDER BY.
Using where
A WHERE expression (in additional to the possible key lookup) is used to check if the row should be accepted. If you don't have 'Using where' together with a join type of ALL, you are probably doing something wrong!
Using where with pushed condition
Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the row level.
Using buffer
The UPDATE statement will first buffer the rows, and then run the updates, rather than do updates on the fly. See Using Buffer UPDATE Algorithm for a detailed explanation.
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 SHOW WARNINGS 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.
As synonym for DESCRIBE
or SHOW COLUMNS FROM
:
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 | |
+------------+----------+------+-----+---------+----------------+
A simple set of examples to see how EXPLAIN
can identify poor index usage:
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;
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees_example | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | |
| employees_example | 0 | employee_code | 1 | employee_code | A | 7 | NULL | NULL | | BTREE | | |
| employees_example | 1 | first_name | 1 | first_name | A | NULL | NULL | NULL | | BTREE | | |
| employees_example | 1 | first_name | 2 | last_name | A | NULL | NULL | NULL | | BTREE | | |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
SELECT
on a primary key:
EXPLAIN SELECT * FROM employees_example WHERE id=1;
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employees_example | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
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:
EXPLAIN SELECT * FROM employees_example WHERE home_phone='326-555-3492';
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees_example | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
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.
SHOW EXPLAIN example:
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)
ref_or_null
OptimizationSELECT * FROM table_name
WHERE key_column=expr OR key_column IS NULL;
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.
This page is licensed: GPLv2, originally from fill_help_tables.sql
This article explains the UPDATE statement's Using Buffer algorithm.
Consider the following table and query:
Babatunde
1000
Jolana
1050
Pankaja
1300
UPDATE employees SET salary = salary+100 WHERE salary < 2000;
Suppose the employees table has an index on the salary column, and the optimizer decides to use a range scan on that index.
The optimizer starts a range scan on the salary index. We find the first record Babatunde, 1000. If we do an on-the-fly update, we immediately instruct the storage engine to change this record to be Babatunde, 1000+100=1100.
Then we proceed to search for the next record, and find Jolana, 1050. We instruct the storage engine to update it to be Jolana, 1050+100=1150.
Then we proceed to search for the next record ... and what happens next depends on the storage engine. In some storage engines, data changes are visible immediately, so we will find the Babatunde, 1100 record that we wrote at the first step, modifying it again, giving Babatunde an undeserved raise. Then we will see Babatunde again and again, looping continually.
In order to prevent such situations, the optimizer checks whether the UPDATE statement is going to change key values for the keys it is using. In that case, it will use a different algorithm:
Scan everyone with "salary<2000", remembering the rowids of the rows in a buffer.
Read the buffer and apply the updates.
This way, each row will be updated only once.
The Using buffer
EXPLAIN output indicates that the buffer as described above will be used.
This page is licensed: CC BY-SA / Gnu FDL
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.
BACKUP LOCK blocks a table from DDL statements. This is mainly intended to be used by tools like mariadb-backup that need to ensure there are no DDLs on a table while the table files are opened. For example, for an Aria table that stores data in 3 files with extensions .frm, .MAI and .MAD. Normal read/write operations can continue as normal.
To lock a table:
BACKUP LOCK table_name
To unlock a table:
BACKUP UNLOCK
BACKUP LOCK [database.]table_name;
- Open all files related to a table (for example, t.frm, t.MAI and t.MYD)
BACKUP UNLOCK;
- Copy data
- Close files
This ensures that all files are from the same generation, that is created at the same time by the MariaDB server. This works, because the open files will point to the original table files which will not be affected if there is any ALTER TABLE while copying the files.
BACKUP LOCK requires the database LOCK TABLES privileges.
BACKUP LOCK requires the RELOAD privilege.
BACKUP LOCK requires the RELOAD privilege.
The idea is that the BACKUP LOCK
should be held for as short a time as possible by the backup tool. The time to take an uncontested lock is very short! One can easily do 50,000 locks/unlocks per second on low end hardware.
One should use different connections for BACKUP STAGE commands and BACKUP LOCK
.
Internally, BACKUP LOCK is implemented by taking an MDLSHARED_HIGH_PRIO
MDL lock on the table object, which protects the table from any DDL operations.
MDEV-17309 - BACKUP LOCK: DDL locking of tables during backup
This page is licensed: CC BY-SA / Gnu FDL
The BACKUP STAGE
commands are a set of commands to make it possible to make an efficient external backup tool.
BACKUP STAGE [START | FLUSH | BLOCK_DDL | BLOCK_COMMIT | END ]
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.
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 mariadb-backup 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
.
Should work efficiently with backup tools that use disk snapshots.
Should work as efficiently as possible for all table types that store data on the local disks.
As little copying as possible under higher level stages/locks. For example, .frm (dictionary) and .trn (trigger) files should be copying while copying the table data.
BACKUP STAGE
CommandsBACKUP 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 CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and RENAME TABLE.
Blocks also start off a new ALTER TABLE and the final rename phase of ALTER TABLE. 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
BACKUP STAGE
Commands with Backup ToolsBACKUP STAGE
Commands with mariadb-backupThe 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 mariadb-backup 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 MariaDB Enterprise Server.
The BACKUP STAGE
commands are a set of commands to make it possible to make an efficient external backup tool. How mariadb-backup uses these commands depends on 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 MariaDB Enterprise Server.
See mariadb-backup and BACKUP STAGE Commands for some examples on how mariadb-backup uses these commands.
BACKUP STAGE
Commands with Storage SnapshotsThe 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 Storage Snapshots and BACKUP STAGE Commands for some examples on how to use each BACKUP STAGE
command in an efficient way.
BACKUP STAGE
requires the RELOAD privilege.
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.
One can use the max_statement_time or lock_wait_timeout system variables to ensure that a BACKUP STAGE
command doesn't block the server too long.
DDL logging is only be available from MariaDB 10.11.8, MariaDB 11.0.6, MariaDB 11.1.5 and MariaDB 11.2.4, or in MariaDB Enterprise Server.
A disconnect will automatically release backup stages.
There is no easy way to see which is the current stage.
BACKUP LOCK Locking a table from DDL.
MDEV-5336. Implement BACKUP STAGE for safe external backups.
This page is licensed: CC BY-SA / Gnu FDL
The BACKUP STAGE 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.
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:
First, execute the following:
BACKUP STAGE START
BACKUP STAGE BLOCK_COMMIT
Then, take the snapshot.
Then, execute the following:
BACKUP STAGE END
The above ensures that all non-transactional tables are properly flushed to disk before the snapshot is done. Using BACKUP STAGE
commands is also more efficient than using the FLUSH TABLES WITH READ LOCK command as the above set of commands will not block or be blocked by write operations to transactional tables.
Note that when the backup is completed, one should delete all files with the "#sql" prefix, as these are files used by concurrent running ALTER TABLE
. Note that InnoDB will on server restart automatically delete any tables with the "#sql" prefix.
This page is licensed: CC BY-SA / Gnu FDL
Learn about FLUSH statements in MariaDB Server. This section details how to clear various internal caches and logs, essential for administrative tasks and ensuring consistent database state.
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
FLUSH TABLE[S] table_name [, table_name] FOR EXPORT
FLUSH TABLES ... FOR EXPORT
flushes changes to the specified tables to disk so that binary copies can be made while the server is still running. This works for Archive, Aria, CSV, InnoDB, MyISAM and MERGE tables.
The table is read locked until you issue UNLOCK TABLES.
If a storage engine does not support FLUSH TABLES FOR EXPORT
, a 1031 error (SQLSTATE 'HY000') is produced.
If FLUSH TABLES ... FOR EXPORT
is in effect in the session, the following statements will produce an error if attempted:
FLUSH TABLES WITH READ LOCK
FLUSH TABLES ... WITH READ LOCK
FLUSH TABLES ... FOR EXPORT
Any statement trying to update any table
If any of the following statements is in effect in the session, attempting FLUSH TABLES ... FOR EXPORT
will produce an error.
FLUSH TABLES ... WITH READ LOCK
FLUSH TABLES ... FOR EXPORT
LOCK TABLES ... READ
LOCK TABLES ... WRITE
FLUSH FOR EXPORT
is not written to the binary log.
This statement requires the RELOAD and the LOCK TABLES privileges.
If one of the specified tables cannot be locked, none of the tables will be locked.
If a table does not exist, an error like the following will be produced:
ERROR 1146 (42S02): Table 'test.xxx' doesn't exist
If a table is a view, an error like the following will be produced:
ERROR 1347 (HY000): 'test.v' is not BASE TABLE
FLUSH TABLES test.t1 FOR EXPORT;
# Copy files related to the table (see below)
UNLOCK TABLES;
For a full description, please see copying MariaDB tables.
myisampack - Compressing the MyISAM data file for easier distribution.
aria_pack - Compressing the Aria data file for easier distribution
This page is licensed: CC BY-SA / Gnu FDL
FLUSH [NO_WRITE_TO_BINLOG | LOCAL]
flush_option [, flush_option] ...
or when flushing tables:
FLUSH [NO_WRITE_TO_BINLOG | LOCAL] TABLES [table_list] [table_flush_option]
where table_list
is a list of tables separated by ,
(comma).
The FLUSH
statement clears or reloads various internal caches used by MariaDB. To execute FLUSH
, you must have the RELOAD
privilege. See GRANT.
The RESET
statement is similar to FLUSH
. See RESET.
You cannot issue a FLUSH statement from within a stored function or a trigger. Doing so within a stored procedure is permitted, as long as it is not called by a stored function or trigger. See Stored Routine Limitations, Stored Function Limitations and Trigger Limitations.
If a listed table is a view, an error like the following will be produced:
ERROR 1347 (HY000): 'test.v' is not BASE TABLE
By default, FLUSH
statements are written to the binary log and will be replicated. The NO_WRITE_TO_BINLOG
keyword (LOCAL
is an alias) will ensure the statement is not written to the binary log.
The different flush options are:
CHANGED_PAGE_BITMAPS
XtraDB only. Internal command used for backup purposes. See the Information Schema CHANGED_PAGE_BITMAPS Table.
CLIENT_STATISTICS
Reset client statistics (see SHOW CLIENT_STATISTICS).
DES_KEY_FILE
Reloads the DES key file (Specified with the --des-key-file startup option).
HOSTS
Flush the hostname cache (used for converting ip to host names and for unblocking blocked hosts. See max_connect_errors and performance_schema.host_cache
INDEX_STATISTICS
Reset index statistics (see SHOW INDEX_STATISTICS).
[ERROR
ENGINE
BINARY LOGS
FLUSH BINARY LOGS rotates the current binary log.
BINARY LOGS DELETE_DOMAIN_ID=(list-of-domains)
FLUSH BINARY LOGS DELETE_DOMAIN_ID can be used to discard obsolete GTID domains from the server's binary log state. In order for this to be successful, no event group from the listed GTID domains can be present in existing binary log files. If some still exist, then they must be purged prior to executing this command. If the command completes successfully, then it also rotates the binary log.
MASTER
Deprecated option, use RESET MASTER instead.
PRIVILEGES
Reload all privileges from the privilege tables in the mysql database. If the server is started with --skip-grant-table option, this will activate the privilege tables again.
Defragment the query cache to better utilize its memory. If you want to reset the query cache, you can do it with RESET QUERY CACHE.
QUERY_RESPONSE_TIME
See the QUERY_RESPONSE_TIME plugin.
QUERY_RESPONSE_TIME_READ
See the QUERY_RESPONSE_TIME plugin. From MariaDB 11.5.
QUERY_RESPONSE_TIME_READ_WRITE
See the QUERY_RESPONSE_TIME plugin. From MariaDB 11.5.
QUERY_RESPONSE_TIME_WRITE
See the QUERY_RESPONSE_TIME plugin. From MariaDB 11.5.
SLAVE
Deprecated option, use RESET REPLICA or RESET SLAVE instead.
SSL
Used to dynamically reinitialize the server's TLS context by reloading the files defined by several TLS system variables. See FLUSH SSL for more information.
[ GLOBAL
SESSION ] STATUS
TABLE[S]
Close tables given as options or all open tables if no table list was used. Using without any table list will only close tables not in use, and tables not locked by the FLUSH TABLES connection. If there are no locked tables, FLUSH TABLES will be instant and will not cause any waits, as it no longer waits for tables in use. When a table list is provided, the server will wait for the end of any transactions that are using the tables. Previously, FLUSH TABLES only waited for the statements to complete.
For InnoDB tables, flushes table changes to disk to permit binary table copies while the server is running. See FLUSH TABLES ... FOR EXPORT for more.
TABLE[S] WITH READ LOCK
Closes all open tables. New tables are only allowed to be opened with read locks until an UNLOCK TABLES is given.
TABLE[S] WITH READ LOCK AND DISABLE CHECKPOINT
As TABLES WITH READ LOCK but also disable all checkpoint writes by transactional table engines. This is useful when doing a disk snapshot of all tables.
TABLE_STATISTICS
Reset table statistics (see SHOW TABLE_STATISTICS).
USER_RESOURCES
Resets all per hour user resources. This enables clients that have exhausted their resources to connect again.
USER_STATISTICS
Reset user statistics (see SHOW USER_STATISTICS).
USER_VARIABLES
Reset user variables (see User-defined variables).
You can also use the mariadb-admin client to flush things. Use mariadb-admin --help
to examine what flush commands it supports.
FLUSH RELAY LOGS
FLUSH RELAY LOGS 'connection_name'
The FOR CHANNEL
keyword was added for MySQL compatibility. This is identical to using the channel_name directly after the FLUSH command
. For example, one can now use:
FLUSH RELAY LOGS FOR CHANNEL 'connection_name';
FOR CHANNEL
isn't available.
Server status variables can be reset by executing the following:
FLUSH STATUS
This statement requires the RELOAD privilege.
Specify FLUSH GLOBAL or FLUSH SESSION. Flushing of global status variables has been moved to FLUSH GLOBAL STATUS
which is a synonym for FLUSH STATUS
.
You can use old-mode=OLD_FLUSH_STATUS
to restore the old behavior of the FLUSH STATUS
statement.
The variables flushed are mainly session, but some are global. Not all session (or global) variables are flushed - the decision was made per variable.
FLUSH STATUS
Not all global status variables support being reset by FLUSH STATUS
. Currently, the following is an incomplete list of status variables are reset by FLUSH GLOBAL STATUS
in 11.5 or FLUSH STATUS
in earlier versions:
MariaDB starting with 10.11.12
FLUSH TABLES
doesn't cause InnoDB statistics to be reloaded or recalculated. RENAME TABLE, however, triggers a reload of the statistics.
FLUSH TABLES
causes InnoDB statistics to be reloaded or recalculated.
FLUSH TABLES
causes InnoDB statistics to be reloaded or recalculated.
The purpose of FLUSH TABLES
is to clean up the open table cache and table definition cache of tables that are not in use. This frees up memory and file descriptors. Normally this is not needed as the caches works on a first-in, first-out basis, but can be useful if the server seems to use too much memory for some reason.
FLUSH TABLES WITH READ LOCK
is useful if you want to take a backup of some tables. When FLUSH TABLES WITH READ LOCK
returns, all write access to tables is blocked and all tables are marked as
'properly closed' on disk. The tables can still be used for read operations.
FLUSH TABLES
table_list is useful if you want to copy a table object or files to or from the server. This command puts a lock that stops new users of the table and will wait until everyone has stopped using the table. The table is then removed from the table definition and table cache.
Note that it's up to the user to ensure that no one is accessing the table between FLUSH TABLES
and the table is copied to or from the server. This can be secured by using LOCK TABLES.
If there are any tables locked by the connection that is using FLUSH TABLES
all the locked tables will be closed as part of the flush and reopened and relocked before FLUSH TABLES
returns. This allows one to copy the table after FLUSH TABLES
returns without having any writes on the table. For now this works with most tables, except InnoDB as InnoDB may do background purges on the table even while it's write locked.
FLUSH TABLES table_list WITH READ LOCK
should work as FLUSH TABLES WITH READ LOCK
, but only those tables that are listed will be properly closed. However in practice this works exactly like FLUSH TABLES WITH READ LOCK
as the FLUSH
command has anyway to wait for all WRITE operations to end because we are depending on a global read lock for this code. In the future we should consider fixing this to instead use meta data locks.
Free memory and file descriptors not in use
Lock all tables read only for simple old style backup.
All background writes are suspended and tables are marked as closed.
No statement requiring table changes are allowed for any user until UNLOCK TABLES
.
Instead of using FLUSH TABLE WITH READ LOCK
one should in most cases instead useBACKUP STAGE BLOCK_COMMIT.
Free memory and file descriptors for tables not in use from table list.
Lock given tables as read only.
Wait until all translations has ended that uses any of the given tables.
Wait until all background writes are suspended and tables are marked as closed.
Free memory and file descriptors for tables not in use from table list.
Lock given tables as read.
Wait until all background writes are suspended and tables are marked as closed.
Check that all tables supports FOR EXPORT
.
No changes to these tables allowed until UNLOCK TABLES
.
This is basically the same behavior as in older MariaDB versions if you first lock the tables, then do FLUSH TABLES
. The tables will be copyable until you issue UNLOCK TABLES
.
The FLUSH SSL
command can be used to dynamically reinitialize the server's TLS context. This is most useful if you need to replace a certificate that is about to expire without restarting the server.
This operation is performed by reloading the files defined by the following TLS system variables:
These TLS system variables are not dynamic, so their values can not be changed without restarting the server.
If you want to dynamically reinitialize the server's TLS context, then you need to change the certificate and key files at the relevant paths defined by these TLS system variables, without actually changing the values of the variables. See MDEV-19341 for more information.
To flush some of the global caches that take up memory, you could execute the following command:
FLUSH LOCAL HOSTS,
QUERY CACHE,
TABLE_STATISTICS,
INDEX_STATISTICS,
USER_STATISTICS;
This page is licensed: GPLv2, originally from fill_help_tables.sql
Learn about plugin SQL statements in MariaDB Server. This section details how to install, uninstall, enable, and disable server plugins, extending database functionality as needed.
INSTALL PLUGIN [IF NOT EXISTS] plugin_name SONAME 'plugin_library'
This statement installs an individual plugin from the specified library. To install the whole library (which could be required), use INSTALL SONAME. See also Installing a Plugin.
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 plugin_dir 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:
[mariadbd]
plugin_dir=/path/to/plugin/directory
If the value of plugin_dir is a relative path name, it istaken to be relative to the base directory (the value of the basedir 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 theINSERT privilege 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
mariadbd option.
When the IF NOT EXISTS
clause is used, MariaDB will return a note instead of an error if the specified plugin already exists. See SHOW WARNINGS.
INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
The extension can also be omitted:
INSTALL PLUGIN innodb SONAME 'ha_xtradb';
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 |
+-------+------+------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
INSTALL SONAME 'plugin_library'
This statement is a variant of INSTALL PLUGIN. It installs all plugins from a given plugin_library
. See INSTALL PLUGIN for details.
plugin_library
is the name of the shared library thatcontains the plugin code. The file name extension (forexample, libmyplugin.so
or libmyplugin.dll
) 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 plugin_dir 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:
[mariadbd]
plugin_dir=/path/to/plugin/directory
If the value of plugin_dir is a relative path name, it istaken to be relative to the MySQL base directory (the value of the basedir
system variable).
INSTALL SONAME
adds one or more lines to the mysql.plugin
table thatdescribes the plugin. This table contains the plugin name and library filename.
INSTALL SONAME
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 SONAME
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 SONAME
, you must have theINSERT privilege 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 SONAME
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
mariadbd option.
If you need to install only one plugin from a library, use the INSTALL PLUGIN statement.
To load the LOCALES plugin and all of its information_schema
tables with one statement, use
INSTALL SONAME 'locales';
This statement can be used instead of INSTALL PLUGIN
even when the library contains only one plugin:
INSTALL SONAME 'ha_sequence';
This page is licensed: CC BY-SA / Gnu FDL
UNINSTALL PLUGIN [IF EXISTS] plugin_name
This statement removes a single installed plugin. To uninstall the whole library which contains the plugin, use UNINSTALL SONAME. You cannot uninstall a plugin if any table that uses it is open.
plugin_name
must be the name of some plugin that is listedin the mysql.plugin table. The server executes the plugin's deinitializationfunction and removes the row for the plugin from the mysql.plugin
table, so that subsequent server restarts will not load and initializethe plugin. UNINSTALL PLUGIN
does not remove the plugin'sshared library file.
To use UNINSTALL PLUGIN
, you must have theDELETE privilege for the mysql.plugin table.
If the IF EXISTS
clause is used, MariaDB will return a note instead of an error if the plugin does not exist. See SHOW WARNINGS.
UNINSTALL PLUGIN example;
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 |
+-------+------+-------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
UNINSTALL SONAME [IF EXISTS] 'plugin_library'
This statement is a variant of UNINSTALL PLUGIN statement, that removes all plugins belonging to a specified plugin_library
. See UNINSTALL PLUGIN for details.
plugin_library
is the name of the shared library thatcontains the plugin code. The file name extension (forexample, libmyplugin.so
or libmyplugin.dll
) can be omitted (which makes the statement look the same on all architectures).
To use UNINSTALL SONAME
, you must have theDELETE privilege for the mysql.plugin
table.
If the IF EXISTS
clause is used, MariaDB will return a note instead of an error if the plugin library does not exist. See SHOW WARNINGS.
To uninstall the XtraDB plugin and all of its information_schema
tables with one statement, use
UNINSTALL SONAME 'ha_xtradb';
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 |
+-------+------+-------------------------------------+
This page is licensed: CC BY-SA / Gnu FDL
Learn about replication SQL statements in MariaDB Server. This section covers commands for configuring, controlling, and monitoring replication, essential for high availability and data distribution.
CHANGE MASTER ['connection_name'] TO master_def [, master_def] ...
[FOR CHANNEL 'channel_name']
master_def:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_DELAY = interval
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_CRL = 'crl_file_name'
| MASTER_SSL_CRLPATH = 'crl_directory_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| MASTER_USE_GTID = {current_pos|slave_pos|no}
| MASTER_DEMOTE_TO_SLAVE = bool
| IGNORE_SERVER_IDS = (server_id_list)
| DO_DOMAIN_IDS = ([N,..])
| IGNORE_DOMAIN_IDS = ([N,..])
| MASTER_RETRY_COUNT = long
CHANGE MASTER
is used on a replica to set up or change replication 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.
If you are using multi-source replication, then you need to specify a connection name when you execute CHANGE MASTER
. There are two ways to do this:
Setting the default_master_connection system variable prior to executing CHANGE MASTER
.
Setting the connection_name
parameter when executing CHANGE MASTER
.
SET default_master_connection = 'gandalf';
STOP SLAVE;
CHANGE MASTER TO
MASTER_PASSWORD='new3cret';
START SLAVE;
STOP SLAVE 'gandalf';
CHANGE MASTER 'gandalf' TO
MASTER_PASSWORD='new3cret';
START SLAVE 'gandalf';
The MASTER_USER
option for CHANGE MASTER
defines the user account that the replica will use to connect to the primary.
This user account will need the REPLICATION REPLICA privilege on the primary.
This user account will need the REPLICATION SLAVE privilege on the primary.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_USER='repl',
MASTER_PASSWORD='new3cret';
START SLAVE;
The maximum length of the MASTER_USER
string is 128 characters.
The maximum length of the MASTER_USER
string is 96 characters.
The MASTER_PASSWORD
option for CHANGE MASTER
defines the password that the replica will use to connect to the primary as the user account defined by the MASTER_USER option.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_PASSWORD='new3cret';
START SLAVE;
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 MDEV-29994, 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.
The MASTER_HOST
option for CHANGE MASTER
defines the hostname or IP address of the primary.
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.
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 START SLAVE command would fail.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='dbserver1.example.com',
MASTER_USER='repl',
MASTER_PASSWORD='new3cret',
MASTER_USE_GTID=slave_pos;
START SLAVE;
The maximum length of the MASTER_HOST
string is 255 characters.
The maximum length of the MASTER_HOST
string is 60 characters.
The MASTER_PORT
option for CHANGE MASTER
defines the TCP/IP port of the primary.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='dbserver1.example.com',
MASTER_PORT=3307,
MASTER_USER='repl',
MASTER_PASSWORD='new3cret',
MASTER_USE_GTID=slave_pos;
START SLAVE;
The MASTER_CONNECT_RETRY
option for CHANGE MASTER
defines how many seconds that the replica will wait between connection retries. The default is 60
.
STOP SLAVE;
CHANGE MASTER TO
MASTER_CONNECT_RETRY=20;
START SLAVE;
The MASTER_RETRY_COUNT
option limits the number of connection attempts (i.e., Connects_Tried
in SHOW REPLICA STATUS). For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_RETRY_COUNT=1; # attempt only once; do not retry if it fails
START SLAVE;
Setting this option resets the Connects_Tried
statistic in SHOW REPLICA STATUS to 0.
The default is the --master-retry-count
option, which be set either on the command-line or in a server option group in an option file 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 --master-retry-count
option instead, which be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:
[mariadb]
...
master_retry_count=4294967295
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 MDEV-19248 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.
The MASTER_HEARTBEAT_PERIOD
option for CHANGE MASTER
can be used to set the interval in seconds between replication heartbeats. Whenever the primary's binary log 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
.
Its default value is the value of the slave_net_timeout system variable divided by 2.
If it's set to 0
, then heartbeats are disabled.
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 RESET SLAVE statement is executed, then the heartbeat interval is reset to the default.
The TLS options are used for providing information about TLS. 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 master_info_file option, but these TLS options are ignored unless the replica supports TLS.
See Replication with Secure Connections for more information.
The MASTER_SSL
option for CHANGE MASTER
tells the replica whether to force TLS 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:
STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL=1;
START SLAVE;
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 TLS. This option requires that you use the absolute path, not a relative path.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;
See Secure Connections Overview: Certificate Authorities (CAs) for more information.
The maximum length of MASTER_SSL_CA
string is 511 characters.
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 TLS. 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 openssl rehash command.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CAPATH='/etc/my.cnf.d/certificates/ca/',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;
See Secure Connections Overview: Certificate Authorities (CAs) for more information.
The maximum length of MASTER_SSL_CA_PATH
string is 511 characters.
The MASTER_SSL_CERT
option for CHANGE MASTER
defines a path to the X509 certificate file to use for TLS. This option requires that you use the absolute path, not a relative path.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;
The maximum length of MASTER_SSL_CERT
string is 511 characters.
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 TLS. 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 TLS and Cryptography Libraries Used by MariaDB for more information about which libraries are used on which platforms.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1,
MASTER_SSL_CRL='/etc/my.cnf.d/certificates/crl.pem';
START SLAVE;
See Secure Connections Overview: Certificate Revocation Lists (CRLs) for more information.
The maximum length of MASTER_SSL_CRL
string is 511 characters.
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 TLS. 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 openssl rehash command.
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 TLS and Cryptography Libraries Used by MariaDB for more information about which libraries are used on which platforms.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1,
MASTER_SSL_CRLPATH='/etc/my.cnf.d/certificates/crl/';
START SLAVE;
See Secure Connections Overview: Certificate Revocation Lists (CRLs) for more information.
The maximum length of MASTER_SSL_CRL_PATH
string is 511 characters.
The MASTER_SSL_KEY
option for CHANGE MASTER
defines a path to a private key file to use for TLS. This option requires that you use the absolute path, not a relative path.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;
The maximum length of MASTER_SSL_KEY
string is 511 characters.
The MASTER_SSL_CIPHER
option for CHANGE MASTER
defines the list of permitted ciphers or cipher suites to use for TLS. 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 Using TLSv1.3 for details.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1,
MASTER_SSL_CIPHER='TLSv1.2';
START SLAVE;
The maximum length of MASTER_SSL_CIPHER
string is 511 characters.
The MASTER_SSL_VERIFY_SERVER_CERT
option for CHANGE MASTER
enables server certificate verification. This option is enabled by default.
The MASTER_SSL_VERIFY_SERVER_CERT
option for CHANGE MASTER
enables server certificate verification. This option is disabled by default.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;
See Secure Connections Overview: Server Certificate Verification for more information.
These options are related to the binary log position on the primary.
The MASTER_LOG_FILE
option for CHANGE MASTER
can be used along with MASTER_LOG_POS
to specify the coordinates at which the replica's I/O thread should begin reading from the primary's binary logs the next time the thread starts.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4;
START SLAVE;
The MASTER_LOG_POS
option for CHANGE MASTER
can be used along with MASTER_LOG_FILE
to specify the coordinates at which the replica's I/O thread should begin reading from the primary's binary logs the next time the thread starts.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4;
START SLAVE;
These options are related to the relay log position on the replica.
The RELAY_LOG_FILE
option for CHANGE MASTER
can be used along with the RELAY_LOG_POS option to specify the coordinates at which the replica's SQL thread should begin reading from the relay log the next time the thread starts.
The CHANGE MASTER
statement usually deletes all relay log files. However, if the RELAY_LOG_FILE
and/or RELAY_LOG_POS
options are specified, then existing relay log files are kept.
When you want to change the relay log position, you only need to stop the replica's SQL thread. The replica's I/O thread can continue running. The STOP SLAVE and START SLAVE statements support the SQL_THREAD
option for this scenario. For example:
STOP SLAVE SQL_THREAD;
CHANGE MASTER TO
RELAY_LOG_FILE='slave-relay-bin.006',
RELAY_LOG_POS=4025;
START SLAVE SQL_THREAD;
When the value of this option is changed, the metadata about the replica's SQL thread's position in the relay logs will also be changed in the relay-log.info
file or the file that is configured by the relay_log_info_file system variable.
The RELAY_LOG_POS
option for CHANGE MASTER
can be used along with the RELAY_LOG_FILE option to specify the coordinates at which the replica's SQL thread should begin reading from the relay log the next time the thread starts.
The CHANGE MASTER
statement usually deletes all relay log files. However, if the RELAY_LOG_FILE
and/or RELAY_LOG_POS
options are specified, then existing relay log files are kept.
When you want to change the relay log position, you only need to stop the replica's SQL thread. The replica's I/O thread can continue running. The STOP SLAVE and START SLAVE statements support the SQL_THREAD
option for this scenario. For example:
STOP SLAVE SQL_THREAD;
CHANGE MASTER TO
RELAY_LOG_FILE='slave-relay-bin.006',
RELAY_LOG_POS=4025;
START SLAVE SQL_THREAD;
When the value of this option is changed, the metadata about the replica's SQL thread's position in the relay logs will also be changed in the relay-log.info
file or the file that is configured by the relay_log_info_file system variable.
The MASTER_USE_GTID
option for CHANGE MASTER
can be used to configure the replica to use the global transaction ID (GTID) when connecting to a primary. The possible values are:
current_pos
- Replicate in GTID mode and use gtid_current_pos 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, MASTER_DEMOTE_TO_SLAVE= option instead.
replica_pos
- Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions from the primary.
no
- Don't replicate in GTID mode.
The MASTER_USE_GTID
option for CHANGE MASTER
can be used to configure the replica to use the global transaction ID (GTID) when connecting to a primary. The possible values are:
current_pos
- Replicate in GTID mode and use gtid_current_pos 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, MASTER_DEMOTE_TO_SLAVE= option instead.
slave_pos
- Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions from the primary.
no
- Don't replicate in GTID mode.
The MASTER_USE_GTID
option for CHANGE MASTER
can be used to configure the replica to use the global transaction ID (GTID) when connecting to a primary. The possible values are:
current_pos
- Replicate in GTID mode and use gtid_current_pos 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, MASTER_DEMOTE_TO_SLAVE= option instead.
slave_pos
- Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions from the primary. From MariaDB 10.5.1, replica_pos
is an alias for slave_pos
.
no
- Don't replicate in GTID mode.
For example:
STOP SLAVE;
CHANGE MASTER TO
MASTER_USE_GTID = current_pos;
START SLAVE;
Or:
STOP SLAVE;
SET GLOBAL gtid_slave_pos='0-1-153';
CHANGE MASTER TO
MASTER_USE_GTID = slave_pos;
START SLAVE;
Used to transition a primary to become a replica. Replaces the old MASTER_USE_GTID=current_pos 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:
STOP SLAVE;
CHANGE MASTER TO
MASTER_DEMOTE_TO_SLAVE = 1;
START SLAVE;
MASTER_DEMOTE_TO_SLAVE
is not available.
Also see Replication filters.
The IGNORE_SERVER_IDS
option for CHANGE MASTER
can be used to configure a replica to ignore binary log events that originated from certain servers. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.
The option's value can be specified by providing a comma-separated list of server_id values. For example:
STOP SLAVE;
CHANGE MASTER TO
IGNORE_SERVER_IDS = (3,5);
START SLAVE;
If you would like to clear a previously set list, then you can set the value to an empty list. For example:
STOP SLAVE;
CHANGE MASTER TO
IGNORE_SERVER_IDS = ();
START SLAVE;
The DO_DOMAIN_IDS
option for CHANGE MASTER
can be used to configure a replica to only apply binary log events if the transaction's GTID is in a specific gtid_domain_id value. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.
The option's value can be specified by providing a comma-separated list of gtid_domain_id values. Duplicate values are automatically ignored. For example:
STOP SLAVE;
CHANGE MASTER TO
DO_DOMAIN_IDS = (1,2);
START SLAVE;
If you would like to clear a previously set list, then you can set the value to an empty list. For example:
STOP SLAVE;
CHANGE MASTER TO
DO_DOMAIN_IDS = ();
START SLAVE;
STOP SLAVE;
CHANGE MASTER TO
IGNORE_DOMAIN_IDS = (),
DO_DOMAIN_IDS = (1,2);
START SLAVE;
The IGNORE_DOMAIN_IDS
option for CHANGE MASTER
can be used to configure a replica to ignore binary log events if the transaction's GTID is in a specific gtid_domain_id value. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.
The option's value can be specified by providing a comma-separated list of gtid_domain_id values. Duplicate values are automatically ignored. For example:
STOP SLAVE;
CHANGE MASTER TO
IGNORE_DOMAIN_IDS = (1,2);
START SLAVE;
If you would like to clear a previously set list, then you can set the value to an empty list. For example:
STOP SLAVE;
CHANGE MASTER TO
IGNORE_DOMAIN_IDS = ();
START SLAVE;
STOP SLAVE;
CHANGE MASTER TO
DO_DOMAIN_IDS = (),
IGNORE_DOMAIN_IDS = (1,2);
START SLAVE;
The MASTER_DELAY
option for CHANGE MASTER
can be used to enable delayed replication. 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.
STOP SLAVE;
CHANGE MASTER TO
MASTER_DELAY=3600;
START SLAVE;
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 MASTER_PASSWORD option by executing the following commands:
STOP SLAVE;
CHANGE MASTER TO
MASTER_PASSWORD='new3cret';
START SLAVE;
There are some cases where options are implicitly reset, such as when the MASTER_HOST and MASTER_PORT options are changed.
The values of the MASTER_LOG_FILE and MASTER_LOG_POS options (i.e. the binary log 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 master_info_file option. The replica's I/O thread keeps this binary log position updated as it downloads events only when MASTER_USE_GTID optionis set to NO
. Otherwise the file is not updated on a per event basis.
The master_info_file option can be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:
[mariadb]
...
master_info_file=/mariadb/myserver1-master.info
The values of the RELAY_LOG_FILE and RELAY_LOG_POS options (i.e. the relay log position) are written to either the default relay-log.info
file or the file that is configured by the relay_log_info_file system variable. The replica's SQL thread keeps this relay log position updated as it applies events.
The relay_log_info_file system variable can be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:
[mariadb]
...
relay_log_info_file=/mariadb/myserver1-relay-log.info
If the replica is replicating binary log events that contain GTIDs, then the replica's SQL thread will write every GTID that it applies to the mysql.gtid_slave_pos table. This GTID can be inspected and modified through the gtid_slave_pos system variable.
If the replica has the log_slave_updates system variable enabled and if the replica has the binary log enabled, then every write by the replica's SQL thread will also go into the replica's binary log. This means that GTIDs of replicated transactions would be reflected in the value of the gtid_binlog_pos system variable.
The CHANGE MASTER
statement is useful for setting up a replica when you have a backup of the primary and you also have the binary log position or GTID position corresponding to the backup.
After restoring the backup on the replica, you could execute something like this to use the binary log position:
CHANGE MASTER TO
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4;
START SLAVE;
Or you could execute something like this to use the GTID position:
SET GLOBAL gtid_slave_pos='0-1-153';
CHANGE MASTER TO
MASTER_USE_GTID=slave_pos;
START SLAVE;
See Setting up a Replication Slave with mariadb-backup for more information on how to do this with mariadb-backup.
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:
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
START SLAVE;
RESET SLAVE. Removes a connection created with CHANGE MASTER TO
.
This page is licensed: GPLv2, originally from fill_help_tables.sql
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.
The PURGE BINARY LOGS statement is intended for use in active replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
RESET { SLAVE | REPLICA } ["connection_name"] [ALL] [FOR CHANNEL "connection_name"].
RESET REPLICA makes the replica forget its replication position in the master's binary log. This statement is meant to be used for a clean start. It deletes the master.info and relay-log.info files, all therelay log files, and starts a new relay log file. To use RESET REPLICA, the replica threads must be stopped (use STOP REPLICA if necessary).
Note: All relay log files are deleted, even if they have not been completely executed by the replica SQL thread. (This is a condition likely to exist on a replication replica if you have issued a STOP REPLICA statement or if the replica is highly loaded.)
Note: RESET REPLICA
does not reset the globalgtid_slave_pos
variable. This means that a replica server configured with CHANGE MASTER TO MASTER_USE_GTID=slave_pos
will not receive events with GTIDs occurring before the state saved ingtid_slave_pos
. If the intent is to reprocess these events,gtid_slave_pos
must be manually reset, e.g. by executingset global gtid_slave_pos=""
.
Connection information stored in the master.info file is immediately reset using any values specified in the corresponding startup options. This information includes values such as master host, master port, master user, and master password. If the replica SQL thread was in the middle of replicating temporary tables when it was stopped, and RESET REPLICA is issued, these replicated temporary tables are deleted on the replica.
The ALL
also resets the PORT
, HOST
, USER
and PASSWORD
parameters for the replica. If you are using a connection name, it will permanently delete it and it will not show up anymore in SHOW ALL REPLICAS STATUS.
The connection_name
option is used for multi-source replication.
If there is only one nameless primary, or the default primary (as specified by the default_master_connection system variable) is intended, connection_name
can be omitted. If provided, the RESET REPLICA
statement will apply to the specified primary. connection_name
is case-insensitive.
MariaDB starting with 10.7.0
The FOR CHANNEL
keyword was added for MySQL compatibility. This is identical as using the channel_name directly after RESET REPLICA
.
FOR CHANNEL
is not available.
The FOR CHANNEL
keyword was added for MySQL compatibility. This is identical as using the channel_name directly after RESET REPLICA
.
MariaDB starting with 11.6.0
RESET REPLICA
resets the Master
/Slave_last_event_time
and Connects_Tried
values (see SHOW REPLICA STATUS).
RESET REPLICA
resets the Master
/Slave_last_event_time
values (see SHOW REPLICA STATUS).
RESET REPLICA
does not reset the Master
/Slave_last_event_time
values (see SHOW REPLICA STATUS).
STOP REPLICA stops the replica, but it can be restarted with START REPLICA or after next MariaDB server restart.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SET GLOBAL sql_slave_skip_counter = N
This statement skips the next N
events from the primary. This is useful for recovering from replication stops caused by a statement.
If multi-source replication is used, this statement applies to the default connection. It could be necessary to change the value of the default_master_connection system variable.
Note that, if the event is a transaction, the whole transaction will be skipped. With non-transactional engines, an event is always a single statement.
This statement is valid only when the replica threads are not running. Otherwise, it produces an error.
The statement does not automatically restart the replica threads.
SHOW SLAVE STATUS \G
...
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
Multi-source replication:
SET @@default_master_connection = 'master_01';
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
sql_slave_skip_counter
can't be used to skip transactions on a replica if GTID replication is in use and if gtid_slave_pos contains multiple gtid_domain_id values. In that case, you'll get an error like the following:
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.
In order to skip transactions in cases like this, you will have to manually change gtid_slave_pos.
This page is licensed: GPLv2, originally from fill_help_tables.sql
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
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 replication) needed to connect with a master setup with CHANGE MASTER TO . The I/O thread reads events from the primary server and stores them in the relay log. The SQL thread reads events from the relay log and executes them.
START REPLICA
requires the REPLICATION SLAVE ADMIN privilege.
START REPLICA
requires the SUPER 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 binary log, or some other problem). START REPLICA
does not warn you about this. You must check the replica's error log for error messages generated by the replica threads, or check that they are running satisfactorily with SHOW REPLICA STATUS (SHOW REPLICA STATUS).
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 Global Transaction ID/START REPLICA UNTIL master_gtid_pos=xxx for more details.
MariaDB starting with 11.3.0
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:
START REPLICA UNTIL (SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS)="<gtid_list>"
See Global Transaction ID#SQL_BEFORE_GTIDS/SQL_AFTER_GTIDS for details.
SQL_BEFORE_GTIDS
and SQL_AFTER_GTIDS
are not available.
If there is only one nameless primary, or the default primary (as specified by the default_master_connection 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 as using the channel_name directly after START REPLICA
.
The FOR CHANNEL
keyword is not available.
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 WARNINGS.
CHANGE MASTER TO is used to create and change connections.
STOP REPLICA is used to stop a running connection.
RESET REPLICA is used to reset parameters for a connection and also to permanently delete a primary connection.
This page is licensed: GPLv2, originally from fill_help_tables.sql
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
Stops the replica threads. STOP REPLICA
requires the SUPER privilege, or, from MariaDB 10.5.2, the REPLICATION SLAVE ADMIN privilege.
Like START REPLICA, this statement may be used with the IO_THREAD
andSQL_THREAD
options to name the thread or threads to be stopped. In almost all cases, one never need to use the thread_type
options.
STOP REPLICA
waits until any current replication event group affecting one or more non-transactional tables has finished executing (if there is any such replication group), or until the user issues a KILL QUERY or KILL CONNECTION statement.
Note that STOP REPLICA
doesn't delete the connection permanently. Next time you execute START REPLICA or the MariaDB server restarts, the replica connection is restored with it's original arguments. If you want to delete a connection, you should execute RESET REPLICA.
STOP ALL REPLICAS
stops all your running replicas. It will give you a note
for every stopped connection. You can check the notes with SHOW WARNINGS.
The connection_name
option is used for multi-source replication.
If there is only one nameless master, or the default master (as specified by the default_master_connection system variable) is intended, connection_name
can be omitted. If provided, the STOP REPLICA
statement will apply to the specified master. connection_name
is case-insensitive.
MariaDB starting with 10.7.0
The FOR CHANNEL
keyword is available for MySQL compatibility. This is identical as using the channel_name directly after STOP REPLICA
.
The FOR CHANNEL
keyword is not available.
CHANGE MASTER TO is used to create and change connections.
START REPLICA is used to start a predefined connection.
RESET REPLICA is used to reset parameters for a connection and also to permanently delete a master connection.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Category for old replication statements
Old name for RESET REPLICA
This page is licensed: CC BY-SA / Gnu FDL
Old name for SHOW REPLICA HOSTS
This page is licensed: CC BY-SA / Gnu FDL
Old name for SHOW REPLICA STATUS
This page is licensed: CC BY-SA / Gnu FDL
Old name for START REPLICA
This page is licensed: CC BY-SA / Gnu FDL
Old name for STOP REPLICA
This page is licensed: CC BY-SA / Gnu FDL
Learn about SET statements in MariaDB Server. This section details how to configure system variables at global, session, and local levels, enabling fine-tuning of database behavior and performance.
SET variable_assignment [, variable_assignment] ...
variable_assignment:
user_var_name = expr
| [GLOBAL | SESSION] system_var_name = expr
| [@@global. | @@session. | @@]system_var_name = expr
One can also set a user variable in any expression with this syntax:
user_var_name:= expr
The SET
statement assigns values to different types of variables that affect the operation of the server or your client.
The SET
statement assigns values to different types of variables that affect the operation of the server or your client. Older versions of MySQL employed SET OPTION
, but this syntax was deprecated in favor of SET
without OPTION
.
Changing a system variable by using the SET statement does not make the change permanently. To do so, the change must be made in a configuration file.
For setting variables on a per-query basis, see SET STATEMENT.
See SHOW VARIABLES for documentation on viewing server system variables.
See Server System Variables for a list of all the system variables.
When setting a system variable, the scope can be specified as either GLOBAL or SESSION.
A global variable change affects all new sessions. It does not affect any currently open sessions, including the one that made the change.
A session variable change affects the current session only.
If the variable has a session value, not specifying either GLOBAL or SESSION will be the same as specifying SESSION. If the variable only has a global value, not specifying GLOBAL or SESSION will apply to the change to the global value.
Setting a global variable to DEFAULT will restore it to the server default, and setting a session variable to DEFAULT will restore it to the current global value.
innodb_sync_spin_loops is a global variable.
skip_parallel_replication is a session variable.
max_error_count is both global and session.
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 |
+---------------------------+---------------+--------------+
Setting the session values:
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 |
+---------------------------+---------------+--------------+
Setting the global values:
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 will by default return the session value unless the variable is global only.
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 |
+------------------------+-------+
Using the inplace syntax:
SELECT (@a:=1);
+---------+
| (@a:=1) |
+---------+
| 1 |
+---------+
SELECT @a;
+------+
| @a |
+------+
| 1 |
+------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SET [SESSION] sql_log_bin = {0|1}
Sets the sql_log_bin system variable, which disables or enables binary logging for the current connection, if the client has the SUPER
privilege. The statement is refused with an error if the client does not have that privilege.
Note that setting sql_log_bin=1
has no effect if log_bin variable, which enables global binary logging, is not set.
You cannot set sql_log_bin
as a global variable.
You can set sql_log_bin
as a global variable. This is considered dangerous, though, as it can damage replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SET STATEMENT can be used to set the value of a system variable for the duration of the statement. It is also possible to set multiple variables.
SET STATEMENT var1=value1 [, var2=value2, ...]
FOR <statement>
var
is a system variable (list of allowed variables is provided below), and value
is a constant literal.
SET STATEMENT var1=value1 FOR stmt
is roughly equivalent to
SET @save_value=@@var1;
SET SESSION var1=value1;
stmt;
SET SESSION var1=@save_value;
The server parses the whole statement before executing it, so any variables set in this fashion that affect the parser may not have the expected effect. Examples include the charset variables, sql_mode=ansi_quotes, etc.
One can limit statement execution time max_statement_time:
SET STATEMENT max_statement_time=1000 FOR SELECT ... ;
One can switch on/off individual optimizations:
SET STATEMENT optimizer_switch='materialization=off' FOR SELECT ....;
It is possible to enable MRR/BKA for a query:
SET STATEMENT join_cache_level=6, optimizer_switch='mrr=on' FOR SELECT ...
Note that it makes no sense to try to set a session variable inside a SET STATEMENT
:
#USELESS STATEMENT
SET STATEMENT sort_buffer_size = 100000 FOR SET SESSION sort_buffer_size = 200000;
For the above, after setting sort_buffer_size to 200000 it will be reset to its original state (the state before the SET STATEMENT
started) after the statement execution.
There are a number of variables that cannot be set on per-query basis. These include:
autocommit
character_set_client
character_set_connection
character_set_filesystem
collation_connection
default_master_connection
debug_sync
interactive_timeout
gtid_domain_id
last_insert_id
log_slow_filter
log_slow_rate_limit
log_slow_verbosity
long_query_time
min_examined_row_limit
profiling
profiling_history_size
query_cache_type
rand_seed1
rand_seed2
skip_replication
slow_query_log
sql_log_off
tx_isolation
wait_timeout
The feature was originally implemented as a Google Summer of Code 2009 project by Joseph Lukas.
Percona Server 5.6 included it as Per-query variable statement
MariaDB ported the patch and fixed many bugs. The task in MariaDB Jira is MDEV-5231.
This page is licensed: CC BY-SA / Gnu FDL
Learn about SHOW statements in MariaDB Server. These SQL commands provide insights into server status, database objects, configurations, and various internal states for monitoring and troubleshooting.
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 fill_help_tables.sql
The following SHOW statements can be extended using a WHERE
clause and a LIKE
clause to refine the results:
As with a regular SELECT, the WHERE
clause can be used for the specific columns returned, and the LIKE clause with the regular wildcards.
This statement shows all tables:
SHOW TABLES;
+----------------------+
| Tables_in_test |
+----------------------+
| animal_count |
| animals |
| are_the_mooses_loose |
| aria_test2 |
| t1 |
| view1 |
+----------------------+
This statement only shows tables starting with the letter 'a':
SHOW TABLES WHERE Tables_in_test LIKE 'a%';
+----------------------+
| Tables_in_test |
+----------------------+
| animal_count |
| animals |
| are_the_mooses_loose |
| aria_test2 |
+----------------------+
This statement shows variables whose names start with aria and have a value greater than 8192:
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 |
+------------------------------+---------------------+
This page is licensed: CC BY-SA / Gnu FDL
SHOW ANALYZE
was added in MariaDB 10.9.
SHOW ANALYZE [FORMAT=JSON] FOR <connection_id>;
SHOW ANALYZE
allows one to retrieve ANALYZE-like output from a currently running statement. The statement
SHOW ANALYZE [FORMAT=JSON] FOR <connection_id>;
connects to the query running in connection connection_id
, gets information about the query plan it is executing, also gets information about the runtime statistics of the execution so far and returns it in a format similar to ANALYZE [FORMAT=JSON]
output.
This is similar to the SHOW EXPLAIN command, the difference being that SHOW ANALYZE
also produces runtime statistics information.
You're trying to troubleshoot a query that never finishes. Since it doesn't finish, it is not possible to get ANALYZE output for it. With SHOW ANALYZE
, you can get the runtime statistics without waiting for the query to finish.
Consider the tables orders
and customer
and a join query finding the total amount of orders from customers with Gold status:
EXPLAIN format=json
SELECT sum(orders.amount)
FROM
customer JOIN orders ON customer.cust_id=orders.cust_id
WHERE
customer.status='GOLD';
The output of this query looks like this:
+------+-------------+----------+------+---------------+---------+---------+------------------+--------+-------------+
| 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 | |
+------+-------------+----------+------+---------------+---------+---------+------------------+--------+-------------+
We run the SELECT, and it has been running for 30 seconds. Let's try SHOW ANALYZE
:
SHOW ANALYZE format=json FOR 3;
| {
"r_query_time_in_progress_ms": 32138,
The statement shows how long the query has been running.
"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,
rows
shows the number of rows expected. r_rows
in shows how many rows were processed so far (110K out of expected 200K). r_loops
shows we're doing the first table scan (which is obvious for this query plan).
"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,
rows: 1
shows the optimizer was expecting 1 order per customer. But r_rows: 99.9
shows that it has found on average 100 orders per customer. This may be the reason the query is slower than expected.
The final chunk of the output doesn't have anything interesting but here it is:
"filtered": 100,
"r_filtered": 100
}
}
]
}
}
Regular SELECT queries collect row count information, so SHOW ANALYZE
can display it. However, detailed timing information is not collected, as collecting it may have CPU overhead. But if the target query is collecting timing information, SHOW ANALYZE
will display it. How does one get the target query to collect timing information? Currently there is one way: if the target is running ANALYZE
, it IS collecting timing information.
Re-running the previous example:
Connection 1> ANALYZE SELECT ... ;
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,
ANALYZE
prints timing information in members named r_..._time_ms
.
You can see that, so far, out of 30 seconds, only 232 millisecond were spent in reading the customer table. The bottleneck is elsewhere...
"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,
29.4 seconds were spent reading the orders table (and 0.986 seconds in processing the obtained rows). Now we can see where the query is spending time.
"filtered": 100,
"r_filtered": 100
}
}
]
}
}
This page is licensed: CC BY-SA / Gnu FDL
SHOW AUTHORS
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...
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 CONTRIBUTORS. This list all members and sponsors of the MariaDB Foundation and other sponsors.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW BINARY LOGS
SHOW MASTER LOGS
Lists the binary log files on the server. This statement is used as part of the procedure described in PURGE BINARY LOGS, that shows how to determine which logs can be purged.
This statement requires the BINLOG MONITOR privilege.
This statement requires the SUPER privilege and the REPLICATION_CLIENT privilege.
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 |
+--------------------+-----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW BINLOG EVENTS
[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Shows the events in the binary log. If you do not specify log_name
, the first binary log is displayed.
This statement requires the BINLOG MONITOR privilege.
This statement requires the REPLICATION SLAVE privilege.
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) |
+-------------------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW BINLOG STATUS
SHOW MASTER STATUS
Provides status information about the binary log files of the primary.
This statement requires the BINLOG MONITOR privilege.
This statement requires the SUPER privilege and the REPLICATION_CLIENT privilege.
To see information about the current GTIDs in the binary log, use thegtid_binlog_pos variable.
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 |
+--------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CHARACTER SET
[LIKE 'pattern' | WHERE expr]
The SHOW CHARACTER SET
statement shows all available character sets. 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 Extended SHOW.
The same information can be queried from the Information Schema CHARACTER_SETS table.
See Setting Character Sets and Collations for details on specifying the character set at the server, database, table and column levels.
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 |
+---------+---------------------------+-------------------+--------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CLIENT_STATISTICS
The SHOW CLIENT_STATISTICS
statement has effectively been replaced by the generic SHOW TABLE STATISTICS statement. The information_schema.CLIENT_STATISTICS table holds statistics about client connections.
The SHOW CLIENT_STATISTICS
statement is part of the User Statistics feature. The information_schema.CLIENT_STATISTICS table holds statistics about client connections.
The userstat system variable must be set to 1 to activate this feature. See the User Statistics and information_schema.CLIENT_STATISTICS articles for more information.
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
This page is licensed: CC BY-SA / Gnu FDL
SHOW COLLATION
[LIKE 'pattern' | WHERE expr]
The output from SHOW COLLATION
includes all availablecollations. The LIKE
clause, if present on its own, indicates which collation names to match. The WHERE
and LIKE
clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.
Similar information, including some extra information, can be queried from the Information Schema COLLATIONS table.
No similar information or extra information can be queried from the Information Schema COLLATIONS table.
See Setting Character Sets and Collations for details on specifying the collation at the server, database, table and column levels.
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 |
+------------------------------+---------+------+---------+----------+---------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW [FULL] {COLUMNS | FIELDS} FROM tbl_name [FROM db_name]
[LIKE 'pattern' | WHERE expr]
SHOW COLUMNS
displays information about the columns in a given table. It also works for views. The LIKE
clause, if present on its own, indicates which column names to match. The WHERE
and LIKE
clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.
If the data types differ from what you expect them to be based on aCREATE TABLE
statement, note that MariaDB sometimes changes data types when you create or alter a table. The conditions under which this occurs are described in the Silent Column Changes article.
The FULL
keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.
You can use db_name.tbl_name
as an alternative to thetbl_name FROM db_name
syntax. In other words, these two statements are equivalent:
SHOW COLUMNS FROM mytable FROM mydb;
SHOW COLUMNS FROM mydb.mytable;
SHOW COLUMNS
displays the following values for each table column:
Field indicates the column name.
Type indicates the column data type.
Collation indicates the collation for non-binary string columns, or NULL for other columns. This value is displayed only if you use the FULL keyword.
The Null field contains YES if NULL values can be stored in the column, NO if not.
The Key field indicates whether the column is indexed:
If Key is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, non-unique index.
If Key is PRI, the column is a PRIMARY KEY
or is one of the columns in a multiple-column PRIMARY KEY
.
If Key is UNI, the column is the first column of a unique-valued index that cannot contain NULL
values.
If Key is MUL, multiple occurrences of a given value are allowed within the column. The column is the first column of a non-unique index or a unique-valued index that can contain NULL
values.
If more than one of the Key values applies to a given column of a table, Key displays the one with the highest priority, in the order PRI, UNI, MUL.
A UNIQUE
index may be displayed as PRI
if it cannot contain NULL
values and there is noPRIMARY KEY
in the table. A UNIQUE
index may display as MUL
if several columns form a compositeUNIQUE
index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.
The Default field indicates the default value that is assigned to the column.
The Extra field contains any additional information that is available about a given column.
AUTO_INCREMENT
The column was created with the AUTO_INCREMENT keyword.
PERSISTENT
The column was created with the PERSISTENT keyword.
VIRTUAL
The column was created with the VIRTUAL keyword.
on update CURRENT_TIMESTAMP
The column is a TIMESTAMP column that is automatically updated on INSERT and UPDATE.
Privileges indicates the privileges you have for the column. This value is displayed only if you use the FULL
keyword.
Comment indicates any comment the column has. This value is displayed only if you use the FULL
keyword.
SHOW FIELDS
is a synonym forSHOW COLUMNS
. Also DESCRIBE and EXPLAIN can be used as shortcuts.
You can also list a table's columns with:
mariadb-show db_name tbl_name
See the mariadb-show command for more details.
The DESCRIBE statement provides information similar to SHOW COLUMNS
. The information_schema.COLUMNS table provides similar, but more complete, information.
The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements also provide information about tables.
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 | |
+---------------+-------------+------+-----+---------+-------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CONTRIBUTORS
The SHOW CONTRIBUTORS
statement displays information about the companies and people who financially contribute to MariaDB. For each contributor, it displays Name
, Location
, and Comment
values. All columns are encoded as latin1
.
It displays all members and sponsors of the MariaDB Foundation as well as other financial contributors.
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 AUTHORS list the authors of MariaDB (including documentation, QA etc).
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CREATE {DATABASE | SCHEMA} db_name
Shows the CREATE DATABASE statement that creates the given database. SHOW CREATE SCHEMA
is a synonym for SHOW CREATE DATABASE
. SHOW CREATE DATABASE
quotes database names according to the value of the sql_quote_show_create server system variable.
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 */ |
+----------+-----------------------------------------------------------------+
With sql_quote_show_create turned off, the output looks slightly differently:
SHOW CREATE DATABASE test;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test | CREATE DATABASE test /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
In the DEFAULT
clause of a SHOW CREATE
statement, numbers are quoted.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CREATE EVENT event_name
This statement displays the CREATE EVENT statement that creates a given event, as well as the SQL_MODE that was used when the trigger was created, and the character set used by the connection. To find out which events are present, use SHOW EVENTS.
SHOW CREATE EVENT
quotes identifiers according to the value of the sql_quote_show_create system variable.
SHOW CREATE EVENT
quotes identifiers according to the value of the sql_quote_show_create system variable. Note, however, that the output of this statement is unreliably affected by the sql_quote_show_create system variable.
The information_schema.EVENTS table provides similar, but more complete, information.
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
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CREATE FUNCTION func_name
This statement is similar to SHOW CREATE PROCEDURE but for stored functions.
SHOW CREATE FUNCTION
quotes identifiers, according to the value of the sql_quote_show_create system variable.
SHOW CREATE FUNCTION
quotes identifiers, according to the value of the sql_quote_show_create system variable. However, the output of this statement is unreliably affected by the sql_quote_show_create system variable.
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
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CREATE PACKAGE BODY [ db_name . ] package_name
The SHOW CREATE PACKAGE BODY
statement shows the CREATE PACKAGE BODY
statement that creates the given package body (that is, the implementation of the package).
The SHOW CREATE PACKAGE BODY
statement shows the CREATE PACKAGE BODY
statement that creates the given package body (that is, the implementation of the package). CREATE PACKAGE BODY
can be used when Oracle SQL_MODE is set.
SHOW CREATE PACKAGE BODY
quotes identifiers according to the value of the sql_quote_show_create system variable.
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
This page is licensed: CC BY-SA / Gnu FDL
SHOW CREATE PACKAGE [ db_name . ] package_name
The SHOW CREATE PACKAGE
statement can be used when Oracle SQL_MODE is set. It shows the CREATE
statement that creates the given package specification.
SHOW CREATE PACKAGE
quotes identifiers according to the value of the sql_quote_show_create system variable.
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
This page is licensed: CC BY-SA / Gnu FDL
SHOW CREATE PROCEDURE proc_name
This statement is a MariaDB extension. It returns the exact string that can be used to re-create the named stored procedure, as well as the SQL_MODE that was used when the trigger has been created and the character set used by the connection.. A similar statement, SHOW CREATE FUNCTION, displays information about stored functions.
Both statements require that:
you are the owner of the routine;
you have the SHOW CREATE ROUTINE privilege (from MariaDB 11.3.0); or
you have the SELECT privilege on the mysql.proc table.
When none of the above statements are true, the statements display NULL
for the Create Procedure
or Create Function
field.
Users with SELECT
privileges on mysql.proc or USAGE
privileges on *.*
can view the text of routines, even when they do not have privileges for the function or procedure itself.
SHOW CREATE PROCEDURE
quotes identifiers, according to the value of the sql_quote_show_create system variable.
SHOW CREATE PROCEDURE
quotes identifiers, according to the value of the sql_quote_show_create system variable. The output of this statement is unreliably affected by the sql_quote_show_create system variable.
Here's a comparison of the SHOW CREATE PROCEDURE
and SHOW CREATE FUNCTION statements.
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
When the user issuing the statement does not have privileges on the routine, attempting to CALL the procedure raises Error 1370.
CALL test.prc1();
Error 1370 (42000): execute command denied to
user 'test_user'@'localhost' for routine 'test'.'prc1'
If the user neither has privilege to the routine nor the SELECT privilege on mysql.proc table, it raises Error 1305, informing them that the procedure does not exist.
SHOW CREATE TABLES test.prc1\G
Error 1305 (42000): PROCEDURE prc1 does not exist
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CREATE SEQUENCE sequence_name;
Shows the CREATE SEQUENCE statement that creates the given sequence. The statement requires the SELECT
privilege for the table.
SHOW CREATE SEQUENCE
quotes identifiers according to the value of the sql_quote_show_create system variable.
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
If you want to see the underlying table structure used for the SEQUENCE
you can use SHOW CREATE TABLE on the SEQUENCE
. You can also use SELECT
to read the current recorded state of the SEQUENCE
:
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
The Information Schema SEQUENCES Table also provides information about available sequences:
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
This page is licensed: CC BY-SA / Gnu FDL
SHOW CREATE SERVER server_name
Shows the CREATE SERVER statement that created the given server definition.
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');
The SHOW CREATE SERVER
statement is not available.
This page is licensed: CC BY-SA / Gnu FDL
SHOW CREATE TABLE tbl_name
Shows the CREATE TABLE statement that creates the given table. The statement requires the SELECT privilege for the table. This statement also works with views and SEQUENCE.
SHOW CREATE TABLE
quotes table and column names according to the value of the sql_quote_show_create server system variable.
Certain SQL_MODE 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 NO_TABLE_OPTIONS, NO_FIELD_OPTIONS and NO_KEY_OPTIONS SQL_MODE flags are used. All MariaDB-specific table attributes are also not shown when a non-MariaDB/MySQL emulation mode is used, which includes ANSI, DB2, POSTGRESQL, MSSQL, MAXDB or ORACLE.
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 IGNORE_BAD_TABLE_OPTIONS SQL_MODE. Remember that replaying a CREATE TABLE statement with uncommented invalid options will fail with an error, unless the IGNORE_BAD_TABLE_OPTIONS SQL_MODE 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 TEXT and BLOB data types to be assigned a DEFAULT 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.
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
LONG UNIQUE keys
Normal keys
Fulltext keys
See sql/sql_table.cc for details.
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
With sql_quote_show_create off:
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
SQL_MODE impacting the output:
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")
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CREATE TRIGGER trigger_name
This statement shows a CREATE TRIGGER statement that creates the given trigger, as well as the SQL_MODE that was used when the trigger has been created and the character set used by the connection.
The TRIGGER privilege is required on the table the trigger is defined for to execute this statement.
SHOW CREATE TRIGGER
quotes identifiers, according to the value of the sql_quote_show_create system variable.
SHOW CREATE TRIGGER
quotes identifiers, according to the value of the sql_quote_show_create system variable. However, the output of this statement is unreliably affected by the sql_quote_show_create system variable.
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
The Created
column serves to better view multiple trigger events.
The Created
column is unavailable.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW CREATE USER [user-name]
Shows the CREATE USER statement that creates the given user. The statement requires the SELECT privilege for the mysql database, except for the current user. The CREATE USER statement for the current user is shown where no user is specified.
SHOW CREATE USER
quotes identifiers according to the value of the sql_quote_show_create system variable.
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 |
+------------------------------------------------------------------+
SHOW GRANTS shows the GRANTS/PRIVILEGES
for a user.
SHOW PRIVILEGES shows the privileges supported by MariaDB.
This page is licensed: CC BY-SA / Gnu FDL
SHOW CREATE VIEW [view-name]
This statement shows a CREATE VIEW statement that creates the given view, as well as the character set used by the connection when the view was created. This statement also works with views.
SHOW CREATE VIEW
quotes table, column and stored function names according to the value of the sql_quote_show_create server system variable.
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
With sql_quote_show_create off:
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
To be able to see a view, you need to have the SHOW VIEW and the SELECT privilege on the view:
GRANT SHOW VIEW,SELECT ON test_database.test_view TO 'test'@'localhost';
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]
SHOW DATABASES
lists the databases on the MariaDB server host.SHOW SCHEMAS
is a synonym forSHOW DATABASES
. The LIKE
clause, if present on its own, indicates which database names to match. The WHERE
and LIKE
clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.
You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege. You can also get this list using the mariadb-show command.
If the server was started with the --skip-show-database
option, you cannot use this statement at all unless you have the SHOW DATABASES privilege.
The list of results returned by SHOW DATABASES
is based on directories in the data directory, which is how MariaDB implements databases. It's possible that output includes directories that do not correspond to actual databases.
The Information Schema SCHEMATA table also contains database information.
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
SHOW DATABASES LIKE 'm%';
+---------------+
| Database (m%) |
+---------------+
| mysql |
+---------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB STATUS
is a specific form of the SHOW ENGINE statement that displays the InnoDB Monitor output, which is extensive InnoDB information which can be useful in diagnosing problems.
The following sections are displayed
Status: Shows the timestamp, monitor name and the number of seconds, or the elapsed time between the current time and the time the InnoDB Monitor output was last displayed. The per-second averages are based upon this time.
BACKGROUND THREAD: srv_master_thread lines show work performed by the main background thread.
SEMAPHORES: Threads waiting for a semaphore and stats on how the number of times threads have needed a spin or a wait on a mutex or rw-lock semaphore. If this number of threads is large, there may be I/O or contention issues. Reducing the size of the innodb_thread_concurrency system variable may help if contention is related to thread scheduling. Spin rounds per wait
shows the number of spinlock rounds per OS wait for a mutex.
LATEST FOREIGN KEY ERROR: Only shown if there has been a foreign key constraint error, it displays the failed statement and information about the constraint and the related tables.
LATEST DETECTED DEADLOCK: Only shown if there has been a deadlock, it displays the transactions involved in the deadlock and the statements being executed, held and required locked and the transaction rolled back to.
TRANSACTIONS: The output of this section can help identify lock contention, as well as reasons for the deadlocks.
FILE I/O: InnoDB thread information as well as pending I/O operations and I/O performance statistics.
INSERT BUFFER AND ADAPTIVE HASH INDEX: InnoDB insert buffer (old name for the change buffer) and adaptive hash index status information, including the number of each type of operation performed, and adaptive hash index performance.
LOG: InnoDB log information, including current log sequence number, how far the log has been flushed to disk, the position at which InnoDB last took a checkpoint, pending writes and write performance statistics.
BUFFER POOL AND MEMORY: Information on buffer pool pages read and written, which allows you to see the number of data file I/O operations performed by your queries. See InnoDB Buffer Pool for more. Similar information is also available from the INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS table.
ROW OPERATIONS:Information about the main thread, including the number and performance rate for each type of row operation.
If the innodb_status_output_locks system variable is set to 1
, extended lock information will be displayed.
Example output:
=====================================
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
============================
This page is licensed: CC BY-SA / Gnu FDL
SHOW ENGINE [engine-name] {STATUS | MUTEX}
SHOW ENGINE
displays operational information about a storage engine. The following statements currently are supported:
SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB MUTEX
SHOW ENGINE PERFORMANCE_SCHEMA STATUS
SHOW ENGINE ROCKSDB STATUS
If the Sphinx Storage Engine is installed, the following is also supported:
SHOW ENGINE SPHINX STATUS
SHOW ENGINE INNODB STATUS
displays extensive information from the standard InnoDB Monitor about the state of the InnoDB storage engine. See SHOW ENGINE INNODB STATUS for more.
SHOW ENGINE INNODB MUTEX
displays InnoDB mutex statistics.
The statement displays the following output fields:
Type: Always InnoDB.
Name: The source file where the mutex is implemented, and the line number in the file where the mutex is created. The line number is dependent on the MariaDB version.
Status: This field displays the following values if UNIV_DEBUG
was defined at compilation time (for example, in include/univ.h in the InnoDB part of the source tree). Only the os_waits
value is displayed if UNIV_DEBUG
was not defined. Without UNIV_DEBUG
, the information on which the output is based is insufficient to distinguish regular mutexes and mutexes that protect rw-locks (which allow multiple readers or a single writer). Consequently, the output may appear to contain multiple rows for the same mutex.
count indicates how many times the mutex was requested.
spin_waits indicates how many times the spinlock had to run.
spin_rounds indicates the number of spinlock rounds. (spin_rounds divided by spin_waits provides the average round count.)
os_waits indicates the number of operating system waits. This occurs when the spinlock did not work (the mutex was not locked during the spinlock and it was necessary to yield to the operating system and wait).
os_yields indicates the number of times a the thread trying to lock a mutex gave up its timeslice and yielded to the operating system (on the presumption that allowing other threads to run will free the mutex so that it can be locked).
os_wait_times indicates the amount of time (in ms) spent in operating system waits, if the timed_mutexes system variable is 1 (ON). If timed_mutexes is 0 (OFF), timing is disabled, so os_wait_times is 0. timed_mutexes is off by default.
Information from this statement can be used to diagnose system problems. For example, large values of spin_waits and spin_rounds may indicate scalability problems.
The information_schema.INNODB_MUTEXES table provides similar information.
This statement shows how much memory is used for performance_schema tables and internal buffers.
The output contains the following fields:
Type: Always performance_schema
.
Name: The name of a table, the name of an internal buffer, or the performance_schema
word, followed by a dot and an attribute. Internal buffers names are enclosed by parenthesis. performance_schema
means that the attribute refers to the whole database (it is a total).
Status: The value for the attribute.
The following attributes are shown, in this order, for all tables:
row_size: The memory used for an individual record. This value will never change.
row_count: The number of rows in the table or buffer. For some tables, this value depends on a server system variable.
memory: For tables and performance_schema
, this is the result of row_size
* row_count
.
For internal buffers, the attributes are:
count
size
See also MyRocks Performance Troubleshooting
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW [STORAGE] ENGINES
SHOW ENGINES
displays status information about the server's storage engines. This is particularly useful for checking whether a storage engine is supported, or to see what the default engine is.SHOW TABLE TYPES
is a deprecated synonym.
The information_schema.ENGINES table provides the same information.
Since storage engines are plugins, different information about them is also shown in the information_schema.PLUGINS table and by the SHOW PLUGINS statement.
Note that both MySQL's InnoDB and Percona's XtraDB replacement are labeled as InnoDB
. However, if XtraDB is in use, it will be specified in the COMMENT
field. See XtraDB and InnoDB. The same applies to FederatedX.
The output consists of the following columns:
Engine
indicates the engine's name.
Support
indicates whether the engine is installed, and whether it is the default engine for the current session.
Comment
is a brief description.
Transactions
, XA
and Savepoints
indicate whether transactions, XA transactions and transaction savepoints are supported by the engine.
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)
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW ERRORS [LIMIT row_count OFFSET offset]
SHOW COUNT(*) ERRORS
This statement is similar to SHOW WARNINGS, except that instead of displaying errors, warnings, and notes, it displays only errors.
The LIMIT
clause has the same syntax as for the SELECT statement.
The SHOW COUNT(*) ERRORS
statement displays the number of errors. You can also retrieve this number from the error_count variable.
SHOW COUNT(*) ERRORS;
SELECT @@error_count;
The value of error_count might be greater than the number of messages displayed by SHOW WARNINGS if the max_error_count system variable is set so low that not all messages are stored.
For a list of MariaDB error codes, see MariaDB Error Codes.
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 |
+-------+------+---------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW EVENTS [{FROM | IN} schema_name]
[LIKE 'pattern' | WHERE expr]
Shows information about Event Manager events (created with CREATE EVENT). Requires the EVENT privilege. Without any arguments, SHOW EVENTS
lists all of the events in the current schema:
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
To see the event action, use SHOW CREATE EVENT instead, or look at the information_schema.EVENTS table.
To see events for a specific schema, use the FROM
clause. For example, to see events for the test schema, use the following statement:
SHOW EVENTS FROM test;
The LIKE
clause, if present, indicates which event names to match. The WHERE
clause can be given to select rows using more general conditions, as discussed in Extended Show.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW EXPLAIN [FORMAT=JSON] FOR <connection_id>;
EXPLAIN [FORMAT=JSON] FOR CONNECTION <connection_id>;
The SHOW EXPLAIN
command allows one to get an EXPLAIN (that is, a description of a query plan) of a query running in a certain connection.
SHOW EXPLAIN FOR <connection_id>;
will produce an EXPLAIN
output for the query that connection number connection_id
is running. The connection id can be obtained with SHOW PROCESSLIST.
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)
The output is always accompanied with a warning which shows the query the target connection is running (this shows what the EXPLAIN
is for):
SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message |
+-------+------+------------------------+
| Note | 1003 | select sum(a) from tbl |
+-------+------+------------------------+
1 row in set (0.00 sec)
EXPLAIN FOR CONNECTION
and FORMAT=JSON
are not available.
The output can be only produced if the target connection is currently running a query, which has a ready query plan. If this is not the case, the output will be:
SHOW EXPLAIN FOR 2;
ERROR 1932 (HY000): Target is not running an EXPLAINable command
You will get this error when:
The target connection is not running a command for which one can run EXPLAIN
;
The target connection is running a command for which one can run EXPLAIN
, but there is no query plan yet (for example, tables are open and locks are acquired before the query plan is produced).
In MySQL, EXPLAIN
execution takes a slightly different route from the way the real query (typically the SELECT
) is optimized. This is unfortunate, and has caused a number of bugs in EXPLAIN
. (For example, see MDEV-326, MDEV-410, and lp:1013343.lp:992942 is not directly about EXPLAIN
, but it also would not have existed if MySQL didn't try to delete parts of a query plan in the middle of the query)
SHOW EXPLAIN
examines a running SELECT
, and hence its output may be slightly different from what EXPLAIN SELECT
would produce. We did our best to make sure that either the difference is negligible, or SHOW EXPLAIN
's output is closer to reality than EXPLAIN
's output.
SHOW EXPLAIN
may have Extra='no matching row in const table
', where EXPLAIN
would produce Extra='Impossible WHERE ...
'
For queries with subqueries, SHOW EXPLAIN
may print select_type==PRIMARY
where regular EXPLAIN
used to print select_type==SIMPLE
, or vice versa.
Running SHOW EXPLAIN
requires the same permissions as running SHOW PROCESSLIST would.
EXPLAIN ANALYZE, which will perform a query and outputs enhanced EXPLAIN
results.
It is also possible to save EXPLAIN into the slow query log.
This page is licensed: CC BY-SA / Gnu FDL
SHOW FUNCTION CODE func_name
SHOW FUNCTION CODE
shows a representation of the internal implementation of the stored function.
It is similar to SHOW PROCEDURE CODE but for stored functions.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE expr]
This statement is similar to SHOW PROCEDURE STATUS but for stored functions.
The LIKE clause, if present on its own, indicates which function names to match.
The WHERE
and LIKE
clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.
The information_schema.ROUTINES table contains more detailed information.
Showing all stored functions:
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
Stored functions whose name starts with 'V':
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
Stored functions with a security type of 'DEFINER':
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
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW GRANTS [FOR user|role]
The SHOW GRANTS
statement lists privileges granted to a particular user or role.
The statement lists the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MariaDB user account. The account is named using the same format as for theGRANT
statement; for example, 'jeffrey'@'localhost
'. If you specify only the user name part of the account name, a host name part of '%
' is used. For additional information about specifying account names, see GRANT.
SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
If SHOW GRANTS FOR CURRENT_USER
(or any of the equivalent syntaxes) is used in DEFINER
context (such as within a stored procedure that is defined withSQL SECURITY DEFINER
), the grants displayed are those of the definer and not the invoker.
SHOW GRANTS
can also be used to view the privileges granted to a role.
SHOW GRANTS FOR journalist;
+------------------------------------------+
| Grants for journalist |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'journalist' |
| GRANT DELETE ON `test`.* TO 'journalist' |
+------------------------------------------+
GRANT ... TO PUBLIC grants privileges to all users. SHOW GRANTS FOR PUBLIC
shows all these grants.
SHOW GRANTS FOR public;
+------------------------------------------------+
| Grants for PUBLIC |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON `dev_db`.* TO `PUBLIC` |
+------------------------------------------------+
FOR PUBLIC
is not available.
SHOW CREATE USER shows how the user was created.
SHOW PRIVILEGES shows the privileges supported by MariaDB.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHOW INDEX_STATISTICS
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 SHOW TABLE STATISTICS statement.
The SHOW INDEX_STATISTICS
statement was introduced in MariaDB 5.2 as part of the User Statistics feature. It was removed as a separate statement in MariaDB 10.1.1, but effectively replaced by the generic SHOW TABLE STATISTICS statement.
The userstat system variable must be set to 1 to activate this feature. See the User Statistics and information_schema.INDEX_STATISTICS table for more information.
SHOW INDEX_STATISTICS;
+--------------+-------------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+-------------------+------------+-----------+
| test | employees_example | PRIMARY | 1 |
+--------------+-------------------+------------+-----------+
This page is licensed: CC BY-SA / Gnu FDL