[SOLVED] How to backup all mysql database individually in single command

sanjay87 · 03-16-2012, 01:42 AM

Hi
Im trying the backup all my mysql database indidually i could int find the command .Wright now im using off follwoing command to backup all mysql databases.Can any one help me how can i get all individuall backup of single database in single mysql command.

mysqldump -u root -pPASSWORD -routines --all-databases > alldatabases.sql

TenTenths · 03-16-2012, 06:34 AM

You can't do it in a single command into separate files.

This is the script I use to create backups from a MySQL server. In this case the server is a replication slave which is why the options are there to turn off the replication before the backup and turn it back on afterwards.

The databases are listed individually in DBLIST variable to give control over exactly what is backed up.

I use it with the "day name" BACKUPDATE so that I end up with 7 days worth of backups.

Once all the databases have been dumped the SQL files are then compressed.

You can put your username and password etc. into the DUMPOPTIONS variable as needed.

Code:

#!/bin/bash

# Options
# BACKUPDIR     Location to store backups
# DBLIST        Space separated list of database names to backup
# DUMPOPTIONS   Any options you wish mysqldump to use
# BINDIR        Path to executables

BACKUPDIR=/backup
DBLIST="mydatabase1 myforumdatabase mywordpressdatabase"
DUMPOPTIONS="--lock-all-tables"
BINDIR=/usr/bin

## Script Logic Starts Here
echo `date` MySQL Backup START

# Date to append to database names

# Uncomment the line below to use full dates
# Remember to make the day of week line a comment if you want to use the full dates.
#BACKUPDATE=`/bin/date +%Y%m%d`

# The versions gives day of the week backups, eg mydatabase.Mon.sql, mydatabase.Tue.sql
BACKUPDATE=`/bin/date +%a`

# Stop accepting replication while we backup
echo `date` Stop replication
${BINDIR}/mysqladmin stop-slave

# Iterate through the list of databases and take a dump
for DATABASE in ${DBLIST} ; do
  echo `date` Dumping ${DATABASE} START
  ${BINDIR}/mysqldump ${DUMPOPTIONS} ${DATABASE} > ${BACKUPDIR}/${DATABASE}_${BACKUPDATE}.sql
  echo `date` Dumping ${DATABASE} FINISH
done

# Start accepting replication
echo `date` Start replication
${BINDIR}/mysqladmin start-slave

# Compress the backups
echo `date` Compress Backup START
${BINDIR}/gzip ${BACKUPDIR}/*_${BACKUPDATE}.sql
echo `date` Compress Backup FINISH

# Thank you and goodnight
echo `date` MySQL Backup FINISH

Kustom42 · 03-16-2012, 04:35 PM

Yes you can do it in a single command...

mysqldump --opt --all-databases > outputfile.sql

Kustom42 · 03-16-2012, 04:38 PM

A note on the above, I NEVER USE THAT!

I usually do a simple for loop so that my database dumps are to seperate files.

Code:

for db in `ls /var/lib/mysql | egrep -v "mysql|otherdbyoudontwanttobackup|anotheroneyoudont|etc.."; do mysqldump --opt $db > /tmp/mysqldumps/$db_$(date +%H-%M_%m-%d-%Y).sql; done

Kustom42 · 03-16-2012, 04:39 PM

I have a syntax error in the code, there is a missing "`" after the quote for the egrep command.

TenTenths · 03-17-2012, 08:48 AM

Quote:

Originally Posted by Kustom42

Yes you can do it in a single command...

mysqldump --opt --all-databases > outputfile.sql

Read the Original Post, he wants individual files per database rather than a single file.

Kustom42 · 03-17-2012, 01:19 PM

Yea i missed that one my first post. The for loop works perfectly for individual files