Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Can't add variable to file name in mySQL dump script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Can't add variable to file name in mySQL dump script
# 1  
Old 03-17-2010
Can't add variable to file name in mySQL dump script
I have a script (below) which works ok, but I have tried to modify it as I want to keep the older files for a restore if needed. I have tried adding a date suffix to the newly created files (second lump of code), but it doesn't seem to work.
I get the error:
$SOURCEDIR/p1db_$DATEVAR.sql: ambiguous redirect
I looked at the earlier post that used sed - but I didn't understand how that could gbe applied here.
The working original script:

Code:
#!/bin/bash
# mysql dump of wordpress website db's and export to a destination machine
#These first commands set the variables for the destination of the dumped db, an optional date extension to the file and the password for the host server.
SOURCEDIR=/home/wordpress/backups/dump
PASS=password
#This section controls the dumps from the mysql database, parsing the above variables. You can edit the commands below to include all the mySQL options or just the tables you require.
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases DBNAME1 > $SOURCEDIR/p1db.sql
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases DBNAME2 > $SOURCEDIR/jtdb.sql
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases DBNAME2 > $SOURCEDIR/ppdb.sql
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases DBNAME3 > $SOURCEDIR/pfdb.sql
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases DBNAME4 > $SOURCEDIR/pcdb.sql
#The following command activates rsync to talk to the destination backup over SSH
#You can use this to export a backup to a remote machine or,
#..you can use this command to send the mysql databases to a mirrored server. A second script will need to be run on the destination server to import the transfered db into mysql.
rsync -rtlzv --ignore-errors -e ssh /home/wordpress/backups/dump/ root@OTHERHOSTIP:/home/wordpress/backups/import

Then when trying to add date suffix with variable:

Code:
 
#!/bin/bash
# mysql dump of wordpress website db's and export to a destination machine
#These first commands set the variables for the destination of the dumped db, an optional date extension to the file and the password for the host server.
SOURCEDIR=/home/wordpress/backups/dump
DATEVAR='date +%d_%m_%y'
PASS=password
#This section controls the dumps from the mysql database, parsing the above variables. You can edit the commands below to include all the mySQL options or just the tables you require.
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases pageone > $SOURCEDIR/p1db_$DATEVAR.sql
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases pageonejanettxt > $SOURCEDIR/jtdb_$DATEVAR.sql
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases pageonepager > $SOURCEDIR/ppdb_$DATEVAR.sql
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases pageoneflare > $SOURCEDIR/pfdb_$DATEVAR.sql
/opt/lampp/bin/mysqldump --opt -u root -p$PASS --add-drop-table --lock-tables --databases pageonecontact > $SOURCEDIR/pcdb_$DATEVAR.sql
#The following command activates rsync to talk to the destination backup over SSH
#You can use this to export a backup to a remote machine or,
#..you can use this command to send the mysql databases to a mirrored server. A second script will need to be run on the destination server to import the transfered db into mysql.
rsync -rtlzv --ignore-errors -e ssh /home/wordpress/backups/dump/ root@OTHERHOSTIP:/home/wordpress/backups/import

Any ideas? Bit stumped and I can't seem to make any of the examples I've found from googling work in the scenario.

Cheers,

