Amazon Aurora(2)Dump Data to CSV

Amazon Aurora(2)Dump Data to CSV

Install MySQL on MAC
> brew install mysql

> mysql --version
mysql Ver 14.14 Distrib 5.7.22, for osx10.13 (x86_64) using EditLine wrapper

Dump Script similar to This
> mysql -f -h127.0.0.1 -P3301 -ucloudmaster -pxxxxxx -Dmasterserver --batch -e "select * from SKY_Device_Pairing" > /Users/hluo/temp/devicepair.csv

Which I am using SSH Tunnel, let me try that

Count the CSV file
> wc -l ./css_devices_only_csv.txt


Here is the thing, we need to do some sed to make the CSV more formatted.
#!/bin/bash

# ./dump-mms-devices-to-csv.sh HOST DB_NAME USER PASSWORD

DEFAULT_DB_HOST="xxxxxx.rds.amazonaws.com"
DEFAULT_DB_USER="xxxxx"
DEFAULT_DB_TABLE="xxxxxx"

DB_HOST=${1:-$DEFAULT_DB_HOST}
DB_TABLE=${2:-$DEFAULT_DB_TABLE}
DB_USER=${3:-$DEFAULT_DB_USER}
DB_PASSWORD=$4

rm -fr /home/sillycat/device_dumps
mkdir -p /home/sillycat/device_dumps

touch /home/sillycat/device_dumps/devices_only_csv.txt

chmod 666 /home/sillycat/mdevice_dumps/devices_only_csv.txt

mysql -f -h$DB_HOST -P3306 -u$DB_USER -p$DB_PASSWORD -D$DB_TABLE --batch -e "select
serial as serialNumberAlias,
creationDate,
sessionID,
status as mmsStatus,
expiryDate,
pairingCode,
previousToken,
pairedDisplayName,
pairingDisplayName,
AMSStatus,
AMSEndDate,
lastAMSRefreshDate,
adminAccountID,
AMSfaultTollerance,
pairingCompleteAttempt,
cssUserID,
cssUserUUID
from
Device_Pairing;" | sed 's/\t/","/g' | sed 's/^/"/g' | sed 's/$/"/g' | sed 's/\n//g' | sed 's/"NULL"//g' > /home/sillycat/device_dumps/devices_only_csv.txt

Combine 2 CSV into 1 CSV
https://github.com/bahmutov/csv-pair
https://github.com/maxogden/csv-join

Merge the 2 file
> csv-join ./csv1.txt serialNumber ./csv1.txt serialNumber1 > merged.csv.txt

Some requirement to merge, first of all
csv1.txt will be something as follow:
serialNumber,platform
Hello9527, android

csv2.txt will be as follow:
serialNumber1,system
Hello9527, ubuntu

The output will be
serialNumber,serialNumber1, platform, system

At list the content of first line of the 2 csv data will match to make the merge works.

References:
https://forums.aws.amazon.com/thread.jspa?threadID=128140
https://gist.github.com/jeremybmerrill/1d058424aca5ebe2eb3d