MySQL Simplified


This is a pre-release version of my book, MySQL Simplified. To sign-up to hear when it's ready to ship, enter your e-mail below!

Installing MySQL

Choosing a Version

Right now, there are three supported, production-ready versions of MySQL: 5.1, 5.5, and 5.6. The choice between the three really comes down to two. MySQL 5.1 was first released in fourth quarter 2008, so its life will soon be over. That means no more bugfixes except for major bugs and security issues. I can safely assume you don’t want to migrate to a supported version in a few months when 5.1 is no longer actively developed, so I’m going to skip to whether you should use 5.5 or 5.6.

5.6 vs 5.5

MySQL 5.6 went General Availability in the first quarter of 2013, and brought along some new features such as a Memcached API and better replication. Seasoned database administrators generally wait until the next bugfix round before they upgrade, but by third quarter 2013, there have already been four more releases, so I think we are past that threshold. Many other customers who were greedy for new features or new resume bullet points have already tested it for you.

Improvements in 5.6

  1. There’s now an additional memcached API that bypasses the SQL layer, allowing for a fast, NoSQL interface while maintaining ACIDity
  2. Replication has improved:
    1. Crash proof slaves that automatically resume in the event of a server restart
    2. Checksums on replicated data to prevent corruption
    3. Time delayed replication (so you can have a safety window should something go terribly wrong on the master)
    4. General performance enhancements
    5. The ability to back-up the binary logs realtime
  3. Greatly enhanced instrumentation and logging

Reasons to stay on 5.5:

  1. Your software only works on 5.5
  2. There are no features that you will use in 5.6
  3. The software packaging system you use only has 5.5
  4. You like the symmetry of the version number (Disclaimer: not a real reason)

Reasons to move to 5.6

  1. Memcached API
  2. Replication is a big part of your system, and you need the reliability and monitoring improvements
  3. You need access to better performance monitoring
  4. You have a system that needs online schema migrations You want the lastest bugfixes and performance enhancements

Brass Tax: If you don’t have a good reason to use 5.5, make sure to use 5.6.

Choosing an OS

If you don’t already have a standard OS, I would recommend using a Linux distribution such as Debian, CentOS, or Ubuntu since there’s a large community for those OSes and they are free as in speech and beer. If you don’t have a favorite Linux distro, I would recommend starting with Ubuntu, as it is easy on beginners and has a large group of committers and users. There’s an added perk that many open-source teams provide packages for the Long Term Support (LTS) versions.

There’s also the fact that Linux systems can run and upgrade the system software, including the kernel, without restarting. Zero-downtime is standard for most updates in Linux. Normally, kernel upgrades require a restart, but there’s a service provided by Oracle called Ksplice that allows Linux to update on the fly (though this is only available to subscribers and only on Oracle’s Enterprise Linux, which is a clone of Red Hat Enterprise Linux).

Windows is a really hard sell, since you must purchase a license for the OS and MySQL is built for Unix. The only reason to choose Windows is if you and your team do not support Linux at all.

A Note on Package Managers

Whether you use apt, yum, or Homebrew, a package system is a great tool for anyone installing and upgrade software. A package is like having an experienced system administrator install the software for you.

If you are not familiar with these tools, package management systems consist of two parts: online package repositories and a tool to resolve dependencies and install the software using these repositories. The repos are available over the Internet and contain packages versioned both for the application and for the OS. When a user requests a package to install, the tool searches the repositories, finds the software, reads the package for what it depends on, downloads and installs those other packages, and finally the package itself. Each package installation also provides basic configuration, and for MySQL, adds the service to the init system.

The package maintainers conform to the practices of that distribution, allowing system administrators to know exactly where to find data files, configuration, and binaries. They also provide a second layer of tests, including tests that see how various programs interact. Finally, they provide absolute ease and automation that’s hard to match, especially for headless servers that you administer over SSH. If you use an automatic build tool like Chef or Puppet, adding a package dependency "mysql-server-5.5" is priceless.

You can configure the package management system to apply bugfix updates automatically, but this will always mean your server restarts. For production systems, I would choose manual upgrades so that you can correct the issue if something goes wrong. It’s also a rule that you should also always develop and test on the same version of the server as you are deploying to, and automatic upgrades make this easier to break.

Installing on Windows

Graphical Installation

Go to dev.mysql.com/downloads/mysql. It should read that you’re on a Windows box, but you can just select it from the Select Platform dropdown. Now, click the Download button next to the Windows (x86, 64-bit), MySQL Installer MSI. Click the first one (mysql-installer-web-community-5.6.X.0.msi) and follow the instructions to install MySQL Server 5.6.X. This will download and install the software for you.

Howto on Unattended Installation

Windows also offers you unattended installation. With MySQL 5.5 and the MSI (Microsoft Installer), it was a one-step process:

C:\> msiexec /q /i mysql-5.5.30-winx64.msi

This is a little different with 5.6. You now need to install an Installer:

C:\> msiexec /q /i mysql-installer-community-5.6.14.0.msi

Then navigate over to that directory:

C:\> cd "C:\Program Files (x86)\MySQL\MySQL Installer"

And install:

C:\...\> MySQLInstallerConsole.exe --config=mysql-server-5.6-winx64:passwd=<random password> --product=* --catalog=mysql-5.6-winx64 --action=install --type=full --nowait

