Include only local subsections into TOC. This prevent duplication of
TOC when build single page HTML document. Also this make unnecessary CSS hack
to hide document title as top level section.
.. -*- coding: utf-8; -*-
.. include:: HEADER.rst
================
MySQL/MariaBD.
================
.. contents::
:local:
SQL clients.
============
https://mariadb.com/kb/en/mariadb/clients-and-utilities/
Clients and Utilities.
https://mariadb.com/kb/en/mariadb/graphical-and-enhanced-clients/
Graphical and Enhanced Clients.
mysql.
------
Official client. Install ``mariadb-client`` package - ``mysql`` utility
will be installed with dependent package.
Usage::
$ mysql -u $USER -p
Enter password: <== Type password.
To save content of session interaction in ``mysql`` use::
mysql> tee out.txt
mycli.
------
Python command line client with completion. Usage::
$ mycli -u $USER
Password: <== Type password.
List databases, tables, columns, indexes.
=========================================
::
$ mysql -u $USER -p
Enter password: <== Type password.
SHOW DATABASES;
USE mysql;
MariaDB [mysql]> SHOW TABLES;
MariaDB [mysql]> SHOW COLUMNS FROM user;
MariaDB [mysql]> DESCRIBE user;
MariaDB [mysql]> SHOW STATUS;
MariaDB [mysql]> SHOW CREATE DATABASE mysql;
MariaDB [mysql]> SHOW CREATE TABLE user;
MariaDB [mysql]> SHOW GRANTS;
List indexes::
SELECT * FROM INFORMATION_SCHEMA.STATISTICS;
SHOW INDEX FROM tbl;
SHOW INDEX FROM tbl FROM db;
SHOW CREATE TABLE tbl;
Table/index sizes.
==================
::
MariaDB [mysql]> SHOW TABLE STATUS FROM mydb;
::
SELECT
table_schema "Database",
ROUND(SUM(data_length+index_length)/1024/1024, 2) "Size, MiB"
FROM information_schema.TABLES
GROUP BY table_schema;
SELECT
table_schema AS `Database`,
table_name,
table_rows,
round(((data_length + index_length) / 1024 / 1024), 2) `Size, MiB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
SELECT
table_schema as `Database`,
table_name AS `Table`,
round((data_length / 1024 / 1024), 2) `Data, MiB`,
round((index_length / 1024 / 1024), 2) `Index, MiB`,
round(((data_length+index_length) / 1024 / 1024), 2) `Total, MiB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
System information.
===================
Server version, platform, etc::
[mysql]> SHOW VARIABLES LIKE "%version%";
Debugging queries error.
========================
After error or warning run::
MariaDB [mysql]> SHOW WARNINGS;
MariaDB [mysql]> SHOW ERRORS;
MariaDB [mysql]> show engine innodb status;
Debugging quiery performance.
=============================
::
explain select 1;
..
https://mariadb.com/kb/en/mariadb/explain/
Syntax and transcription of EXPLAIN.
Monitor server execution.
=========================
List all current executions::
MariaDB [mysql]> show processlist;
Stop selected process::
MariaDB [mysql]> kill 12;
Dump execution plan for selected process::
MariaDB [mysql]> show explain for 1;
.. NOTE::
You shoud check for code ``1003``::
MariaDB [mysql]> show warnings;
to ensure for which query plan shown becase query may finished and new
started.
See:
* `SHOW EXPLAIN in MariaDB 10.0 vs EXPLAIN FOR CONNECTION in MySQL 5.7
<http://s.petrunia.net/blog/?p=89>`_
Profiling queries.
==================
Enable profiling, execute query and review profiling data::
[mysql]> set profiling=1;
[mysql]> select 1;
[mysql]> show profile;
Each profiled query results stored separately. To get list of results::
[mysql]> show profiles;
To retrieve selected profiling data::
[mysql]> show profile for query 1;