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 as below, or enter http://melksh.am/nnnn for individual articlesS161 - Data Access and Security in MySQL 
Current MySQL and PHP paths and upgrades - (2005-01-28) 
The wrong MySQL - (2005-01-29) 
What is an SQL injection attack? - (2005-08-02) 
MySQL permissions and privileges - (2005-12-20) 
Checking for MySQL errors - (2006-03-15) 
What is an SQL injection attack? - (2006-11-27) 
MySQL - Password security (authentication protocol) - (2007-04-02) 
Images in a database? How big is a database? (MySQL) - (2009-05-28) 
Mysqldump fails as a cron job - a work around - (2009-06-30) 
Removing duplicates from a MySQL table - (2010-02-22) 
Checking MySQL database backups have worked (not failed) - (2015-01-10) 
Fixing damaged MySQL tables - Error 1712 and Error 2013 - (2015-01-25) 
Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01) 
Web Server Admin - some of those things that happen, and solutions - (2015-05-10) 
Forgotten / lost MySQL root password - (2015-05-16)S157 - More MySQL commands 
MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20) 
MySQL - Optimising Selects - (2004-12-21) 
Getting a list of unique values from a MySQL column - (2005-04-14) 
Matching in MySQL - (2005-09-24) 
MySQL - a score of things to remember - (2005-11-12) 
SELECT in MySQL - choosing the rows you want - (2005-11-22) 
MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01) 
MySQL - an FAQ - (2005-12-03) 
An occasional chance, and reducing data to manageable levels - (2005-12-04) 
Combining similar rows from a MySQL database select - (2006-01-17) 
Giving the researcher power over database analysis - (2006-01-22) 
Saving a MySQL query results to your local disc for Excel - (2006-01-29) 
Key facts - SQL and MySQL - (2006-02-04) 
Helicopter views and tartans - (2006-04-06) 
MySQL - the order of clauses and the order of actions - (2007-06-01) 
Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17) 
MySQL joins revisited - (2007-09-03) 
Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15) 
Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03) 
Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23) 
MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31) 
Grouping rows for a summary report - MySQL and PHP - (2009-06-27) 
MySQL - efficiency and other topics - (2009-10-10) 
Relating tables with joins in MySQL - (2010-02-21) 
Counting rows in joined MySQL tables - (2010-02-22) 
Optimising and caching your MySQL enquiries - (2010-02-22) 
Databases - why data is split into separate tables, and how to join them - (2010-11-20)S154 - MySQL - Designing an SQL Database System 
MySQL - Pivot tables - (2004-09-22) 
Binary Large Objects or bars - (2005-06-27) 
Oops - I got my initial database design wrong - (2005-07-12) 
Database design - get it right from first principles - (2006-04-02) 
Databases needn't be frightening, hard or expensive - (2006-11-08) 
Display an image from a MySQL database in a web page via PHP - (2006-11-22) 
Code quality counts - (2006-11-26) 
MySQL - table design and initial testing example - (2007-11-06) 
Database design for a shopping application (MySQL) - (2008-03-15) 
More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24) 
What a difference a MySQL Index made - (2009-02-25) 
MySQL - licensing issues, even with using the name - (2009-03-16) 
Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02) 
Blowing our own trumpet - MySQL resources - (2011-07-18) 
Databases - when to treat the rules as guidelines - (2011-10-23) 
FileMaker Day to Unix Time conversion - (2015-02-15)
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