Orchestrator MySQL replication topology tool tutorial

Preamble

MySQL replication is simple to put in place but it could be a little bit harder when it comes to monitoring and promoting slave servers to master role. Orchestrator is super easy to put in place and if you do not like command line tools or have not subscribed to MONyog this is the tool to give a try. Orchestrator also forbid you operations that would result in an error.

I have already tested multiple solution like Master High Availability Manager and tools for MySQL (MHA), MySQL Utilities, Multi-Master Replication Manager for MySQL (MMM), Maxscale and MySQL Fabric. Orchestrator simply aim at graphically or command line representing your configuration and perform few re-configuration. It is anyway not a pure monitoring tool as SNMP traps cannot be send to your favorite corporate monitoring tool.

In below post I have used three virtual machines running Oracle Linux Server release 7.2 64 bits. I expected to do my tests with a simple master-slave configuration but realized this was not really interesting so created two slaves of my master instance. So I have:

  • server2.domain.com (192.168.56.102) is MariaDB 10.1.14 64 bits master server.
  • server3.domain.com (192.168.56.103) is MariaDB 10.1.14 64 bits slave server running two MariaDB instances.
  • server4.domain.com (192.168.56.104) is Orchestrator node with MySQL 5.7.13 64 bits for repository database.

For MariaDB instances I have:

  • server2.domain.com on port 3316 as master instance
  • server3.domain.com on port 3316 as first slave instance
  • server3.domain.com on port 3326 as second slave instance

Orchestrator installation

I’m not re-entering in MySQL replication (more specifically here MariaDB) as we have seen it recently in a previous post.

Orchestrator requires a MySQL backend database to store its information. I have to say that I’m a bit disappointed by that as I would have expected information to be saved in a JSON file or something similar. But according to Orchestrator author (Shlomi Noach) this is planned !

In your MySQL 5.7 repository instance create a database and an account to use it:

mysql> CREATE DATABASE IF NOT EXISTS orchestrator;
Query OK, 1 row affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON `orchestrator`.* TO 'orchestrator'@'%' IDENTIFIED BY 'secure_password';
Query OK, 0 rows affected (0.00 sec)

Copy and edit the Orchestrator configuration file:

[root@server4 ~]# cd /usr/local/orchestrator/
[root@server4 orchestrator]# cp orchestrator-sample.conf.json /etc/orchestrator.conf.json
[root@server4 orchestrator]# vi /etc/orchestrator.conf.json

What I have changed inside to match my configuration:

  "MySQLOrchestratorHost": "127.0.0.1",
  "MySQLOrchestratorPort": 3316,
  "MySQLOrchestratorDatabase": "orchestrator",
  "MySQLOrchestratorUser": ""orchestrator",
  "MySQLOrchestratorPassword": "secure_password",

For better display I have also customized:

  "RemoveTextFromHostnameDisplay": ".domain.com",

And also remove the default verbose output, re-activate it if you face any issue:

  "Debug": false,

Log file in case of issue is located at /var/log/orchestrator.log.

On all MariaDB instance create the Orchestrator account with:

GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'%' IDENTIFIED BY 'secure_password';

To start Orchestrator you need to move first to its directory because if you issue:

[root@server4 ~]# /usr/local/orchestrator/orchestrator --debug http &

You will get below error message:

html/template: "templates/layout" is undefined

Because resources directory is not accessible by daemon at startup so better use:

[root@server4 ~]# cd  /usr/local/orchestrator/
[root@server4 orchestrator]# ./orchestrator --debug http &
2016-06-24 15:05:20 INFO starting orchestrator
2016-06-24 15:05:20 INFO Read config: /etc/orchestrator.conf.json
2016-06-24 15:05:20 DEBUG Initializing orchestrator
2016-06-24 15:05:20 DEBUG Migrating database schema
2016-06-24 15:05:20 FATAL Cannot initiate orchestrator: Error 1067: Invalid default value for 'end_timestamp'

If you encounter this error this is because of the value of SQL mode for your backend repository instance and more specifically the NO_ZERO_DATE option:

mysql> show variables like 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                                                |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

