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

As a leading database cloud platform solution provider in China, Walker Technology has been committed to the research and development of enterprise-class database cloud platform products. To provide users with high-performance, high-availability and scalable database cloud environment and database platforms required by different business scenarios, to meet customers' needs for extreme performance, data security, disaster recovery backup, business sustainability and other needs. With its professional team, high-quality products, cutting-edge technology and intimate service, Wachi Technology has won the trust and respect of customers and the recognition of the market.

-

Background

The last article talked about the installation of a single node of orchestrator. In this article, we continue to explore the journey of orchestrator and talk about how orchestrator enables the discovery of database instance replication topologies.

Given an example, how to find yourself

Two parameters are involved here: HostnameResolveMethod and MySQLHostnameResolveMethod

HostnameResolveMethod has three options: "cname", "default" and "none"

Cname: domain name resolution through CNAME (resolve hostname)

Default: no special parsing, no special resolving via net protocols

None:do nothing

MySQLHostnameResolveMethod has three options: "@ @ hostname", "@ @ report_host", ""

@ @ hostname: select @ @ hostname

@ @ report_host: select @ @ report_host

"": do nothing

Here is a problem to pay attention to:

Suppose there are two database servers in the production environment with the same hostname, such as localhost.localdomain;, and the orch configuration parameter HostnameResolveMethod uses the default "default" and MySQLHostnameResolveMethod uses the default "@ @ hostname". Then, when looking for it, orch parses the I / P address entered by the user into hostname, but because there are two machines with the same hostname, it may lead to an error, that is, orch cannot find the correct server.

Therefore, it is best to ensure that different servers have different hostnames in the online environment.

Given the master library, how to find the slave library

It is controlled by parameter DiscoverByShowSlaveHosts. If true, an attempt is made to discover the slave library first through the show slave hosts command. There will be three situations at this time.

If the host field in the correct report_host,show slave hosts from the library is set to display the correct IP, the slave library is found directly through show slave hosts.

If the host field in the report_host,show slave hosts where the library is set incorrectly displays the wrong IP, orchestrator cannot find the slave library.

-if the IP ping is not available, report the following information:

[mysql] 17:57:24 on 2019-10-29 driver.go:81: net.Error from Dial ()': dial tcp 10.10.30.222 dial tcp 3306: I timeout

[mysql] 17:57:25 on 2019-10-29 driver.go:81: net.Error from Dial ()': dial tcp 10.10.30.222 dial tcp 3306: I timeout

[mysql] 17:57:26 on 2019-10-29 driver.go:81: net.Error from Dial ()': dial tcp 10.10.30.222 dial tcp 3306: I timeout

2019-10-29 17:57:26 ERROR driver: bad connection

-if IP ping is connected, the following information may be reported:

2019-10-29 18:15:34 ERROR dial tcp 10.10.30.228 ERROR dial tcp 3306: connect: connection refused

2019-10-29 18:15:40 ERROR dial tcp 10.10.30.228 ERROR dial tcp 3306: connect: connection refused

2019-10-29 18:15:46 ERROR dial tcp 10.10.30.228 ERROR dial tcp 3306: connect: connection refused

2019-10-29 18:15:52 ERROR dial tcp 10.10.30.228 ERROR dial tcp 3306: connect: connection refused

/ / or

2019-10-29 18:11:11 ERROR Error 1045: Access denied for user 'orchestrator'@'10.10.30.146' (using password: YES)

WARNING: NamedStopwatch.Stop ("instance") IsRunning is false

2019-10-29 18:11:17 ERROR Error 1045: Access denied for user 'orchestrator'@'10.10.30.146' (using password: YES)

WARNING: NamedStopwatch.Stop ("instance") IsRunning is false

If the slave library is not set, the host field in the report_host,show slave hosts is displayed as empty, then the slave library is found through processlist.

-at this point, the following information will be reported:

2019-08-06 18:12:49 ERROR ReadTopologyInstance (10.10.30.129) show slave hosts: ReadTopologyInstance (10.10.30.129) 'show slave hosts' returned row with:

If false, the slave library is discovered through information_schema.processlist.

Select substring_index (host,':', 1) as slave_hostname from information_schema.processlist where command IN ('Binlog Dump',' Binlog Dump GTID'); given the slave library, how to find the master library

Use the show slave status command to discover the main library.

DiscoveryByShowSlaveHosts meaning

Since the host displayed by the show slave status command is not necessarily accurate, why add the parameter DiscoverByShowSlaveHosts?

There are several reasons for this:

First of all, MaxScale does not support PROCESSLIST, so SHOW SLAVE HOSTS is the only option.

More importantly, if you only discover the slave library through information_schema.processlist, master will not know which port replica is listening on. Show processlist displays only the socket port used by the replication process, not the port on which the replica instance listens. So you need to set the report_host and report_port parameters in the configuration file, and set the parameter DiscoverByShowSlaveHosts to true in the orch configuration file.

Pay attention

Report_port

Report_port can not be configured in the mysql configuration file, because report_port is set to the port of slave by default.

The default value for this option is the port number actually used by the slave. This is also the default value displayed by SHOW SLAVE HOSTS.

DiscoverByShowSlaveHosts is set to false

In this case, orch discovers the slave library through information_schema.processlist. If the port of slave is different from that of master, orch assumes that the slave library listens on the same port as the master database, so the slave cannot be automatically discovered by orch and needs to be discovered manually:

Command line: orchestrator-client-b hjj:hjj-c discover-I 10.10.30.230purl 3307

Web interface: clusters/discover

In the actual production environment, it is possible that the master and slave ports are not the same, so DiscoverByShowSlaveHosts cannot be false.

DiscoverByShowSlaveHosts is set to true

If you do not use the default port 3306, for example, slave uses port 3308, and then the report_host parameter is not configured in the mysql configuration file, orch will first try to discover the slave library through show slave hosts, but will report an error, and then use processlist to find the slave library. At this time, orch assumes that the listening port from the library is the same as that of the master library (the information of port obtained from show slave hosts will not be used, because port cannot correspond to host without setting report_host). If port 3306 is used by the master library, the slave cannot be found automatically.

# # here my master is 10.10.30.230 3307, and no report_host is set from the slave library.

/ / show slave hosts error message is as follows

2019-10-29 17:37:18 ERROR ReadTopologyInstance (10.10.30.230) show slave hosts: ReadTopologyInstance (10.10.30.230) 'show slave hosts' returned row with:

/ / shows that 10.10.30.249 3307 cannot be connected, indicating that the slave database is using the same port as the master database through processlist.

2019-10-29 17:37:24 ERROR dial tcp 10.10.30.249 3307: connect: connection refused

At this point, manual discovery is required:

Command line: orchestrator-client-b hjj:hjj-c discover-I 10.10.30.249 discover 3306

Web interface: clusters/discover

Conclusion theory

To sum up, we need to set DiscoverByShowSlaveHosts to true and at least set the correct report_host in the mysql configuration file.

Reference article

Https://github.com/github/orchestrator/blob/master/docs/supported-topologies-and-versions.md

Http://code.openark.org/blog/mysql/the-importance-of-report_host-report_port

| | author profile |

Han Jie Walk Senior Database engineer of Science and Technology

Focus on MySQL database for three years, proficient in MySQL architecture, database optimization, trouble shooting. Serve many bank customers, familiar with banking business and different database architecture scenarios under the system. Familiar with the principle of MySQL master-slave replication and various high availability scenarios for applications.

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.