Desired Outcome
OK -Understanding root and its password
-Understanding user account that has root privs within mysqladmin
-Understanding privs within mysqladmin and mysql
-Confirming that the sql server is created correctly re: ip address, etc
-Confirming the UbuntuServer is configured correctly before dealing with mysql
Issue
-I now have UbuntuServer 18.04.4 LTS running
-My account “paul” is part of sudoer – confirmed
-Within phpmyadmin, I have the default account of “myphpadmin”
-I’m noticing within phpmyadmin via browser, I don’t have enough rights
Changing the Root Password
-Within /etc/sudoers (where I had to do an su -, and enter root password), I see that root and paul are in there with full access
-This gives more info on rights, groups, etc — https://vitux.com/add-and-manage-user-accounts-in-ubuntu-18-04-lts/
-These are the instructions I’m referring to – https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04
Initial setup for mysql server
sudo mysql_secure_installation
Note: Entered password for root and still received, “Error: Access denied for user ‘root’@’localhost’
Note: I’m thinking there is an initial issue with the naming (ip address) of the server
Note: Maybe there is an issue with the password for root
XREF to https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-18-04 — I need to review how to configure the server manually
Logging into server – with SSH
-In a prior version of UbuntuServer (because I upgraded), I already configured for SSH. That means I should have the private key for the root account
-I know the IP address — 192.168.1.10. I remember also configuring for static IP
-Somehow, I cannot login as root, but I can login as paul – this was originally my alternative account. I’m sure that the paul account is a superuser because I can do sudo and su – on it
-This is within the /etc/sudoers file:
# User privilege specification
root ALL=(ALL:ALL) ALL
paul ALL=(ALL:ALL) ALL
Setting up firewall
ufw app list — this returns the apps that have a profile set within ufw
Available applications:
Apache
Apache Full
Apache Secure
OpenSSH
To ensure the ufw fireall will accept SSH
ufw allow OpenSSH
Returns:
Rules updated
Rules updated (v6)
To enable the firewall
ufw enable
Note: Returns with Command may disrupt existing ssh connections
Returns: Firewall is active and enabled on system startup
To find status of ufw:
ufw status (Note: Right now it’s disabled)
Returns action ALLOW from Anywhere for OpenSSH
Configuring mySQL
sudo mysql_secure_installation
Error: Access denied for user ‘root’@’localhost’ (using password: YES)
Logging in as root to 127.0.0.1
ssh root@127.0.0.1
Note: ECDSA key fingerprint is SHA256xxxx
Note: This isn’t the answer because not accepting password for paul and root
Confirming mySQL is running
systemctl status mysql.service
Active: active (running) since Sun 2020-02-02
sudo mysqladmin -p -u root version
Note: mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’
Review the HOSTS file
127.0.0.1 localhost
127.0.1.1 ubuntusec
Note: I’m wondering if I need to have my static IP address for server here
Note: The file /etc/hosts and /etc/hosts.allow has nothing to do with which IP MySQL binds to.
Make change to /etc/mysql/my.cnf
Comment out this line — bind-address = 127.0.0.1
Note: That will make it bind to all available IP’s.
Restarted MySQL server – no difference
NOTE: I reversed this, as phpmyadmin broke
Modifying another file re: bind-address
/etc/mysql/mysql.conf.d/mysqld.cnf
Note: Also — systemctl restart mysql.service
NOTE: I reversed this, as phpmyadmin broke
Running netstat
sudo netstat -tap | grep mysql
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.53:domain 0.0.0.0:* LISTEN 753/systemd-resolve
tcp 0 0 0.0.0.0:ssh 0.0.0.0:* LISTEN 1588/sshd
tcp 0 0 192.168.1.10:ssh 192.168.1.3:51434 ESTABLISHED 18679/sshd: paul [p
tcp6 0 0 [::]:mysql [::]:* LISTEN 19006/mysqld
tcp6 0 0 [::]:http [::]:* LISTEN 8820/apache2
tcp6 0 0 [::]:ssh [::]:* LISTEN 1588/sshd
Issue with Firewall
Had a hard time accessing myphpadmin web-page, so I had reversed out the bind commands in my.cnf. Now I find that by disabling the UFW (firewall), that worked. Somethings up with the firewall settings
Overcoming this error – error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
root@ubuntusec:~# mysql -u root
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
Stop the service/daemon of mysql running
root@ubuntusec:~# service mysql stop
Start mysql without any privileges using the following option; This option is used to boot up and do not use the privilege system of MySQL.
mysqld_safe –skip-grant-tables &
At this moment, the terminal will seem to halt. Let that be, and use new terminal for next steps.
enter the mysql command prompt
mysql -u root
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
NOTE: Possibly because sql server not running – we had stopped the service/daemon
service mysql start
mysql – root
Note: Still have issues
RESOLVED!!!
I had documented what the root password was as this server was formerly used as a test for ZoneMinder. Now I can continue on with myphpadmin and edit user accounts for databases. Cool!!!!!
Cleaning my installation up re: SSH, now I know the root password
https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-18-04
Things I need to have in order to follow these setup instructions:
IP address of server – 192.168.1.10
Private key of the root users account (because I configured SSH a while back)
I entered: ssh root@192.168.1.10
The password I entered didn’t work, which makes me now wonder if I only have a root password configured within sql – MySQL Administrative “root” user. Note: Still a bit puzzled about this
Decision, for now
-My primary focus is getting to learn MySQL
-It’s weird that I have to have ufw disabled, but that’s because I need to resolve the actual password for root – which will take more time
-I’m now able to access myphpadmin with the administrative root password, so that’s good enough for now
-Will circle back later to resolve the root password and also the ufw firewall