View source for Oily Rag 1: MySQL upgrade 5.0.41 to 5.0.51b

{{Uc nav oily rag 1}}
'''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 http://dev.mysql.com/downloads/mysql/5.0.html#downloads] 

'''''Note'':''' Older versions can be found here: [http://downloads.mysql.com/archives.php Archives] 

'''''[[#top | Top]]'''''
== Download ==
Go to the MySQL [http://dev.mysql.com/downloads/mysql/5.0.html#downloads 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 [http://portableapps.com/apps/utilities/winmd5sum_portable Portable Apps] look in the utilities section and download '''winMd5Sum portable checker'''

'''''[[#top | Top]]'''''
== 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)<br>A folder named: '''mysql-noinstall-5.0.51b-win32''' is created<br>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'''<br>To folder '''*\Uniform Server\udrive\usr\local\mysql\share'''<br>(allow existing content to be over written)
# Copy folder '''data''' from '''*\mysql-5.0.51b-win32'''<br>To folder '''*\Uniform Server\udrive\usr\local\mysql'''<br>(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'''<br>To folder *\Uniform Server\udrive\usr\local\mysql\bin<br>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'''<br>Delete the file '''mysqld-opt.exe'''<br>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'''<br>(This keeps PHP happy)
# Start the servers (both Apache and MySQL will run) from Apanel run phpMyAdmin expect the following error.

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

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 | 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 | 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 | Top]]'''''
=== 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:<br>''' W:'''<br>'''cd usr\local\mysql\bin'''<br>'''mysqld-opt --skip-grant-tables --user=root –console'''<br>
# Note the flashing cursor indicating server is running. Minimise this window '''do not close it'''.

Dialogue in the command prompt looks similar to this:

{|cellpadding="6" style="border: 1px solid #c6c9ff;background:#f0f0ff"
|-
|
Microsoft Windows XP [Version 5.1.2600]<br>
(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'''<br>
InnoDB: The first specified data file .\ibdata1 did not exist:<br>
InnoDB: a new database to be created!<br>
080712 17:26:52  InnoDB: Setting file .\ibdata1 size to 10 MB<br>
InnoDB: Database physically writes the file full: wait...<br>
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<br>
InnoDB: Database physically writes the file full: wait...<br>
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<br>
InnoDB: Database physically writes the file full: wait...<br>
InnoDB: Doublewrite buffer not found: creating new<br>
InnoDB: Doublewrite buffer created<br>
InnoDB: Creating foreign key constraint system tables<br>
InnoDB: Foreign key constraint system tables created<br>
080712 17:26:54  InnoDB: Started; log sequence number 0 0<br>
080712 17:26:54 [Note] mysqld-opt: ready for connections.<br>
Version: '5.0.51b-community-nt'  socket: <nowiki>''</nowiki>  port: 3306  MySQL Community Edition (GPL)<br>
'''_'''<span style="color:#0000ff"> ''Note Flashing cursor''</span>
|} 

'''''[[#top | Top]]'''''

=== 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:<br>'''W:'''<br>'''cd usr\local\mysql\bin'''<br>'''mysql'''<br>'''USE mysql;'''<br>'''UPDATE user SET password=password("root") WHERE user="root";'''<br>'''flush privileges;'''<br>'''exit'''
# That's it finished.

Dialogue in the command prompt looks similar to this:

{|cellpadding="6" style="border: 1px solid #c6c9ff;background:#f0f0ff"
|-
|
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'''<br>
Welcome to the MySQL monitor.  Commands end with ; or \g.<br>
Your MySQL connection id is 1<br>
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;'''<br>
Database changed<br>
mysql> '''UPDATE user SET password=password("root") WHERE user="root";'''<br>
Query OK, 3 rows affected (0.47 sec)<br>
Rows matched: 3  Changed: 3  Warnings: 0

mysql> '''flush privileges;'''<br>
Query OK, 0 rows affected (0.08 sec)

mysql> '''exit'''<br>
Bye

W:\usr\local\mysql\bin>
|}

'''''[[#top | Top]]'''''

=== 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'''.<br>Under Uniform Server check Server version: Server version: 5.0.51b-community-nt<br>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 | Top]]'''''

----

{|
| [[Image:uc_small_logo.gif]] || [[User:Ric|Ric]]
|}

[[Category: UniCenter]]
[[Category: Oily Rag]]
[[Category: MySQL]]
[[Category: Support]]
[[Category: Patches]]
[[Category: Troubleshooting]]