MySQL ERROR Access denied you need SUPER privilege for this operation
WHen restoring a MySQL database, i get following error
root@ns3043014:~# mysql -u NEW_DB_USER -p'PASSOWRD_HERE' DB_NAME < BACKUP_FILE.sql ERROR 1227 (42000) at line 4382: Access denied; you need (at least one of) the SUPER privilege(s) for this operation root@ns3043014:~#
I opened the file in vim editor. On checking line 4382, i found following
/*!50013 DEFINER=`OLD_DB_USER`@`localhost` SQL SECURITY DEFINER */
This is because some stored procedures use old database user as definer. To fix you need to find and replace all entry for old username with new username.
In vim, i can run
:%s/OLD_DB_USER/NEW_DB_USER/g
Or delete the DEFINER line from SQL file.
sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' BACKUP_FILE.sql > BACKUP_FILE-modified.sql
Or
sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' BACKUP_FILE.sql
Now restore BACKUP_FILE-modified.sql with
mysql -u NEW_DB_USER -p'PASSOWRD_HERE' DB_NAME < BACKUP_FILE-modified.sql