Installing, Configuring and Performance Tweaking MySQL for OS X Server

Installing MySQL for OS X is mostly straightforward, but taking a minute to complete a few extra steps of configuration will help keep your server more secure and can vastly improve performance.

If you’ve followed along with the basic configuration articles earlier in this series, you’ll already have installed things like Apple’s own Workgroup Manager and the command line tools from Xcode (“Basic Configuration and Preparing the Way for Extra Software OS X Server Needs”). You’ll have a basic backup strategy in place (“OS X Server First Things First: Backup and Recovery and Other Preliminaries”), and you’ll have tweaked a few security settings (“Mac OS X Packet Filter and Adaptive Firewall” and “Changing the Default SSH Port on Mac OS X Server”). You’ll probably be itching to fire up Server.app and start doing something!

But if you’ll be transferring existing sites to your server which rely on MySQL — and if you’re not big on the idea of going back and modifying plenty of code to change to PostgreSQL, which is what comes bundled with OS X Server — then you’ll probably want to get MySQL installed first.

First, let’s grab the latest 64-bit DMG version of MySQL and install using the main MySQL.pkg and the MySQL pref pane and the MySQL startup item:

• mysql5.5.xxx.pkg
• MySQLPrefPane
• MySQLstartupitem.pkg

In a bit of counter-intuitive and screwy user interface design, if you do not install the startup item, then the checkbox to “Automatically Start MySQL Server on Startup” will still appear, but it will have no effect: even though you can check the box, MySQL Server will not start automatically after a restart.

Now you can use Terminal to hook up the plumbing for the just-installed MySQL. Instructions for the first part — adding to the $PATH environment variable — are all over the place, usually involving vi. I don’t personally like or use vi unless I have to and much prefer something like SublimeText 2 for all these sorts of tasks, but sometimes for just a line or two, it will do:

Use the ‘i’ key to enter vi’s insert mode, and add the line:

export PATH=$PATH:/usr/local/mysql/bin

Use the Esc key to exit insert mode and type :wq followed by enter to write the file and quit vi.

Now we’ll grant the root user all privileges and set up a root password for MySQL. One quick way is by first entering the MySQL command line as follows:

And from there, entering the following, replacing ‘yournewpassword’ with a suitable password:

GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'yournewpassword' WITH GRANT OPTION;
quit

However, this alone is inadequate to secure root access to MySQL. Since MySQL distinguishes between ‘root’ accounts at localhost, at 127.0.0.1, at ‘::1’ used in IPv6, and at any other host — including any static host name which may exist for your IP address in your provider’s network — you should assign the root password to all variants of the ‘root’ account with the following at the MySQL command line, on two separate lines:

UPDATE mysql.user SET Password = PASSWORD('yournewpassword') WHERE User = 'root';
FLUSH PRIVILEGES;

(If you’ve already typed ‘quit’ from the previous line, just enter mysql -u root -p to interact directly with MySQL again.)

You can also remove access to the ‘test’ database and other database names that start with ‘test_’ by removing relevant rows from the mysql.db table:

DELETE FROM mysql.db WHERE Db LIKE 'test%';
FLUSH PRIVILEGES;

And the ‘test’ database can be dropped altogether with:

DROP DATABASE test;

More details are available in the manual entry on default privileges.

Now the plumbing necessary for Apache and PHP to see MySQL:

And finally, to get ourselves a functional my.cnf for customising the installation (which we will want to do), copy the included ‘huge’ example configuration file to one that MySQL will actually use:

If you don’t complete this step, MySQL will operate with default settings, which for many reasons are not at all suitable for a live server environment. You may have several tweaks you’d like to make to MySQL, and requirements vary wildly, but having said that I would recommend at least the following modifications to the [mysqld] section of my.cnf for smooth sailing:

character-set-server = utf8
default-storage-engine = MyISAM
bind-address = 127.0.0.1

In addition, although a value of ‘2’ is the default anyway when running on Mac OS X, you can avoid warnings in your error log by setting:

lower_case_table_names=2

And finally, very very importantly, unless you specifically intend to use multiple machines in a master/slave configuration to handle your databases, you should disable binary logging by commenting it out with a ‘#’ character, like so:

#log-bin=mysql-bin

Why? This type of logging creates a massive performance hit when tables are updated. Sure, it’s possible just to get MySQL up and running and never configure a thing, and your server will probably run OK, but this one single setting makes a vast difference between a zippy machine living up to its potential as a great server, and a slow machine weighed down with lead boots.

You may also wish to move MySQL logging out of the default (which is MySQL’s data folder) and into /var/log/ by first creating a suitable directory and assigning it to the _mysql user but ‘staff’ group (so you’ll be able to access the logs via Console):

You can then add the following to the [mysqld] section of my.cnf:

slow-query-log
long_query_time = 3
log-error=/var/log/mysql/error.log
log-slow-queries=/var/log/mysql/slow-queries.log

Note that because MySQL will be started with mysqld_safe, initial startup messages will be written to the error log; don’t worry, this is entirely expected.

(If you later find that MySQL is still using the old default log location, once you’re up and running, just enter the SQL command FLUSH LOGS via phpMyAdmin or via the MySQL command line, and this will kick start it into using the new log files.)

If you’re feeling adventurous, and your server has a decent amount of RAM, you may also wish to bump up a few basic settings that can make a significant difference to performance. Note that I offer these purely on an experimental basis. I have no idea what your specific requirements might be, and the best numbers for you might be very significantly higher or possibly even lower:

query_cache_size = 64M
query_cache_limit = 2M
max_heap_table_size = 64M
tmp_table_size = 64M

In addition, you may wish to increase the default InnoDB buffer pool from 128 MB to something a little beefier, such as the 384 MB suggested in the ‘huge’ example configuration:

innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M

Note, however, that InnoDB is picky. If you make the changes above, you must stop MySQL completely, rename ib_logfile0 and ib_logfile1 from the MySQL data directory at /usr/local/mysql/data, and then start MySQL again. (Don’t remove them entirely right away; simply renaming them will allow you to restore them just in case anything goes wrong.) You can stop, start or restart the service from the command line like so:

If you do not complete the step of removing the log files and allowing InnoDB to rebuild them, the InnoDB engine will shut down with an error. You can watch the MySQL error.log in Console to observe what happens when you stop and then start MySQL, to verify that InnoDB has properly taken care of itself, and you should see a different buffer pool size now listed in the log with something along the lines of:

Assuming InnoDB is happy after starting MySQL again, you can then remove the files you renamed previously.

Finally, for connecting with your databases remotely, it’s worth trying the native Mac OS X title Sequel Pro, although a few tasks are still easier with phpMyAdmin running on the server itself. (I’ll come back to installing phpMyAdmin a little later, since we have yet to fire up Server.app!) For example, it’s quite a bit easier with phpMyAdmin to check on the current value of all MySQL options and click a provided link to read the MySQL manual to understand what they do and the conditions under which your server might benefit from changing them.

• macOS
• MySQL
• security