Need Help Restoring mysql DB

[roarkh]

I am trying to restore a backup of a mysql database and seem to be having problems and am wondering if someone can help me.

I have a mysql database I want to move from one site to another on the same directadmin server, I have tried these steps to make it happen.

1. I logged into the site with the existing database and backed it up through the DirectAdmin interface, that seems to work fine.

2. Then I logged into the new site and created a blank empty database

3. I then attempted to restore the backup up my database to the new blank one with the "Your current login information" button selected.

When I do this I receive the following error: "Unable to restore database xxxxx_wp : ERROR 1227 (42000) at line 955: Access denied; you need (at least one of) the SUPER privilege(s) for this operation"

I also tried change the user name to the username and password for the database I am restoring, that did not work either.

Can anyone tell me what I might be doing wrong?

Thanks in advance.

 

[roarkh]

I decided to try to do this through the command line but am still not getting the results I need.

I started by making a backup of the existing db using mysqldump

mysqldump -u olduser -p olddb > olddb.out

I then created a new empty database using the DirectAdmin Control panel and ran the following command

mysql -u newuser -p newdb < olddb.out

That throws the same error I got when trying to do it using the Control Panel (ERROR 1227 (42000) at line 955: Access denied; you need (at least one of) the SUPER privilege(s) for this operation)

After reading through some other posts regarding the issue it seemed that perhaps I needed to restore the database using the da_admin mysql user instead so I tried the following

mysql -u da_admin -p newdb < olddb.out

That seems to make it a bit further but I end up with a different error (ERROR 1356 (HY000) at line 993: View 'newdb' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them)

If anyone has any pointers that might help me resolve this issue I would greatly appreciate it.

 

[scsi]

If you're going to import/export the database as a user its just easiest to use phpmyadmin. I am not sure what your new error means. You might have to ask on a mysql forum. I always do my imports/exports through root mysql user though as you just posted. Are you sure your database is not corrupt at all? I would try a repair database before you export it. You can attempt to repair the database through the directadmin mysql tools.

 

[ditto]

I don't know if it helps, but I have noticed that when you export a database using DirectAdmin, it creates the file without file extension. So you might want to try to edit the file name and add the extenison ".sql" to the file. Also I agree with scsi, try export/import using phpMyAdmin instead.

 

[roarkh]

I am still working on this and appreciate both of your responses so far. While I have not tested whether the restored database is fully working yet I was finally able to restore the database without the error message, in order to accomplish that I did the following.

1. I granted the SUPER privilege to the new user using the mysql command line (so I could restore the database as the new user instead of the da_admin user):

GRANT SUPER ON *.* to newuser@'localhost';
FLUSH PRIVILEGES;

2. Near the end of the sql dump file there are a number of lines like this...

/*!50013 DEFINER=`oldlinuxuser`@`localhost` SQL SECURITY DEFINER */

I changed all of the "oldlinuxuser" lines to "newlinuxuser".

The database restored without error after that. I am not sure what those user references are for but they seem to reference the linux account associated with the database, not the mysql username for the database.

 

[AndyII]

one thing you want to do is strip (trim) all the header and footer remarks and any reference to the other user, and Yes use phpmyadmin to Export and Import the file if its big compress to a zip.
if its real huge use "BigDump" to populate the database.
I do this all the time, so if you want exactly the same data in each of the tables then leave that otherwise may want to trim any data thats not wanted, you know the comment that states "Dumping data for table `tableName`

PS what options did you select when exported? (using phpmyadmin is a cleaner export without DA's added info)

 

[Richard G]

I always use mysqldump, because some times we had so big databases, we got several timouts importing them with phpmyadmin, even when the timeouts were set to greater values.
Never had any problems using the mysqldump and mysql commands. However, you could run into problems in some cases when having different mysql versions.
I also trim stuff if possible and already change paths (like /home/user/domains/domain.com/public_html) to the new path before restoring. Same for ip addresses if present, saves a lot of work afterwards.