0

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?

  • It's not a simple space issue? Your server is obviously quite old (5.5) so that this should suddenly start happening now is strange. BTW, i'd add ONLY_FULL_GROUP_BY to my sql-mode in my.ini if I were you. – Vérace Jul 4 '16 at 17:02
  • My laptop has 60gb free and the total size of my data directory which contains all tables and other database files is about a megabyte. Not being able to execute mysql from the command line or connect using the workbench leaves me limited. I've got some old backups of the mysql table which I can get my hands on in an hour - I might just unzip and copy them into place and see if that works. A database upgrade/re-install could bring other side effects so that is my last straw. – fiprojects Jul 4 '16 at 17:40
  • 1
    So, it's obviously not a space issue. Do your best with those old files, but from now on, I'd put a proper backup strategy in place if I were you - run mysqldump on a regular basis. Also, I'd upgrade if I were you - 5.6 (latest) or 5.7. – Vérace Jul 4 '16 at 18:33
  • I do mysqldump my mysql database several times during my work day (its a simple batch script that dumps mysql and work database to sql files, and winrars the sql files, and my php/js code in to a sequential file). I could not restore the file though as attempts to import resulted in the same ERROR 1130. – fiprojects Jul 4 '16 at 20:57
  • Can you try to restore your backups on another machine? If so, unintall 5.5, install 5.7 and use your dump to restore a new database. These strange things happen sometimes with servers that have been around for a while. – Vérace Jul 4 '16 at 21:07
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.