Elastic Compute Service:Deploy MySQL on a Linux instance

Step 1: Install MySQL

1. Connect to an ECS instance.

   For more information, see Connect to a Linux instance by using a password or key.

2. Run the following command to update the Yellowdog Updater Modified (YUM) repository.

   In this example, MySQL 8.0 is installed. For information about how to install other MySQL versions, see official MySQL documentation.

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

3. Optional. If the ECS instance runs Alibaba Cloud Linux 3, run the following command to install the packages required for MySQL:

   sudo rpm -Uvh https://mirrors.aliyun.com/alinux/3/updates/x86_64/Packages/compat-openssl10-1.0.2o-4.0.1.al8.x86_64.rpm

4. Run the following command to install MySQL:

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

   The following error message appears: Unknown repo: 'mysql80-community'

   Run the following commands to clear the YUM cache, create a YUM cache, and then install MySQL:

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

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

   mysql -V

   The following command output indicates that MySQL is installed:

   mysql Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)

Step 2: Configure MySQL

1. Run the following command to check the status of MySQL:

   sudo systemctl status mysql

   Common states of MySQL:

   • active (running): MySQL is up and running.

   • active (exited): MySQL completed tasks and gracefully exited. MySQL is in the active (exited) state only in rare cases. As a long-running service, MySQL is expected to be in the active (running) state rather than the active (exited) state.

   • inactive (dead): MySQL is not running. MySQL is in this state if MySQL has never been started or is already stopped.

   • failed: MySQL failed to start or is terminated due to a fatal error that occurred during runtime.

   • reloading (reload): In rare cases, MySQL may be in this state when you check the status of MySQL and MySQL is reloading configurations. However, in most cases, MySQL does not perform reload operations.

   • activating (start): MySQL is in this state for a short period of time during startup.

   Note

   The command output also displays the detailed log information about MySQL, such as the service startup time, primary process ID, error messages, and alerts. You can check the health status of MySQL and identify the causes of issues based on the information.

2. Run the following commands to start MySQL and enable MySQL to run on system startup:

   sudo systemctl start mysqld
   sudo systemctl enable mysqld

3. Run the following command to obtain and record the initial password of the root user:

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

   The following command output 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 of MySQL:

   sudo mysql_secure_installation

   1. Reset the password of the root user as prompted.

      Note

      For data security purposes, no output is returned when you enter a password. You need only to enter the correct password and then press the Enter key.

      Enter password for user root: # Enter the initial password that you obtained for the root user.
      
      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: # Re-enter the new password.
      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 the password update. 
      
      New password: # Enter a new password.
      
      Re-enter new password: # Re-enter the new password.
      
      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 revoke 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 revoke 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 official MySQL documentation.

Step 3: Connect to MySQL

We recommend that you use a non-root user to connect to MySQL. In this example, an account is created to connect to MySQL.

1. Connect to an ECS instance.

   For information about the connection methods, see Connection method overview.

2. Run the following command and enter the password of the root user to log on to MySQL:

   sudo mysql -uroot -p

3. Run the following commands in sequence to create an account and allow remote access to MySQL by using the account.

   In this example, the dmsTest account and the Ecs@123**** password are used.

   Important

   When you create an account, replace the Ecs@123**** password with a valid password and keep the password confidential. The password must be 8 to 30 characters in length and contain uppercase letters, lowercase letters, digits, and special characters. The following special characters are supported:

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

   # Create an account named dmsTest and grant the remote connection permissions to the account. 
   create user 'dmsTest'@'%' identified by 'Ecs@123****'; 
   # Grant all permissions on MySQL to the dmsTest account. 
   grant all privileges on *.* to 'dmsTest'@'%'; 
   # Refresh the permissions. 
   flush privileges; 

4. Run the following command to exit MySQL:

   exit

5. Connect to MySQL by using the dmsTest account.

   • We recommend that you use Alibaba Cloud Data Management (DMS) to access MySQL databases. For more information, see Register an Alibaba Cloud database instance.

   • You can use a MySQL client to log on to MySQL to perform tests. The MySQL client can be MySQL Workbench or Navicat.