Tag Archives: create db

MySQL DB creation and user management

Useful commands:

>>Create DB and user
create database $DB;
grant all privileges on $DB.* to $DBUSER@localhost identified by '$PASS';

>> Check GRANT permissions:
SHOW GRANTS FOR 'root'@'localhost';

>> How to grant privileges
GRANT ALL PRIVILEGES ON *.* TO 'admin1'@'localhost' IDENTIFIED BY '$password';
GRANT ALL PRIVILEGES ON 'db01.* TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON 'db02.* TO 'db02admin'@'localhost' WITH GRANT OPTION;

>> Grant read-only
GRANT SELECT ON $database_name.* TO $user@$host IDENTIFIED BY '$password';

>> Set password:
set password for 'root'@'%' = password('yournewpassword');
set password for 'root'@'localhost' = password('yournewpassword');

>> Show all users
mysql> use mysql;
mysql> SELECT User,host FROM mysql.user;

>> Remove user
mysql> use mysql;
mysql> DROP USER 'bob'@'localhost';

>> Update user
SELECT User,host FROM mysql.user where user='chris';
update host set host='%' FROM mysql.user where user='chris';

>> Check grants/permission for a user
show grants for 'chris'@'%';