06 January,2020 by Jack Vamvas
--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;
--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
--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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |