Improving MySQL with Tarantool

Replicating MySQL is one of the Tarantool’s killer functions. It allows you to keep your existing MySQL database while at the same time accelerating it and scaling it out horizontally. Even if you aren’t interested in extensive expansion, simply replacing existing replicas with Tarantool can save you money, because Tarantool is more efficient per core than MySQL. To read a testimonial of a company that implemented Tarantool replication on a large scale, please see here.

Notes:

if you run into any trouble with regards to the basics of Tarantool, you may wish to consult the Getting started guide or the Data model description.
these instructions are for CentOS 7.5 and MySQL 5.7. They also assume that you have systemd installed and are working with an existing MySQL installation.
a helpful log for troubleshooting during this tutorial is replicatord.log in /var/log. You can also have a look at the instance’s log example.log in /var/log/tarantool.

So let’s proceed.

First we’ll install the necessary packages in CentOS:

yum -y install git ncurses-devel cmake gcc-c++ boost boost-devel wget unzip nano bzip2 mysql-devel mysql-lib

Next we’ll clone the Tarantool-MySQL replication package from GitHub:

git clone https://github.com/tarantool/mysql-tarantool-replication.git

Now we can build the replicator with cmake:

cd mysql-tarantool-replication
git submodule update --init --recursive
cmake .
make

Our replicator will run as a systemd daemon called replicatord, so let’s edit its systemd service file, replicatord.service, in the mysql-tarantool-replication repo.
```
nano replicatord.service
```
Change the following line:
```
ExecStart=/usr/local/sbin/replicatord -c /usr/local/etc/replicatord.cfg
```
Replace the .cfg extension with .yml:
```
ExecStart=/usr/local/sbin/replicatord -c /usr/local/etc/replicatord.yml
```

Next let’s copy some files from our replicatord repo to other necessary locations:

cp replicatord /usr/local/sbin/replicatord
cp replicatord.service /etc/systemd/system

Now let’s enter the MySQL console and create a sample database (depending on your existing installation, you may of course be a user other than root):
```
mysql -u root -p
CREATE DATABASE menagerie;
QUIT
```

Next we’ll get some sample data from MySQL, which we’ll pull into our root directory, then install from the terminal:

cd
wget http://downloads.mysql.com/docs/menagerie-db.zip
unzip menagerie-db.zip
cd menagerie-db
mysql -u root -p menagerie < cr_pet_tbl.sql
mysql -u root -p menagerie < load_pet_tbl.sql
mysql menagerie -u root -p < ins_puff_rec.sql
mysql menagerie -u root -p < cr_event_tbl.sql

Let’s enter the MySQL console now and massage the data for use with the Tarantool replicator (we are adding an ID, changing a field name to avoid conflict, and cutting down the number of fields; note that with real data, this is the step that will involve the most tweaking):
```
mysql -u root -p
USE menagerie;
ALTER TABLE pet ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE pet CHANGE COLUMN 'name' 'name2' VARCHAR(255);
ALTER TABLE pet DROP sex, DROP birth, DROP death;
QUIT
```

Now that we have the sample data set up, we’ll need to edit MySQL’s configuration file for use with the replicator.

cd
nano /etc/my.cnf

Note that your my.cnf for MySQL could be in a slightly different location. Set:

[mysqld]
binlog_format = ROW
server_id = 1
log-bin = mysql-bin
interactive_timeout = 3600
wait_timeout = 3600
max_allowed_packet = 32M
socket = /var/lib/mysql/mysql.sock
bind-address = 127.0.0.1

[client]
socket = /var/lib/mysql/mysql.sock

After exiting nano, we’ll restart mysqld:
```
systemctl restart mysqld
```
Next, let’s install Tarantool and set up spaces for replication. Go to the Download page and follow the instructions there to install Tarantool.
Now we will write a standard Tarantool program by editing the Lua example, which comes with Tarantool:
```
cd
nano /etc/tarantool/instances.available/example.lua
```

Replace the entire contents of the file with the following:

