Moving MySQL databases from one account to another

[pumpkinkid]

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.

 

[thewebhosting]

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.

 

[pumpkinkid]

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.

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

 

[Infopro]

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

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.

 

[pumpkinkid]

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:

'mysql://user_name:[email protected]/drupal_database';

While I could just type in a new password in the section above... I normally use special characters such as [email protected]&% ... 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?

 

[pumpkinkid]

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

 

[Infopro]

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.

 

[pumpkinkid]

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.

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

 

[pumpkinkid]

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.

Well, I should have listened to you, you were right, officially cPanel said pretty much the same thing:

There is now way to move a database from one cpanel account to another using cpanel except by creating a dump of the database and restoring it on the new account. You can do this with phpMyAdmin ro by using the mysqldump CLI.

An example of using mysqldump

mysqldump <databasename> > <databasename>.sql
or with a real name

mysqldump user_databse > user_database.sql

Then on the new cPanel account create the database and then restore the sql dump to that database by doing

mysql -D newuser_database < ./user_database.sql

That would restore the dump user_database.sql into database newuser_database

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!

 

[pumpkinkid]

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!

 

[stuart4487]

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

 

[Luchanu]

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 [email protected]:/home/usersuper (su)
or
scp database-user.tar.gz [email protected]:/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.