Table of contents
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:
If you click on gear icon along server name you have access to few customization, for master server:
For slave server (read only option is directly accessible):
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:
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:
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:
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:
And the main one is made of two servers:
Obviously nothing cannot be done command line to recover original situation but for this Orchestrator is of no help…
References
- Orchestrator Manual
- Introducing Orchestrator: manage and visualize your MySQL replication topologies and get home for dinner
- Orchestrator: MySQL Replication Topology Manager
Nwokike Chineme says:
is orchestrator compatible with windows OS
Yannick Jaquier says:
No not available on Windows OS. In any case doing MariaDB or MySQL on Windows is not the best option…
miczard says:
I just wondering if we can switch from Master (not the intermediate master) into slave and vice versa ?
thanks
Yannick Jaquier says:
Yes this is exactly what you would expect from a standard MySQL replication topology.