Syntax when backing up mysql db?

**AndrewT** · 12-03-2005, 04:08 PM

That's just a part of the way that the utility works.

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

**ZYV** · 12-06-2005, 02:33 AM

I use the following syntax:

Code:

mysqldump -u user --password=password --result-file=file.sql --add-drop-table --quote-names --compress --extended-insert --skip-comments --databases bases

That saves you from using pipes/output redirection when not needed. Not sure why exactly, but I remember I've had problems with > file.sql syntax.

**ZYV** · 12-06-2005, 02:37 AM

P.S. The first syntax is a complete rubbish. What it does is just to run the mySQL command-line client and it would obviousely freeze your terminal since you are not giving mySQL any commands and also use output redirection. Try the same without > ... and see you'll get the greeting.

**ChrisTech** · 12-12-2005, 10:37 AM

Not to hijack the thread, but does anyone have a cron job set to backup their sql database?

**-Oz-** · 12-12-2005, 08:22 PM

my old one with a php script doesn't work anymore, db is too big so i'd like a better one.

**russgriechen** · 12-12-2005, 11:14 PM

Bash Script ...from drupal user

Drupal.org

http://drupal.org/node/434

Using a Shell Script to Backup Drupal on mySQL <node/434>
Rick Cogley <user/6006> - January 29, 2005 - 06:10
Here is a script you can set up on your system, and run by cron.

Code:

#!/bin/sh

#

# dumpdrupalwithexclusions.sh - grab all tables except grepped out

# and dump to a file

#

# Delete dump

rm -f ~/backups/drupal01.dump

# Dump structure to file

mysqldump -uMySQLadminAccount -pMySQLAdminPassword -d -e -q --single-transaction --add-drop-table 
DrupalDBname >> ~/backups/drupal01.dump

# Dump tables with data except excluded tables and append to file

