Installing MySQL is quite an easy thing to do, especially when done using pkgs, dmgs or exes. It gets just a tad more time consuming and brain intensive when installing a .tar.gz binary package. It is when you’re compiling MySQL source directly that you’ll need some planning and playing, but the latter is only done in particular cases such as when you’ll need a particular engine not shipped with a pre-compiled package etc.

Today we’re going through the steps required for a typical MySQL installation from a .tar.gz package on a *nix based platform, including the download, installation, configuration and securing.

Steps involved:
1. Download MySQL binary tarball from mysql.com
2. Create a folder structure where the installation will be held.
3. Install the package
4. Secure the installation

Step 1: Download MySQL

Go to http://dev.mysql.com/downloads/ and choose the particular version that suits your platform (OS and machine type). Do note that installing a 32 bit version of MySQL on a 64 bit machine will not give you the full power available. There is quite a bit of performance impact when going for 64 bit, so if possible install the 64 bit version.

Step 2: Set up a folder structure to hold the MySQL related files

Normally in any database server I like to create a /mysql which will be holding any MySQL related info including, the releases, installation, configuration, data, logs etc. The below is a just personal choice and is subject to your liking. A typical folder structure would be

/mysql/ ->
     applications ->
          maatkit
          sandbox
          etc
     doc ->
          MySQL books
          RefMans
          etc

     installations ->
          mysql_cnf ->
               mysqlpreacher_master_malta_3306
               mysqlpreacher_slave_malta_3406
               etc

          mysql_data ->
               mysqlpreacher_master_malta_3306
               mysqlpreacher_slave_malta_3406

          mysql_dist ->
               ..
          mysql_inst ->
               ..
          mysql_logs ->
               ..

     releases ->
          5.0
          5.1
          6.0

     testing ->
          perl
          sandbox
          scripts
          etc

Step 3: Installing MySQL

The installation part takes just a few standard commands which follow:

#Execute the following only if you don’t already have a mysql user and mysql group. If you are not sure run: `cat /etc/group | grep -i mysql` and `cat /etc/group | grep -i mysql`. The ‘grep -i’ permits you to find just the user mysql or variant of it including those of a different case.

mysqlpreacher:~ darrencassar$ cat /etc/group | grep -i mysql
_mysql:*:74:
mysqlpreacher:~ darrencassar$ cat /etc/passwd | grep -i mysql
_mysql:*:74:74:MySQL Server:/var/empty:/usr/bin/false

#groupadd mysql
#useradd -g mysql mysql

#Let’s assume you followed the above folder structure

cd /mysql/releases/5.1
ls
gunzip mysql-5.1.xx-xxx-xx.tar.gz
tar -xf mysql-5.1.xx-xxx-xx.tar
ls

mysqlpreacher:~ darrencassar$ cd /mysql/releases/5.1/
mysqlpreacher:5.1 darrencassar$ ls
5.1.26                    mysql-5.1.26-rc-osx10.5-x86_64.dmg    mysql-5.1.28-rc-osx10.5-x86.tar.gz
5.1.28                    mysql-5.1.26-rc-osx10.5-x86_64.tar.gz    mysql-5.1.30-osx10.5-x86_64.tar.gz
mysqlpreacher:5.1 darrencassar$ gunzip mysql-5.1.30-osx10.5-x86_64.tar.gz
mysqlpreacher:5.1 darrencassar$ tar -xf mysql-5.1.30-osx10.5-x86_64.tar
mysqlpreacher:5.1 darrencassar$ ls
5.1.26                    mysql-5.1.26-rc-osx10.5-x86_64.dmg    mysql-5.1.28-rc-osx10.5-x86.tar.gz    mysql-5.1.30-osx10.5-x86_64.tar
5.1.28                    mysql-5.1.26-rc-osx10.5-x86_64.tar.gz    mysql-5.1.30-osx10.5-x86_64

mv mysql-5.1.xx-xxx-xx ../../installations/mysql_inst/
cd /mysql/installations/mysql_inst/
mv mysql-5.1.xx-xxx-xx mysql-5.1.xx-xxx-xx_001

mysqlpreacher:5.1 darrencassar$ mv mysql-5.1.30-osx10.5-x86_64 ../../installations/mysql_inst/
mysqlpreacher:5.1 darrencassar$ cd /mysql/installations/mysql_inst/
mysqlpreacher:mysql_inst darrencassar$ mv mysql-5.1.30-osx10.5-x86_64 mysql-5.1.30-osx10.5-x86_64_001

#set up your naming convention, this is a way I find useful to work with myself
ln -s mysql-5.1.xx-xxx-xx_001 mysql_51xx_001
cd mysql_51xx_001

mysqlpreacher:mysql_inst darrencassar$ ln -s mysql-5.1.30-osx10.5-x86_64_001 mysql_5130_001
mysqlpreacher:mysql_inst darrencassar$ cd mysql_5130_001

#set ownerships and permissions
sudo chown -R mysql .
sudo chgrp -R mysql .

