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
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
How to Update
Complete list of steps to perform MySQL update:
- Download the file mysql-noinstall-5.0.51b-win32.zip and save to any folder (e.g. c:\temp_big)
- 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. - Install a clean copy of 3.5-Apollo to any folder (e.g. c:\new_us)
- 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) - Copy folder data from *\mysql-5.0.51b-win32
To folder *\Uniform Server\udrive\usr\local\mysql
(allow existing content to be over written) - 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) - That completes the transfer of folders and files needed for the update. To save space delete folder c:\temp_big (173 MB).
- 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) - Copy file libmysql.dll to folder *\Uniform Server\udrive\usr\local\php
(This keeps PHP happy) - 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.
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:
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)
Step 2) Restart MySQL server - from a command prompt
- Start a command prompt: Start > click on Run > type cmd click OK
- Type following lines into the command prompt:
W:
cd usr\local\mysql\bin
mysqld-opt --skip-grant-tables --user=root –console - 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:\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: Setting log file .\ib_logfile0 size to 5 MB InnoDB: Setting log file .\ib_logfile1 size to 5 MB |
3) Set Password and Update grant tables
Start a second command prompt, set new password (root) and update grant tables as follows:
- Start a command prompt: Start > click on Run > type cmd click OK
- 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 - 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 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> USE mysql; mysql> flush privileges; mysql> exit W:\usr\local\mysql\bin> |
4) Kill process and restart
Final steps:
- Kill the process mysqld-opt.exe see Step 1), close command prompt windows.
- Stop the servers using Stop.bat close your browser.
- Start the servers using Server_Start.bat
- 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.
 |
Ric |