How-2-Move an SQL Database
Posted 04-09-2009 at 09:53 AM by DJL Solutions
Tags database, myphpadmin, mysql
By DJL
How-2-Host.com
As a hosting provider there are services your clients will expect from you and as a hosting provider you should expect to provide these services. Remember, there are thousands of other hosting providers out there. Customer service matters and when the customer needs your help, you should always be there.
Recently I had client come to me asking if I could help him move his SQL database from another hosting server to mine. As his host I felt that this was part of my job description and my duty to help him with this move so naturally I said of course.
During the transfer I ran into two issues. One, the server his SQL resided on was setup for SQL 5.0 while I was running version 4.0. The second issue was the sheer size of the db. There were 99 tables which is resonable bad but one of the tables was 300 mb’s in size alone making it hard to import.
Road Block 1: How do you move SQL5.0 to SQL4.0?
When I ran into the version issue I spoke with my server management team who informed me of the risk in performing an upgrade on the SQL since I have other clients on the server. I continued to research with google and try different exports with no avail until I noticed the ‘SQL compatibility mode ’ which has a setting for ‘MYSQL40’. I ran a few tables as a test and it worked like a charm!
Now that I could export the database in a 4.0 format I could easily import it right?!? Wrong.
Road Block 2: How do you import a db that is too big?
I have always been fortunate enough to be able to do all my db within myphpadmin which makes life easy but not this time. You see, myphpadmin only accepts import files of 51mg’s. This is 1/6th of the size of a table.
Back to google.
After a few hours of searching I came to BigDump: The Staggered MySQL Dump Importer. Big Dump is a script that executes only a small part of the SQL export and then restarts itself to import the next segment.
This tool worked like a dream, so much in fact it has crossed my mind to donate a few bucks to the authors of it. All you have to do is upload the bigdump.php file and the gzip SQL export into the same directory, then edit the bigdump.php file to your db settings. Once you have to complete just go to your website and view the php file, click the link and off it goes!
At first I was trying to use a zip file since the compressions seemed smaller but that was unsuccessful and I ended up exporting it into the gzip file.
Hope this helps anyone having to move an SQL db.
How-2-Host.com
As a hosting provider there are services your clients will expect from you and as a hosting provider you should expect to provide these services. Remember, there are thousands of other hosting providers out there. Customer service matters and when the customer needs your help, you should always be there.
Recently I had client come to me asking if I could help him move his SQL database from another hosting server to mine. As his host I felt that this was part of my job description and my duty to help him with this move so naturally I said of course.
During the transfer I ran into two issues. One, the server his SQL resided on was setup for SQL 5.0 while I was running version 4.0. The second issue was the sheer size of the db. There were 99 tables which is resonable bad but one of the tables was 300 mb’s in size alone making it hard to import.
Road Block 1: How do you move SQL5.0 to SQL4.0?
When I ran into the version issue I spoke with my server management team who informed me of the risk in performing an upgrade on the SQL since I have other clients on the server. I continued to research with google and try different exports with no avail until I noticed the ‘SQL compatibility mode ’ which has a setting for ‘MYSQL40’. I ran a few tables as a test and it worked like a charm!
Road Block 1: Passed
Now that I could export the database in a 4.0 format I could easily import it right?!? Wrong.
Road Block 2: How do you import a db that is too big?
I have always been fortunate enough to be able to do all my db within myphpadmin which makes life easy but not this time. You see, myphpadmin only accepts import files of 51mg’s. This is 1/6th of the size of a table.
Back to google.
After a few hours of searching I came to BigDump: The Staggered MySQL Dump Importer. Big Dump is a script that executes only a small part of the SQL export and then restarts itself to import the next segment.
This tool worked like a dream, so much in fact it has crossed my mind to donate a few bucks to the authors of it. All you have to do is upload the bigdump.php file and the gzip SQL export into the same directory, then edit the bigdump.php file to your db settings. Once you have to complete just go to your website and view the php file, click the link and off it goes!
Road Block 2: Passed
At first I was trying to use a zip file since the compressions seemed smaller but that was unsuccessful and I ended up exporting it into the gzip file.
Hope this helps anyone having to move an SQL db.
Total Comments 0
