$ mysqldump address –add-drop-database –add-drop-table –add-locks –complete-insert –flush-privileges -R –triggers > /home/user/addressdump.mysql
Repeat above for every table in your system. You can also put it in a batch file for a series of tables.
This is the only way to restore databases if you upgrade to MySQL to a major version. This was an exception, since I never did a mysqldump, but always did a copy *.MYI and so on to backup. To overcome the problem of not restoring correctly, following steps were followed:
1.Do not overwrite the /var/lib/mysql/mysql folder.
2.Start mysql and create each database using the CREATE DATABASE command.
3.Copy your database files (*.MYI and so on) to folder in /var/lib/mysql/ individually.
4.Now, use the mysqldump command shown above to dump data for backup.