So change sql_mode to something like:

sql_mode=oracle

Which gives:

mysql> show variables like 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                |
+---------------+----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER |
+---------------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

When started access your Orchestrator node on default port 3000 and in Discover menu start to add the node of your topology. At then end you should get something like:

orchestrator01
orchestrator01

If you click on gear icon along server name you have access to few customization, for master server:

orchestrator02
orchestrator02

For slave server (read only option is directly accessible):

orchestrator03
orchestrator03

You can also use commend line to display it:

[root@server4 ~]# /usr/local/orchestrator/orchestrator -c topology -i server2:3316 cli
server2.domain.com:3316   [0s,ok,10.1.14-MariaDB,rw,ROW]
+ server3.domain.com:3316 [0s,ok,10.1.14-MariaDB,rw,ROW,GTID]
+ server3.domain.com:3326 [0s,ok,10.1.14-MariaDB,rw,ROW,GTID]

Orchestrator testing

I have faced issues when trying to drag and drop my slaves, for example if I try to make server3.domain.com:3326 a slave of server3.domain.com:3316 then it is not possible:

orchestrator04
orchestrator04

If my master server fails Orchestrator report it but I have no option to restart it. To do this it would require an agent or a system connection to remote, Orchestrator agent is available but it would be a much complex implementation. Here, same, if I try to make make server3.domain.com:3326 a slave of server3.domain.com:3316 then it is not possible:

orchestrator05
orchestrator05

As no error message with graphical interface I have tried command line and now error message is clear:

[root@server4 ~]# /usr/local/orchestrator/orchestrator -c relocate -i server3.domain.com:3326 -d server3.domain.com:3316
2016-06-29 15:45:51 DEBUG Initializing orchestrator
2016-06-29 15:45:51 ERROR server3.domain.com:3326 cannot replicate from server3.domain.com:3316. Reason: instance does not have log_slave_updates enabled: server3.domain.com:3316
2016-06-29 15:45:51 FATAL 2016-06-29 15:45:51 ERROR server3.domain.com:3326 cannot replicate from server3.domain.com:3316. Reason: instance does not have log_slave_updates enabled: server3.domain.com:3316

So I activated log_slave_updates parameter in my.cnf file of all my instances (in case my master becomes a slave) as the parameter is not dynamic:

MariaDB [(none)]> show variables like '%log_slave_updates%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> set global log_slave_updates=on;
ERROR 1238 (HY000): Variable 'log_slave_updates' is a read only variable

Once activated we see a difference in printing with the stripes along the servers either graphical or command line:

[root@server4 ~]# /usr/local/orchestrator/orchestrator -c topology -i server2:3316 cli
server2.domain.com:3316   [0s,ok,10.1.14-MariaDB,rw,ROW,>>]
+ server3.domain.com:3316 [0s,ok,10.1.14-MariaDB,rw,ROW,>>,GTID]
+ server3.domain.com:3326 [0s,ok,10.1.14-MariaDB,rw,ROW,>>,GTID]

If my master fail (or in case you want to cascade the slaves) I make server3.domain.com:3316 my new master and stop replication on it (you would do the same in case you have only one slave). I set server3.domain.com:3326 a slave of server3.domain.com:3116:

orchestrator06
orchestrator06
orchestrator07
orchestrator07

But the graphical interface does not allow me to put back server2.domain.com:3316 a slave and even command line is asking me to do it manually:

[root@server4 ~]# /usr/local/orchestrator/orchestrator -c relocate -i server2.domain.com:3316 -d server3.domain.com:3316
2016-06-30 15:12:36 ERROR Relocating server2.domain.com:3316 below server3.domain.com:3316 turns to be too complex; please do it manually
2016-06-30 15:12:36 FATAL 2016-06-30 15:12:36 ERROR Relocating server2.domain.com:3316 below server3.domain.com:3316 turns to be too complex; please do it manually

So you end up with this configuration made of two clusters:

orchestrator08
orchestrator08

And the main one is made of two servers:

orchestrator09
orchestrator09

Obviously nothing cannot be done command line to recover original situation but for this Orchestrator is of no help…

References

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>