mysqlpreacher:mysql_5130_001 darrencassar$ sudo chown -R mysql .
mysqlpreacher:mysql_5130_001 darrencassar$ sudo chgrp -R mysql .

#Install the MySQL using mysql_install_db
sudo scripts/mysql_install_db –user=mysql

mysqlpreacher:mysql_5130_001 darrencassar$ sudo scripts/mysql_install_db --user=mysql
WARNING: The host 'mysqlpreacher' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h mysqlpreacher password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

sudo chown -R root .
sudo chown -R mysql data

mysqlpreacher:mysql_5130_001 darrencassar$ sudo chown -R root .
mysqlpreacher:mysql_5130_001 darrencassar$ sudo chown -R mysql data

#provide a link from the data folder onto the mysql_data folder
cd /mysql/installations/mysql_data/
ln -s  /mysql/installations/mysql_inst/mysql_51xx_001/data/ data_51xx_001

cd /mysql/installations/mysql_inst/mysql_51xx_001/
sudo su

mysqlpreacher:mysql_5130_001 darrencassar$ cd /mysql/installations/mysql_data/
mysqlpreacher:mysql_data darrencassar$ ln -s /mysql/installations/mysql_inst/mysql_5130_001/data/ data_5130_001
mysqlpreacher:mysql_data darrencassar$ cd /mysql/installations/mysql_inst/mysql_5130_001
mysqlpreacher:mysql_5130_001 darrencassar$ mkdir /mysql/installations/mysql_cnf/mysqlpreacher_master_malta_3306/
mysqlpreacher:mysql_5130_001 darrencassar$ cp -rp support-files/my-small.cnf /mysql/installations/mysql_cnf/mysqlpreacher_master_malta_3306/my.cnf
mysqlpreacher:mysql_5130_001 darrencassar$ sudo su
Password:
sh-3.2# pwd
/mysql/installations/mysql_inst/mysql-5.1.30-osx10.5-x86_64_001

#Depending upon the size of your dataset, a generic my.cnf to use (options file where you set specific parameters which are read by MySQL before starting up) can be obtained from the support-files folder. This file normally requires quite some tweaking depending on the hardware, dataset, type of application querying the database, etc. Configuration is not a one time task either since things change with time, and so must the parameters set in this file.

#The generic samples of my.cnf are:
my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf

#Unless the location of the options file described above is hardcoded in the startup script (mysql.server), the order by which MySQL looks for a my.cnf is:

Filename                            Purpose

/etc/my.cnf                       Options
/etc/mysql/my.cnf            Global options (as of MySQL 5.1.15)
SYSCONFDIR/my.cnf          Global options
$MYSQL_HOME/my.cnf      Server-specific options
defaults-extra-file             The file specified with –defaults-extra-file=path, if any
~/.my.cnf                          User-specific options

#Once the installation is complete, just start MySQL using:
sudo bin/mysqld_safe  –defaults-file=/mysql/installations/mysql_cnf/mysqlpreacher_master_malta_3306/my.cnf –user=mysql &
#note that the –defaults-file needs to be the first parameter in this command otherwise you’ll see an error “Too many arguments (first extra is ‘–defaults-file=/mysql/installations/mysql_cnf/mysqlpreacher_master_malta_3306/my.cnf’).”

#you can check if MySQL did indeed start using the following
ps -ef | grep -i mysql

sh-3.2# sudo bin/mysqld_safe  --defaults-file=/mysql/installations/mysql_cnf/mysqlpreacher_master_malta_3306/my.cnf --user=mysql &
[1] 88880
sh-3.2# 090105 03:19:09 mysqld_safe Logging to '/mysql/installations/mysql_inst/mysql-5.1.30-osx10.5-x86_64_001/data/mysqlpreacher.err'.
090105 03:19:09 mysqld_safe Starting mysqld daemon with databases from /mysql/installations/mysql_inst/mysql-5.1.30-osx10.5-x86_64_001/data

sh-3.2# ps -ef | grep -i mysql
0 88880 88282   0   0:00.02 ttys001    0:00.04 /bin/sh bin/mysqld_safe --defaults-file=/mysql/installations/mysql_cnf/mysqlpreacher_master_malta_3306/my.cnf --user=mysql
74 88992 88880   0   0:00.04 ttys001    0:00.12 /mysql/installations/mysql_inst/mysql-5.1.30-osx10.5-x86_64_001/bin/mysqld --defaults-file=/mysql/installations/mysql_cnf/mysqlpreacher_master_malta_3306/my.cnf --basedir=/mysql/installations/mysql_inst/mysql-5.1.30-osx10.5-x86_64_001 --datadir=/mysql/installations/mysql_inst/mysql-5.1.30-osx10.5-x86_64_001/data --user=mysql --log-error=/mysql/installations/mysql_inst/mysql-5.1.30-osx10.5-x86_64_001/data/mysqlpreacher.err --pid-file=/mysql/installations/mysql_inst/mysql-5.1.30-osx10.5-x86_64_001/data/mysqlpreacher.pid --socket=/tmp/mysql.sock --port=3306
0 89007 88282   0   0:00.00 ttys001    0:00.00 grep -i mysql

