MYSQL

So we decide to remove the mysql server and reinstall again, below are the step that we were done.

1. sudo apt-get remove --purge mysql*

2. sudo apt-get purge mysql*
   

3. sudo apt-get autoremove

4. sudo apt-get autoclean

5. sudo apt-get remove dbconfig-mysql

6. sudo apt-get dist-upgrade

https://linuxscriptshub.com/uninstall-completely-remove-mysql-ubuntu-16-04/

Ensure there are no .my.cnf file

sudo find / -name my.cnf

find all my.cnf file for mysql and rm -rf them

https://askubuntu.com/questions/760724/16-04-upgrade-broke-mysql-server#comment1290831_762432

netstat -tulnp | grep <port no>

Check to see if mysql default 3306 port is being listened

https://askubuntu.com/questions/538208/how-to-check-opened-closed-ports-on-my-computer

if it is find task uses 3306 and remove it

lsof -i :3306

kill task_id

https://www.binarytides.com/close-open-ports-manually-in-ubuntu/




Check mysql error log 

 /var/log/mysql/error.log

https://askubuntu.com/questions/538208/how-to-check-opened-closed-ports-on-my-computer

reinstall and set root password, and set login without sudo 

login mysql sudo mysql -u root -p (default password is empty)

UPDATE
  mysql.user
SET
  Password = PASSWORD('YOURNEWPASSWORD')
WHERE
  User = 'root';
FLUSH PRIVILEGES;
exit;

https://stackoverflow.com/questions/16556497/how-to-reset-or-change-the-mysql-root-password

now change to login mysql without sudo

Some systems like Ubuntu, mysql is using by default the UNIX auth_socket plugin.

Basically means that: db_users using it, will be "auth" by the system user credentias. You can see if your root user is set up like this by doing the following:

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

As you can see in the query, the root user is using the auth_socket plugin

There are 2 ways to solve this:

1. You can set the root user to use the mysql_native_password plugin
2. You can create a new db_user with you system_user (recommended)

Option 1:

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost

https://stackoverflow.com/questions/16556497/how-to-reset-or-change-the-mysql-root-password