Introduction
Creating backups is a fundamental process to secure the data of your systems, in this case, your database data.
In this guide, you will be shown how to export the data and structure of the tables present in a database and how to retrieve the information from the backup files, using the phpMyAdmin tool.
Creating a backup copy
To create a backup copy, just search for the ‘Export’ item after opening the desired database by clicking on its name.
By navigating through the options of the ‘Custom’ mode a ‘quick’ export method will be available. In addition to that, controlling in detail what to export will also be possible.
Obviously, the format of the backup file can also be selected from the many formats available. Then, the file can be imported into your phpMyAdmin to retrieve saved table structures and data.
It is advisable to keep several backup files on a regular basis, in order to have various stages of the database ready to be restored in case of errors or damage to your database.
Custom mode
In the customized export mode of a backup several specific options can be accessed with regards to:
- Tables to select for backup
- Outputs
- File formats
- Object creation
- Data creation
Outputs
In this section, it is possible to choose how to rename the exported database, tables or fields, as well as, the file names of the exported contents, in addition to changing the character set of the file, exporting it in a compressed format and choosing whether to execute the ‘LOCK TABLES’ statement to temporarily block the editing of tables during export.
File format
In addition to the format, some additional declarations within the exported file can also be selected. For example,comments can be included in the file header to display information on:
- Creation time
- Last updated and last checked database
- Foreign key relationships
- MIME types
Object creation
Using these features, SQL language instructions can be selected and added to the file to be exported. These instructions allow you to manage, for example, the creation or selection of a database or the insertion of tables in it, only when specific conditions are met.
Data creation
In this section, the table can be selected before its potential restore (useful to avoid conflicts of existing records in case of loading the database backup); the preferred syntax can be chosen for entering data or the SQL function to use for insertion, along with other details regarding query length or field representation notation.
Restoring a copy of the database
To restore a copy of the database, simply search for the ‘Import’ item and select the dump file to re-import, i.e. the backup file previously exported. A dump file is nothing more than a summary of the structures and data stored in the tables, which is why it is the one you refer to for backups.
Scripting
As you may have already guessed, importing a .sql file is not limited to retrieving data to be inserted into the tables, but it is an actual tool for importing external files containing all the SQL statements that may be useful to you to build or populate a database.
This possibility can be explored by studying the SQL language in depth and building, for example, scripts that, once loaded, allow you to build or fill entire databases with a simple import.
Conclusions
At the end of this guide you will have learnt how to export a copy of the tables present in your databases or reload them inside your phpMyAdmin, as well as some custom options and all the possibilities that SQL scripting offers you.