Software, your way.
How To Get Good Custom Software
(Download)
(PDF)
burger menu icon
WillMaster

WillMaster > LibraryWebsite Development and Maintenance

FREE! Coding tips, tricks, and treasures.

Possibilities weekly ezine

Get the weekly email website developers read:

 

Your email address

name@example.com
YES! Send Possibilities every week!

Demystification of MySQL

MySQL (pronounced My-S-Q-L) is software that stores and retrieves data.

Software that uses MySQL tells MySQL what to store and which data table to store it in. And what to retrieve and which data table to retrieve it from.

MySQL hums along doing its magical stuff. Kinda like Apple computers. It just works.

What that Uses MySQL Software Requires for Installation

Because MySQL does its stuff in the background, it can seem a bit mysterious. Especially when it comes to installing software that uses MySQL and you have to provide the MySQL URL, a database name, and a username and password. Perhaps you'll need to create a database and/or a username with password before the software can be installed.

Knowing why you have to provide the information can go a long way to demystifying the whole thing. Instead of blindly following instructions hoping you don't break something.

Requiring the MySQL URL makes sense. After all, if the software is to work with MySQL, it will need to know where it is located.

Often, the MySQL URL is simply "localhost", but some hosting companies have unique URLs to differentiate which of their accounts is using the MySQL installation.

Requiring the database name also makes sense. The software has to know which database to use.

But why both a database name and a username? Why can't the database itself just have a password and dispense with the username? I'll show you the database name/username relationship in the diagram below.

A MySQL Overview

One username/password set can be assigned to many different databases.

One database can contain data tables maintained by many different software programs.

Here is an illustration.

MySQL diagram

An overview of the relationship between MySQL databases, username/password sets, and data tables.

The Username and Password

A username with password is required to access a MySQL database.

The same username can be used to access many different databases. You assign usernames to databases according to your preference. MySQL keeps track of which username/password set you assigned to which database.

In order to use a database, the software must authenticate itself by providing a username and password assigned to the database.

The Data Tables

The database contains data tables.

Each data table in the database has a unique name. The database doesn't care how many different software programs use the database, so long as each table name is unique.

Although it is possible for several software programs to use the same data tables, it is not recommended. One of the programs may change the data in ways the other does not expect. An exception would be a central data table utilized by several software programs written by the same publisher, perhaps a data table with "about the publisher" information.

Keeping Data Table Names Unique

To keep table names unique, software generally begins data table names with certain characters.

For example, Master Form .PHP use "mf_" at the beginning of each data table name. And Carefree Ad Manager uses "cam_". The preferences tables can be named "mf_preferences" and "cam_preferences", respectively, without a table name clash if they are installed on the same server and use the same database.

For additional assurance of unique data table names, because it is impossible to know ahead of time what other tables may already exist in the database, Willmaster software and probably most other good software allows you to prefix a series of characters to the table names when the software is installed.

Prefixing data table names with a custom series of characters lets you ensure each table name is unique. For example, you may prefix the letter "d" and a series of numbers, perhaps representing the year, month, and day of installation. Like "d20110906" for September 6, 2011.

In that case, the above mentioned "mf_preferences" and "cam_preferences" data table names would become "d20110906mf_preferences" and "d20110906cam_preferences".

Other systems of determining data table name prefixes include:

  1. Accepting the installation's suggested prefix.

  2. The installation suggested prefix plus the month number.

  3. The installation suggested prefix plus the installation number when the same software is being installed in several different places of the same domain.

Use a system that works for you.

When each installed software program starts its tables with a different set of characters, the table names can not clash.

Creating Databases and Username/Password Sets

When software using MySQL is installed, you'll need to provide the MySQL URL, the database name, and the username/password set that may use the database.

If the database or username/password you wish to use do not yet exist, they need to be created.

I can not tell you specifically how to create a database or a username/password set at the hosting account where your domain is at. That's because different hosting companies have different methods. Many use cPanel, and even those can be different from version to version.

Your hosting company most likely has instructions on how to set up a database and username/password with the system they have implemented for their hosting accounts.

Now that MySQL is less mysterious, you have more certainty about what you are doing.

What You Learned

You know databases and username/password sets are separate. You know a username needs to be assigned to a database before the database can be used. And you know several or many software programs can use the same database so long as data table names are unique.

It is good to know why you do certain things rather than just doing it and hoping you don't break something.

Will Bontrager

Was this article helpful to you?
(anonymous form)

Support This Website

Some of our support is from people like you who see the value of all that's offered for FREE at this website.

"Yes, let me contribute."

Amount (USD):

Tap to Choose
Contribution
Method

All information in WillMaster Library articles is presented AS-IS.

We only suggest and recommend what we believe is of value. As remuneration for the time and research involved to provide quality links, we generally use affiliate links when we can. Whenever we link to something not our own, you should assume they are affiliate links or that we benefit in some way.

How Can We Help You? balloons
How Can We Help You?
bullet Custom Programming
bullet Ready-Made Software
bullet Technical Support
bullet Possibilities Newsletter
bullet Website "How-To" Info
bullet Useful Information List

© 1998-2001 William and Mari Bontrager
© 2001-2011 Bontrager Connection, LLC
© 2011-2024 Will Bontrager Software LLC