MySQL/MariaBD.

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

mysql> tee out.txt

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

MariaDB [mysql]> show warnings;

See:

• SHOW EXPLAIN in MariaDB 10.0 vs EXPLAIN FOR CONNECTION in MySQL 5.7

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;