import.sh

#!/bin/bash

# Import ordered list of SQL files
files="./logs/*.sql.gz"

for f in $files
do
    echo $f
    echo $f >> import.log
    zcat $f | mysql -uUSER -pPASS -h gloved-hand-3467.cjkao2aiaxxq.us-east-1.rds.amazonaws.com &>> import.log
    mv $f ./logs_imported
done

mysql.store_time_zone.sql

# http://meonthecloud.blogspot.ca/2012/07/setting-up-non-gmt-timezone-on-aws-rds.html

DELIMITER |
CREATE PROCEDURE mysql.store_time_zone ()
IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN
SET SESSION time_zone = 'America/Edmonton';
END IF |
DELIMITER ;

GRANT EXECUTE ON PROCEDURE `mysql`.`store_time_zone` TO 'root'@'%' IDENTIFIED BY 'PASS';
GRANT EXECUTE ON PROCEDURE `mysql`.`store_time_zone` TO 'blipsystem'@'%' IDENTIFIED BY 'PASS';

sync.sh

#!/bin/bash             

# First half of https://engineering.gosquared.com/migrating-mysql-to-amazon-rds                                                                                                                                                                                             

# Copy bin log files we want to process to working directory
# We assume that the binlogs are rotated every hour so get the files older than that
files=$(find /var/log/mysql/mysql-bin.* -mmin +60 -newer /root/binlogs/lasttimestamp -type f)

# populate the out file
for f in $files
do
    echo $f
    mysqlbinlog --base64-output=NEVER $f | grep -v "SET @@session.pseudo_thread_id" | grep -v "SET @@session.time_zone" | gzip -9 > /root/binlogs/logs/$(basename $f).sql.gz
    touch -d "$(date -r $f)" /root/binlogs/logs/$(basename $f).sql.gz
    touch -d "$(date -r $f) + 1 second" /root/binlogs/lasttimestamp
done

# Move the files to the EC2 box
rsync -avP --remove-source-files --bwlimit=50 /root/binlogs/logs/ [email protected]:~/binlogs/tmp/