Using MySQL under Windows NT, Part 2By Kerry Garrison
In Part 1 we looked at what, exactly, mySQL is, where to obtain it, and how to get some GUI tools for management. In this part we'll look at accomplishing some standard database tasks, such as: granting access permissions, creating a table, inserting data, etc.!
So now we have our user set up, now it's time to create a database table, populate some data, and try getting some results.
- Create the database:
\mysql\bindirectory you will find an application called
winmysqladmin, you can view tons of information about your MySQL installation, you can also use it to create new databases.
For the purposes of this example, click on the Databases tab, right-click on the server name in the left hand window to create a new database. Name your database
- Setting up our test table:
You can use the command line tools to manage databases, tables, and users. I prefer using DBTools as described above. When you create a new user using DBTools, it gives you an easy interface to grant different permissions for each use to specific databases. The method for using the command line tool is given below those of you who like to do it "the hard way".
- Grant rights to our test user:
If you wish to use a different user account than root (and you should) then your next step would be to create the user account you want. You can either use the admin tools discussed later in this article, or simply do it from the command line as shown here with the fictional account
D:\mysql\bin>mysql -uroot -proot_password
mysql> use mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO bubba@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO bubba@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
This sets up our user
Bubba; as a full superuser who can connect to the server from anywhere, but who must use a password
some_passto do so. Note that you must issue
GRANTstatements for both
bubba@"%"to prevent the anonymous user created during the install will take precendence.
- Create the table:
mysql -ububba dbtest mysql> create table linkdemo ( -> LinkName varchar(50), -> LinkURL varchar(150) -> ); Query OK, 0 rows affected (0.07 sec)
- Populate the table with some data:
Again, you can use either the command line tools or DBTools to import your data. To use DBTools, select the database table and use the SQL Query tool to run the following queries. From the command line, you can enter them by starting up the MySQL tool as follows:
D:\mysql\bin>mysql -ububba -psome_pass
INSERT INTO LinkDemo (LinkName,LinkURL) VALUES ('Yahoo','http://www.yahoo.com');
INSERT INTO LinkDemo (LinkName,LinkURL) VALUES ('Google','http://www.google.com');
INSERT INTO LinkDemo (LinkName,LinkURL) VALUES ('Alta Vista','http://www.altavista.com');
INSERT INTO LinkDemo (LinkName,LinkURL) VALUES ('InfoSeek','http://www.infoseek.com');
Next, create an ODBC connection to your table using the ODBC control panel. At this point your data is ready for you to access via and standard ODBC connection.
The following ASP code snippet was originally used with a Microsoft Access database and required no modifications to work with MySQL.
Set MyConn = Server.CreateObject("ADODB.Connection") MyConn.Open "dbTest" SQL_query = "SELECT * FROM Links;" Set RS = MyConn.Execute(SQL_query) WHILE NOT RS.EOF Response.write "<a href=""" & rs("LinkURL") & _ """>" & rs("LinkName") & "</a><BR>" RS.MoveNext Wend
So there you have it, a fully functioning SQL Database server populated with data and returning the data into and ASP page.
One further advantage of using MySQL is that it is available for multiple platforms. You may want to develop your website using Active Server Pages on a Windows NT/2000 platform but don't want the financial hit of having to purchase a second copy on NT to run the database. By putting the MySQL database engine on an inexpensive Linux server, your cost savings could really add up. The same Windows based administration tools such as DBTools described above, will still connect to a Unix based MySQL server making administration identical regardless of platform.
In the short time I have been using MySQL, I have grown to like it a lot. It is fast, reliable, and with the visual management tools, very easy to work with. Being designed from the beginning for speed, it actually takes up a surprisingly small amount of resources on a server allowing some sites to run both MySQL and their web server on a single machine that would normally require two servers when running a larger database.
Kerry Garrison is the Senior Sales Engineer for Dedicated Hosting Products at XO Communications.