1. #!/bin/bash

  2.  	#************************************************************************/

  3.  	#*   Author: Unknown							Create Date: 08/03/23   */

  4.  	#*  Purpose: This Code copied from an online source nnd is designed to  */

  5.  	#*			 SYNC datasbases.  The original design was Sync MySQL		*/

  6.  	#*			 Databases for Websites across different stages (live, dev, */

  7.  	#*			 test).													*/

  8.  	#*   Source: https://bash.cyberciti.biz/guide/Menu_driven_scripts 		*/

  9. 	#*     Type: A menu driven shell script sample template.				*/

  10. 	#************************************************************************/

  11.  	#* Modified: Nyle E. Davis						Modify Date: 08/03/23	*/

  12.  	#*  Changes: Changed allowing local 2 local DB & table 2 table syncs	*/

  13.  	#*  		 Trucated the var names to fit the editor scren.			*/

  14.  	#************************************************************************/

  15.  

  16. 	# ----------------------------------

  17. 	# Step #1: Define global variables

  18. 	# ----------------------------------

  19.  	RED='\033[0;41;30m'

  20. 	STD='\033[0;0;39m'

  21. 	TODAY=$(date +"%Y-%m-%d")

  22. 

  23. 	# --------------------------------------

  24. 	# Define the arrays

  25. 	# indexes as follows:

  26. 	#	0 = type : wp | magento | local | table

  27. 	#	1 = host : my.db-ho.st

  28. 	#	2 = user : username

  29. 	#	3 = dbn  : name of the database

  30. 	#	4 = pwrd : password

  31. 	#	5 = subdomain / prefix : for Magento's core_config_data change 

  32. 	#		and to compare $SRC with $DEST

  33. 	# --------------------------------------

  34. 	declare -a live_mag=(mag my-live.db-ho.st user db pwrd www)

  35. 	declare -a live_wp=(wp my-live.db-ho.st user db pwrd www)

  36. 	declare -a dev_mag=(mag my-dev.db-ho.st user db pwrd dev)

  37. 	declare -a dev_wp=(wp my-dev.db-ho.st user db pwrd dev)

  38. 	declare -a test_mag=(mag my-test.db-ho.st user db pwrd test)

  39. 	declare -a test_wp=(wp my-test.db-ho.st user db pwrd test)

  40. 	declare -a dev2_mag=(mag my-dev.db-ho.st user db pwrd dev2)

  41. 	declare -a dev2_wp=(wp my-dev.db-ho.st user db pwrd dev2)

  42. 	declare -a loc1_ldb1=(loc 1.db-ho.st user db pwrd loc1)

  43. 	declare -a loc2_ldb2=(loc 2.db-ho.st user db pwrd loc2)

  44. 	declare -a tab1_tab1=(ltb-1.db-ho.st user db pwrd ltb1)

  45. 	declare -a tab2_tab2=(ltb-2.db-ho.st user db pwrd ltb2)

  46. 

  47. 	# ----------------------------------

  48. 	# Step #2: User defined function

  49. 	# ---------------------------------- 

  50. 	init() {

  51. 		clear

  52. 		echo "##################################"

  53. 		echo "## A simple script to keep your ##"

  54. 		echo "## MySQL Databases in sync.     ##"

  55. 		echo "##################################"

  56. 	}

  57.  

  58. 	select_source_menu() {

  59. 		echo ""

  60. 		echo "Select a SOURCE database."

  61. 		echo ""

  62. 		echo "1. Live"

  63. 		echo "2. Dev"

  64. 		echo "3. Testing"

  65. 		echo "4. Nav"

  66. 		echo "5. Local"

  67. 		echo "6. Table"

  68. 		echo "~~~~~~~~~~~~~~~~~~~~~"

  69. 		echo "0. Exit"

  70. 		read_options_1

  71. 	}

  72. 

  73. 	select_destination_menu() {

  74. 		init

  75. 		echo ""

  76. 		echo "Select a DESTINATION database."

  77. 		echo ""

  78. 		echo "A. Live"

  79. 		echo "B. Dev"

  80. 		echo "C. Testing"

  81. 		echo "D. Nav"

  82. 		echo "E. Local"

  83. 		echo "F. Table"

  84. 		echo "~~~~~~~~~~~~~~~~~~~~~"

  85. 		echo "0. Exit"

  86. 		read_options_2

  87. 	}

  88.  

  89. 	select_confirmation_menu() {

  90. 		get_db_variables

  91. 		init

  92. 		echo ""

  93. 		echo "### ATTENTION!!! ###"

  94. 		echo "This will delete all information in the $DEST-database."

  95. 		echo ""

  96. 		echo "Are you sure?"

  97. 		echo ""

  98. 		echo "(y)es"

  99. 		echo "(n)ot really"

  100. 		echo "~~~~~~~~~~~~~~~~~~~~~"

  101. 		echo "0. Exit"

  102. 		read_options_2

  103. 	}

  104.  

  105. 	select_remains_menu() {

  106. 		init

  107. 		echo ""

  108. 		echo "What about the created SQL dumpfiles?"

  109. 		echo "What are we gonna do with them?"

  110. 		echo ""

  111. 		echo "(k)eep them as zipped backup-file."

  112. 		echo "(d)elete them."

  113. 		read_options_2

  114. 	}

  115. 

  116. 	select_restart_menu() {

  117. 		init

  118. 		echo ""

  119. 		echo "All done. What now?"

  120. 		echo ""

  121. 		echo "r) Restart sync."

  122. 		echo "0) Exit."

  123. 		read_options_2

  124. 	}

  125. 

  126. 	# read input from the keyboard and take a action

  127. 	# Exit when user the user select 0 form the menu option.

  128. 	read_options_1(){

  129. 		local choice

  130. 		read -p "Enter choice: " choice

  131. 		case $choice in

  132. 			1) SRC=live;select_destination_menu ;;

  133. 			2) SRC=dev;select_destination_menu ;;

  134. 			3) SRC=testing;select_destination_menu ;;

  135. 			4) SRC=nav;select_destination_menu ;;

  136. 			5) SRC=loc;select_destination_menu ;;

  137. 			6) SRC=tab;select_destination_menu ;;

  138. 			0) exit 1 ;;

  139. 			*) echo -e "${RED}Error...${STD}" && sleep 2 && start

  140. 		esac

  141. 	}

  142.  

  143. 	read_options_2(){

  144. 		local choice

  145. 		read -p "Enter choice: " choice

  146. 		case $choice in

  147. 			A) DEST=live;select_confirmation_menu ;;

  148. 			B) DEST=dev;select_confirmation_menu ;;

  149. 			C) DEST=testing;select_confirmation_menu ;;

  150. 			D) DEST=nav;select_confirmation_menu ;;

  151. 			E) DEST=loc;select_confirmation_menu ;;

  152. 			F) DEST=tab;select_confirmation_menu ;;

  153. 			y) sync_db ;;

  154. 			n) start ;;

  155. 			k) backup ;;

  156. 			d) delete_files ;;

  157. 			r) start ;;

  158. 			82517) sync_db ;;

  159. 			0) exit 1 ;;

  160. 			*) echo -e "${RED}Error...${STD}" && sleep 2 && select_destination_menu

  161. 		esac

  162. 	}

  163. 

  164. 

  165. 	get_db_variables(){

  166. 		case $SRC in

  167. 			live) SRC_MAGE=("${live_mag[@]}");SRC_WP=("${live_wp[@]}") ;;

  168. 			dev) SRC_MAGE=("${dev_mag[@]}");SRC_WP=("${dev_wp[@]}") ;;

  169. 			test) SRC_MAGE=("${test_mag[@]}");SRC_WP=("${test_wp[@]}") ;;

  170. 			nav) SRC_MAGE=("${nav_mag[@]}");SRC_WP=("${nav_wp[@]}") ;;

  171. 		esac

  172. 	 

  173. 		case $DEST in

  174. 			live) DEST_MAGE=("${live_mag[@]}");DEST_WP=("${live_wp[@]}") ;;

  175. 			dev) DEST_MAGE=("${dev_mag[@]}");DEST_WP=("${dev_wp[@]}") ;;

  176. 			test) DEST_MAGE=("${test_mag[@]}");DEST_WP=("${test_wp[@]}") ;;

  177. 			nav) DEST_MAGE=("${nav_mag[@]}");DEST_WP=("${nav_wp[@]}") ;;

  178. 		esac

  179. 	 

  180. 		if [ ${SRC_MAGE[5]} == ${DEST_MAGE[5]} ]; then

  181. 			echo -e "${RED}Error: source and destination must not be the same.${STD}"

  182. 			echo -e "${RED}Source is: ${SRC_MAGE[5]}.${STD}"

  183. 			echo -e "${RED}Destination is: ${DEST_MAGE[5]}.${STD}" && sleep 5

  184. 			start

  185. 		fi

  186. 		

  187. 		

  188. 		if [ ${DEST_MAGE[5]} == www ]; then

  189. 			init

  190. 			echo ""

  191. 			echo "##################################"

  192. 			echo "########## ATTENTION!!!! #########"

  193. 			echo "##################################"

  194. 			echo ""

  195. 			echo "This will delete all information in the $DEST-database and"

  196. 			echo "will overwrite it with information from $SRC"

  197. 			echo ""

  198. 			echo "##################################"

  199. 			echo "There is NO WAY of restoring the data from $DEST"

  200. 			echo "unless you made a backup once."

  201. 			echo ""

  202. 			echo "##################################"

  203. 			echo ""

  204. 			echo "So: Are you REALLY DEFINITELY sure?"

  205. 			echo ""

  206. 			echo "##################################"

  207. 			echo ""

  208. 			echo "Enter 82517 for yes."

  209. 			echo "(n) to abort."

  210. 			echo "~~~~~~~~~~~~~~~~~~~~~"

  211. 			echo "0. Exit"

  212. 			read_options_2

  213. 		fi

  214. 	}

  215. 

  216. 	ync_db(){

  217. 		## The real purpose of this bash script

  218. 		echo "Now copying MySQL databases from $SRC to $DEST..."

  219. 		# indexes as follows:

  220. 		#	0 = type : wordpress | mag

  221. 		#	1 = host : db13.variomedia.de

  222. 		#	2 = user : u36052 | u36065 ..

  223. 		#	3 = database name : db36052 | db36065 ..

  224. 		#	4 = password

  225. 		#	5 = subdomain / prefix : for Magento's core_config_data change and to compare $SRC with $DEST

  226. 	 

  227. 		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

  228. 	 

  229. 		echo "mag DB dumped to ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql"

  230. 	 

  231. 		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

  232. 	 

  233. 		echo "WordPress DB dumped to ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql"

  234. 		echo "Now importing into $DEST database..."

  235. 	 

  236. 		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

  237. 	 

  238. 		echo "mag DB imported from ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql"

  239. 	 

  240. 		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

  241. 	 

  242. 		echo "WordPress DB imported from ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql"

  243. 		echo "Now changing the 'base_url's for the destination Magento-DB."

  244. 	 

  245. 		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;'

  246. 	 

  247. 		echo "Copying databases done. Now clearing ${DEST_MAGE[5]} Magento cache" && sleep 2

  248. 	 

  249. 		rm -rfv ~/${DEST_MAGE[5]}/var/cache/

  250. 		select_remains_menu

  251. 	}

  252. 

  253. 

  254. 

  255. 	backup(){

  256. 		echo "Zipping SQL dumpfiles and saving them as backup..."

  257. 		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

  258. 		delete_files

  259. 	}

  260. 	 

  261. 	delete_files(){

  262. 		echo "Deleting sql-files..."

  263. 		rm -fv ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql

  264. 		select_restart_menu

  265. 	}

  266. 

  267. 

  268. 

  269. 	# ----------------------------------------------

  270. 	# Step #3: Trap CTRL+C, CTRL+Z and quit singles

  271. 	# ----------------------------------------------

  272. 	trap '' SIGINT SIGQUIT SIGTSTP

  273. 	 

  274. 	# -----------------------------------

  275. 	# Step #4: Main logic - infinite loop

  276. 	# ------------------------------------

  277. 	 

  278. 	 

  279. 	start() {

  280. 		unset -v SRC

  281. 		unset -v DEST

  282. 		unset -v SRC_MAGE

  283. 		unset -v SRC_WP

  284. 		unset -v DEST_MAGE

  285. 		unset -v DEST_WP

  286. 		init

  287. 		select_source_menu

  288. 	}

  289.  

  290. 	start