Configuring MySql for your LAMA server and Visual Web Developer

 

This is part of the LAMA – Best of all worlds tutorial
LAMA Tutorial Home Page

MySql Connector .Net

MySql Connector/Net is the component that allows Asp.net to talk to Mysql Databases.

You will need this connector to be installed on the machine that has Visual Web Developer installed.
The latest version of MySql Connector is 5.2. 

Download the Widows Binary (ZIP) file from MySql Website:
http://dev.mysql.com/downloads/connector/net/5.2.html

1.)Unzip and go thru the Installer.  When it is completed, we will have to add the Assembly file to the LAMA Server.

2.)Navigate to the MySql Connector/Net folder inside Program Files.  This is usually:

C:\Program Files\MySQL\MySQL Connector Net 5.2.1

3.)Navigate into the Binaries\.NET 2.0\ folder.

You should see a MySql.Data.dll file.

4.)Copy that file into your asp_net share on your LAMA server.

5.)Now use Putty to connect to your LAMA server.

6.)Change to the asp_net directory

cd /asp_net

7.) Run the gacutil to install the MySql Assembly into Mono’s Global Assembly Cache

sudo gacutil -i MySql.Data.dll

You should see:

Installed MySql.Data.dll into the gac (/usr/local/lib/mono/gac)

 

Setting up a MySql user for Asp.net to use for web apps

Asp.net needs to have access to the MySql database for it’s web apps.  To allow this, we are going to setup a new user on the LAMA server.

1.) Use  Putty to connect to your LAMA server.

2.)Go into MySql.  You will need to know the root password if you set one during install.

sudo mysql -u root -p

It will ask for password, hit enter if you didnt use one

3.) From the Mysql command line, add a user for Asp.net

CREATE USER ‘aspnet’ IDENTIFIED by ‘aspnetpassword’;

This creates an aspnet user with a password of aspnetpassword
*This is for testing / demonstration purposes only.  For securing your mysql database please see MySql’s website for more information.

4.) Now we need to grant privileges to the user

GRANT ALL ON *.* TO ‘aspnet’@’%’ IDENTIFIED BY  ‘aspnetpassword’;

This grants all privileges to the aspnet user
*This is for testing / demonstration purposes only.  For securing your mysql database please see MySql’s website for more information.

 

Create a sample database

We will setup a simple database for testing.  We will make a simple phone book.

1.) Create the database,  from the Mysql command line type:

CREATE DATABASE testsite;

2.) Change to the testsite database

USE testsite;

3.) Create the phonebook table

CREATE TABLE `testsite`.`phonebook` ( `name` VARCHAR(50) NOT NULL, `number` VARCHAR(45) NOT NULL );

This will create a table (phonebook) with 2 fields: name, number

4.) Add a test record

INSERT INTO phonebook VALUES (‘John Doe’, ‘555-123-45678’);

 

One more thing about MySql:

By default MySql does not allow connections from outside computers.  They only allow connections from 127.0.0.1 (localhost).

If you want to test your connections to MySql on your LAMA server from your development machine, you will have to make a change to your MySql configuration file.

To do this:

1.) Use  Putty to connect to your LAMA server.

2.) change directory to the Mysql configuration directory.

cd /etc/mysql

3.) Make a copy of your my.cnf file

sudo cp my.cnf old_my.cnf

4.) Open my.conf in your editor

sudo pico my.cnf

5.) find the following line and comment it out:

            bind-address = 127.0.0.1
            
            change to :
            
            #bind-address = 127.0.0.1

6.) ctrl-x to exit, Y to save the file

7.) restart MySql

sudo /etc/init.d/mysql restart

Security note: This opens up your MySql server to anyone.  Please take all necessary precautions before making your LAMA server live on the internet. You can learn more about MySql security from the MySql web site.

Next in tutorial .. Create a MySql test site

Author: Nick Jolin

My name is Nick Jolin and I’m a full time entrepreneur making a living by creating wonderful tools that help people with online marketing.

Connect with me on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *