I made a dump of a MySQL DB with mysqldump and am now trying to restore it to my DigitalOcean managed DB using the doadmin user and I get this error:
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
Line 18 of the SQL dump is this:
SET @@SESSION.SQL_LOG_BIN= 0;
I comment that line out by prepending # to that and then got an error on these lines:
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '060bc47a-c804-11ec-a3fa-b269afb00e36:1-4029'; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
Manually commenting out the lines is fine for MySQL dumps that are a few MB big but it’s a lot trickier with a multi GB DB dump.
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.