This tutorial explains, how to Install mysql on Ubuntu/Centos/Amazon Linux.

Mysql is an open-source Relational Database Management System(RDBMS). It is used in Relational Database and Structured query language to manage all the data. It is one of the most popular databases used in the world.

In this tutorial, we will learn to install MySQL in Ubuntu/Centos and Amazon Linux(EC2) and some basic commands.

1)  Install MySql on Ubuntu

 2) Install Mysql on Centos/Amazon Linux

3) Basic Commands for MySQL

Also Read: Upgrading Redis with zero downtime

How to Install PostgreSQL from source on Linux

 Install MySQL Ubuntu

Step 1- Update your Ubuntu box

$ sudo apt-get update  -y

Step 2– Install mysql from apt-get

 $ sudo apt-get install mysql-server -y

Step 3- Check status and start MySQL service

$ sudo systemctl status mysql

$ sudo systemctl start mysql

Step 4– Run sudo mysql_secure_installation

$ sudo mysql_secure_installation

The following Prompts will appear.

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN 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 plugin?

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

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters, and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

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

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

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) : y
– Dropping test database…
Success.

– Removing privileges on test database…
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

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

All done!

Step 5– Connect MySQL

  $ sudo mysql -u root -p

Note: Make sure to use sudo command to connect mysql first time else you may get the following error.

ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

Click to Tweet this tip!

Install MySQL on Amazon Linux/Centos

Step 1- Update your Amazon LInux/Centos box.

$ sudo yum update -y

Step 2- Install MYSQL using the following commands.

$ rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm

$ sed -i ‘s/enabled=1/enabled=0/’ /etc/yum.repos.d/mysql-community.repo

$ yum -enablerepo=mysql80-community install mysql-community-server

Step3– Check MYSQL service status and start the service

 $ service mysqld status

$ service mysqld start

Step 4– Grep Temporary Password to login.

 $ grep “A temporary password” /var/log/mysqld.log

2020-04-25T15:14:01.997277Z 5 [Note] [MY-010454] [Server] A temporary password is generated for [email protected]: .piuGSM4qAnd

Step5–  Run mysql_secure_installation

$  mysql_secure_installation

The following Prompts will appear. 

Securing the MySQL server deployment.

Enter password for user root:         ————> Enter the temporary password obtained in Step 4

The existing password for the user account root has expired. Please set a new password.

New password:   ——————> Enter new password

Re-enter new password: ———-> Re-enter new password
The ‘validate_password’ component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : yes

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : yes
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

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

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

… skipping.
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) : y
– Dropping test database…
Success.

– Removing privileges on test database…
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

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

All done!

Step 6– Connect MySQL

$sudo  mysql -u root -p
Enter password:             ———————-> Enter the new Password that you reset for root

The following screen will appear.

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14
Server version: 8.0.19 MySQL Community Server – GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

mysql>

Basic Commands

1) Connect Database

$ mysql -u username -p

1) Show databases

mysql> Show databases;

2) Create Database

mysql> create database mydb;

4) Use Database to work with

mysql> use mydb;

2) show tables

mysql> show tables;

2) Create User

mysql> CREATE USER ‘backup’@’localhost’ IDENTIFIED BY ‘[email protected]’;
Query OK, 0 rows affected (0.01 sec)

2) Grant full permission to an user

mysql> GRANT ALL ON *.* TO ‘backup’@’localhost’;

5) Grant Select permission to an User

mysql> GRANT SELECT ON *.* TO ‘backup’@’localhost’;

3) Run Flush Privileges after Granting/Changing any permission.

mysql> flush privileges;

4) List user’s information

mysql> select * from mysql.user;

or

mysql> select host, user from mysql.user;

6) Drop database

mysql>drop database mydb;

Note: Here mydb is the database name

Click to Tweet this tip!

I hope you enjoyed this tutorial and learned to Install mysql on Ubuntu/Centos/Amazon Linux. If you think this article is helpful, please do share this post with others as well. Please also share your valuable feedback, comment or any query in the comment box. I will really happy to resolve your all queries.

Thank You