Wednesday, July 10, 2013

Install TokuDB Mysql Server along with Existing mysql-server on Centos/Ubuntu Server

Tokudb has fractal tree engine which is mentioned to perform bettern than innodb in certain instances. In some scenarios you might want to run mysql-server which is patched with tokudb engine along with your current mysql server (for performance testing, evaluation etc).

A straightforward installation will mess up /var/lib/mysql, so follow these instructions to make sure that the server installation doesn't mess up existing installation.

1. Download and unzip the tokudb to /usr/local/

nareshv@fallenangel:~/Downloads$ sudo mv mysql-5.5.30-tokudb-7.0.1-linux-x86_64 /usr/local/
nareshv@fallenangel:/usr/local$ sudo ln -s mysql-5.5.30-tokudb-7.0.1-linux-x86_64 mysql
nareshv@fallenangel:/usr/local$ cd mysql
nareshv@fallenangel:/usr/local/mysql$ ls -l
total 80
drwxrwxr-x  2 nareshv nareshv  4096 Apr 19 04:57 bin
-rw-r--r--  1 nareshv nareshv 17987 Apr 19 03:11 COPYING
drwxrwxr-x  4 nareshv nareshv  4096 Apr 19 04:57 data
drwxrwxr-x  2 nareshv nareshv  4096 Apr 19 04:57 docs
drwxrwxr-x  3 nareshv nareshv  4096 Apr 19 04:57 include
-rw-r--r--  1 nareshv nareshv  7469 Apr 19 03:11 INSTALL-BINARY
drwxrwxr-x  3 nareshv nareshv  4096 Apr 19 04:57 lib
drwxrwxr-x  4 nareshv nareshv  4096 Apr 19 04:57 man
drwxrwxr-x 10 nareshv nareshv  4096 Apr 19 04:57 mysql-test
-rw-r--r--  1 nareshv nareshv  2552 Apr 19 03:11 README
-rw-r--r--  1 nareshv nareshv  3659 Apr 19 04:52 README-TOKUDB
drwxrwxr-x  2 nareshv nareshv  4096 Apr 19 04:57 scripts
drwxrwxr-x 27 nareshv nareshv  4096 Apr 19 04:57 share
drwxrwxr-x  4 nareshv nareshv  4096 Apr 19 04:57 sql-bench
drwxrwxr-x  2 nareshv nareshv  4096 Apr 19 04:57 support-files

2. Install the mysqlserver to /usr/local/mysql (not /var/lib/mysql)

nareshv@fallenangel:/usr/local/mysql$ sudo -u mysql ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql
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:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h fallenangel password 'new-password'

Alternatively you can run:
/usr/local/mysql/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 /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

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

Please report any problems with the /usr/local/mysql/scripts/mysqlbug script!

3. Create a new startup script to launch mysqld on port 3307

nareshv@fallenangel:/usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64$ sudo vi run.sh
#!/bin/bash

/usr/bin/env MYSQL_HOME=/usr/local/mysql ./bin/mysqld --user=mysql \
    --pid-file=/usr/local/mysql/mysqld.pid \
    --socket=/usr/local/mysql/mysqld.sock \
    --port=3307 \
    --basedir=/usr/local/mysql \
    --datadir=/usr/local/mysql/data \
    --lc-messages-dir=/usr/local/mysql/share \
    --tmpdir=/tmp \
    --skip-external-locking \
    --bind-address=127.0.0.1 \

nareshv@fallenangel:/usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64$

4. Run the mysql server in another terminal as root user

nareshv@fallenangel:/usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64$ sudo ./run.sh

5. Connect and check if mysql is available on 3307 / 127.0.0.1

nareshv@fallenangel:/usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64$ mysql -u root -h 127.0.0.1 -P 3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30-tokudb-7.0.1 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> quit
Bye

Confirm that variables point to /usr/share/mysql only
mysql> show variables like '%dir%';
+-----------------------------------------+-------------------------------------------------------------------+
| Variable_name                           | Value                                                             |
+-----------------------------------------+-------------------------------------------------------------------+
| basedir                                 | /usr/local/mysql                                                  |
| binlog_direct_non_transactional_updates | OFF                                                               |
| character_sets_dir                      | /usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64/share/charsets/ |
| datadir                                 | /usr/local/mysql/data/                                            |
| innodb_data_home_dir                    | /usr/local/mysql/data                                             |
| innodb_log_group_home_dir               | /usr/local/mysql/data                                             |
| innodb_max_dirty_pages_pct              | 75                                                                |
| lc_messages_dir                         | /usr/local/mysql/share/                                           |
| plugin_dir                              | /usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64/lib/plugin/     |
| slave_load_tmpdir                       | /tmp                                                              |
| tmpdir                                  | /tmp                                                              |
| tokudb_data_dir                         |                                                                   |
| tokudb_directio                         | OFF                                                               |
| tokudb_log_dir                          |                                                                   |
| tokudb_tmp_dir                          |                                                                   |
+-----------------------------------------+-------------------------------------------------------------------+
15 rows in set (0.00 sec)

4 comments:

  1. Hi Naresh,

    Article of Tokudb is pretty informative,Just need few clarifications.

    #if i have data of 200GB in MySQL version 5.6 with innodb engine,is it possible to install tokudb engine in existing mysql and convert the tables with innodb to tokudb engine?

    ReplyDelete
    Replies
    1. Vamsi,

      Yes, it is possible to convert the data. But, make sure to go through the User guide[1] to understand the differences between engines. (Read the FAQ for sure)

      [1] http://www.tokutek.com/wp-content/uploads/2014/03/mysql-5.5.36-tokudb-7.1.5-users-guide.pdf

      Delete
    2. Hi Naresh,

      I think what Vamsi meant is if it is possible to patch somehow the existing, factory mysql to allow conversion of innodb to tokudb engine, without installing a fresh patched mysql (with tokudb). This is my question also.

      Delete
    3. AFAIK, we can use MariaDB which has tokudb plugin available for use. So, the steps would look like this

      1. Take backup of existing mysql data (mysqldump)
      2. Uninstall mysql packages
      3. Install MariaDB packages and enable tokudb from /etc/my.cnf.d/tokudb.conf
      4. (use exact same settings for newer mariadb for innodb stuff)
      5. Alter tables to use tokudb

      or

      compile the tokudb plugin for existing mysql server

      HTH

      Delete