Tech Chorus

We discussed how to install LAMP in one of our previous articles. Some of you may be wondering how to create databases, users and tables on your newly installed MySQL server. This blog post introduces the preliminary steps to work with your new LAMP server.

We are going to learn the following:

• Changing MySQL user password
• Creating databases
• Deleting databases
• Creating MySQL user accounts
• Deleting MySQL user accounts
• Connecting to the MySQL server from the command line
• Creating and listing tables

Changing root password: As soon as the MySQL server is installed, root user with blank password is created. The MySQL root user will have full access to perform any operation on the MySQL server. It is a good practice to change the root password immediately after installation.

It is also worth mentioning that MySQL users are different from system users. If you try to connect to the MySQL server as root using your system root password, it will not work. MySQL server maintains its own users in the `user` table of `mysql` database.

To change the MySQL root password follow the below steps.

1. Launch the Linux terminal. We will use the MySQL client commands to change the root password.
2. Connect to the MySQL server as root user.

mysql -u root -p

3. Just press the enter key when prompted for password, since the default password is blank. You will enter the MySQL client upon successful connection. The shell prompt changes to 'mysql>'.
4. In the MySQL client, type the below command to change the password for root user.

SET PASSWORD FOR 'root'@'localhost' = password('newpassword');

Replace 'newpassword' with the password you want to set.

Creating database: creating a new database is as simple as typing three easy to remember words in the MySQL client interface.

CREATE DATABASE myfirstdatabase;

Your new database 'myfirstdatabase' is ready to use. Let us create a MySQL user that has access only to 'myfirstdatabase' database. Let us call our new MySQL user 'jamesbond'.

GRANT ALL PRIVILEGES ON myfirstdatabase.* TO 'jamesbond'@'localhost' IDENTIFIED BY 'jamesbondspassword' WITH GRANT OPTION;

The above command performs the following:

• Creates the user account 'jamesbond'
• Sets the password 'jamesbondspassword' for the user 'jamesbond'
• Grants all privileges to 'jamesbond' on the database 'myfirstdatabase'.
  ALTER, CREATE, DELETE, DROP, UPDATE are some examples of MySQL privileges. The 'WITH GRANT OPTION' clause enables 'jamesbond' to give other users privileges on 'myfirstdatabase'.

  The syntax we used in our command is:
  

  GRANT ALL PRIVILEGES ON databasename.* TO 'user'@'hostname' IDENTIFIED BY 'password' WITH GRANT OPTION;

Flushing privileges: After using the GRANT command, you have to flush the privileges so that the MySQL server reads the grant tables again. Otherwise you have to restart MySQL server for the changes to take effect.

FLUSH PRIVILEGES;

Deleting database: Deleting a MySQL database is accomplished with a three word command.

DROP DATABASE myfirstdatabase;

Deleting user: We created the user 'jamesbond' using the GRANT command. To delete the user 'jamesbond' we use the DROP USER command.

DROP USER 'jamesbond'@'localhost';
FLUSH PRIVILEGES;

Creating sample table: You are now capable of creating and deleting database and users. Let us create a sample table assuming 'jamesbond' user has privileges to create tables on 'myfirstdatabase'.

From the Linux termianl connect to the MySQL server:

mysql -u jamesbond -ppassword -D myfirstdatabase

mysql -u jamesbond -D myfirstdatabase -p

Type the below SQL in the MySQL client to create a sample table

CREATE TABLE myfirsttable (myfirstcolumn int);
SHOW TABLES;

I hope this article has shed light on setting your MySQL server for the first time. You should now be able focus more on creating MySQL database driven applications.