Upgrading from MySQL5.5 to 5.6 on Windows.

Update information      Edit(Feb.9)    Edit2(Aug.2)

   Now, MySQL Community Server’s latest version is 5.6.10, and I heard this is the first GA. As I used MySQL5.5 until yesterday, I’ve upgraded to 5.6. I installed the previous version mysql-installer-community-5.5.29.0.msi, but this time I decided to use a Zip archive. I didn’t want to change the installation path name of MySQL because for easy configuration. So I took the next procedures. My server OS is still WindowsXP SP3(x86).

1. Changing our MySQL, we have to back up our current MySQL installation before performing a change. I backed up all my databases by phpMyAdmin. I stored the file my.ini and wrote down the installation path name of MySQL, C:Program FilesMySQLMySQL Server x.x.
2. Control Panel >> Administrative tools >> Services
   Select the MySQL Service name and stop
3. Control Panel >> Add or Remove Programs
   Select the MySQL Server 5.5 and remove

   When you remove it, the application data of MySQL still remains. So, your current databases are maybe all safe. But, if you want more safe, you’d better back up them.
   Upgrading one MySQL Zip archive to another, you can overwrite the existing installation. But, I used the msi to install the previous version, so, I need the uninstaller, if not, a MySQL zombie remains on the list of ‘Add or Remove Programs’.

4. Download and extract the file mysql-5.6.10-win32.zip
5. Remake the folder ‘MySQL Server x.x’ in my C:Program FilesMySQL, then, install the directories and files to the folder. You can install all of extract things. But, I only installed the next 5 directories and 2 files because I do not use others on my server by remote.
   • directories
     bin
     data
     include
     lib
     share
   • files
     COPYING
     my-default.ini

   I also installed my old my.ini to the same hierarchy of the new my-default.ini.

6. As I used the uninstaller, the MySQL Service was also deleted. So I need the new service.

   Run cmd.exe
   cd C:Program FilesMySQLMySQL Server x.xbin
   mysqld.exe –install

7. Control Panel >> Administrative tools >> Services
   Select the MySQL Service name and start

   If its ‘Startup Type’ is not ‘Automatic’, we have to change it to ‘Automatic’.

   When I made to start it, I had an error. orz.
    

8. I checked my event viewer. It gave me “MySQL: unknown variable ‘table_cache=256′”. At MySQL5.6 default I found ‘table_cache′ disabled.

   By the way, why did I set this option? I had no idea. Though I re-learned about ‘ table_cache, max_connections, open_files_limit’, I made no sense of it. Hmm.

   On the new server the default value of max_connections is 151 (<<--- You can know it by the command "show variables like 'max_connections'"). According to Upgrading from MySQL 5.5 to 5.6, between 5.5 and 5.6 have some Configuration Changes. They say ‘The idea behind autosizing’. So I deleted two lines about table_cache and max_connections in my old my.ini.

   Start the service again. Complete!!!

9. Run cmd.exe
   cd C:Program FilesMySQLMySQL Server x.xbin
   mysql_upgrade.exe

   I had the error.
   Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: NO) when trying to connect
   FATAL ERROR: Upgrade failed

   So I did again.
   mysql_upgrade.exe –password[=password]

   Complete. But I have the warning next.
   Warning: Using a password on the command line interface can be insecure.

   My server configuration doesn’t make the command line data go the Internet. If your server permits your command line input to go outside and other users use the server, you maybe need more attentions.

   I restarted the MySQL service. mysql_upgrade — Check and Upgrade MySQL Tables says the need for it.

10. After this, I can access my blog. So I thought I made it, but, without reason I checked my event viewer again. Then, I found the warning next.
    TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).

    I think this is very common warning because Upgrading from MySQL 5.5 to 5.6 has the information about this. I added the next line to the [mysqld] area of the my.ini.
    explicit_defaults_for_timestamp=true

    I restarted the MySQL service. The warning has gone.

That’s it. Mission complete. I’m exhausted! (^_~;)

Edit(Feb.9):
   I had another warning on the log file xxxx.err. Like this.

• InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB’s internal memory allocator.

   I found “innodb_use_sys_malloc=on”. What does this warning say exactly?

   I turned “innodb_use_sys_malloc=off” and got the warning next.

• InnoDB: Warning: Setting innodb_use_sys_malloc to FALSE is DEPRECATED. This option may be removed in future releases, together with the InnoDB’s internal memory allocator.

   As I got it probably said “no need to set the option innodb_additional_mem_pool_size”, I comment-outed innodb_additional_mem_pool_size in the my.ini. By the way, I found out MySQL 5.6 features made much progress gained a good reputation. It is wasted on a tiny blogger like me (^^;).

   I also had ‘IPv6 is not available.’ on the xxxx.err. It’s neither an error nor a warning, though…… I can not deal with it by myself. I wonder my provider is preparing for it.

Edit2(Aug.2):
   I wrote an article about a new installation of a MySQL5.6 on the post ‘To create a Wamp-like Web Server in Windows7-#3.‘.