Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Modification of MySQLDump-files before compression needed

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Modification of MySQLDump-files before compression needed
# 1  
Old 03-06-2013
[Done] Modification of MySQLDump-files before compression needed
Hi @all!

In my MySQL-backup-script I backup and compress every single table with this command:
Code:
/usr/bin/mysqldump --opt database_x table_y | /usr/bin/pbzip2 -c > "/media/BackUpDrive/Backup/table_x.gz"

Unfortunately these files need modification - they have to start with the following line(s):
Code:
CREATE DATABASE  IF NOT EXISTS `Database_X` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `Database_X`;

which is not supported by mysqldump (when exporting single tables)
With the echo-command I can produce the files I want, but all the linebreaks are missing - causing the file to crash at import...
Code:
#!/bin/sh
UseAnweisung="CREATE DATABASE  et cetera";
echo $UseAnweisung `/usr/bin/mysqldump --opt database_x table_y` | /usr/bin/pbzip2 -c > "/media/BackUpDrive/Backup/table_x.gz";

so I wrote a new script using "cat" - but there I could only get through by writing the data to the disk:
Code:
UseAnweisung="CREATE DATABASE  et cetera"
cd /home/backupuser/DB_Backup/tmp
echo $UseAnweisung > USE_Anweisung
/usr/bin/mysqldump --opt database_x table_y > SQL_Anweisung
cat USE_Anweisung SQL_Anweisung | $GZIP -c > "/media/BackUpDrive/Backup/table_x.gz";

so ... is there anybody out there, who can do this without creating the two helper-files?
thx

g
Last edited by gogo555; 03-07-2013 at 11:21 AM.. Reason: Thread marked as [Done] - thx
# 2  
Old 03-06-2013
just curious, did you try with
Code:
"--add-drop-database"

and what version of mysql are you using?
# 3  
Old 03-06-2013
Quote:
Originally Posted by gogo555
With the echo-command I can produce the files I want, but all the linebreaks are missing - causing the file to crash at import...
Just wrap the variable in quotes (otherwise echo will interpret the string as a bunch of separate arguments):

Code:
[user@host] cat test.sh
#!/bin/ksh

UseAnweisung='CREATE DATABASE  IF NOT EXISTS `Database_X` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `Database_X`;'

echo "${UseAnweisung}"
[user@host] ./test.sh
CREATE DATABASE  IF NOT EXISTS `Database_X` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `Database_X`;

Last edited by CarloM; 03-06-2013 at 09:19 AM..
# 4  
Old 03-06-2013
Server version: 5.5.29-0ubuntu0.12.04.1 (Ubuntu)
mysqldump Ver 10.13 Distrib 5.5.29, for debian-linux-gnu (x86_64)
Code:
"--add-drop-database"

does NOT add the drop or create-command even using the --skip-opt option and unless exporting a whole database or a single table.

@CarloM:
sry my question was not precise enough:
There's no problem in the first 2 lines, but with the .sql-file generated by:
Code:
... `/usr/bin/mysqldump --opt database_x table_y` ...

this command produces a text-output with linebreaks that vanish when the echo command combines USE_Anweisung and the text-output

What the script should do is:
1. create a dump-text-output with MySQLDump
2. add a (short) text-prefix in front of the output from #1
3. compress it to somwhere
Smilie ... avoid writing temporary files to disk ... if possible.

