You have seen how MySQL data can be backed up by exporting the data to a text file which describes the database, tables, structure and data. That data can then be imported (the backup can be restored) in a similar way. This can be done to recover from data loss or to move data from one server to another. It is possible to do this with MySQL commands in a query but PHPMyAdmin makes it easier at first.
SQL import of a whole database
If you do not have privileges to create databases you can't do this first import.
Download (right click) this example sql database which was exported using PHPMyAdmin. To import the database just start PHPMyAdmin as root and on the main page click the Import tab. Browse to where you saved the example database and click on the Go button. You should see a new database in the list on the left of PHPMyAdmin. It comes complete with a table and data.
Go to the downloaded sample and open it in a text editor to see the structure of the file (Windows users use WordPad not Notepad). Although the words are SQL commands you should be able to work out what is happening.
SQL import of a table with data
Download this example table.
In PHPMyAdmin choose a database on the left (not the one you just imported). Click the Import tab. Browse to the file you just downloaded and press Go. A new table should appear in the database and the data is in the table. Look in the downloaded file to see the structure.
SQL import of just data
In PHPMyAdmin click on the table you just imported (users). Click the Empty tab. Think very carefully to see if you really want to lose all of the data. Empty is very dangerous if you are not careful (are you sure you are emptying the right table?). Once emptied you can click on the Browse tab to see the lack of data.
Download this file.
Click on the Import tab and Browse to the downloaded file. Press Go. Click on the Browse tab to see that the data is back.
The table structure must match the table structure of the imported data (although there can be extra fields in the table.)
CSV import and export
SQL format exports are available to any SQL server but other applications may not understand the format. MySQL (and PHPMyAdmin) therefore allow exporting in other formats. Comma-separated values are a standard which is understood by many applications (for example spreadsheets and stand-alone database management systems).
Open PHPMyAdmin and go to the table you imported above (users). Click on export and change the bullet on the left from SQL to CSV. Tick Save as file and press Go. Look for the file and open it in a text edior. Although this type of file is commonly called CSV almost any character can be used to separate the values. MySQL defaults to semi-colons which is fairly normal.
You should now be able to open the data in a spreadsheet or database application. File - Import is common. Make sure you select CSV as the format and semi-colon as the separating character.
Getting data from a spreadsheet to SQL
Open a spreadsheet program (Openoffice.org has a good free one) and use the menu (often File - Export) to export some data. As a spreadsheet is not a database table you will need to manually create a suitable table ready to hold the imported data. One possible structure would be:
- firstname
- surname
- gender
Then you can put this data into a spreadsheet and export it:
| John | Smith | Male |
| Reuben | Almeida | Male |
| Janet | Jones | Female |
This can be frustrating when it doesn't work but as long as you check a few things you should be OK:
- are you using the same separating character when exporting from the spreadsheet and importing with PHPMyAdmin?
- are there the same number of fields in the table as there are columns in the spreadsheet?
- is the data the right type to put into each field in the table (e.g. numbers in INT)
Playing
Experiment with different types of export and import. Edit the exported files manually in an editor to change the data and then import it. Try importing data to a table with data already in it.
When you have finished use the Drop tab VERY CAREFULLY to get rid of the databases and tables you have created. Drop will totally get rid of whole databases and there is no undelete so make sure you are dropping the right table/database!
