Shulou(Shulou.com)06/03 Report--

I. Description of environment: 1.1. Introduction to the system environment of 3 vm virtual machines:

The 3 VM systems are:

[root@mgr01 ~]# cat /etc/redhat-release CentOS Linux release 7.2.1511 (Core)

3 VM centos systems are closed iptables, closed selinux

3 virtual machine system time synchronization:

ntpdate ntp1.aliyun.com

Install an orchestrator mysql on each of the 3 vm virtual machines

orchestrator version: orchestrator-3.1.4-linux-amd64.tar.gz download address: https://github.com/github/orchestrator/releases

mysql version is mysql 5.7.24 GA binary version installed

Three machines IP:

10.0.0.130 172.16.0.13010.0.0.131 172.16.0.13110.0.0.132 172.16.0.132

Three VMs bound to host name:

[root@mgr01 bin]# cat /etc/hosts172.16.0.130 mgr01172.16.0.131 mgr03172.16.0.132 mgr02[root@mgr02 ~]# cat /etc/hosts172.16.0.132 mgr02172.16.0.131 mgr03172.16.0.130 mgr01[root@mgr03 bin]# cat /etc/hosts172.16.0.132 mgr02172.16.0.131 mgr03172.16.0.130 mgr01

Tip: orchestrator recommends using machine name instead of ip to manage MySQL instances, such as master_host in change master to If ip is specified, it may cause master-slave switchover or failover problems

So it is best to bind hosts and set the host name

1.2 Install MySQL on three VMs

The steps for installing MySQL are omitted and are the same as for a regular MySQL installation.

Configure in advance a master 2 copy from mysql based on Gtid filtering

172.16.0.131 master 172.16.0.130 slave 172.16.0.132 slave

The following parameters should be enabled in the configuration files of the three vm instances mysql:

Description: Open gtid, copy only the tables under test library test001, ignore other databases

[root@mgr01 orchestrator]# egrep -i 'gtid|replicate_wild' /data/mysql/mysql3306/my3306.cnf####: for gtid#gtid_executed_compression_period =1000 # 1000gtid_mode =on # offenforce_gtid_consistency =on # offreplicate_wild_do_table=test001.% replicate_wild_ignore_table=information_schema.% replicate_wild_ignore_table=performance_schema.% replicate_wild_ignore_table=mysql.% replicate_wild_ignore_table=orchestrator.%

172.16.0.131 master operation:

mysql -uroot -p'123456' -e "reset mater;"mysql -e "grant replication slave on *.* to repuser@'172.16.0.% ' identified by 'JuwoSdk21TbUser'; flush privileges;"mysqldump -uroot -p'123456' -B -A -F --set-gtid-purged=OFF --master-data=2 --single-transaction --events|gzip >/opt/test_$(date +%F).sql.gz

172.16.0.130 slave operation:

mysql

