Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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"

However:

  1. I'm scared to run this with little knowledge of whats 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 in tact 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?

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
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). –  Thomas Clayson May 10 '12 at 14:41
add comment

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-drop-database
  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.

#!/bin/sh

PATH=/bin:/usr/bin
DATE="`date +%Y%m%d`"
DB="mysql drupal drupaldev"
DUMP_DIR="/u01/mysql_dump"

for db in `echo $DB`
 do
  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 '{}' \;
done
share|improve this answer
    
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. :) –  Thomas Clayson May 11 '12 at 8:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.