Mysql Snippets
Mar 25, 2018 18:15 · 201 words · 1 minute read
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@'%'