< /test_$(date +%F).sql.gzmysql -e "CHANGE MASTER TO MASTER_HOST='mgr03',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',MASTER_AUTO_POSITION = 1;start slave;show slave status\G" |grep -i "yes" 172.16.0.132 slave 操作： mysql < /test_$(date +%F).sql.gzmysql -e "CHANGE MASTER TO MASTER_HOST='mgr03',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',MASTER_AUTO_POSITION = 1;start slave;show slave status\G" |grep -i "yes"二、三台vm上安装Orchestrator 2.1、机器角色说明： orchestrator机器：172.16.0.130 172.16.0.131 172.16.0.132 orchestrator后端元数据库MySQL：172.16.0.131 监控目标数据库：172.16.0.130 172.16.0.131 172.16.0.132 2.2、每台VM机器都执行下面命令 安装orchestrator: 下载orchestrator安装包，orchestrator-3.1.4-linux-amd64.tar.gz https://github.com/github/orchestrator/releases 解压orchestrator安装包: tar -xf orchestrator-3.1.4-linux-amd64.tar.gz 会多出usr 、etc下面2个目录: [root@mgr01 ~]# ls -lrt /root/ drwxr-xr-x 3 root root 4096 Jan 26 22:05 usr drwxr-xr-x 3 root root 4096 Jan 26 22:05 etc 将usr/local/orchestrator/orchestrator-sample.conf.json移动到/etc下，并命名为orchestrator.conf.json cp /root/usr/local/orchestrator/orchestrator-sample.conf.json /etc/orchestrator.conf.json 安装完成后创建orchestrator需要用到的库和用户： CREATE DATABASE orchestrator;CREATE USER 'orchestrator'@'127.0.0.1' IDENTIFIED BY 'orchestrator';GRANT ALL PRIVILEGES ON `orchestrator`.* TO 'orchestrator'@'127.0.0.1';这里元数据库MySQL和orchestrator在同一台机器上，所以创建账号的时候用的'127.0.0.1'，如果不在同一台机器上，将IP换成orchestrator所在机器ip。 监控目标数据库授权： 在需要监控的目标数据库上进行授权CREATE USER 'orchestrator'@'172.16.0.%' IDENTIFIED BY 'orchestrator';GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'172.16.0.%';GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'172.16.0.%';提示：MySQLTopologyUser 账号的权限应该设置为super,process,reload,select,replicatiopn slave，官网文档中缺少了select权限，orchestrator切换过程中需要通过读取从库的mysql.slave_master_info表，获取复制账号和密码，如果没有select权限，将导致读取失败，并且不会有任何错误信息报出来。2.3、每台VM修改orchestrator配置文件 修改/etc/orchestrator.conf.json如下： ####配置orchestrator后端元数据库信息 "MySQLOrchestratorHost": "127.0.0.1","MySQLOrchestratorPort": 3306,"MySQLOrchestratorDatabase": "orchestrator","MySQLOrchestratorUser": "orchestrator","MySQLOrchestratorPassword": "orchestrator", ###配置orchestrator监控的目标数据库信息 "MySQLTopologyUser": "orchestrator","MySQLTopologyPassword": "orchestrator",2.4、只单独启动一台orchestrator服务 单独启动172.16.0.131 机器上的orchestrator服务，默认监听的端口是3000 启动命令： cd /root/usr/local/orchestrator && ./orchestrator --config=/etc/orchestrator.conf.json http &[root@mgr01 ~]# ps -ef|grep orcroot 3478 3477 6 23:47 pts/3 00:00:02 ./orchestrator --config=/etc/orchestrator.conf.json httproot 3489 2648 0 23:48 pts/2 00:00:00 grep --color=auto orc[root@mgr01 ~]# ss -lntup|grep orctcp LISTEN 0 128 :::3000 :::* users:(("orchestrator",pid=3478,fd=5)) 日志中有报错： 2020-02-20 23:47:40 ERROR ReadTopologyInstance(mgr01:3306) show slave hosts: ReadTopologyInstance(mgr01:3306) 'show slave hosts' returned row with : 2020-02-20 23:47:41 DEBUG Waiting for 15 seconds to pass before running failure detection/recovery2020-02-20 23:47:41 ERROR ReadTopologyInstance(mgr02:3306) show slave hosts: ReadTopologyInstance(mgr02:3306) 'show slave hosts' returned row with : 报错的解决办法： 在MySQL配置文件my.cnf中report_host参数， report_host为只读参数，必须重启mysql服务才可生效 report_host=x.x.x.x //ip为服务器自身的ip 提示:关于mysql的report-系列参数说明如下： #report-系列Report系列是设置在从库上的，包含四个参数 report-[host|port|user|password]. 当my.cnf中设置了report-host时，在从库执行start slave的时候，会将report-host和report-port（默认3306）发给主库，主库记录在全局哈希结构变量 slave_list 中 同时需要注意的是 mysql对report_host限制为最长60个字节长度，也就是非中文的60个字符，所以mysql服务器的主机名要小于60个字符，否则在做主从复制时，slave会报错 参考：https://www.jianshu.com/p/9a5b7d30b0ae 原因:my.cnf配置文件不加report_host ，在orchestrator程序中 show slave hosts 不会显示host，会导致程序报错的 或者是修改/etc/orchestrator.conf.json 配置文件参数DiscoverByShowSlaveHosts 为false,重启orchestrator 服务，这样就不需要设置report_host了 2.5、Web页面访问介绍 http://10.0.0.130:3000/web/status 初次打开web页面是看不到mysql cluster 集群名称的，需要点击discover发现instance，如下图：

Click Clusters again and cluster aliases and Instance appear:

Select status under home to see the current healthy nodes:

View detailed replication topology:

View analysis of replication failures:

Diagnosis of replication failure:

View details of replication:

On-line adjustment of replication relationship: 1 master 2 slave becomes cascade replication:

Then from cascade replication to 1 master 2 slave:

The above is an introduction to the simple web page application of orchestrator service single node startup management mysql replication cluster.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.