[toc]

MySQL 如何确认从库的 IP

show slave hosts

1
2
3
4
5
6
7
8
show slave hosts;
mysql> show slave hosts;
+------------+---------------+------+------------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+------------+---------------+------+------------+--------------------------------------+
| 1647312172 | 192.168.11.14 | 3306 | 1647312173 | af5b1a5b-a409-11ec-b404-0050568a5583 |
+------------+---------------+------+------------+--------------------------------------+
1 row in set (0.00 sec)

主库上通过 show slave hosts 可以很直观的显示从库信息,当然包含ip了。

在从库上执行 show slave hosts 是没有结果的。

1
2
mysql> show slave hosts;
Empty set (0.00 sec)

如果不知道当前主机是主库还是从库,可以通过 show slave hosts 来判断,如果有信息说明当前是主库,同时也会显示从库的信息。如果没有信息,然后通过show slave status\G 进一步确认从库信息,同时也可以 Master_Host 看到主库的ip信息。

show process list

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show processlist;
+--------+-----------------+---------------------+------+------------------+--------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------------+---------------------+------+------------------+--------+---------------------------------------------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 405282 | Waiting on empty queue | NULL |
| 7520 | repl | 192.168.11.14:46544 | NULL | Binlog Dump GTID | 421846 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 330853 | root | 127.0.0.1:35564 | NULL | Query | 0 | starting | show processlist |
| 330857 | _ha | 192.168.11.4:60294 | NULL | Sleep | 5 | | NULL |
| 330858 | _ha | 192.168.11.4:60298 | NULL | Sleep | 5 | | NULL |
| 330859 | _ha | 192.168.11.4:60300 | NULL | Sleep | 5 | | NULL |
+--------+-----------------+---------------------+------+------------------+--------+---------------------------------------------------------------+------------------+
6 rows in set (0.00 sec)

通过在主库上执行 show processlist,可以看到 state 对应的 Master has sent all binlog to slave; waiting for more updates 的 host 就是从库的ip信息。

如果是在从库上执行 show processlist,会有 Slave has read all relay log; waiting for more updates 的进程信息,通过 show slave status\G 显示的 Master_Host: 192.168.11.15 可以确定主库的 IP 信息。

show processlist; 数据来自于 information_schema.processlist ,可以通过如下SQL 查看。

1
2
select substring_index(host, ':', 1) as Host from information_schema.processlist where
command IN ('Binlog Dump', 'Binlog Dump GTID');
1
2
3
4
5
6
7
8
9
10
mysql> select substring_index(host, ‘:’, 1) as Host from information_schema.processlist where
-> command IN (‘Binlog Dump’, ‘Binlog Dump GTID’);
+—————+
| Host |
+—————+
| 192.168.11.14 |
+—————+
1 row in set (0.00 sec)

mysql>

原文作者: liups.com

原文链接: http://liups.cn/posts/230d7b6f/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议