All Products
Search
Document Center

Elastic Compute Service:Deploy MySQL on a Linux instance

Last Updated:Feb 08, 2024

MySQL is a relational database management system that is commonly used to build Linux, Apache, MySQL, and PHP (LAMP) or Linux, NGINX, MySQL, and PHP (LNMP) stacks. This topic describes how to install, configure, and remotely access MySQL on a Linux Elastic Compute Service (ECS) instance.

Background information

In this topic, the MySQL version that is used may be different from the version that is used in your actual scenarios due to MySQL updates. The following MySQL installation paths are used:

  • Configuration file: /etc/my.cnf

  • Data storage: /var/lib/mysql

  • Command files: /usr/bin and /usr/sbin

Prerequisites

When you use an existing ECS instance to deploy MySQL, make sure that the instance meets the following requirements:

  • The ECS instance is associated with an auto-assigned public IP address or an elastic IP address (EIP).

  • The ECS instance runs CentOS 7.x, CentOS 8.x, Alibaba Cloud Linux 2, or Alibaba Cloud Linux 3.

  • An inbound rule is added to the security groups of the ECS instance to open ports 22, 80, 443, and 3306. For more information, see Add a security group rule.

Alibaba Cloud Linux 2, Alibaba Cloud Linux 3, and CentOS 7.x

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 YUM repository:

    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
  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 enable MySQL to run on system startup:

    sudo systemctl start mysqld
    sudo systemctl enable mysqld
  2. 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.

  3. Run the following command to configure the security settings of MySQL:

    sudo mysql_secure_installation
    1. Reset the password of the root user based on the command prompts.

      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 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: # 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 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 information about the security settings of MySQL, see MySQL documentation.

Step 3: Connect to MySQL

We recommend that you use a non-root user to connect to MySQL. In this example, a user 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 a user and allow the user to remotely access MySQL.

    In this example, the user is named dmsTest and the user password is Ecs@123****.

    Important

    When you create a user, 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 must contain uppercase letters, lowercase letters, digits, and special characters. The following special characters are supported:

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

    # Create a user named dmsTest and grant the remote connection permissions to the user. 
    create user 'dmsTest'@'%' identified by 'Ecs@123****'; 
    # Grant all permissions on MySQL to the dmsTest user. 
    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 user.

    • 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.

CentOS 8.x

Quick deployment

  1. Log on to the Resource Orchestration Service (ROS) console and go to the Create Stack page.

  2. In the top navigation bar, select the region and resource group to which the resource belongs. 地域

  3. In the Configure Parameters step, enter a name for the stack and select or create an ECS instance.

    If you choose to create an ECS instance, configure the zone ID, instance type, system disk category, and password of the instance.

    Important

    The created instance runs a minor version of CentOS 8, uses the pay-as-you-go billing method, and has a pay-as-you-go EIP. For information about the billing of ECS and EIP, see ECS billing overview and EIP billing overview.

  4. In the MySQL section, configure the MySQL User Name and MySQL Password parameters.

  5. Confirm that the specified information is correct and click Create.

    When the value of the Status field on the Stack Information tab changes from Creating to Created, MySQL is deployed.

  6. Click the Outputs tab and copy the value that is displayed next to the DbCommand keyword.

    The value is the command that is run to log on to MySQL.

  7. Log on to MySQL.

    • We recommend that you use Alibaba Cloud 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.

Manual deployment

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. Change the CentOS 8 repository address.

    CentOS 8 reached end of life (EOL). In accordance with Linux community rules, all content was removed from the following CentOS 8 repository address: http://mirror.centos.org/centos/8/. If you continue to use the default CentOS 8 repository 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:

    sudo dnf -y install @mysql
  4. Run the following command to check the version of MySQL:

    mysql -V

    The following command output indicates the MySQL version.mysql 8.0.21

Step 2: Configure MySQL

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

    sudo systemctl start mysqld
    sudo systemctl enable mysqld
  2. Run the following command to configure the security settings of MySQL:

    sudo mysql_secure_installation
    1. Reset the password of the root user based on the command prompts.

      Press y | Y for Yes, any other key for No: y # Enter Y and press Enter to configure the settings. 
      
      There are three levels of password validation policy:
      
      LOW Length >= 8
      MEDIUM Length >= 8, numeric, mixed case, and special characters
      STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
      
      Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 # Select the password strength policy. 0 specifies weak, 1 specifies medium, and 2 specifies strong. We recommend that you use a strong password strength policy. 
      Please set the password for root here.
      
      New password: # Specify 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 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 information about the security settings of MySQL, see MySQL documentation.

Step 3: Connect to MySQL

We recommend that you use a non-root user to connect to MySQL. In this example, a user 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 a user and allow the user to remotely access MySQL.

    In this example, the user is named dmsTest and the user password is Ecs@123****.

    Important

    When you create a user, 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 must contain uppercase letters, lowercase letters, digits, and special characters. The following special characters are supported:

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

    # Create a user named dmsTest and grant the remote connection permissions to the user. 
    create user 'dmsTest'@'%' identified by 'Ecs@123****'; 
    # Grant all permissions on MySQL to the dmsTest user. 
    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 user.

    • 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.