In this post, I will present few basic commands for MySQL administration. You’ll see how to create a database, create user, assign a database for specific user, etc. First I suppose that you have root password set and that you know it… (in next posts I will present few steps to set this password or to recover it…)
Log in to mysql console:
[root@hydra mysql]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 248 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> |
Here you can see server version (in this case 5.0.45). With next command you can see all databases
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | birds | | mysql | | private | | bdd1 | | test | +--------------------+ 6 rows in set (0.05 sec) mysql> |
If you want to create a new database, enter next command (remember that all SQL commands must be terminated with ; )
mysql> create database db1; Query OK, 1 row affected (0.00 sec) |
Now we need to assign a user to newly created database. Also, we need to grant him all privileges for this database but restrict access and operations on another databases (except test database)
mysql> CREATE USER 'db1'@'localhost' IDENTIFIED BY 'db1pass'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT ALL PRIVILEGES ON db1.* TO 'db1'@'localhost' IDENTIFIED BY 'db1pass'; Query OK, 0 rows affected (0.01 sec) mysql> quit; Bye |
To add readonly account you can set next privileges
GRANT SELECT ON db1.* TO 'db1'@'%'; |
If you replace “localhost” with “%”, the user will be able to connect from remote host.
After this you can repeat step from the beginning and test new user account. In next posts I will present phpMyadmin