Backing up your database is very important in a database driven application. Ideally database should be backed up often. There are a lot of ways to accomplish this. I’ll show how to back up database using command-line commands.
The command to run the backup is:
mysqldump -u mysqluser -p mysqldatabase
“mysqldump” program is a tool for creating database backups.
The parameters used are:
“-u” switch means you’re going to specify a username to connect with, which must follow, like “-u mysqluser” above
“-p” switch means you’re either going to immediately specify the password to use (with no space), or it’ll prompt you for one
The final parameter used in the example above is the name of the database to backup
If you run the command above, you would see the contents of your database go scrolling on the screen.
To place the contents of the output into a file, execute the following command
mysqldump -u mysqluser -p mysqldatabase > db_backup.sql
Now you should be able to see a file named “db_backup.sql”, and if you open it you can see a SQL script with the structure and content of your database ready for restoration or migration.
Now compress this SQL script using GZip compression. Instead of gzip, you can use bzip, tar etc..
To add compression into this command, just execute the following command.
mysqldump -u mysqluser -p mysqldatabase | gzip > db_backup.sql.gz
Now your database backup is ready to import/export for future use.