+
−.. -*- 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;
+
−