Howto Install MySQL from Source

MySQL is a good example of an application that can really benefit from being built from source instead of installing a pre packaged binary file.  But like anything being compiled locally, it can impact and degrade performance if done incorrectly.

If you are building MySQL server for production use, obviously you are definitely going to want to do your homework on your hardware and make sure its done right the first time.

MySQL’s website is packed full of what I would feel safe calling “great documentation”.  Their white papers are actually really well written and provide a good amount of test cases and examples to help you get the most performance out of your MySQL database server.  Even when it comes to compiling it yourself!

Those of you just looking for a development tool or to use it in a sandbox environment you are still better off installing the pre packaged MySQL database server binaries via aptitude or what ever your distrobution’s chosen package manager is.

This MySQL database install guide is a fairly general configuration utilizing some performance enhancements, if you’ve done your homework this guide will make the process a piece of cake.  Just modify your compile time options accordingly and you’ll be done in no time!

Let’s begin.

1. Install a couple dependencies needed to compile on a server

$ sudo aptitude install build-essential libncurses5-dev

2. Create a mysql group

$ sudo groupadd mysql

3. Create the mysql system user

$ sudo useradd -g mysql mysql

4. Download the latest MySQL Community Server, feel free to pick a different mirror.

$ wget http://www.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.45.tar.gz/from/http://mysql.he.net/

5. Extract the Source Code and navigate into the extracted directory.

$ tar zxvf mysql-5.1.45.tar.gz && cd mysql-*/

6. Before we run the configure script I’d like to mention something about setting your make options.

Manually setting your CFLAGS to match your cpu’s architecture can help with your make compile time’s and help configure MySQL to perform better. It “can” also degrade server performance if done improperly. This is all really dependant on your hardware specifications and what you set them too.

I won’t be going into detail about this, but it is definitely worth looking into. I’ve included some links talking more about this. But for now the only optimization I am going to recommend that will help specifically with the compile time is the “-j” flag when running make.

• Single Core Processor (Athlon XP, Celeron etc)
  
  • Example – $ make -j2
• Dual Core Processor (Athlon X2, Core2Duo etc)
  
  • Example – $ make -j3
• Triple Core Processor (AMD Phenom X3)
  
  • Example – $ make -j4
• Quad Core Processor (AMD Opteron, Intel Xeon etc)
  
  • Example – $ make -j5

A general rule of thumb would be (CPU Cores) + 1 = (-jx) This will tell the make command to run in parallel doubling your compiling speed, cutting that time nearly in half!

Sometimes this does cause compilers to crash. If yours does crash, either drop the -j option down and increment and try again. Still crashes? Omit the -j option all together.

CFLAGS Optimizations
MySQL Reference Manual 5.1

7. There, now that we’ve got that out of the way, let’s proceed to running the configure script. This is a fairly generic configure script to provide us with a fairly default MySQL server. Feel free to modify it as you see fit.

./configure \
--prefix=/usr/local/mysql \
--with-mysqld-user=mysql \
--without-debug \
--with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static \
--disable-shared \
--localstatedir=/usr/local/mysql/data \
--with-extra-charsets=none \
--enable-assembler \
--with-unix-socket-path=/tmp/mysql.socket

8. That will install to /usr/local/mysql . Once configure completes, it is time to run “make” and “make install”. I’ll be using -j3 because I have a Core2Duo.

$ make -j3 && sudo make -j3 install

9. Time to go grab yourself a drink, a smoke or what ever pleases you. This will take a bit. Once it does complete, we need to copy over the default configuration file. You “will” want to modify this to your liking after we finish this tutorial.

$ sudo cp support-files/my-medium.cnf /etc/my.cnf

10. Now we’ll create our default database for MySQL.

$ sudo /usr/local/mysql/bin/mysql_install_db --user=mysql

11. Set the proper ownership with the two lines below doing them one by one.

$ sudo chown -R root  /usr/local/mysql
$ sudo chown -R mysql /usr/local/mysql/data
$ sudo chgrp -R mysql /usr/local/mysql

12. Lets fire up the server for the first time so we can change the root password.

$ sudo /usr/local/mysql/bin/mysqld_safe -user=mysql &

13. Check to see it’s running, Should see at least these two output lines. One for root, which issued the server start. And another for the mysql user for the server that is currently running.

$ ps aux | grep mysql
   root      8945  0.0  0.1   4004   616 pts/1    S    00:55   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe -user=mysql
   mysql     9046  0.0  1.2  46320  6336 pts/1    Sl   00:55   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql -user=mysql --log-error=/usr/local/mysql/data/ubuntu64jeos.err --pid-file=/usr/local/mysql/data/ubuntu64jeos.pid --socket=/tmp/mysql.socket --port=3306

14. Lets change our mysql root password now that the server is up and running. If it worked correctly you should NOT receive any output.

$ /usr/local/mysql/bin/mysqladmin -u root password 'new_password'

15. Lets login!

$ /usr/local/mysql/bin/mysql
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 4
   Server version: 5.1.45-log Source distribution
 
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
   mysql>_

16. Type in exit to exit (don’t forget the semicolon at the end!)

mysql> exit;
Bye

17. Now that the server works, let’s configure it to automatically start at bootup.

$ sudo cp support-files/mysql.server /etc/init.d/mysql

18. Modify the permissions

$ sudo chmod +x /etc/init.d/mysql

19. Add it to the default runlevel

$ sudo update-rc.d mysql defaults
    Adding system startup for /etc/init.d/mysql ...
    /etc/rc0.d/K20mysql -> ../init.d/mysql
    /etc/rc1.d/K20mysql -> ../init.d/mysql
    /etc/rc6.d/K20mysql -> ../init.d/mysql
    /etc/rc2.d/S20mysql -> ../init.d/mysql
    /etc/rc3.d/S20mysql -> ../init.d/mysql
    /etc/rc4.d/S20mysql -> ../init.d/mysql
    /etc/rc5.d/S20mysql -> ../init.d/mysql

20. Give it a reboot.

$ sudo reboot

21. Once the machine is back up check to see everything started up like it should have.

$ ps aux | grep mysql
   root      1146  0.0  0.1   4004   616 pts/1    S    00:55   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe -user=mysql
   mysql     1248  0.0  1.2  46320  6336 pts/1    Sl   00:55   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql -user=mysql --log-error=/usr/local/mysql/data/ubuntu.err --pid-file=/usr/local/mysql/data/ubuntu.pid --socket=/tmp/mysql.socket --port=3306

And that’s a wrap!

Tags: 10.04 9.04 9.10 compile database howto mysql source code ubuntu