Introduction to using MySQL at home 

It can be a daunting task getting started with MySQL. Its organization and the way you use it are somewhat different from other database programs that many people are more familiar with, such as Microsoft Access.

This article provides an overview and some tips that will hopefully help make MySQL less of a mystery and get you oriented to how it works and how you use it.

MySQL is complex, and there is so much documentation for it that it's easy to get overwhelmed.

Some keys to learning MySQL:

1. Start with a simple concrete task that you want to accomplish, and use your study and experimentation to work toward completion of that one task. After completing each task, set a slightly more ambitious one, and work toward that.
2. Stay focused on the one task you're working on. You'll encounter documentation about features you're not ready for. Remember that they exist, but don't get dragged into trying to learn all of MySQL at once.
3. There are many advanced features that you will never need. The documentation looks especially intimidating because most of it is about those features.

What is MySQL?

Many people are familiar with MySQL as the database program most often used by web applications such as WordPress to store data for use on a website, but it can also be used as a general purpose database management program in the home or office.

Some of its advantages are that it is free, fast, efficient, well documented, and can easily handle very large databases. You interact with it using the (somewhat) standardized SQL command language, so when you learn MySQL methods, you're learning things that will be useful working with any database program that understands SQL, including Microsoft Access.

Some of its disadvantages are that there can be a steep learning curve, especially if you're not already familiar with the SQL command language, and MySQL doesn't have the built-in capabilities for graphical charts, forms, and fancy reports that other database applications provide.

MySQL is a database server. What this means can be illustrated by comparing it with some other programs.

MySQL compared to Apache HTTP Web Server

If you have a website, and even if you don't, you might be somewhat familiar with Apache HTTP Server. It is a program that runs as a service (aka daemon) on a computer. It has one, or sometimes many hundreds, of websites stored in its internal data folders. Its job is to receive incoming requests from the outside world and send the requested pages to the people or programs that asked for them. When you requested with your browser this page you are reading now, you requested it from the Apache web server that handles this website.

MySQL is very much like Apache Web Server, except that its purpose is to serve database data rather than web documents. It runs as a service on a computer. It has one, or sometimes many hundreds, of databases and their tables stored in its internal data folders. Its job is to receive incoming requests from the outside world, extract the requested data from its database tables, and send the data to the people or programs that asked for it.

Apache and MySQL are server programs that send information out to people (or programs) who request it, but if you want information, how do you send your request? You do it by using a separate "client" program. The client knows how to a) establish a communication channel with the server (which might be, and often is, on a separate and distant computer), b) properly format and send your request to the server, c) receive the data back from the server, d) organize and format it into a presentable form for you to view it.

That sounds very fancy, but confusion can easily be cleared up with just one example: What is the most popular type of client program in the world? A web browser! Now reread the previous paragraph, and you'll see that's exactly what it does. While Apache is a web (document) server, a browser is a web (document) client.

The same model holds true for MySQL. Since the purpose of MySQL Server is to send data out, you need a separate program that you use to connect to the server and get the data from it. Just as you can use a variety of different web browsers to connect to Apache Web Server, you can use a variety of different database client programs to connect to the MySQL server. Some of the client programs will be discussed later. 

The key to the client/server model is that the two sides are handled by two separate and different programs, and I think this is the first and most important point of confusion that people might experience when trying to understand MySQL after working with another database program like Microsoft Access.

MySQL compared to Microsoft Access

With Access, you "open" an .mdb file, just like you open a .doc document with Word or an .xls document with Excel.

Most of the things you do in Access are client-side functions, but behind the scenes Access is also acting as its own server side, executing the queries and sending the results back to the GUI interface to display to you.

A difference in "feel" between Access and MySQL is that in Access you feel that you are working with a document that is your database. In MySQL, there are continual reminders (and it can help your mindset to be continually aware) that you are working on the client-side of a connection to a remote data server (even if that server is on your same computer). 

Access provides elaborate graphical interfaces for writing program code, designing databases, tables, queries, forms, and reports, and viewing result sets in tabular form or via forms and reports. All these are client-side tasks, since they relate to either design (which in MySQL you do manually and send the database or table creation code to the server via SQL commands) or to the display of returned data (which in MySQL is the responsibility of your MySQL-connected client program).

A summary of differences:

Ways to connect to MySQL (client programs)

Each connection method requires a user/password

When you install MySQL, the automatically created "root" user has full privileges on all databases and can do "anything". You can use this root user to access any of your databases, but it's normal to create at least one additional user whose privileges are more limited, for everyday use. Spending most of your time logged in as a limited user helps prevent doing accidental damage with typing errors or by applying a command to a database different from the one you thought you were working with. You can create as many users as you want, even a different user for each database, and they can have all different, or the same, passwords. 

Whenever you connect to MySQL from any of the clients described below, you'll be prompted to log in with a user and password. Log in with a user that has privileges to access the database you want to work with.

MySQL Command Line Client (text mode)

