Sign in Subscribe
Linux

Installing MySQL on AWS

Install MySQL on Ubuntu Linux and make it accessible to the world (better use a good password)

Professor S

• 3 min read

MySQL is a popular open source relational database software that can be used to power any data-driven application. It's awesome, it's free, and it can be installed on any operating system. In this post, you'll learn to install MySQL on an AWS instance, then open up the firewall to allow traffic to connect.

Goals

  1. Be able to install and set up MySQL on your own AWS EC2 instance
  2. Connect to that MySQL server from MySQLWorkbench on your computer

Steps

  1. Create an EC2 instance running Ubuntu Server. I used Ubuntu Server 24.04 LTS
  2. Log into the server with SSH
  3. Install the MySQL server:
    sudo apt install mysql-server
    When installed, the MySQL server automatically starts itself. If you want to check if the server is running, you can use sudo service mysql status to check if it is active.
💡
If the service status doesn't drop you right back into the terminal (with ubuntu@...), just hit the q key on your keyboard to quit the status program.

Setting up MySQL Server

Allow MySQL to listen for outgoing connections

For security reasons, MySQL server only listens for connections from the localhost computer (127.0.0.1). To connect from outside, you need to bind the MySQL service to other IP addresses:

  1. On the server (in SSH) open /etc/mysql/mysql.conf.d/mysqld.cnf with your favorite text editor (probably need sudo)
  2. Find the line that says bind-address = 127.0.0.1
  3. Change out 127.0.0.1 for 0.0.0.0 to allow MySQL to listen to all incoming connections
  4. Save and close the file
  5. Restart the MySQL service with sudo service mysql restart

Set up your MySQL user

When MySQL is installed, it automatically creates a user account for your computer's root user. It's generally a bad idea to log in as root all the time, and especially remotely, so we're going to set up a non-root admin user for remote access.

Generally it's not great practice to allow connections to your database from anywhere. These connections should be restricted to specific hosts (IP addresses) to reduce the possibility that your database gets attacked by brute force password attempts. Since this database is just for experimentation, we're going to open it up a little more than we should.

❗
It is critical to use good, long, hard-to-crack passwords for your database. If you have a password manager that can generate passwords for you, use that. If not, you can use the LastPass password generator at https://www.lastpass.com/features/password-generator. Choose a password that is at least 20 characters long and includes numbers and uppercase and lowercase letters. Symbols are a little tricky with databases, so you can exclude those. Then DON'T LOSE YOUR PASSWORD.
  1. On the server (in SSH) open MySQL as the root user with sudo mysql.
  2. Create a new user with access to your database from anywhere: CREATE USER 'username'@'%' IDENTIFIED BY 'yourpassword';. Make sure to sub in your username and password, but leave the '%', as that allows connections from anywhere.
  3. Grant the new user privileges on all databases to be able to create and use databases and tables: GRANT ALL PRIVILEGES on *.* to 'username'@'%';
  4. Run the following command in the mysql prompt to refresh the privileges: FLUSH PRIVILEGES;
  5. To quit the MySQL terminal, type exit.

Setting up AWS

Now that you've got a user that can log in, you need to set up AWS to allow MySQL network traffic to get to the server.

  1. Open the AWS Management Console, navigate to the EC2 section and open your instance information.
  2. On the bottom half of the page, find the Security section
  1. Click the Security group ID (it starts with sg-0e12 in the screenshot above)
  2. Under Inbound rules, click "Edit inbound rules"
  3. Click "Add Rule" at the bottom of the page to create a new inbound rule.
  4. On the page that pops up, select "MYSQL/Aurora" for the type, and "Anywhere-IPv4" for the Source
  5. Click the "Save rules" button

Connect to your database from MySQL Workbench

Now that the server is up and running, and the server is accessible, you can connect to it from anywhere in the world.

  1. Open MySQL Workbench on your computer
  2. Create a new connection with the following details:
    1. Connection Name: whatever you want to call it
    2. Connection Method: Standard (TCP/IP)
    3. Hostname: The public IP address of your server
    4. Port: 3306
    5. Username: The MySQL user you created
    6. Password: The password you used (in the IDENTIFIED BY part)
  3. You can click "Test Connection" to make sure you've got all the details correct. Once that works, you can click OK to save the new connection.

Now when you use this MySQL Workbench connection, the database, tables, and records you create will be created on the server.