LATEST TOPICS

Installing MySQL on Linux: Part 2

This is the 2nd article in the series “Install MySQL on Linux”. In the first article, I had demonstrated how to install MySQL on Linux using RPM packages.

Today, I would be demonstrating another method of installing MySQL on Linux platform. In today’s demonstration, we would be installing MySQL using binary package.

Install MySQL on Linux using binary package:

I would be using the following directory structure for this custom installation.

Directory Purpose
/workspace/mysql/5.6.21 MySQL server and client binaries and scripts
/workspace/mysql/data MySQL database datafile and logfiles

In addition to the custom directories, I would be using a custom TCP port and Socket file.

TCP Port 3313
Socket File /workspace/mysql/5.6.21/socket/mysql.sock

We need a user to own the MySQL server installation. Lets create an OS user and OS group for the software ownership.

[root@labserver home]$groupadd mysql
[root@labserver home]$useradd mysql -d /home/mysql -m -g mysql
[root@labserver home]$passwd mysql
Changing password for user mysql.
New UNIX password:
BAD PASSWORD: it is too short
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

Before starting with the installation, lets set some Environment variables for the mysql OS user for easy reference. For the Environment variables to be permanent, we can add them to the user profile file.

[mysql@labserver ~]$ export MYSQL_HOME=/workspace/mysql/5.6.21
[mysql@labserver ~]$ export MYSQL_DATA=/workspace/mysql/data
[mysql@labserver ~]$ export MYSQL_PORT=3313
[mysql@labserver ~]$ export MYSQL_SOCKET=/workspace/mysql/5.6.21/socket/mysql.sock
[mysql@labserver ~]$ export PATH=$PATH:$MYSQL_HOME/bin


[mysql@labserver data]$ env | grep MYSQL
MYSQL_DATA=/workspace/mysql/data
MYSQL_SOCKET=/workspace/mysql/5.6.21/socket/mysql.sock
MYSQL_HOME=/workspace/mysql/5.6.21
MYSQL_PORT=3313

Lets create the respective directories and change their ownership to mysql user.

[root@labserver ~]$mkdir -p /workspace/mysql/5.6.21
[root@labserver ~]$mkdir -p /workspace/mysql/5.6.21/socket
[root@labserver ~]$mkdir -p /workspace/mysql/data
[root@labserver ~]$chown mysql:mysql /workspace/mysql/ -R

MySQL binaries comes in compressed TAR format (files with a .tar.gz extension). We can directly UNTAR the binaries to MySQL installation directory.

Lets place all the MySQL binaries by doing an UNTAR to the MySQL installation directory (/workspace/mysql/5.6.21).

[mysql@labserver stage]$ ls -lrt mysql-5.6.21-linux-glibc2.5-i686.tar.gz
-rw-rw-r-- 1 mysql mysql 302990030 Nov  1  2014 mysql-5.6.21-linux-glibc2.5-i686.tar.gz

[mysql@labserver stage]$ tar -zxf mysql-5.6.21-linux-glibc2.5-i686.tar.gz --strip-components=1 -C /workspace/mysql/5.6.21/

Lets validate if all the binaries and supported files are copied to the MySQL installation directory.

[mysql@labserver stage]$ cd /workspace/mysql/5.6.21/
[mysql@labserver 5.6.21]$ ls -lrt
total 164
-rw-r--r--  1 mysql mysql  2496 Sep 11 19:23 README
-rw-r--r--  1 mysql mysql 17987 Sep 11 19:23 COPYING
-rw-r--r--  1 mysql mysql 87980 Sep 11 19:23 INSTALL-BINARY
drwxrwxr-x  2 mysql mysql  4096 Oct 27 07:41 socket
drwxrwxr-x  2 mysql mysql  4096 Oct 27 07:41 support-files
drwxrwxr-x 28 mysql mysql  4096 Oct 27 07:41 share
drwxrwxr-x  2 mysql mysql  4096 Oct 27 07:41 scripts
drwxrwxr-x  4 mysql mysql  4096 Oct 27 07:41 man
drwxrwxr-x  3 mysql mysql  4096 Oct 27 07:41 include
drwxrwxr-x  3 mysql mysql  4096 Oct 27 07:41 data
drwxrwxr-x  2 mysql mysql  4096 Oct 27 07:41 bin
drwxrwxr-x 10 mysql mysql  4096 Oct 27 07:41 mysql-test
drwxrwxr-x  4 mysql mysql  4096 Oct 27 07:42 sql-bench
drwxrwxr-x  3 mysql mysql  4096 Oct 27 07:42 lib
drwxrwxr-x  2 mysql mysql  4096 Oct 27 07:42 docs

Note: The binary package contains all the MySQL components. Therefore, we need not install the MySQL client separately.

At this point, we have the binaries installed. However, the mandatory core databases are not yet created unlike the RPM installation. We need to create the core databases manually using mysql_install_db script.

The script can be located under the script/ subdirectory of the installation directory.

[mysql@labserver 5.6.21]$ pwd
/workspace/mysql/5.6.21
[mysql@labserver 5.6.21]$ cd scripts/
[mysql@labserver scripts]$ ls -lrt
total 36
-rwxr-xr-x 1 mysql mysql 34544 Sep 11 20:05 mysql_install_db

