MYSQL is a famous open-source relational database management system that provides scalable and easy-to-use function sets. It uses a relational DB and Structured Query Language (SQL) for managing its data. This tutorial describes a basic MySQL installation and setup; later we will connect MySQL to EC2 instance from Ubuntu. Once done with the basics, we will create a user for our database.

MYSQL is a famous open-source relational database management system that provides scalable and easy-to-use function sets. It uses a relational DB and Structured Query Language (SQL) for managing its data.

This tutorial describes a basic MySQL installation and setup; later we will connect MySQL to EC2 instance from Ubuntu. Once done with the basics, we will create a user for our database.

Table of Contents

What is AWS EC2?

AWS EC2 – AWS Elastic Compute Cloud is one of the most famous Amazon services that provides the ability of running the app on the public cloud. It offers safe, reliable, and resizable cloud capacity with minimum obstacles.

I assume that you are quite familiar with Amazon EC2 and MySQL to proceed with MySQL Setup in EC2 Ubuntu Instance.

Prerequisites

To get started with the tutorial for setting up MySQL in EC2 Ubuntu Instance, please make sure of the following prerequisites-

⦿ Create EC2 Ubuntu Instance in AWS Account
⦿ Connect EC2 Instance using SSH or Putty

Now, let’s move towards how to set up and connect MySQL to EC2 Instance Ubuntu.

6 Steps to Setup and Connect MySQL to EC2 Instance from Ubuntu

Step 1: Update System Packages and Install MySQL

Run install update for your server system packages

Copy Text
sudo apt update

Now, install MySQL using the below command to install the latest MySQL.

Copy Text
sudo apt-get install mysql-server -y

After successful installation of mysql-server, we can check the MySQL version by using this command-

Copy Text
mysql --version

Step 2: Configure MySQL Installation

To configure the installation of MySQL, run the security script with sudo to run as the root user- the super privileged user.

Run the below command-

Copy Text
sudo mysql_secure_installation
Configure MySQL Installation

When we install MySQL for the first time, we will be prompted to create a new password.

If you are getting an error while setting up the password, check the temporary password in the log file.

Use the below command to get a temporary password.

Copy Text
cat /var/log/mysqld.log

We can find something like this:

Configure MySQL Installation

Step 3: Login into MySQL and Create a User

Use the below command to login into MySQL:

Copy Text
sudo mysql 

The command will log in to MySQL using the root user. Once you are logged in, create a user in the MySQL database using these commands:

Copy Text
use mysql;
CREATE USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

Step 4: Grant Access Privileges to User

Now, to grant access privileges to this newly created user, use this command:

Copy Text
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';

Once this is done, run this command,

Copy Text
FLUSH PRIVILEGES;

Step 5: Connect User with MySQL

Now you can use this user to connect with MySQL like this:

Copy Text
mysql -u user -p

Once prompted for a password, provide the password, and you will be connected.

Trick: If you want to store passwords for that user, so prompting will not annoy us, do these things.
Create .my.cnf file inside /home/Ubuntu path

Write this command:

Copy Text
sudo nano .my.cnf

Write these lines in that file and save.

Copy Text
[mysql]
user=username
password=password

Now, whenever you want to connect, you can use this .my.cnf file like this, and no need to provide a password.

Copy Text
mysql --defaults-file=/home/ubuntu/.my.cnf -u username

This file is generally needed when running the CI/CD pipeline and connecting to another server’s MySQL server.

Step 6: Restart MySQL Service

To affect these changes, restart the MySQL service by following commands:
to check the status of MySQL service:

Copy Text
systemctl status mysql.service

Now, if it is in “Running” mode, then restart it using the below command:

Copy Text
sudo service mysql restart

This will restart the service and will reflect our changes.

Now, your MySQL has been set up in your Ubuntu Ec2 Instance.

Conclusion

So, this was about how we can set up MySQL in EC2 Ubuntu Instance. If you have followed all the steps as discussed above, then MySQL setup is successfully done in EC2 Ubuntu Instance. Moreover, we covered creating a new database and adding a user to it. For more such tutorials, you can visit the Cloud Tutorials page to explore more about Cloud and DevOps services.

Are you looking for a certified architect for managing AWS services? Then, without wasting your time, connect us to hire AWS developers. Bacancy has proficient and qualified experts with AWS certifications who have competence in providing optimum solutions.

Start MySQL-EC2 Integration Today

Begin the process of harnessing the power of MySQL on your EC2 Instance to optimize your infrastructure. Reach Out to Our AWS Experts for Clarity and Success.

CONNECT NOW

Build Your Agile Team

Hire Skilled Developer From Us

Subscribe for
weekly updates

newsletter

What Makes Bacancy Stand Out?

  • Technical Subject Matter Experts
  • 2500+ Projects Completed
  • 90% Client Retention Ratio
  • 12+ Years of Experience

Our developers primarily focus on navigating client's requirements with precision. Besides, we develop and innovate to deliver only the best solutions to our clients.

get in touch
[email protected]

Your Success Is Guaranteed !

We accelerate the release of digital product and guaranteed their success

We Use Slack, Jira & GitHub for Accurate Deployment and Effective Communication.

How Can We Help You?