Easy way to backup all MySQL db on server?

[embsupafly]

Is there an easy way to backup all of the MySQL databases for all accounts on the server. I am going to upgrade from 4.0.x to 4.1.x and need to backup all of the dbs before doing so.

 

[kistler]

You can use the myself dump in ssh but it not 100% easy if you haven’t done it before

Easy maybe...
http://www.phpmybackuppro.net/ - very fast and doesn’t use much resources


When I updated mysql, I didn’t have an issue with any account – I’d still backup

 

[apea2rh]

You could just do this as root through ssh:

cp -R /var/lib/mysql/ /backup/tmp/mysql/

 

[elix]

You might also want to create a tar.gz file for each database, as opposed to huge archive.

You can use the following bash script to accomplish this:

#!/bin/bash


## Specify the directory for these backups, be sure to make sure it exists.
BACKUPDIR="/backup/sql"

clear

echo -n "Creating archives on a per database basis..."

for i in `ls /var/lib/mysql`
do
   tar -czf $BACKUPDIR/$i.tar.gz $i
   echo -n "."
done
echo -n "Process complete."

exit 0

 

[mohit]

HI,
and how do one restore the files available under the /var/lib/mysql/user_db/
the files are :-

*.MYI
*.MYD
*.frm

any steps to get this back working if you are restoring the DB's manually.

regards,
mohit