From Wiki99

↑ Computers ↑
← prev: Recovery Using a Backup next: Managing MySQL →

---

Both WordPress, the blog server we wish to install, and MediaWiki, the wiki server we wish to install, utilize MySQL as a database to store their data. So we need to install MySQL and perform some minimal configuration.

The database files that actually store data are treated by their controlling software (the database manager, ie MySQL) in a way unlike most of the files you have used before; in particular it is common for parts of the database file to be in memory at any given time, while other parts are on disk. Two consequence of this are that database files can become corrupted, and that they require somewhat different techniques for backup from most files.
For this reason, this basic article on installing MySQL is followed by an article on the basics of dealing with corruption and backup.

Contents 1 What is a Database Manager? 2 How specifically do I deal with MySQL? 2.1 The MySQL programs and files 2.2 MySQL users and permissions 2.3 MySQL communication with other applications 3 Installation 3.1 Download and Compile 3.2 Start the mysql demon 3.3 Give the mysql root account a password 3.4 Remove unnecessary user accounts 3.5 The PHP password problem 4 What's Next?

What is a Database Manager?

With PCs everywhere, many previously esoteric aspects of computing, like the details of operating systems or networking are now common knowledge. Databases, however, remain something of an exception to that. Many of us have had very little experience with database managers.

At the very simplest level, a database manager is a program which, surprise, manages, databases. A database is anything into which one can store information tagged with certain identifiers, or structured in a certain way. A database could be a text file, for example a file of names and addresses, and some people do use text files as databases. The database manager, reponsible for low-level details like where to store the data on disk and how to find it, is, in this case, a combination of the file system and some simple routines that utilize the file system.

However there are problems with this obvious text file approach as one tries to be more sophisticated.

• Security:
  Large databases frequently want to tag certain material with particular permissions so that some users cannot see all the data, some users cannot modify some data, and so on. The file system has a model for permissions that does not match this requirement, since the file system applies a single set of permissions to an entire file.

• Transactions:
  Large databases need a level of reliability (called transactions) that has not been common in file systems. The essential point of transactions is to be able tell the database manager to perform a number of modifications to a database subject to the constraint that if anything goes wrong (IO error for example) all the modifications that were requested are undone, not just the most recent modification (eg the one that triggered the IO error).

• Performance:
  Large databases are generally used in a different way from file systems. Files tend to be largish, and the file system does not impose much structure on the relationship between files, apart from the directory hierachy. Databases tend to be comprised of small amounts of data that are related to each other in various ways. Thus it makes sense to use a different way of storing these items of data on disk than is used by the file system.

A consequence of what I've said is that database managers feel a little like a replacement operating system, only different just for the sake of being different. Until you have a lot of experience, they can be frustrating to deal with because it seems like you are forced to replicate, inside the database manager, all the knowledge you had to acquire to deal with the operating system, only everything is different in subtle ways.
I have nothing useful to say about this frustration accept to remind you that databases are the way they are, not copies of the file system, for good reasons, and the only way to get past the frustration is to acquire some experience with them.

How specifically do I deal with MySQL?

MySQL, the program, is the database manager. We will be installing version 5, the most recent version at the time of this writing.

MySQL operates as a server, that is it runs on some computer in the background, and you communicate with it by asking it to perform tasks just like you when talking to any other server over a network. For this reasons, when dealing with sophisticated database managers like MySQL, one often calls them database servers.

As a practical matter, we will run MySQL on the same computer that is running all our other servers like Apache and Postfix, but in theory you do have the option of running MySQL on a different computer from that running your blog or your wiki, and if your Apache server were overloaded, the first, simplest thing you might consider to reduce the load would be to move MySQL to a separate computer.

The MySQL programs and files

As is common for large programs like MySQL in the UNIX world, there is not a single program installed on your computer that "is" MySQL. Instead what you will find in /opt/local/bin (once we have performed the installation) is a number of programs with names like mysql_config5, mysql_find_rows5, mysqld_safe5, and mysql5. Many of these are only relevant to people administering high-end databases, or developing programs that communicate with databases, and we can ignore them. Mostly we will care only about the programs:

• mysqld_safe5, which starts the database server (which then runs in the background until shut down)

• mysql5, which provides a command-line interface for communicating with the database server

• mysqladmin to shut down the server

As for the actual databases, these (for the most part) live as directories in /opt/local/var/db/mysql5/. For example WordPress creates a database called wordpress. Everything related to this database lives inside the directory /opt/local/var/db/mysql5/wordpress. You can (if you are superuser) look inside this directory, but there's really nothing there that you should modify in any way; if you need to change anything about any database you should do so through the MySQL command line.

This simple model of a database corresponding to a directory breaks down for more sophisticated databases created using the so-called innoDB model. Recent versions of MediaWiki create such databases. These databases are stored in some fashion across files with names like /opt/local/var/db/mysql5/ib_logfile0 and /opt/local/var/db/mysql5/ibdata1.

