I am in a process of moving my site from one hosting company or another. My current MySQL database is about $20 MB, but none of the two hosting companies give me Telnet or SSH access to move it. The new hosting company allows me to use phpmyadmin, but they are telling me that my sql file is too big to upload it through phpmyadmin. Could anybody suggest if there is any tool that would allow me to backup only certain tables from MySQL? Maybe that way I will be able break down my current MySQL into smaller files and then upload them through phpmyadmin.

depending on wether or not your web host has a firewall blocking the port that mysql runs on you could try using MySQL-Front. The group that was developing the software recently closed the project but you can find links to download it here

http://mysqlfront.venturemedia.de/index.php?act=ST&f=2&t=328

Do the hosts both use CPanel? I know CPanel has a backup and restore feature for MySQL.

- Matt

If the host won't give you SSH access and won't create the large database for you on top of that, it's probably a good idea to find a host who is a bit more accomodating.

Normally, I don't say things like this, but that's just silly.

The new hosting company is HostNexus. The don't use CPanel. I think Plesk is what they use.

They can create a large database - just not through phpmyadmin. They say a 20 MB file will take over 10 hours to upload through phpmyadmin. I provided them with a backup .sql file that I created through phpmybackup, but they are saying it is corrupted. I then made another file like that, but again, they tell me it is corrupted also. So they are asking me to make my current hosting company to create a backup, but for some reason my current hosting company won't do it. Maybe they suspect that I am moving away from them. That is why I am trying to find out how I can make a backup of my large MySQL database on my own.

I don't know if you can restore with this script...
http://www.phpfreaks.com/script/view/11.php

They say a 20 MB file will take over 10 hours to upload through phpmyadmin.
What speed modem do you have? My cable modem with 128kbps upload would take 20 minutes on that.

- Matt

I have DSL, but I don't think that is the issue. Here is a reply from HostNexus:

The sql is pretty huge - over 20 MB. PHP is configured to allow uploads of up to 20 GB. Also, uploading this through phpmyadmin might take hours - maybe 8-10 hours and I would not recommend populating databases with phpmyadmin with files more than 5 MB.

The 'easiest' way, and all you need to do is copy the database files from one machine to the other. If both hosts will put themselves out just a little for you, and assuming you have sftp or ftp access, ask them to tar up the database files and put the archive into the ftp area and migrate the files that way.

Alternatively, ask them to produce the archive and run split on it and email you the bits in 2MB chunks. Ask your new host to reverse the process. Mail the files to the new host and reverse the process.

Before producing the archive, either stop the mysql daemon or at least flush the tables.

If their PHP allows uploading 20 MB there would be no problem. You can upload your MySQL database via PHPMyAdmin. It may take a little bit long. Because http file transfer method is a little bit slow. Thats all. That does not take 8 or 10 hours. You should try to upload once, I think it can take no more than one an half hour. :)

I recently moved to a new host and had to move a file about half the size of yours (approx. 13 MB).

One of the options you might consider is splitting the sql file into smaller ones and uploading them individually. Just save the data + structure of your old database using the "save as file" option. Then use Wordpad to save diff. sections into separate files. I split my files into separate 1 MB *.sql files and uploaded them in the Run SQL Queries section. It seems to work better than loading a really big file all at once.

If you want to really save time, though - see if your host will do the Telnetting for you if you give them the file (through FTP) or something.

Originally posted by alosito
They can create a large database - just not through phpmyadmin. They say a 20 MB file will take over 10 hours to upload through phpmyadmin. I provided them with a backup .sql file that I created through phpmybackup, but they are saying it is corrupted. I then made another file like that, but again, they tell me it is corrupted also. So they are asking me to make my current hosting company to create a backup, but for some reason my current hosting company won't do it. Maybe they suspect that I am moving away from them. That is why I am trying to find out how I can make a backup of my large MySQL database on my own.

Oh, that's okay then. I thought they were unwilling to do it for you. Your old host would have to either give you shell access or dump your database for you from the command line, as the database is too big to dump via phpmyadmin as well.

I hope you're leaving on good terms! :)

Ask your previous host for SSH/Telnet.

I use a simple php script to restore databases from a .sql file uploaded to the server. This doesn't always work for everybody, but nonetheless worth a try.

Example:

1. Upload the database backup to the new server's main directory.
2. Name it mybackup.sql
3. Insert this code alone into a file called rundump.php and execute it from your browser on the new site's main directory. You need to edit the first 5 lines beforehand, with the full path and access details, but not the passthru code.


<?php
$user = "yourusername";
$Password = "yourpassword";
$db = "yourdatabasename";
$host="localhost";
$dumpfile = "/home/virtual/yoursitename/var/www/html//mybackup.sql";
passthru("mysql -h ".$host." -u ".$user." -p".$Password." ".$db." < ".$dumpfile);
?>

<H3>Read dump completed</H3>.