mysql.rst
author Oleksandr Gavenko <gavenkoa@gmail.com>
Wed, 30 Dec 2015 01:20:37 +0200
changeset 1850 84707c692266
parent 1849 a3b639d803e7
child 1851 e6ffc5f7200e
permissions -rw-r--r--
Monitor server execution. Profiling queries.

.. -*- coding: utf-8; -*-
.. include:: HEADER.rst

================
 MySQL/MariaBD.
================
.. contents::

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.

mycli.
------

Python command line client with completion. Usage::

  $ mycli -u $USER
  Password:         <== Type password.

List databases, tables, columns.
================================
::

  $ 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;

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;

Monitor server execution.
=========================
::

  MariaDB [mysql]> SHOW PROCESSLIST;

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;