This method allows you to specify different parameters, so it may be worth it if you want that level of customization.

Installing on Mac OS X

Many developers are choosing to develop on Macs, but I don’t recommend using MySQL on Macs in production simply because of the cost. Also, Apple has stopped selling hardware that is specifically created for server systems like those that a production instance of MySQL requires. That being said, installing MySQL on a Mac as a developer is simple and easy.

Uninstalling MySQL on Mac OS X

This will nuke any previous installation. Backup your data, obviously, and be very, very careful before running any of these commands that you type the whole line.

$ sudo -s
$ rm /usr/local/mysql
$ rm -rf /usr/local/mysql*
$ rm -rf /Library/StartupItems/MySQLCOM
$ rm -rf /Library/PreferencePanes/My*
$ rm -rf /Library/Receipts/mysql*
$ rm -rf /Library/Receipts/MySQL*
$ rm -rf /var/db/receipts/com.mysql.*
$ vim /etc/hostconfig

Remove the line with MYSQLCOM=-YES

$ exit

You’ll need to kill the mysqld process or restart your system.

Installation Package from MySQL.com

This is the hard way. I’d recommend using Homebrew. This will cover installation of 5.6.

  1. Visit the MySQL development site (http://dev.mysql.com/downloads/mysql/)
  2. Select the right one for your OS X, make sure it’s 64-bit, and click the Download button
  3. For instance, Mac OS X ver. 10.7 (x86, 64-bit), DMG Archive
  4. Click “No thanks, just start my download.” on the next page
  5. When the download finished, open up the dmg
  6. Then click “mysql-5.6.14-osx10.7-x86.pkg” (or whatever the version of MySQL you chose)
  7. Follow the instructions in the installer
  8. Now, install the next package MySQLStartupItem.pkg
  9. Finally, add the newly added mysql/bin directory to your path
  10. Open your .bashprofile document (vim ~/.bashprofile)
  11. Add the mysql/bin directory to your path by appending to the $PATH variable: export PATH=/usr/local/mysql/bin:$PATH
  12. Restart your system, and MySQL should be installed and mysql (the command line program) should be available in Terminal

Homebrew

You can install MySQL 5.6 by simply typing:

$ brew install mysql

This should download the source, compile it, and install it for you. It will also magically be available by your path. You can now add this to launchctl:

$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

If you run into troubles with startup, you may want to remove the Homebrew installation and try this route instead. Once again, don’t do this unless you don’t have any data in MySQL or you’ve ensured that it’s backed up.

$ brew remove mysql
$ brew cleanup
$ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
$ rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
$ sudo rm -rf /usr/local/var/mysql
$ brew install mysql
$ mysql.server start
$ mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)"
$ mysql_secure_installation
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

You may get an error message like this:

nothing found to load

Try this:

$ launchctl load -w -F homebrew.mxcl.mysql.plist

Installing on Linux

There are literally hundreds of distributions and many, many different package managers. I’m going to cover the shining stars of two major Linux distro families: Ubuntu (12.04) which represents Debian-based systems with deb and apt as their format and package manager, respectively, and CentOS (6.4) as the example of a Red Hat Enterprise Linux clone with its rpm-based yum repositories.

Installing on Ubuntu 12.04

The most recent version of Ubuntu comes with MySQL 5.5 in the main repository. This is a version behind since MySQL 5.6 wasn’t stable in time to be released as part of 12.04, but you may want to install it anyway.

$ sudo apt-get install mysql-server-5.5

Wow, wasn’t that simple?

Install Percona 5.5 on 12.04

If you want to get the latest and greatest, use the Percona fork (which is 100% compatible with Oracle builds), with a fully supported apt repository (including security fixes).

$ apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Add to the /etc/apt/sources.list:

deb http://repo.percona.com/apt precise main deb-src http://repo.percona.com/apt precise main

$ sudo apt-get update

You can get more information here: http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html

If you’d like to install the old Percona version (5.5), you can just specify that version:

$ sudo apt-get install percona-server-server-5.5

Installing on CentOS

Just like Ubuntu, you can just install MySQL 5.X with a simple yum command:

$ yum install mysql-server

To get the goodies that come along with Percona, you need to add the Percona yum repo (as root):

$ rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Now we can install the server itself:

$ yum install Percona-Server-server-56

Let’s start-up the server:

/etc/init.d/mysql start

Close the security holes that come with standard in MySQL:

$ mysql_secure_installation

If you want to allow access to MySQL’s port, you’ll need to open it up in iptables. Add the following line:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

to the file /etc/sysconfig/iptables. It should look something like this when you’re done:

# Firewall configuration written by system-config-firewall # Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT

Finally, restart iptables.

$ /etc/init.d/iptables restart

Other Options

With almost every OS, there’s an option to install MySQL by source or by prepackaged binaries, availably at dev.mysql.com. You are welcome to use these, but I don’t believe they are the best option for a production system. You want to reduce the amount of time you spend on pretty mundane tasks like upgrading and installing. This is why I’d recommend using Ubuntu 12.04 with Percona. Everything’s up-to-date and automatic.

This is a pre-release version of my book, MySQL Simplified. To sign-up to hear when it's ready to ship, enter your e-mail below!

 No spam



RSS Feed

My other sites

© Copyright 2011-2018 Noel Herrick. All rights reserved.