how to download mysql database contents?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gratou
Forum Newbie
Posts: 10
Joined: Sun Jul 28, 2002 4:27 am
Location: Auckland, NZ

how to download mysql database contents?

Post by gratou »

Hi,

I may change hosting company and I wonder what's the best way to retrieve my database contents.
I could somehow dump each record from each table to an HTML page (some fields are blobs) and read the url from my pc to rebuilt the database locally and check it.
Then upload it again, though I am unsure how, except as php files holding sql inserts.
Or read the URLs directly from my new host.

Do such download/upload example scripts exist?
Any better idea than using HTML pages to dump it and retreive it all?
Any security, data formatting, etc. isuue I should be careful about?

Thanks for your insight.
User avatar
haagen
Forum Commoner
Posts: 79
Joined: Thu Jul 11, 2002 3:57 pm
Location: Sweden, Lund

Post by haagen »

If you have shell access to your new host and your account are able to connect from other than localhost I would have done this:

Code: Select all

# mysqladmin create database
# mysqldump -h old.host.com -u userold name -p database | mysql -u usernew -p database
That's it.

If you have access to phpmyadmin i belive there's some functionallity to dump tables and data into a flat html file.
DSM
Forum Contributor
Posts: 101
Joined: Thu May 02, 2002 11:51 am
Location: New Mexico, USA

Post by DSM »

The easiest way is if you have phpMyAdmin, it has a dump function which produces an *.sql (text) file, then when you move to your new server, just upload the *.sql thru phpMyAdmin, viola your db is in place tables and all.
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

Well, if no phpMyAdmin (and you shouldn't learn to realy on it anyways), but you can at least ssh to your acouunt (Telnet? Egads! Horrors!), do this.

To dump it to a file type in

Code: Select all

mysqldump databasename > databasename.sql
That will dump the schema and all data to the file. When it's on your new server,
and the database has been created, type in...

Code: Select all

mysql databasename < databasename.sql
.... and you're off to the races.

Later on,
BDKR
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

If no phpMyAdmin then download the pages and upload to your own site, they are just web pages after all.
gratou
Forum Newbie
Posts: 10
Joined: Sun Jul 28, 2002 4:27 am
Location: Auckland, NZ

Thank you.

Post by gratou »

Thank you to all of you people. :D

I have to try that now.

I'll come back if I need more help, but all the stuff you have given me looks great!
Post Reply