MySQL GRANT oddities

The problem

Just yesterday after setting up a mysql slave I tried to switch the backups of the master to the slave, trying to reduce the load on the master. While trying the following command:

mysql> GRANT ALL ON *.* TO 'backupuser'@'localhost';

I got the following error:

 Access denied for user 'root'@'localhost' (using password: NO)

hmmm, quite curious since I was logged into the mysql server as root. After some googling on the subject I found a weird little problem with the tables between versions.

This error above only happened because I had upgraded the mysql server from a 5.0 to a 5.5 server. I had thought there wasn't much to this, I also changed the location of the data directory to be a new partition to handle the amount of data that I had.

The crux of the issue was that during the upgrade I moved the data directory contents as well. Meaning that the old tables were in place with the new server.

The Solution

What I didn't realize was that I needed to upgrade the mysql tables from the old version to the new. So with the following command:

shell> mysql_upgrade

I got the above error again. 

 Access denied for user 'root'@'localhost' (using password: NO)

Damn! But not to worry, with the above command is able to take  a username and password:

shell> mysql_upgrade -u root -p

Nice one I'm now in and it gives me a bunch of errors. Damn! Error code 13? Hold on that's a permissions error. I wonder if running it as the mysql user that has ownership of the data file tables will work:

shell> sudo su - mysql
shell> mysql_upgrade -u root -p

Nice, worked a charm this time.

I can now go back to my little grant that I was trying to do in the first place:

mysql> GRANT ALL ON *.* TO 'backupuser'@'localhost';

And I'm able to complete it. Nice one.