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.
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:
-
Accepting the installation's suggested prefix.
-
The installation suggested prefix plus the month number.
-
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
