Useful Commands and SQLs for MySQL and Postgres

create_db.sql

create database db_name;
GRANT ALL ON db_name.* TO db_name@localhost IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;

create_postgres_user.sh

createuser -P -s -e -d username

dump_and_restore.sh

# dump
mysqldump -u root -p --opt --flush-logs --single-transaction --all-databases > dump_name.sql

# restore
mysql -u root -p

my.conf

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
skip-external-locking

bind-address		= 0.0.0.0

# these are just examples
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover          = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
max_connections         = 100
table_cache             = 64
thread_concurrency      = 10
query_cache_type	= 1
innodb_buffer_pool_size	= 64M
#innodb_log_file_size	= 128M
#innodb_log_buffer_size	= 8M
join_buffer_size	= 64M
sort_buffer_size	= 16M
query_cache_size	= 32M
query_cache_limit	= 16M
key_buffer_size		= 16M
innodb_additional_mem_pool_size	= 16M

#general_log_file       = /var/log/mysql/mysql.log
#general_log            = 1

log_error               = /var/log/mysql/error.log

#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

#server-id		= 1
#log_bin		= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name

# chroot = /var/lib/mysql/
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
default-character-set=utf8
skip-character-set-client-handshake

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

show_db_size.sql

SELECT
  table_schema "Data Base Name",
  sum(data_length+index_length)/1024/1024 "Data Base Size in MB"
FROM
  information_schema.TABLES GROUP BY table_schema;