Step 4: Secure the installation

When a database is placed online it does require some careful securing since it would otherwise be vulnerable to malicious acts such as alterations and denial of service.

MySQL uses Access Controlled Lists in order to discriminate between users, that means each user is given access to perform certain tasks while prohibited from doing others. It is quite flexible but managing fine grained rights allocation to a lot of users can also become quite a nightmare (consult maatkit mk_show_grants at http://www.maatkit.org/doc/mk-show-grants.html for this kind of setup). A MySQL command helpful in the latter task is:
SHOW GRANTS FOR ‘user’@’host’;

Using GRANT and REVOKE will permit you to set privileges for individual users e.g.
In order to grant select to john on database FOO while connected via localhost, the command would be:
GRANT SELECT ON FOO.* TO ‘john’@’localhost’ IDENTIFIED BY ‘password’;
FLUSH PRIVILEGES;

Note that omitting the ‘identified by’ would cause a user to have access without a password, something which should never be permitted for an online live server.

A further security measure, apart from ACL described above is to place MySQL server behind a firewall, preferably inside a DMZ (demilitarized zone) and should the MySQL server be accessible only through the socket file, then it would be a good idea to insert ‘skip-networking’ in the configuration file my.cnf thus disabling any client from accessing the instance from any machine other than the machine on which it is installed.

One last suggestion as regards the security topic, consult MySQL updates and security patches in case there is something which directly impacts your current setup so that you can upgrade immediately if you are vulnerable in any way.

#access MySQL directly can be done using
bin/mysql -u root -p
SHOW DATABASES;

#If running a live MySQL system, run `drop database test`, you can always execute `create database test` later

#Create a password for root
USE MYSQL;

UPDATE user
SET Password = PASSWORD(“password”)
WHERE User=’root’;

FLUSH PRIVILEGES;
EXIT

#checking MySQL password set earlier
bin/mysql -u root -p #don’t insert a password to test it out
bin/mysql -u root -p #…. insert the new password (“password” in our case)

#It is good practice to set up a monitoring script which queries MySQL for any user without a password or for anonymous users.

USE MYSQL;
SELECT User, Host, Password
FROM user
WHERE User=” OR Host=” OR Password=”;

#This should be remedied using:
DELETE FROM user
WHERE User=”;

sh-3.2# bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.02 sec)

mysql> USE MYSQL;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user
-> SET Password = PASSWORD("password")
-> WHERE User='root';
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> EXIT
Bye
sh-3.2# bin/mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
sh-3.2# bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.30 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE MYSQL;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT User, Host, Password
-> FROM user
-> WHERE User='' OR Host='' OR Password='';
+------+---------------+----------+
| User | Host          | Password |
+------+---------------+----------+
|      | localhost     |          |
|      | mysqlpreacher |          |
+------+---------------+----------+
2 rows in set (0.00 sec)

mysql> DELETE FROM user
-> WHERE User='';
Query OK, 2 rows affected (0.02 sec)

mysql> SELECT User, Host, Password
-> FROM user
-> WHERE User='' OR Host='' OR Password='';
Empty set (0.00 sec)

mysql> \q
Bye
sh-3.2#

This section does not exhaust all security guidelines of course, but helps making your instance more secure.

#DONE

———– o ———–

#A few general notes:

#Note that not all the MySQL commands need a ‘;’ at the end, an example is `\s` and `SHOW STATUS \G`
#Exiting from MySQL can be done using `EXIT` or `QUIT` or `\q`
#A helpful command to get you started is `?` which provides a list of commands available

#There are many other commands and combination of syntaxes which you can write in order to obtain results

#It is good practice to insert MySQL path in the env variable PATH in order to avoid typing the whole path each time you want to use MySQL. In bash you’d do:

export PATH=/path/to/mysql/bin:$PATH

o insert the MySQL’s path before all the others, otherwise just interchange the positions of $PATH and the actual /path/to/mysql/bin

#Killing the MySQL is done using the following command:
bin/mysqladmin -u root -p shutdown

#Starting and stopping MySQL automatically can be done using mysql.server, found in support-files folder. The file needs to be copied in /etc/rc.d and edited to insert the custom parameters of data directory, base directory etc.

#If the storage engine used is innodb, a good my.cnf option would be innodb_file_per_table since otherwise innodb will store all it’s table data in a single file which is not optimal when having big tables which need to be optimzed / analyzed.

#Remember to set up monitoring scripts should this be a live system in order to avoid downtime.

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

MySQL Installing: Binary tarball (.tar.gz) *nix based platforms, 10.0 out of 10 based on 1 rating

  1. datacharmer says:

    Hi,
    If you are in a hurry and want to install MySQL from a tarball in a matter of seconds, you can use MySQL-Sandbox ( http://launchpad.net/mysql-sandbox )

    It will be as simple as

    ./make_sandbox /path/to/mysql-tarball.tar.gz

    Giuseppe

  2. Hi Giuseppe,

    Thanks for your comment, your sandbox is excellent and I do love to use it while testing with different mysql distros, BUT more information will flow in another blog post :).

    Darren

*