Creating a new database
Please type mysql inside your shell as a root:
$ mysql
mysql>
Now create a database:
mysql> CREATE DATABASE your_db;
Now create a user:
mysql> CREATE USER ‘Joe’@’localhost’ IDENTIFIED BY ‘password’;
Please note:
localhost – it’s an allowed hostname from which user joe can log in.
Now grant permissions for user joe to database your_db:
mysql> GRANT ALL PRIVILEGES ON your_db.* TO ‘joe’@’localhost’;
Syntax:
GRANT type_of_permission ON database_name.table_name TO ‘username’@’localhost’;
Permissions:
- ALL PRIVILEGES – to grant all privileges for user to database
- CREATE – allows to create a new tables or databases
- DROP – allows to delete tables or databases
- DELETE – allows to delete rows from tables
- INSERT – allows to insert rows into tables
- SELECT – allows to use the SELECT command to read databases
- UPDATE – allow to update table rows
- GRANT OPTION – allows to grant or remove other users privileges
Please reload privileges after GRANT command:
mysql> FLUSH PRIVILEGES;
Check user’s privileges:
mysql> SHOW GRANTS FOR ‘joe’@’localhost’;
If you want to revoke privileges:
mysql> REVOKE ALL PRIVILEGES ON your_db.* ‘joe’@’localhost’;
If you don’t longer need user Joe, you can remove it by typing:
mysql> DROP USER ‘joe’@’localhost’;
Please bear in mind any changes of privileges need to be reloaded by using:
mysql> FLUSH PRIVILEGES;
