开始本文的前提:
1. 配好了主从架构的两台MySQL服务器

环境介绍:
在我的环境中有两台服务器:
master,192.168.230.129
slave,192.168.230.130

下面开始。
————————————————————
在MySQL的主从架构中,如果我希望在从库上获取主库的信息是很容易的,通过【show slave status\G】就可以得到。

如下:
从:

[root@slave ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.22-22-log Percona Server (GPL), Release 22, Revision f62d93c

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| binlog-slave.000002 |      154 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.230.129
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog-master.000001
          Read_Master_Log_Pos: 755
               Relay_Log_File: slave-relay-bin.000004
                Relay_Log_Pos: 324
        Relay_Master_Log_File: binlog-master.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 755
              Relay_Log_Space: 531
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 11
                  Master_UUID: 55327f4a-7631-11e9-9eea-000c2955973a
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> 

可以看到:
【Slave_IO_Running: Yes】
【Slave_SQL_Running: Yes】
【Seconds_Behind_Master: 0】
【Master_Host: 192.168.230.129】
当前,从库和主库的状态是【同步】的。

如果我们要从主库上看某个MySQL的数据库实例有几个从库的时候,可以这么操作:
Command:【show slave hosts】
如下:
主:

[root@master ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.22-22-log Percona Server (GPL), Release 22, Revision f62d93c

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| binlog-master.000001 |      755 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show slave status\G
Empty set (0.00 sec)

mysql> 
mysql> show slave hosts;
+-----------+-----------------+------+-----------+--------------------------------------+
| Server_id | Host            | Port | Master_id | Slave_UUID                           |
+-----------+-----------------+------+-----------+--------------------------------------+
|        12 | 192.168.230.130 | 3306 |        11 | c6ebbf81-7701-11e9-bb86-000c29e28a5b |
+-----------+-----------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

mysql> 

可以很清楚的看到,这个数据库的从库是【192.168.230.130】。

不过需要注意的是:默认情况下:
1. 主库上查询【show slave hosts】的时候【host】是空的
2. 只查得到【slave_uuid】

如果需要像上面我查到的那样显示,需要在从库配置相关参数:【report_host】

具体如下:

mysql> show variables like '%server%id%';
+------------------+--------------------------------------+
| Variable_name    | Value                                |
+------------------+--------------------------------------+
| pseudo_server_id | 0                                    |
| server_id        | 12                                   |
| server_id_bits   | 32                                   |
| server_uuid      | c6ebbf81-7701-11e9-bb86-000c29e28a5b |
+------------------+--------------------------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | c6ebbf81-7701-11e9-bb86-000c29e28a5b |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show variables like '%report%';
+-----------------+-----------------+
| Variable_name   | Value           |
+-----------------+-----------------+
| report_host     | 192.168.230.130 |
| report_password |                 |
| report_port     | 3306            |
| report_user     |                 |
+-----------------+-----------------+
4 rows in set (0.01 sec)

mysql> 

——————————
最后,列一下在我的主从环境中,主从两端的【my.cnf】的具体配置:

主:

[root@master ~]# cat /etc/my.cnf
[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# MySQL: Replication

# server id
server-id = 11

# Binlog
log-bin = /var/lib/mysql/binlog-master

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@master ~]# 

从:

[root@slave ~]# cat /etc/my.cnf
[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# MySQL: Replication

# server id
server-id = 12

# Binlog
log-bin = /var/lib/mysql/binlog-slave

# Report: slave
report_host = "192.168.230.130"

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@slave ~]# 

————————————————
Done。

说点什么

avatar

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

  Subscribe  
提醒