Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Well I was planning an upgrade of some CMMS software that we are trialling , I did a mysqldump and then the usual import.

I got the following error:-

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

 

The steps that were taken before this were as follows, obviously the names have been changed to protect the innocent.

 

create database calemean_upgrade;

create user 'calemean_upgrade'@'localhost' identified by 'password';

create user 'calemean_upgrade'@'%' identified by 'password';

mysqldump -u root -p rootpassword -h host_name calemean_data > calemean.sql

mysql -u root -p rootpassword -h host_name calemean_upgrade < calemean.sql

There is and issue with the creation script and the user not having enough privileges.

 

There is a simple solution to this, your need to change the dump sql file.

 

sed -e 's/calemean_data/root/g' calemean.sql > calemean_new.sql

 

where calemean_data is the owner of the calemean tables and root is the root user of the Mysql database.

 

You will now be able to issue the command

mysql -u root -p rootpassword -h host_name calemean_upgrade < calemean_new.sql

 

This will create the tables and views.