Mastering MySQL: Drivers, clustering and remote access

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:

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.