Deploying MySQL on an Ubuntu ECS

Overview

This section describes how to deploy a MySQL database on an ECS running Ubuntu.

Prerequisites

1. An EIP has been bound to the ECS.
2. The rules listed in the following table have been added to the security groups that the target ECS belongs to. For details, see Adding a Security Group Rule.

   Table 1 Security group rules

   Direction	Priority	Action	Type	Protocol & Port	Source
   Inbound	1	Allow	IPv4	TCP: 22	0.0.0.0/0
   Inbound	1	Allow	IPv4	TCP: 3306	0.0.0.0/0

Procedure

Deploying MySQL

1. Create an Ubuntu ECS that can access the public network. Select the VNC or SSH login.

   

2. Run the following commands to update the ECS image source:

   sudo apt update
   sudo apt upgrade

3. Run the following command to install MySQL:

   sudo apt -y install mysql-server

   

4. Check the MySQL status and configure it to automatically start upon ECS startup:

   sudo systemctl status mysql
   sudo systemctl enable mysql

   

5. Run the following command to access MySQL:

   sudo mysql

   

6. Run the following command to set the password for user root:

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'kmXNB)60Ckap';

   

7. Run the following command to exit MySQL:

   exit

   

8. Run the following command to harden MySQL security as required:

   mysql_secure_installation

   (1) Enter the password configured in 6.

   (2) Enter y or Y if you want to configure a password policy.

   (3) Set the password strength:

   • Weak password: The password contains at least eight characters.
   • Medium password: The password contains at least eight characters and consists of uppercase letters, lowercase letters, digits, and special characters.
   • Strong password: The password contains at least eight characters, consists of uppercase letters, lowercase letters, digits, and special characters, and is checked against a dictionary attack.

     0: Weak password; 1: Medium password; 2: Strong password

   Enter 2 to use a strong password.

   (4) Enter y or Y for Change the password for root?.

   Enter the new password of user root. The password must meet strong password requirements.

   (5) Enter y or Y for Do you wish to continue with the password provided?.

   (6) Enter y or Y for Remove anonymous users?.

   (7) Enter y or Y for Disallow root login remotely?.

   (8) Enter y or Y for Remove test database and access to it?.

   (9) Enter y or Y for Reload privilege tables now?.

   

Adding a user for remote MySQL access

1. Run the following command to log in to the MySQL database as user root and enter the password configured in 8 as prompted:

   sudo mysql -u root -p

   

2. Run the following command to create a database named workspace:

   CREATE DATABASE workspace;

   

3. Create a user and grant the user full access to the database. In the command, workspaceuser indicates the database username, and xxxxx indicates the password of the database user.

   CREATE USER 'workspaceuser'@'localhost' IDENTIFIED BY 'xxxxx';
   GRANT ALL ON workspace.* TO workspaceuser@localhost;

   

4. Run the following command to update the MySQL permission table to ensure that the permissions granted in 3 take effect:

   FLUSH PRIVILEGES;

   

5. Run the following command to exit the MySQL CLI:

   exit

   

6. (Optional) Run the following commands in sequence to check that the database and user have been created and then exit the MySQL CLI (after the first command is executed, enter the password configured in 3):

   mysql -u workspaceuser -p
   SHOW DATABASES;
   exit