mysql-server-setup-ubuntu v0.0.3

#SETTING MySql-Server On VPS Ubuntu Server Or Ubuntu Desktop

# MYSQL-SERVER-5.7
sudo apt-get install mysql-server

sudo mysql_secure_installation
# ...
#secure enough. Would you like to setup VALIDATE PASSWORD plugin?
#
Press y|Y for Yes, any other key for No: n
#Please set the password for root here.
#
New password: 
#
Re-enter new password: 
# ...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
#Success.
# ...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
# ...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
# - Dropping test database...
# ...
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
#Success.
#
#All done! 

nano /etc/mysql/my.cnf
#
# ...
#

# !includedir /etc/mysql/conf.d/
# !includedir /etc/mysql/mysql.conf.d/

sudo service mysql restart
sudo service mysql status
#● mysql.service - MySQL Community Server
#   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
#   Active: active (running) since Thu 2019-07-25 19:31:27 +07; 39min ago
#...

sudo mysql -u root -p
#Enter password: 
# ...

# MYSQL-SERVER-5 ================================================
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YourPassword' WITH GRANT OPTION;
#Query OK, 0 rows affected, 1 warning (0.00 sec)
UPDATE mysql.user SET authentication_string=PASSWORD('YourPassword'), plugin='mysql_native_password' WHERE user='root' AND host='localhost' AND plugin='auth_socket';
#Query OK, 1 row affected (0.00 sec)
SELECT User, Host, authentication_string, plugin FROM mysql.user;
#+------------------+-----------+-------------------------------------------+-----------------------+
#| User             | Host      | authentication_string                     | plugin                |
#+------------------+-----------+-------------------------------------------+-----------------------+
#| root             | localhost | *2DF3063C523DF16DEFB8A454F9DA045D92D509BA | mysql_native_password |
#| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
#| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
#| debian-sys-maint | localhost | *36344B5F51D5AC28082BFD53A501793F61389B3B | mysql_native_password |
#| root             | %         | *2DF3063C523DF16DEFB8A454F9DA045D92D509BA | mysql_native_password |
#+------------------+-----------+-------------------------------------------+-----------------------+
#5 rows in set (0.00 sec)
# ================================================================


# MYSQL-SERVER-8 =================================================
UPDATE mysql.user SET plugin='caching_sha2_password' WHERE user='root';
#Query OK, 0 rows affected (0.00 sec)
#Rows matched: 2  Changed: 0  Warnings: 0
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'newPassword';
#Query OK, 0 rows affected (0.01 sec)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
#Query OK, 0 rows affected (0.01 sec)

CREATE USER 'username'@'%' IDENTIFIED BY 'YourPassword';
#Query OK, 0 rows affected (0.00 sec)
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
#Query OK, 0 rows affected (0.01 sec)

# OR
CREATE USER 'root'@'%';
#Query OK, 0 rows affected (0.01 sec)
ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'newPassword';
#Query OK, 0 rows affected (0.01 sec)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
#Query OK, 0 rows affected (0.01 sec)

SELECT User, Host, authentication_string, plugin FROM mysql.user;
#+------------------+-----------+------------------------------------------------------------------------+-----------------------+
#| User             | Host      | authentication_string                                                  | plugin                |
#+------------------+-----------+------------------------------------------------------------------------+-----------------------+
#| username         | %         | $A$005$nfd7S?4Xd7VokyL8b8XzULpOkHXn.mo4dr9zIdWykQ5me0pwmQlkVB1         | caching_sha2_password |
#| root             | %         | $A$005$nfd7S?4Xd7VokyL8b8XzULpOkHXn.mo4dr9zIdWykQ5me0pwmQlkVB1         | caching_sha2_password |
#| debian-sys-maint | localhost | $A$005$▒sl%PKU,_K▒%el/ZW<> wVo6L0LT/oGtlexFeSdOrS6pAUTgp5nCFpcpIxwlqV7 | caching_sha2_password |
#| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
#| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
#| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
#| root             | localhost | $A$005$nfd7S?4Xd7VokyL8b8XzULpOkHXn.mo4dr9zIdWykQ5me0pwmQlkVB1         | caching_sha2_password |
#+------------------+-----------+------------------------------------------------------------------------+-----------------------+
#7 rows in set (0.00 sec)
# ===============================================================

exit
#Bye

sudo service mysql restart
#Connect your server_ip, port 3306, username: root, password: YourPassword

#Done!



# ===============================================================
# if used fireware
sudo ufw enable
#Firewall is active and enabled on system startup
sudo ufw status
#Status: active
#
#To                         Action      From
#--                         ------      ----
sudo ufw allow mysql
sudo ufw allow 3306/tcp
sudo ufw allow 3306