Database Commands

databseCommands.txt

#MSQL
CREATE DATABASE `DB_NAME`;
CREATE USER `DB_USER`@'%' IDENTIFIED BY 'DB_PASS';
GRANT CREATE,DELETE,INSERT,SELECT,UPDATE ON `DB_NAME`.* TO `DB_USER`@'%';
SELECT User from mysql.user;
SELECT User from mysql.user WHERE USER='DB_USER';
SHOW GRANTS for DB_USER;
FLUSH PRIVILEGES;
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'DB_USER'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `DB_NAME`.* TO `DB_USER`@'%';

#Show user connections.
SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
    SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
    FROM information_schema.processlist
    WHERE user NOT IN ('system user','root')
) A GROUP BY usr,hst WITH ROLLUP;

#RDS
#Show current connection by users:
SHOW PROCESSLIST;
#Kill connection id:
CALL mysql.rds_kill(2417469);

#PSQL
CREATE DATABASE ptest;
REVOKE connect ON DATABASE database_name FROM PUBLIC;
CREATE USER ptest WITH PASSWORD 'ptest';
GRANT connect ON DATABASE ptest TO ptest;
\c ptest
GRANT DELETE,INSERT,SELECT,UPDATE ON ALL TABLES IN SCHEMA public TO ptest;

// This has to be done from the user who created the database. By default admin dopesn't have access to any tables.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO redash;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO redash;

#PSQL OPTIMIZATIONS
alter database DB_NAME set statement_timeout = 60000;
alter database DB_NAME set idle_in_transaction_session_timeout = 10000;

SELECT  state,datname, COUNT(*) FROM pg_stat_activity GROUP BY datname, state;
select pg_terminate_backend(pid) from pg_stat_activity where datname='DB_NAME';