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!
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.
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.
Reasons to stay on 5.5:
Reasons to move to 5.6
Brass Tax: If you don’t have a good reason to use 5.5, make sure to use 5.6.
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.
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.
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-22.214.171.124.msi
Then navigate over to that directory:
C:\> cd "C:\Program Files (x86)\MySQL\MySQL Installer"
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.
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.
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
You’ll need to kill the mysqld process or restart your system.
This is the hard way. I’d recommend using Homebrew. This will cover installation of 5.6.
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
$ launchctl load -w -F homebrew.mxcl.mysql.plist
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.
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?
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
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:
Close the security holes that come with standard in MySQL:
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
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.
© Copyright 2011-2018 Noel Herrick. All rights reserved.