box.cfg {
    listen = 3301;
    memtx_memory = 128 * 1024 * 1024; -- 128Mb
    memtx_min_tuple_size = 16;
    memtx_max_tuple_size = 128 * 1024 * 1024; -- 128Mb
    vinyl_memory = 128 * 1024 * 1024; -- 128Mb
    vinyl_cache = 128 * 1024 * 1024; -- 128Mb
    vinyl_max_tuple_size = 128 * 1024 * 1024; -- 128Mb
    vinyl_write_threads = 2;
    wal_mode = "none";
    wal_max_size = 256 * 1024 * 1024;
    checkpoint_interval = 60 * 60; -- one hour
    checkpoint_count = 6;
    force_recovery = true;

     -- 1 – SYSERROR
     -- 2 – ERROR
     -- 3 – CRITICAL
     -- 4 – WARNING
     -- 5 – INFO
     -- 6 – VERBOSE
     -- 7 – DEBUG
     log_level = 7;
     too_long_threshold = 0.5;
 }

box.schema.user.grant('guest','read,write,execute','universe')

local function bootstrap()

    if not box.space.mysqldaemon then
        s = box.schema.space.create('mysqldaemon')
        s:create_index('primary',
        {type = 'tree', parts = {1, 'unsigned'}, if_not_exists = true})
    end

    if not box.space.mysqldata then
        t = box.schema.space.create('mysqldata')
        t:create_index('primary',
        {type = 'tree', parts = {1, 'unsigned'}, if_not_exists = true})
    end

end

bootstrap()

To understand more of what’s happening here, it would be best to have a look back at the earlier articles in the Tarantool 101 series or use the getting-started guide.

Now we need to create a symlink from instances.available to a directory named instances.enabled (similar to NGINX). So in /etc/tarantool run the following:
```
mkdir instances.enabled
ln -s /instances.available/example.lua instances.enabled
```
Next we can start up our Lua program with tt, the Tarantool command-line utility:
```
tt start example
```
Now let’s enter our Tarantool instance, where we can check that our target spaces were successfully created:
```
tt connect example
```
```
tarantool> box.space._space:select()
```
At the bottom you will see “mysqldaemon” and “mysqldata” spaces. Then exit with “CTRL+C”.

Now that we have MySQL and Tarantool set up, we can proceed to configure our replicator. First let’s work with replicatord.yml in the main tarantool-mysql-replication directory.

nano replicatord.yml

Change the entire file as follows, making sure to add your MySQL password and to set the appropriate user:

mysql:
    host: 127.0.0.1
    port: 3306
    user: root
    password:
    connect_retry: 15 # seconds

tarantool:
    host: 127.0.0.1:3301
    binlog_pos_space: 512
    binlog_pos_key: 0
    connect_retry: 15 # seconds
    sync_retry: 1000 # milliseconds

mappings:
    - database: menagerie
      table: pet
      columns: [ id, name2, owner, species ]
      space: 513
      key_fields:  [ 0 ]
      # insert_call: function_name
      # update_call: function_name
      # delete_call: function_name

Now we need to copy replicatord.yml to the location where systemd looks for it:
```
cp replicatord.yml /usr/local/etc/replicatord.yml
```

Next we can start up the replicator:

systemctl start replicatord

Now we can enter our Tarantool instance and do a select on the “mysqldata” space. We will see the replicated content from MySQL:

tt connect example

tarantool> box.space.mysqldata:select()
---
- - [1, 'Fluffy', 'Harold', 'cat']
  - [2, 'Claws', 'Gwen', 'cat']
  - [3, 'Buffy', 'Harold', 'dog']
  - [4, 'Fang', 'Benny', 'dog']
  - [5, 'Bowser', 'Diane', 'dog']
  - [6, 'Chirpy', 'Gwen', 'bird']
  - [7, 'Whistler', 'Gwen', 'bird']
  - [8, 'Slim', 'Benny', 'snake']
  - [9, 'Puffball', 'Diane', 'hamster']

Finally let’s enter a record into MySQL and then go back to Tarantool to make sure it’s replicated. So first we’ll exit our Tarantool instance with CTRL-C, and then say:
```
mysql -u root -p
USE menagerie;
INSERT INTO pet(name2, owner, species) VALUES ('Spot', 'Brad', 'dog');
QUIT
```
Once back in the terminal enter:
```
tt connect example
```
```
tarantool> box.space.mysqldata:select()
```
You should see the replicated data in Tarantool!

Version:

Improving MySQL with Tarantool