MySQL Backup and Restore – Using Command Line

Here is how you backup database using command line. The first line is to backup and the second line is to restore.
These commands work on both Windows and Linux. In Windows you might want to setup mysql bin directory the environment PATH, you can do this easily by pressing Window + Break > Advanced System Settings > Environment > PATH. Then run the cmd or the command prompt. If you already open a command prompt before you setup the PATH, you need to restart your command prompt

MySql doesn’t export stored procedure/function by default. It also doesn’t export triggers. If you need those, the you’ll need to add “–routines –triggers” to your command. Even if you don’t use stored procedure, function, or triggers it is safer if you issue your backup with those option enabled.

$ mysqldump -uusername -ppassword --routines --triggers --lock-tables=false database_name > YOURSQLFILE.sql
$ mysql -uusername -ppassword database_name < YOURSQLFILE.sql

If you need to backup all database. The user that we usually use to backup all database is "root". This will backup all triggers, and functions. You will need to pre-create other users that has certain privilege over stored procedure or functions.

mysqldump -uusername -ppassword --opt --events --all-databases > alldb.sql
mysql -uusername -ppassword < alldb.sql