for TARGETTBL in $(echo "show tables" | mysql -u MySQLadminAccount -pMySQLAdminPassword 
DrupalDBname | grep -v -e "access\|access_log\|cache\|search_index\|statistics\|watchdog
\|Tables_in_")

do

echo "Dumping ${TARGETTBL} ..."

mysqldump -uMySQLadminAccount -pMySQLAdminPassword -e -q --single-transaction --add-drop-table 
DrupalDBname ${TARGETTBL} >> ~/backups/drupal01.dump

done

Some notes: ///from Roger....
The --single-transaction switch tells mysqldump to dump as a snapshot, which is said to be better for integrity. There is
apparently a chance that a table being updated during the dump could cause you trouble later.
The first mysqldump uses -d, which tells it to not get row info. Basically you just get structure. If you run this separately, it
yields a 30K file. Removing the -d dumps the rows as well, and that brings the size of the dump up to about 1MB.
Excluding the various tables brings the size down from 5MB to about 1MB on a fairly new install with just a bunch of quotes
(fortunes) loaded and a few pages of content.
To use, the above (of course) would be saved to a file (I use ~/utils to store scripts) and you need to chmod 755 it so it is
executable. I can run it manually from my shell using ./scriptname.sh
Once the script is working, use cron to schedule it.
This could be improved by:
Abstracting some of the strings into variables, such as password
Naming dump with a date to avoid overwriting if you choose to not delete, or, if you want to set up more complex backup
scenarios.
Rick Cogley :: rick.cogley@esolia.co.jp <mailto:rick.cogley@esolia.co.jp>

Tokyo, Japan
login <user/login> or register <user/register> to post comments
Improved Version of Backup Shell Script - with Variables <node/434>
Rick Cogley <user/6006> - January 29, 2005 - 23:22
Here is an improved version with variables you can set.

Code:

#!/bin/sh

#

# dumpmysql.sh - script for backing up your mysql databases

# - Update Variables before use

# - Loops through specified DBs less exception tables and dumps to files with dates

#

# SET VARIABLES

# - List MySQL DBs to backup separated by space

# - Set other variables

# - Note quotes not needed on commands or path

databases="drupaldb01 drupaldb02"

stripstring="access\|access_log\|cache\|search_index\|statistics\|watchdog\|Tables_in_"

backupdir=~/backups/

mysqluidpw="--user=mysqladminid --password=mysqladminpass"

mysqldumpcmd=/usr/bin/mysqldump

mysqlcmd=/usr/bin/mysql

mysqldumpoptions=" --quick --add-drop-table --add-locks --extended-insert --single-transaction"

gzip=/bin/gzip

dateandtime=`date +%F-%H%M%S`

# Create backup folder if it does not exist

mkdir -p ${backupdir}

# Loop through specified databases and dump excluding certain tables

for MYSQLDB in $databases

do

echo "Dumping ${MYSQLDB} ..."

for TARGETTBL in $(echo "show tables" | $mysqlcmd $mysqluidpw ${MYSQLDB} | grep -v -e 
$stripstring)

do

echo " --- ${TARGETTBL}"

$mysqldumpcmd $mysqluidpw $mysqldumpoptions ${MYSQLDB} ${TARGETTBL} >> ${backupdir}/${MYSQLDB}-
$dateandtime.sql

done

echo "Compressing ${MYSQLDB} dump ..."

rm -f ${backupdir}/${MYSQLDB}-$dateandtime.sql.gz

$gzip ${backupdir}/${MYSQLDB}-$dateandtime.sql

done

ls -l ${backupdir}

Enjoy.
Rick Cogley :: rick.cogley@esolia.co.jp <mailto:rick.cogley@esolia.co.jp>

Note: Help me clean up this code.
and post a cleaned up version.
I noticed there is some php echo code. and probably should be enclosed in tags...or is this some sort of pipe?
So have your way with it and it will help me as well as you.

**ChrisTech** · 12-13-2005, 12:25 AM

This is what I was using, obtained from (www.kionic.com)

I get a "Permission denied" when the cron ran last nite.

Code:

#!/bin/sh

#############################################################
# File:		 dbbackup.sh
# Description:  A shell script to backup Multiple MySQL database
# Features:	 The script can create a MySQL dump, compress it
#			   then both store a copy in the server and send a
#			   copy to the defined email address
# Supported OS: FreeBSD and RedHat, should work with other distributions
#
# Version: 0.4
# License: GPL
#
#How to install:
#
# 1. Upload dbbackup.sh to your account. i.e. in your home directory
# 2. Set the permission for the script to 755
#
#Cron:
#
#  Example: This will cause the cron to run every day at 2AM.  Be sure
#  to change "/path/to" to the location where you placed dbbackup.sh.  
#
#  0 2 * * * sh /path/to/dbbackup.sh 
#
#
#  BY KIONIC INC (www.kionic.com)
#  Questions, comments, suggestions, or improvements please send to
#  any contact below
#
#
# Modifications:

# 12/07/05:	Jayesh (jayesh@kionic.com)
#		Version 0.4
#		Now have option to bzip as well as gzip as per your need
#
# 11/29/05:	 Jayesh (jayesh@kionic.com)
#		Version 0.3
#		Added the capability of the script to backup multiple databases
#
# 11/27/05:	Stephen (Stephen@kionic.com)
#		Inserted comments
#
#		Jayesh:
#		Cleaned code
#
#		Prasul (Prasul@kionic.com)
#		Version 0.2
#		Added emailing capability
#
#
# 11/26/05: 	Jayesh (Jayesh@kionic.com)
#		Version 0.1
#		Script created to backup a MySQL database
#############################################################


####DEFINE THESE#############################################

#Enter the cpanel username here
USER="control panel username";

#Enter the cpanel password here
PASSWORD="control panel password";

#Enter the database names here
DATABASES="dbname1 dbname2";

#Zipping options. 1 means you will get mail with a bz2 copy else you will get a gz copy bz2 is more powerful.
BZIP="0";

#Enter the full path to where you would like to store the backup here
#i.e. make a folder in your home directory, example: dbbackup
#then the path is /home/username/dbbackup
PATH="/path/to";

#Enter the email address you would like to receive the backup
EMAIL="youremail@here.com";

####END CHANGE HERE###########################################

#The main function which will create the required backup and send the mail
backup ()
{
	DATE=`/bin/date "+%d-%m-%Y"`;
	DUMP="$DATABASE-$DATE";
	EXT1=".sql";
	EXT2=".tar";
	EXT3=".gz";
	EXT4=".bz2";

	#Locate the mysqldump and mail commands
	if [ -f /etc/redhat-release ]
	then
			   MAIL="/bin/mail";
	else
			MAIL="/usr/bin/mail";
	fi
	
	if [ -e "/usr/bin/mysql" ]; then
			MYSQLDUMP="/usr/bin/mysqldump";
	else
			MYSQLDUMP="/usr/local/bin/mysqldump";
	fi

	#Create the MySQL dump
	echo "$PATH/$DUMP$EXT1";
	$MYSQLDUMP --add-drop-table --user $USER --password=$PASSWORD $DATABASE >> "$PATH/$DUMP$EXT1";

	#Compress the dump
	cd $PATH;
	/bin/tar -cf $DATABASE-$DATE$EXT2 $DATABASE-$DATE$EXT1;
	
	echo $BZIP;

	#Zip and Send mail - as an attachment
	if [ $BZIP -eq "0" ]
	then
		/bin/gzip $DATABASE-$DATE$EXT2;
		/usr/bin/uuencode $DUMP$EXT2$EXT3 $DUMP$EXT2$EXT3 |$MAIL -s "Mysql Backup For $DATABASE" $EMAIL ;
	elif [ $BZIP -eq "1" ]
	then
		/usr/bin/bzip2 $DATABASE-$DATE$EXT2;
		/usr/bin/uuencode $DUMP$EXT2$EXT4 $DUMP$EXT2$EXT4 |$MAIL -s "Mysql Backup For $DATABASE" $EMAIL ;
	fi

	#Remove the mysql dump (but still keep the compressed copy)
	/bin/rm -f $DUMP$EXT1;

	#Task complete
	cd;
}

# Function which backs up database one by one.
for DATABASE in $DATABASES
do
	echo $DATABASE;
	backup;
done

exit;

**sdjl** · 12-13-2005, 04:26 AM

I posted this link last night and for some reason it didn't appear:

http://sourceforge.net/projects/automysqlbackup/

David

**Pedja** · 12-19-2005, 01:39 AM

How about restoring backuped database?

I am quite familiar with the process, but there is one problem I do not know how to deal with: UTF8. My tables are filled in with unicode, and when I do backup and restore, characters out of ASCII are messed up.

**ZYV** · 12-24-2005, 03:29 AM

Pedja, that's quite strange. Actually I restored a dump of a Russian forum yesterday (via mysql < ...) and it worked fine, no problems with Unicode, didn't tweak anything at all. I was amazed that it was that simple, but it shows up as greek in phpmyadmin, although I don't care because it works

Syntax when backing up mysql db?

Syntax when backing up mysql db?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment