« Transferring Databases - How Do I Transfer A Website? - Part 2 | Home | Continue To Earn Even After Selling Your Website »
Transferring Databases - How Do I Transfer A Website? - Part 3
By Max | June 10, 2007
If your database is too large, then you’ll probably have trouble using the phpMyAdmin interface. There are two problems with large DBs and phpMyAdmin.
First, you have to download the exported file to your own personal workstation and then upload it to the new server. That can be pretty slow going if your DB is over 100MB. Upload times are not very good even with a cable modem. FTPing from the old host to the new host would be much faster. They have heavy duty bandwidth installed for just the occasion.
Second, phpMyAdmin just isn’t designed for uploaded largish files. Because you have to transfer through your browser an upload to the new server can time out if the DB is too large. This can be remedied somewhat by selecting the “Partial Import” option on the import screen, but I don’t like to rely on that since you could potentially lose data.
The solution in this case to go directly to the command line to export a DB, then ftp the file to the new server where you’ll import it there from the command line. You’ll need ssh access to your host. I highly recommend getting a hosting plan that allows ssh access.
Here’s the steps.
Exporting:
mysqldump -u username -ppassword database_name > DB_backup.sql
In the above command line, “username” should be replaced with the user associated with the DB. The “password” is the DB password that is associated with the user. You can get the DB name and user name from cpanel’s DB menu. The password can usually be found in a config file on the server, and if you are unsure where to look, you’ll have to ask the previous admin. When I did this the first time, I had a lot of trouble because there is a space between the “-u” option and “username”, however there is no space between “-p” and “password”. Weird huh?
Another gotcha that arises when using cpanel is the naming convention. If cpanel was used to create the DB, it will prepend a sitename to the DB name you chose. For this blog, I called my wordpress DB, “wordpress”. Creating it through cpanel creates a DB named “howtobuy_wordpress”. Thats the real name you’ll want to use with the mysqldump command.
Now you’ll want to “gzip” the file DB_backup.sql, and then ftp it to your new host. This can be done directly through the ssh session. Type “gzip DB_backup.sql” to zip your file, and then “ftp xxx.xxx.xxx.xxx” where xxx.xxx.xxx.xxx is the new host’s IP. I won’t get into ftp commands here, but there are plenty of guides available on the internet.
Importing:
Now the DB_backup.sql.gz file should be located on the new server which you’ll need to ssh in to. Go ahead and unzip it with
gunzip DB_backup.sql.gz
Access your cpanel and create a new DB and a new user that will be associated with the DB.
mysql -u username -ppassword database_name < DB_backup.sql
Notice that in the above command the direction of the “<” sign. Its different from the first command. You still don’t have a space between the “-p” and “password”.
Thats it. Your DB should now be accessible from your website’s software. If you changed the any of the username, DB name, or password, you’ll have to reconfigure your website software with the new info.
Topics: Article |
July 4th, 2007 at 3:47 am
[…] You will still have to transfer the DBs by exporting them as I mentioned before. […]