Mastering MySQL: Drivers, clustering and remote access

MySQL expert Scott Noyes describes the tricky aspects of clustering with MySQL and upgrading from MySQL 4.0 to MySQL 5.0.

The benefits of using MySQL Cluster and MySQL 5.0 are many, and so are the gotchas in implementing them, says Scott Noyes, Bookit.com's senior Web apps developer and SearchOpenSource.com's resident MySQL expert. In this Q&A, he uncovers some of those implementation roadblocks. He also gives advice on where to find and how to choose drivers.

What are some issues that users may encounter when upgrading from MySQL 4.0 to MySQL 5.0?

Scott Noyes: Here are a few common problems:

See also the upgrade guide.

What options exist for clustering in MySQL? How can I setup and configure clusters in MySQL?

More tips on MySQL:
SQL Server vs. MySQL: Syntax differences, similar GUIs 

MySQL tips and tricks 

Noyes: MySQL Cluster is free, just like the rest of the MySQL software. Configuration can be daunting, especially for a mission-critical situation. If your business will depend on Cluster, I recommend purchasing a MySQL Network subscription, which entitles you to official support. If you are just curious and want to set up Cluster for your own education, everything you need to know can be found in the documentation.

Which MySQL API sets would you recommend? Which are the most stable?

Noyes: Driver use depends on the type of connection you are attempting to make; what programming language or application you want to connect to MySQL. Generally speaking, use whichever language or app is currently listed for your language.

What are some ways to access and control remote MySQL DBs in VB6 using either sockets or a free VB control? Can you offer examples of how to manipulate them?

Noyes: MySQL supports ODBC. Download the ODBC drivers. In your VB project, add the Microsoft Remote Data Object and connect. Your connection code might look something like this:

 Dim cnMySql As New rdoConnection cnMySql.CursorDriver = rdUseOdbc cnMySql.Connect = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=yourHostName;PORT=3306;" & _ "DATABASE=dbName;" & _ "UID=yourUserName;PWD=yourPassword;" & _ "OPTION=3;"

Check the values for the OPTION parameter.

Can you give some advice on connecting MySQL to ASP pages? What drivers should be used?

Noyes: You can connect ASP pages to MySQL using ODBC. Download the ODBC drivers. Once you have MyODBC configured, you can connect to MySQL with or without DSN.

 Using DSN: <% set conn = Server.CreateObject("ADODB.Connection") conn.Open "DSN=mysql_dsn" %> Without DSN: <% set conn = createobject("ADODB.Connection") conn.open = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=yourHostName;" & _ "DATABASE=yourDBName;" & _ "UID=yourUserName;PWD=yourPassword; OPTION=35;" %>

Check for values for the OPTION parameter.

This was first published in September 2006

Dig deeper on Open source databases

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close