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

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

–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

–show create table
show create table MYTABLE

–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

Author: Jack Vamvas