So I try to import sql file into rds (1G MEM, 1 CPU). The sql file is like 1.4G
mysql -h xxxx.rds.amazonaws.com -u user -ppass --max-allowed-packet=33554432 db < db.sql
It got stuck at:
ERROR 1227 (42000) at line 374: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
The actual sql content is:
/*!50003 CREATE*/ /*!50017 DEFINER=`another_user`@`1.2.3.4`*/ /*!50003 TRIGGER `change_log_BINS` BEFORE INSERT ON `change_log` FOR EACH ROW
IF (NEW.created_at IS NULL OR NEW.created_at = '00-00-00 00:00:00' OR NEW.created_at = '') THEN
SET NEW.created_at = NOW();
END IF */;;
another_user
is not existed in rds, so I do:
GRANT ALL PRIVILEGES ON db.* TO another_user@'localhost';
Still no luck.
This question is related to
mysql
amazon-web-services
amazon-rds
All the above solutions are fine. And here I'm gonna combine all the solutions so that it should work for all the situations.
For Linux and Mac
sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' file.sql
For Windows
download atom or notepad++, open your dump sql file with atom or notepad++, press Ctrl+F
search the word DEFINER, and remove the line DEFINER=admin
@%
(or may be little different for you) from everywhere and save the file.
As for example
before removing that line: CREATE DEFINER=admin
@%
PROCEDURE MyProcedure
After removing that line: CREATE PROCEDURE MyProcedure
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
When you restore backup, Make sure to try with the same username for the old one and the new one.
When we create a new RDS DB instance, the default master user is not the root user. But only gets certain privileges for that DB instance. This permission does not include SET permission. Now if your default master user tries to execute mysql SET commands, then you will face this error: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
Comment out or remove these lines
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 1;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
You can also ignore the errors by using the -f option to load the rest of the dump file.
mysql -f <REPLACE_DB_NAME> -u <REPLACE_DB_USER> -h <DB_HOST_HERE> -p < dumpfile.sql
* Answer may only be applicable to MacOS *
When trying to import a .sql file into a docker container, I encountered the error message:
Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Then while trying some of the other suggestions, I received the below error on my MacOS (osx)
sed: RE error: illegal byte sequence
Finally, the following command from this resource resolved my "Access Denied" issue.
LC_ALL=C sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' fileName.sql
So I could import into the docker database with:
docker exec -i dockerContainerName mysql -uuser -ppassword table < importFile.sql
Hope this helps! :)
Need to set "on" server parameter "log_bin_trust_function_creators" on server side. This one you can easily find on left side blade if it is azure maria db.
I commented all the lines start with SET
in the *.sql
file and it worked.
Another useful trick is to invoke mysqldump with the option --set-gtid-purged=OFF which does not write the following lines to the output file:
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
not sure about the DEFINER one.
If your dump file doesn't have DEFINER
, make sure these lines below are also removed if they're there, or commented-out with --
:
At the start:
-- SET @@SESSION.SQL_LOG_BIN= 0;
-- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
At the end:
-- SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
Problem: You're trying to import data (using mysqldump file) to your mysql database ,but it seems you don't have permission to perform that operation.
Solution: Assuming you data is migrated ,seeded and updated in your mysql database, take snapshot using mysqldump and export it to file
mysqldump -u [username] -p [databaseName] --set-gtid-purged=OFF > [filename].sql
From mysql documentation:
GTID - A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs.
--set-gtid-purged=OFF SET @@GLOBAL.gtid_purged is not added to the output, and SET @@SESSION.sql_log_bin=0 is not added to the output. For a server where GTIDs are not in use, use this option or AUTO. Only use this option for a server where GTIDs are in use if you are sure that the required GTID set is already present in gtid_purged on the target server and should not be changed, or if you plan to identify and add any missing GTIDs manually.
Afterwards connect to your mysql with user root ,give permissions , flush them ,and verify that your user privileges were updated correctly.
mysql -u root -p
UPDATE mysql.user SET Super_Priv='Y' WHERE user='johnDoe' AND host='%';
FLUSH PRIVILEGES;
mysql> SHOW GRANTS FOR 'johnDoe';
+------------------------------------------------------------------+
| Grants for johnDoe |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `johnDoe` |
| GRANT ALL PRIVILEGES ON `db1`.* TO `johnDoe` |
+------------------------------------------------------------------+
now reload the data and the operation should be permitted.
mysql -h [host] -u [user] -p[pass] [db_name] < [mysql_dump_name].sql
For importing database file in .sql.gz
format, remove definer and import using below command
zcat path_to_db_to_import.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -u user -p new_db_name
Earlier, export database in .sql.gz format using below command.
mysqldump -u user -p old_db | gzip -9 > path_to_db_exported.sql.gz;
Import that exported database and removing definer using below command,
zcat path_to_db_exported.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -u user -p new_db
Just a MacOS extra update for hjpotter92 answer.
To make sed
recognize the pattern in MacOS, you'll have to add a backslash before the =
sign, like this:
sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' file.sql
Statement
DEFINER=
username
@`%
is an issue in your backup dump.
The solution that you can work around is to remove all the entry from sql dump file and import data from GCP console.
cat DUMP_FILE_NAME.sql | sed -e 's/DEFINER=
<username>
@%
//g' > NEW-CLEANED-DUMP.sql
Try importing new file(NEW-CLEANED-DUMP.sql).
Source: Stackoverflow.com