back up database

just_nobody

how can I back up a database?

in phpadmin I can export the database as a ms Exel file or as a CVS file, but not as a sql file.

If I export it as a CVS, can I import it back then when somethinig goes wrong?

Or do you ppl have an other way of backin up a MySQL Database?

Can you plz give me some tips? I'm new to this :o

herve

Hi,

Yes, what you need is "mysqldump"

here you'll find more info about this utility :

http://www.mysql.com/doc/en/mysqldump.html

Regards,
Hervé.

Ph0eniX

Someone asked about that in this forum a few days ago. Just go back, search for the post and find my reply. That should help you.

just_nobody

Ph0eniX, i found a code from you, but I don't really get it :o

#!/bin/sh
databases="dbname"
backupdir=/export/home/sqlbackup
mysqldumpcmd=/opt/sfw/mysql/bin/mysqldump

userpassword=" --user=username --password=password"
dumpoptions="--quick --add-drop-table --add-locks --extended-insert --lock-tables"
echo "Dumping MySQL Databases"
for database in $databases
do
   $mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done
echo "Compressing Dump Files"
for database in $databases
do
   rm -f ${backupdir}/${database}.sql.gz
   gzip ${backupdir}/${database}.sql
done
ls -l ${backupdir}
echo "Dump Complete!"
exit

Do I put that in a script and upload it to the host?

herve, thnx for the site, but it are all shell commands... I think... I have no idea how to use them 🙁

And euhm If I manage to save my db as a sql file, how do I upload it back to the host then?

Ph0eniX

Originally posted by just_nobody
Ph0eniX, i found a code from you, but I don't really get it :o
#!/bin/sh
databases="dbname"
backupdir=/export/home/sqlbackup
mysqldumpcmd=/opt/sfw/mysql/bin/mysqldump

userpassword=" --user=username --password=password"
dumpoptions="--quick --add-drop-table --add-locks --extended-insert --lock-tables"
echo "Dumping MySQL Databases"
for database in $databases
do
   $mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done
echo "Compressing Dump Files"
for database in $databases
do
   rm -f ${backupdir}/${database}.sql.gz
   gzip ${backupdir}/${database}.sql
done
ls -l ${backupdir}
echo "Dump Complete!"
exit
Do I put that in a script and upload it to the host?

herve, thnx for the site, but it are all shell commands... I think... I have no idea how to use them 🙁

And euhm If I manage to save my db as a sql file, how do I upload it back to the host then? [/B]

That's a Unix shell script (might work on Linux though I haven't tried it). It backs up one database of your choice, compresses it into a .gz file and removes old backup files in the backupdir. You need to upload it to the ISP and tell them that you'd like to have it scheduled to run daily, nightly, weekly - whatever the case may be unless, of course, you have telnet access to the server and can schedule it yourself using cron. They shouldn't object but before the script works for you, you need to replace "dbname", username, password and backupdir with the correct info.

See the one-line batch file in that same post if your ISP is running NT. That's the most basic backup script for Win32 but it'll do the job.

Hope this helps.

just_nobody

yeah now I get it 🙂

but euhm, I have one of those free hosts, can't aford an other one for the moment, my ISP doesn't support PHP and MySQL or I have to pay more 🙁 So I signed up for a free host and euhm they don't answer mails and stuff, so they won't accept the script 🙁 They say you get what you pay for so I can't really complain...
I can only upload php scripts...

So is there an other way maybe?
And what if there is a way, and I can save the file to my pc, how can I upload it back then?

Thnx for the help so far 🆒

Ph0eniX

Originally posted by just_nobody
yeah now I get it 🙂

but euhm, I have one of those free hosts, can't aford an other one for the moment, my ISP doesn't support PHP and MySQL or I have to pay more 🙁 So I signed up for a free host and euhm they don't answer mails and stuff, so they won't accept the script 🙁 They say you get what you pay for so I can't really complain...
I can only upload php scripts...

So is there an other way maybe?
And what if there is a way, and I can save the file to my pc, how
Thnx for the help so far 🆒

In this case you're asking for a lot. But you can try uploading and running the following script (modify the host, user, password and dbname). The script will time out if the database is very big and it will not work if the user account that php executes as doesn't have enough privilege.

<?php
    $fpath = getcwd(),"backup.sql";
    echo $bckp = exec('mysqldump --quick -hlocalhost -uuser -ppassword dbname >,$fpath');
?>

Then you can just download the backup.sql file to your computer via FTP.