Manually deploy MySQL database on Linux system

Posted by anujgarg on Mon, 21 Feb 2022 15:18:56 +0100

Introduction: MySQL is a relational database management system, which is often used to build websites such as LAMP and LNMP. This book will introduce you how to install, configure and remotely access MySQL database on ECS instance of Linux system.

For image download, domain name resolution and time synchronization, please click Alibaba open source mirror station

1, Preconditions

Please ensure that you have registered an alicloud account before proceeding. If you have not registered, please complete it first Account registration.
You have added rules in the security group entry direction used by the ECS instance and released port 3306. For specific steps, see Add security group rule.

2, Background information

This article uses the following versions of software in the sample steps. In actual operation, your software version shall prevail.

Operating system: common image CentOS 7.2 64 bit
MySQL: 5.7.26

This article uses the following configured ECS instances in the sample steps. In actual operation, please refer to your instance configuration.

CPU: 2 vCPU
Memory: 4 GiB
Network type: private network
IP address: public IP address

3, Installation steps

1. Prepare the environment

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

2. Install MySQL

Complete the following operations to install MySQL.

1) Run the following command to update the YUM source.

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

4) The returned results are as follows, indicating that MySQL is successfully installed.

mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

3. Configure MySQL

Complete the following operations to 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 / sqld / var 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: the initial password will be used when resetting the root user password in the next step.

4) Run the following command to configure MySQL security.

mysql_secure_installation

The security configuration includes the following five aspects:

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

Enter Y to delete the 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.

Enter Y to prohibit remote login of root account.

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

Enter Y to 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 its access rights, enter Y
- Dropping test database...
Success.

Enter Y to 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.

4. Remote access to MySQL database

You can use the database client or the Data Management Service DMS (Data Management Service) provided by Alibaba cloud to remotely access the MySQL database. This section takes DMS as an example to introduce the operation steps of remote access to 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 () ~! @#$%^&*-+=| {}[]:;‘<>,.?/`.

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

This article is transferred from: Manual deployment of MySQL database in Linux system - Alibaba cloud developer community

Topics: Linux Database MySQL

Programmer Think