Elastic Compute Service:Manually deploy a MySQL database on an ECS instance that runs CentOS 7

Step 1: Install MySQL

1. Connect to the ECS instance.
   For more information, see Connection methods.
2. Run the following command to update the YUM repository:

   sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

3. Run the following command to install MySQL:

   sudo yum -y install mysql-community-server --enablerepo=mysql80-community --nogpgcheck

4. Run the following command to check the version of MySQL:

   mysql -V

   The following example command output indicates that MySQL is installed.

Step 2: Configure MySQL

1. Run the following command to start MySQL:

   systemctl start mysqld

2. Run the following command to enable MySQL to run on system startup:

   systemctl enable mysqld

3. Run the following command to check the /var/log/mysqld.log file and obtain and record the initial password of the root user:

   grep 'temporary password' /var/log/mysqld.log

   A command output similar to the following one is returned:

   2022-02-14T09:27:18.470008Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: r_V&f2wyu_vI

   Note r_V&f2wyu_vI at the end of the command output is the initial password, which is required to configure the security settings for MySQL.
4. Run the following command to configure the security settings for MySQL:

   mysql_secure_installation

   1. Reset the password of the root user.
      Note When you enter a password, no command output is returned to maximize data security. You need only to enter the correct password and then press the Enter key.

      Enter password for user root: # Enter the initial password of the root user that you obtained.
      
      The existing password for the user account root has expired. Please set a new password.
      
      New password: # Enter a new password.
      
      Re-enter new password: # Enter the new password again.
      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.
      Change the password for root ? (Press y|Y for Yes, any other key for No) : Y # Enter Y to update the password. You can also enter N to skip updating the password. 
      
      New password: # Enter the new password.
      
      Re-enter new password: # Enter the new password again.
      
      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 # Enter Y to use the new password. 

   2. Delete the anonymous user.

      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 # Enter Y to delete the default anonymous user. 
      Success.

   3. Deny remote access by the root user.

      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 # Enter Y to deny remote access by the root user. 
      Success.

   4. Delete the test database and the access permissions on the database.

      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 # Enter Y to delete the test database and the access permissions on the database. 
       - Dropping test database...
      Success.
      
       - Removing privileges on test database...
      Success.

   5. Reload privilege tables.

      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 # Enter Y to reload privilege tables. 
      Success.
      
      All done!

   For more information about the security settings of MySQL, see MySQL documentation.

Step 3. Access the MySQL database

You can use a database client or Data Management Service (DMS) provided by Alibaba Cloud to access the MySQL database. In this topic, DMS is used to access the MySQL database.

1. Add an inbound rule to a security group of the ECS instance to allow traffic on the port number of the MySQL database.
   The default port number of the MySQL database is 3306. You must add inbound rules to a security group of the ECS instance and allow traffic on port 3306. For more information, see Add security group rules.
2. On the ECS instance, create an account for remote logon to the MySQL database.
   1. Run the following command and enter the password for the root user to log on to the MySQL database:

       mysql -uroot -p

   2. Run the following commands in sequence to create an account for remote logon to the MySQL database.
      We recommend that you use a non-root account to log on to the MySQL database. In this example, the dms account and the 123456 password are used.

      Notice When you create an account, replace the 123456 password with a valid password and keep it confidential. The password must be 8 to 30 characters in length and must contain uppercase letters, lowercase letters, digits, and special characters. Special characters include

      ( ) ` ~ ! @ # $ % ^ & * - + = | { } [ ] : ; ‘ < > , . ? /

      mysql> create user 'dmsTest'@'%' identified by '123456'; # Create a database account named dmsTest and grant the remote connection permissions to the account. 
      mysql> grant all privileges on *.* to 'dmsTest'@'%'; # Grant the dmsTest account all permissions on the database. 
      mysql> flush privileges; # Refresh the permissions. 

3. Log on to the DMS console.
4. In the top navigation bar of the Data Assets tab, click Instances.
5. On the Instances tab, click + New.
6. In the Add Instance dialog box, configure the parameters.
   1. Click ECS Self-built and then click MySQL.
   2. In the Basic Information section, configure the following parameters to access the database:
      • Data Source: Select ECS Self-built.
      • Database Type :Select MySQL.
      • Instance Region and ECS Instance ID: Set the parameters based on the information of the ECS instance. For more information about how to obtain the information of an ECS instance, see View information of instances on the Instances page.
      • Port: Use the default value 3306.
      • Database Account: Enter the database account for remote connection. In this example, enter dmsTest.
      • Database Password: Enter the password of the database account for remote connection.

      Use the default values for other parameters.
   3. Click Test Connection.
      If you are connected to the MySQL database, the Success dialog box appears.
   4. Click Submit.
7. After you submit the configurations, go to the Database List tab on the Home page as prompted.
   You can use Shortcuts in the DNS console to manage the database. For more information about the operations on DMS, see Overview.