(not sure what's going on with the code tags - seems to be ignoring the close..)
# 2  
Old 03-17-2010
try using

Code:
p1db_${DATEVAR}.sql

instead of
Code:
p1db_$DATEVAR.sql



and you set DATEVAR wrong

use:
Code:
DATEVAR=$(date +%d_%m_%y)

Edit: perhaps you wanted to use backticks `` instead of single quotes, this would work too
# 3  
Old 03-17-2010
unsure if you are using ` or '
those two are different
` is located top left on keyboard
' is located next to enter key

you want the ` with your date command
Code:
DATEVAR=`date +%d_%m_%y`

Can you verify this?
# 4  
Old 03-17-2010
Quote:
Originally Posted by joeyg
those two are different
` is located top left on keyboard
' is located next to enter key

you want the ` with your date command
Code:
DATEVAR=`date +%d_%m_%y`

Can you verify this?
Spot on with this - I had put single quotes in. Never used 'backticks' before - bit new to all this! Swapped ' for ` and it works a treat.

It's often the little details eh? Cheers for your help!!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Copy file from local network and add it to MYSQL

Hello, i need some help. I have a Debian machine in network, which is a web server. I need to copy every day files from local network to MySQL table. Now it is done manually and i am just adding them from web panel (backend) and saving it's binary, extension and etc. Can i automaticly do this... (2 Replies)
Discussion started by: Dyrem
2 Replies

2. Red Hat

How to pass value of pwd as variable in SED to replace variable in a script file

Hi all, Hereby wish to have your advise for below: Main concept is I intend to get current directory of my script file. This script file will be copied to /etc/init.d. A string in this copy will be replaced with current directory value. Below is original script file: ... (6 Replies)
Discussion started by: cielle
6 Replies

3. UNIX and Linux Applications

MySQL Daemon failed to start - no mysql.sock file

After doing a yum install mysql mysql-server on Fedora 14 I wasn't able to fully install the packages correctly. It installed MySQL 5.1. I was getting the following error when running the: mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)... (3 Replies)
Discussion started by: jastanle84
3 Replies

4. Shell Programming and Scripting

Using a shell script variable in a mysql query using 'LIKE'

Hello. I am writing a simple script that reads a text file and removes records from a mysql database. The items in the text file are of the format: firstname.middle.lastXXX, where XXX is a 3 digit number. The table has an email field that will match the firstname.middle.last. So, I thought I... (1 Reply)
Discussion started by: bricoleur
1 Replies

5. Shell Programming and Scripting

Exporting .csv file into mysql server 2005 using script.

Hi, I have a .csv file created by a script with data in a tabular format. I need to insert all the value into mysql database which is running in a different machine. what is the command to export the .csv file into database using shell script. Thanks in advance. (3 Replies)
Discussion started by: ahamed
3 Replies

6. Shell Programming and Scripting

Passing a variable from shell script to mysql query?

I heard this was possible but from my research I haven't been able to figure it out yet. Seems it should be simple enough. Basically from a high level view I'm trying to accomplish... . $X='grep foo blah.log' then 'mysql command SELECT foo FROM bar WHERE ' . $X or something like that. ... (2 Replies)
Discussion started by: kero
2 Replies

7. Web Development

Confused maybe about MySQL Dump & PHP

Hello. the purpose of my efforts right now are to get a larger script of mine (which the admin told me he'd put into cron for me) to properly back-up my MySQL database. To test out the sql back-up part (before getting the whole script into cron, and having it not work) I wanted to test it. So... (12 Replies)
Discussion started by: jzacsh
12 Replies

8. Shell Programming and Scripting

how to break mysql dump sql file

Hi folks I have mysql dump which having insert queries, i want to break that file when 10 complete "INSERTS" lines so extract that line and store in 1.sql and 2.sql and for next 10 insert lines. pls guide me how can i do that. Regards, Bash (2 Replies)
Discussion started by: learnbash
2 Replies

9. Shell Programming and Scripting

Mysql Script File

Hi, I'm trying to develop a script file that can manipulate a mysql database. I can query the db no problem... That is until I try to create a function that will do the work for me. I've tested several scenarios to try to get around it. But I can't seem to find anything that will work when... (1 Reply)
Discussion started by: mph
1 Replies

10. Shell Programming and Scripting

restore mysql dump file in many remote servers?

Hi all, I want to restore DB file in many mysql servers, i already using script for sending the dumpfile in all servers, but it's just annoying if i have to restore the dumpfile in all servers, i want just execute 1 script, and will restore in all remote mysql servers. I make script but not... (2 Replies)
Discussion started by: blesets
2 Replies
Login or Register to Ask a Question