How to backup and restore MySQL database

Two ways to take backup of existing MySQL database:

1. Exporting tables to text files using MySQL dump.
2. Copying database files. (within same version) not recommended.

 

Exporting tables to text files using MySQL dump:

 

This is the best solution to keep backup of database so that we can restore it to any version of MySQL in any platform.

 

Syntax for exporting database to text files:
mysqldump -u root -p --all-databases > C:\MySQL_Backup.sql

Syntax for restoring database:
mysql --user=root --password=password < c:\ MySQL_Backup.sql

Copying database files:

 

MySQL stores all the tables in data directory which is divided table-wise, where table and table indexes are represented in the form of files. This approach is only valid for moving data between same version of MySQL.

 

Keep a copy of data directory so that we can just place it while restoring database. This process is valid only for moving database within same version.

 

Note: Unix file names are case sensitive whereas windows are not. So tables with names in mixed case are corrupted when we move database and tables from window to UNIX. If we use mixed case names in database and tables then it might not work properly. For best results ALWAYS use lower case names.