MySQL is a relational database management system. It provides tools to build web applications, such as applications based on the LAMP (Linux, Apache, MySQL, PHP) or LNMP (Linux, NGINX, MySQL, PHP) stack. This topic describes how to install, configure, and remotely access a MySQL database on a Linux ECS instance.

Prerequisites

You must have registered an Alibaba Cloud account before you follow the instructions provided in the tutorial. If not, create a new Alibaba Cloud account first.

You have added inbound rules to the security group associated with the ECS instance. Port 3306 is open for inbound traffic. For more information, see Add security group rules.

Background information

This topic is based on the following software versions. The operations may vary depending on the versions of your software.
  • Operating system: public image for CentOS 7.2 64-bit
  • MySQL: 5.7.26
This topic is based on an ECS instance with the following configuration. The operations may vary depending on the configuration of your ECS instance.
  • CPU: 2 vCPUs
  • Memory: 4 GiB
  • Network type: VPC
  • IP address: public IP address
The basic procedure for deploying a MySQL database on a Linux ECS instance is as follows:

Step 1: Prepare the environment

Connect to your ECS instance. For more information, see Connect to a Linux instance by using an SSH key pair or Connect to a Linux instance by using a password.

Step 2: Install MySQL

Perform the following operations to install MySQL:

  1. Run the following command to update the YUM repository:
    rpm -Uvh  http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
  2. Run the following command to install MySQL:
    yum -y install mysql-community-server
  3. Run the following command to view the MySQL version:
    mysql -V
    MySQL is installed if the following result is returned:
    mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Step 3: Configure MySQL

Perform the following operations to configure MySQL:

  1. Run the following command to start MySQL:
    systemctl start mysqld
  2. Run the following command to configure MySQL to run upon system startup:
    systemctl enable mysqld
  3. Run the following command to view the /var/log/mysqld.log file and record the temporary password of the root user:
    # grep 'temporary password' /var/log/mysqld.log
    2019-04-28T06:50:56.674085Z 1 [Note] A temporary password is generated for root@localhost: 3w)WqGlM7-o,
    Note You need the temporary password when you reset the password for the root user.
  4. Run the following command to configure the security settings of MySQL:
    mysql_secure_installation

    The security settings of MySQL involve the following operations:

    1. Reset the password for the root user.
      Enter password for user root: #Enter the temporary password for the root user that you previously obtained.
      The 'validate_password' plugin is installed on the server.
      The subsequent steps will run with the existing configuration of the plugin.
      Using existing password for root.
      Estimated strength of the password: 100 
      Change the password for root? (Press y|Y for Yes, any other key for No) : Y #Specify whether to change the password of the root user. Press the Y key.
      New password: #Enter a new password that must be 8 to 30 characters in length. The password must contain uppercase letters, lowercase letters, digits, and special characters. Special characters include: ( ) ` ~ ! @ # $ % ^ & * - + = | { } [ ] : ; ' < > , . ? /
      Re-enter new password: #Re-enter the new password for confirmation.
      Estimated strength of the password: 100 
      Do you wish to continue with the provided password?( Press y|Y for Yes, any other key for No) : Y
    2. Enter Y to disable the anonymous user account.
      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 #Specify whether to delete anonymous users. Press the Y key.
      Success.
    3. Enter Y to deny remote access by the root user.
      Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y #Specify whether to disable remote logon as a root user. Press the Y key.
      Success.
    4. Enter Y to delete the test database and permissions to access this database.
      Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y #Specify whether to delete the test database and permissions to access this database. Press the Y key.
      - Dropping test database...
      Success.
    5. Enter Y to reload privilege tables.
      Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y #Specify whether to reload privilege tables. Press the Y key.
      Success.
      All done!
    For more information about security settings of MySQL, see MySQL documentation.

Step 4: Remotely access the MySQL database

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

  1. On the ECS instance, create an account for remote access to MySQL.
    1. Run the following command and enter the password for the root user to log on to MySQL:
       mysql -uroot -p
    2. Run the following commands in the sequence to create an account for remote logon to MySQL. In this example, the account is dms and the password is 123456.
      mysql> grant all on *.* to 'dms'@'%'IDENTIFIED BY '123456'; #Replace dms with root to enable remote logon with the root account.
      mysql> flush privileges;
      Note
      • We recommend that you use an account other than root to remotely log on to the MySQL database.
      • When you create an account, you need to replace the 123456 password with a password that meets the complexity requirements. It must be 8 to 30 characters in length. The password must contain uppercase letters, lowercase letters, digits, and special characters. Special characters include: ( ) ` ~ ! @ # $ % ^ & * - + = | { } [ ] : ; ' < > , . ? /
  2. Log on to the DMS console.
  3. In the left-side navigation pane, select User-created Databases (ECS, Internet).
  4. Click Add Database.
  5. Configure the database that you have created. For more information, see Configure user-created databases.
  6. Click Log On.
    After logging on, you can use the menu bar of DMS to create objects such as databases, tables, and functions. For more information, see Manage user-created databases hosted on ECS.