... thx for the quick replys!
# 5  
Old 03-06-2013
You'll need more code. You may try something like this.
The code assumes that:
- your shell supports ANSI escape sequences, otherwise you'll need to enter a literal TAB in the first assignment
- your shell supports the ${variable/pattern/string} parameter expansion
- your client is configured to connect to MySQL directly (i.e. it doesn't prompt for username and password)

Code:
_tab=$'\t'
mysql -BNe'show databases' | 
  while read _db; do 
    mysql -BNe"show create database $_db; show tables from $_db" 
  done |
    while read _data; do
      case $_data in
        ( *"CREATE DATABASE"* ) 
          _db_ddl=${_data#*$_tab}
           _db_ddl=${_db_ddl/DATABASE/DATABASE IF NOT EXISTS}      
          _db=${_data%%$_tab*}
          continue ;;        
      esac
      { 
        printf '%s;\nuse %s;\n' "$_db_ddl"  "$_db"
        mysqldump "$_db" "$_data"  
        } | 
          gzip > "$_db"_"$_data".dmp.gz
      done

The script will back up all tables from all databases. If you want to backup only a specific database(s),
you could use:

Code:
show databases where `Database` in (db1, db2, ...)

Last edited by radoulov; 03-06-2013 at 11:40 AM..
# 6  
Old 03-06-2013
to avoid further complications:

my script is working - without any problems!

Code:
#!/bin/sh

BACKUPDIR="/home/backupuser/DB_Backup";
DBFILTER="(information_schema|main_db|department_db|offices_db)"
MYSQLDUMP="$(which mysqldump) --opt";

MYSQL=$(which mysql);
GZIP=$(which pbzip2);
NICE=$(which nice);
EGREP=$(which egrep);

DBS=$($MYSQL -Bse "show databases");

for db in $DBS
do
    if (echo $db | $EGREP $DBFILTER > /dev/null);
    then

      TBL=$($MYSQL -Bse "show tables from $db");
      for tb in $TBL
      do
         FILENAME="$db""_""$tb-`date +%Y%m%d_%H%M%S`.sql.gz";
         UseAnweisung="CREATE DATABASE  IF NOT EXISTS \`$db\` /*!40100 DEFAULT CHARACTER SET latin1 */;USE \`$db\`;"
         cd /home/backupuser/DB_Backup/tmp
         echo $UseAnweisung > USE_Anweisung
         $MYSQLDUMP $db $tb > SQL_Anweisung
         cat USE_Anweisung SQL_Anweisung | $GZIP -c > "$BACKUPDIR/$FILENAME";
      done
    fi

done

exit 0

what I try to figure out ist, if it is possible to shorten this block:
Code:
         echo $UseAnweisung > USE_Anweisung
         $MYSQLDUMP $db $tb > SQL_Anweisung
         cat USE_Anweisung SQL_Anweisung | $GZIP -c > "$BACKUPDIR/$FILENAME";

to one line -and it is! e.g. with:
Code:
echo $UseAnweisung `$MYSQLDUMP $db $tb` | $GZIP -c > "$BACKUPDIR/$FILENAME";

but unfortunately the output of `$MYSQLDUMP $db $tb` looses it's linebreaks when the echo-command is working. That's the reason why I used cat, and 2 files, but in general I avoid writing this temporary data to the disk - maybe cat works with some kind of:
Code:
cat << EOF

but I'm not familiar at all with this...
# 7  
Old 03-06-2013
Quote:
Originally Posted by gogo555
[...]
what I try to figure out ist, if it is possible to shorten this block:
Code:
         echo $UseAnweisung > USE_Anweisung
         $MYSQLDUMP $db $tb > SQL_Anweisung
         cat USE_Anweisung SQL_Anweisung | $GZIP -c > "$BACKUPDIR/$FILENAME";

Try:

Code:
{
  printf '%s\n' "$UseAnweisung"
  $MYSQLDUMP "$db" "$tb" 
  } | 
    "$GZIP" -c > "$BACKUPDIR/$FILENAME"

I didn't quote the MYSQLDUMP variable, because it seems that you need shell word splitting there ...

Update:
A few observations.

By using the code above, you get rid of ( ... shortening):
- the two temporary files: USE_Anweisung and SQL_Anweisung
- the cat command

You said you wanted the code to be on one line ...
You could use something like this (just copy/paste, some space characters matter):

Code:
{ printf '%s\n' "$UseAnweisung"; $MYSQLDUMP "$db" "$tb";} | "$GZIP" -c > "$BACKUPDIR/$FILENAME"

...but this version is less readable.
Last edited by radoulov; 03-06-2013 at 05:00 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Needed shell script to read txt file and do some modification

Hi ...programmers... I need a shell script to perform some specific task.. my txt file looks like this netcdf new { dimensions: XAX1_11 = 11 ; variables: double XAX1_11(XAX1_11) ; XAX1_11:point_spacing = "even" ; XAX1_11:axis = "X" ; float DEPTH(XAX1_11) ;... (19 Replies)
Discussion started by: Akshay Hegde
19 Replies

2. Shell Programming and Scripting

Compression - Exclude huge files

I have a DB folder which sizes to 60GB approx. It has logs which size from 500MB - 1GB. I have an Installation which would update the DB. I need to backup this DB folder, just incase my Installation FAILS. But I do not need the logs in my backup. How do I exclude them during compression (tar)? ... (2 Replies)
Discussion started by: DevendraG
2 Replies

3. UNIX for Dummies Questions & Answers

Modification of Two Files

I have a script that produces two output files each containing the same number of lines <file1.txt> and <file2.txt>. What I need to do is combine both files into a new file <file3.txt> where line 1 of <file1.txt> is put to the right (and on the same line) as line 1 of <file2.txt> and then the same... (5 Replies)
Discussion started by: theref
5 Replies

4. Shell Programming and Scripting

Rename old files with last modification date

Hi everyone, I have files like file1_Mod.txt, file2_Mod.txt. I want to rename the old files with the last modification date. I write the below script to rename with current date, but I donīt know how to use "date -r" to get the last modification date with the same format I have below... (5 Replies)
Discussion started by: cgkmal
5 Replies

5. Shell Programming and Scripting

Help needed for mysqldump command

I want to take a backup of a database and redirect the output of the whole process to a log file. I am using the below command: mysqldump -A --add-drop-table > mysql-daily-backup.sql &> /tmp/backup_log/mysql.log Is there anything wrong with the syntax? ---------- Post updated at 08:32 PM... (0 Replies)
Discussion started by: proactiveaditya
0 Replies

6. UNIX for Advanced & Expert Users

Help with sorting files according to modification date

Hi, I was very surprised to not be able to find an answer to this question despite my best efforts in Google and elsewhere. Maybe it's a good thing as it forced me to finally become a member in this great forum that i use frequently. Ok my question: I want to be able to sort files inside a... (3 Replies)
Discussion started by: stavros
3 Replies

7. Shell Programming and Scripting

7za compression,urgent help needed!!!

Hi all, I am manipulating a ram disk image.that is 7za compressed... But i somehow am not able to get a hold of the command line for 7za compression and i need urgent help!!! this is what am doing... gzcat /path/to/directory/x86.microroot >/tmp/microroot x=`lofiadm -a /tmp/microroot` mount... (0 Replies)
Discussion started by: wrapster
0 Replies

8. UNIX for Advanced & Expert Users

Best compression for log files?

I have been doing some investigation into a log file from one of my systems, and the means which I currently use to compress and rotate it. I am looking for something smarter than gzip, faster than bzip2, and that can match or beat "my script" (which is slow as heck, but WAY better compression... (2 Replies)
Discussion started by: jjinno
2 Replies

9. UNIX for Dummies Questions & Answers

Last modification times of files less than 6 months old

How do I get the modification time for files less than 6 months old? (It seems for fles as old or older than this I get to see only the day,month and year. I tried the option -u but it gives me the last accessed time) (1 Reply)
Discussion started by: Abhishek Ghose
1 Replies
Login or Register to Ask a Question