MySQL: instalasi ubuntu 20.04 dan ubah auth mode
Sumber: https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04
Introduction
MySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It implements the relational model and uses Structured Query Language (better known as SQL) to manage its data.
This tutorial will go over how to install MySQL version 8 on an Ubuntu 20.04 server. By completing it, you will have a working relational database that you can use to build your next website or application.
Prerequisites To follow this tutorial, you will need:
One Ubuntu 20.04 server with a non-root administrative user and a firewall configured with UFW. To set up, follow our initial server setup guide for Ubuntu 20.04. Step 1 — Installing MySQL On Ubuntu 20.04, you can install MySQL using the APT package repository. At the time of this writing, the version of MySQL available in the default Ubuntu repository is version 8.0.19.
To install it, update the package index on your server if you’ve not done so recently:
sudo apt update Then install the mysql-server package:
sudo apt install mysql-server This will install MySQL, but will not prompt you to set a password or make any other configuration changes. Because this leaves your installation of MySQL insecure, we will address this next.
Step 2 — Configuring MySQL For fresh installations of MySQL, you’ll want to run the DBMS’s included security script. This script changes some of the less secure default options for things like remote root logins and sample users.
Run the security script with sudo:
sudo mysql_secure_installation This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options. The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the strength of your MySQL password.
If you elect to set up the Validate Password Plugin, the script will ask you to choose a password validation level. The strongest level — which you select by entering 2 — will require your password to be at least eight characters long and include a mix of uppercase, lowercase, numeric, and special characters:
Output Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: Y
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
Regardless of whether you choose to set up the Validate Password Plugin, the next prompt will be to set a password for the MySQL root user. Enter and then confirm a secure password of your choice:
Output Please set the password for root here.
New password:
Re-enter new password: If you used the Validate Password Plugin, you’ll receive feedback on the strength of your new password. Then the script will ask if you want to continue with the password you just entered or if you want to enter a new one. Assuming you’re satisfied with the strength of the password you just entered, enter Y to continue the script:
Output 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 From there, you can press Y and then ENTER to accept the defaults for all the subsequent questions. This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so that MySQL immediately respects the changes you have made.
Note that even though you’ve set a password for the root MySQL user, this user is not configured to authenticate with a password when connecting to the MySQL shell. If you’d like, you can adjust this setting by following Step 3.
Step 3 — (Optional) Adjusting User Authentication and Privileges In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) to access the user.
In order to use a password to connect to MySQL as root, you will need to switch its authentication method from auth_socket to another plugin, such as caching_sha2_password or mysql_native_password. To do this, open up the MySQL prompt from your terminal:
sudo mysql Next, check which authentication method each of your MySQL user accounts use with the following command:
SELECT user,authentication_string,plugin,host FROM mysql.user; Output +------------------+------------------------------------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+------------------------------------------------------------------------+-----------------------+-----------+ | debian-sys-maint | $A$005$lS|M#3K #XslZ.xXUq.crEqTjMvhgOIX7B/zki5DeLA3JB9nh0KwENtwQ4 | caching_sha2_password | localhost | | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | root | | auth_socket | localhost | +------------------+------------------------------------------------------------------------+-----------------------+-----------+ 5 rows in set (0.00 sec) In this example, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run an ALTER USER statement to change which authentication plugin it uses and set a new password.
Be sure to change password to a strong password of your choosing, and be aware that this command will change the root password you set in Step 2:
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password'; Note: The previous ALTER USER statement sets the root MySQL user to authenticate with the caching_sha2_password plugin. Per the official MySQL documentation, caching_sha2_password is MySQL’s preferred authentication plugin, as it provides more secure password encryption than the older, but still widely used, mysql_native_password.
However, some versions of PHP don’t work reliably with caching_sha2_password. If you plan to use this database with a PHP application such as phpMyAdmin, you may want to set root to authenticate with mysql_native_password instead:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:
FLUSH PRIVILEGES; Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:
SELECT user,authentication_string,plugin,host FROM mysql.user; Output +------------------+------------------------------------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+------------------------------------------------------------------------+-----------------------+-----------+ | debian-sys-maint | $A$005$lS|M#3K #XslZ.xXUq.crEqTjMvhgOIX7B/zki5DeLA3JB9nh0KwENtwQ4 | caching_sha2_password | localhost | | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | root | *3636DACC8616D997782ADD0839F92C1571D6D78F | caching_sha2_password | localhost | +------------------+------------------------------------------------------------------------+-----------------------+-----------+ 5 rows in set (0.00 sec) You can see in this example output that the root MySQL user now authenticates using caching_sha2_password. Once you confirm this on your own server, you can exit the MySQL shell:
exit Alternatively, some may find that it better suits their workflow to connect to MySQL with a dedicated user. To create such a user, open up the MySQL shell once again:
sudo mysql Note: If you have password authentication enabled for root, as described in the preceding paragraphs, you will need to use a different command to access the MySQL shell. The following will run your MySQL client with regular user privileges, and you will only gain administrator privileges within the database by authenticating:
mysql -u root -p From there, create a new user and give it a strong password:
CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password'; Then, grant your new user the appropriate privileges. For example, you could grant the user privileges to all tables within the database, as well as the power to add, change, and remove user privileges, with this command:
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION; Note that, at this point, you do not need to run the FLUSH PRIVILEGES command again. This command is only needed when you modify the grant tables using statements like INSERT, UPDATE, or DELETE. Because you created a new user, instead of modifying an existing one, FLUSH PRIVILEGES is unnecessary here.
Following this, exit the MySQL shell:
exit Finally, let’s test the MySQL installation.
Step 4 — Testing MySQL Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status.
systemctl status mysql.service You’ll see output similar to the following:
Output ● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2020-04-21 12:56:48 UTC; 6min ago
Main PID: 10382 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 1137)
Memory: 370.0M
CGroup: /system.slice/mysql.service
└─10382 /usr/sbin/mysqld
If MySQL isn’t running, you can start it with sudo systemctl start mysql.
For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect to MySQL as root (-u root), prompt for a password (-p), and return the version.
sudo mysqladmin -p -u root version You should see output similar to this:
Output mysqladmin Ver 8.0.19-0ubuntu5 for Linux on x86_64 ((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.
Server version 8.0.19-0ubuntu5 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 10 min 44 sec
Threads: 2 Questions: 25 Slow queries: 0 Opens: 149 Flush tables: 3 Open tables: 69 Queries per second avg: 0.038 This means MySQL is up and running.
Conclusion You now have a basic MySQL setup installed on your server. Here are a few examples of next steps you can take:
Set up a LAMP stack Practice running queries with SQL