torv.rocks

December 20, 2015

Useful commands for PostgreSQL

Some useful PostgreSQL commands that I need from time to time. Execute with postgres user.

List all users

psql
\du

Remove a user

dropuser USERNAME

List all databases

psql
\list

Remove a database

dropdb DATABASENAME;

Update password of user

psql
alter user USERNAME with password 'NEWPASSWORD';

Create a database and user, assign user to database

createuser -P USERNAME 
createdb -O USERNAME DATABASENAME

Remove all tables of a schema (if all tables in a single schema)

drop schema public cascade;
create schema public;

December 13, 2015

Useful commands for MySQL

Some useful MySQL commands that I need from time to time:

List all users

select User, Host from mysql.user;

Remove a user

show grants for 'USERNAME'@'localhost';
revoke all privileges, grant option from 'USERNAME'@'localhost';
drop user 'USERNAME'@'localhost';

List all databases

show databases;

Remove a database

drop database DATABASENAME;

Update password of user

update mysql.user set password=PASSWORD("NEWPASSWORD") where User = 'USERNAME';
flush privileges;

Create a database and user, assign user to database

create database DATABASE default character set utf8 collate utf8_general_ci;
grant all on DATABASE.* to 'USERNAME'@'localhost' identified by 'PASSWORD';
flush privileges;

Dump a database

mysqldump --user="<USERNAME>" --password="<PASSWORD>" <DATABASE> --quick --lock-tables --add-drop-table | gzip > /tmp/<NAME>.sql.gz

Category HowTo

List all users

Remove a user

List all databases

Remove a database

Update password of user

Create a database and user, assign user to database

Remove all tables of a schema (if all tables in a single schema)

List all users

Remove a user

List all databases

Remove a database

Update password of user

Create a database and user, assign user to database

Dump a database