phpmyadmin import database

Sooner or later, you will find it is difficult to import database using phpmyadmin. Phpmyadmin often fails to import large database although you may see the limit for database size is 80M or larger for your hosting account. But it is almost impossible to upload and import a big database larger than 10M. This is because phpmyadmin is not designed to handle large database. You can compare the time used by phpmyadmin and the command line tool mysql to export/import a database. There is a significant difference. Thus phpmyadmin often fails to import databases due to php timeout and unfortunately you usually cannot change the php timeout settings if you use shared hosting service. Even you can change the php timeout to unlimited(e.g., on localhost or VPS), you may also find you cannot export/import large database files due to other problems of phpmyadmin. So do not try to use phpmyadmin to export or import databases large than 10M. The correct way to import and export big database files is using command tool mysql/mysqldump. Specifically, to export a database:

mysqldump -u username -p databasename > sqlfilename.sql

After the command begins to execute, it will prompt you to input the password for the user. After a while, the database is dumped to the file sqlfilename.sql. Now you should upload the sql file to the target host to be imported. After uploading the database file to the remote machine, you should login to your host using a ssh terminal. If you use a shared hosting account, you may not be familiar with the ssh login because you usually login with CPanel. Then you should download a ssh terminal such as putty and login your host with your account information. After login, go to the directory that the database file is uploaded to then issue the following command:

mysql -u username -p databasename < sqlfilename.sql

After a blink of eye, the sql file is imported into the database. Note that the command will prompt you to input the password for the user and does not echo the password when you input. So carefully input the correct password and press the enter key. Also note that the sql file to be imported can be the one mysqldump generates,or the one phpmyadmin exports. But in some case, you cannot use mysql to import successfully the sql file exported by phpmyadmin probably due to the imcompatability between mysql and phpmyadmin. In such case, you have to export the database with mysqldump then import it with mysql.

If you really want to use phpmyadmin to import large databases, you may need to raise the limit of file size that phpmyadmin allows to upload occasionally. You can see the file size limit nearby the “Browse” button when you import a database. To change that limit, you need to modify the post_max_size in php.ini, then restart httpd. The post_max_size is not the same as the file size limit. For example, if you set post_max_size=32M, you will get a limit of 20M for the file size that is allowed to upload.

Posted in tips of hosting