Transfer a Large Database using MySQL Dump and a SHH Client

Have you needed to transfer a large database? One of an eCommerce Cart or of a big blog? phpMyAdmin only allow’s imports of databases of 2 mbs, if you fix the php.ini file you can get maybe 10 or 12 max on most servers. In order to move this you will need to do it through shell access. Here is instructions on how to do this:

1. Contact your service provider and tell them to allow shell access in your account.
2. Download a SHH client like puTTY
3. Open the application, enter your domain and the port. (I have usually encounter them to be 22 or 2222)
4. Enter your username and password. These are usually the same as your FTP credentials.
5. To export or dump the MySQL database you will be using a tool calles MySQL Dump, to do this you will type:
   1. mysqldump -u [username] -p[password] [dbname] | gzip > [location/name].sql.gzip
   2. the items in [ ] need to replaced and the brackets removed. Also please note there is no space between -p and your password
   3. This will create what is called a dump file of your database which will be gzipped for easier development.
6. Now you need to get the file that was created and upload it to your new site via an SFTP or FTP client.
7. After this enable shell access on your new server or location and access it
8. First we need to decompress the .gzip file, to do this type:
9. gzip -d [location/file].sql.gzip
10. After this is completed the gzip file will dissapear and you will only have the sql file needed.
11. Now all you need to do is import the file into your database, to do this type:
    1. mysql -u[username] -p[password] [dbname] < [location/file].sql
12. Your import and transfer of your MySQL database is now complete.

————

Do you need any work performed on your database or on your web application? You can contact me at (619) 800-2678 or using the Quote Request form.

Share on Facebook

Tweet

Follow us