• Login
• Become a member
• RSS
• Part of the TechTarget network

Many companies have data scattered across several databases that are often maintained by proprietary software. For example, we might have a computer inventory in a specialized application that keeps track of assets, an office phone list in a Word document, employee locations in an Excel spreadsheet, etc.

More on LAMP: Software stacks: Mixing proprietary and open source  Golden's Rules: Beyond the LAMP stack - A guide to open source Nagios, Xen & Asterisk

One advantage to having such a system is that it's flexible, extensible and we're not beholden to any one vendor should we wish to add a feature. Suppose, for example, we want to email a certain group whenever a change is made to the employee's office location field. This is relatively easy for a LAMP programmer to implement. But if we use proprietary software, such a feature may not exist or may need to be custom written at considerable cost.

Another advantage is that because MySQL is so ubiquitous, it's much easier to pull information and use it in other applications. For example, an online phone list can be compiled directly into a new HTML page every time an employee's phone extension changes. We can also pull information into customized lists, such as all the employees who report to a particular supervisor or all the employees in a particular department. Or we could pull employee office location data into a Java application that generates floor plans. Our options here are essentially limited only by our needs and our imagination.

Further, we can control security by protecting certain information (databases, or database tables, etc) directly from PHP by simply requiring a password to access such information.

The disadvantage to such a system, given that we're not all LAMP programmers, is that we must secure the services of a programmer capable of developing and maintaining such a system. Here we have to weigh the costs of deploying off-the-shelf propriety software with its limited capabilities against the costs of hiring a LAMP developer with unlimited flexibility.

We must also consider the longterm big picture. An off-the-shelf proprietary solution may adequately serve our immediate needs today, but if we expect to grow and expand, we must be wary of vendor lock-in and the costs of migrating from such a system to LAMP should we need to do so in the future.

MySQL tutorial for newbies
For those completely unfamiliar with MySQL, here's a brief tutorial on obtaining, installing and running MySQL on Linux:

The first thing to do is download a binary MySQL distribution for your platform from www.mysql.com and save the tar file in /usr/local

For security, we'll want to run our MySQL server as a non root user. So we'll need to add a mysql group and then add a new mysql user as part of that group. We can do this with the following commands:

 groupadd mysql useradd -g mysql mysql

Now let's untar the binary. Replace X.X with the MySQL version you downloaded in the following:

 cd /usr/local tar xvfz mysql-X.X.tar.gz

For convenience, let's make a soft link in /usr/local called mysql that points to the mysql directory we just untarred.

 ln -s mysql-X.X mysql

Next we need to initialize the mysql database containing the grant tables that store the server access permissions. We do this with the mysql_install_db script.

 cd mysql scripts/mysql_install_db --force --user=mysql

The --force option turns off DNS lookups. You don't need this option if the computer you're installing MySQL on has a DNS entry and can be resolved.

For security we assign ownership of everything except the data directory to the root user. The data directory should belong to the mysql user and group ownership of everything should be assigned to the mysql group. So in the /usr/local/mysql directory we do:

 chown -R root . chown -R mysql data chgrp -R mysql .

Now we run the MySQL server with the following:

 bin/mysqld_safe --user=mysql &

Assign a password for the root user with the following:

 bin/mysqladmin -u root password your-password

Replace your-password with a real password.

Now we connect to the server as user root using the password we assigned in the previous step.

 bin/mysql -u root -pyour-password

We should see the mysql> command prompt.

 mysql>

Now we're ready to begin using mysql commands. To begin with, let's create a database.

 mysql> CREATE DATABASE acme_tool_co;

MySQL commands are generally not case sensitive. For clarity, we'll type the commands in uppercase and the variables in lowercase. We've just created a database called acme_tool_co. Let's make sure it's there with the following:

 mysql> show databases;

You should see acme_tool_co listed in the output, like so:

 +--------------------+ | Database | +--------------------+ | information_schema | | acme_tool_co | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec)

The other databases are the ones that were set up when we ran the mysql_install_db script above.

Now we need to create a table within the acmetool_co database. First we select the database for use with the use command like this:

 mysql> USE acme_tool_co;

Then we create a table.

 mysql> CREATE TABLE employee_info (employee_name varchar (30), phone_extension varchar (5), location varchar (4));

Here we created a table called employee_info that contains the fields employee_name, phone_extension, and location. Note that varchar is a data type which is a variable length string, the number of characters assigned to that string is the number in parenthesis. There are many data types in MySQL including types for date and time, numeric operations and several for text. Here we'll just use varchar for simplicity.

So now we've created a database and a table containing some fields within that database. Now we're ready to add some data. Let's add two employees john and jane doe and give them phone extensions and office locations. We can do this using the insert into command like so:

 mysql> INSERT INTO employee_info values ('john doe', '54332', '223B'); mysql> INSERT INTO employee_info values ('jane doe', '54333', '223C');

We can view the data we added by using the select and from commands like this:

 mysql> SELECT * FROM employee_info;

We should see the following:

 +---------------+-----------------+----------+ | employee_name | phone_extension | location | +---------------+-----------------+----------+ | john doe | 54332 | 223B | | jane doe | 54333 | 223C | +---------------+-----------------+----------+ 2 rows in set (0.00 sec)

We can also use the select and from commands to refine the output. For example, suppose we only want employee names and extensions. In this case, we could use:

 mysql> SELECT employee_name, phone_extension FROM employee_info;

Which will generate the following containing only the employee_name and phone_extension fields.

 +---------------+-----------------+ | employee_name | phone_extension | +---------------+-----------------+ | john doe | 54332 | | jane doe | 54333 | +---------------+-----------------+ 2 rows in set (0.00 sec)

Further we can use the order by command to sort the output. To sort alphabetically by employee name, we could use:

 mysql> SELECT employee_name, location FROM employee_info ORDER BY employee_name;

Which will generate the following alpha sorted output.

 +---------------+----------+ | employee_name | location | +---------------+----------+ | jane doe | 223C | | john doe | 223B | +---------------+----------+ 2 rows in set (0.00 sec)

We can use the '=' comparison operator to generate output for a particular condition. If we wanted to get information on the employee at location 223C, we could use:

 mysql> SELECT * FROM employee_info WHERE location = '223C';

This will generate only the information we want.

 +---------------+-----------------+----------+ | employee_name | phone_extension | location | +---------------+-----------------+----------+ | jane doe | 54333 | 223C | +---------------+-----------------+----------+ 1 row in set (0.00 sec)

To delete an entry from the table we can use the delete command like this.

 mysql> DELETE FROM employee_info WHERE employee_name = 'john doe'; Query OK, 1 row affected (0.00 sec)

Here we use from the employee_info table where our condition is that employee_name ='john doe'.

We can check the user 'john doe' has been deleted using an earlier command like this.

 mysql> SELECT * FROM employee_info; +---------------+-----------------+----------+ | employee_name | phone_extension | location | +---------------+-----------------+----------+ | jane doe | 54333 | 223C | +---------------+-----------------+----------+ 1 row in set (0.00 sec)

To exit the MySQL command prompt, we use the QUIT command.

 mysql>QUIT

This will take us back to a regular shell prompt.

Stopping the server:

Finally, we can stop the MySQL server by changing to the mysql directory and using the mysql.server command in the support-files directory by using the following commands:

 cd /usr/local/mysql support-files/mysql.server stop

Barry Railton has worked as a Linux and Unix systems administrator since the early 1990s.

Join the conversationComment

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.