To access postgres from database server as postgres user,
su - postgres
psql
To access as specific user to specific database,
psql -d DATABASE -U USER -W
Use -h for host, can require ssl
psql -U USER -h HOST "dbname=DATABASE sslmode=require"
List databases,
\l or \list
Switch to database,
\c DATABASE or \connect DATABASE
List tables,
\dt
If no tables exist, will get the following:
No relations found.
Exit psql prompt,
\q
Describe a table,
\d TABLE
List schema,
\dn
List available functions,
\df
List available views,
\dv
List users/roles,
\du
View version,
SELECT version();
Alternatively, from BASH prompt,
psql -V
Run previous command,
\g
View command history,
\s
Execute psql commands from a file,
\i FILENAME
View help
\?
List extension for database,
\dx
Must be connected to a database!
List users and roles,
\du
View help for specific command,
\h COMMAND
i.e. \h ALTER TABLE
Time query,
\timing QUERY
Turn off timing with same command
MySQL dump,
mysqldump -u root -p --databases DATABASE >> DATABASE.sql
MySQL restore from sql dump,
mysql -u USER -p DATABASE < mysqldump.sql
MySQL remove database,
DROP DATABASE database;
MySQL show running queries,
SHOW PROCESSLIST;
MySQL create database,
CREATE DATABASE database;
MySQL list users,
SELECT user FROM mysql.user;
MySQL list users w/ hostnames,
SELECT user,host FROM mysql.user;
MySQL remove user,
DROP USER 'USERNAME'@'IP';
MySQL create user,
CREATE USER 'user'@'IP' IDENTIFIED BY 'password';
MySQL show user grants,
SHOW GRANTS FOR 'USERNAME'@'IP';
MySQL reset user password,
ALTER USER 'USERNAME'@'IP' IDENTIFIED BY 'NEWPASS';
FLUSH PRIVILEGES;
MySQL remove table:
DROP TABLE TABLENAME;
MySQL update user host in database:
UPDATE mysql.user SET host = 'newIP' WHERE host = 'oldIP';
FLUSH PRIVILEGES;
MySQL create table:
CREATE TABLE table;
MySQL review queries dropped from cache to lack of memory,
SHOW STATUS LIKE 'Qcache_lowmem_prunes';
MySQL filter SHOW TABLES:
SHOW TABLES FROM DBNAME WHERE Tables_in_DBNAME LIKE '%Table%';
Cloud SQL create user,
CREATE USER 'USER_root'@'%' IDENTIFIED BY 'PASSWORD';
Get locked out of database, unable to login as root user:
Open and edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distribution.
Add skip-grant-tables under [mysqld]
Restart MySQL
You should be able to log in to MySQL now using the below command mysql -u root -p
Run mysql> flush privileges;
Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
Replace "NewPassword" with your actual password.
Go back to /etc/my.cnf and remove/comment skip-grant-tables
Restart MySQL
Now you will be able to login with the new password mysql -u root -p
MySQL check,
mysqlcheck -u root -p --databases DATABASE
MySQL check w/ repair,
mysqlcheck -u root -p --auto-repair --databases DATABASE