Install MySQL on Ubuntu

MySQL is an open source database management system, usually installed as part of the most popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl), which manages data through relational databases and SQL.

MySQL is relatively easy to install, requiring only a few simple steps, but some of its configurations are tedious. The following uses Ubuntu 18.04 (similar to other versions) as an example to describe how to install and configure the MySQL database.

1. The installation of MySQL

In Ubuntu, only the latest version of MySQL is included in the APT package repository by default.

To install it, update the package index on the server with APT:

$ sudo apt update
Copy the code

Then install the default MySQL package:

$ sudo apt install mysql-server
Copy the code

This step will not do any configuration related prompts (such as setting password), because it will make MySQL installation insecure, we will solve this problem in the next step.

2. Configure the MySQL

After installing MySQL, you should run the included security script:

$ sudo mysql_secure_installation
Copy the code

This will help us do some routine security Settings through a series of tips:

Focusing on the first tip, this will ask us if we want to set up a validation password plug-in that can be used to test the strength of MySQL passwords.

3. Change the user authentication mode

If you log in to the MySQL terminal, you cannot use the password for authentication:

$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Copy the code

This is because in MySQL 5.7 and later, the root user is set to be authenticated with the auth_socket plug-in (instead of a password) by default, mainly for database security reasons.

That being said, occasionally an external program is needed to access it, which can be troublesome. To enable user root to connect to MySQL using password, open MySQL prompt on terminal:

$ sudo mysql
Copy the code

Then run the following command to check the authentication mode of each user in MySQL:

mysql> SELECT user, authentication_string, plugin, host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *ABA968D18E3A0B6DEB02F9D5FBDA21415A86977B | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
Copy the code

The root user is authenticated by auth_socket. Now run the following command to change the authentication mode to password authentication (that is: mysql_native_password) :

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Copy the code

Note: It is important to set a strong password (” 123456 “here is only for testing purposes), this will change the password set in Step 2.

Then check the root user authentication mode:

mysql> SELECT user, authentication_string, plugin, host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *ABA968D18E3A0B6DEB02F9D5FBDA21415A86977B | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
Copy the code

SQL > alter database; exit database;

mysql> exit
Copy the code

Try again with root as password:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.729.-0ubuntu018.. 041. (Ubuntu)

Copyright (c) 2000.2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
Copy the code

It can connect normally, ha ha!

4. Configure remote access

By default, MySQL only listens for connections to localhosts. To enable remote connections, do the following.

MySQL > alter database mysqld. CNF

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
Copy the code

Comment out the following configuration line:

bind-address          = 127.0. 01.
Copy the code

When done, save and exit!

Alter host from user; rename ‘localhost’ to ‘%’;

$ mysql -u root -p

mysql> use mysql;
mysql>
mysql> update user set host = The '%' where user = 'root';
mysql>
mysql> grant all on *.* to root@The '%' identified by '123456' with grant option;
mysql>
mysql> flush privileges;  # refresh permission
mysql>
mysql> exit
Copy the code

Run the following command to restart the mysql service:

$ sudo systemctl restart mysql
Copy the code

Now you can connect to the MySQl database remotely:

MySQL installation is complete!

Install MySQL on Ubuntu