This article comes from Alibaba cloud official mirror: Alibaba open source mirror - OPSX mirror - Alibaba cloud developer community ]

Original link: Manual deployment of MySQL database in CentOS - Alibaba cloud developer community

1, Preconditions

• Registered alicloud account.
• If you use ECS in mainland China, please ensure that your account has completed real name authentication.
• An ECS instance has been created. See for detailed steps Create an instance using the wizard.

2, Background information

This tutorial uses the following example specifications and software versions in the sample steps. In actual operation, your software version shall prevail.

• Example specification: ECs c6. Large (2 vCPU, 4 GiB memory)
• Operating system: common image CentOS 7.2 64 bit
• MySQL: 5.7.26
• Database port: 3306

Note: you need to add rules in the security group entry direction used by the ECS instance and release port 3306. For specific steps, see Add security group rule.

3, Operation steps

Step 1: prepare the environment

Connect your ECS instance remotely. For details, see Connecting to a Linux instance using an SSH key pair or Connect to a Linux instance using user name and password authentication.

Step 2: install MySQL

1. Run the following command to update the YUM source. (click) on trial)

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

mysql -V

The returned results are as follows, indicating that MySQL installation is successful.

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 start the MySQL service.

systemctl start mysqld

2. Run the following command to set MySQL service startup and self startup.

systemctl enable mysqld

3. Run the following command to view / var / log / mysqld Log file to obtain and record the initial password of 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 this initial password will be used when configuring MySQL security in the next step.

4. Run the following command to configure MySQL security.

mysql_secure_installation

• Reset the password of the root user.

Enter password for user root: #Enter the initial password of the root user 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 #Whether to change the root user password, enter Y
New password: #Enter a new password with a length of 8 to 30 characters. It must contain both uppercase and lowercase English letters, numbers and special symbols. Special symbols can be()` ~!@#$%^&*-+=|{}[]:;'<>,.?/
Re-enter new password: #Enter the new password again
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 #To continue, enter Y

• Delete 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  #To delete anonymous users, enter Y
Success.

• Remote login of root account is prohibited.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y #To disable root remote login, enter Y
Success.

• Delete the test library and access rights to the test library.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y #Whether to delete the test library and access rights to it, enter Y
- Dropping test database...
Success.

• Reload the authorization table.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y #Whether to reload the authorization table, enter Y
Success.
All done!

For more details on security configuration, see MySQL official documents.

Step 4: remote access to MySQL database

You can use the database client or the Data Management Service DMS (Data Management Service) provided by alicloud to remotely access the MySQL database. This section takes DMS as an example to introduce the operation steps of remotely accessing the MySQL database.
1. On the ECS instance, create an account to log in to MySQL remotely.

• After running the following command, enter the password of root user to log in to MySQL.

mysql -uroot -p

• Run the following commands in sequence to create an account for remote login to MySQL. The example account is dms and the password is 123456.

mysql> grant all on *.* to 'dms'@'%'IDENTIFIED BY '123456'; #Using root to replace dms can be set to allow remote login of root account.
mysql> flush privileges;

explain

• It is recommended that you use a non root account to log in to the MySQL database remotely.
• When you actually create an account, you need to replace 123456 with a password that meets the requirements: it is 8 to 30 characters long and must contain both uppercase and lowercase English letters, numbers and special symbols. Special symbols can be () ~! @#$%^&*-+=| {}[]:;‘<>,.?/`.

1. Sign in Data management console.
2. In the left navigation bar, select self built database (ECS, public network).
3. Click new database.
4. Configure self built database information. For configuration details, see Manage ECS instance self built database.
5. Click login. After successful login, you can use the menu bar function provided by DMS to create databases, tables, functions, etc.