Tag Archives: bash

Mysqldump Specific Tables From An RDS Database and Archive To S3

Posted on by
1

I was recently tasked with doing a daily backup of specific tables from an RDS database and storing that backup in date formatted S3 bucket. I made use of the awesome s3cmd cli tool.

The first thing I did was manually dump the desired tables from the database to get the correct syntax.


mysqldump -h database.dcolon.org -u dbuser -pABCD1234 table1 table2 table3 > dump.sql

This worked as expected. The dump.sql file contains table1, table2, and table3. Next I created a shell script and defined a number of variables. The format for the date in the S3 bucket is year/month/day. Today is 3/24/2014 so the date format for the bucket s3://net.dcolon.backups/mysql/ is:


s3://net.dcolon.backups/mysql/2014/03/24

Using the date command I get each of the values that I need and store them in a variable in the script. I take the mysqldump and store it locally and verify that the process completed without an error. After copying the mysqldump I rename the local copy appending the date. You can also add some logic to keep a certain number of recent copies on local disk and delete everything older.

Here is the complete script:


#!/bin/bash

export PATH=/bin:/usr/bin

DBHOST=db.dcolon.net
DBUSER=dbuser
DBPASSWD=ABCD1234
DATABASE=somedb
TABLES="table1 table2 table3"
YEAR=$(date +"%Y")
MONTH=$(date +"%m")
DAY=$(date +"%d")
S3BUCKET="s3://net.dcolon.backups/mysql/$YEAR/$MONTH/$DAY/"
DUMPFILE="/storage/backups/dump.sql"

mysqldump -h $DBHOST -u $DBUSER -p$DBPASSWD $DATABASE $TABLES > $DUMPFILE 

# if successful copy dump.sql to S3            
if [ $? -eq 0 ]; then
        s3cmd put $DUMPFILE $S3BUCKET
fi

mv $DUMPFILE $DUMPFILE.$YEAR$MONTH$DAY

Note: There is an inherent security risk of storing the password in clear text in a script or configuration file. mysqldump will mask your password while the process is running so another user can’t get the password from the process list.


dcolon    4668  0.0  0.0  22816  1776 pts/3    R+   00:45   0:00 mysqldump -u root -px xxxxxxxxxxxxxxxxxx zm

This post shows how to use mysql_config_editor to generate a config file with your password encrypted. Note that this requires MySQL 5.6 is greater.

If you have any questions, please ask below.

Change the Extension on a Large Number of Files

Posted on by
1

Here’s a common problem. You untar an archive and it contains a large number of files that end .jpeg. You need them to have a .jpg extension. At first glance, you might be tempted to try this:
mv *.jpeg *.jpg

Give it a try and you will find it doesn’t work. The correct way to change the extension is to iterate over all of the files. Let’s start with the following:

dcolon@dcolonbuntu:/tmp/foobar$ ls
friday.jpeg  monday.jpeg  Saturday.jpeg  sunday.jpeg  Thursday.jpeg  Tuesday.jpeg  WEDNESDAY.jpeg
dcolon@dcolonbuntu:/tmp/foobar$

Once in the loop, you need to save the filename before the extension. I use a temporary variable and awk to extract it.

Here is my solution:

dcolon@dcolonbuntu:/tmp/foobar$ for i in *.jpeg
> do
>    basefilename=$(echo $i | awk -F.jpeg '{print $1}')
>    mv "$i" "$basefilename.jpg"
> done
dcolon@dcolonbuntu:/tmp/foobar$ ls
friday.jpg  monday.jpg  Saturday.jpg  sunday.jpg  Thursday.jpg  Tuesday.jpg  WEDNESDAY.jpg
dcolon@dcolonbuntu:/tmp/foobar$

I use double quotes around the variable names to compensate for filenames with spaces. In the awk statement I use the entire replacement pattern as my field separator. Using -F. will fail if you have a filename like foo.bar.jpeg.

Bash Hostname Completion

Posted on by
Reply

One of the more well known features in bash is command and filename tab completion. Installing the bash-completion package adds onto this. This package enables hostname completion and a lot more. After installing bash-completion, add the following to your .bashrc:

. /etc/bash_completion
complete -F _known_hosts ssh
 
I also suggest adding
complete -F _known_hosts ping
complete -F _known_hosts traceroute

Hostname completion relies on the ssh known_hosts file. Most modern distributions hash the ~/.ssh/known_hosts file for security reasons. This prevents hostname completion from working. If you are comfortable turning off hostname hashing, then add the following to your ~/.ssh/config:
HashKnownHosts no

If you had to turn off hostname hashing, you will need to re-populate your known_hosts file. I suggest creating a list of all of your hosts and logging into them in a for loop:


for i in $(cat hostlist)
do
   ssh -n -o StrictHostKeyChecking=no $i "uname -a"
done

This assumes that you are using ssh keys. If you are not, you will need to type your password for each host. At this point you can now use hostname tab completion. If you added the second two complete commands, the same hostname completion will work for ping and traceroute.

Quickly Generate Sequential Bind Zone Files

Posted on by
1

For a large number of servers in a given ip block, I frequently see people use the following notation which incorporates the ip address with the hostname:


ip-10.10.10.1.mydomain.com
ip-10.10.10.2.mydomain.com
...
ip-10.10.10.255.mydomain.com

Using this type of notation makes it easy to script the creation of your zone file. To generate the forward dns for this zone (mydomain.com), I would do the following:

for i in $(seq 1 255)
do
   echo "ip-10.10.10.$i        IN A      10.10.10.$i" >> db.mydomain.com
done

Conversely, to generate the 10.10.10.0.in-addr.arpa reverse zone do the following:

for i in $(seq 1 255)
do
   echo "$i             IN PTR    ip-10.10.10.$i.mydomain.com." >> 10.10.10.0.in-addr.arpa
done

This creates the sequence from 1 to 255. For each iteration, you generate a line using echo for the given value of $i. Each line is appended to your zone file.

I like to use tabs instead of spaces in between columns. To add tabs to the echo statement, you need to escape each tab with a control-v. If you want to add two tabs, hit ‘control-v’ followed by the tab key followed by another ‘control-v’ and another tab.

There are obviously many other ways to do this. You can use a for loop:


for ((i=1; i<=255; i++));