Generate mySQL dump for mySQL 4.x and below

If you have developed a website in mySQL 5.x and your client somehow manages to purchase a hosting which supports mySQL 4.x (don’t know how this could happen but it happened to me once) then you can do the following to generate SQL script in the right version by using

mySQLDump
Go to the command prompt. Log into mySQL using the command

mysql –user=<user_name> –password=<pass_word> <db_name>

After logging in issue the command

mysqldump –compatible=mysql40 <db_name>

If you want to learn more about the other options of this great utility then visit mysql.com website.

PHPmyAdmin
This is the easier method. Login into PHPmyAdmin. Select the database. Click on Export. On the Export page you will be provided with a drop down from which you can select the format in which you want to export the dump. After selecting the desired option click on Export.

Hope the above helps.

No website response due to auto close in SQL Server 2005

I had been working on a website based on ASP.NET 2.0 and SQL Server 2005 Express Edition. However I was facing a problem on it. Problem was that the website worked all right during the busy hours such as in the morning and afternoon but in the wee hours of the night the website would hang i.e. website did not give response at all and some how the website used up all the server memory and processing speed.

The problem was peculiar in that it happened when there was no traffic on the website and thereafter it worked fine. I ignored it thinking it might be due to the server maintenance that usually went on at that time however when this started to happen frequently and the client began to lose patience I knew this had to be tackled soon.

So I looked at the SQL Server logs. I found nothing in it. Then I looked at the web server logs thinking some one may be trying to use brute force or DOS attack on the website. I again found nothing.  This started to become confusing.

Out of nowhere I thought why not look at the Event Viewer and surely I saw that the SQL Server database was being closed at some time and then it was opened. I deduced that the time when it was opened must be the same time at which the website did not respond and caused that hanging effect.

I knew that SQL Server had an auto-close feature but this feature was by default unchecked in SQL Server 2000 where as after going through Books Online I found that it was by default checked in SQL Server 2005 due to which I faced this problem.

I unchecked the Auto Close option in SQL Server 2005 and the problem was resolved.

How to move large sized mySQL database

We have all moved databases from one hosting machine to another by creating SQL dumps with PhpMyAdmin or any other mySQL desktop client on the source machine and then running those SQL scripts on the destination machine. This is fine when the size of the database is a few MBs. However when the size is several hundred MBs then it requires some other procedure. Below is one such procedure for moving a large sized mySQL database.

The following technique applies to mySQL database in which all tables store data in the MyISAM format.

1.  Go to the physical location where the mySQL database maintains its FRM and MDY files on the source machine.

2. Download all the files on which the database is based on.

3. Create database with same name on the destination machine.

4. Upload the files which you have downloaded.

5. Copy the files to the location where the mySQL database maintains the FRM and MDY files on the destination machine. PLEASE STOP mySQL SERVICE ON THE DESTINATION MACHINE BEFORE COPYING THE FILES.

6. Start the mySQL database.

You can start using the database as you normally would.