Today I will show you the config of a web application using database replication architecture.
Before you begin, make sure your computer has the following programs installed:
- Docker
- Ruby on Rails v6.0
- MySQL Workbench
References
If you do not know what the database replication architecture is, you can refer to the following article to understand more https://kipalog.com/posts/Gioi-thieu-MySQL-Replication
In addition, this article I also refer to the following links: https://github.com/wagnerjfr/mysql-master-slaves-replication-docker https://tomkadwill.com/multiple-databases-in-rails
1. Overview
I will build an example of a web application using the Rails framework, have a database using a Replication architecture, including 1 master and 2 slaves. The work that we need to do will include 2 main jobs:
- Set up Replication architecture at Database layer
- Building a Web app that can read and write data with a built-in database architecture.
2. Building the Master Slave architecture with MySQL
In fact, when building the Master Slave architecture, each instance will be on a different host, but during this demo I can only use one computer, so I will use the docker to Can create the Mysql server intance to build.
2.1 Install Mysql Server docker container
Create a network docker
1 2 | $ docker network create replicanet |
To view available docker networks, use the command
1 2 | $ docker network ls |
Use the following command line to initialize 3 MySQL containers.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $ docker run -d --name=master --net=replicanet --hostname=master -p 3308:3306 -v $PWD/d0:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mypass mysql/mysql-server:5.7 --server-id=1 --log-bin='mysql-bin-1.log' $ docker run -d --name=slave1 --net=replicanet --hostname=slave1 -p 3309:3306 -v $PWD/d1:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mypass mysql/mysql-server:5.7 --server-id=2 $ docker run -d --name=slave2 --net=replicanet --hostname=slave2 -p 3310:3306 -v $PWD/d2:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mypass mysql/mysql-server:5.7 --server-id=3 |
Can check whether the container has started with the command
1 2 | $ docker ps -a |
Wait until the containers reach a healthy state to continue.
1 2 3 4 5 | CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 33790572c785 mysql/mysql-server:5.7 "/entrypoint.sh --..." 22 minutes ago Up 22 minutes (healthy) 33060/tcp, 0.0.0.0:3310->3306/tcp slave2 0918892aaad3 mysql/mysql-server:5.7 "/entrypoint.sh --..." 22 minutes ago Up 22 minutes (healthy) 33060/tcp, 0.0.0.0:3309->3306/tcp slave1 3ece985d3470 mysql/mysql-server:5.7 "/entrypoint.sh --..." 22 minutes ago Up 22 minutes (healthy) 33060/tcp, 0.0.0.0:3308->3306/tcp master |
2.2 Configuring Master and Slave
2.2.1 Installing Master Node
[Optional] If you want to use semisynchronous replication, then run the command below
1 2 3 4 5 6 | docker exec -it master mysql -uroot -pmypass -e "INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';" -e "SET GLOBAL rpl_semi_sync_master_enabled = 1;" -e "SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 2;" -e "SHOW VARIABLES LIKE 'rpl_semi_sync%';" |
The semisynchronous plugin installation has the wrong purpose to minimize data lag during data synchronization between master and slave.
If installed successfully, the output will be as follows
1 2 3 4 5 6 7 8 9 10 11 12 | mysql: [Warning] Using a password on the command line interface can be insecure. +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 2 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | +-------------------------------------------+------------+ |
Initialize at the master node a user replicate so that the slave can access and retrieve data.
1 2 3 4 5 | docker exec -it master mysql -uroot -pmypass -e "CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';" -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';" -e "SHOW MASTER STATUS;" |
Output:
1 2 3 4 5 6 7 | mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | mysql-bin-1.000003 | 595 | | | | +--------------------+----------+--------------+------------------+-------------------+ |
2.2.2 Install Slaves Node
Similarly, to be able to use semisynchronous mechanisms, the slave nodes need to install the plugin
1 2 3 4 5 6 7 | for N in 1 2 do docker exec -it slave$N mysql -uroot -pmypass -e "INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';" -e "SET GLOBAL rpl_semi_sync_slave_enabled = 1;" -e "SHOW VARIABLES LIKE 'rpl_semi_sync%';" done |
Output
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql: [Warning] Using a password on the command line interface can be insecure. +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ mysql: [Warning] Using a password on the command line interface can be insecure. +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | |
Change the name of the log file at the Master Node we printed before running the setting command at the slave node
The value needs to change according to the system on your computer:
1 2 | MASTER_LOG_FILE='mysql-bin-1.000003' |
Run the command below to set Slave Node
1 2 3 4 5 6 7 8 | for N in 1 2 do docker exec -it slave$N mysql -uroot -pmypass -e "CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin-1.000003';" docker exec -it slave$N mysql -uroot -pmypass -e "START SLAVE;" done |
Check slave replication status on slave1 and slave2 :
1 2 3 4 | $ docker exec -it slave1 mysql -uroot -pmypass -e "SHOW SLAVE STATUSG" $ docker exec -it slave2 mysql -uroot -pmypass -e "SHOW SLAVE STATUSG" |
If the output is similar to the below, it is successful.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | *************************** 1. row *************************** Slave_IO_State: Checking master version Master_Host: master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-1.000003 Read_Master_Log_Pos: 595 Relay_Log_File: slave1-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin-1.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... |
2.2.3 Testing installation results
Now I will check if the installation result was successful or not by creating a new database at the Master Node, if it is correct, it will synchronize that database to the Slave Node. Execute the command below at Master Node
1 2 | $ docker exec -it master mysql -uroot -pmypass -e "CREATE DATABASE TEST; SHOW DATABASES;" |
Output:
1 2 3 4 5 6 7 8 9 10 11 | mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | TEST | | mysql | | performance_schema | | sys | +--------------------+ |
Check if the slave node is synchronized or not
1 2 3 4 5 6 | for N in 1 2 do docker exec -it slave$N mysql -uroot -pmypass -e "SHOW VARIABLES WHERE Variable_name = 'hostname';" -e "SHOW DATABASES;" done |
If the output on the 2 slave nodes both Database TEST exists, it seems we have successfully installed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+--------+ | Variable_name | Value | +---------------+--------+ | hostname | slave1 | +---------------+--------+ +--------------------+ | Database | +--------------------+ | information_schema | | TEST | | mysql | | performance_schema | | sys | +--------------------+ mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+--------+ | Variable_name | Value | +---------------+--------+ | hostname | slave2 | +---------------+--------+ +--------------------+ | Database | +--------------------+ | information_schema | | TEST | | mysql | | performance_schema | | sys | +--------------------+ |
2.3 Small note
When installing mysql server instances with docker, to access mysql from the external environment into the docker container, you need to create a mysql account to grant it the following permissions (used to access mysql workbench or an application). Use rails to access it as the demo I made below):
B1: Go to the bash of docker container
1 2 | docker exec -it <mysql container name> /bin/bash |
B2: Open terminal of mysql server
1 2 | mysql -u root -p |
B3: Execute the following command:
1 2 3 4 | create user 'user'@'%' identified by 'pass'; grant all privileges on *.* to 'user'@'%' with grant option; flush privileges; |
The symbol (%) means allow all ip can access. If you want to restrict, you can change the% sign with the ip you want to access the database.
3. Build webapp with Rails framework
3.1 Using multiple databases in Rails 6
An interesting point is that from Rails 6 onwards, this framework supports multiple databases, so we can easily install our application using a replication architecture.
First, I create a web app with rails 6 as follows:
1 2 | $ rails new rails_replication_without_gem -d mysql |
Then use the scaffold command to quickly generate a basic CRUD flow
1 2 | $ rails g scaffold User name:string address:string |
Now the most important part is configuring the database, using the replication architecture.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | default: &default adapter: mysql2 encoding: utf8mb4 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> socket: /var/run/mysqld/mysqld.sock development: master: <<: *default database: master username: user password: pass host: 127.0.0.1 port: 3308 slave1: <<: *default database: slave1 host: 127.0.0.1 port: 3309 username: user password: pass replica: true slave2: <<: *default database: slave2 host: 127.0.0.1 port: 3309 username: user password: pass replica: true |
OK, similar to configing multiple databases with Rails 6, the configuration for database replication is the same, except we will add the option
1 2 | replica: true |
to determine which node is slave.
Then proceed to create and migrate database
1 2 | rails db:create && rails db:migrate |
There is one place that you should pay attention, when I proceed to create the database, the log only returns a database master created, thanks to the replica: true option that I have configured. In the absence of config or set to false, there will be 3 databases initialized: master, slave1 and slave2.
Later in the model, we will use the connects_to function to specify which database to write and which databas to read. According to master-slave architecture, data will be written to master and read in slave.
1 2 3 4 | class User << ApplicationRecord connects_to database: { writing: :master, reading: :slave } end |
OK, now there is a problem that, in the initial overview, my database architecture consists of 1 master and 2 slaves. Why is there a slave node in this config? This is a limitation when using multiple databases of Rails when applied to the Replication architecture. We can only set 1 Node Master and 1 Node Slave. In case of using with 2 or more Slave, I will continue to guide in the next section.
In addition, there is a problem that Rails 6 has supported us that is solving data lag by automatically switching read / write database. As we know, each request to write / edit data will be sent to the master node, and the request to read data will be sent to the slave node. However, in the case of a request that sends a request to read data in less than 2 seconds after a write request, that read request will be sent to the master node instead of the slave to ensure it can read the latest data instead. Old data has not been updated in the slave. The default will be 2 seconds, but we can set it in the application.rb file
To enable it, we configure the following in application.rb
1 2 3 4 | config.active_record.database_selector = { delay: 2.seconds } config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session |
3.2 Use Makara gem
As I mentioned above, when using multiple with Rails 6, it has 1 drawback is it is impossible to use 2 or more slaves for the entire model. However, some gems that can do this are: Octopus and Makara .
According to the announcement, the Octopus gem is in maitain mode due to Rails 6 already having support for multiple databases. So I’ll use the Makara gem as an example. Although it was published later than the Octopus gem, Makara was rated better by the user than Octopus.
And the installation of gem is extremely simple, you just need to go to the gem’s document page to be able to do it.
In addition to allowing the user to set up the master slave easily, the Makara gem also allows us to add many other customizations such as adjusting the weight of requests of the slaves, the ability to report errors is also very clear.
Conclude
The above is my guide on how to install a database replication architecture, in addition to how to configure it for use with a basic Rails web app. And you should note that it is up to your application and the advantages and disadvantages of each type of database architecture to choose the architecture to suit your application. Hope the article gives you a lot of useful knowledge. Dear