MySQL Synchronize

pull-db.sh

#!/bin/bash
# Filename: pull-db.sh
# Author: IjorTengab (http://ijortengab.id)
# Last Update: 2018-02-19

# Begin of Variables. Set by your own value.
# SSH.
REMOTE_SSH_HOST=example.com
REMOTE_SSH_PORT=22
REMOTE_SSH_USER=user

# Remote MySQL.
SOURCE_MYSQL_HOST=db_host
SOURCE_MYSQL_USER=db_user
SOURCE_MYSQL_PASSWORD=db_password
SOURCE_MYSQL_DATABASE=db_name

# Local MySQL.
DESTINATION_MYSQL_HOST=db_host
DESTINATION_MYSQL_PASSWORD=db_user
DESTINATION_MYSQL_USER=db_password
DESTINATION_MYSQL_DATABASE=db_name

# Common MySQL.
MYSQL_TABLE_PREFIX=
MYSQL_EXCLUDED_TABLES=(
    # Use wildcard * enabled.
    sessions
    node
    field_data_*
    field_revision_*
    watchdog
)
MYSQL_EXCLUDED_AND_TRUNCATE_DESTINATION_TABLES=(
    # Use wildcard * enabled.
    cache
    flood
    cache_*
)
# End of Variables. 

# Build Additional Variables.
NOW=$(date +%Y%m%d%H%M%S)
DUMPFILENAME=${SOURCE_MYSQL_DATABASE}-${NOW}.sql
SCP_OPTION=Port=${REMOTE_SSH_PORT}
if [[ "$(command -v pwgen)" != '' ]]; then
  SHELL_AGENT=$(pwgen -1 25).sh
else  
  SHELL_AGENT=${NOW}${NOW}${NOW}.sh
fi
SSH_EXEC=\'./${SHELL_AGENT}\'

# Create script untuk dump database remote.
touch $SHELL_AGENT
chmod u+x $SHELL_AGENT
echo '#!/bin/bash' >> ${SHELL_AGENT}
echo 'HOST='$SOURCE_MYSQL_HOST >> ${SHELL_AGENT}
echo 'USER='$SOURCE_MYSQL_USER >> ${SHELL_AGENT}
echo 'PASSWORD='$SOURCE_MYSQL_PASSWORD >> ${SHELL_AGENT}
echo 'DATABASE='$SOURCE_MYSQL_DATABASE >> ${SHELL_AGENT}
echo 'DUMPFILENAME='$DUMPFILENAME >> ${SHELL_AGENT}
echo 'TABLE_PREFIX='$MYSQL_TABLE_PREFIX >> ${SHELL_AGENT}
echo 'EXCLUDED_TABLES=(' >> ${SHELL_AGENT}
for TABLE in "${MYSQL_EXCLUDED_TABLES[@]}"
do
   echo '    '$TABLE >> ${SHELL_AGENT}
done
for TABLE in "${MYSQL_EXCLUDED_AND_TRUNCATE_DESTINATION_TABLES[@]}"
do
   echo '    '$TABLE >> ${SHELL_AGENT}
