Move mysql database to another user

ranz

ranz

Verified User
Joined
Apr 8, 2010
Messages
305
Location
Australia
I'm going to attempt to move a mysql database from one user to another - there's a KB article on how to do this:

http://help.directadmin.com/item.php?id=224

Code: 
VERBOSE=1 DBUSER="da_admin" DBPASS="da_adminpass" USERNAME="username" NEWUSERNAME="newusername" /usr/local/bin/php /usr/local/directadmin/scripts/change_database_username.php

After reading the KB article, it's not clear to me how to execute this.

My question: Do I just run that command on my shell prompt?

(obviously changing the details to my applicable system)

Another Question:

will this modify any of the database settings?

what I'm concerned with is do I then need to go into the website and re-configure the database connection strings?
 
I dont see how its possible without getting a db dump and redumping to a new db under the new user. Ive never seen that move db script before.
 
Run the entire command line, exactly as shown (except for your changes to the boldfaced items) in your root shell.

Jeff
 
Thanks Jeff,

I'll let you know how I go - and I will have an answer to my own question by then ...
 
shouldn't this be added to the "move domain between users"?
 
daveyw said:
No, since its not a domain but a DB :)
Click to expand...
yes I know that. but most of the time, when you move a domain to another user you have to move the DB with it (otherwise it'll stay in the first user's records).


so maybe add a "move domain/db to another user", which will run both "move domain" & "move DB"...
 
MtK said:
yes I know that. but most of the time, when you move a domain to another user you have to move the DB with it (otherwise it'll stay in the first user's records).


so maybe add a "move domain/db to another user", which will run both "move domain" & "move DB"...
Click to expand...
That could be usefull, that it will also change the database names to the new username.
 
It cannot be implemented into "move domain" script. If you have a user which has the following domains:
userdomain1.com, userdomain2.com, userdomain3.com

And the following databases:
user_forum, user_cms, user_gallery

And database users:
user_myuser, user_new, user_database

How should we assign a database/database_user to any of the domains? (e.g. you transfer userdomain2.com)
 
In my case - it was purely the DB, as this user had more than one hosting account and had setup a DB in the wrong account for a website in another account.
 
did not function

Hi

I tried this script but it simply did not function, (mY sql 5.0.81). The database were not renamed.
I went through phpmyadmin and modified the databases there to perform the transfer.
 
MtK said:
yes I know that. but most of the time, when you move a domain to another user you have to move the DB with it (otherwise it'll stay in the first user's records).
Click to expand...

How do you propose to associate the database with the domain? Which databases go with which domains in a user's account? Currently databases are not associated with at domain at all.
 
I just used this script.
after replacing all the bolded strings, I got the line:
Code: 
swapping database DB_NAME ...
(the only DB for that oldusername)

but the DB is still in its original place, and newusername cannot see this DB under his DBs...
 
Without knowing the database name and the new username ...
 
Because DA determines if a user can see a database based on the username and the database name.
 
floyd said:
Because DA determines if a user can see a database based on the username and the database name.
Click to expand...
yes, of course it's important for DA.
but I don't understand why is that important to you (you asked for that information)...
 
You want to know why the new user cannot see the database. I need to know the database name and username to be sure of my suggestion to you.

If you want me (one of the most experienced at using DA on this forum) to help you then I need certain information from you since I cannot personally see your system.

I do not trust people to take examples and apply them to their own system. For instance I could say that if the username is myusername then the database name has to be in the format of myusername_databasename. But I do not trust that you or anybody else will apply that example to their own system correctly.

I could also tell you to look at the file and directory listing to check ownership and permissions. But I do not trust that you know what you are looking at so my next question would be to post the output of
Code: 
ls -l /var/lib/mysql
ls -l /var/lib/mysql/myusername_mydatabasename
Of course I can only do that once I have the information I have asked for.

Too many times people say they have checked certain things but because they did not know what they were looking at we have wasted hours or days trying to figure out a problem. I refuse to do that anymore.

If you feel that its a security risk then hire somebody to look at your system.
 
I agree with your distrust method, but please at least give me the benefit of the doubt :)

I've tried both the real DB username, and also the client username.
only the later worked and I got the message:
Code: 
swapping database DBNAME ...

but nothing moved...




permission and ownership of: /var/lib/mysql/DBNAME/*
Code: 
-rw-rw---- 1 mysql mysql


and /var/lib/mysql/DBNAME itself:
Code: 
drwx------ 2 mysql mysql
 
You must log in or register to reply here.
Back
Top