Backup and restore a MySQL Database from the command line

Creating a backup from the command line on the source server

mysqldump -h localhost -u username -p database_name > backup_db.sql

Restoring this backup to an existing, empty database on the target server

mysql -u username -p database_name < backup_db.sql

When scheduling your backup with a batchfile you can use a simple trick to include a day stamp, something like below will producte a different backup file for erach day the script is run:

md \dbbackup\mysql
mysqldump -h localhost -u root --password=somepass database1 > \dbbackup\mysql\database1_%date:~-10,2%.sql

Makeing a dump of the structure for a mysql database

By adding the -d switch it is possible to make a dump of just the structure of the database without exporting data. This way you can quickly create a new database with the same structure on another server or instance.

mysqldump -h localhost -u root --password=somepass -d database1 > juststructure.sql