Virtualizing your database instances is one of the pleasures of running virtual machines (VMs). Here are a few situations and resolutions that I have had to deal with in the past few months, and the nitty-gritty of how I escaped database pain.

MySQL database move
Recently, I had to migrate a database from one VM to another, while neither taking down or restarting either VM or running MySQL instance. Normally, this can be accomplished with some form of:

mysqldump databasename >filetodumpto.sql
mysql -u user -ppassword databasename < filetodumpto.sql

Exporting and then importing works when trying to move data, but when you introduce binary blobs and GBs of data, it becomes much more time-consuming.

The database contained approx. 6GB worth of data, so the way I chose was to simply copy (SCP) the files from one server to the other, rather than performing a mysqldump command (which would create a rather large and impractical SQL script).

The only problem is, when you copy the files from server to server, the new server will read the new (database) files as 'corrupt' until they are 'repaired' (assuming the mysql server versions are compatible).

Thankfully, there is an all-powerful program that every MySQL wannabe should have in their technical repertoire: mysqlcheck. When migrating database servers, recovering from a mysql server crash, repairing corrupt databases/tables, or other unexpected database/table catastrophes occur, the 'mysqlcheck'

program comes to the rescue.

Here's a fairly standard workflow:

scp -r /var/lib/mysql/dbname server:/var/lib/mysql/ 
chown -R mysql:mysql /var/lib/dbname
mysqlcheck -h localhost -u user -r -p -B dbname

The mysqlcheck program often helps when you are powering off (accidentally or otherwise) VMs and you corrupt your mysql instances/table/etc.

Importing data from Windows to MySQL in a VM
The situation was that the CFO needed daily reporting spit out from his mini-VB program, directly into a MySQL DB running in a VM. His program output the numbers into CSV format, an easily-parseable text format that uses commas to separate DB tables, much like a spreadsheet (which is why spreadsheets spit out this format so often).

Obviously, you need to get at the data first. You can either mount a Samba share or ftp the file to your running VM, stick it out on a NFS share, etc.

In MySQL, your query would look like this, with long explanation below it:

LOAD DATA LOCAL INFILE '/home/vm/CFO_output.csv' 
INTO TABLE db_example_name.tbl_secret_stuff 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\'' 
LINES TERMINATED BY '\r\n' 
(example_field_name,example_field_city,example_field_state); 

And here is a breakdown of what that meant:
- load data local infile
Lets MySQL know where the data lives.
- into table
the into table declaration signifies the database and table to be used for the data
- fields terminated by
the fields terminated by declaration assigns the field-separator flag, which tells mysql when the field ends. A comma works here, since it's a CSV file.
- enclosed by
the enclosed by declaration assigns the character in which we use to wrap string values in each field. (in this case, we use double-quotes, which is extremely common)
- lines terminated by
Whether the file has Windows or UNIX-style line endings (it matters).

And there you are with spreadsheet data in your MySQL DB.

Debugging data-changing queries
Let's look at debugging your data-changing queries or binary binlogs to ASCII, for those who aren't conversant in binary.

Often, you will be working on a VM and trying to figure out how the heck your data changed, when and by whom. This tip's short, but can save you hours of hassle by relying on your binlogs (binary logs that MySQL keeps track of changes in).

In each binlog file there's a log of all INSERT, UPDATE and DELETE queries performed on the databases. If you open such a file in a text editor, it's kind of hard to figure out what exactly happened, since it looks all jumbled. Thankfully, there's a nice tool to convert those binary binlog files to plain ASCII SQL statements. It's called mysqlbinlog.

Running this tool on your binlogs will spit out genuine, readable ASCII data that you can grep, cut, paste and awk to your heart's delight. For example, the following command would give you all the queries that affected everything but the wandusage db:

mysqlbinlog binarystinks.001 > binarynomore.log 
or even:
mysqlbinlog binaryisformuggles.001 |grep -v wandusage

If you are running into problems with a MySQL instance in a VM, and you want to debug the MySQL program and what's running inside it, a good command is:

mysqladmin -v -uroot -p -i 1 processlist

This command gives you a running processlist of what's running on the DB server. It can help a lot.

About the author: Brendan Barry has been professionally involved in the technology sector for 12 years. In 2005, Brendan started his own technical training school and development consultancy, and simultaneously became heavily involved with virtualizing an existing company's infrastructure, saving them hundreds of thousands of dollars in the process.

This was first published in August 2007