Installing MySQL on Linux

Before you proceed, you should read the build instructions in the MySQL documentation. A PDF of the latest documentation can be downloaded from http://dev.mysql.com/. The "MySQL Reference Manual" contains everything that you need.

If you haven't already done so, create a userid and group for MySQL. The standard group id and userid for CentOS/RedHat is 27 so use them if they are available. Under Ubuntu, the standard userid is 102 and the standard group ID is 105. Note that you want to create a system account (i.e. one who's ID number is below 500 for CentOS/RedHat, or below 1000 for Ubuntu, and that has no home directory). This example shows how to do it on CentOS/RedHat:

     su
     /usr/sbin/groupadd -g 27 mysql
     /usr/sbin/useradd -c "MySQL Server" -d /var/mysql -g mysql -s /sbin/nologin
                       -M -n -r -u 27 mysql

Download the latest MySQL tar file from http://dev.mysql.com/.

Note that some of the later builds (e.g. MySQL Community Server 5.5.15 GA) don't build or install cleanly, especially if you wish to use the ODBC connector. As of this writing, MySQL Community Server 5.5.28 GA does appear to work but you may want to pass over some of the intermediate versions and go with a known working version. The latest known working version, that we are aware of, before 5.5.28, was mysql-5.1.58.

Make sure you get the source files and not one of the myriad of pre-built distributions (you will experience a lot less problems if you build it yourself). Untar the tar file in the top level source directory (e.g. /rpm/MySQL):

     tar -xvzf mysql-m.n.xx.tar.gz

The tar command will create a new directory for the m.n.xx version of MySQL. Switch to that directory and build MySQL. Earlier versions of MySQL required some compiler flags to build properly, like this:

     cd mysql-m.n.xx
     CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions \
       -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler \
       --enable-thread-safe-client --with-mysqld-ldflags=-all-static \
       --enable-local-infile
     make

The mysql-5.1.58 version doesn't appear to need these flags so you can build it like this:

     cd mysql-5.1.58
     ./configure --prefix=/usr/local/mysql --enable-assembler \
       --enable-thread-safe-client --with-mysqld-ldflags=-all-static \
       --enable-local-infile
     make

If you wish to build one of the mysql-5.5.x or later versions, Oracle has switched the build to use cmake instead of GNU configure, thereby making the build portable across many different systems. You will first need to make sure that cmake is installed on your system. Under RedHat/CentOS, you can install cmake like this:

     su
     yum install cmake

Under Ubuntu, you can install cmake like this:

     su
     apt-get install cmake

Once you have cmake installed, you can build MySQL by first creating a build directory (of your choice) and then running cmake:

     cd mysql-5.5.28
     mkdir bldprod
     cd bldprod
     cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
       -DMYSQL_DATADIR=/var/mysql -DSYSCONFDIR=/etc \
       -DENABLED_LOCAL_INFILE=ON
     make

Note that for mysql-5.1.x or mysql-5.5.x, InnoDB may no longer built into MySQL. If you want to be sure that it is, you should include the "--with-plugins=innobase" parameter on the ./configure line for mysql-5.1.x or "-DWITH_INNOBASE_STORAGE_ENGINE=1" on the cmake line for mysql-5.5.x or later. There are lots of other plugins that can be built in a similar manner so consult the docs, if you need one.

Incidentally, when using cmake, if you ever need to find out what options were used to build the makefile, you can look in your build directory at a file named CMakeCache.txt.

After make is done, switch to super-duper user and install MySQL:

     su
     make install

Copy the default config file to /etc, where MySQL can find it:

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

Configure the client and server as you require. Here are some options to consider:

     [client]
       port = 3306            # This should be the default but you never know
       socket = /tmp/mysql.sock
     [mysqld]
       port = 3306            # This should be the default but you never know
       datadir = /var/mysql   # Data directory
       socket = /tmp/mysql.sock
       skip-networking        # If the server is just going to be used locally

If you want InnoDB, you should uncomment all of the lines pertaining to it and pay attention to the lines that point to the data directory:

     [mysqld]
       innodb_data_home_dir = /var/mysql/
       innodb_log_group_home_dir = /var/mysql/
       innodb_log_arch_dir = /var/mysql/
            .
            .
            .
       innodb_lock_wait_timeout = ...

Also, some bright guy got the idea to turn off local infile as a security enhancement in later versions of MySQL. If you want this capability (trust me, you do), make sure it is turned on for all clients (the "loose" prefix actually turns it on only for those clients that understand the option) and the server by editing the appropriate sections of the configuration file:

     [client]
       loose-local-infile = 1
            .
            .
            .
     [mysqld]
       local-infile = 1

Note that, apparently, calmer heads thought better of the "no local infile" thing and have partially reversed the decision to turn it off. Later versions of MySQL (e.g. 5.5.28) are pre-built with it turned on and only if you are building it from source do you need to supply the build option. You still have to pass "local-infile=1" to mysqld (as is shown in the config file, above) but you no longer need to tell the clients about it. So, for later versions of MySQL "loose-local-infile=1" becomes an error and it should be left out of the config file. Nothing like having your stuff together.

Install the predefined tables and then set the file permissions on them (see next paragraph first). For earlier versions of MySQL, mysql_install_db is found in /usr/local/mysql/bin. For later versions of MySQL, mysql_install_db is found in /usr/local/mysql/scripts. Run it from the appropriate place:

     su
     /usr/local/mysql/scripts/mysql_install_db \
          --basedir=/usr/local/mysql --user=mysql
     chown -R mysql:mysql /var/mysql
     chmod g=rwx /var/mysql
     chmod g=rw /var/mysql/
     chgrp root /var/mysql/mysql /var/mysql/test
     chmod go= /var/mysql/mysql /var/mysql/perform /var/mysql/test

Probably, the permissions will already be set this way so you could just check before messing with them. They should look like this:

     drwxrwx--- 4 mysql mysql 4096 Mar 13 23:20 /var/mysql
     /var/mysql
       drwx------ 2 mysql root    4096 Mar 13 23:20 mysql
       -rw-rw---- 1 mysql mysql  15147 Mar 13 23:20 mysql-bin.000001
       -rw-rw---- 1 mysql mysql 510697 Mar 13 23:20 mysql-bin.000002
       -rw-rw---- 1 mysql mysql     38 Mar 13 23:20 mysql-bin.index
       drwx------ 2 mysql mysql   4096 Mar 13 23:20 performance_schema
       drwx------ 2 mysql root    4096 Mar 13 23:20 test
     /var/mysql/mysql
       -rw-rw---- 1 mysql mysql   8820 Mar 13 23:20 columns_priv.frm
       -rw-rw---- 1 mysql mysql      0 Mar 13 23:20 columns_priv.MYD
            .
            .
            .

To complete the next steps, MySQL must be running. Start it as follows:

     su
     /usr/local/mysql/bin/mysqld_safe --user=mysql &

You can test to see if it is up and running by:

     /usr/local/mysql/bin/mysqladmin version
     /usr/local/mysql/bin/mysqladmin variables

You should see information about the server version and then typical query output, from the second command.

If you don't want the test database, you can get rid of it while the server is up and running:

     /usr/local/mysql/bin/mysqladmin -u root drop test

You should assign a password to the root userid. Also, if you don't want the anonymous userids, you should get rid of them (its a good idea, especially if you got rid of the test database):

     /usr/local/mysql/bin/mysql -u root
       set password for 'root'@'localhost' = password('newpwd');
       set password for 'root'@'host_name' = password('newpwd');
       delete from mysql.user where User = '';
       flush privileges;

Since we installed MySQL in /usr/local/mysql, it is probable that most users won't have this directory in their PATH environment variable so they won't be able to execute the MySQL programs by name only. We like to add a couple of symlinks to the most commonly-used MySQL programs to a standard location so that they can be found by everyone, without having to update their PATH environment variable. Here is what we add:

     ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
     ln -s /usr/local/mysql/bin/mysqldump /usr/local/bin/mysqldump

Sometimes the host name used in the user table is qualified by the local domain (e.g. myhost.localdomain). Also, you may need to set the password for the root user, qualified by '127.0.0.1' and/or '::1'. You can see all of the rows in this table with the following select:

     select * from mysql.user;

If you need to add another root user for the machine (e.g. when you change the machine's name), the following should do the trick:

     grant all privileges on *.* to 'root'@'newhost_name'
               identified by 'newpwd' with grant option;

Note that you should replace "host_name" with the actual name of your server and "newpwd" with the new password that you wish to use.

If you want to add new users with access to everything, here's a quick synopsis of what to do:

     grant all privileges on *.* to 'monty'@'localhost'
               identified by 'some_pass' with grant option;
     grant all privileges on *.* to 'monty'@'host_name'
               identified by 'some_pass' with grant option;

If you want a user with access to everything, who can login from anywhere (dangerous, wot?), try this:

     grant all privileges on *.* to 'monty'@'%'
               identified by 'some_pass' with grant option;

If you want to add a user with privileges on just one database, try something like:

     grant all privileges on OneDatabase. to 'monty'@'localhost'
               identified by 'some_pass';
     grant all privileges on OneDatabase. to 'monty'@'host_name'
               identified by 'some_pass';

If monty gets out of hand and you'd like to revoke his privileges, this should do the trick:

     revoke all privileges, grant option from 'monty'@'localhost',
                'monty'@'host_name';

If you'd like to expunge all traces of monty from your, otherwise pristine, database, first revoke his privs (as shown above) and then do:

     delete from mysql.user where User='monty';

OK, finished playing? Shut down the server:

     /usr/local/mysql/bin/mysqladmin -u root -pnewpwd shutdown

Note that the install doesn't appear to tell the loader where it puts the dynamic link libraries that are necessary to access MySQL databases. If you are developing any applications that will access a MySQL database, you may have to fix this situation manually. One fix is to add symlinks for the MySQL modules, in an obvious location such as /usr/lib. Another fix is to change ld.so.conf or ld.so.conf.d to point the loader at the directory where the MySQL modules can be found.

If you prefer the first approach, this command can take care of things:

     find /usr/local/mysql/lib -name \*.so -exec ln -s \{\} /usr/lib \;

Our preference is to add a file to /etc/ld.so.conf.d that points the loader at the MySQL dynamic link libraries. The file should look like this:

/etc/ld.so.conf.d/mysql-i386.conf:

     /usr/local/mysql/lib

Note that this file may already exist as a result of a previous MySQL installation not being properly removed. This can occur if your OS install thinks it knows better about what should be on your system and installs the MySQL RPM, for example, after which you remove the RPM before building MySQL from source. In that case, the file may already point to /usr/lib/mysql. We remove that libary's name and replace it with /usr/local/mysql/lib. If you are paranoid, you can just order the two libraries so that the modules from /usr/local/mysql/lib will take precedence but any missing modules will be found in /usr/lib/mysql. You config fill will look like:

/etc/ld.so.conf.d/mysql-i386.conf:

     /usr/local/mysql/lib
     /usr/lib/mysql

Run the loader configuration command to inform the loader about the new dynamic link library:

     su
     /sbin/ldconfig