The contents of a database (tables, indexes, data, etc.) can be exported from MySQL as a text file containing the SQL statements that recreate the database structure and populate it with the data. The following are the steps required to restore a MySQL database from such an SQL text file using PHPMyAdmin.
- Suppose we have an SQL file called db.sql. (PHPMyAdmin can also handle compressed or archived file formats like db.sql.zip – no need to unpack them first.)
- Using the web browser, navigate to http://localhost/phpmyadmin to start PHPMyAdmin.
- Be aware that importing an SQL file can overwrite existing tables or databases. Make sure that you know what the SQL file is supposed to do and that it won’t interfere unexpectedly with existing databases.
- Click the Import tab. Use the “Browse your computer” input box to select the SQL file to import.
- Note the size of the file, and the PHP “Max” file size next to “Browse your computer”. The default “Max” size on my installation was a puny 2,048KiB. If the SQL file that you want to import is too big, you could first try compressing it (to a zip file, say). If it is still too big, you need to edit the PHP configuration file
php.ini. You could do this by hand with a text editor. Mine is at
C:\wamp\bin\php\php5.3.8\. Alternatively you could go via the WampServer icon in the Notification Area… … (PHP > php.ini), which amounts to the same thing. Find the line that begins
upload_max_filesizeand modify the value that follows it. I have just changed mine from 2M (2 megabytes) to 100M. Save the modified
php.inifile. Restart PHP/Apache.
- Choose the appropriate character encoding – my data files should always be in utf-8. Uncheck the “partial import” box unless you have a good reason not to. Make sure that “Format” is set to SQL.
- Click “Go”.