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')
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: |