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

Related configuration

This topic is based on the following software:
  • Operating system: public image for CentOS 7.2 64-bit
  • MySQL: 5.7.26
This topic is based on an Elastic Compute Service (ECS) instance with the following specification:
  • CPU: 2 vCPUs
  • Memory: 4 GiB
  • Network type: VPC
  • IP address: public IP address

Prerequisites

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.

Procedure

  1. Prepare the environment.
  2. Install MySQL.
  3. Configure MySQL.
  4. Remotely access MySQL.

Step 1: Prepare the environment

  1. Log on to the 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

  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 version of MySQL. 
    mysql -V
    MySQL is successfully installed if the following is returned. 
    mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Step 3: Configure MySQL

  1. Run the following command to launch MySQL. 
    systemctl start mysqld
  2. Run the following command to enable MySQL to run at boot time. 
    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 steps.
    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
New password: # Enter a new password, that is 8 to 30 characters in length. It must contain uppercase and lowercase letters, digits, and special characters. The following special characters are allowed: ( ) ` ~ ! @ # $ % ^ & * - + = | { } [ ] : ; ' < > , . ? /
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
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
Success.
    4. Enter Y to remove the test database and access permissions to this database. 
      Remove test database and access to it? (Press y|Y for Yes, any other key for No): Y
- 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
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 Service (DMS) provided by Alibaba Cloud to remotely access the MySQL database. In this topic, we take DMS as an example to describe 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 order described here to create an account for remote logon to MySQL. In this example, we use dms as the account name and 123456 as the password. 
      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 a non-root account to remotely log on to the MySQL database instead of using the root account.
      • When you create an account, you need to replace the password 123456 with a password that meets the security strength requirements. It must be 8 to 30 characters in length and contain uppercase and lowercase letters, digits, and special characters. The following special characters are allowed: ( ) ` ~ ! @ # $ % ^ & * - + = | { } [ ] : ; ' < > , . ? /.
  2. Log on to the Data Management Service 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 databases, tables, functions, and other objects. For more information, see Manage user-created databases that are hosted on ECS.