MariaDB Cheatsheet

06 January,2020 by Jack Vamvas

--connect to a MariaDB through command line 

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 (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on MariaDB Cheatsheet


dba-ninja.com