This section lists some of the steps you should take when upgrading MySQL on Windows.
Review Section 2.11.1, “Upgrading MySQL”, for additional information on upgrading MySQL that is not specific to Windows.
You should always back up your current MySQL installation before performing an upgrade. See Section 6.2, “Database Backup Methods”.
Download the latest Windows distribution of MySQL from http://dev.mysql.com.
Before upgrading MySQL, you must stop the server. If the server is installed as a service, stop the service with the following command from the command prompt:
shell> NET STOP MySQL
If you are not running the MySQL server as a service, use the following command to stop it:
shell> C:\mysql\bin\mysqladmin -u root shutdown
If the MySQL root
user account has a
password, you need to invoke mysqladmin
with the -p
option and supply the
password when prompted.
When upgrading to MySQL 4.1.5 or higher from a previous version, or when upgrading from a version of MySQL installed from a Zip archive to a version of MySQL installed with the MySQL Installation Wizard, you must manually remove the previous installation and MySQL service (if the server is installed as a service).
To remove the MySQL service, use the following command:
shell> C:\mysql\bin\mysqld --remove
If you do not remove the existing service, the MySQL Installation Wizard may fail to properly install the new MySQL service.
If you are using the MySQL Installation Wizard, start the wizard as described in Section 2.3.3, “Using the MySQL Installation Wizard”.
If you are installing MySQL from a Zip archive, extract the
archive. You may either overwrite your existing MySQL
installation (usually located at
C:\mysql
), or install it into a
different directory, such as C:\mysql5
.
Overwriting the existing installation is recommended.
If you were running MySQL as a Windows service and you had to remove the service earlier in this procedure, reinstall the service. (See Section 2.3.11, “Starting MySQL as a Windows Service”.)
Restart the server. For example, use NET START MySQL if you run MySQL as a service, or invoke mysqld directly otherwise.
If you encounter errors, see Section 2.3.13, “Troubleshooting a MySQL Installation Under Windows”.
User Comments
If you do forget to remove the service. Especially when uninstalling and installing a fresh copy.
Goto regedit in:
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Service
Find mysql and delete. Reboot to take effect in services manager.
"sc delete mysql" will delete the service and update the list without having to reboot the machine.
Note that the "sc" command was introduced in Windows XP.
The 'sc delete mysql' is also available in the Microsoft Windows 2000 Professional Resource Kit. (Not only XP)
This may seem obvious, but:
Step 6 says to overwrite your existing MySQL install, but if
you do, you'll overwrite any security changes you may have made to the mysql database (most probably the user and db tables). So only overwrite them if you know you haven't made any changes to that database.
You do not need to uninstall the service
Simply change the my.ini file in C:\windows\my.ini
Then copy the new mysqld-nt.exe to the oldversion/bin/ folder.
Change the line basedir=C:/apache/mysql to
basedir=C:/apache/newversion
for example or whatever you called the new folder.
Leave the data folder path the same if you want
datadir=C:/apache/mysql/data or
datadir=C:/apache/newversion/data if you want a new mysql without any data in it.
That way if the new version makes new issues for you (errors) just change the my.ini path back to the old version's folder.
In simple terms there is no need to change your data folder and so you keep all the info and users data safe.
Updating the mysql_fix_privilege_tables is still the same problem for us though.
If during windows installation MySQL server refuses
1 row in set (0.00 sec)to be connected to try to do the following:
1. Check that the MySQL server is running
->ControlPanel->Administrative Tools->Component Services->Services(local)
2. Use the client command without password:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -u root
By default MySQL installation creates the following privileges:
mysql> select user, host, password from user;
Once you got in you can change the privileges and create secure
installation.
I had some fun upgrading from mysql-4.1.15-win32 (mysql 4.1) to mysql-5.0.41-win32. I figured I would write down what I did to save people time. The permissions table thing is still tricky. I used the no-installer to get the version 5 mysql. Then I just swapped the installs as I had done in the past. when I relaunched mysql (mysql5/bin/mysqld-nt --console) I kept getting Error 1130 [HYO 000x] cannot logon locally errors. So this was the mysql permission tables upgrade problem. After attacking the upgrade from a few differnt angles I found the way that worked for me. This is detailed below.
The upgrade took me 15 minutes after I had my plan.
1. download mysql 5 no install.
2. unzip it.
3. export you mysql.db and mysql.user tables using whatever tool you use (make import of old users permissions and table rights)
so you get something like (I used EMS extract)...
use mysql;
INSERT INTO `db` (`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`) VALUES
('%','Database','Table','Y','Y','Y','Y','N','N','N','N','N','N','N','N'),
next rec..
next rec
....);
COMMIT;
#
# Data for the `db` table (LIMIT 0,500)
#
INSERT INTO `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`) VALUES
('%','parts','old-encrypted-password','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0),
...next rec.....;
commit;
save as a text file somewhere.
4. open a command window and end the mysql service, if you run as service.
5. go to mysql install folder/bin and unregister whatever mysql daemon you are using
c:/mysql/bin/mysqld-nt --remove
6. zip up all your old mysql (basedir and DataDir) for
backup and later use.
7. find all your old my.cnf and my.ini files that were left over from previous installs and put them in you backup folder with the other ziped folders so the new install can't see them.
8. place mysql 5's unzipped stuff where ever you want it to run from (mine is c:/mysql5.
9. just let iot use the mysql and test databases at first to get mysql5 running.
10. Set up your my.ini file for mysql 5 and just leave it in the mysql directory (none in %sysroot% any more I guess.
11. start mysql5 in console c:/mysql5/bin/mysqld-nt --console.
12. Try to log into the console with a blank root password (since this is a fresh install).
13. assuming you got this far, type "SHOW DATABASES;" in mysql. Should only be Mysql and Test.
14. Copy you old databases except of course the 4.1 mysql and 4.1 test dbs (if it is still there) to you c:/mysql5/data/ directory.
15. check in mysql console if they can be seen by running "Show Databases;" again.
16. Hopefully that worked, no import your old db and user permission from the sql file you made earlier.
in mysql console: SOURCE "c:/your-file-path.txt";
that should bring in your old user and password in the old format.
17. check if you import worked by selecting from users (use mysql; select * from user;)
18. I was fine at this point besides the old password format, so I ran the Mysql_upgrade.exe file that is mentioned in here a million times, but omly worked for me this way.
18. I ran mysql_upgrade from a batch file:
C:\mysql5\bin\mysql_upgrade.exe --datadir=c:/mysql5/data/ --basedir=c:/mysql5/ -u root
@pause
19. a whole bunch of output about bad table struct, 4.1 style passwords and duplicate columns. when it finsihed I ran it again, and only got the dup column warnings which can be ignored. Then I ran it again just for fun.
20. at this point all the db's are in the right structure, my logins all worked.
21. closed my console session running mysql, closed my console window mysql was running in (--mysqld-nt --console) and moved my data directory back to which it came (personal choice).
22. Checked my cofig file (in mysql base dir ...c:/mysql5/my.ini) using "c/mysql5/mysqld-nt --print-defaults" and they looked fine, changed my datadir="x:/path", and re-ran the --print-defaults.
22. I then reinstalled the msyql as a service with the my.ini file still only in my basedir (c:/mysql5/bin/mysqld-nt --install), and started mysql (net start mysql) or services gui.
23. Last thing, make the root user a password, easily crackable of course like a bad word (don't really do this, but do make a password).
Hope that saves somebody else some time.
-Phil
Citrus Motors
I followed Phil Collett instructions but instead I used the MySQL setup version and the MySQL Administrator Backup/Restore feature to backup the db and user tables.
Everything worked just fine. Thanks Phil for posting your steps. It sure helped me when migrating from 4.1.12 to 5.0.45.
If when you go to install again it can't start the service & it had asked you for the old root password, you need to delete the folder C:\ProgramData\MySQL
This took me 3 hours to discover, its BS that no one seems to have pointed out this fact.
Add your own comment.