How to take Mysql server backup to local machine?

kandalu

Hi, I developed a website in asp.net with mysql. I have to take backup of the server data to my local machine. I am new to this concept. Please give me an idea which concept i have to use. Thanks.

JoseMenendez

you can use the mysqldump.exe utility, it should work like this: mysqldump -hserver -uroot -pmypassword databasename

Jm www.menendezpoo.com

kandalu

Hi, Thanks for your reply. I executed the command you have given. But it shows the syntax error. My website is in shared hosting server. I saw in the mysql forum that mysqldump cannot be used in shared hosting. Could you please give me other idea to take backup of the server data? Thanks.

mike3169

I run a cron job that runs a web based cgi script each night. It backs up my MySQL database to a directory on my server. I then offload that file along with others nightly with another cronjob backup script to tape. Or at the very least you could download the *.sql file that is created by the cgi script each night. Here is the cgi script I use. Save and upload it as YOUR_DB_NAME.cgi to your web server's cgi-bin directory

#!/bin/sh # uncomment to run as UNIX script. File must be executable.

Edit this file and put correct string for following items

MYSQLUSERNAME1&2 MYSQLPASSWORD1&2 mysqlXX1&2 DATABASE_NAME1&2

Don't forget to set permission of 755 to this script.

You can do that with WebFTP, SiteTools, most FTP software, other utilities.

echo # Send text/plain Content-type header.
exec 2>&1 # Senf error output (stderr) to the browser

filename=/path/where/you/save/database_file/DATABASE_NAME1.sql # ex.: dbbackup_2006-03-07.sql --add after name for date--> _`date "+%Y-%m-%d"`

Dump DB

mysqldump --opt --no-create-db --user=MYSQLUSERNAME1 --password=MYSQLPASSWORD1 --host=localhost DATABASE_NAME1 > $filename
#echo "Database DATABASE_NAME1 saved in $filename."

You can actually run that script from your web browser. e.g. http://www.yoursite.com/cgi-bin/YOUR\_DATABASE\_NAME.cgi