done
echo ')' >> ${SHELL_AGENT}
echo 'EXCLUDED_TABLES_EXPANDED=()' >> ${SHELL_AGENT}
echo 'IGNORED_TABLES_STRING=' >> ${SHELL_AGENT}
(
cat <<'SCRIPT'
for TABLE in "${EXCLUDED_TABLES[@]}"
do
   if [[ $TABLE =~ \*$ ]];then
       PREFIX=${TABLE:0:-1}
       TABLES_PREFIX=$(mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${USER}" "${PASSWORD}") -e "SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema = '${DATABASE}' AND table_name REGEXP '^${TABLE_PREFIX}${PREFIX}.*';")
       if [[ ${#TABLES_PREFIX[@]} -gt 0 ]];then
            DELETE=(table_name)
            TABLES_PREFIX=("${TABLES_PREFIX[@]/$DELETE}")
            EXCLUDED_TABLES_EXPANDED+=(${TABLES_PREFIX})
       fi
   else
       EXCLUDED_TABLES_EXPANDED+=(${TABLE_PREFIX}${TABLE})
   fi
done
for TABLE in "${EXCLUDED_TABLES_EXPANDED[@]}"
do
    IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done
echo Mulai dump database remote.
echo > ${DUMPFILENAME}
mysqldump --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${USER}" "${PASSWORD}") --host=${HOST} --replace --skip-set-charset --lock-tables=false --skip-add-locks --skip-add-drop-table --create-options --single-transaction --no-data ${DATABASE} >> ${DUMPFILENAME}
sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' ${DUMPFILENAME}
mysqldump --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${USER}" "${PASSWORD}") --host=${HOST} --replace --skip-set-charset --lock-tables=false --skip-add-locks ${DATABASE} --no-create-info ${IGNORED_TABLES_STRING} >> ${DUMPFILENAME}
echo Selesai dump database remote.
SCRIPT
) >> ${SHELL_AGENT}

# Push Script file then delete.
scp -q -o $SCP_OPTION ${SHELL_AGENT} ${REMOTE_SSH_USER}@${REMOTE_SSH_HOST}:${SHELL_AGENT}
rm $SHELL_AGENT

# Execute script for dump database remote.
ssh ${REMOTE_SSH_USER}@${REMOTE_SSH_HOST} -p ${REMOTE_SSH_PORT} $SSH_EXEC

# Pull SQL file.
echo Menarik file SQL dari remote ke local.
scp -o $SCP_OPTION ${REMOTE_SSH_USER}@${REMOTE_SSH_HOST}:${DUMPFILENAME} ${DUMPFILENAME}

# Delete SQL file in remote.
echo Menghapus file SQL di remote.
touch $SHELL_AGENT
chmod u+x $SHELL_AGENT
echo #!/bin/bash >> $SHELL_AGENT
echo rm ${DUMPFILENAME} >> $SHELL_AGENT
echo rm $SHELL_AGENT >> $SHELL_AGENT
scp -q -o $SCP_OPTION ${SHELL_AGENT} ${REMOTE_SSH_USER}@${REMOTE_SSH_HOST}:${SHELL_AGENT}
rm ${SHELL_AGENT}
ssh ${REMOTE_SSH_USER}@${REMOTE_SSH_HOST} -p ${REMOTE_SSH_PORT} $SSH_EXEC

# Restore database ke local.
echo Mulai restore database local.
mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${DESTINATION_MYSQL_USER}" "${DESTINATION_MYSQL_PASSWORD}") ${DESTINATION_MYSQL_DATABASE} < $DUMPFILENAME
# Truncate jika ada.
TRUNCATE_DESTINATION_TABLES=()
for TABLE in "${MYSQL_EXCLUDED_AND_TRUNCATE_DESTINATION_TABLES[@]}"
do
   if [[ $TABLE =~ \*$ ]];then
       PREFIX=${TABLE:0:-1}
       TABLES_PREFIX=$(mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${DESTINATION_MYSQL_USER}" "${DESTINATION_MYSQL_PASSWORD}") -e "SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema = '${DESTINATION_MYSQL_DATABASE}' AND table_name REGEXP '^${MYSQL_TABLE_PREFIX}${PREFIX}.*';")
       if [[ ${#TABLES_PREFIX[@]} -gt 0 ]];then
            DELETE=(table_name)
            TABLES_PREFIX=("${TABLES_PREFIX[@]/$DELETE}")
            TRUNCATE_DESTINATION_TABLES+=(${TABLES_PREFIX})
       fi
   else
       TRUNCATE_DESTINATION_TABLES+=(${MYSQL_TABLE_PREFIX}${TABLE})
   fi
done
if [[ ${#TRUNCATE_DESTINATION_TABLES[@]} -gt 0 ]];then
    TRUNCATE_QUERY=
    for TABLE in "${TRUNCATE_DESTINATION_TABLES[@]}"
    do
        TRUNCATE_QUERY+=' TRUNCATE '$DESTINATION_MYSQL_DATABASE.${MYSQL_TABLE_PREFIX}${TABLE}'; '
    done
    mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${DESTINATION_MYSQL_USER}" "${DESTINATION_MYSQL_PASSWORD}") -e "${TRUNCATE_QUERY}"
fi
echo Selesai restore database local.
rm ${DUMPFILENAME}

push-db.sh

#!/bin/bash
# Filename: push-db.sh
# Author: IjorTengab (http://ijortengab.id)
# Last Update: 2018-08-18

# Begin of Variables. Set by your own value.
# SSH.
REMOTE_SSH_HOST=example.com
REMOTE_SSH_PORT=22
REMOTE_SSH_USER=user

# Remote MySQL.
DESTINATION_MYSQL_HOST=db_host
DESTINATION_MYSQL_PASSWORD=db_user
DESTINATION_MYSQL_USER=db_password
DESTINATION_MYSQL_DATABASE=db_name

# Local MySQL.
SOURCE_MYSQL_HOST=db_host
SOURCE_MYSQL_USER=db_user
SOURCE_MYSQL_PASSWORD=db_password
SOURCE_MYSQL_DATABASE=db_name

# Common MySQL.
MYSQL_TABLE_PREFIX=
MYSQL_EXCLUDED_TABLES=(
    # Use wildcard * enabled.
    sessions
    node
    field_data_*
    field_revision_*
    watchdog
)
MYSQL_EXCLUDED_AND_TRUNCATE_DESTINATION_TABLES=(
    # Use wildcard * enabled.
    cache
    flood
    cache_*
)
# End of Variables. 

# Build Additional Variables.
NOW=$(date +%Y%m%d%H%M%S)
DUMPFILENAME=${SOURCE_MYSQL_DATABASE}-${NOW}.sql
SCP_OPTION=Port=${REMOTE_SSH_PORT}
if [[ "$(command -v pwgen)" != '' ]]; then
  SHELL_AGENT=$(pwgen -1 25).sh
else  
  SHELL_AGENT=${NOW}${NOW}${NOW}.sh
fi
SSH_EXEC=\'./${SHELL_AGENT}\'
IGNORED_TABLES_STRING=''
MYSQL_EXCLUDED_TABLES_EXPANDED=()
for TABLE in "${MYSQL_EXCLUDED_TABLES[@]}"
do
   if [[ $TABLE =~ \*$ ]];then
       PREFIX=${TABLE:0:-1}
       TABLES_PREFIX=$(mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${SOURCE_MYSQL_USER}" "${SOURCE_MYSQL_PASSWORD}") -e "SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema = '${SOURCE_MYSQL_DATABASE}' AND table_name REGEXP '^${MYSQL_TABLE_PREFIX}${PREFIX}.*';")
       if [[ ${#TABLES_PREFIX[@]} -gt 0 ]];then
            DELETE=(table_name)
            TABLES_PREFIX=("${TABLES_PREFIX[@]/$DELETE}")
            MYSQL_EXCLUDED_TABLES_EXPANDED+=(${TABLES_PREFIX})
       fi
   else
       MYSQL_EXCLUDED_TABLES_EXPANDED+=(${MYSQL_TABLE_PREFIX}${TABLE})
   fi
done
for TABLE in "${MYSQL_EXCLUDED_AND_TRUNCATE_DESTINATION_TABLES[@]}"
do
   if [[ $TABLE =~ \*$ ]];then
       PREFIX=${TABLE:0:-1}
       TABLES_PREFIX=$(mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${SOURCE_MYSQL_USER}" "${SOURCE_MYSQL_PASSWORD}") -e "SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema = '${SOURCE_MYSQL_DATABASE}' AND table_name REGEXP '^${MYSQL_TABLE_PREFIX}${PREFIX}.*';")
       if [[ ${#TABLES_PREFIX[@]} -gt 0 ]];then
            DELETE=(table_name)
            TABLES_PREFIX=("${TABLES_PREFIX[@]/$DELETE}")
            MYSQL_EXCLUDED_TABLES_EXPANDED+=(${TABLES_PREFIX})
       fi
   else
       MYSQL_EXCLUDED_TABLES_EXPANDED+=("${MYSQL_TABLE_PREFIX}${TABLE}")
   fi
done
for TABLE in "${MYSQL_EXCLUDED_TABLES_EXPANDED[@]}"
do
   IGNORED_TABLES_STRING+=" --ignore-table=${SOURCE_MYSQL_DATABASE}.${TABLE}"
done

# Create script for dump database local.
touch $SHELL_AGENT
chmod u+x $SHELL_AGENT
echo '#!/bin/bash' >> ${SHELL_AGENT}
echo 'HOST='$SOURCE_MYSQL_HOST >> ${SHELL_AGENT}
echo 'USER='$SOURCE_MYSQL_USER >> ${SHELL_AGENT}
echo 'PASSWORD='$SOURCE_MYSQL_PASSWORD >> ${SHELL_AGENT}
echo 'DATABASE='$SOURCE_MYSQL_DATABASE >> ${SHELL_AGENT}
echo 'DUMPFILENAME='$DUMPFILENAME >> ${SHELL_AGENT}
echo 'IGNORED_TABLES_STRING='"'$IGNORED_TABLES_STRING'" >> ${SHELL_AGENT}
echo echo Mulai dump database local. >> $SHELL_AGENT
echo 'echo > ${DUMPFILENAME}' >> ${SHELL_AGENT}
echo 'mysqldump --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${USER}" "${PASSWORD}") --host=${HOST} --replace --skip-set-charset --lock-tables=false --skip-add-locks --skip-add-drop-table --create-options --single-transaction --no-data ${DATABASE} >> ${DUMPFILENAME}' >> ${SHELL_AGENT}
echo 'sed -i '"'s/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g'"' ${DUMPFILENAME}' >> ${SHELL_AGENT}
echo 'mysqldump --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${USER}" "${PASSWORD}") --host=${HOST} --replace --skip-set-charset --lock-tables=false --skip-add-locks ${DATABASE} --no-create-info ${IGNORED_TABLES_STRING} >> ${DUMPFILENAME}' >> ${SHELL_AGENT}
TRUNCATE_DESTINATION_TABLES=()
for TABLE in "${MYSQL_EXCLUDED_AND_TRUNCATE_DESTINATION_TABLES[@]}"
do
   if [[ $TABLE =~ \*$ ]];then
       PREFIX=${TABLE:0:-1}
       TABLES_PREFIX=$(mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${SOURCE_MYSQL_USER}" "${SOURCE_MYSQL_PASSWORD}") -e "SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema = '${SOURCE_MYSQL_DATABASE}' AND table_name REGEXP '^${MYSQL_TABLE_PREFIX}${PREFIX}.*';")
       if [[ ${#TABLES_PREFIX[@]} -gt 0 ]];then
            DELETE=(table_name)
            TABLES_PREFIX=("${TABLES_PREFIX[@]/$DELETE}")
            TRUNCATE_DESTINATION_TABLES+=(${TABLES_PREFIX})
       fi
   else
       TRUNCATE_DESTINATION_TABLES+=(${MYSQL_TABLE_PREFIX}${TABLE})
   fi
done
for TABLE in "${TRUNCATE_DESTINATION_TABLES[@]}"
do
    echo 'echo '"'"'TRUNCATE `'${TABLE}'`;'"'"' >> ${DUMPFILENAME}' >> $SHELL_AGENT
done
echo echo Selesai dump database local. >> $SHELL_AGENT

# Execute script for dump database local.
./$SHELL_AGENT

# Push SQL file.
echo Mendorong file SQL dari local ke remote.
scp -o $SCP_OPTION ${DUMPFILENAME} ${REMOTE_SSH_USER}@${REMOTE_SSH_HOST}:${DUMPFILENAME}

# Delete script dump and SQL file in local.
rm ${DUMPFILENAME}
rm $SHELL_AGENT

# Create script for restore database remote.
touch $SHELL_AGENT
chmod u+x $SHELL_AGENT
echo #!/bin/bash >> $SHELL_AGENT
echo echo Mulai restore database remote. >> $SHELL_AGENT
(
cat <<SCRIPT
mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "${DESTINATION_MYSQL_USER}" "${DESTINATION_MYSQL_PASSWORD}") ${DESTINATION_MYSQL_DATABASE} < $DUMPFILENAME
SCRIPT
) >> $SHELL_AGENT
echo echo Selesai restore database remote. >> $SHELL_AGENT
echo rm ${DUMPFILENAME} >> $SHELL_AGENT
echo rm $SHELL_AGENT >> $SHELL_AGENT

# Push Script file.
scp -q -o $SCP_OPTION ${SHELL_AGENT} ${REMOTE_SSH_USER}@${REMOTE_SSH_HOST}:${SHELL_AGENT}
rm ${SHELL_AGENT}

# Execute script for restore database remote.
ssh ${REMOTE_SSH_USER}@${REMOTE_SSH_HOST} -p ${REMOTE_SSH_PORT} $SSH_EXEC