Richard Sevcik
asked on
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?
ASKER
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.
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.
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.
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.
ASKER
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...
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
DB migration to a inactive site may not be possible with Mysql Workbench
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.