What is the best way to backup a database in MySql

We have been working with MySql version 5.0.91 and wanted to know how to make a full backup of a specific database
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
FYI:

The tools we have is Workbench 6.2 and PHPcoder for PHP (just incase PHP is better way to go)
Julian HansenCommented:
Do you want the backup to run automatically or manually.

There is always mysqldump - which can be scripted to dump the database regularly.
rayluvsAuthor Commented:
manually.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Julian HansenCommented:
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

Open in new window

Enter password when prompted and you are done.
rayluvsAuthor Commented:
In workbench, is it Server >> Data Export (see pic below), is this correct?

bk1bk2
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
Julian HansenCommented:
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.
rayluvsAuthor Commented:
Thanx!

What about "mysqldump", where do we execute it?
Julian HansenCommented:
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.
madunix (Fadi SODAH)Chief Information Security Officer Commented:
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

Open in new window

madunix (Fadi SODAH)Chief Information Security Officer Commented:
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

Open in new window

rayluvsAuthor Commented:
Please excuse our ignorance, how do we get to the command line / terminal; is this CMD or DOS prompt?
Julian HansenCommented:
The previous post assumed linux (or unix given the name of the poster)

For a Windows environment you would need to do the following

1. Update the PATH variable to point to your MySQL Bin folder - steps to do this are shown below
2. Open a command prompt or create a BAT / CMD file (see later for instructions on how to do this)
3. Type the following into your file / on the command line
mysqldump -h SERVER-u USERNAME--password=YOURPASSWORD DATABASE [TABLENAME] > name_of_backup_file.sql

Open in new window

In the above statement DATABASE is the name of your database that USERNAME / PASSWORD has access to. You can optionally add a table name if you only want to dump a specific table.

To set the PATH for your system
1. Open File Explorer and browse to the bin folder of your MySQL installation. This varies depending on version and how you installed it - on my x64 system it is here
C:\Program Files\MySQL\MySQL Server 5.6\bin

Open in new window


2. Copy this path to the clipboard

3. On Windows 7 right click Computer onn Windows 8 right click This PC in File Explorer and select properties

4. Select Advanced System Settings

5. Click Environment Variables

6. In the bottom Window scroll down to the Path entry and select it

7. Click the Edit button to open the Path settings.

8. Check to see if the MySQL bin folder is in the Path - if not add a ';' followed by the path you copied above

9. Click Ok.

To create a BAT file - open a file in a Text Editor and add the line in the first code snippet above.

Save this with .BAT extension - example dbbackup.bat

You should now be able to double click this - or call it from scheduler or whatever and it should do the backup.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Thanx!!!
Julian HansenCommented:
You are welcome thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.