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
Need help with Linux Server or WordPress? We can help!

Leave a Reply

Your email address will not be published. Required fields are marked *