Installing and Configuring MySQL for MailSteward Pro

Installing and Configuring MySQL for MailSteward Pro

I recently upgraded from MailSteward to MailSteward Pro. The primary difference is that MailSteward uses SQLite and the Pro version uses MySQL. You’re left to your own devices to get MySQL installed and configured, although the documentation doesn’t suck.

Still, there are easier and better ways. 1

I’m running Mavericks, and a quick a web search led me to Mac Mini Vault’s script. There’s also a version for Mountain Lion. The instructions are pretty straightforward and, for the most part, worked as advertised.

Note: If you have previously installed MySQL and have not completely obliterated it from your system, this script will fail. I have no idea what’s involved in completely removing MySQL. Oracle doesn’t, to my knowledge, provide an uninstall script. Awesome.

I had one issue with the bash <(curl -Ls http://git.io/eUx7rg) command spitting out weird errors. I got around that thusly:

# cd ~/Downloads
# wget http://git.io/eUx7rg
# bash eUx7rg

The script will prompt for your OS X admin/root password. Once MySQL is installed, it’ll ask if you want to use the performance configuration. I did, so I entered Y. It’ll then ask if you want to install Sequel Pro, and there’s really not a good reason not to. Once done, you’ll have three things:

  1. Sequel Pro in your Applications folder;
  2. A text file called MYSQL_PASSWORD on your Desktop; and
  3. A working MySQL installation.

I only use MailSteward from one computer, so I don’t need MySQL to be available on my network. I only want to use MySQL from the same computer I run MailSteward Pro from. So the first thing to do is modify the /etc/my.cnf file. You’ll want to change

[mysqld]

to

[mysqld]
bind-address=127.0.0.1

This will make MySQL only answer to programs that run on the same computer. 2 While you’re editing /etc/my.cnf, change

max_allowed_packet = 1M

to

max_allowed_packet = 200M

This last change is only necessary if you’re storing somewhat large attachments in MailSteward. I never know, so I figure better safe than sorry.

Now we need to restart MySQL. From the command line:

# /usr/local/mysql/bin/mysqladmin -u root -p shutdown
# sudo /usr/local/mysql/bin/mysqld_safe &

After hitting enter on the first command, the system will ask for your MySQL password. You can find this in ~/Desktop/MYSQL_PASSWORD.

If you follow the MailSteward Pro instructions, you’ll end up with a database called myemaildb, but more importantly, you’ll be doing everything as the MySQL root user. This is not good practice as the root user account, if breached, can do a lot of damage to your MySQL databases. So, we’re going to create a new user account that only access our MailSteward Pro database, and we’re going to give our MailSteward database a more sensible name.

From the command line:

# mysql mysql -u root -p

The system will ask for the MySQL password, which you can find in your ~/Desktop/MYSQL_PASSWORD file. The following commands should be entered exactly, but you’ll want to replace new password with a decent password that you won’t forget. Keep the passwords the same. The lines beginning with Query OK are system responses indicating that you did good. You only need to enter the information following the mysql> prompt. The semicolons are important.

mysql> CREATE USER 'mailsteward'@'127.0.0.1' IDENTIFIED BY 'new password';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'mailsteward'@'localhost' IDENTIFIED BY 'new password';
Query OK, 0 rows affected (0.00 sec)

mysql> create DATABASE mailsteward;
Query OK, 1 row affected (0.00 sec)

mysql> grant ALL on mailsteward.* TO 'mailsteward'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> grant ALL on mailsteward.* TO 'mailsteward'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

At this point, we’ve created a new user called mailsteward which can only login from the local computer, a new database called mailsteward, and we’ve granted the mailsteward user total access to this database.

Now, finally, it’s time to fire up MailSteward Pro. You’ll configure your connection settings as follows:

MailSteward Pro Connection Screen

The password is the password you used in the CREATE USER statements above. Clicking the Save button will save this information so you don’t have to remember it in the future. Magic!

  1. This involves a lot of command line diddling, which involves opening the Terminal program located in /Applications/Utilities.

  2. If you want to access MySQL across your network, don’t do this. You’ll also need to change the CREATE USER statements. It gets messy.