This variety in how databases are stored in the file system underscores the fundamental point that, like it or not, the MySQL replaces the file system as far as your interaction with your data is concerned. This is the case not just obviously, as when you want to read and write data, but even in cases where you want to interact with the entire database as in backing it up.

MySQL users and permissions

The MySQL programs runs under UNIX as the userID mysql. Everything they create is owned by this user and much of what is created has permissions such that so that no other user can read it (for the obvious reason that you may not want random people to be able to read, let alone write the databases through file system accesses, eg opening the database files and looking for particular text strings).

When you install MySQL, and when you need to interact with it in some way that is relevant to the operating system, (for example if you need to restart it after having shut it down) then, as you would expect, you will need to be superuser.

However, for the reasons mentioned earlier about wanting to protect parts of databases with finer granularity permissions than the OS allows, MySQL maintains its own permissions system and its own set of userIDs that are only relevant to MySQL, and that have nothing to do with the operating system permissions and userIDs.

Each MySQL user has a userID and a password and has to login to the database manager before they can read or write from a database, or do much of anything else. MySQL comes with a default userID, the ID root which, as you would expect, is a superuser who can do anything within the world of MySQL. Just to repeat the point and make it quite clear, the MySQL root userID has nothing to do with the UNIX root userID, apart from the fact that they have the same name. If you login to MySQL as (MySQL) root, you can do anything in the world of MySQL, eg create or destroy databases, and read or write data in them, but you cannot do anything special in the UNIX world like delete files in other users directories. And the same goes the other way; currently being logged in to UNIX as root does not make you root in MySQL. Finally (and this should be obvious by now) root in MySQL has its own password (managed by MySQL) which you can (and probably should) set to be different from the password you use to become root under UNIX.

Programs you install that use MySQL, like WordPress or MediaWiki, will each create their own MySQL userID, with their own password, and a database to be used by that userID.
Some installers are sophisticated enough to do all this by themselves, others will provide you with the command line instructions you need to type into MySQL to create the userID and the database.

MySQL communication with other applications

Finally there is the question of how other programs talk to MySQL.

As I said, MySQL runs as a server, so you might imagine that what you would need to talk to a MySQL server is an IP address (usually localhost) and a TCP port number. This is one way of running MySQL, but it is not the most common way.
More common is to communicate with MySQL using what are called UNIX-domain sockets. These are a way of transferring data between programs that is limited to a single machine (ie does not involve networking) and that are, for that reason, both faster than networking and more secure.
To identify the socket with which a program needs to communicate, instead of providing an IP address and TCP port number, you provide a string (which identifies a pseudo-file in the file system that was created by the server program and that is hooked up to send data to the server program).

What this means in practice is that the DarwinPorts installation of MySQL, when it runs, creates the socket /opt/local/var/run/mysql5/mysqld.sock, and when a program like MediaWiki or WordPress needs to be told where to find its database server, it gets given an address like localhost:/opt/local/var/run/mysql5/mysqld.sock.

It is common for people using programs like MediaWiki and DarwinPorts not to give the socket name explicity, and simply to give the database server address as localhost; with the assumption that the client software will convert this into a full socket name. The problem with this is that it assumes that MySQL will always place its socket in the same place in the file system (for example /tmp/mysqld.sock), and that the client software will likewise always assume this same default location for the socket. Both of these are really bad assumptions.
Using an explicit address makes it a little more clear what is going on, and if, after you've installed a program that is supposed to connect to MySQL and it complains that it cannot form a database connection, the most likely problem is that you have not provided a full path for the database socket.

In the best of all possible worlds, this convention of simply using localhost as a specification for the database server would be treated as an error since it is so prone to causing problems and misunderstanding, but sadly we do not live in the best of all possible worlds. You might, however, complain to the authors of programs like MediaWiki and WordPress and encourage them to adopt a more sensible attitude.

Installation

Download and Compile

With all this in mind, we're ready for installation. As always when using DarwinPorts, it makes sense to update the DarwinPorts infrastructure before doing anything else. (It is also wise, if you are able, to wait a month or so after substantial updates to MacOS X or the MacOS X development tools before dealing with DarwinPorts, to allow the ports infrastructure to be updated as necessary to support whatever has changed.)

So to update the ports infrastructure we type

and wait. When that's done, lets install MySQL

The +server variant has nothing to do with MacOS X Server. It means that we want to run MySQL as a server on this computer, so the installer will do some helpful extra things for us, the most important of which is to create a launchd file that will automatically start MySQL whenever this computer is rebooted.

MySQL is a large program. Depending on how fast your computer is, this install may take half an hour or more as the source code is downloaded and compiled.

When everything is done, you now run

which will create some initial directories and files that MySQL needs.

Start the mysql demon

At this point, we can start the MySQL server and perform initial configuration. We start the MySQL server with

Note:

• the mysql demon needs to start up as root (but once started it converts its userID to mysql)

