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

Enable MySQL Enterprise Plugin for Connector/NET

Figure 1. Source Location

In a prior post ( Trace SQL From Database to Source Code ), I showed how to enable SQL trace capabilities for java/MySQL application to trace SQL statements from the database to the exact line of code from which the statement was executed (see Figure 1).  In this post, I’ll enable SQL tracing in the sample C# application, which is included with the MySQL Connector/NET (MySQL’s ADO.NET provider ) install.

The following instructions assume that the MySQL Enterprise Agent and Monitor is already installed.  The Monitor is available for support customers at http://customer.mysql.com or a trial is available at http://www.mysql.com/trials .

Step #1.  Download and install the Connector/NET and the MySQL Enterprise Plugin for Connector/NET

• Connector/NET 6.2.3 or newer version. The assembly is MySQL.Data.dll  Select the option to install the C# samples.
• MySQL Enterprise Plugin for Connnector/NET ( http://customer.mysql.com or http://www.mysql.com/trials ). The assembly is MySQL.MonitorPlugin.dll

Step #2.  Copy the plugin, MySQL.MonitorPlugin.dll to your application directory.  In my case, I’m building and debugging the sample application using Microsoft Visual C# 2008 Express Edition.  The MySQL.MonitorPlugin.dll needs to reside in the same directly as the application’s executable.  Copy the dll to C:\Program Files\MySQL\MySQL Connector Net 6.2.3\Samples\Table Editor\cs\bin\Debug

Step #3.  Open the sample project TableEditor.csproj ( C:\Program Files\MySQL\MySQL Connector Net 6.2.3\Samples\Table Editor\cs\TableEditor.csproj ) in Microsoft Visual Studio and add an App.config file to the project.  For this exercise, I’m using Microsoft Visual C# 2008 Express Edition.

To add the App.config file, select Solution Explorer on the View menu, right-click on TableEditor, the project name.  Point to Add, New Item, and choose Application Configuration File.

In Solution Explorer, double-click App.config to open the file.  Replace the contents of the file with the XML below.  Note: replace the items in brackets [ ] with the appropriate information for your server.  (Omit the brackets) Likewise, the UserID and Password should match the credentials that the agent uses to authenticate to the Monitor.

Copy and paste the following into App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.diagnostics>
    <sources>
      <source name="mysql" switchName="SourceSwitch"    switchType="System.Diagnostics.SourceSwitch">
        <listeners>
          <add name="EMTrace" type="MySql.EMTrace.EMTraceListener, MySql.MonitorPlugin"
                    initializeData=""
            Host="[http://yourServer.com:18080]"
            PostInterval="60"
            UserId="[agent]"
            Password="[mysql]"/>
          </listeners>
      </source>
    </sources>
    <switches>
      <!-- You can set the level at which tracing is to occur -->
      <add name="SourceSwitch" value="All"/>
    </switches>
  </system.diagnostics>
</configuration>

[Note: if you're following the readme or documentation, you'll notice that I omitted the <system.data> section.  I believe this to be an error in the documentation.  In my testing, it never works with the <system.data> entry.]

Step #4.  Modify the connection string to include “logging = true”

Here’s the full connection string that I used while running the sample application:

string connStr = String.Format(“server={0};user id={1}; password={2}; database=mysql; port=3307; pooling=false; logging=true”,server.Text, userid.Text, password.Text );

Step #5.  Compile the application ( Debugging | Start Debugging (F5) ).   On Form1, enter the appropriate Server, User ID and Password for the MySQL Server and toggle the databases and tables.  Watch the Output window, select Output from the View menu in the IDE, and you should start seeing SQL debug information, which will be passed to the MySQL Enterprise Monitor.  In the App.config, the PostInterval is set to 30, meaning that it will take 30 seconds before the data is sent to the monitor.

If you’re a .NET developer, please give it a try.  The query analyzer provides a great view into your application and the database.

Trace SQL From the Database to the Source Code with MySQL Enterprise Monitor

OK, you found the problem SQL statement that was affecting your server’s performance, now where did it originate?

The new MySQL Enterprise Plugins for Connector/J and Connector/NET send query statistics, including the source location for each query, directly to the MySQL Enterprise Monitor.

Figure 1 is a screenshot of new source location feature.

Figure 1. Source Location

Figure 2 shows the standard query statistics, which are collected in the query analyzer.  In both cases, the statistics are gathered by the MySQL Connector and the Plugin, not MySQL proxy.

Figure 2. Query Analyzer

If you’re a MySQL Enterprise customer, you can download the new monitor and plugins from customer.mysql.com.  If you’d like to try it out, a 30 day trial is available at
http://www.mysql.com/trials/

Please review the help file and readme for full instructions, but here are a few of the highlights for setting up monitoring for Java applications.  (I’ll provide further instructions for .NET in a future post.)

1.  Set up the MySQL Enterprise Monitor and Agent.  The agent resides on the MySQL production server and the monitor should reside on a non-production server within your environment.  Although the Plugin gathers the query statistics, the agent needs to run and connect to the monitor at least once so the Monitor can associate the data with the appropriate server.

2.  Download the following files from the customer portal (customer.mysql.com) and add them to your classpath:

• Connector/J version 5.1.12 or newer

• Plugin for Connector/J c-java-mysql-enterprise-plugin-1.0.0.42.jar

3. Change your JDBC url.  In my case, I changed it from the standard JDBC url, “jdbc:mysql:127.0.0.1:3306/sakila , to

“jdbc:mysql://127.0.0.1:3306/sakila?statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters& amp;serviceManagerUrl=http://[mymonitorURL.com]&serviceManagerUser=[myagentusername]& serviceManagerPassword=[myagentpassword]l”;

Full instructions are in the readme.  Substitute appropriate URL and credentials for the bracketed [ ] items above.

4. Finally, the mysql user account that is used by the application needs to have SELECT privilege to the mysql.inventory table.  This table includes a unique identifier which the monitor uses to match the data with the MySQL instance.  I executed the following:

mysql> GRANT SELECT ON mysql.inventory TO ‘elgato’@'%’;

I found it helpful to monitor the application log file for error messages.   The readme includes additional logging information.  In my tomcat test application, the log information was sent to /var/logs/tomcat6/daemon.log file.

I hope this is helpful information.  If you have any issues, feel free to post a comment on this blog post or post on the forums: 
http://forums.mysql.com/list.php?166