WAMP MySQL 4 to 5 Database Migration

Issue – I had installed an AMP server on my pc at work to do development work. The setup was around 5 years ago, so I was using version 4 of php and mysql and version 2 of apache(Which was state of the art at that time). Now I needed to upgrade my setup(MySQL 5 and php 5) for development purposes but leave the current system operating for internal project use.

WAMP came to the rescue since it encapsulates different configurations and prevents conflicts. To prevent conflicts with my current installation I changed the apache server port to 8090 in the httpd.conf file and the MySQL port to 3307 in the my.ini file and the phpMyAdmin config.inc.php file mysql port setting.

I also had to update the references in my php define and include files to include the port when referring to the mysql database. So the mysql server constant would be set to localhost:3307 instead of the standard localhost value.

Get MySQL Working and Database setup
Since my mysql skillset was rusty from using phpMyAdmin I went to www.pantz.org mysql reference site for a refresher on commands I needed.

I successfully created a new database using phpMyAdmin but was having problems setting up users and loading in a new database which was very large.

The first thing I needed to do was add a user to the database that was not root, so I needed to create a neppster user. I went to the WAMP /bin/mysql 5/bin folder and
the following sequence of commands worked by using the command window in this directory and using the mysql command client:

mysql - h localhost -u root

INSERT INTO user(Host,User,Password) VALUES (‘%’, ‘neppster’, PASSWORD(‘xxxxxx’));

flush privileges;

GRANT ALL privileges on nim.* to neppster@localhost;

flush privileges;

The next step was load the latest live and very large database locally in order to test whether it was operational in mysql 5 and whether I needed to do anything to make it work due to changes in the versions.

Loading in a large database
The first attempt I tried was to load it using phpMyAdmin. Due to the default settings, the interface was balking and giving me feedback the the file was tagged as too big when loading data. The provided link recommended that I change the following php.ini constants:

  • set upload_max_filesize to 60M
  • set memory_limit to 128M
  • set post_max_size to 60M

I stopped and restarted all services but still no go. The phpMyAdmin interface was still balking. It was possible that I did not set the values high enough for a 50MB sized file but I decided to come at it from a different angle.

First I massaged the dumped table and changed all MyISAM tables to innoDB format and removed the charset info. I left the auto increment current value as is.

This modification was done to eliminate the MyISAM feature when updating tables that the whole table is locked vs the innoDB feature of only locking the single row which needs to be updated. Our initial site only had a few hundred users, we now have a much larger user base and the MyISAM tables could potentially cause update issues and confusion(due to the php code not generating cogent messages).

Try MySQL Command Client to load
I went to the WAMP /bin/mysql 5/bin folder and did the following:

  • copied the massaged nim_innodb.sql file to this directory
  • Forked a command prompt window to open
  • Tried to login to mysql as neppster with no success
  • Successfully logged in as root
  • mysql –user=root nim
  • SHOW TABLE STATUS returned an empty set
  • source nim_innodb.sql

I then sat back and waited a few hours since there were thousands of lines of entry(mostly for users responses to questions in the post test results tables).

The mysql 5 version of our site database is now ready to be accessed and tested to see that it works in the upgraded scenario.

Eventually I need to wipe out my previous installation and just toggle my WAMP installations as needed since all my code appears to run under the latest versions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: