Mysql Snippets

Execute a statment without logging in

mysql -u root -pPassword -e "mysql statement"

Drop a user

DROP USER root@localhost

Get list of databases

mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database

Create a db with new user

CREATE DATABASE IF NOT EXISTS dbname
GRANT USAGE ON *.* TO user@host IDENTIFIED BY 'password'
GRANT ALL PRIVILEGES ON dbname.* TO user@host;"
FLUSH PRIVILEGES;"

Export a database

mysqldump --default-character-set=utf8 -u root -ppassword --result-file=dbname.sql dbname
mysqldump -uroot -p database -r utf8.dump

Note that when your MySQL server is not set to UTF-8 you need to do mysqldump –default-character-set=latin1 (!) to get a correctly encoded dump. In that case you will also need to remove the SET NAMES=‘latin1’ comment at the top of the dump, so the target machine won’t change its UTF-8 charset when sourcing.

See all the Users and their hosts

SELECT User, Host from mysql.user;

Turn General log on

set GLOBAL general_log = 0;

Set the general log

set GLOBAL general_log_file='/var/log/mysql.log';

Create a database

CREATE DATABASE IF NOT EXISTS db_name;

Give user access to db

GRANT USAGE ON *.* TO user_name@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON db_name.* TO user_name@'%';

Drop User

DROP USER user_name@'%'