How to backup and restore data in mysql database

Jul 27, 2012, by admin

Multiple backups should be taken. E.g.

  One copy on the same computer or locally networked computer that can be used to quickly replace a database that is in use.

    Another copy in a physically different location in case something happens at the current location (fire, flood). This could be another computer at a remote site or some portable medium like a tape or CD.

If using Windows, open a command prompt window. If using Linux, execute the commands on the command line. The examples below are for Linux – convert backslashes and add .exe to the command for the Windows equivalents

Steps to backup and restore data in mysql database

1.To make a backup:

• mysqldump –user=username –password=password databasename >path/backupfilename

2.To repair a table: 

•     Stop the MySQL server:
•        mysqladmin -u username -p ‘password’ shutdown
•    Start the repair:
•        myisamchk -r databasepath/databasename/tablename.MYI
•             databasepath – The complete path to your data directory.
•             tablename can be “*” which means fix all tables.
•     You should see output on the screen telling you which tables are being checked.
•     Restart the MySQL server:
•         mysqladmin -u username -p start

3.If the repair doesn’t work, you may need to delete the table and restore from the backup file: 

•   Delete the table – in MySQL, send the query:
•         DROP TABLE tablename
•     Back at the command prompt:
•         mysql -u username -p databasename < /path/backupfilename