Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

Quick access to MariaDB Cheatsheet

06 January,2020 by Rambler

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

--Stop , Start . Status , Disable 

systemctl stop mysql
systemctl start mysql
systemctl status mysql
systemctl disable mysql 

systemctl enable mysql

If you get the error message   mysql.service is not a native service, redirecting to /sbin/chkconfig.  try :

systemctl start mysql

 

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

--How to export MariaDB SHOW GLOBAL VARIABLES to a text file

mysql -uroot -p -A -e"SHOW GLOBAL VARIABLES;" > /tmp/global_variables.txt

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

Note: you'll be prompted for the password

--list databases

show databases;

--list indexes

SHOW INDEX FROM mytable;

--drop index
DROP idx1 ON MYTABLE

--list tables
show tables;

--Move a table to another database on the same filesystem

RENAME TABLE MYDB1.mytable TO MYDB2.mytable;

--list views

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

--list databases
show databases;

--list functions

SHOW FUNCTIOn STATUS;

--get the MariaDB function status

SHOW FUNCTION STATUS WHERE `name` = 'my_function'

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

--find MariaDB 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 

--Load data from a csv file  into a pre existing table.

use MY_NEW_DATA;
LOAD DATA LOCAL INFILE "/mydirectory/mydata.csv" INTO TABLE MYDATABASE.MYTABLE FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

 

--Dump data out of a MariaDB database with mysqldump   with a limit on rows

mysqldump -t -u my_un -pmy_pw MY_DB MY_TABLE --opt --where="1 limit 10" > /tmp/my_table_top10.sql

--Import data back in 

mysql -u my_un -p MY_DB < my_table_top10.sql

--Change User password

SET PASSWORD FOR 'myuser'@'localhost' = PASSWORD('newpassword')


Author: Rambler (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 Quick access to MariaDB Cheatsheet


dba-ninja.com