Backing up your database is absolutely vital if the data is of any importance. This will save you in the event of an accidental deletion, either due to a slip of the finger or a major failure in your code.
If you’re familiar with phpMyAdmin, then you may be able to export and import your database from there. But this approach doesn’t work too well if you have a large database. For large databases, you’re better off using the command line.
Backup Your MySQL Database
Log in to the server you wish to work with. The command you’ll need to run is as follows:
mysqldump -u username -p database > file
Replace the username, database and file parameters with the values that match your database. For instance:
mysqldump -u root -p mydatabase > /home/backups/mybackup.sql
If all goes well, you’ll be prompted for the password, and then the backup file will be created.
Note that in the above example, the /home/backups folder must exist. If it doesn’t, you’ll need to create it first.
Restore Your MySQL Database
Be careful with this, because any existing data will be overwritten. You’ll need to do the following:
mysql -u username -p database < file
If we wanted to restore from the file we just created, here’s what we’d do:
mysql -u root -p mydatabase < /home/backups/mybackup.sql
Once again, just enter the password, and the database will be restored from the file you supplied.
Photo by Tim Morgan
