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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.