2

i have this script and it works but the output isn't zipped so it's huge. I want to save it zipped but i don't understand shell script and i don't want to mess it up. Can you help me zip it like so: ".sql.zip", please?

/usr/bin/mysqldump -h $mysql_host -u $mysql_username -p$mysql_password $mysql_database > $backup_path/$today/$mysql_database-`date +%H%M`.sql

Any help appreciated.

Michael Rogers
  • 60
  • 1
  • 3
  • 15

3 Answers3

19

I think you can just do this.

/usr/bin/mysqldump -h $mysql_host -u $mysql_username -p$mysql_password $mysql_database | gzip -9 -c > $backup_path/$today/$mysql_database-`date +%H%M`.sql.gz
Alex Gitelzon
  • 314
  • 1
  • 4
6

Just 'pipe the output' to gzip

The command would become this:

 /usr/bin/mysqldump -h $mysql_host -u $mysql_username -p$mysql_password $mysql_database | gzip > $backup_path/$today/$mysql_database-`date +%H%M`.sql.gz
Bert
  • 2,733
  • 11
  • 12
5

You can pipe it though gzip (or bzip2, pbzip2, xz, etc...) like so:

/usr/bin/mysqldump -h $mysql_host -u $mysql_username -p$mysql_password $mysql_database | gzip -c > $backup_path/$today/$mysql_database-`date +%H%M`.sql.gz

zip isn't the optimal tool for this job because it stores data as files in an archive (like tar + gzip), where the other tools just compress data.

mysqldump (mysqldump -C) also supports compressed communication which could perhaps make things transfer faster over the network.

Ryan Babchishin
  • 6,160
  • 2
  • 16
  • 36
  • 1
    Funny all the same answers. I honestly posted at the same time as @Bert, but he was first. – Ryan Babchishin Sep 20 '16 at 16:44
  • 1
    What is the best answer of the 3 posted? [gzip] - [gzip -c] - [gzip -9 -c] – Michael Rogers Sep 20 '16 at 17:36
  • 4
    @MichaelRogers `gzip` and `gzip -c` are the same. gzip -9 will be slower and cause heavier CPU usage (-6 is default) but would result in a higher compression ratio. I suggested alternative compression utilities, particularly `pbzip2` (a threaded bzip2) because compressing a large stream can consume 100% of one CPU core and could become a bottleneck. gzip is the most common (standard?) compression utility for Linux which is likely why everyone recommended it, but it's not the fastest or *best*. Some new algorithms such as xz and lz4 (a few more too) aim to be faster. – Ryan Babchishin Sep 20 '16 at 18:02