The most basic way to connect, possibly the easiest to understand, and the best way to get started, is with the text mode Command Line Client that you launch in Windows from a Command Prompt or in Linux from a Terminal, like this:

mysql -uYOURUSER -p

Enter your password when prompted. Then you'll get a mysql> command prompt where you enter SQL commands for it to execute. The results are presented in text mode.

Section 3 of the extensive MySQL Reference Manual is a tutorial for working in command mode. I'd suggest that working through this tutorial be the very first thing you do with MySQL because it will help understand what's going on "under the hood", even when you use more automated methods or a GUI interface later. The GUI interfaces are automated code generators that translate your actions in the GUI to underlying SQL commands, and it's beneficial to have an understanding of the code that is being generated for you. Besides helping to understand any problems that arise, it can happen with some experience that you find working with raw SQL code to be easier, more intuitive, more flexible, and more powerful than being dependent on the GUI to do it for you. In addition, if you plan to use MySQL from a programming language like PHP, you will need to understand SQL because that's what you must use to communicate with the MySQL server.

Besides letting you enter SQL commands one at a time, the Command Line Client has a "batch mode" that allows you to run a set of commands stored in a text file. To use it, write the command(s) in a plain text file with an extension of .sql, such as test.sql. Then, in the Client, type this line to execute all the commands as if you typed them yourself:

source test.sql;

You can make an .sql file for any task you expect to do more than once, or for a single command that you're experimenting with, to avoid a lot of typing.

.sql files can even be used to automate creating and populating an entire database, which happens to be the basis of the MySQL backup method. Backing up the database writes to an .sql file that, when run, recreates the structure of the database and its tables from scratch and then inserts the data into the tables. The next article in this series shows example .sql files that create and populate a database.

As a side note, if you have a website and use cPanel's MySQL Backup feature, what it creates for you to download is a compressed .sql.gz file that when decompressed with gunzip or 7-Zip is a text .sql file. Besides being a backup, you can use it to create a copy of the remote database on your local PC by running the .sql file in your local MySQL.

In the Command Line Client, if you want to simultaneously echo your commands and the resulting output to a text file for later viewing, type:

tee filename;

Then execute whatever commands you want... Then turn echo off with:

notee;

You usually terminate MySQL commands with a semicolon. If you terminate a command with \G instead of a semicolon, the result set is formatted so each column of each row is on its own line, which helps readability for queries where the rows are wider than the terminal screen width.  

There are a few other commands that you can type while already in the Command Line Client (client-side), which control its behavior. You can get a list by typing:

help;

That also gives you instructions how to obtain quick help with SQL commands (server-side) (by typing help contents;).

There are many options you can specify on the command line when launching the MySQL Command Line Client. Most of these are advanced and not required during normal use, but you can get a list with:

mysql --help

The Command Line Client is especially useful for running your automated .sql scripts and for queries that don't return thousands of result rows or where the rows aren't wider than the screen width. I've also found the Command Line Client combined with tee/notee to be a surprisingly usable way to browse result sets.

MySQL Workbench or Query Browser (GUI, graphical interface)

The most recent version of the old MySQL Query Browser is a module of MySQL Workbench. The user interfaces of the old and new versions are considerably different, but they both can help build your SQL commands, and they show table data or query results in a horizontally and vertically scrollable table. You can add or edit and save table records interactively, and can do the same in queries that are "updatable" (editable).

The old name of the program, "Query Browser", was an interesting play on words. Although it does allow you to "browse your queries", probably the most common interpretation, it can also be considered a browser, a client program like a web browser, but customized for entering queries (rather than web addresses) and for graphical table display of the data returned.

The Workbench Query Browser is especially good for result sets that contain thousands of rows (easy vertical scrolling) and for results sets with columns wider than the screen (easy horizontal scrolling).

phpMyAdmin (GUI, web browser interface)

phpMyAdmin is probably best known for being the MySQL client that is included in the Control Panel (such as cPanel) for customers at many shared web hosting companies. If you want to view the data in your installation of WordPress, SMF, phpBB, etc., phpMyAdmin might be your only easy-to-use option other than connecting directly to your website's MySQL server from your local PC, which your hosting account might not be configured to allow.

I haven't used phpMyAdmin much, and don't have a lot to say about it, but mention it here for the opportunity to point out that it is basically a MySQL client like the others described here except that it's written in PHP, you access it using your web browser, and it connects to your database using PHP methods, as described below.

In order to use phpMyAdmin on your PC, it is necessary to also install a web server such as Apache, and the PHP language interpreter.

When you install phpMyAdmin on your local PC, the "control user" that you must create, contrary to what its name implies, is actually a very low-privileged MySQL user whose only purpose is for phpMyAdmin to do its own administrative tasks. You normally do not give the control user privileges to access databases other than the one or two it requires for its own use.

To work with your databases from phpMyAdmin, log in as one of your other MySQL users that has privileges for the database you'll be accessing.

