Windows MySQL 5.5 Cannot connect to localhost: ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

Question

I can ping successfully from my Windows command line to localhost and to 127.0.0.1, but attempts to connect with mysql.exe fail...

I found similar errors but their suggested solutions don't work/apply...

https://stackoverflow.com/questions/19101243/error-1130-hy000-host-is-not-allowed-to-connect-to-this-mysql-server

Host is not allowed to connect to MySQL

Host not Allowed Connectivity to MySQL Server

I'm using a Windows 7 WAMP environment (an old Zend installation) meaning its all local (connections made from localhost/127.0.0.1). All worked fine last Friday when I did an import of a database, today, I get the cannot connect error. I did not overwrite mysql database (or at least, i am pretty sure I did not), so the error has taken me a little by surprise.

C:\Backups\Daily\201607>ping /n 1 localhost

Pinging Silver [::1] with 32 bytes of data:
Reply from ::1: time<1ms

Ping statistics for ::1:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\Backups\Daily\201607>ping /n 1 127.0.0.1

Pinging 127.0.0.1 with 32 bytes of data:
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128

Ping statistics for 127.0.0.1:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\Backups\Daily\201607>mysql --user=root 
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

C:\Backups\Daily\201607>mysql --user=root --host=localhost 
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

My partial extract from my.ini file:

[client]
port=3306
#
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files (x86)/Zend/MySQL55/"
#Path to the database root
datadir="C:/Program Files (x86)/Zend/MySQL55/Data/"
# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server = utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I'm curious on what went wrong - I suspect I'm responsible for it but lack a history file to examine the error of my ways.

Can anyone advise how I can fix and reconnect?

Answer 1

Short answer: I recovered from backup

During my work day, I do perform several mysqldumps of my work database and mysql. But I could not perform an import since it would fail with the same Error 1130.

I stopped the db, I moved my data/mysql directory out of the way, and then dropped in my March 1st data/mysql. I restarted the database and found I could connect and perform selects from within MySQL Workbench and the DOS command line.

I performed a reboot, restarted the database, and all good.

My backup batch file (copied below) performs a dump of my work database (wipdb) to wipdb.sql and mysql database to mysql.sql

Note the WinRAR command contains the word "PASSWORD" which is the plaintext password used to encrypt my backups. If you use the script, you might want to change it to something else...

The WinRAR files are saved to C:\Backups\Daily\BackupYYYYMMDD-XX.rar where YYYYMMDD is the date and XX is numeric incremented during ever iteration of the script during the day

PATH=%PATH%;C:\Progra~1\WinRar

call mysqldump.exe wipdb --single-transaction --default-character-set=utf8 --max_allowed_packet=1G --user=root > C:\Backups\Daily\wipdb.sql

call mysqldump.exe mysql --single-transaction --default-character-set=utf8 --max_allowed_packet=1G --user=root > C:\Backups\Daily\mysql.sql

cd C:\backups

rar.exe a C:\Backups\Daily\Backup.rar -pPASSWORD -ai -mt2 -m4 -t -agYYYYMMDD-NN -dh -ed -idcdp -r C:\Tools\ C:\Data\websites\  C:\Progra~2\Zend\*.conf C:\Progra~2\Zend\MySQL55\data\mysql C:\Progra~2\Zend\MySQL55\data\wipdb C:\Progra~2\Zend\*.ini C:\Windows\System32\drivers\etc\* C:\Backups\Keep\* C:\Backup\Daily\*.sql