How to manage a super user privileges for mysql migration to a dedicated server.

I am unable to migrate a database from a share server from godaddy to my dedicated server Windows 2012 r2.  I have tried it from the plesk site by cloning it and I have tried with the migration wizard on Mysql workbench.  Both have stated that my user does not have super privileges.  How do I arrange that?
Richard SevcikdeveloperAsked:
Who is Participating?
 
Richard SevcikdeveloperAuthor Commented:
The user was correct for all the privileges with the workbench.  The db was placed on a site that was not associated with a DNS.  When I placed it with an active site it migrated successfully.  I believe it had to do with the association of a dns to an active site.  I don't know why or how but it worked.  My next task is to associated the other site with a DNS and make it active and transferring the db to be associated with the new site.   That I have done successfully in the past

Thanks for you help.
0
 
arnoldCommented:
Not clear what your setup is. Usually, when the db is on your database server, root gas full access

Grant is the command, but the user running it just have ..you may have to talk to the dedicated host to make ..
Are you using workbench, connecting to localhost with root as the username?

How are you getting the database from the shared server?

Are you getting a backup, are you able to access your shared hosted database using workbench on your local workstation?

Cpanel, if not mistaken provides a db backup option.
Using this, you could restore it on your dedicated server.

Puzzling thing, you are using a MySQL db, but elected win2k12 as the dedicated system.
0
 
Richard SevcikdeveloperAuthor Commented:
I am propagating my website from godaddy shared server to a godaddy dedicated server.

I have tried to use the workbench to mitigate the db to the dedicated server but an error was encountered (user ...did not have super privileges).  I have granted all privileges to the user that I am using with the plesk host edition that is being utilized with the workbench.

I tried using cpanel in the shared server to export and clone the db, but had the same user super privileges problem.

I am using a user and password from the workbench and am able to access my db from my local station.

I do have a backup on the shared host.

I have used the mysql db with win2k12 for the past 4 years...... Guess I didn't know any better and got use to it.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
arnoldCommented:
If you can access the shared MySQL server from the 2012 dedicate dserver,

Look at using the mysql command line tool, mysqldump -u user -P -H shareddbshost <your databasename> >yourdatabasename.sql

This will create the backup of your shared host database, that you can then restore on your dedicated server
mysql -u root -p <yourdayabsename.sql

Look at toad for MySQL, using this tool you can copy the db from one to the other.

If you are transitioning, you might transition the web site first to the dedicated, and have it query the shared. This way you can transition an item at a time versus trying to transition both at the same time...

Setting up db replication.......

A bit much all depends on how active and your available downtime for transition.
0
 
Richard SevcikdeveloperAuthor Commented:
When I import the backup dump from the shared server.  I get this.

Unable to import the MMHCRNA_ dump:

    Unable to restore database 'MMHCRNA_'
    ERROR 1227 (42000) at line 1335: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
    (Error code 22)


Thanks in advance...
0
 
arnoldCommented:
Which use are you using?
Localhost and root?

Grant or if you using workbench, administrator to  manage users, make sure you ... Often, you shoukd use root to import, while a less privileged user for access by the website.

Potentially, the user you are using lacks rights to create a database.

https://dev.mysql.com/doc/refman/5.7/en/grant.html
0
 
arnoldCommented:
Unusually, restoration of databases, are not reliant on DNS in any way.

Usually, relative references can be had that are not reliant on external resources.

Based on your comment, your Cpanel on the dedicated server references the name of your site explicitly.
glad to hear you have things working.
0
 
Richard SevcikdeveloperAuthor Commented:
DB migration to a inactive site may not be possible with Mysql Workbench
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.