1. MyISAM vs InnoDB
2. Backup and restore MySQL databases with PhpMyAdmin
3. Backup and restore MySQL databases with mysqldump.exe

Environment :

• Relational database management system : MySQL
• Storage engine : InnoDB / MyISAM
• Database management : PhpMyAdmin
• Operating system : Microsoft Windows
• WAMP stack : EasyPHP

With MyISAM, it’s extremely easy to backup / restore your databases. Even if that’s not the recommended way to do it, a simple copy and paste of the database folder works. However, the better solution is to use the “export” feature of PhpMyAdmin or the dump the databases with mysqldump.exe.

1 MyISAM vs InnoDB

Unlike MyISAM, InnoDB databases cannot be copied individually. If you do so with the entire folder, it’ll work. But you cannot do it with just one or several databases.  This part will explain why.

InnoDB propose to ways of storing the tables with the parameter innodb_file_per_table.

With innodb_file_per_table disabled, InnoDB stores the data for all tables, indexes and metadata in the system tablespace (ibdata1, ibdata2 … files).

When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each table in a separate .ibd file next to the .frm file, rather than in the system tablespace. For example, for the table mytable, you will have two files: mytable.frm and mytable.ibd. However, all the metadata about mytable will still reside in the system tablespace.

In conclusion, with innodb_file_per_table enable or not, copying an individual InnoDB table is totally impossible because part of the data of these tables is mixed with all other tables in  the ibdata files.

If you want to backup your databases (one, several, or all), use PhpMyAdmin or mysqldump.

2 Backup and Restore MySQL Databases with PhpMyAdmin

Using PhpMyAdmin is an easy way to export (backup) and import (restore) your databases. Below are the steps you need to follow.

1. Login to phpMyAdmin
2. Click on the tab “Export”
3. Export Method: select “Quick – display only the minimal options” (default option) if you want to quickly backup all your databases, or “Custom – display all possible options” if you want to select the databases that you want to backup and use more options. We recommend the second option.
4. Output:
   • choose “Save output to a file”
   • tick “Save on server in the directory” only if you want save your backup on the server. If you don’t tick this option the system will ask you where you want to save your backup (recommended).
   • if you want to save space (and time) tick “Compression” and choose one of the options.
5. Format: keep “SQL”
6. Format-specific options: keep default settings
7. Now click “Go” and you should be prompted for a file to download (if you didn’t  tick “Save on server in the directory”). Save the file to your computer. Depending on the databases size, this action may take time.

If you wan to restore a backup, you just need to click on the tab “Import”, select your backup and “Go”. If you import a large file, select “Partial Import”.

3 Backup and Restore MySQL Databases with mysqldump.exe

Backup your databases

1. Windows command prompt
   • Open the “Start” menu and click “Run…”
   • Enter “cmd” into the dialog box and click “OK”
2. Change the directory to access the mysqldump.exe program.
   cd <your installation path>\mysql\bin
For example, if you use EasyPHP: C:\Program Files\EasyPHP-DevServer-xx\binaries\mysql\bin
3. Run the mysqldump.exe program using the following arguments:
   mysqldump –u [username] -p [password] -h [hostname] --databases [databasename] > [backupname].sql
Replace [xxx] with the proper information.
   For example, if you use EasyPHP: mysqldump.exe -u root -h 127.0.0.1 --databases mydatabase > mybackup.sql

   Note 1: by default with EasyPHP, there is not password for the root, that’s why there is no option -p [password] in this example.
   Note 2: if you don’t specify any path for your backup, the backup will be saved in the same folder as mysqldump.exe. If you wan to save your backup  somewhere else, you need to specify a path: mysqldump.exe -u root -h 127.0.0.1 mydatabase > C:\mybackup.sql
   Note 3: if you want to backup several databases, use --databases mydatabase1 mydatabase2 mydatabase3 …
   Note 4:  if you want to backup all databases replace --databases [databasename] by --all-databases and don’t specify any database name

Doing so, the program will connect to your current MySQL server and create a dump of your databases.
Note: there is no message that will indicate the dump has been completed. You need to check your folder.

Restore your databases

1. Windows command prompt
   • Open the “Start” menu and click “Run…”
   • Enter “cmd” into the dialog box and click “OK”
2. Change the directory to access the mysqldump.exe program.
   cd <your installation path>\mysql\bin
   For example, if you use EasyPHP: C:\Program Files\EasyPHP-DevServer-xx\binaries\mysql\bin
3. Run the mysqldump.exe program using the following arguments:
   mysqldump –u [username] -p [password] -h [hostname] --databases [databasename] < [backupname].sql
Replace [xxx] with the proper information.
   For example, if you use EasyPHP: mysqldump.exe -u root -h 127.0.0.1 --databases mydatabase < mybackup.sqlNote 1: the only difference compared to the backup procedure is the “<” (“>” for backup, “<” for restoring).
   Note 2: if you have a table name present in your database that matches a table name in the .sql file you want to import, the table will be overwritten with the new data. Otherwise the script will create a new table and populate it with the new data.

If you want to know all other features and commands available, read the MySQL Reference Manual.