1. Create Database Backup:
You can use mysqldump to create a simple backup of your database using the following syntax.
mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
- [username] – this is your database username
- [password] – this is the password for your database
- [databasename] – the name of your database
- [backupfile.sql] – the file to which the backup should be written.
You can also ask mysqldump to add a drop table command before every create command by using the option –add-drop-table. This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.
mysqldump --add-drop-table -u [username] -p [password] [databasename] > [backupfile.sql]
If you want to back up certain tables
mysqldump -u [username] -p [password] [databasename] [table1] [tableN]> [backupfile.sql]
If you want to back up multiple databases
mysqldump -u [username] -p [password] [databasename1] [databasenameN] > [backupfile.sql]
Or want to back up all databases
mysqldump -u [username] -p [password] --databases-all > [backupfile.sql]
2. Restore from database backup
Restore a particular database
mysql -u [username] -p [password] [database_to_restore] < [backupfile]
Restore all databases
mysql -u [username] -p [password] < [backupfile]