Sure-fire MySQL Install on Windows

Due to firewalls, virus scans, corporate security restrictions, or just plain bad luck; there are times when MySQL just won’t install on a specific Windows server.  Here’s a sure-fire install method.   Relax, I won’t have you run the msi installer again.

The first step is to remove directories from your previous install attempts.  Uninstall from the control panel.  Manually, rename or delete “C:\Program Files\MySQL\MySQL Server 5.1″.  Next, delete (or rename) the data directory.  Warning! If you have previously entered data into MySQL, deleting the data directory will delete data.  The directory is located at “C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data”.   You may need to unhide the data directory.

Let’s get started.

#1.  Download the no-install MySQL Version for Windows.  Choose either “Windows (x86, 32-bit) ZIP Archive” or “Windows (x86, 64-bit) ZIP Archive” based on whether your system is 64 or 32-bit .

#2.  Unzip the files to “C:\Program Files\MySQL\MySQL Server 5.1″.

#3.  Use notepad or wordpad to create a my.ini file and save the file in the MySQL directory, “C:\Program Files\MySQL\MySQL Server 5.1″   Be careful that notepad doesn’t add a default “.txt” extension to the my.ini file ( http://support.microsoft.com/kb/253688 )

Add the following following to your my.ini file and save.

[mysqld]
##update basedir if you move the install location
basedir="C:/Program Files/MySQL/MySQL Server 5.1"

##update datadir if you move the data directory
datadir="C:/Program Files/MySQL/MySQL Server 5.1/data"

## increase to 40% of RAM if using MyISAM
key_buffer_size=32MB

## increase to 80% of available RAM for production usage
innodb_buffer_pool_size = 128MB

## may want to increase if write intensive
innodb_log_file_size = 64MB
innodb_log_buffer_size=8MB
table_cache=1024
thread_cache=16
query_cache_size=32M

Note the basedir and the datadir variables.  If you wish to install in a different location or put the data in a different location, update these values accordingly.

#3.  Create and start the service.    Open a command prompt, and enter the following:

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld"  --install "MySQL 5.1" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\my.ini"

This installs the server.  To start the service, type

net start "MySQL 5.1"

or start the service from the control panel.

You should be up and running.  If not, check your err log in “C:\Program Files\MySQL\MySQL Server 5.1\Data”

#4.  Connect from your client application and update your user accounts.  By default, there are 3 accounts: root@localhost, root@127.0.0.1 and an anonymous account.

The following commands log in with the mysql command-line client, remove the anonymous account and set the password for the root accounts.

At a command prompt, enter the following:

"c:\program files\mysql\mysql 5.1 server\bin\mysql" -uroot

This will log you into MySQL and you should have a mysql command prompt like mysql>

Here are the commands to delete your anonymous account and set passwords for your two root accounts.  When setting the password, substitute your desired password for “mypassword”.

mysql> DROP USER ""@localhost;
mysql> Set PASSWORD FOR "root"@"localhost" = password("mypassword");
mysql> Set PASSWORD FOR "root"@"127.0.0.1" = password("mypassword");
mysql> flush privileges;
mysql> exit

Feel free to post any questions/comments or issues to the comment section of this blog.  For more comprehensive instructions see http://dev.mysql.com/doc/refman/5.1/en/windows-install-archive.html

Launch MySQL from the Command Line

In my last blog entry, I discussed a “silent” install method for deploying MySQL on Windows.  However, the best way to distribute MySQL may be to not install it.  Instead, design the application to launch mysql from the command line.  With this method, you can deploy with two files, avoid registry entries, and hide database files from end-users and their IT staff.

[Note:  these instructions may also be useful if you can't get the MySQL installer to run on a particular Windows machine.  I find it easier to copy the system tables from an existing install of MySQL that was deployed with the Windows MSI installer.  Nevertheless, you could download the "without installer" MySQL zip file and use the mysql_system_tables.sql and mysql_system_table_data.sql to create your system tables.  I had to remove the "WHERE @had_user_table=0" from the mysql_system_table_data.sql to get it to work.  The SC.exe tool can be used to add it to the services]

The following is a minimum install example.  I prefer to run the full install using the Windows Essentials or the Windows MSI Installer download from http://dev.mysql.com/downloads/mysql/5.1.html#win32 .  Stop the service and use the files that were generated from the install to set up MySQL on your target machine.

Step 1.  Create an empty directory on the target server.  I created c:\min.  From an existing MySQL install, copy the following files into c:\min:

mysqld.exe

errmsg.sys

In my unmodified 5.1 version, mysqld.exe is 8.2 MB and errmsg.sys is 36K.  The following command will launch the full version of the server with only the aforementioned files:

Step 2.  c:\min>mysqld.exe --language=. --datadir=. --skip-grant-tables --bind-address=localhost

That’s it.  MySQL is running and you can now connect and create tables.

I used the following server options:

--language=. This is an essential parameter.  Your install needs to find errmsg.sys to start.  I retrieved this file from the “share” folder in an existing installation.

--datadir=. This determines the location of the data directory.  In my simple example, I put the data files in the application directory.  You may prefer to specify a data directory in the recommended Windows application directory.

--skip-grant-tables This variable allows MySQL to load without the privileges table.  Anyone can connect without authenticating.   I’ve specified --bind-address for only local connections. If you’re deploying to a server in a secure location on a private network and MySQL is only running when your application is running, this may be acceptable.

--bind-address=localhost  This option determines the IP address for which to bind, it will only listen on localhost.  As an alternative, it’s possible to run MySQL on windows with named pipes or shared memory.

To force authentication, you will need to include the privilege tables.  In this case, create a data directory.  In my case I used c:\min\data .  Then, copy the mysql folder from an existing install into the data directory.  Then, start MySQL and specify the new data directory:

c:\min>mysqld.exe --language=. --datadir=./data

Since these are MyISAM files, MySQL will recognize the additional database and files.  Also, omit the --skip-grant-tables parameter.  Now, you’ll be prompted for a password.  It will be the same password that you specified when you ran the full install.

MySQL is running.  What’s next?  If you’re deploying an application, you’ll need to deploy your initial schema and data.  The simplest way would be to dump the schema and tables from your development machine and then load in the sql script on your target server.  Documentation for dumping a loading and database can be found here: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

There are many startup options and system variables which can be specified on the command line or the configuration file (--defaults-file).

Here are the appropriate links:

http://dev.mysql.com/doc/refman/5.1/en/server-options.html

http://dev.mysql.com/doc/refman/5.1/en/using-system-variables.html

If only using MyISAM, you might want to start MySQL with --skip-innodb.  You can use these options to make your installation more secure. You may want to change the default TCP/IP port.  If using InnoDB, you can set innodb-data-file-path to a different location, a more obscure filename and a different size.  This would prevent someone from copying the data to another computer and trying to access the data with a different application.

There are other ways to deploy MySQL with your application, including using the “silent” install method or using the deeply embedded library (libmysqld).  If you’d prefer that your customer has full visibility and access to MySQL, a “silent” install adds MySQL to the program files, service control panel and the Add/Remove program options.  If you’re developing a stand-alone application in C/C++, the deeply embedded library (libmysqld) provides excellent performance with a small footprint. Otherwise, your best option may be to  launch MySQL from the command line.  This method provides a simple and lightweight deployment with all the MySQL server capabilities.