MySql Access Denied for User root without sudo

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) is not something you want to see when you try and login to mysql using the default root account with no password, especially when there is no password, on a fresh install!

So this was the problem I faced when I installed MySql on Ubuntu Server 18.04 TLS.

I SSH into my server since its a headless machine, just sat there with no peripherals connected at all. Just an ethernet cable and power cable. I was beginning to set up the machine as a local network server for a database heavy project I’m working on, so I installed MySql with sudo apt install mysql-server and immediately tried logging in to create a non-root user for my tasks:

mysql -u root

I typed. It threw me that error above. So I tried it with sudo and sure enough it worked. I’m not entirely sure why, but it did. Perhaps because my machine is referred to as the-lab and MySql just didn’t know who that was, because it isn’t localhost. Who knows.

Anyway, to remedy this particular issue I had to run a series of commands that removed the root user and added it again, after logging in with sudo. The steps were as follows, in case you find yourself in a similarly annoying situation:

  1. sudo mysql -u root
  2. drop user 'root'@'localhost'; This one is a bit scary because, well, you’re deleting the root user. But rest assured you can still continue to run commands after this, I promise.
  3. create user 'root'@'localhost' identified by '';
  4. grant all privileges on *.* to 'root'@'localhost' with grant option;
  5. flush privileges;

Now exitMySql with exit; or CTRL+D and try to log in again without sudo, job done!

Setting up Wake-on-lan on Ubuntu Server 18.04 LTS

Alright, where to begin?!

When I work from home, which is almost always, I often find myself working on various devices (MacBook, Dell laptop, desktop) and lately I’ve been working on a heavily database oriented project. Local dev with databases involved isn’t very friendly across multiple machines. You can add your code to VCS but including a database full of test data isn’t my idea of a fun time.

So, long story short, I re-purposed my linux desktop pc from its semi-neglected state to something more useful. A LAN MySql server. Of course, there’s the whole LAMP stack on there just for shigs, though. That, and ease of data management with PMA.

So what is the problem?

Well, I’m lazy. I’m a developer. I’m not going to go to where my server is located and turn it on every time I need to access it, no sir! Nor am I going to leave it on permanently, leeching my hard earned electricity from under my nose. That coupled with the fact that it’s next to my bed and when night sets in, it sounds like an engine next to my face. So, I need to be able to turn it on from my laptops. Easy stuff, wake-on-lan at your service.

The BIOS supports it, awesome, why not. So, I set the bios to listen for those magic packets and locate a tutorial to enable Ubuntu to cooperate. If you’re unaware, generally speaking, when an Operating System shuts down it also powers down the network adapter. This is bad for Wake-on-lan. If it’s not on, it’s not listening! The problem is, all the tutorials I’m finding are for Ubuntu pre-18, at which point Ubuntu dropped Upstart support.



The solution

The steps for preparing Ubuntu for WoL are as follows:

  1. Install ethtool with:
    sudo apt-get install ethtool
    – Ubuntu Server 18.04 LTS already has this installed
  2. Run:ifconfig
    to get the name of your network interface. Its worth noting here that all the guides I found say that “your network interface is most likely eth0”. The thing is, that’s no longer the case. Ubuntu has been transitioning to systemd since version 15.04 and part of that transition is the implementation of Predictable Network Interface Naming and so you might just find that your network interface name is something along the lines of:enp0s15
    Regardless, run: ifconfig
    to get your network interface name: 
  3. Run the command sudo ethtool -s interface wol g
    and this tells ethtool to tell the network card to listen out for magic packets. The g denotes that it should listen for magic packets. The problem is, this solution isn’t persistent between shutdowns so once the machine is powered down and then booted up again, the setting is lost. The general consensus is that you should create a system service that runs this command at start-up.
  4. On Ubuntu 18.04, you need to create a systemd service as opposed to enabling, creating and/or modifying rc.local as you would’ve done on previous versions. So, navigate to
    /etc/systemd/system
    and create a new file here called `wol.service` – you could be more descriptive but I prefer short filenames, I know what wol means here. I use vim for all my terminal based editing so I run this command: sudo vim wol.service
    to create and begin editing my service file.
  5. Now you need to populate your wol.service file with all it needs to run as a service. The most comprehensive documentation I found on this was provided by RedHat linux here. My file looks like this:
    [Unit]
    Description=Configure Wake-up on LAN
    
    [Service]
    Type=oneshot
    ExecStart=/sbin/ethtool -s enp35s0 wol g
    
    [Install]
    WantedBy=basic.target
    

    ExecStart provides the command to run. It’s important to note that this must be the absolute path to ethtool because services don’d do relative paths. Check the documentation if you want to understand the file structure more thoroughly.

  6. Once you’ve created your file, you need to add it to the systemd services so you should run systemctl daemon-reload to tell the system to update and/or locate new service files, like the one we just created.
  7. Run systemctl enable wol.service to enable the service to run on start up and,
  8. finally, systemctl start wol.service to fire up the service. This may be a redundant command but I’m not sure if step 7 does this automatically or not so there’s no harm in running it anyway.

And there we have it, if you’ve gone through all of that and enabled Wake-on-lan in BIOS, you should be able to power off your machine and then wake it up with a magic packet.


The Magic Packet

I opted to use a python script to send my magic packet, provided by San Bergmans, thank you! I had to modify it ever so slightly, maybe because I’m using it on a Mac, it expected a mac address as a parameter and it always took its own filename as the first parameter, which is obviously not a mac address, regardless of whether or not I actually provided it a parameter. I actually wen’t further than that and just dumped my mac address in file, skipped the parameter requirement and now it just sends a magic packet with my pre-defined mac address whenever I run the script. I literally type:
wol.py
in my mac terminal and the magic packet is sent, the server fires up and I’m good to go! (I have the script in a directory added to path, some advise against such a practice but convenience is what I thirst for!)