1.Installing MySQL
sudo apt update
sudo apt install mysql-server
2.Configuring MySQL
sudo mysql_secure_installation
In order to use a password to connect to MySQL as root, you will need to switch its authentication method from auth_socket
to 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 |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | | auth_socket | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
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 |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
ทีนี้ก็จะ login ด้วย $ mysql -u root -p
ได้ละ
3.Adjusting User Authentication and Privileges
$ mysql -u root -p
mysql> GRANT ALL ON *.* TO 'sammy'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
4.Testing MySQL
$ systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2020-02-06 15:32:25 +07; 17min ago
Main PID: 3831 (mysqld)
Tasks: 28 (limit: 4915)
CGroup: /system.slice/mysql.service
└─3831 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
ถ้า mysql ไม่ start ให้ start ด้วย
$ sudo systemctl start mysql
ดูเวอร์ชันของ mysqladmin
$ sudo mysqladmin -p -u root version
Enter password:
mysqladmin Ver 8.42 Distrib 5.7.29, for Linux on x86_64
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 5.7.29-0ubuntu0.18.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 18 min 31 sec
Threads: 1 Questions: 21 Slow queries: 0 Opens: 113 Flush tables: 1 Open tables: 106 Queries per second avg: 0.018
5.ติดตั้ง Workbench
sudo apt install mysql-workbench