Friday, August 1, 2014

Basic Windows MySQL Installation Without Installer

It dawned on me that most folks are using the Installer these days.

As I need quick access to every MySQL version,  using an Installer is never an option.
And for folks wanting 100% control over their setups, they may not want an installer doing things.

So this shows how to setup an instance manually.

  1. download non-installer .zip version from dev.mysql.com
  2. create a directory c:\mysql and c:\mysql\tmp
  3. unzip the .zip into c:\mysql
  4. move the data directory into c:\mysql for easier future upgrades
  5. create a basic my.ini
  6. install the service
  7. start the service


Here I'll show each step with more detail.   I purposely leave out things like post-installation security, to keep it simple.

1.  Create a directory.

Decide where you will put the installation and datadir.  I use c:\mysql and c:\mysql\data
since I truly despise the "windows way" with long paths such as "c:\Program Files\MySQL Server 5.6" ...

C:\>mkdir mysql
C:\>cd mysql
C:\mysql>mkdir tmp
C:\mysql>dir
 Volume in drive C has no label.
 Volume Serial Number is 802E-2730
 Directory of C:\mysql
2014/08/01  09:31    <DIR>          .
2014/08/01  09:31    <DIR>          ..
2014/08/01  09:31    <DIR>          tmp
               0 File(s)              0 bytes
               3 Dir(s)  74 040 700 928 bytes free

2.   Download the non-installer .zip version.

       Use a browser to download the latest version, for example:  


3.   Extract the zip file.


I use 7zip or winrar, but windows explorer can also be used to extract the .zip file right here.


4.  Move the data directory

The non-installer .zip comes with a data directory which I will use in this installation.
As you might want to upgrade the instance later,  I prefer to put the datadir a separate location to the version just downloaded.


C:\mysql>dir
 Volume in drive C has no label.
 Volume Serial Number is 802E-2730
 Directory of C:\mysql
2014/08/01  08:11    <DIR>          .
2014/08/01  08:11    <DIR>          ..
2014/08/01  08:11    <DIR>          tmp
2014/08/01  08:11    <DIR>          mysql-5.6.20-win32
2014/08/01  07:52       353 970 000 mysql-5.6.20-win32.zip
               1 File(s)    353 970 000 bytes
               4 Dir(s)  73 969 897 472 bytes free
C:\mysql>move mysql-5.6.20-win32\data data
        1 dir(s) moved.



5.  Write the my.ini.

I'll keep the my.ini in the datadir, to lesson complexity.

C:\mysql>notepad data\my.ini
C:\mysql>type data\my.ini
[mysqld]
datadir=c:/mysql/data
tmpdir=c:/mysql/tmp
log-error=c:/mysql/data/mysql.err
port=3306
slow-start-timeout=0
log-warnings=2


6.  Install mysqld as a service.

This part seems confusing due to the options used.  To keep things clear, I use a specific service name for each version, so that I know what it is later.
You must be running cmd.exe as an administrative user to do this.


C:\mysql>cd mysql-5.6.20-win32
C:\mysql\mysql-5.6.20-win32>cd bin
C:\mysql\mysql-5.6.20-win32\bin>mysqld.exe --install MySQL_5620 --defaults-file=c:/mysql/data/my.ini --local-service
Service successfully installed.
C:\mysql\mysql-5.6.20-win32\bin>cd..\..\


7.   Start the service.



C:\mysql>sc start MySQL_5620
SERVICE_NAME: MySQL_5620
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 2  START_PENDING
                                (NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x1
        WAIT_HINT          : 0x1f40
        PID                : 4352
        FLAGS              :

If you have larger innodb settings, give it a few seconds/minutes to start, then check it.


C:\mysql>mysql-5.6.20-win32\bin\mysql.exe --no-defaults -h127.0.0.1 -uroot -P3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql-5.6.20-win32\bin\mysql.exe  Ver 14.14 Distrib 5.6.20, for Win32 (x86)
Connection id:          1
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.20 MySQL Community Server (GPL)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3306
Uptime:                 2 min 37 sec
Threads: 1  Questions: 5  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.031
--------------

The way I personally stay sane with >50 versions on my machine is to follow simple rules:

  • not any MySQL products 'installed' on my work machine.
  • not any global my.cnf and my.ini lurking around.
  • always use --no-defaults when running mysql programs, in case I broke the last rule.
  • not any mysql program in the path.

Note, the manual pages cover everything here, and even more verbosely.

The reason I prefer this method is that upgrades are generally easier. You simply download the next 5.6.21, extract it, delete the existing MySQL_5620 service, create a new MySQL_5621 service using same command, and run mysql_upgrade once it's started.