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 remotely
access a MySQL database on a Linux ECS instance.
Background information
In this topic, the following instance type and software versions are used. The operations
may vary depending on the versions of your software.
- Instance type: ecs.c6.large (2 vCPUs and 4 GiB memory)
- Operating system: public image CentOS 7.2 64-bit
- MySQL: 5.7.26
In this example, the MySQL installation paths are as follows:
- Configuration file: /etc/my.cnf
- Data storage: /var/lib/mysql
- Command files: /usr/bin and /usr/sbin
- Port: 3306
Note You must add inbound rules to the security group associated with the ECS instance
and enable port 3306 for inbound traffic. For more information, see
Add security group rules.
Step 2. Install MySQL
- Run the following command to update the YUM repository:
rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
- Run the following command to install MySQL:
yum -y install mysql-community-server
- Run the following command to check the MySQL version:
The following command output indicates that MySQL is installed:
mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper
Step 3. Configure MySQL
- Run the following command to start MySQL:
- Run the following command to enable MySQL to start upon system startup:
- Run the following command to check the /var/log/mysqld.log file, and obtain and record the initial password of the root user:
grep 'temporary password' /var/log/mysqld.log
A command output similar to the following one is displayed:
2020-04-08T08:12:07.893939Z 1 [Note] A temporary password is generated for root@localhost: xvlo1lZs7>uI
Note The initial password is used in the subsequent security configurations for MySQL.
- Run the following command to make security configurations for MySQL:
mysql_secure_installation
- Reset the password of the root user.
Enter password for user root: #Enter the initial password that you obtained in the previous step.
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 #Enter Y to change the password of the root user.
New password: #Enter a new password that is 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: #Enter the new password again for confirmation.
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 continue.
- Delete anonymous users.
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 anonymous users.
Success.
- Deny remote access from the root user.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y #Enter Y to deny remote access from the root user.
Success.
- Delete the test database and access permissions on this database.
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 access permissions on this database.
- Dropping test database...
Success.
- Reload privilege tables.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y #Enter Y to reload privilege tables.
Success.
All done!
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, DMS is used to show how
to remotely access the MySQL database.
- On the ECS instance, create an account for remote logon to the MySQL database.
- Run the following command and enter the password of the root user to log on to the
MySQL database:
- Run the following commands in 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;
- Log on to the DMS console.
- In the left-side navigation pane, click User-Created Database (ECS, Internet).
- Click Add Database.
- Add the database that you have created. For more information, see Manage user-created databases hosted on an ECS instance.
- Click Log On.
After you are logged on, you can use the menu bar of DMS to create objects such as
databases, tables, and functions.