Basic MySQL operations I

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.

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)

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;

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.