Tuesday, April 15, 2008

Create Database and Migrate Data Example for MySQL

Over the weekend, I was supposed to be preparing for my second Toastmaster's speech which is "organizing your speech." I really dislike public speaking and that was the primary reason I joined up - basically facing my fears. I did the “Icebreaker” speech about three months ago and decided it was time to do another one a couple weeks ago. Predictably, I found everything else in the world to do, except preparing for my speech.

All of sudden, I decided it was "time" to setup a MySQL server, Apache webserver, FTP server, PHP and another CMS interface. I have wanted to install another instance of my website on a local box, but I guess I just needed an impetus (i.e. speech preparation avoidance) to get it done. I performed the setup twice because I had some issues with the initial run and wanted to get it done right. I had enough time, right? Smile.

The part I didn't like was the data migration (always seems like dead time). But anyways, here is how I handled that part from an exported file. Make sure your user has database creation privileges.

Note: Used the "_" to get around Blogger's parser.

mysql -u database_user -p > c:_\tmp\adlistInfile.txt
Enter password: *********

Sample contents of adlistInfile.txt:
create database my_exampledb;
use my_exampledb
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE `ad_admin` (
...
...
PRIMARY KEY (`IdAd`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `ad_admin` VALUES(3, 'exampleuser', '987...', '2008-04-12
22:59:06', '2008-04-12 22:57:09');
...

By the way, I did give my speech today. It was about how the housing, credit, and financial crises are contributing to a possible recession. After getting through the speech, I volunteered for another speech for the next meeting. Well, you know what that means…maybe it is time to build that "time converter" program I have been thinking about in C#.

No comments: