Forums

I have an older mysql dump file from my database I host here. I am trying to restore this but keep getting the same error:

04:22 ~ $ mysql -u drewxcom -h drewxcom.mysql.pythonanywhere-services.com 'drewxcom$rh' < db-backup.sql                                                                                                                                                                                
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

I'm not really sure what the SUPER privilege(s) is or how I can get them. The dump file is only a day old and was created using:

mysqldump -u drewxcom -h drewxcom.mysql.pythonanywhere-services.com 'drewxcom$rh' > db-backup.sql

I can restore this dump file locally but not on your site.

Any help would be most appreciated.

Drew

Are your credentials in ~/.my.cnf matching your real ones?

Yes. I double checked that the password there is correct. That's the only information listed in the .my.cnf file.

What appears at line 18 in your dump file? It's just a text file containing SQL statements, so you should be able to load it up in the editor.

Here is line 17 and 18 from the dump file:

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

I'm wondering if it's worth trying to reset my password? Not sure if that would help here with this but I'm out of ideas at this point.

I don't think it's a password problem; instead, it looks like the mysqldump command put some extra stuff in there that only makes sense if you're importing the data into a server as the superuser. We normally suggest adding the command-line switch --set-gtid-purged=OFF to your mysqldump commands, which generally suppresses that kind of thing.

I'd suggest just taking a copy of the dump file for safety, then deleting those two lines from the file; there will also likely be another SET @@SESSION.SQL_LOG_BIN later on, which can also be deleted.

Brilliant! I removed the lines you mentioned as well as this one:

SET @@GLOBAL.GTID_PURGED='';

Everything worked as expected. Thank you so much for your support. YOU ROCK!

No problem at all, glad to help :-D