The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Moving MySQL databases from one account to another

Discussion in 'Database Discussions' started by pumpkinkid, Feb 4, 2010.

  1. pumpkinkid

    pumpkinkid Member

    Joined:
    Feb 1, 2010
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Is it possible to move a database from one cPanel account on one server to another account on the same server?

    Trying to do this without having to recreate the users.
     
  2. thewebhosting

    thewebhosting Well-Known Member

    Joined:
    May 9, 2008
    Messages:
    1,201
    Likes Received:
    1
    Trophy Points:
    38
    It is not possible to move the database from one cpanel account to another on the same server. You can try to take the backup of the database and manually create the database and database user on another account and restore the content of the database.
     
  3. pumpkinkid

    pumpkinkid Member

    Joined:
    Feb 1, 2010
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Yeah, I kinda figured I could do that... it just seemed kinda silly to me that I would destroy a database and its users to recreate it when essentially its already usable by the second account... (minus the fact that cpanel sees it as belonging to the first account.

    See, problem is I don't have the original password for the database... Normally not a problem, but Drupal has the password in a url, using html friendly text to store the password with special characters...

    You wouldn't happen to know how I can re-encode a new password so that I can just replace it in the settings file would you?

    Of course I could always just use plaintext...
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,449
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    I don't think he meant to destroy the original database. :p

    If you have access to phpMyAdmin on the account, assuming you do, you can backup the database from there. If it's a small database you can restore it yourself the same way on the other account, restoring to an exisiting database... that you create ahead of restoring the database you backed up.
    Make any sense? ;)

    Here's another idea you might like to look into:
    Password lost, can't login in my own site | drupal.org

    Never tried it myself but it seems straight forward enough.
     
  5. pumpkinkid

    pumpkinkid Member

    Joined:
    Feb 1, 2010
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Infopro,

    I appreciate the help, however, what I meant was that the database is already on the server I want it on... What I want to do is have it show under a different cPanel account.

    Being that the mysql server already has the database in place, it seems silly to me that cPanel wouldn't allow me to just associate the database with a different cPanel user.

    As for the drupal password, thanks, but my issue was related to the config file which uses a url to connect to the database:
    PHP:
    'mysql://user_name:user_pass@localhost/drupal_database';
    While I could just type in a new password in the section above... I normally use special characters such as !@&% ... these characters should look like d%7 in the above example... like when you have a filename with a blank space in the url...

    Any ideas?
     
  6. pumpkinkid

    pumpkinkid Member

    Joined:
    Feb 1, 2010
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Actually I think this solves my second problem

    URL Encoding

    it explains what I needed.

    Im going to try to hack around at the database and see if I can solve my first question
     
  7. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,449
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    I'm curious, why is the site using a URL to connect to a database on the same server?

    Backing up and restoring an SQL file from the old database to the new one using a different name can be done in a few seconds via shell.
     
  8. pumpkinkid

    pumpkinkid Member

    Joined:
    Feb 1, 2010
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    The answer to your question is... I have no idea... the way that the config file is setup is in that manner after the drupal installation is completed...

    As for your comment, correct me if I am wrong, but using shell would just re-create the database if my information is in that server anyways...

    I think I may need to describe my situation better...

    I used to have shared hosting (one cPanel account) through my old webhost. I had my own personal website, and within the same account had addon domains pointing to different folders for other customers of mine...

    Now I am using my own VPS and I am transferring all my content over as well as the additional domains...

    My first step included bringing the cpanel backup to transfer my websites to the vps server. This recreated my old cpanel account as I had it with my old host. Next I created separate cPanel accounts for the people I was hosting other sites for... This is where my database move comes in...

    The mysql server is localhost... meaning that regardless of cpanel, all my websites can reach this database provided they have the correct credentials... cpanel however has all the databases in my original account as opposed to their corresponding cpanel accounts...
     
  9. pumpkinkid

    pumpkinkid Member

    Joined:
    Feb 1, 2010
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Well, I should have listened to you, you were right, officially cPanel said pretty much the same thing:

    Figured I'd post and share that since it has the official solution to my problem.

    Thanks to both of you for the offered support!
     
  10. pumpkinkid

    pumpkinkid Member

    Joined:
    Feb 1, 2010
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Hmm, Strike that, going back and forward with cPanel support, I came across the solution.

    The way that cPanel associates MySQL users and databases is by the prefix the name has... for example cpaneluser_database and cpaneluser_dbuser.

    1. To move the databases around, simply locate the database:
      I used #locate databasename
    2. Stop MySQL
    3. Rename the folder the database is located in to reflect the new user
    4. Start MySQL

    The users work in the same fashion. So do:
    RENAME USER old_user TO new_user

    That's it!
     
  11. stuart4487

    stuart4487 Registered

    Joined:
    Feb 9, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Hello there..

    it will be much faster to create the new account and new user and dump the db and restore it to the new db. otherwise you will be tied up in changing parent child values, database names and then even running sql querys to update multple tables on the mysql side for the new database names.

    On the mysql server itself just dump the old data, then reinport it back to the new user. will take a matter of seconds.

    Thank you..
     
  12. Luchanu

    Luchanu Member
    PartnerNOC

    Joined:
    Aug 7, 2007
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    yeahp db

    Use cpanel transfer > Copy multiple accounts/packages from another server
    Migrate the account and later a db for the account:

    cd /var/lib/mysql
    tar -czf database-user.tar.gz username_db
    scp database-user.tar.gz usersuper@ipserverdestiny:/home/usersuper (su)
    or
    scp database-user.tar.gz root@xxx.xxx.xxx.xx:/home/root or /home (chown root.root)

    on other terminal:
    cd /home/root/
    mv database-user.tar.gz /var/lib/mysql
    tar -xzvf database-user.tar.gz
    rm database-user.tar.gz

    finish migration.
     
Loading...

Share This Page