Backup Mysql Database From Commandline

 Backing up your database is something you should do often. Like most things, there are a lot of ways to do this, but using the command-line is one of the best. Why? Because it helps you get around potential problems like execution timeouts for tools like phpMyAdmin, and potential network dropouts from using a local administration tool like MySQL Workbench.



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

take a backup in compress mode:

mysqldump -u username -h localhost -p database_name | gzip -9 > backup_db.sql.gz

(OR) 

mysqldump -u username -p database_name | gzip -c backup.sql.gz 

Now, to explain what’s going on here! The “mysqldump” program is a tool for creating database backups. The parameters being 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 (of course!)

If you ran the command above, you would’ve seen the contents of your database go whizzing by on the screen. That’s good, because we know that part works (actually connecting to the database), but it’s also bad, because… where did it go? Answer: nowhere! It scrolled past, and that was it. Now we need to capture it and put it in a file.
To place the contents of the output into a file, for back-up purposes, we need to use what’s called a redirection.