However, before starting with the core database installation, we need to set few configuration settings to match our customized installation. The settings can be customized by modifying the my.cnf.
A generic template of the file is located in subdirectory support-files/ under installation directory (/workspace/mysql/5.6.21).

[mysql@labserver ~]$ cd /workspace/mysql/5.6.21
[mysql@labserver 5.6.21]$ cd support-files/
[mysql@labserver support-files]$ ls -lrt *.cnf
-rw-r--r-- 1 mysql mysql 1126 Sep 11 20:05 my-default.cnf

Copy the configuration file in /etc directory or in the installation directory (/workspace/mysql/5.6.21) and rename it to my.cnf

[mysql@labserver support-files]$ pwd
/workspace/mysql/5.6.21/support-files
[mysql@labserver support-files]$ cp my-default.cnf /workspace/mysql/5.6.21/my.cnf
[mysql@labserver support-files]$ ls -lrt /workspace/mysql/5.6.21/my.cnf
-rw-r--r-- 1 mysql mysql 1126 Oct 27 07:55 /workspace/mysql/5.6.21/my.cnf

Now edit the my.cnf file to match our customized installation.

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.

# basedir refers to the MySQL installation directory
basedir = /workspace/mysql/5.6.21

# datadir refers the directory containing database files and logs
datadir = /workspace/mysql/data

# port refers to the TCP/IP port used by MySQL for incoming connections
port = 3313

# socket refers to the socket file on the MySQL server host for sock authentication
socket = /workspace/mysql/5.6.21/socket/mysql.sock

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Once the settings are adjusted, we can create the core databases. Lets create the core databases.

[mysql@labserver 5.6.21]$ /workspace/mysql/5.6.21/scripts/mysql_install_db --basedir /workspace/mysql/5.6.21
Installing MySQL system tables...2014-10-27 08:04:07 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-10-27 08:04:07 25854 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2014-10-27 08:04:07 25854 [Note] InnoDB: The InnoDB memory heap is disabled
2014-10-27 08:04:07 25854 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2014-10-27 08:04:07 25854 [Note] InnoDB: Memory barrier is not used
2014-10-27 08:04:07 25854 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-10-27 08:04:07 25854 [Note] InnoDB: Using Linux native AIO
2014-10-27 08:04:07 25854 [Note] InnoDB: Not using CPU crc32 instructions
2014-10-27 08:04:07 25854 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-10-27 08:04:07 25854 [Note] InnoDB: Completed initialization of buffer pool
2014-10-27 08:04:07 25854 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2014-10-27 08:04:07 25854 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2014-10-27 08:04:07 25854 [Note] InnoDB: Database physically writes the file full: wait...
2014-10-27 08:04:07 25854 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2014-10-27 08:04:08 25854 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2014-10-27 08:04:09 25854 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2014-10-27 08:04:09 25854 [Warning] InnoDB: New log files created, LSN=45781
2014-10-27 08:04:09 25854 [Note] InnoDB: Doublewrite buffer not found: creating new
2014-10-27 08:04:10 25854 [Note] InnoDB: Doublewrite buffer created
2014-10-27 08:04:10 25854 [Note] InnoDB: 128 rollback segment(s) are active.
2014-10-27 08:04:10 25854 [Warning] InnoDB: Creating foreign key constraint system tables.
2014-10-27 08:04:10 25854 [Note] InnoDB: Foreign key constraint system tables created
2014-10-27 08:04:10 25854 [Note] InnoDB: Creating tablespace and datafile system tables.
2014-10-27 08:04:10 25854 [Note] InnoDB: Tablespace and datafile system tables created.
2014-10-27 08:04:10 25854 [Note] InnoDB: Waiting for purge to start
2014-10-27 08:04:10 25854 [Note] InnoDB: 5.6.21 started; log sequence number 0
2014-10-27 08:04:10 25854 [Note] Binlog end
2014-10-27 08:04:10 25854 [Note] InnoDB: FTS optimize thread exiting.
2014-10-27 08:04:10 25854 [Note] InnoDB: Starting shutdown...
2014-10-27 08:04:11 25854 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK

Filling help tables...2014-10-27 08:04:11 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-10-27 08:04:11 25897 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2014-10-27 08:04:11 25897 [Note] InnoDB: The InnoDB memory heap is disabled
2014-10-27 08:04:11 25897 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2014-10-27 08:04:11 25897 [Note] InnoDB: Memory barrier is not used
2014-10-27 08:04:11 25897 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-10-27 08:04:11 25897 [Note] InnoDB: Using Linux native AIO
2014-10-27 08:04:11 25897 [Note] InnoDB: Not using CPU crc32 instructions
2014-10-27 08:04:11 25897 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-10-27 08:04:12 25897 [Note] InnoDB: Completed initialization of buffer pool
2014-10-27 08:04:12 25897 [Note] InnoDB: Highest supported file format is Barracuda.
2014-10-27 08:04:12 25897 [Note] InnoDB: 128 rollback segment(s) are active.
2014-10-27 08:04:12 25897 [Note] InnoDB: Waiting for purge to start
2014-10-27 08:04:12 25897 [Note] InnoDB: 5.6.21 started; log sequence number 1625977
2014-10-27 08:04:12 25897 [Note] Binlog end
2014-10-27 08:04:12 25897 [Note] InnoDB: FTS optimize thread exiting.
2014-10-27 08:04:12 25897 [Note] InnoDB: Starting shutdown...
2014-10-27 08:04:14 25897 [Note] InnoDB: Shutdown completed; log sequence number 1625987
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:

  /workspace/mysql/5.6.21/bin/mysqladmin -u root password 'new-password'
  /workspace/mysql/5.6.21/bin/mysqladmin -u root -h labserver.home.com password 'new-password'