Microsoft Access

With your data stored in MySQL, you can use Access to connect to it, getting the best of both worlds. Use linked tables in Access and the MySQL ODBC Connector. It looks and feels like you're using an Access database, but it's MySQL doing the backend work. This can also be done across a local network or across the internet.

The primary key of a database table is a field (column) that is guaranteed to always uniquely identify each record in the table. Often, the primary key is simply an auto-increment integer in a field called something like "id".

Access, when you are working with "native" (normal non-linked) tables, will usually allow you to create a table that has no primary key. However, when creating a link to a MySQL table, the table must have a primary key. Otherwise, rows can be omitted from data listings, shown as "#Deleted" due to duplications. Before linking to a MySQL table from Access, be sure to give it a primary key field if it doesn't already have one.

Here is an example SQL snippet that uses the ALTER TABLE command to create a new primary key as the first field in a table. Note that the single quotes are actually backticks, which is the MySQL delimiter for table and column names. They prevent ambiguity when a name is the same as a MySQL keyword. Otherwise, they are optional:

ALTER TABLE `table1` ADD COLUMN `id` INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);

PHP, Perl, C++, other languages

MySQL can receive and respond to requests not just from people, but from other programs. Most programming languages today have methods for communicating with MySQL. PHP is one of the most commonly used because it is so often used in websites, where the PHP language is almost always available.

The connection method (as far as MySQL Server is concerned) is the same as when you do it manually yourself. PHP logs into the MySQL server with a user and password, and from that point on can send queries and receive the results.

Using a custom program for your MySQL connection is especially useful on a website or for any other application where the entire connection and query process must be automated, or for situations where you need to manipulate the returned data or format it in highly customized ways such as with custom graphics. PHP has a graphics library called GD.

When writing your own PHP code for MySQL connections, the methods of the PHP extension/module called mysqli are easier to use and more secure than the older extension called "mysql" even though most online articles are about the mysql module because it's been around for so long. If your code is for use in a website, mysqli makes it easier to protect your code against SQL Injection attack, a method by which malware is inserted into a database by a hacker entering SQL code where they were supposed to enter a search query.

Making MySQL backups

The command line utility program for backing up MySQL databases and tables to .sql files is called mysqldump. It is described in the MySQL documentation. I won't try to supplement it here except to show an example command that I used in Ubuntu Linux to back up all databases and tables and send the result through the gzip program to compress it:

mysqldump -uroot -p --verbose --all-databases --hex-blob --skip-extended-insert | gzip > my-databases-backup.sql.gz

I'm not sure I chose the exact best combination of options, and I should have done each database individually, because the file containing all of them turned out to be nearly 800MB, but I just wanted to quickly backup everything before doing something risky.

However, the example demonstrates something important: it might seem like a real pain to carefully study a huge long list of options in the documentation and gradually build the text-mode command to perform a particular task, but once it's done, it's done. You can save the command for later. From then on, using it is very simple, much simpler than trying to remember which bunch of dialog-box options and checkboxes you used previously when working in a GUI. I favor and promote using text mode programs over GUIs when possible, and this is one of the reasons. Using a GUI for what is basically a text mode task is inefficient and more confusing rather than less (except at first!). 

MySQL Documentation

As mentioned earlier, you can get help summaries at various locations with --help or help; or help contents; depending on where you are, but the most complete help is in the Reference Manual. I've already provided links to the online version, but there are versions you can install and use locally.

Linux

In Linux (I'm using Ubuntu), very good HTML manuals (a set of web pages) are installed along with the MySQL and PHP programs or available as separate documentation packages. The files are installed into /usr/share/doc. Go to that directory, find the MySQL or PHP subdirectory you need, look there for the file index.html, and launch it in a browser.

With Apache installed in Linux, there is an easier method. I go to the manuals with my browser using the URL http://localhost/doc/, and navigate to the needed documentation from there. 

Windows (or Linux)

The Reference Manual is available for download in several different formats, including HTML, PDF, and CHM. I'd suggest the HTML because you can have multiple pages open at once in your browser. The PDF is 2,240 pages long, and scrolling around in it is difficult.

Where to get MySQL

• MySQL Server for Windows. Rather than the "Essentials - Recommended" packages, I'd suggest getting the big version of the MSI installer that is appropriate to your system, containing the documentation and everything you might need or want. Easier now than later.
• MySQL Server for Linux. The above link is also for Linux versions, but you should be able to get MySQL as a package in every major Linux distro, which would be the more recommended way to install it. I'd also recommend installing the MySQL Client, the administrative tools (if they are separate packages) and any related MySQL packages that you might find useful.
• The above link is also for Mac OS X and several other operating systems. Depending on how you normally install software, I'd suggest installing the MySQL programs through the official channels of your operating system if you can, just because those versions are more likely to have any needed customization for your system. 

The next article discusses how to migrate database tables and data from Microsoft Access to MySQL. 

Questions and comments are welcome in the discussion forum.