Oily Rag 1: MySQL upgrade 5.0.41 to 5.0.51b

Oily Rag: Be prepared to get your hands dirty.

How to upgrade Uniform Server 3.5-Apollo's MySQL server to 5.0.51b.

Occasionally MySQL updates are significant and require Uniform Server to be updated from scratch. The above version comes into this category, core and associated tables need updating.

Never do this on a production server always start with a clean version of Uniform Server. When you are convinced it works, before making a full commitment try it on a copy of your production server.

Components

You can find the latest MySQL version at http://dev.mysql.com/downloads/mysql/5.0.html#downloads

Note: Older versions can be found here: Archives

Top

Download

Go to the MySQL downloads page scroll down to Windows downloads section. You require the file named Without installer (unzip in C:\) to the right of this click on Pick a mirror

• Save the file mysql-noinstall-5.0.51b-win32.zip to any folder of your choice.
• MD5: 807aee1d3f2ee1097cd84074456abb79 (Size: 55.8M)

Note: If you require a program to check the MD5 pop over to Portable Apps look in the utilities section and download winMd5Sum portable checker

Top

How to Update

Complete list of steps to perform MySQL update:

1. Download the file mysql-noinstall-5.0.51b-win32.zip and save to any folder (e.g. c:\temp_big)
2. Un-Zip the above file into this temporary folder (c:\temp_big)
   A folder named: mysql-noinstall-5.0.51b-win32 is created
   Inside this folder you will find a folder named mysql-5.0.51b-win32 containing folders and files to be copied to Uniform Server.
3. Install a clean copy of 3.5-Apollo to any folder (e.g. c:\new_us)
4. Copy the two folders charsets and english from *\mysql-5.0.51b-win32\share
   To folder *\Uniform Server\udrive\usr\local\mysql\share
   (allow existing content to be over written)
5. Copy folder data from *\mysql-5.0.51b-win32
   To folder *\Uniform Server\udrive\usr\local\mysql
   (allow existing content to be over written)
6. Copy files: mysql.exe, mysqladmin.exe, mysqld-nt.exe and libmysql.dll from folder *\mysql-5.0.51b-win32\bin
   To folder *\Uniform Server\udrive\usr\local\mysql\bin
   allow existing content to be over written)
7. That completes the transfer of folders and files needed for the update. To save space delete folder c:\temp_big (173 MB).
8. Go to folder *\Uniform Server\udrive\usr\local\mysql\bin
   Delete the file mysqld-opt.exe
   Rename the file mysqld-nt.exe to mysqld-opt.exe (Note: This is to ensure backwards compatibility with Uniform Server's control batch files)
9. Copy file libmysql.dll to folder *\Uniform Server\udrive\usr\local\php
   (This keeps PHP happy)
10. Start the servers (both Apache and MySQL will run) from Apanel run phpMyAdmin expect the following error.

MySQL said: Documentation
#1045 - Access denied for user 'root'@'localhost' (using password: YES)

The above installs a clean version of MySQL, there are no passwords set hence trying to access the server using a password, will fail with the above error message.

Top

Setting a new Password

Resolving this access problem requires setting a password and updating grant tables. The method bellow uses command line tools and Windows Task Manger.

I assume you have the servers running (Instructions are referenced to drive W) and the above error message was displayed.

The MySQL server requires restarting with skip grant table option set, this bypasses any security allowing access to the server. A password is set and grant tables updated this requires the use of two command windows and task manger as follows:

Top

Step 1) Kill MySQL process

Open Windows Task Manager by pressing ctrl, alt and del keys together. In the window that opens click on the Process tab search for the file mysqld-opt.exe right click on this and select End Process confirm to kill the process. (Reason for not using stop.bat is to keep the virtual drive running just makes life a little easier)

Top

Step 2) Restart MySQL server - from a command prompt

1. Start a command prompt: Start > click on Run > type cmd click OK
2. Type following lines into the command prompt:
   W:
   cd usr\local\mysql\bin
   mysqld-opt --skip-grant-tables --user=root –console
   
3. Note the flashing cursor indicating server is running. Minimise this window do not close it.

Dialogue in the command prompt looks similar to this:

Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\mike>w: W:\>cd usr\local\mysql\bin W:\usr\local\mysql\bin>mysqld-opt --skip-grant-tables --user=root --console InnoDB: The first specified data file .\ibdata1 did not exist: InnoDB: a new database to be created! 080712 17:26:52 InnoDB: Setting file .\ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 080712 17:26:53 InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 080712 17:26:53 InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 080712 17:26:54 InnoDB: Started; log sequence number 0 0 080712 17:26:54 [Note] mysqld-opt: ready for connections. Version: '5.0.51b-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) _ Note Flashing cursor

Top

3) Set Password and Update grant tables

Start a second command prompt, set new password (root) and update grant tables as follows:

1. Start a command prompt: Start > click on Run > type cmd click OK
2. Type following lines into the command prompt:
   W:
   cd usr\local\mysql\bin
   mysql
   USE mysql;
   UPDATE user SET password=password("root") WHERE user="root";
   flush privileges;
   exit
3. That's it finished.

Dialogue in the command prompt looks similar to this:

Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\mike>w: W:\>cd usr\local\mysql\bin W:\usr\local\mysql\bin>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.51b-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> USE mysql; Database changed mysql> UPDATE user SET password=password("root") WHERE user="root"; Query OK, 3 rows affected (0.47 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.08 sec) mysql> exit Bye W:\usr\local\mysql\bin>

Top

4) Kill process and restart

Final steps:

1. Kill the process mysqld-opt.exe see Step 1), close command prompt windows.
2. Stop the servers using Stop.bat close your browser.
3. Start the servers using Server_Start.bat
4. From apanel run phpMyAdmin.
   Under Uniform Server check Server version: Server version: 5.0.51b-community-nt
   Confirms the update was successful.

Conclusion

Upgrading the MySQL server requires a large number of steps if you do make a mistake repeat all steps from the beginning.

Apart from security updates real reason for making this change is because itcontains a number of bug fixes.

Top

---

Ric