Exporting and Importing Large database using SSH

Hey,

I got problem with large database to import and export.so i decided to share my knowledge with you…


Exporting a MySQL database via the command line

I confess to having little problem exporting via phpMyAdmin, but large db’s do sometimes not like being zipped or gzipped that way, so here’s the run down:

  • login to your web site using Puty or similar.
  • navigate to the directory where you want to store your extracted database file. This can be a temporary directory you create just for the purpose. Note that after the MySql dump is created, it shouldn’t be left in a publically accessible directory longer than necessary.
  • run the following on the command line: mysqldump -a -u USERNAME -pDATABASE_NAME > FILENAME.sql
  • If you want to, check the size of your file (command: ls -al)
  • gzip your file to make the download faster using the command:
    gzip FILENAME.sql
  • download your file in ftp to your local pc to upload and import into your new database

Importing a MySQL database via the command line

  • create a new blank database using phpMyAdmin or your web server control panel. Take careful note of the server host name, db name, username and password.
  • upload your *.sql file to your new web site (I create a temporary directory for the purpose)
  • Fire up Putty, log in and navigate to the directory with the uploaded sql file.
  • If you gzipped the sql file, unzip your database file with this command:
    gzip -d FILENAME.mysql
  • run the following on the command line to import:
    mysql -u USERNAME -p DATABASE_NAME < FILENAME.sql
  • The system will ask you the password for the database, enter it.
  • If all goes well, the command line prompt will sit there for anything from a few seconds to several minutes as the db is processed. When prompt changes, check your database in phpMyAdmin to make sure all the tables imported properly.

you are done after following these steps…………Cheers!!!!!!

This entry was posted in Categories, Mysql. Bookmark the permalink.