What is the best way to backup a database in MySql

FYI:

The tools we have is Workbench 6.2 and PHPcoder for PHP (just incase PHP is better way to go)

Do you want the backup to run automatically or manually.

There is always mysqldump - which can be scripted to dump the database regularly.

manually.

You can use the Data Export functionality in Workbench

Other tools like Heidi and SQLYog also have export functionality

The following will do it as well. Remember to prefix with path if mysqldump is not in the path

mysqldump -h server -u username -p databasename > database_dump.sql

Select allOpen in new window

Enter password when prompted and you are done.

In workbench, is it Server >> Data Export (see pic below), is this correct?


Note that we circle in red to know if that is correct to go.  

Also, if wanted to backup triggers and procedures, just mark them?

Lastly, where do we execute mysqldump?

please advice

Yup - I would change to a self-contained file though - easier to move around / archive etc

So under Export options just click the bottom radio - self contained file.

Thanx!

What about "mysqldump", where do we execute it?

Mysqldump is run from the command line / terminal

You can also put it in a batch file / shell script and have a scheduler / cron job kick it off.

1 0 * * * /root/MysqlScripts/MysqlDumpScript.sh

[root@sdc MysqlScripts]# cat MysqlDumpScript.sh 
mysqldump -u backup --password=xxxxxx --all-databases --add-locks > /root/MysqlScripts/mysqldump.db

Select allOpen in new window

Or you could have this script; set the permissions for the script carefully

#!/bin/sh
Today="`date +%m%d%y%H%M`"
mysqldump -uUserBackup -pYourPassword --all-databases | gzip -cq > /path/to/storage/sqldmp$Today.gz

Select allOpen in new window

Please excuse our ignorance, how do we get to the command line / terminal; is this CMD or DOS prompt?

Thanx!!!

You are welcome thanks for the points.