MySQL is a system for managing data ... so it's natural for it to manage its own configuration data too in the same way. However, there are a few elements which have to be kept outside those internal tables - such as the data about which port number the daemon is to listen on, and the directory name (file path) for the databases to be stored.
There are defaults values built in to each of the MySQL processes which will be used in the absence of any other settings. But they are overridden as follows:
• The file /etc/my.cnf - a system wide file that contains settings for MySQL server and client programs for all user accounts
• Files ~username/.my.cnf - settings for all server and client programs on a "per user" basis. So there's (potentaiily) a file called .my.cnf in every user's home directory, containing instructions for all client and servers run by that user which will override any conflicing directives in /etc/my.cnf, and defaults.
• From the command line - specific MySQL settings can be made for individual processes, irrespective of defaults and overrides in /etc/mt.cnf and ~username/.my.cnf
Database engines look after data - and they also look after the structure
of how that data is held. That's known as the Metadata. So the metadata is "how" and the data is "what".
The diagram shows the separation between data and metadata. As a comparison on this week's MySQL course
, I equated the metadata to the cages and enclosures at a zoo, and the data to the animals themselves. SQL instructions too differ between those that run on the data and metadata ... to look
at data, you'll use a SELECT
, but to look at metadata, you'll use SHOW
. And so it goes on.
MySQL databases are accessed through logins - unlike web servers which are often open to all (at least at a protocol level), users of MySQL need to establish credentials. Not only is there the traditional user name and password associated with an account (and the authorisation or denial of a request), but there's an additonal layer that can be used with is the remote location from which the connection is being made.
By using the remote location on database accounts, MySQL can be locked down to respond to requests only from the subnet on which the server islocated, or indeed purely from other processes on the very host on which it is running. Very often these restrictions will be backed up by firewall settings that would (in any case) prevent information on MySQL ports getting diretly to the database server - but of course it's better to have two levels of access control in place than none at all.
Even when I'm running a course during the week, other things are going on too. Early on Thursday morning, I had to drop off some papers in our neighbouring town of Trowbridge. Looking briefly around Trowbridge (and carefully pushing the camera's shutter button when there was no traffic), I marvelled at some of the hidden beauty that there's to see in our area, even in towns which are not renowned for the tourist attraction and great beauty.
There are quite a few steps from data being extracted from database tables to it being returned to the user who made the SQL request, and each of the steps may be specified by a clause on a SELECT command.
• Tables are JOINED to link columns in one table to columns in another table - linking rows, if you like.
• Records are filtered with a WHERE clause, in which individual records that don't meet required criteria.
• If there's a need to link a whole series of rows into a single row, it's done by a GROUP clause. You may require, for example to know how many soldiers are based at a certain barracks rather than all their names!
• The HAVING clause allows for further records to be eliminated after they have been grouped - so that (following on from the previous example) we could eliminate all barracks with less than 20 soldiers based there.
• the remaining records are now SORTed into order
• and finally a LIMIT is applied if required so that only the first / top records are returned. (written 2011-04-29)
Associated topics are indexed underS154 - MySQL - Designing an SQL Database System 
Databases - when to treat the rules as guidelines - (2011-10-23) 
Blowing our own trumpet - MySQL resources - (2011-07-18) 
Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02) 
Images in a database? How big is a database? (MySQL) - (2009-05-28) 
MySQL - licensing issues, even with using the name - (2009-03-16) 
What a difference a MySQL Index made - (2009-02-25) 
More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24) 
Database design for a shopping application (MySQL) - (2008-03-15) 
MySQL - table design and initial testing example - (2007-11-06) 
Code quality counts - (2006-11-26) 
Display an image from a MySQL database in a web page via PHP - (2006-11-22) 
Databases needn't be frightening, hard or expensive - (2006-11-08) 
Database design - get it right from first principles - (2006-04-02) 
MySQL - an FAQ - (2005-12-03) 
MySQL - a score of things to remember - (2005-11-12) 
Oops - I got my initial database design wrong - (2005-07-12) 
Binary Large Objects or bars - (2005-06-27) 
MySQL - Pivot tables - (2004-09-22)S161 - Data Access and Security in MySQL 
Removing duplicates from a MySQL table - (2010-02-22) 
Mysqldump fails as a cron job - a work around - (2009-06-30) 
MySQL - Password security (authentication protocol) - (2007-04-02) 
What is an SQL injection attack? - (2006-11-27) 
Checking for MySQL errors - (2006-03-15) 
MySQL permissions and privileges - (2005-12-20) 
What is an SQL injection attack? - (2005-08-02) 
The wrong MySQL - (2005-01-29) 
Current MySQL and PHP paths and upgrades - (2005-01-28)S157 - More MySQL commands 
Databases - why data is split into separate tables, and how to join them - (2010-11-20) 
Optimising and caching your MySQL enquiries - (2010-02-22) 
Counting rows in joined MySQL tables - (2010-02-22) 
Relating tables with joins in MySQL - (2010-02-21) 
MySQL - efficiency and other topics - (2009-10-10) 
Grouping rows for a summary report - MySQL and PHP - (2009-06-27) 
MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31) 
Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23) 
Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03) 
Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15) 
MySQL joins revisited - (2007-09-03) 
Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17) 
MySQL - the order of clauses and the order of actions - (2007-06-01) 
Helicopter views and tartans - (2006-04-06) 
Key facts - SQL and MySQL - (2006-02-04) 
Saving a MySQL query results to your local disc for Excel - (2006-01-29) 
Giving the researcher power over database analysis - (2006-01-22) 
Combining similar rows from a MySQL database select - (2006-01-17) 
An occasional chance, and reducing data to manageable levels - (2005-12-04) 
MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01) 
SELECT in MySQL - choosing the rows you want - (2005-11-22) 
Matching in MySQL - (2005-09-24) 
Getting a list of unique values from a MySQL column - (2005-04-14) 
MySQL - Optimising Selects - (2004-12-21) 
MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)
Some other Articles
Small scale improvement - big scale gain. And they CAN be done with local knowledgeWanted - a look to the futureMelksham Car Parking - current charges and limitsThe importance of feedbackSQL - Data v Metadata, and the various stages of data selectionFiles or Databases? MySQL, SQLite, or Oracle?Baby PicturesReybridge, Easter MondayEaster on the Canal - near Melksham, WiltshireAlternative Vote (AV) - cutting the crap