How to install MySQL in Ubuntu?

MySQL is a most popular and open source relational database. it provides multi user support with a many storage engines. in this tutorial we are going to install MySQL in Ubuntu.

Let’s begin with simple steps:

Step 1 : Open terminal in Ubuntu and install MySQL package

Here, we are going to install the MySQL using the terminal. Open terminal by pressing Ctrl+Alt+T or ssh into server

sudo apt-get install mysql-server

 This will install the MySQL server and won’t ask you to set the password so let’s move to the next step of securing the MySQL server.

2.2. Securing MySQL server

After the fresh installation of MySQL, we are going to secure our MySQL by strengthening the password, type of authentication and root password.

sudo mysql_secure_installation

The above command will ask many questions as below

2.2.1 Do you want to Validate password plugin

Securing the MySQL server deployment

Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No:

2.2.2 Set root password for MySQL

Next prompt will be set root password .

Please set the password for root here.

New password:
Re-enter new password:

Enter the password twice and move to next step.

2.2.3 Remove Anonymous Users

Next question would be for removing Anonymous user.

Remove anonymous users? (Press y|Y for Yes, any other key for No) 

Enter Y and go ahead.

2.2.3 Disable Remote Root Login

By default, MySQL comes with a database named ‘test’ that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

Enter Y and go ahead.

2.2.4 Reload Privilege Tables

Reloading privileges to ensure that all changes will take effect.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

Enter Y and go ahead.

2.2.5 Check MySQL Service

sudo service mysql status

2.2.6 Login into MySQL terminal

sudo mysql -u root -p 

Enter password and you will be in to mysql session.

Create a user in MySQL

Login into MySQL and enter below commnad

CREATE USER 'test'@'localhost' IDENTIFIED BY 'newpassword';

test is the username, localhost is name of server and newpassword is password for new user.

Flush privileges so new user reflect in users table of MySQL.

FLUSH PRIVILEGES;

Grant permissions to new user

GRANT SELECT ON * . * TO 'test'@'localhost';

Here *.* means we are assigning all the permissions to user test. First * denotes the databases so we can use the database name and second * denotes the permission for the database so lets change it to database level.

GRANT ALL PRIVILEGES ON `testdb` . * TO 'test'@'localhost';

Login using the new user rather then root

mysql -u test -p

Now you will be logined as test user.