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, 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, see the following article.

If you run into any trouble with regards to the basics of Tarantool, see the Getting started guide or the Data model description. 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.

The tutorial is intended for CentOS 7.5 and MySQL 5.7. The tutorial requires that systemd and MySQL are installed.

In this section, you configure MySQL and create a database.

1. First, 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

2. Clone the Tarantool-MySQL replication package from GitHub:

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

3. Build the replicator with cmake:

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

4. The replicator will run as a systemd daemon called replicatord, so, edit its systemd service file (replicatord.service) in the mysql-tarantool-replication repository:

    
   $ nano replicatord.service

   The following line should be changed:

    
   ExecStart=/usr/local/sbin/replicatord -c /usr/local/etc/replicatord.cfg

   To change it, replace the .cfg extension with .yml:

    
   ExecStart=/usr/local/sbin/replicatord -c /usr/local/etc/replicatord.yml

5. Next, copy the files from the replicatord repository to other necessary locations:

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

6. Enter MySQL console and create a sample database (depending on your existing installation, you may be a user other than root):

    
   mysql -u root -pCREATE DATABASE menagerie;QUIT

7. Get some sample data from MySQL. The data will be pulled into the root directory. After that, install it from the terminal.

    
   cdwget http://downloads.mysql.com/docs/menagerie-db.zipunzip menagerie-db.zipcd menagerie-dbmysql -u root -p menagerie < cr_pet_tbl.sqlmysql -u root -p menagerie < load_pet_tbl.sqlmysql menagerie -u root -p < ins_puff_rec.sqlmysql menagerie -u root -p < cr_event_tbl.sql

8. Enter MySQL console and massage the data for use with the Tarantool replicator. In this step, you:

   • add an ID
   • change a field name to avoid conflict
   • cut down the number of fields

   With real data, this is the step that involves the most tweaking.

    
   mysql -u root -pUSE 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

9. The sample data is set up. Edit MySQL configuration file to use it 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 = ROWserver_id = 1log-bin = mysql-bininteractive_timeout = 3600wait_timeout = 3600max_allowed_packet = 32Msocket = /var/lib/mysql/mysql.sockbind-address = 127.0.0.1[client]socket = /var/lib/mysql/mysql.sock

10. After exiting nano, restart mysqld:

     
    $ systemctl restart mysqld

In this section, you install Tarantool and set up spaces for replication.

1. Go to the Download page and follow the installation instructions.

2. Install the tt CLI utility.

3. Create a new tt environment in the current directory using the tt init command.

4. In the /etc/tarantool/instances.available/mysql directory, create the tt instance configuration files:

   • config.yaml – specifies the following configuration

 
app:  file: 'myapp.lua'groups:  group001:    replicasets:      replicaset001:        instances:          instance001:            iproto:              listen:              - uri: '127.0.0.1:3301'

 
- `instances.yml` – specifies instances to run in the current  environment

 
instance001:

 
- `myapp.lua` – contains a Lua script with an application to load

 
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 })    endendbootstrap()

 
For details, see the [Configuration](../../../configuration#configuration) section.

5. Inside the instances.enabled directory of the created tt environment, create a symlink (mysql) to the directory from the previous step:

 
``` bash$ ln -s /etc/tarantool/instances.available/mysql mysql```

6. Next, start up the Lua program with tt, the Tarantool command-line utility:

 
``` bash$ tt start mysql```

7. Enter the Tarantool instance:

 
``` bash$ tt connect mysql:instance001```

8. Check that the target spaces were successfully created:

 
``` tarantoolsessionmysql:instance001> box.space._space:select()```At the bottom, you will see `mysqldaemon` and `mysqldata` spaces.Then exit with "CTRL+C".

MySQL and Tarantool are now set up. You can proceed to configure the replicator.

1. Edit the replicatord.yml file in the main tarantool-mysql-replication directory:

    
   nano replicatord.yml

2. Change the entire file as follows. Don't forget to add your MySQL password and set the appropriate user:

    
   mysql:    host: 127.0.0.1    port: 3306    user: root    password:    connect_retry: 15 # secondstarantool:    host: 127.0.0.1:3301    binlog_pos_space: 512    binlog_pos_key: 0    connect_retry: 15 # seconds    sync_retry: 1000 # millisecondsmappings: - 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

3. Copy replicatord.yml to the location where systemd looks for it:

    
   $ cp replicatord.yml /usr/local/etc/replicatord.yml

4. Next, start up the replicator:

    
   $ systemctl start replicatord

5. Enter the Tarantool instance:

    
   $ tt connect mysql:instance001

6. Do a select on the mysqldata space. The replicated content from MySQL looks the following way:

    
   mysql:instance001> 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']

In this section, you enter a record into MySQL and check that the record is replicated to Tarantool. To do this:

1. Exit the Tarantool instance with CTRL-D.

2. Insert a record into MySQL:

    
   mysql -u root -pUSE menagerie;INSERT INTO pet(name2, owner, species) VALUES ('Spot', 'Brad', 'dog');QUIT

3. In the terminal, enter the Tarantool instance:

    
   $ tt connect mysql:instance001

4. To see the replicated data in Tarantool, run the following command:

    
   mysql:instance001> box.space.mysqldata:select()