Installing MySQL Database Server on AWS EC2 - Part 1

Introduction

Amazon EC2 instances use yum as their default software package manager. I once needed to install a MySQL server on one of my EC2 instances, So I ran sudo yum list | grep mysql to see the available MySQL packages, and I couldn't find anything related to the MySQL Server (not the client). In this blog, I will guide you through installing MySQL Database Server - Community Edition on an Amazon EC2 instance.

Let's now jump into the steps!

Steps

1. We need to download the MySQL DB Server .rpm file using the below command:
   
   wget https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm
   You can get this URL from: https://dev.mysql.com/downloads/repo/yum/
2. Install the downloaded file using:
   
   sudo dnf install mysql84-community-release-el9-1.noarch.rpm
3. Let's verify that mysql-community-server is now available through the below command:
   1. sudo yum list | grep mysql-community-server
   2. You should now be able to see something like mysql-community-server.x86_64
4. Now, we can install it using:
   
   sudo yum install mysql-community-server
5. You now have installed MySQL on your server; let's start it by running:
   
   sudo systemctl start mysqld
6. Let's verify that MySQL is running on the default port (3306):
   
   sudo lsof -i :3306
   you should be able to see there's a new process running on that port number
7. Try logging in to your MySQL server using:
   
   mysql -uroot
   you'll get the bellow error message:
   
   ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
8. Let's fix that by modifying the my.cnf file as below:
   
   1. sudo vim /etc/my.cnf
   2. Press on the letter I on your keyboard to enter INSERT mode
   3. Include skip-grant-tables under [mysqld] so the final contents will be:
      
      # For advice on how to change settings please see
      # http://dev.mysql.com/doc/refman/8.4/en/server-configuration-defaults.html
      [mysqld]
      skip-grant-tables
      #
      # Remove leading # and set to the amount of RAM for the most important data
      # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
      # innodb_buffer_pool_size = 128M
      #
      # Remove the leading "# " to disable binary logging
      # Binary logging captures changes between backups and is enabled by
      # default. It's default setting is log_bin=binlog
      # disable_log_bin
      #
      # Remove leading # to set options mainly useful for reporting servers.
      # The server defaults are faster for transactions and fast SELECTs.
      # Adjust sizes as needed, experiment to find the optimal values.
      # join_buffer_size = 128M
      # sort_buffer_size = 2M
      # read_rnd_buffer_size = 2M
      
      datadir=/var/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      
      log-error=/var/log/mysqld.log
      pid-file=/var/run/mysqld/mysqld.pid
      
   4. Exit and save changes by pressing ESC on your keyboard, writing :wq, and then enter.
9. We then have to restart the server for changes to take effect:
   
   sudo systemctl restart mysqld
10. Now, let's try accessing the server using:
    mysql
11. And that's it; you now have MySQL Database Server - Community Edition installed on your server!

But now we have an issue: Our Database server stopped listening on port 3306 because of adding skip-grant-tables; so it won't be able to receive requests from our backend code.

Don't worry—I've got you covered. We will solve this issue in part 2 of this blog; you can check it here.