MySQL is a relational database management system commonly used in LAMP and LNMP website scenarios. This tutorial provides instructions on how to install, configure, and remotely access MySQL on a Linux Elastic Compute Service (ECS) instance.
Please note that the MySQL version used in this tutorial is for demonstration purposes only. The actual MySQL version may vary due to updates. The following are the installation paths for MySQL:
• Configuration file: /etc/my.cnf
• Data storage: /var/lib/mysql
• Command files: /usr/bin and /usr/sbin
Before deploying MySQL on an existing ECS instance or manually deploying it, ensure that the instance meets the following requirements:
• The instance has a public IP address or an elastic IP address (EIP).
• The 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 instance to allow traffic on ports 22, 80, 443, and 3306. For more information, see Add a security group rule.
1. Connect to an ECS instance. For more information, see Connect to an instance by using VNC.
2. Run the following command to update the YUM repository:
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm3. Run the following command to install MySQL:
sudo yum -y install mysql-community-server --enablerepo=mysql80-community --nogpgcheck4. Run the following command to check the version of MySQL:
mysql -VA command output similar to the following one indicates that MySQL is installed:
mysql Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)1. Run the following commands to enable MySQL to run on system startup:
sudo systemctl start mysqld
sudo systemctl enable mysqld2. Run the following command to obtain and record the initial password of the root user.
sudo grep 'temporary password' /var/log/mysqld.logA command output similar to the following one is returned:
2022-02-14T09:27:18.470008Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: r_V&f2wyu_vINote: 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 security settings for MySQL.
sudo mysql_secure_installationa) Reset the password of the root user based on the command prompts.
Note: When you enter a password, no command output is returned to maximize data security. You only need to enter the correct password and 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 the new password.
Re-enter new password: # Enter the new password again.
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 updating the password.
New password: # Enter the new password.
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 # Enter Y to use the new password. b) Delete the anonymous user as prompted.
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.c) 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.d) 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.e) 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 more information about the security settings of MySQL, see MySQL documentation.
We recommend that you use a non-root account to connect to MySQL databases. In this example, a user is created to connect to MySQL.
1. Connect to an ECS instance. For more information about connection methods, see Connection methods.
2. Run the following command and enter the password for the root user to log on to MySQL:
sudo mysql -uroot -p3. Run the following commands in sequence to create a user and allow remote access to MySQL by using the user.
In this example, the user is dmsTest, and the user password is Ecs@123****.
Note: 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:
exit5. Connect to MySQL by using the dmsTest user.
• We recommend that you use Data Management Service (DMS) provided by Alibaba Cloud to access MySQL databases. For more information, see Register an ApsaraDB instance.
• You can use a MySQL client to log on to MySQL to perform tests. The MySQL client can be MySQL Workbench or Navicat.
1. Open the Quick Configuration Template page to go to the Resource Orchestration Service (ROS) console.
2. In the upper-left corner of the top navigation bar, select a region.
3. On the Configure Template Parameters page, enter a stack name, and select or create an ECS instance. To create an ECS instance, you must specify the zone, instance type, system disk category, and password for the instance.
Note: 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 the information and click Create. When the Status on the Stack Information tab changes from Creating to Created, the MySQL database is deployed.
6. Click the Outputs tab, and then copy the value that is displayed next to DbCommand. This value is the command that is run to log on to MySQL.
7. Log on to MySQL.
• We recommend that you use DMS provided by Alibaba Cloud to access MySQL databases. For more information, see Register an ApsaraDB instance.
• You can use a MySQL client to log on to MySQL to perform tests. The MySQL client can be MySQL Workbench or Navicat.
1. Connect to an ECS instance. For more information, see Connect to an instance by using VNC.
2. Change the CentOS 8 source 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 will be 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 @mysql4. Run the following command to check the version of MySQL:
mysql -V5. A command output similar to the following one indicates the MySQL version.
1. Run the following commands to enable MySQL to run on system startup:
sudo systemctl start mysqld
sudo systemctl enable mysqld2. Run the following command to configure security settings for MySQL.
sudo mysql_secure_installationa) 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 indicates weak, 1 indicates medium, and 2 indicates strong. We recommend that you use a strong password policy.
Please set the password for root here.
New password: # Specify a new password.
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 any other key for No) : y ## Enter Y to use the new password. b) Delete the anonymous user as prompted.
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.c) 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.d) 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.e) 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 more information about the security settings of MySQL, see MySQL documentation.
We recommend that you use a non-root account to connect to MySQL databases. In this example, a user is created to connect to MySQL.
1. Connect to an ECS instance. For more information about connection methods, see Connection methods.
2. Run the following command and enter the password for the root user to log on to MySQL:
sudo mysql -uroot -p3. Run the following commands in sequence to create a user and allow remote access to MySQL by using the user.
In this example, the user is dmsTest, and the user password is Ecs@123****.
Note: 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:
exit5. Connect to MySQL by using the dmsTest user.
• We recommend that you use DMS provided by Alibaba Cloud to access MySQL databases. For more information, see Register an ApsaraDB instance.
• You can use a MySQL client to log on to MySQL to perform tests. The MySQL client can be MySQL Workbench or Navicat.
2023 Gartner® Magic Quadrant™ for Cloud Database Management Systems
1,079 posts | 265 followers
FollowAlibaba Clouder - November 15, 2019
Alibaba Cloud Community - January 31, 2022
Alibaba Cloud Community - January 15, 2024
Alibaba Clouder - February 14, 2020
Alibaba Cloud Community - January 5, 2024
Alibaba Clouder - April 26, 2019
1,079 posts | 265 followers
Follow
ECS(Elastic Compute Service)
Elastic and secure virtual cloud servers to cater all your cloud hosting needs.
Learn More
Elastic High Performance Computing Solution
High Performance Computing (HPC) and AI technology helps scientific research institutions to perform viral gene sequencing, conduct new drug research and development, and shorten the research and development cycle.
Learn More
Alibaba Cloud Linux
Alibaba Cloud Linux is a free-to-use, native operating system that provides a stable, reliable, and high-performance environment for your applications.
Learn More
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn MoreMore Posts by Alibaba Cloud Community