Bash script to copy remote MySQL databases

Advertisement:

I am glad that I have set up multiple environments for a current project that I’m working on. The live site, a testing site, and two developing sites – all with their unique databases just in case something’s going very Murphy – are a fair deal to develop and test new features and bugfixes on any web project. But there’s one major drawback: Keeping data “in sync”. Version control like git or SVN do a great job with all that PHPs, JavaScripts and CSS definitions. But what about the frontend? Especially the MySQL databases are a pretty laborious thing to keep “in sync”.

!This post is seriously aged.The world has moved on and the stuff described in this post might be outdated. Having trouble getting this to work? Leave a comment and I'll have a look into that.

As a developer that is using a seperate setup for building new features or removing bugs, you should never ever use the original databases from the live project. It is too easy to have a typo and literally destroy its contents by mistake. You might still not destroy the whole database, but a single table or a single column inside a table is often enough a severe problem. But using a completely seperate environment still has its drawbacks sometimes. Maybe you are to develop a new feature for WordPress posts that don’t make sense on the ones that are in your developing environment? Or Maybe you want to present new features to your stakeholders and they get confused by viewing a project site whose content is three months old, as it happened to me? So keeping your developing database up-to-date is an inevitably neccessity.

One way to update a MySQL database from a live-site to a developing-site is using phpMyAdmin. You’d download the whole database from one place and import it to another place. Or you are cloning it inside the database-host to another, which is even simpler. But that’s usually a time-intense thing to do.

Another way would be with the help of a terminal-client like PuTty. Exporting and importing through the console is a lot faster than through the GUI of phpMyAdmin, but it still involves the same process: manually dump the database and import it somewhere else. Including typing cryptic commands into the console-interface – typos guaranteed.

The project I am currently working on consists of four environments – a live site, a testing area and two developing environments (one is for a special feature that is very instense and comlpex). Since we are using WordPress and Magento, which use a seperate database each, that is a total of eight databases to keep in sync. I guess it is understandable that I wanted to have an easy (and straight forward) process to update the subordinated environments from the live site.

What I wanted

  • kind of an user interface which allows me (or someone else) to select a source and a destination environment to copy the databases from and to
  • no more fooling around with command line prompts that include
  • manually entering database server, username, database name and passwords for both connections
  • no more file transfers of large sql-dumps from one server to another or from one FTP-folder to another
  • less time consumption / more efficiency by the overall process of cloning databases between environments

I knew from scratch, that I could actually write some simple scripts that are executing a batch of commands. But I wanted to keep it simple and use an – at least simulated – user interface. So I googled around a bit and eventually found a script-template to start with. Then I began sketching the simple and pretty fool-proof interface actions that I wanted to have:

  1. select an environment to copy the MySQL databases from
  2. select an environment to copy the databases to
  3. make sure that the user understands that the destination database is completely emptied before the import and the database of the live server could not be overwritten by accident
  4. try to ensure that everything works fine after the import
  5. since I happen to have dump files anyway on the server, why would I not have the chance to back them up?

With these premises in mind, I basically came down to writing my first low-complexity bash-script that I am going to walk you through, You’ll find a link to the full script and a downloadable text file at the end of this post.

The beginning

1
2
3
4
5
6
7
8
9
10
11
12
#!/bin/bash
# A menu driven shell script sample template
# src: https://bash.cyberciti.biz/guide/Menu_driven_scripts
#
# Purpose: Sync MySQL Databases for Websites across different stages (live, dev, testing)
## ----------------------------------
# Step #1: Define global variables
# ----------------------------------
 
RED='\033[0;41;30m'
STD='\033[0;0;39m'
TODAY=$(date +"%Y-%m-%d")

All programms start with setting up the environment variables. There’s nothing special here, just defining some color variations the script will use and getting the current (server) date as variable, which we will use later.

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
## ----------------------------------
# Define the arrays
# indexes as follows:
#	0 = type : wordpress | magento
#	1 = host : my.database-ho.st
#	2 = user : username
#	3 = database name : name of the database
#	4 = password
#	5 = subdomain / prefix : for Magento's core_config_data change and to compare $SRC with $DEST
# ----------------------------------
 
declare -a live_magento=(magento my-live.database-ho.st user database password www)
declare -a live_wordpress=(wordpress my-live.database-ho.st user database password www)
declare -a dev_magento=(magento my-dev.database-ho.st user database password dev)
declare -a dev_wordpress=(wordpress my-dev.database-ho.st user database password dev)
declare -a testing_magento=(magento my-test.database-ho.st user database password testing)
declare -a testing_wordpress=(wordpress my-test.database-ho.st user database password testing)
declare -a dev2_magento=(magento my-dev.database-ho.st user database password dev2)
declare -a dev2_wordpress=(wordpress my-dev.database-ho.st user database password dev2)

