Make a copy of a database -- how?

[Tedd-3]

Hi:

I'm developing php scripts for a client that will eventually use his
database. However, I don't want to touch his database during
development and instead want to use a copy.

While I thought it was going to be easy to make a copy (i.e., just
dump the database and reload it via phpMyAdmin) the database turns
out to be too large.

So, I resorted to saving individual tables with data and then
reloading these one at a time. However, even some of those tables are
too large. At 1.3 Meg (not compressed and 132k gz compressed), the
phpMyAdmin 2.6.0-pl3, after a considerable delay, reports "Service
Unavailable" and fails.

The total database size is around 13 Meg and phpMyAdmin reports that
it can handle uploads up-to 2 Meg, but craters at far less.

So, what are my options?  Any quick one line solutions? Nothing I've
read address the problem I'm facing.

Thanks in advance.

Cheers,

tedd

--
-------
http://sperling.com  http://ancientstones.com  http://earthstones.com
_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql

NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com

Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php

[Hans Zaunere]

Hi,

tedd wrote on Wednesday, September 12, 2007 4:33 PM:

> Hi:
>
> I'm developing php scripts for a client that will eventually use his
> database. However, I don't want to touch his database during
> development and instead want to use a copy.
>
> While I thought it was going to be easy to make a copy (i.e., just
> dump the database and reload it via phpMyAdmin) the database turns
> out to be too large.
>
> So, I resorted to saving individual tables with data and then
> reloading these one at a time. However, even some of those tables are
> too large. At 1.3 Meg (not compressed and 132k gz compressed), the
> phpMyAdmin 2.6.0-pl3, after a considerable delay, reports "Service
> Unavailable" and fails.
>
> The total database size is around 13 Meg and phpMyAdmin reports that
> it can handle uploads up-to 2 Meg, but craters at far less.
>
> So, what are my options?  Any quick one line solutions? Nothing I've
> read address the problem I'm facing.

You'll need to use the mysql command line tools, mysqldump and mysql.
Something like this:

mysqldump -u username -p --database yourdatabase > yourdatabase.sql

Then copy the .sql file to where you want it, and do something like:

mysql -u username -p
mysql> source yourdatabase.sql

Of course, read a bit about mysqldump before just doing this.  There may be
certain flags you should set, depending on the naming of your database, etc.
Just make sure it doesn't drop your customer's database :)

