Improving MySQL with Tarantool | Tarantool
How-to guides SQL guides Improving MySQL with Tarantool

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 -p
    CREATE 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.

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

    For details, see the Configuration section.

  5. 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
    
  6. Next, start up the Lua program with tt, the Tarantool command-line utility:

    $ tt start mysql
    
  7. Enter the Tarantool instance:

    $ tt connect mysql:instance001
    
  8. 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”.

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 # 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
    
  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 -p
    USE 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()
    
Found what you were looking for?
Feedback