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.
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 calledreplicatord
, so, edit itssystemd
service file (replicatord.service
) in themysql-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
, restartmysqld
:$ systemctl restart mysqld
In this section, you install Tarantool and set up spaces for replication.
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 thett
instance configuration files:config.yaml
– specifies the following configurationapp: 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 environmentinstance001:
myapp.lua
– contains a Lua script with an application to loadbox.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
andmysqldata
spaces. Then exit with «CTRL+C».
MySQL and Tarantool are now set up. You can proceed to configure the replicator.
Edit the
replicatord.yml
file in the maintarantool-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 wheresystemd
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']
In this section, you enter a record into MySQL and check that the record is replicated to Tarantool. To do this:
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()