Manually create MySQL database in CLI

MySQL is a widely spread SQL database management system mainly used on LAMP (Linux/Apache/MySQL/PHP) projects.

The best way to modify mysql database is going through phpmyadmin webgui. But if you only have local SSH shell access to the database, you will need to confirm the way of create mysql database and user in command line.

For the purpose of this tutorial, I will explain how to create a database and user for the wordpress.
The requirement for this set up is to have access to a database. We are going to create a database called wordpressdb which will be accessible from localhost to user wpuser idetified by the password passwd123….

On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

Now that you are logged in, we create a database:

mysql> create database wordpressdb;
Query OK, 1 row affected (0.00 sec)

We allow user wpuser to connect to the server from localhost using the password passwd123:

mysql> grant usage on *.* to wpuser@localhost identified by ‘passwd123’;
Query OK, 0 rows affected (0.00 sec)

And finally we grant all privileges on the wordpress database to this user:

mysql> grant all privileges on wordpressdb.* to wpuser@localhost ;
Query OK, 0 rows affected (0.00 sec)

And that’s it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u wpuser -p’passwd123′ wpdb
Your MySQL connection id is 12
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql>