I've been able to now restore the account, but I logged in to phpMyAdmin and found that I am still missing permissions for 'Event' and 'Trigger'. When I tick these boxes and click 'Go' I get the following:
GRANT ALL PRIVILEGES ON * . * TO 'root'@'%' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
MySQL said: Documentation
#1045 - Access denied for user 'root'@'%' (using password: YES)
I am already logged in to phpMyAdmin with the root account and the correct password, so why does that fail to work?
Also, how can I update (ideally in phpMyAdmin) user host's for MySQL Accounts WITHOUT RECREATING THE ACCOUNT.
SHOW GRANTS for root@%:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'root'@'%'
I redacted the last section of the SHOW GRANTS as it included the account password, however the 'WITH GRANT OPTION' had no options specified so that was the end of the sentence.
EDIT: I just tried doing it myself with mysql.exe out of curiosity as to if phpMyAdmin was the trouble-maker here, and it made no difference - same error messages.