Cheatsheet: database shell commands

In first steps young DBA can greedily soak up all kinds of database knowledges: as RDBMS as NoSQL. We’re realising things like how table index works, how to optimize memory caching, write triggers and procedurs etc.. But we still can forget a simple command to create user or show tables list. Also we can get stuck when switching from MySQL to PostgreSQL or whatever else, cause command syntax is different.


For this issue I lead a special cheatsheet table of DBMS commands. This table is splitted on few parts for each system I faced. I’ll try to update it as soon as I’ll start to explore other DBMS solutions.

MySQL PostgreSQL Oracle Database
Shell command to ask MySQL command line mysql u admin -p psql U admin password sqlplus admin/password
Create new user admin CREATE USER ‘admin’@‘localhost’ IDENTIFIED BY ‘password’; CREATE USER admin WITH PASSWORD ‘password’; CREATE USER admin IDENTIFIED BY

password

List all available databases SHOW DATABASES \l cat /etc/oratab #from shell
List all available tables in current database SHOW TABLES \dt SELECT table_name FROM all_tables
List all users in RDBMS SELECT User FROM mysql.user \du select * from dba_users
Check database grants for any user SHOW GRANTS \dp SELECT GRANTEE, OWNER, GRANTOR,

PRIVILEGE,GRANTABLE

FROM DBA_TAB_PRIVS;

Switch to another database USE database2 \connect database2 sqlplus

admin/password@localhost/database2

Read help about command syntax HELP select’ \h SELECT HELP SELECT
Execute shell command \! pwd \! pwd Hard-to-explain Oracle scheduler
Execute SQL script from file source mydir/test.sql \i mydir/test.sql echo @mydir/test.sql | sqlplus username/password@connect

(only from command line, sorry)

Get database size SELECT data_length + index_length as “size” FROM information_schema.TABLES WHERE table_schema = “mydb” SELECT pg_database_size(mydb); Still hard to explain
Check data storage directory SHOW VARIABLES WHERE Variable_Name LIKE “%dir”; SHOW data_directory; SELECT value FROM v$parameter

WHERE name = ‘db_create_file_dest’

List all indexes SELECT *

FROM   information_schema.STATISTICS

WHERE  TABLE_SCHEMA = DATABASE();

\di SELECT user_tables.table_name, user_indexes.index_name

FROM user_tables JOIN user_indexes on user_indexes.table_name = user_tables.table_name

ORDER by user_tables.table_name,user_indexes.index_name;

Grant SELECT on all DB tables GRANT SELECT ON mydb.* TO ‘user’@‘localhost’; GRANT SELECT ON ALL TABLES IN SCHEMA public TO user; So much hard to explain
Set config parameter SET parameter=value; ALTER SYSTEM SET parameter = value; ALTER SYSTEM SET parameter = value;
Make a logical backup mysql dump -h host -u user -pPassword db pg_dump -h host -d db -U user -W NO WAY!
Return info about dead tuples at the table (PostgreSQL only) SELECT relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_catalog.pg_stat_all_tables WHERE n_dead_tup > 0 AND relname NOT LIKE ‘pg%’;
Terminate all connections to database SELECT CONCAT(‘KILL ‘,id,’;’) FROM information_schema.processlist WHERE user=’root’ INTO OUTFILE ‘/tmp/a.txt’;

SOURCE /tmp/a.txt;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname=’ticktest’; startup forse; (as sys)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s