I want to backup all databases, users and etc on our MySQL sever.

I have come across the command mysqldump and believe I can use it like this? (Trying to backup a REMOTE server to our LOCAL machine)

mysqldump -h web-server.com -u root -p root --all-databases > "/backups/mysql/`date +%Y%m%d`.sql"


  1. I'm scared to run this with little knowledge of what's happening (we have a lot of data on the MySQL database and we can't afford to lose it!). So first question is: Will this do what I want? Log into the remote server (located at web-server.com for example) and backup all the mysql databases to the path /backups/mysql on the LOCAL machine?

  2. I'm not convinced that this will also keep all the user information for each database intact in the event of a MySQL server failure. Is user information just stored as another database in the MySQL databases? Or is it a function of the server itself? In this case is there a different way I can backup the database to include the user data too?

2 Answers 2


1) Yes, that will dump the contents of all databases as a series of INSERT sql commands so you can restore the whole lot with mysql < backup.sql. Purely for wieldability's sake, I'd recommend you do this per database rather than using --all-databases as you get one honking big file containing EVERYTHING and it's not easily searchable.

You may want to look at the mysqldump manual page to see if there are any other options you'd like to use.

2) User information and access control is stored in the mysql database; --all-databases does include that one.

  • Ah, brilliant, thanks for the answer. :) Is there an easy way of looping through all the databases and backing up each one separately? Given that we may add new databases at any time (and take them away just as easily). Commented May 10, 2012 at 14:41

If you are running mysqldump remotely and want a separate dump file for each database, you will have to maintain a list of the databases you want to back up. If you run mysqldump locally on the database server, you can loop through the databases that exist in the mysql data directory (such as /var/lib/mysql). If mysqldump has to be run remotely, you could write a script that connects via SSH, retrieves a list of the databases, then disconnects and runs mysqldump with the database names.

I would not use --all-databases without specifying individual database names and would additionally use the option --add-drop-database. This will also include SQL statements in the dump file to create the database if it doesn't exist, or drop the database if it does exist and recreate it. From man 1 mysqldump:

  Add a DROP DATABASE statement before each CREATE DATABASE statement.      
  This option is typically used in conjunction with the --all-databases 
  or --databases option because no CREATE DATABASE statements are written
  unless one of those options is specified.

This is a quick and dirty script designed to run locally on the database server that will backup the databases specified in the $DB variable resulting in a dump file for each database. It also removes any previous dumps files that are older than 15 days.


DATE="`date +%Y%m%d`"
DB="mysql drupal drupaldev"

for db in `echo $DB`
  mysqldump -u root --password=password --databases --add-drop-database $db > $DUMP_DIR/$db.$DATE.dmp
  gzip -9 $DUMP_DIR/$db.$DATE.dmp
  find $DUMP_DIR/* -mtime +14 -exec rm -f '{}' \;
  • Thanks for this. I reckon I could do it on the server and rsync it to the local machine. :) I need to find a way to retrieve a list of all the databases though and do one at a time. :) I'll look into that. Thanks mate. :) Commented May 11, 2012 at 8:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .