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 restartsudo 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 mysqlsudo ufw allow 3306/tcpsudo ufw allow 3306