The first important part of the script. Since I did not want to enter the database credentials (and throwing typos in) over and over again, I set them up as arrays. I couldn’t manage to use assiciative arrays that basically have a human readable index-name, so I had to use plain old indexed arrays. I chose to use the origin-CMS name as type for the dump-file name convention and I needed the subdomain / prefix for changing the corresponding values in Magento’s config-data.

32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
# ----------------------------------
# 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 "~~~~~~~~~~~~~~~~~~~~~"
	echo "0. Exit"
	read_options_1
}
 
select_destination_menu() {
	init
	echo ""
	echo "Select a DESTINATION database."
	echo ""
	echo "5. Live"
	echo "6. Dev"
	echo "7. Testing"
	echo "8. Nav"
	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
}

The “GUI” menus with their possible choices. Basically a bunch of echoed lines that will be shown in the terminal followed by a function call to wait for the user to enter something with the keyboard.

107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
# 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 ;;
		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
		5) DEST=live;select_confirmation_menu ;;
		6) DEST=dev;select_confirmation_menu ;;
		7) DEST=testing;select_confirmation_menu ;;
		8) DEST=nav;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
}

Those two functions “read” the entry made by the user and execute their corresponding functions. I had to seperate the first menu from the rest to make sure that the user cannot acidentally change the source database when typing the wrong number on the destination selection screen. There’s probably a better way, but this was the only way that came to my mind due to my lack of knowledge and experience in bash-scripting.

140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
get_db_variables(){
	case $SRC in
		live) SRC_MAGE=("${live_magento[@]}");SRC_WP=("${live_wordpress[@]}") ;;
		dev) SRC_MAGE=("${dev_magento[@]}");SRC_WP=("${dev_wordpress[@]}") ;;
		testing) SRC_MAGE=("${testing_magento[@]}");SRC_WP=("${testing_wordpress[@]}") ;;
		nav) SRC_MAGE=("${nav_magento[@]}");SRC_WP=("${nav_wordpress[@]}") ;;
	esac
 
	case $DEST in
		live) DEST_MAGE=("${live_magento[@]}");DEST_WP=("${live_wordpress[@]}") ;;
		dev) DEST_MAGE=("${dev_magento[@]}");DEST_WP=("${dev_wordpress[@]}") ;;
		testing) DEST_MAGE=("${testing_magento[@]}");DEST_WP=("${testing_wordpress[@]}") ;;
		nav) DEST_MAGE=("${nav_magento[@]}");DEST_WP=("${nav_wordpress[@]}") ;;
	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

This function assigns the database credentials for the Magento and the WordPress installation as source and destination. For the sake of erroring users, I prevented using the same environment as source and destination at the same time. When a user chooses identical environments, an error message is displayed for five seconds and the script will start from the beginning.

161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
	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
}

Another user check when the database of the live site is chosen to be overwritten. Just in case, you never know who is going to use the script. The numerical code to enter for authorizing the action is simple to enter but complicated enough to make sure the origin database on the live site is not emptied by accident. The number 82517 is used on purpose 🙂

188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
sync_db(){
	## The real purpose of this bash script
	echo "Now copying MySQL databases from $SRC to $DEST..."
	# indexes as follows:
	#	0 = type : wordpress | magento
	#	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 "Magento 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 "Magento 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
}

The main function of this whole script. It is dumping both the databases from Magento and WordPress to a single file each. It then imports the files to the destination database. To make Magento work on the new destination, the base_url and the base_secure_url have to be changed in the destination database. Using the config_id as filter might not be the best choice. This function is also clearing Magento’s cache folder. Each step is accompanied by a kind-of status-message.

If you’re going to re-use this script, make sure the folder specified for the SQL-dumps exists (it will not be created!) or change it to another value. Also check if the config_ids for the base and and the secure URL defined in your Magento database are actually true. If not, change them to the correct value before testing.

224
225
226
227
228
229
230
231
232
233
234
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
}

The two last functions of this script. The first will GZip both SQL-files into one file and afterwards call the deletion-function that … deletes the SQL files from the server.

235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
# ----------------------------------------------
# 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

Thats all what’s left of the script: Trapping the CRTL-commands to exit / quit the script while in use and the start-function which is called directly after. It unsets (deletes) the previously assigned variables for source and destination. I figured that my bash shell will keep the choices from the first run in memory so unsetting the variables is needed when the user chooses to run the script again (“restart”).

You can download the full bash script to copy remote MySQL-databases via command line as text-file. Simply upload it to your server, rename it if you like but make sure to remove the .txt extension, and execute it with the command bash {your_chosen_filename}.

Image attribution: "lnav – log monitor" by Linux Screenshots via flickr. License: CC BY 2.0, no changes made to the origin image.

Advertisement:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>