MySQL Cheatsheet (commands and queries)
19 Sep 2018
Basic console commands:
# connect to some db:
mysql -h hostname -u username -pPassword schemaname
# create dump of the database (DB - test; dump - test.dump; host - localhost):
mysqldump -u root test > test.dump
# restore dump of the database:
mysql> create database test;
mysql> use test;
mysql> select database();
mysql> source test.dump
# create dump of the table:
mysqldump -u username -pPassword -h hostname databasename tablename > dumpname
# restore dump of the table:
mysql -h hostname -u username -pPassword databasename < dumpname
# connect to local DB as root:
mysql -uroot
# list available schemas:
show databases;
# select schema
use schema-name;
# show current schema:
select database();
Show size for all DB tables:
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
Show all table indexes:
SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
where table_name='opx_subscriptions';
Show DB Timezone
-- choose query you like
SELECT @@session.time_zone;
SELECT @@system_time_zone;
SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone);
LOG all SQL queries
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'table';
select * from mysql.general_log;
Count connections from all hosts
select substr(host, 1, locate(':', host) - 1) as ip, count(*) as connections_nr
from information_schema.processlist
group by ip
order by connections_nr desc;
-- for quick view
show processlist;
“Plot” bar-graph for query data
mysql> SELECT city, population, REPEAT('x', population/200000) FROM `us` ORDER BY population DESC;
+----------------------------+------------+-------------------------------------------+
| city | population | REPEAT('x', population/200000) |
+----------------------------+------------+-------------------------------------------+
| New York | 8107916 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| Los Angeles | 3877129 | xxxxxxxxxxxxxxxxxxx |
| Chicago | 2841952 | xxxxxxxxxxxxxx |
| Houston | 2027712 | xxxxxxxxxx |
| Philadelphia | 1453268 | xxxxxxx |
| Phoenix | 1428509 | xxxxxxx |
| San Diego | 1287050 | xxxxxx |
| San Antonio | 1256810 | xxxxxx |
| Dallas | 1211704 | xxxxxx |
| San Jose | 897460 | xxxx |
Thanks for hint to Rick James : https://stackoverflow.com/questions/53876360/plot-graphs-in-mysql