Improving MySQL with Tarantool

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 

Clone the Tarantool-MySQL replication package from GitHub:

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

Build the replicator with cmake:

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

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 

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

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

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

mysql -u root -p CREATE DATABASE menagerie; QUIT 

Get some sample data from MySQL. The data will be pulled into the root directory. After that, install it 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 

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 -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 

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 = 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, restart mysqld:

$ systemctl restart mysqld 

Go to the Download page and follow the installation instructions.

Install the tt CLI utility.

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

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 }) end end bootstrap() 

For details, see the Configuration section.

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

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

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

$ tt start mysql 

Enter the Tarantool instance:

$ tt connect mysql:instance001 

Check that the target spaces were successfully created:

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

At the bottom, you will see mysqldaemon and mysqldata spaces. Then exit with “CTRL+C”.

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

nano replicatord.yml 

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 # 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 

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

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

Next, start up the replicator:

$ systemctl start replicatord 

Enter the Tarantool instance:

$ tt connect mysql:instance001 

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'] 

Exit the Tarantool instance with CTRL-D.

Insert a record into MySQL:

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

In the terminal, enter the Tarantool instance:

$ tt connect mysql:instance001 

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

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