Edit Article
How to Back Up and Restore Data in MySQL
Edited by Candyy., Choicefresh, Nicole Willson, Brandywine and 2 others
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.
EditSteps
-
1To make a backup:- mysqldump --user=username --password=password databasename >path/backupfilename
-
2To 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.
- myisamchk -r databasepath/databasename/tablename.MYI
- You should see output on the screen telling you which tables are being checked.
- Restart the MySQL server:
- mysqladmin -u username -p start
- Stop the MySQL server:
-
3If 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
- Delete the table - in MySQL, send the query:
EditVideo
EditWarnings
- The backup file contains all INSERT commands to restore all tables you specified when you made the backup. If you only want to restore some of the tables, you'll need to edit the backup file before running the restore command.
