MySQL is a relational database management system and is often used to build the LAMP or LNMP environment. This topic describes how to install, configure, and connect to a MySQL database on an Elastic Compute Service (ECS) instance that runs CentOS 8.

Prerequisites

An ECS instance is created. For more information, see Create an instance by using the wizard.

In this topic, an ECS instance that has the following configurations is used:
  • Instance type: ecs.c6.large
  • Operating system: CentOS 8.2 64-bit public image
  • Network type: Virtual Private Cloud (VPC). A public IP address is assigned to the instance.

Background information

In the example, MySQL 8.0.21 is deployed. The version of MySQL may vary based on the update of software repositories. The following MySQL installation paths are used:
  • Configuration file: /etc/my.cnf
  • Data storage: /var/lib/mysql
  • Command files: /usr/bin and /usr/sbin

Step 1: Install MySQL

  1. Connect to the ECS instance that runs CentOS 8.
  2. Change the CentOS 8 repository address.

    CentOS 8 has reached its end of life (EOL). In accordance with Linux community rules, all content has been removed from the following CentOS 8 repository address: http://mirror.centos.org/centos/8/. If you continue to use the default CentOS 8 repository configurations on Alibaba Cloud, an error is reported. To use specific installation packages of CentOS 8, change the CentOS 8 repository address. For more information, see Change CentOS 8 repository addresses.

  3. Run the following command to install MySQL:
    dnf -y install @mysql
  4. Run the following command to view the MySQL version:
    mysql -V
    A command output similar to the following one indicates the MySQL version. mysql 8.0.21

Step 2: Configure MySQL

  1. Run the following command to start MySQL and enable it to start on instance startup:
    systemctl enable --now mysqld
  2. Run the following command to check whether MySQL is started:
    systemctl status mysqld
    If MySQL is started, the command output contains Active: active (running).
  3. Run the following command to configure security settings for MySQL and set the password:
    mysql_secure_installation
    After you run the command, perform the following operations based on the command prompts:
    1. Enter Y and press the Enter key to make the configurations.
    2. Enter 2 as the password strength and press the Enter key.

      0 indicates a low password strength, 1 indicates a medium password strength, and 2 indicates a high password strength. We recommend that you use a high password strength.

    3. Enter a new password for MySQL and confirm it.

      In this example, the password is set to PASSword123!.

    4. Enter Y and press the Enter key to use the password.
    5. Enter Y and press the Enter key to delete anonymous users.
    6. Enter Y and press the Enter key to disallow the root account to connect to MySQL.
    7. Enter Y and press the Enter key to delete the test database and the access permissions on the test database.
    8. Enter Y and press the Enter key to reload privilege tables.

Step 3: Connect to the MySQL database

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

  1. Add an inbound rule to a security group of the ECS instance to allow traffic on the port required by MySQL.
    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 a security group rule.
  2. Create and configure an account to connect to MySQL on the instance.
    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 to connect to MySQL on the MySQL client and allow the instance to connect to MySQL by using this account.
      In this example, the account is named dms and the password is set to PASSword123!.
      create user 'dms'@'%' identified by 'PASSword123!';
      grant all privileges on *.* to 'dms'@'%'with grant option;
      flush privileges;
      Note When you create an account, the password must meet the following requirements: The password must be 8 to 30 characters in length and must contain uppercase letters, lowercase letters, digits, and special characters. Special characters include

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

  3. Connect to MySQL by using the dms account.
    • We recommend that you use Data Management Service (DMS) provided by Alibaba Cloud to connect to the MySQL database. For more information, see Register an ApsaraDB instance.
    • You can use connection tools such as MySQL Workbench and Navicat on your computer to connect to the MySQL database.