As the CTO (read one-man-it-shop) of a very small startup1 I have been dragged kicking and screaming into so many different roles over the past few years that I have been forced to get just enough knowledge on most things required to run your own IT infrastructure and products to be able to get by2.
For most common tasks it is pretty easy to just do a quick search online to find out how to do what you need to do, including the most common use-cases and tips to avoid mistakes. So when I had to migrate a MySQL database from one machine to another I didn’t expect it to be too difficult, a quick google search would surely provide the needed help for a very common task for the most popular open source database in the world, right?!?
I did my search, found a large amount of resources providing the mysqldump command required, all with pretty much the same command:
mysqldump -u [username] -p DatabaseName > backupsqlfile.sql
and the following for importing:
mysql -u [username] -p newdatabase < backupsqlfile.sql
So I went ahead and did the import, did a quick check of the imported data, started the application pointing to the new DB, everything seemed in order so I went ahead deleting the original, close the task and went about the rest of my evening considering it a job well done.
Of course around 2am I get a wake-up call - nothing is working!
It turns out that the default parameters for exporting and importing a MySQL database does NOT export and import the entire database. This is quite strange, right? One would expect that by default you would want your data export and import tools to make as close to an exact replica of the original data as possible, but for some reason MySQL developers decided that would be too boring.
Our applications makes extensive use of Stored Procedures, Triggers, Functions and events, of which only triggers are exported by default. Unfortunately the triggers don’t work without the functions… You need to add the following to the export command to get those little useless pieces of code included in your export (note these days I also add --trigger
s just to be safe):
mysqldump -u [username] -p --routines --events DatabaseName > backupsqlfile.sql
As a minimum this should be the export command you use when you export a database. So I went ahead and after jumping through many hoops managed to re-do the export and import the database including the needed SPs etc. and went to bed thinking that now finally everybody should be happy…. this was not the case.
Turns out that none of the comments in the SPs and other code was included in the new database… So I checked the dump file and found that the comments were indeed present. That was a relief but also very confusing. It turns out that during the import process the comments got filtered out somehow. To prevent this from happening you need to add --comments to the mysql command line arguments, like this:
mysql --comments -u [username] -p newdatabase < backupsqlfile.sql
Finally I managed to import the entire database onto the new server and everybody was happy.
To summarize - the minimal mysql dump and import commands to migrate an entire database would be:
mysqldump -u [username] -p --routines --events DatabaseName > backupsqlfile.sql
mysql --comments -u [username] -p newdatabase < backupsqlfile.sql
If you want to export all databases, you can add the --all-databases
parameter, this will create one enormous file with the data of all databases. I find this is rarely what I want, so the following shell script command is quite useful to do the export of all databases into different files:
First you need to extract the list of DBs:
mysql -u root -p -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > db_list.txt
Then you dump all DBs in the db_list.txt file like so:
for DB in `cat db_list.txt`; do mysqldump -u root -p --hex-blob --routines --triggers --events --quote-names --quick --add-drop-table --allow-keywords --single-transaction --skip-lock-tables --skip-add-locks --extended-insert --create-options --comments --set-gtid-purged=OFF ${DB} > ${DB}.sql & done
Note that --set-gtid-purged=OFF is for when you don't care about gtids
If for some reason you need to dump ONLY the SPs and Routines:
for DB in `cat db_list.txt`; do mysqldump -u root -p --routines --triggers --add-drop-trigger --quote-names --allow-keywords --comments -n -d -t --set-gtid-purged=OFF --skip-lock-tables --skip-add-locks ${DB} > ${DB}.sql & done
Note that --set-gtid-purged=OFF is for when you don't care about gtids
can you call yourself a startup after 5 years of just getting by on a single contract?
and in some cases be pretty dangerous!