copying database from one account to another

[randyriegel]

I set up a subdomain under my main account to do a site for a customer. This site also contained a database "myacct_zimish". Now I created an actual account for the client after they accepted the work and want hosted with me. My only question is what is the easiest way to copy the MySQL database from one account to another? so the database will be owned by their account (ie "zimish_zimish"?

[JPC-Jawad]

You can export the database backup from phpMyAdmin to your local computer, create a new blank database and use import in phpMyAdmin to restore the database .sql file. You could also do this easily via command line as root if you have previous experience.

> mysqldump myacct_zimish > db_dump.sql

> mysqladmin create zimish_zimish

> mysql zimish_zimish < db_dump.sql

[randyriegel]

Thanks, I think I'll use the command line. I haven't used mysqldump before but that looks pretty easy. Thanks again!

I thought about phpmysql but was hoping there was an easier way since I do have root access.

Randy

[Ron]

I've never done that as root; when I do that as an account the dumpfile has the account name prepended to the create database command.

Won't he need to edit that, or does it behave differently for root?

[randyriegel]

I used those three statements exactly (except for changing the db names to actual ones) and it worked perfectly as root. Just had to go into cpanel and add a user to the new database.

Checked the .sql file I created and it doesn't contain anything about creating a database. Just tables.

[jason]

Ron,

It depends on the options you specify when you export. By default, I believe mysqldump (and PMA's export feature) do not attach any database names, but there are options that will add create database commands and/or include the database name in all of the SQL references. The only place where I've ever had issues with this stuff is when using views. If you have a MySQL database that is new enough to support views and you use them, the "SHOW CREATE VIEW" statement is sometimes a little more verbose than expected.

--Jason

[Ron]

I just use

mysqldump -u (username) -p(password) -a --all-databases

I had to go hunting for the -a option, as I worte that script several years ago. It seems that it is not an option on MySQL 5. On 4. it says "Include all MySQL specific Create options" so I doubt that is what is prepending my account name to the database names. Perhaps that is a 4.x feature as well.

[Ron]

I used those three statements exactly (except for changing the db names to actual ones) and it worked perfectly as root. Just had to go into cpanel and add a user to the new database.

Checked the .sql file I created and it doesn't contain anything about creating a database. Just tables.

Here is the top of my sql file:

--
-- Current Database: `USER_DB`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `USER_DB` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `USER_DB`;

--
-- Table structure for table `TABLENAME`
--

DROP TABLE IF EXISTS `TABLENAME`;
CREATE TABLE `TABLENAME` (

USER_DB and TABLENAME were edited....

Perhaps this is a result of the --all-databases option.