Another option is that phpmyadmin should have a Copy section under the
Operations tab (if it's fairly recent).  This will use SQL commands to copy
the database, so there are no round trips to the web server.

H

_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql

NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com

Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php

[Jon Baer-2]

Normally if I don't need the entire DB (dev work) and can work w/ the  
last 100 records, I will end up doing something like:

create table mytable_temp select * from original_table limit 100
insert into mytable_temp select * from original_table limit 101,200
etc, etc, etc.

Then either use that in my db config or mysqldump it out as a test  
fixture.  Can probably be bashed up into a one liner using mysql  
client if need be.

- Jon

On Sep 12, 2007, at 4:32 PM, tedd wrote:

> Hi:
>
> I'm developing php scripts for a client that will eventually use  
> his database. However, I don't want to touch his database during  
> development and instead want to use a copy.
>
> While I thought it was going to be easy to make a copy (i.e., just  
> dump the database and reload it via phpMyAdmin) the database turns  
> out to be too large.
>
> So, I resorted to saving individual tables with data and then  
> reloading these one at a time. However, even some of those tables  
> are too large. At 1.3 Meg (not compressed and 132k gz compressed),  
> the phpMyAdmin 2.6.0-pl3, after a considerable delay, reports  
> "Service Unavailable" and fails.
>
> The total database size is around 13 Meg and phpMyAdmin reports  
> that it can handle uploads up-to 2 Meg, but craters at far less.
>
> So, what are my options?  Any quick one line solutions? Nothing  
> I've read address the problem I'm facing.
>
> Thanks in advance.
>
> Cheers,
>
> tedd
>
> --
> -------
> http://sperling.com  http://ancientstones.com  http://earthstones.com
> _______________________________________________
> New York PHP Community MySQL SIG
> http://lists.nyphp.org/mailman/listinfo/mysql
>
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
>
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php

_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql

NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com

Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php

[Rob Marscher]

On Sep 12, 2007, at 4:32 PM, tedd wrote:
> While I thought it was going to be easy to make a copy (i.e., just  
> dump the database and reload it via phpMyAdmin) the database turns  
> out to be too large.  So, what are my options?  Any quick one line  
> solutions? Nothing I've read address the problem I'm facing.

I usually do what Hans suggested as well.  Jon's point about creating  
temporary tables with a subset of data and then exporting/importing  
those is a good idea to limit the amount of data you need to transfer  
and store in your dev environment... although if you don't know the  
database well enough, you could miss copying the necessary related  
rows from all of the tables.

Just a couple additions to Hans' suggestion:

If you are using MySQL 4.1 or greater, the default mysqldump settings  
should work well.  4.0 and earlier need you to specify some extra  
options to use extended insert statement syntax, turn off indexing,  
don't buffer the results, and other stuff like that -- but they  
updated mysqldump to default to those common options as of 4.1.  
mysqldump --help will show you all of the variables/options and their  
default settings on your machine.

You also probably want to pipe the dump to gzip or bzip2, transfer  
that to your computer, and then uncompress and import.

1. mysqldump -hdbhost -udbuser -p dbname | bzip2 > db.sql.bz
2. transfer db.sql.bz
3. bunzip2 -k db.sql.bz
4. mysql -hdbhost -udbuser -p dbname < db.sql

-Rob

_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql

NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com

Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php

[Jon Baer-2]

To add to that ... there are a few nice handy tools which are nice to have in the MySQL Toolkit (by Baron Schwartz) which can help verify things in your setup after (checksums) ...

http://mysqltoolkit.sourceforge.net

http://mysqltoolkit.sourceforge.net/doc/mysqltoolkit.html

I have not run "mysql-archiver" before on anything but it also looks like another nice option for your task + in general.  

- Jon

On Sep 12, 2007, at 11:24 PM, Rob Marscher wrote:

On Sep 12, 2007, at 4:32 PM, tedd wrote:

While I thought it was going to be easy to make a copy (i.e., just dump the database and reload it via phpMyAdmin) the database turns out to be too large.  So, what are my options?  Any quick one line solutions? Nothing I've read address the problem I'm facing.

I usually do what Hans suggested as well.  Jon's point about creating temporary tables with a subset of data and then exporting/importing those is a good idea to limit the amount of data you need to transfer and store in your dev environment... although if you don't know the database well enough, you could miss copying the necessary related rows from all of the tables.

Just a couple additions to Hans' suggestion:

If you are using MySQL 4.1 or greater, the default mysqldump settings should work well.  4.0 and earlier need you to specify some extra options to use extended insert statement syntax, turn off indexing, don't buffer the results, and other stuff like that -- but they updated mysqldump to default to those common options as of 4.1.  mysqldump --help will show you all of the variables/options and their default settings on your machine.

You also probably want to pipe the dump to gzip or bzip2, transfer that to your computer, and then uncompress and import.

1. mysqldump -hdbhost -udbuser -p dbname | bzip2 > db.sql.bz

2. transfer db.sql.bz

3. bunzip2 -k db.sql.bz

4. mysql -hdbhost -udbuser -p dbname < db.sql

-Rob

_______________________________________________

New York PHP Community MySQL SIG

http://lists.nyphp.org/mailman/listinfo/mysql

NYPHPCon 2006 Presentations Online

http://www.nyphpcon.com

Show Your Participation in New York PHP

http://www.nyphp.org/show_participation.php

_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql

NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com

Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php

[Tedd-3]

At 4:32 PM -0400 9/12/07, tedd wrote:
>So, what are my options?  Any quick one line solutions? Nothing I've
>read address the problem I'm facing.


H said:

>You'll need to use the mysql command line tools, mysqldump and mysql.
>Something like this:
>
>mysqldump -u username -p --database yourdatabase > yourdatabase.sql

I don't have access to a command line. But, I've tried this using php
script, but the host has safe_mode on, which limits such activity.

- Jon said:

>Normally if I don't need the entire DB (dev work) and can work w/
>the last 100 records, I will end up doing something like:

That's a good idea. In this case, while I'm not touching the original
database, my copy will be phased into the mix and thus needs to be
complete.

-Rob said:

>If you are using MySQL 4.1 or greater, the default mysqldump
>settings should work well.

I didn't say, but the MySQL version is 3 something and I don't have
command line.

HOWEVER, I did find a solution, which was to have the host upgrade to
a newer version of phpMyAdmin. From there, I was able to transfer
larger tables.

Thanks to all who replied. I shall keep your suggestions.

Cheers,

tedd

--
-------
http://sperling.com  http://ancientstones.com  http://earthstones.com
_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql

NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com

Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php