mysql.rst
author Oleksandr Gavenko <gavenkoa@gmail.com>
Sat, 02 Jan 2016 20:10:31 +0200
changeset 1854 9b224b68c1e1
parent 1853 c6be6cc87df4
child 1875 340162fb09a2
permissions -rw-r--r--
Debugging quiery performance.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
1809
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     1
.. -*- coding: utf-8; -*-
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     2
.. include:: HEADER.rst
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     3
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     4
================
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     5
 MySQL/MariaBD.
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     6
================
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     7
.. contents::
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     8
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     9
SQL clients.
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    10
============
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    11
1810
752c192306ff SQL clients.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1809
diff changeset
    12
  https://mariadb.com/kb/en/mariadb/clients-and-utilities/
752c192306ff SQL clients.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1809
diff changeset
    13
     Clients and Utilities.
752c192306ff SQL clients.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1809
diff changeset
    14
  https://mariadb.com/kb/en/mariadb/graphical-and-enhanced-clients/
752c192306ff SQL clients.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1809
diff changeset
    15
     Graphical and Enhanced Clients.
752c192306ff SQL clients.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1809
diff changeset
    16
1809
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    17
mysql.
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    18
------
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    19
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    20
Official client. Install ``mariadb-client`` package - ``mysql`` utility
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    21
will be installed with dependent package.
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    22
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    23
Usage::
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    24
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    25
  $ mysql -u $USER -p
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    26
  Enter password:   <== Type password.
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    27
1851
e6ffc5f7200e To save content of session interaction.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1850
diff changeset
    28
To save content of session interaction in ``mysql`` use::
e6ffc5f7200e To save content of session interaction.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1850
diff changeset
    29
e6ffc5f7200e To save content of session interaction.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1850
diff changeset
    30
  mysql> tee out.txt
e6ffc5f7200e To save content of session interaction.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1850
diff changeset
    31
1809
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    32
mycli.
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    33
------
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    34
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    35
Python command line client with completion. Usage::
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    36
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    37
  $ mycli -u $USER
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    38
  Password:         <== Type password.
4e78b58a2935 MySQL/MariaBD.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    39
1811
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    40
List databases, tables, columns.
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    41
================================
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    42
::
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    43
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    44
  $ mysql -u $USER -p
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    45
  Enter password:   <== Type password.
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    46
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    47
  SHOW DATABASES;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    48
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    49
  USE mysql;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    50
  MariaDB [mysql]> SHOW TABLES;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    51
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    52
  MariaDB [mysql]> SHOW COLUMNS FROM user;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    53
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    54
  MariaDB [mysql]> DESCRIBE user;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    55
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    56
  MariaDB [mysql]> SHOW STATUS;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    57
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    58
  MariaDB [mysql]> SHOW CREATE DATABASE mysql;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    59
  MariaDB [mysql]> SHOW CREATE TABLE user;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    60
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    61
  MariaDB [mysql]> SHOW GRANTS;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    62
1849
a3b639d803e7 System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1836
diff changeset
    63
System information.
a3b639d803e7 System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1836
diff changeset
    64
===================
a3b639d803e7 System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1836
diff changeset
    65
a3b639d803e7 System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1836
diff changeset
    66
Server version, platform, etc::
a3b639d803e7 System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1836
diff changeset
    67
a3b639d803e7 System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1836
diff changeset
    68
  [mysql]> SHOW VARIABLES LIKE "%version%";
a3b639d803e7 System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1836
diff changeset
    69
1850
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
    70
Debugging queries error.
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
    71
========================
1836
4864231db031 Debugging.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1811
diff changeset
    72
4864231db031 Debugging.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1811
diff changeset
    73
After error or warning run::
4864231db031 Debugging.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1811
diff changeset
    74
1811
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    75
  MariaDB [mysql]> SHOW WARNINGS;
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    76
  MariaDB [mysql]> SHOW ERRORS;
1836
4864231db031 Debugging.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1811
diff changeset
    77
  MariaDB [mysql]> show engine innodb status;
1811
072cc8a704ce List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1810
diff changeset
    78
1854
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    79
Debugging quiery performance.
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    80
=============================
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    81
::
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    82
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    83
  explain select 1;
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    84
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    85
..
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    86
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    87
  https://mariadb.com/kb/en/mariadb/explain/
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    88
    Syntax and transcription of EXPLAIN.
9b224b68c1e1 Debugging quiery performance.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1853
diff changeset
    89
1850
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
    90
Monitor server execution.
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
    91
=========================
1852
f572f646694b Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1851
diff changeset
    92
f572f646694b Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1851
diff changeset
    93
List all current executions::
1850
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
    94
1852
f572f646694b Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1851
diff changeset
    95
  MariaDB [mysql]> show processlist;
f572f646694b Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1851
diff changeset
    96
f572f646694b Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1851
diff changeset
    97
Dump execution plan for selected process::
f572f646694b Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1851
diff changeset
    98
f572f646694b Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1851
diff changeset
    99
  MariaDB [mysql]> show explain for 1;
1850
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   100
1853
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   101
.. NOTE::
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   102
   You shoud check for code ``1003``::
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   103
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   104
     MariaDB [mysql]> show warnings;
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   105
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   106
   to ensure for which query plan shown becase query may finished and new
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   107
   started.
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   108
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   109
See:
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   110
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   111
 * `SHOW EXPLAIN in MariaDB 10.0 vs EXPLAIN FOR CONNECTION in MySQL 5.7
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   112
   <http://s.petrunia.net/blog/?p=89>`_
c6be6cc87df4 Dump execution plan for selected process.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1852
diff changeset
   113
1850
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   114
Profiling queries.
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   115
==================
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   116
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   117
Enable profiling, execute query and review profiling data::
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   118
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   119
  [mysql]> set profiling=1;
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   120
  [mysql]> select 1;
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   121
  [mysql]> show profile;
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   122
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   123
Each profiled query results stored separately. To get list of results::
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   124
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   125
  [mysql]> show profiles;
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   126
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   127
To retrieve selected profiling data::
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   128
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   129
  [mysql]> show profile for query 1;
84707c692266 Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1849
diff changeset
   130