Alternatively you can run:

  /workspace/mysql/5.6.21/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 . ; /workspace/mysql/5.6.21/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 at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /workspace/mysql/5.6.21/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /workspace/mysql/5.6.21/my-new.cnf,
please compare it with your file and take the changes you need.

MySQL core databases are now installed with database files located under the datadir (/workspace/mysql/data).

[mysql@labserver data]$ pwd
/workspace/mysql/data
[mysql@labserver data]$ ls -lrt
total 110724
drwx------ 2 mysql mysql     4096 Oct 27 08:04 test
-rw-rw---- 1 mysql mysql 50331648 Oct 27 08:04 ib_logfile1
drwx------ 2 mysql mysql     4096 Oct 27 08:04 performance_schema
drwx------ 2 mysql mysql     4096 Oct 27 08:04 mysql
-rw-rw---- 1 mysql mysql 50331648 Oct 27 08:04 ib_logfile0
-rw-rw---- 1 mysql mysql 12582912 Oct 27 08:04 ibdata1

Now, we need to copy the file mysql.server to /etc/init.d/ directory for MySQL server to start automatically upon server reboot.

[mysql@labserver support-files]$ su - root -c 'cp /workspace/mysql/5.6.21/support-files/mysql.server /etc/init.d'
Password:

[mysql@labserver support-files]$ ls -lrt /etc/init.d/mysql.server
-rwxr-xr-x 1 root root 10880 Oct 27 08:10 /etc/init.d/mysql.server

Since the above script reads configuration settings from /etc/my.cnf for starting MySQL daemon, we also need to copy the configuration file to /etc directory from the installation directory (/workspace/mysql/5.6.21/my.cnf).

[mysql@labserver ~]$ su - root -c 'cp /workspace/mysql/5.6.21/my.cnf /etc'
Password:
[mysql@labserver ~]$ ls -lrt /etc/my.cnf
-rw-r--r-- 1 root root 1167 Oct 27 08:20 /etc/my.cnf

We are now good to start the MySQL server daemon.

[mysql@labserver ~]$ /etc/init.d/mysql.server start
Starting MySQL....                                         [  OK  ]
[mysql@labserver ~]$ ps -ef | grep mysql
root     14402  6037  0 05:50 pts/1    00:00:00 su - mysql
mysql    14403 14402  0 05:50 pts/1    00:00:00 -bash
root     15030 27650  0 07:30 pts/2    00:00:00 su - mysql
mysql    15042 15030  0 07:30 pts/2    00:00:00 -bash
mysql    32305     1  0 08:23 pts/2    00:00:00 /bin/sh /workspace/mysql/5.6.21/bin/mysqld_safe --datadir=/workspace/mysql/data --pid-file=/workspace/mysql/data/labserver.home.com.pid
mysql    32520 32305 29 08:23 pts/2    00:00:02 /workspace/mysql/5.6.21/bin/mysqld --basedir=/workspace/mysql/5.6.21 --datadir=/workspace/mysql/data --plugin-dir=/workspace/mysql/5.6.21/lib/plugin --log-error=/workspace/mysql/data/labserver.home.com.err --pid-file=/workspace/mysql/data/labserver.home.com.pid --socket=/workspace/mysql/5.6.21/socket/mysql.sock --port=3313
mysql    32604 15042  0 08:23 pts/2    00:00:00 ps -ef
mysql    32605 15042  0 08:23 pts/2    00:00:00 grep mysql

Upon observing the output from the grep command, you can see the custom settings being reflected there.

Our MySQL Server is now up and functional with core databases being loaded into it. We can now access the MySQL Server.
However, before accessing the databases we need to set a password for the MySQL root user as this method of installation doesn’t create a random password for the root user.

[mysql@labserver ~]$ /workspace/mysql/5.6.21/bin/mysqladmin -u root password 'mysql' --socket=$MYSQL_SOCKET
Warning: Using a password on the command line interface can be insecure.

Notice that, I have passed the socket file while using the mysqladmin command. This is because I am using a custom socket file for authentication.

We are now allowed to query the MySQL server.

[mysql@labserver ~]$ mysql -u root -pmysql --socket=$MYSQL_SOCKET
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>

Optionally, we can also secure the MySQL installation by running mysql_secure_installation script.

Further Readings:

MySQL Server Administration

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed