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