Quick access to MariaDB Cheatsheet

Quick access to MariaDB commands I use on a daily basis including connection, DDLs of various database objects, backups , port numbers and other useful metadata details

--connect to a MariaDB through command line 

mysql --user=myuser --password=mypw --port=myportnumber --host=myservername

--find data directory

select @@datadir

--MariaDB port number used

SHOW GLOBAL VARIABLES LIKE 'PORT';

--backup
mysqldump -u myun -p database_name  > /mariadb/backups/mydb171117.sql

Note: you'll be prompted for the password

--list indexes

SHOW INDEX FROM mytable;

--drop index
DROP idx1 ON MYTABLE

--list tables
show tables;

--list views

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

--list databases
show databases;

--list functions

SHOW FUNCTIOn STATUS;

--show function DDL 

SHOW CREATE FUNCTION func_name;

--show create table DDL
show create table MYTABLE

--drop database
drop database db1;

--show columns
show columns FROM MYTABLE;

--get index information
SHOW INDEX FROM MYTABLE;
mysqlshow -k db_name tbl_name

--error log file
SHOW VARIABLES LIKE 'log_error'
tail xxxxx.err
tail -f xxxxxx.err

--select version
SHOW VARIABLES LIKE "%version%";

--find mariadb commands history
find / -name .mysql_history

--database collation

SELECT COLLATION('myDB');

--or 

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

--alter database collation

ALTER DATABASE mydb COLLATE = 'utf8_general_ci';

--Clear Query Cache

RESET QUERY CACHE

--Get Row Count from MariaDB table 

SELECT COUNT(*) FROM MYTABLE;

--Check for MariaDB connections

Method 1 : show status where variable_name = 'threads_connected';

Method2 : select id, user, host, db, command, time, state, info, progress from information_schema.processlist; 

Method 3: show process list