• if you are not familiar with the & above, such an & at the end of a command line means to run the command line in the background, so the mysql demon will start, output a line of text, and then return control of the terminal to you.
  If you forget the & above, mysql will hold onto control of the terminal window and you will not be able to type any more. Killing the terminal window is not helpful since that will also kill the program currently running "in" that window, ie mysql.
  The way you deal with this is to: * type ^Z, ie control-Z, which will freeze whatever application is currently running in the foreground of a terminal window, and then
  * type bg (bg stands for background) which will tell the shell to switch the program you have just frozen to run in the background.

• Having told you about how to start mysql in the background using & above, I now need to warn you that there appears to be a bug in either mysql or MacOS X (as of 10.4.5) which makes this operation flaky.
  I have, on a few occasions had the experience that starting up mysql in this way simply doesn't work. The demon does not detach properly, and any program that attempts to connect to the database fails. So, to be safe, you might want to stick with the more clumsy control-Z method until this gets resolved.

Give the mysql root account a password

MySQL, as first installed, has four user accounts:

• a root account for local connections,
• a root account for network connections,
• an anonymous account for local connections,
• and an anonymous account for network connections.

(Anonymous accounts are usually, as you might expect, restricted to read only access, but have their uses for certain environments.) All four of these accounts have been created with no passwords.

The first thing we need to do is to make our system secure for our planned use, which means we will kill all accounts except the local root account, and we will give that local root account a password.

To give the local root a password, type in

(obviously replace 99merry with whatever password you want to use).

(From this point on we'll assume that your path is correctly set up so that you can use the mysql commands without having to type the full /opt/local/lib/mysql5/bin/ path like above.)

Test this now: Type

This means: create a command line interface to the MySQL server, connecting me as user root. This should fail, MySQL complaining that there is no password. Try again, this time typing

-p means ask for the password. At the prompt, type in your password, and you should now be "logged in" to MySQL as root.
Some people prefer to include the password in the command line, so

Note that there is no space between the -p and the password. You can also leave out the space between -u and root if you prefer. Which of these login possibilities you use is up to you, but this way of specifying user and password on the command line is common across all the various mysql programs we'll learn about.

Remove unnecessary user accounts

Now that you're logged in to MySQL as root, you should remove the three accounts that we don't need. Type

and you should see a table, fairly easy to understand, listing the four users I described above.

Note that all MySQL commands end in ; If you don't type in the ; MySQL will return a blank line, assuming you have not finished typing the command yet. You can type ; on this blank line to finish the previous command line and it will then be acted upon.

Now type

which will delete the anonymous users, and type

where localname.local is whatever your Bonjour name is, which will delete the network root user. (You should have seen it shown in the table that was printed out when you listed the four initial users.)
Let's check that it worked

and you should now see only one user.

We can now exit the MySQL command line interface by typing

The PHP password problem

Now we have to confront a somewhat irritating little problem. When MySQL first came out it used a way of encrypting passwords that, as time has passed, has come to be considered no longer state of the art in security. With MySQL4 a new method of encrypting passwords was built into the program. However, of course, at this point older programs had been built using the previous method of communicating with the server. It is only with PHP5 (which is not yet part of MacOS X) that applications built using PHP are capable of communicating with MySQL using the new encryption.

So the bottom line is that we have to tell MySQL to run in a backwards-compatability mode until all the outside infrastructure we care about (which for now means PHP, WordPress and MediaWiki) has been revised to use the new MySQL4 password encryption.

This has two aspects to it.
Firstly we have to tell the server that it should accept old-style password encryption. (By default this is not the case, and only the new-style, stronger encryption is accepted.)
We do this by creating a prefs file for MySQL with this option set.

write these two lines in the files

and save it.

Secondly, when we create the users for WordPress and MediaWiki, these users have to be given a password using old style encryption. We'll cover this when we get to these apps.

The one final thing we should now do is shutdown the MySQL server, then start it up again, so that it reads the new preferences.
We do this with

Note that, unlike starting mysql, shutting it down does not require that you be superuser. If you are familiar with how demon applications change their userID when they start up, this will make sense to you. So the sudo before the mysqladmin5 -u root -p shutdown above is strictly not necessary. I, however, find it easier to always use sudo than to think before each mysql command whether it requires sudo or not.

What's Next?

You can read vast amounts on the theory of relational databases, and the specifics of managing MySQL if you want, but what's been covered here is pretty much all you need for our simple uses.

The two big subject we need to cover, in the next article, are dealing with things going wrong (corruption) and preventing things from going wrong (backup).

You may also want to install the program CocoaMySQL which is a GUI frontend to managing MySQL and allows you to, fairly easily, get an overview of what databases you have and what's in each one.

If you want to use CocoaMySQL, when it starts up it will ask you how to connect to the database. As you'd expect, set the host to localhost, the user to root, the password to whatever you set earlier, and the socket to /opt/local/var/run/mysql5/mysqld.sock.

---

← prev: Recovery Using a Backup next: Managing MySQL →