As per the reco of someone on this list, I created a bash script that runs once a day to do the following:
# set path to final destination location="/Volumes/foo/sql_dumps/"
# set db_list to the list of databases db_list=`echo "show databases" | /usr/local/mysql/bin/mysql -N -u**** -p****`
for db in $db_list; do echo "dumping " $db "to " $location$db.sql /usr/local/mysql/bin/mysqldump -u**** -p**** --opt $db > $location$db.sql done
echo "changing to directory " $location cd $location echo "Now in:" pwd
echo "begin gzipping and tarballing" tar -zcf $location$time.tar.gz *.sql
echo "removing:" ls -la $location*.sql rm $location*.sql
echo "All your MySql Database are Belong to Us"; echo $location$time.tar.gz
The first thing I would like to know, is what you all think of this method and how secure is it to run the username and passord in the file, if not, what other options do I have?
Seems OK if you're not happy with hot backups. The password is not very safe though, unless it's under root. Perhaps you could make a user with a bizarre user name & no password & minimum rights, just for this op. It's debatable how secure it would be.
Second question, when I do a dump out of phpmyadmin, I get 1 line at a time insert into statements, when they come from my script, I get one insert concatenated with the rest. They both work, so aside from one file being larger than the other, what are the pros and cons?
The 1line at a time file would be more easily editable.
One field in a few databases is of the type password, phpmyadmin outputs it as 0x6ad6600d88afb42e5bef276c039330cc and my script above yields something like this (×-‰€ì ¶3…?$¡
How do I made sure I have a fully restorable backup?
Try to run with the restored database (close down mysql, move the database directory, re-initialize mysql & then run your restore)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]