MySQL + octopus setup

In this article, I would like to show how to configure MySQL for further use of gem octopus, which is used for sharding and replication in Rails applications.
So, let's imagine that we are faced with the task of deploying three servers (the first one is Rails, the application is needed, the second is needed for Master, the third will act as Slave), set up replication between servers and make octopus work.

Step One - Pre-Tuning Servers
The actions described in this step need to be done both on the Master machine and on the Slave.

Install MySQL.
')

sudo apt-get install mysql-server 

Now let's go to the MySQL console:

 mysql -u root -p 

Create a database, as well as a user who will have all rights to work with this database:

 create database rails_myapp; GRANT ALL ON rails_myapp.* TO 'rails_myapp_user'@'localhost'; GRANT ALL ON rails_myapp.* TO 'rails_myapp_user'@'%'; FLUSH PRIVILEGES; EXIT; 

Step Two - Set Up Master
Open the MySQL configuration file on the Master server.

 sudo nano /etc/mysql/my.cnf 

Inside this file we will make a few changes. First, we find the following line:

 bind-address = 127.0.0.1 

Let's replace the standard IP address with 0.0.0.0, so that the Rails application can reach the server:

 bind-address = 0.0.0.0 

Our next step is to change the server-id value, look for the server-id in the [mysqld] section of the config file. You can choose any number as a server-id, but for simplicity, it is better to specify 1, just remember that this number should be unique for the group of servers that will participate in replication.

Once again, make sure that this line is commented out.

 server-id = 1 

Now go to the line with the value log_bin. The slave will copy all changes that will be logged in the log. Again, it is better to just comment out the line with log_bin:

 log_bin = /var/log/mysql/mysql-bin.log 

At the end, you must specify the name of the database to be replicated by the slave server. You can specify several bases by repeating this line for all bases that you want to replicate.

 binlog_do_db = rails_myapp 

All changes are made! You can save and close the file.

Restart MySQL.

 sudo service mysql restart 

Open MySQL - shell.

 mysql -u root -p 

You must grant privileges to Slave. To do this, use the following command:

 GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; 

Next you must switch to your base:

 USE rails_myapp; 

We lock the database to view the data that will be useful to us in the following steps:

 FLUSH TABLES WITH READ LOCK; 

Next, we type:

 SHOW MASTER STATUS; 

You will see something like this if you type the following command:

 mysql> SHOW MASTER STATUS; 

File	Position	Binlog_Do_DB
mysql-bin.000001	107	rails_myapp

1 row in set (0.00 sec)

Important! Write down or remember the file name and position number, these values ​​will be used later.
While your databases are locked, you need to export the rails_myapp database. Open the second terminal in a new window, make sure you type this command in bash shell, and not in the MySQL console.

 mysqldump -u root -p --opt rails_myapp > rails_myapp.sql 

Go back to the MySQL console, unlock the database (allow recording).

 UNLOCK TABLES; QUIT; 

Everything! We have finished setting up the Master.

Step three - set up the Slave.
Go to the MySQL server, open MySQL and create a database (the name is exactly the same as on the wizard):

 CREATE DATABASE rails_myapp; EXIT; 

Transfer the file with SQL - commands that you exported to the wizard and import.

 mysql -u root -p rails_myapp< /path/to/rails_myapp.sql 

Then you need to slightly correct the MySQL configuration file:

 sudo nano /etc/mysql/my.cnf 

The first thing to change is server-id. As you remember, the number should be unique within the group (we indicated 1 in the Master config).

 server-id = 2 

Now we will add (or comment out) the following three lines:

 relay-log = /var/log/mysql/mysql-relay-bin.log log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = newdatabase 

Set the new bind-address value:

 bind-address = 0.0.0.0 

Restart MySQL:

 sudo service mysql restart 

The next step is to start replication directly. Remember! Instead of MASTER_LOG_FILE and MASTER_LOG_POS, you must enter the values ​​you wrote down before.

 CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107; START SLAVE; 

How to check if Slave has successfully launched? You need to enter the command described below and view the log:

 SHOW SLAVE STATUS\G 

Step four - configure octopus.
In the Rails application, you need to add the following line to the Gemfile:

 gem 'ar-octopus' 

Inside the config / directory, you need to create a shards.yml file that will be responsible for configuring the Slave server. Remember! The database.yml file is responsible for the configuration of the Master, and the newly created shards.yml for the configuration of the Slave, do not forget that spaces and tabs in YML are important.

The file shards.yml, after filling should look something like this:

  octopus: replicated: true fully_replicated: true environments: - development - test development: slave: adapter: mysql2 encoding: utf8 database: rails_myapp username: rails_myapp_user password: qwerty host: 130.111.11.111 port: 3306 pool: 10 test: slave: adapter: mysql2 encoding: utf8 database: rails_myapp username: rails_myapp_user password: qwerty host: 130.111.111.111 port: 3306 pool: 10 

Everything! It remains to register in your models that they are replicated_model (), and run the Rails application. Having sent a POST request, you should see something like the following in the console:

  [Shard: slave] OurModel Load (1.0ms) SELECT `model`.* FROM `model` WHERE `chats`.`id` = 'gkjhgfhd' LIMIT 1 

Successful setting!