.. -*- coding: utf-8; -*-

================

 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.

Usage::

  $ mysql -u $USER -p

  Enter password:   <== Type password.

  $ mysql -h $HOST -u $USER -p$PASS

  $ mysql -h $HOST -u $USER -p$PASS  $DATABASE

  $ mysql --host=$HOST --user=$USER --password=$PASS

  $ mysql --host=$HOST --user=$USER --password=$PASS  $DATABASE

  $ mysql --host=$HOST --port=$PORT --user=$USER --password=$PASS  $DATABASE

  $ mysql --host=$HOST --port=$PORT --protocol=TCP --user=$USER --password=$PASS  $DATABASE

To save content of session interaction in ``mysql`` use::

  mysql> tee out.txt

https://dev.mysql.com/doc/refman/8.0/en/connecting.html

  Connecting to the MySQL Server.

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;

Configuring in Debian

=====================

After fresh installation login to DB::

  $ sudo mysql -u root

Create database and grant permissions to access from ``localhost``::

  CREATE DATABASE testdb;

  CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

  GRANT ALL PRIVILEGES ON testdb.* TO 'user'@'localhost';

  FLUSH PRIVILEGES;

  quit

Replace localhost with arbitrary host name or IP address, or use ``%`` to allow any host::

  CREATE USER 'user'@'192.168.0.1' IDENTIFIED BY 'password';

  GRANT ALL PRIVILEGES ON testdb.* TO 'user'@'192.168.0.1';

  CREATE USER 'user'@'%' IDENTIFIED BY 'password';

  GRANT ALL PRIVILEGES ON testdb.* TO 'user'@'%';

Login with client to new database::

  mysql -u user -p

Switch to schema::

  USE testdb;