How to Create MySQL Databases to Install DSN

A DSN enables you to transfer data between two ODBC-compliant programs.

MySQL is a free, open-source RDBMS (relational database management system) that is able to provide access to databases to multiple users, using powerful and highly customizable security features. MySQL is ODBC-compliant, so you can set up a DSN (Data Source Name) to move data to and from the database. In this case, the MySQL database is the data source.

Things You'll Need

• MySQL server

Instructions

1. Open your favorite Web browser and navigate to MySQL's ODBC Connector page (see Resources). Click on the "Download" button next to the version of Windows you are using and save the driver on your hard disk. Double-click on the file you just downloaded and follow the instructions you see on your screen to install the ODBC driver.

2. Open the MySQL client. Type the following without the quotes. Type "mysql --user=root mysql" without the quotes and press "Enter" to log in to your MySQL server as root. Type "create user 'username'@'localhost' identified by 'password';" and press "Enter" to create a new user. Type "grant all privileges on *.* to 'username'@'localhost'" and press "Enter" to grant administrator access to the new user.

3. Type "create database testdb" without quotes and press "Enter" to create a new database. You do not need to create tables or insert data into the new database to set up DSN.

4. Go to the "Start" menu and type "ODBC" in the "Run" box and click on the "Data Sources (ODBC)" result. Click on the "System DSN" tab. Click on the "Add" button to add a new data source.

5. Select "SQL Server" from the list and click on the "Finish" button. Type a name for your new data source in the "Name" text box and a description in the "Description" text box. Click on the "Server" drop-down box and select your MySQL server. If you run the server on your computer, use "localhost".

6. Type the username you created earlier in the "Username" text box and the corresponding password in the "Password" box. Select the "testdb" database you created earlier in the "Database" drop-down box.

7. Click on the "Test" button to test the connection. A success message is displayed if you used the correct login information. Click on the "OK" to finish creating the DSN.

Tips & Warnings

• You can install multiple source names to work with multiple MySQL databases.

References

• Geeks Engine: How to Set Up DSN in ODBC Data Source Administrator
• MySQL: Creating and Selecting a Database

Resources

• MySQL: Download Connector/ODBC
• Indiana University: Getting Started Using ODBC with SAS and SPSS