Introduction: MySQL is a relational database management system commonly used in Web site scenarios such as LAMP and LNMP. This article will show you how to install, configure, and remotely access MySQL databases on an ECS instance of the CentOS system.
Mirror download, domain name resolution, time synchronization Click Alibaba Open Source Mirror Station
1. Preconditions
- Aliyun account has been registered.
- If you use Cloud Server ECS in mainland China, make sure that the account has been authenticated by real name.
- An ECS instance has been created. For detailed steps see Use the wizard to create an instance.
2. Background information
The following sample specifications and versions of the software are used in the sample steps in this tutorial. In practice, please refer to your software version.
- Instance Specification: ecs.c6.large (2 vCPU, 4 GiB memory)
- Operating System: Public Mirror CentOS 7.2 64-bit
- MySQL: 5.7.26
- Database port: 3306
Explain You need to add rules to the direction of security group entry used by the ECS instance and release port 3306. For detailed steps, see Add Security Group Rule.
3. Operational steps
Step 1: Prepare the environment
Connect your ECS instance remotely. For specific operations, see Connecting Linux instances using SSH key pairs or Connect to a Linux instance using username password validation.
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 see the version number of MySQL.
mysql -V
The following results are returned to indicate that MySQL was installed successfully.
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 the MySQL service to start automatically.
systemctl enable mysqld
3. Run the following command to view the/var/log/mysqld.log file and get and record the root user's initial password.
# 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,
Explain The initial password will be used when configuring MySQL security next.
4. Run the following commands to configure MySQL security.
mysql_secure_installation
- Reset the password of the root user.
Enter password for user root: #Enter the root user initial password 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 that is 8 to 30 characters long and must contain both uppercase and lowercase letters, numbers, and special symbols. Special symbols can be()` ~!@#$%^&*-+=|{}[]:;'<>,.?/
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 #Continue operation, enter Y
- Delete anonymous user accounts.
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 #Whether to delete anonymous users, enter Y Success.
- Disable root account remote login.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y #Prohibit root remote login, enter Y Success.
- Delete the test library and access to it.
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 to it, enter Y - Dropping test database... Success.
- Reload the authorization form.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y #Whether to reload the authorization form, enter Y Success. All done!
For more details on security configuration, see MySQL Official Documentation.
Step 4: Remote access to MySQL database
You can access MySQL databases remotely using either the database client or the Data Management Service provided by Ali Cloud. This section takes DMS as an example to introduce the steps of remote access to MySQL database.
1. On the ECS instance, create an account to login to MySQL remotely.
- After running the following command, enter the root user's password to log in to MySQL.
mysql -uroot -p
- Run the following commands in turn to create accounts for remote login to MySQL. Sample account is dms with password 123 456.
mysql> grant all on *.* to 'dms'@'%'IDENTIFIED BY '123456'; #Replace dms with root, which can be set to allow root accounts to log on remotely. mysql> flush privileges;
Explain
- It is recommended that you login to the MySQL database remotely with a non-root account.
- When you actually create an account, you need to change 123456 to a password that meets the requirements: 8 to 30 characters in length, and must contain both uppercase and lowercase letters, numbers, and special symbols. Special symbols can be () ~!@#$%^&*-+=| {}[]:;'<>,.?/'.
- Sign in Data Management Console.
- In the left navigation bar, select the self-built library (ECS, public network).
- Click Add New Database.
- Configure self-built database information. For configuration details, see Manage ECS instance self-built database.
- Click Sign In. After successful login, you can use the menu bar functions provided by DMS to create databases, tables, functions, etc.
This article is from: CentOS System Manual Deployment of MySQL Database-Ali Cloud Developer Community