BASH DB Sync
#!/bin/bash
#************************************************************************/
#* Author: Unknown Create Date: 08/03/23 */
#* Purpose: This Code copied from an online source nnd is designed to */
#* SYNC datasbases. The original design was Sync MySQL */
#* Databases for Websites across different stages (live, dev, */
#* test). */
#* Source: https://bash.cyberciti.biz/guide/Menu_driven_scripts */
#* Type: A menu driven shell script sample template. */
#************************************************************************/
#* Modified: Nyle E. Davis Modify Date: 08/03/23 */
#* Changes: Changed allowing local 2 local DB & table 2 table syncs */
#* Trucated the var names to fit the editor scren. */
#************************************************************************/
# ----------------------------------
# Step #1: Define global variables
# ----------------------------------
RED='\033[0;41;30m'
STD='\033[0;0;39m'
TODAY=$(date +"%Y-%m-%d")
# --------------------------------------
# Define the arrays
# indexes as follows:
# 0 = type : wp | magento | local | table
# 1 = host : my.db-ho.st
# 2 = user : username
# 3 = dbn : name of the database
# 4 = pwrd : password
# 5 = subdomain / prefix : for Magento's core_config_data change
# and to compare $SRC with $DEST
# --------------------------------------
declare -a live_mag=(mag my-live.db-ho.st user db pwrd www)
declare -a live_wp=(wp my-live.db-ho.st user db pwrd www)
declare -a dev_mag=(mag my-dev.db-ho.st user db pwrd dev)
declare -a dev_wp=(wp my-dev.db-ho.st user db pwrd dev)
declare -a test_mag=(mag my-test.db-ho.st user db pwrd test)
declare -a test_wp=(wp my-test.db-ho.st user db pwrd test)
declare -a dev2_mag=(mag my-dev.db-ho.st user db pwrd dev2)
declare -a dev2_wp=(wp my-dev.db-ho.st user db pwrd dev2)
declare -a loc1_ldb1=(loc 1.db-ho.st user db pwrd loc1)
declare -a loc2_ldb2=(loc 2.db-ho.st user db pwrd loc2)
declare -a tab1_tab1=(ltb-1.db-ho.st user db pwrd ltb1)
declare -a tab2_tab2=(ltb-2.db-ho.st user db pwrd ltb2)
# ----------------------------------
# Step #2: User defined function
# ----------------------------------
init() {
clear
echo "##################################"
echo "## A simple script to keep your ##"
echo "## MySQL Databases in sync. ##"
echo "##################################"
}
select_source_menu() {
echo ""
echo "Select a SOURCE database."
echo ""
echo "1. Live"
echo "2. Dev"
echo "3. Testing"
echo "4. Nav"
echo "5. Local"
echo "6. Table"
echo "~~~~~~~~~~~~~~~~~~~~~"
echo "0. Exit"
read_options_1
}
select_destination_menu() {
init
echo ""
echo "Select a DESTINATION database."
echo ""
echo "A. Live"
echo "B. Dev"
echo "C. Testing"
echo "D. Nav"
echo "E. Local"
echo "F. Table"
echo "~~~~~~~~~~~~~~~~~~~~~"
echo "0. Exit"
read_options_2
}
select_confirmation_menu() {
get_db_variables
init
echo ""
echo "### ATTENTION!!! ###"
echo "This will delete all information in the $DEST-database."
echo ""
echo "Are you sure?"
echo ""
echo "(y)es"
echo "(n)ot really"
echo "~~~~~~~~~~~~~~~~~~~~~"
echo "0. Exit"
read_options_2
}
select_remains_menu() {
init
echo ""
echo "What about the created SQL dumpfiles?"
echo "What are we gonna do with them?"
echo ""
echo "(k)eep them as zipped backup-file."
echo "(d)elete them."
read_options_2
}
select_restart_menu() {
init
echo ""
echo "All done. What now?"
echo ""
echo "r) Restart sync."
echo "0) Exit."
read_options_2
}
# read input from the keyboard and take a action
# Exit when user the user select 0 form the menu option.
read_options_1(){
local choice
read -p "Enter choice: " choice
case $choice in
1) SRC=live;select_destination_menu ;;
2) SRC=dev;select_destination_menu ;;
3) SRC=testing;select_destination_menu ;;
4) SRC=nav;select_destination_menu ;;
5) SRC=loc;select_destination_menu ;;
6) SRC=tab;select_destination_menu ;;
0) exit 1 ;;
*) echo -e "${RED}Error...${STD}" && sleep 2 && start
esac
}
read_options_2(){
local choice
read -p "Enter choice: " choice
case $choice in
A) DEST=live;select_confirmation_menu ;;
B) DEST=dev;select_confirmation_menu ;;
C) DEST=testing;select_confirmation_menu ;;
D) DEST=nav;select_confirmation_menu ;;
E) DEST=loc;select_confirmation_menu ;;
F) DEST=tab;select_confirmation_menu ;;
y) sync_db ;;
n) start ;;
k) backup ;;
d) delete_files ;;
r) start ;;
82517) sync_db ;;
0) exit 1 ;;
*) echo -e "${RED}Error...${STD}" && sleep 2 && select_destination_menu
esac
}
get_db_variables(){
case $SRC in
live) SRC_MAGE=("${live_mag[@]}");SRC_WP=("${live_wp[@]}") ;;
dev) SRC_MAGE=("${dev_mag[@]}");SRC_WP=("${dev_wp[@]}") ;;
test) SRC_MAGE=("${test_mag[@]}");SRC_WP=("${test_wp[@]}") ;;
nav) SRC_MAGE=("${nav_mag[@]}");SRC_WP=("${nav_wp[@]}") ;;
esac
case $DEST in
live) DEST_MAGE=("${live_mag[@]}");DEST_WP=("${live_wp[@]}") ;;
dev) DEST_MAGE=("${dev_mag[@]}");DEST_WP=("${dev_wp[@]}") ;;
test) DEST_MAGE=("${test_mag[@]}");DEST_WP=("${test_wp[@]}") ;;
nav) DEST_MAGE=("${nav_mag[@]}");DEST_WP=("${nav_wp[@]}") ;;
esac
if [ ${SRC_MAGE[5]} == ${DEST_MAGE[5]} ]; then
echo -e "${RED}Error: source and destination must not be the same.${STD}"
echo -e "${RED}Source is: ${SRC_MAGE[5]}.${STD}"
echo -e "${RED}Destination is: ${DEST_MAGE[5]}.${STD}" && sleep 5
start
fi
if [ ${DEST_MAGE[5]} == www ]; then
init
echo ""
echo "##################################"
echo "########## ATTENTION!!!! #########"
echo "##################################"
echo ""
echo "This will delete all information in the $DEST-database and"
echo "will overwrite it with information from $SRC"
echo ""
echo "##################################"
echo "There is NO WAY of restoring the data from $DEST"
echo "unless you made a backup once."
echo ""
echo "##################################"
echo ""
echo "So: Are you REALLY DEFINITELY sure?"
echo ""
echo "##################################"
echo ""
echo "Enter 82517 for yes."
echo "(n) to abort."
echo "~~~~~~~~~~~~~~~~~~~~~"
echo "0. Exit"
read_options_2
fi
}
ync_db(){
## The real purpose of this bash script
echo "Now copying MySQL databases from $SRC to $DEST..."
# indexes as follows:
# 0 = type : wordpress | mag
# 1 = host : db13.variomedia.de
# 2 = user : u36052 | u36065 ..
# 3 = database name : db36052 | db36065 ..
# 4 = password
# 5 = subdomain / prefix : for Magento's core_config_data change and to compare $SRC with $DEST
mysqldump -h ${SRC_MAGE[1]} -u ${SRC_MAGE[2]} -p${SRC_MAGE[4]} ${SRC_MAGE[3]} > ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql
echo "mag DB dumped to ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql"
mysqldump -h ${SRC_WP[1]} -u ${SRC_WP[2]} -p${SRC_WP[4]} ${SRC_WP[3]} > ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql
echo "WordPress DB dumped to ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql"
echo "Now importing into $DEST database..."
mysql -h ${DEST_MAGE[1]} -u ${DEST_MAGE[2]} -p${DEST_MAGE[4]} ${DEST_MAGE[3]} < ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql
echo "mag DB imported from ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql"
mysql -h ${DEST_WP[1]} -u ${DEST_WP[2]} -p${DEST_WP[4]} ${DEST_WP[3]} < ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql
echo "WordPress DB imported from ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql"
echo "Now changing the 'base_url's for the destination Magento-DB."
mysql -h ${DEST_MAGE[1]} -u ${DEST_MAGE[2]} -p${DEST_MAGE[4]} ${DEST_MAGE[3]} -e 'UPDATE `core_config_data` SET `value` = "http://'${DEST_MAGE[5]}'.my-website.com/" WHERE `core_config_data`.`config_id` = 7; UPDATE `core_config_data` SET `value` = "http://'${DEST_MAGE[5]}'.my-website.com/" WHERE `core_config_data`.`config_id` = 8;'
echo "Copying databases done. Now clearing ${DEST_MAGE[5]} Magento cache" && sleep 2
rm -rfv ~/${DEST_MAGE[5]}/var/cache/
select_remains_menu
}
backup(){
echo "Zipping SQL dumpfiles and saving them as backup..."
cat ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql | gzip -v > ~/sql-backup/$TODAY-sql-backup-${SRC_MAGE[5]}.gz
delete_files
}
delete_files(){
echo "Deleting sql-files..."
rm -fv ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql
select_restart_menu
}
# ----------------------------------------------
# Step #3: Trap CTRL+C, CTRL+Z and quit singles
# ----------------------------------------------
trap '' SIGINT SIGQUIT SIGTSTP
# -----------------------------------
# Step #4: Main logic - infinite loop
# ------------------------------------
start() {
unset -v SRC
unset -v DEST
unset -v SRC_MAGE
unset -v SRC_WP
unset -v DEST_MAGE
unset -v DEST_WP
init
select_source_menu
}
start Pasted: Aug 8, 2023, 8:36:56 pm
Views: 17