MySQL – Useful commands

(All from the command line)

List all databases

show databases;

MySQL Create Database

To create a database using the MySQL command-line

/usr/local/mysql/bin/mysql -u root -p

create database abc;

MySQL – granting permissions

Grant access to everything locally

grant all on *.* to 'me' [identified by 'password']

Grant access to everything from any host

grant all on *.* to 'me'@'%' [identified by 'password']

SQL to add/revoke permissions

To add/revoke permissions for all tables in a database:


-- to create the role
CREATE ROLE read_only_role_foo LOGIN PASSWORD 'password' NOINHERIT
VALID UNTIL 'infinity';
grant usage on schema public to read_only_role_foo;
select 'GRANT SELECT ON ' || c.relname || ' TO read_only_role_foo;'
from pg_class AS c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname NOT IN('pg_catalog', 'pg_toast')
and pg_table_is_visible(c.oid);



-- to drop the role
select 'REVOKE SELECT ON ' || c.relname || ' FROM read_only_role_foo;'
from pg_class AS c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname NOT IN('pg_catalog', 'pg_toast')
and pg_table_is_visible(c.oid);
REVOKE usage ON schema public FROM read_only_role_foo;
DROP ROLE read_only_role_foo;


MySQL Dump and Restore

BACKUP
dumps out and compresses:
mysqldump –all-databases | bzip2 -c >databasebackup.sql.bz2

or you can just dump out the mantis db:
mysqldump -uusername -ppassword mantisdbname   > mantisbackup.sql

or cron this script with date:
#!/bin/sh
date=`date -I`
mysqldump –all-databases | gzip > /var/backup/backup-$date.sql.gz

RESTORE:
To test restore right from a gz:
gunzip < mantisbackup.sql.gz | mysql -uusername -ppassword mantisdbname

or mysql -uusername -ppassword  mantisdbname < mantisbackup.sql