Official mirror list.
.. -*- 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;