How you save it kinda depends on what your next host will be able to work with. What I'm in the habit of doing is using phpmyadmin's 'export' feature to export the database (click 'select all' and check 'complete inserts' and 'extended inserts') as a .sql file, then using 'backup' from my cpanel to get a .gz file of the same stuff. Oh and before you start you might want to empty your hitlog and sessions tables. Anyway that gives you the same database in 2 diferent formats.

Use your FTP software to download all your files. You won't need to re-do the installation part because all your stuff works - you just want to move it. So you backup all your files, then upload them to your new home. How you make a database on your new server is up to you, and your new host. Either way, once you have a database name and user name and database password you'll want to make sure your conf/_basic_config.php file knows that stuff so it can connect to the database.

So anyway you got all your files and your new database connection info, and a nice happy empty database. If your new host has cpanel with the backup utility you can use it to import your .gz file into the new database. You will probably have to rename the file though. Like when you did backup you got 'olddbase.gz' but your new database wants to be called 'newdbase', so you'll have to rename the file to 'newdbase.gz'. Always keep nice safe copies somewhere else just in case things get really weird!

If you can't restore through the backup utility you'll have to use the .sql file. It's not easy. It's a drag. You go into phpmyadmin at the new server and select your new database then click 'sql'. Now the crappy part: open the .sql file in a text editor and start copying out huge chunks of it, then pasting it into the sql box. Slowly but surely you'll rebuild your database. Much nicer to be able to restore from the .gz file!

EdB wrote:

How you save it kinda depends on what your next host will be able to work with. What I'm in the habit of doing is using phpmyadmin's 'export' feature to export the database (click 'select all' and check 'complete inserts' and 'extended inserts') as a .sql file, then using 'backup' from my cpanel to get a .gz file of the same stuff. Oh and before you start you might want to empty your hitlog and sessions tables. Anyway that gives you the same database in 2 diferent formats.

Use your FTP software to download all your files. You won't need to re-do the installation part because all your stuff works - you just want to move it. So you backup all your files, then upload them to your new home. How you make a database on your new server is up to you, and your new host. Either way, once you have a database name and user name and database password you'll want to make sure your conf/_basic_config.php file knows that stuff so it can connect to the database.

So anyway you got all your files and your new database connection info, and a nice happy empty database. If your new host has cpanel with the backup utility you can use it to import your .gz file into the new database. You will probably have to rename the file though. Like when you did backup you got 'olddbase.gz' but your new database wants to be called 'newdbase', so you'll have to rename the file to 'newdbase.gz'. Always keep nice safe copies somewhere else just in case things get really weird!

If you can't restore through the backup utility you'll have to use the .sql file. It's not easy. It's a drag. You go into phpmyadmin at the new server and select your new database then click 'sql'. Now the crappy part: open the .sql file in a text editor and start copying out huge chunks of it, then pasting it into the sql box. Slowly but surely you'll rebuild your database. Much nicer to be able to restore from the .gz file!

Thank you so much!
I knew it can't be easy... knowing I don't have to re-install is a release...
I will be using the same hosting company though(go daddy)... Just upgraded to the next level so I assume everything is the same then?

I tried to transfer the database this morning without cancel my exsiting hosting so here is my error:

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: 10.8.6.215
-- Generation Time: Mar 10, 2007 at 08:29 AM
-- Server version: 4.0.27
-- PHP Version: 4.4.4
--
-- Database: `old database name`
--
CREATE DATABASE `old database name` ;

MySQL said: Documentation
#1044 - Access denied for user 'club_db_'@'%' to database 'old database name'

Interesting comparing notes. Generally, when I "export" a DB manually using PMA, I do the following:

1) Optimize all the tables.
2) (Ed's idea of emptying sessions/hitlogs is a GREAT idea, especially if you have scads of data. Smaller files are always easier to work with.)
3) EXPORT
4) Select ALL tables
5) Check ( Add DROP TABLE ... in case I'm overriding an existing DB )
6) Check ( Add IF NOT EXISTS ... in case I'm writing to a NEW, empty DB )

(I've never monkeyed with "complete inserts/extended inserts" ... wonder what that does? Generally gone with the defaults and things have worked pretty well.)

7) Check "Save As File" with GZipped compression), which lets me write the backup to my local drive.

Then, at the new host, I simply create a DB (Named as I want, with users as I want and passwords/priviliges as I want) ...

The run the restore, look for the file on my local drive and cross my fingers, cross my legs and my eyes. 7 times out of 10 ... it works. (Other times, I get errors and I just keep trying, until it works). I've always mangaged to get the data across.

Dunno about your error, bbqgal. Was the error on EXPORT? (Weird, b/c you usually select an EXISTING DB ... then EXPORT IT ... no need to "create" anything?)

stk wrote:

Interesting comparing notes. Generally, when I "export" a DB manually using PMA, I do the following:

1) Optimize all the tables.
2) (Ed's idea of emptying sessions/hitlogs is a GREAT idea, especially if you have scads of data. Smaller files are always easier to work with.)
3) EXPORT
4) Select ALL tables
5) Check ( Add DROP TABLE ... in case I'm overriding an existing DB )
6) Check ( Add IF NOT EXISTS ... in case I'm writing to a NEW, empty DB )

(I've never monkeyed with "complete inserts/extended inserts" ... wonder what that does? Generally gone with the defaults and things have worked pretty well.)

7) Check "Save As File" with GZipped compression), which lets me write the backup to my local drive.

Then, at the new host, I simply create a DB (Named as I want, with users as I want and passwords/priviliges as I want) ...

The run the restore, look for the file on my local drive and cross my fingers, cross my legs and my eyes. 7 times out of 10 ... it works. (Other times, I get errors and I just keep trying, until it works). I've always mangaged to get the data across.

Dunno about your error, bbqgal. Was the error on EXPORT? (Weird, b/c you usually select an EXISTING DB ... then EXPORT IT ... no need to "create" anything?)

The message appears when I was trying to import the DB.

I have many times used a little utility called Big Dump.

1. Export the db out of the original installation as described above.
2. Upload the data file to your new server.
3. Upload bigdump.php to your new server.
4. Modify bigdump.php with correct access-to-db info ( db name, db user name, pw )
5. Run bigdump.

Works like a charm (usually) and overcomes the problem of import data size limitations.

Just do a search for Big Dump.