简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Heim > Datenbank > MySQL-Tutorial > Hauptteil

Automatisches MySQL-Failover-Tool – mysqlfailover

黄舟
Freigeben： 2017-02-13 11:17:11
Original
2099 Leute haben es durchsucht

mysqlfailover ist ein wichtiger Hochverfügbarkeitsbefehl, der im MySQL-Dienstprogramm-Toolkit enthalten ist und zur Durchführung einer Zustandserkennung der Master-Slave-Replikationsarchitektur und zur Implementierung eines automatischen Failovers verwendet wird. Es erkennt regelmäßig in bestimmten Abständen den Gesundheitszustand jedes Knotens. Sobald festgestellt wird, dass der Master-Knoten nicht verfügbar ist, löst es Failover-bezogene Aktionen aus und führt automatisch ein Failover auf den aktuell besten Slave-Server durch. Gleichzeitig verweisen andere Slave-Knoten in der gesamten Master-Slave-Architektur auf den neuen Master-Knoten, wodurch die Aktualisierung der Master-Slave-Topologie automatisch abgeschlossen wird.

Aufwärmen zu verwandten Wissenspunkten
Gtid-Master-Slave basierend auf mysqldump erstellen
MySQL GTID-Fehlerbehandlungszusammenfassung
MySQL GTID-Master-Slave-Replikation konfigurieren
Verwenden Sie mysqldump, um die Datenbank zu exportieren

Erstens: MySQL-Failover-Funktionen 

    持续监控主从主从拓扑结构健康状况，当主节点不可用时，触发自动故障转移
    支持GTID全局事务标识符，传统主从模式不支持
    支持设置故障转移首选及备选节点，支持投票选举方式选择新的主节点以及仅监测模式(不切换主从)
    支持自定义时间监测间隔
    支持交互模式以及守护进程的模式开启mysqlfailover
    支持在切换前或切换后执行指定的脚本
    支持操作记录到日志不同的粒度以及日志老化
Nach dem Login kopieren

2. MySQL-Failover-Anforderungen 

    主从需要开启GTID模式(mysql 应使用5.6.5以上版本)    
    所有的slave端需要配置以下参数，建议主库也添加(切换后主从模式变化)
            report-host
            report-port
            master-info-repository=TABLE
            relay-log-info-repository=TABLE
    权限(mysqlfailover工具检测及切换期间需要,主从都需要)
            SHOW SLAVE STATUS
            SHOW MASTER STATUS
            STOP SLAVE, START SLAVE, WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS, CHANGE MASTER TO
            REPLICATE SLAVE
            SUPER, GRANT OPTION, RELOAD, DROP, CREATE, INSERT ，SELECT权限
Nach dem Login kopieren

3. Einige wichtige Parameter 

    --failover-mode(转移模式)
        auto：执行故障自动转移到第一候选人。如果没有从可行的，继续从从列表中找到一个可行的候选者。
          如果没有从被认为是一个可行的候选者，该程序将生成错误并退出。一旦候选者被发现，该程序将进行故障切换到最佳从上。
          该命令测试每个候选从的先决条件。一旦候选从被选中，其他从作为它的从，收集其他从的任何事务交易。该方式，确保候选者是最新的从
        elect：这种模式与aoto一样的，除了如果在指定的候选从列表中没有可行的，不检测剩余的从和产生错误并退出。
        fail: 这种模式会产生一个错误，当主失败后不会进行故障转移。这种模式被用来只进行定期健康监测不进行故障切换。

    --interval
            选项来定义检测主状态和产生健康报告的时间间隔，缺省为15s，最小间隔为5s

    --master=MASTER
            主服务器连接配置
    --slaves=SLAVES
            从服务器连接配置
    --candidates=CANDIDATES
            候选服务器连接配置

            以上3个连接配置值支持是使用下列方式，多个值以逗号分割
            <user>[:<password>]@<host>[:<port>][:<socket>] 
            <login-path>[:<port>][:<socket>]
            <config-path>[<[group]>]    

    --discover-slaves-login=DISCOVER
            基于主服务器用户密码查询当前所有注册到主服务器的从库端
            使用<user>[:<password>] or <login-path>

    --ping=PING  
             Number of ping attempts for detecting downed server.     
        侦测服务器宕机检测，缺省为3s

    --force   override the registration check on master for multiple
          instances of the console monitoring the same master.
        当控制台启动时，主的主机名和端口将被插入一个特殊的表来跟踪记录哪些实例与主联系 
        在启动时，如果行匹配这些值，控制台无法启动。如果使用--force选项，该行将被删除                                  

    --daemon(使用守护进程方式，如未指定，则为交互方式)
            start    启动守护进程。需要--log选项。
            stop     停止守护进程。如果有指定--pidfile选项，该值需要与start指定的一致。
            restart  重新启动守护进程。如果有指定--pidfile选项，该值需要与start指定的一致。
            nodetach 启动守护进程，但是不会从控制台分离进程。需要--log选项。

    --log=<log_file> 
            指定日志文件

    --log-age 
            选项用于指定日志存放天数，默认是7天。旧的日志自动轮滚掉

    --exec-before
            故障转移命令之前执行外部脚本
            脚本的返回码来确定是否成功执行，0表示成功，非0表示失败并返回错误信息

    --exec-after
            故障转移命令之后执行外部脚本
            脚本的返回码来确定是否成功执行，0表示成功，非0表示失败并返回错误信息

    -p, --pedantic        fail if some inconsistencies are found (e.g. errant
                            transactions on slaves).            
            在检查期间如果发现有不一致的情况(从错误的事务或SQL线程错误)阻止故障转移。
Nach dem Login kopieren

4. GTID erstellen Master-Slave

1. Demo-Umgebung

# more /etc/redhat-release CentOS release 6.7 (Final)# more /etc/hosts192.168.1.233 node233.edq.com node233  ###用作主节点192.168.1.245 node245.edq.com node245  ###用作从节点Slave1192.168.1.247 node247.edq.com node247  ###用作从节点Slave2# mysql -Vmysql  Ver 14.14 Distrib 5.6.30, for linux-glibc2.5 (x86_64) using  EditLine wrapper# mysqlfailover --versionMySQL Utilities mysqlfailover version 1.6.4 License type: GPLv2
Nach dem Login kopieren

2. Master-Slave-Knotenkonfiguration

======================192.168.1.233 Master========================
[mysql]#prompt= (\u@\h)[\d]>prompt= (\u@192.168.1.233)[\d]>user=rootpassword=pass

[mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESbasedir = /usr/local/mysqldatadir = /dataserver_id=233gtid_mode=onenforce_gtid_consistency=on#binloglog_bin=node233-binlog
log-slave-updates=1binlog_format=rowreport_host=192.168.1.233report_port=3306
master-info-repository = TABLE
relay-log-info-repository = TABLE#relay logskip_slave_start=1

======================192.168.1.245 Slave1========================
[mysql]prompt= (\u@192.168.1.245)[\d]>user=rootpassword=pass

[mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESbasedir = /usr/local/mysqldatadir = /dataserver_id=245                
gtid_mode=on                 
enforce_gtid_consistency=on  

log_bin=node245-binlog
log-slave-updates=1    
binlog_format=row            
report_host=192.168.1.245report_port=3306
master-info-repository = TABLE
relay-log-info-repository = TABLEskip_slave_start=1     

======================192.168.1.247 Slave2========================
[mysql]prompt= (\u@192.168.1.247)[\d]>user=rootpassword=pass

[mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESbasedir = /usr/local/mysqldatadir = /datauser = mysql 

#Author : Leshami#Blog :  http://www.php.cn/server_id=247gtid_mode=onenforce_gtid_consistency=onlog_bin=node247-binlog    
log-slave-updates=1binlog_format=rowreport_host=192.168.1.247report_port=3306
master-info-repository = TABLE
relay-log-info-repository = TABLEskip_slave_start=1
Nach dem Login kopieren

3 >

--创建主从复制(repl)及切换账户(failover)
--以下mysql提示符下Master即代表1.233节点执行，Slave1代表1.245上执行，Slave2代表1.247上执行
(root@Master)[mysql]>grant all privileges on *.* to &#39;failover&#39;@&#39;%&#39; identified by &#39;pass&#39; with grant option;

(root@Master)[mysql]>grant replication slave on *.* to &#39;repl&#39;@&#39;%&#39; identified by &#39;123456&#39;;

(root@Slave1)[(none)]>grant all privileges on *.* to &#39;failover&#39;@&#39;%&#39; identified by &#39;pass&#39; with grant option;

(root@Slave1)[(none)]>grant replication slave on *.* to &#39;repl&#39;@&#39;%&#39; identified by &#39;123456&#39;;

(root@Slave2)[(none)]>grant all privileges on *.* to &#39;failover&#39;@&#39;%&#39; identified by &#39;pass&#39; with grant option;

(root@Slave2)[(none)]>grant replication slave on *.* to &#39;repl&#39;@&#39;%&#39; identified by &#39;123456&#39;;

--主节点上执行相应的操作
[root@node233 ~]# mysql -uroot -ppass -e "create database testdb;create database tempdb"[root@node233 ~]# mysql -uroot -ppass -e "create table testdb.repl(id int,ename varchar(50))"[root@node233 ~]# mysql -uroot -ppass -e "insert into testdb.repl values(1,&#39;leshami&#39;)"[root@node233 ~]# mysql -uroot -ppass -e "select * from testdb.repl"                  +------+---------+
| id   | ename   |
+------+---------+
|    1 | leshami |
+------+---------+
Nach dem Login kopieren
4. Daten synchronisieren Gehen Sie zum Slave-Knoten und aktivieren Sie die Synchronisierung 

[root@node233 ~]# mysqldump --all-databases --single-transaction --triggers --routines --events \
> --host=localhost --port=3306 --user=root --password=pass >/tmp/alldb.sql 

[root@node233 ~]# scp /tmp/alldb.sql 192.168.1.245:/tmp
[root@node233 ~]# scp /tmp/alldb.sql 192.168.1.247:/tmp

(root@Slave1)[testdb]>reset master;

(root@Slave1)[(none)]>source /tmp/alldb.sql

(root@Slave1)[testdb]>CHANGE MASTER TO  
    -> MASTER_HOST=&#39;192.168.1.233&#39;,        -> MASTER_USER=&#39;rpl&#39;,        -> MASTER_PASSWORD=&#39;rpl&#39;,        -> MASTER_PORT=3306,        -> MASTER_AUTO_POSITION = 1;Query OK, 0 rows affected, 2 warnings (0.06 sec)

(root@Slave1)[testdb]>start slave;

(root@Slave2)[(none)]>reset master;

(root@Slave2)[(none)]>source /tmp/alldb.sql

(root@Slave2)[testdb]>CHANGE MASTER TO  
    -> MASTER_HOST=&#39;192.168.1.233&#39;,        -> MASTER_USER=&#39;rpl&#39;,        -> MASTER_PASSWORD=&#39;rpl&#39;,        -> MASTER_PORT=3306,        -> MASTER_AUTO_POSITION = 1;Query OK, 0 rows affected, 2 warnings (0.02 sec)

(root@Slave2)[testdb]>start slave;

--分别在245及247节点上验证
[root@node245 mysql]# mysql -uroot -p -e "select * from testdb.repl"Enter password: 
+------+---------+| id   | ename   |
+------+---------+|    1 | leshami |
+------+---------+[root@node247 mysql]# mysql -uroot -p -e "select * from testdb.repl"Enter password: 
+------+---------+| id   | ename   |
+------+---------+|    1 | leshami |
+------+---------+--使用mysqlrplshow查看主从结构
[root@node233 ~]# mysqlrplshow --master=failover:pass@&#39;192.168.1.233&#39;:3306 --discover-slaves-login=failover:pass --verbose    
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.1.233: ... connected.
# Finding slaves for master: 192.168.1.233:3306

# Replication Topology Graph
192.168.1.233:3306 (MASTER)   |   +--- 192.168.1.245:3306 [IO: Yes, SQL: Yes] - (SLAVE)   |   +--- 192.168.1.247:3306 [IO: Yes, SQL: Yes] - (SLAVE)
Nach dem Login kopieren
5. Mysqlfailover-Umschaltung

1 Master-Slave 

[root@node233 ~]# mysqlfailover --master=failover:pass@&#39;192.168.1.233&#39;:3306 --discover-slaves-login=failover:passWARNING: Using a password on the command line interface can be insecure.
# Discovering slaves for master at 192.168.1.233:3306
# Discovering slave at 192.168.1.245:3306
# Found slave: 192.168.1.245:3306
# Discovering slave at 192.168.1.247:3306
# Found slave: 192.168.1.247:3306
# Checking privileges.

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Mon Oct 17 17:42:36 2016
--如上行，此时failover模式为autoMaster Information
------------------Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB  
node233-binlog.00000  191                                       

GTID Executed Set     --已经执行的GTID
8dc97c98-9439-11e6-9968-000c29b82d0d:1-403Replication Health Status   --主从复制的健康状态
+----------------+-------+---------+--------+------------+---------+| host           | port  | role    | state  | gtid_mode  | health  |
+----------------+-------+---------+--------+------------+---------+| 192.168.1.233  | 3306  | MASTER  | UP     | ON         | OK      |
| 192.168.1.245  | 3306  | SLAVE   | UP     | ON         | OK      || 192.168.1.247  | 3306  | SLAVE   | UP     | ON         | OK      |
+----------------+-------+---------+--------+------------+---------+--输入大写G，查看GTIDMaster GTID Executed Set
+---------------------------------------------+| gtid                                        |
+---------------------------------------------+| 8dc97c98-9439-11e6-9968-000c29b82d0d:1-403  |
+---------------------------------------------+--大写U,查看UUIDUUIDs
+----------------+-------+---------+---------------------------------------+| host           | port  | role    | uuid                                  |
+----------------+-------+---------+---------------------------------------+| 192.168.1.233  | 3306  | MASTER  | 8dc97c98-9439-11e6-9968-000c29b82d0d  |
| 192.168.1.245  | 3306  | SLAVE   | 5dacc005-943a-11e6-996d-000c29328504  || 192.168.1.247  | 3306  | SLAVE   | eca3bd57-943a-11e6-9971-000c292e1642  |
+----------------+-------+---------+---------------------------------------+--输入大写Q，退出mysqlfailover
Nach dem Login kopieren
2. Daemon-Modus Failover starten

###主库端创建对象并不停插入记录实现初步模拟真实环境[root@node233 ~]# mysql -uroot -ppass -e "create table testdb.tb(userId int)"    [root@node233 ~]# mysql -uroot -ppass -e "create table tempdb.tb(userId int)"  ###使用下面的脚本分别将记录插入到tempdb以及testdb对应得表中# more insert_id.sh #/bin/shcnt=1while [ $cnt -le 10000 ]
do
        mysql -uroot -ppass -e "insert into tempdb.tb(userId) values($cnt);
          insert into testdb.tb(userId) values($cnt)"        
        let cnt=$cnt+1
           sleep 1 
        echo "Insert $cnt"done###执行shell脚本，初步模拟真实环境[root@node233 ~]# ./insert_id.shWarning: Using a password on the command line interface can be insecure.
Insert 2Warning: Using a password on the command line interface can be insecure.
Insert 3Warning: Using a password on the command line interface can be insecure.
Insert 4
                  .........                  ###一下错误部分为主节点挂掉后抛出的错误Warning: Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can&#39;t connect to local MySQL server through socket &#39;/tmp/mysql.sock&#39; (2)
Insert 164Warning: Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can&#39;t connect to local MySQL server through socket &#39;/tmp/mysql.sock&#39; (2)###以守护进程方式启动mysalfailover                  [root@node233 ~]# mysqlfailover --master=failover:pass@&#39;192.168.1.233&#39;:3306 \> --discover-slaves-login=failover:pass --log=/tmp/failover.log --daemon=startWARNING: Using a password on the command line interface can be insecure.
NOTE: Log file &#39;/tmp/failover.log&#39; does not exist. Will be created.
Starting failover daemon...

[root@node233 ~]# tail -fn 50 /tmp/failover.log2016-10-17 17:47:54 PM INFO MySQL Utilities mysqlfailover version 1.6.4.2016-10-17 17:47:54 PM INFO Server &#39;192.168.1.233:3306&#39; is using MySQL version 5.6.30-log.2016-10-17 17:47:54 PM INFO Discovering slaves for master at 192.168.1.233:33062016-10-17 17:47:54 PM INFO Discovering slave at 192.168.1.245:33062016-10-17 17:47:54 PM INFO Found slave: 192.168.1.245:33062016-10-17 17:47:54 PM INFO Server &#39;192.168.1.245:3306&#39; is using MySQL version 5.6.30-log.2016-10-17 17:47:54 PM INFO Discovering slave at 192.168.1.247:33062016-10-17 17:47:54 PM INFO Found slave: 192.168.1.247:33062016-10-17 17:47:54 PM INFO Server &#39;192.168.1.247:3306&#39; is using MySQL version 5.6.30-log.2016-10-17 17:47:54 PM INFO Checking privileges.2016-10-17 17:47:54 PM INFO Unregistering existing instances from slaves.2016-10-17 17:47:54 PM INFO Registering instance on master.2016-10-17 17:47:54 PM INFO Failover daemon started.2016-10-17 17:47:54 PM INFO Failover mode = auto.2016-10-17 17:47:57 PM INFO Master Information2016-10-17 17:47:57 PM INFO Binary Log File: node233-binlog.000003, Position: 25463, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A2016-10-17 17:47:57 PM INFO GTID Executed Set: 8dc97c98-9439-11e6-9968-000c29b82d0d:1-5112016-10-17 17:47:57 PM INFO Getting health for master: 192.168.1.233:3306.2016-10-17 17:47:57 PM INFO Health Status:2016-10-17 17:47:57 PM INFO host: 192.168.1.233, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK2016-10-17 17:47:57 PM INFO host: 192.168.1.245, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK2016-10-17 17:47:57 PM INFO host: 192.168.1.247, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK###上述部分完成主从、权限检查，成功启动mysqlfailover后输出主从健康状态，后会间隔1s持续检查主从健康状态2016-10-17 17:48:15 PM INFO Discovering slaves for master at 192.168.1.233:33062016-10-17 17:48:15 PM INFO Discovering slave at 192.168.1.245:33062016-10-17 17:48:15 PM INFO Discovering slave at 192.168.1.247:33062016-10-17 17:48:15 PM INFO Master Information2016-10-17 17:48:15 PM INFO Binary Log File: node233-binlog.000003, Position: 33887, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A2016-10-17 17:48:15 PM INFO GTID Executed Set: 8dc97c98-9439-11e6-9968-000c29b82d0d:1-5472016-10-17 17:48:15 PM INFO Getting health for master: 192.168.1.233:3306.2016-10-17 17:48:15 PM INFO Health Status:2016-10-17 17:48:15 PM INFO host: 192.168.1.233, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK2016-10-17 17:48:15 PM INFO host: 192.168.1.245, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK2016-10-17 17:48:15 PM INFO host: 192.168.1.247, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK###此时将主节点mysql关闭，发布命令 [root@node233 ~]# service mysqld stop2016-10-17 17:48:42 PM INFO Failed to reconnect to the master after 3 attemps. ###3次检测失败2016-10-17 17:48:42 PM CRITICAL Master is confirmed to be down or unreachable.2016-10-17 17:48:42 PM INFO Failover starting in &#39;auto&#39; mode...2016-10-17 17:48:42 PM INFO Candidate slave 192.168.1.245:3306 will become the new master.2016-10-17 17:48:42 PM INFO Checking slaves status (before failover).2016-10-17 17:48:42 PM INFO Preparing candidate for failover.   ###寻找candidate用于failover2016-10-17 17:48:42 PM INFO Creating replication user if it does not exist.2016-10-17 17:48:42 PM INFO Stopping slaves.2016-10-17 17:48:42 PM INFO Performing STOP on all slaves.2016-10-17 17:48:42 PM INFO Switching slaves to new master.2016-10-17 17:48:42 PM INFO Disconnecting new master as slave.2016-10-17 17:48:42 PM INFO Starting slaves.2016-10-17 17:48:42 PM INFO Performing START on all slaves.2016-10-17 17:48:42 PM INFO Checking slaves for errors.2016-10-17 17:48:42 PM INFO Failover complete.                 ###此处failover完成2016-10-17 17:48:42 PM INFO Discovering slaves for master at 192.168.1.245:33062016-10-17 17:48:47 PM INFO Unregistering existing instances from slaves.2016-10-17 17:48:47 PM INFO Registering instance on new master 192.168.1.245:3306.2016-10-17 17:48:48 PM INFO Master Information                 ###后续部分为持续的主从状态监测2016-10-17 17:48:48 PM INFO Binary Log File: node245-binlog.000002, Position: 41173, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A2016-10-17 17:48:48 PM INFO GTID Executed Set: 5dacc005-943a-11e6-996d-000c29328504:1[...]2016-10-17 17:48:48 PM INFO Getting health for master: 192.168.1.245:3306.2016-10-17 17:48:48 PM INFO Health Status:2016-10-17 17:48:48 PM INFO host: 192.168.1.245, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK2016-10-17 17:49:06 PM INFO Discovering slaves for master at 192.168.1.245:33062016-10-17 17:49:06 PM INFO Discovering slave at 192.168.1.247:33062016-10-17 17:49:06 PM INFO Found slave: 192.168.1.247:33062016-10-17 17:49:06 PM INFO Server &#39;192.168.1.247:3306&#39; is using MySQL version 5.6.30-log.2016-10-17 17:49:06 PM INFO Master Information2016-10-17 17:49:06 PM INFO Binary Log File: node245-binlog.000002, Position: 41173, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A2016-10-17 17:49:06 PM INFO GTID Executed Set: 5dacc005-943a-11e6-996d-000c29328504:1[...]2016-10-17 17:49:06 PM INFO Getting health for master: 192.168.1.245:3306.2016-10-17 17:49:06 PM INFO Health Status:2016-10-17 17:49:06 PM INFO host: 192.168.1.245, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK2016-10-17 17:49:06 PM INFO host: 192.168.1.247, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK2016-10-17 17:49:24 PM INFO Discovering slaves for master at 192.168.1.245:33062016-10-17 17:49:24 PM INFO Discovering slave at 192.168.1.247:33062016-10-17 17:49:24 PM INFO Master Information2016-10-17 17:49:24 PM INFO Binary Log File: node245-binlog.000002, Position: 41173, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A2016-10-17 17:49:24 PM INFO GTID Executed Set: 5dacc005-943a-11e6-996d-000c29328504:1[...]2016-10-17 17:49:24 PM INFO Getting health for master: 192.168.1.245:3306.2016-10-17 17:49:24 PM INFO Health Status:2016-10-17 17:49:24 PM INFO host: 192.168.1.245, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK2016-10-17 17:49:24 PM INFO host: 192.168.1.247, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK
Nach dem Login kopieren
3. Überprüfen Sie das Ergebnis nach dem Wechsel

###从节点Slave2上的记录数
[root@node247 mysql]# mysql -uroot -p -e "select count(*) from testdb.tb" 
Enter password: 
+----------+
| count(*) |+----------+|       89 |
+----------+[root@node247 mysql]# mysql -uroot -p -e "select count(*) from tempdb.tb"
Enter password: 
+----------+
| count(*) |+----------+|       89 |
+----------+###验证从节点复制状态
(root@192.168.1.247)[(none)]>show slave status \G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.245                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: node245-binlog.000002          Read_Master_Log_Pos: 41173               Relay_Log_File: node247-relay-bin.000002                Relay_Log_Pos: 643        Relay_Master_Log_File: node245-binlog.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes###新主节点(原来为Slave1)上的记录数
[root@node245 mysql]# mysql -uroot -p -e "select count(*) from testdb.tb"    
Enter password: 
+----------+
| count(*) |+----------+|       89 |
+----------+[root@node245 mysql]# mysql -uroot -p -e "select count(*) from tempdb.tb"
Enter password: 
+----------+
| count(*) |+----------+|       89 |
+----------+###查看新主节点上slave主机
(root@192.168.1.245)[(none)]>show slave hosts;+-----------+---------------+------+-----------+--------------------------------------+| Server_id | Host          | Port | Master_id | Slave_UUID                           |
+-----------+---------------+------+-----------+--------------------------------------+|       247 | 192.168.1.247 | 3306 |       245 | eca3bd57-943a-11e6-9971-000c292e1642 |
+-----------+---------------+------+-----------+--------------------------------------+(root@192.168.1.245)[(none)]>show slave status \G
Empty set (0.00 sec)
Nach dem Login kopieren
6

1. Problem mit der Zugriffsverweigerung

# mysqlrplshow --master=failover:pass@192.168.1.233:3306 --discover-slaves-login=failover:pass  WARNING: Using a password on the command line interface can be insecure.# master on 192.168.1.233: ... FAILED.

ERROR: Access denied for user &#39;failover&#39;@&#39;node233.edq.com&#39; (using password: YES)

应对方案，用户需要授予基于主机名的访问权限
mysql> grant all privileges on *.* to &#39;failover&#39;@&#39;node233.edq.com&#39; identified by &#39;pass&#39;;
Nach dem Login kopieren
2. Problem mit den Konfigurationsparametern

# ERROR: User root on 192.168.1.233@3306 does not have sufficient privileges to execute the failover command 
    (required: SUPER, GRANT OPTION, REPLICATION SLAVE, SELECT, RELOAD, DROP, CREATE, INSERT).2016-10-08 16:18:20 PM CRITICAL Not enough privileges to execute command.

应对方案，用户需要授予with grant option权限
mysql> grant all privileges on *.* to &#39;root&#39;@&#39;node233.edq.com&#39; identified by &#39;pass&#39; with grant option;
Nach dem Login kopieren
4 . Mehrere Probleme beim Start des MySQL-Failover-Prozesses

[root@node233 ~]# mysqlfailover --master=root:pass@192.168.1.233:3306 --discover-slaves-login=root:passWARNING: Using a password on the command line interface can be insecure.# Discovering slaves for master at 192.168.1.233:3306# Discovering slave at 192.168.1.245:3306# Found slave: 192.168.1.245:3306# Discovering slave at 192.168.1.247:3306# Found slave: 192.168.1.247:3306# Checking privileges.2016-10-08 16:21:40 PM CRITICAL Failover requires --master-info-repository=TABLE for all slaves.ERROR: Failover requires --master-info-repository=TABLE for all slaves.

应对方案，需要在配置文件中增加上述参数，如本文之前描述
Nach dem Login kopieren

Verwandte Etiketten：
mysql
Quelle：php.cn
Vorheriger Artikel：Allgemeines MySQL-Abfrageprotokoll (Allgemeines Abfrageprotokoll) Nächster Artikel：mysqldump erstellt schnell eine spezifische Bibliotheks-Master-Slave-Architektur (GTID)
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Neueste Artikel des Autors
Aktuelle Ausgaben
Wie liste ich Daten in einem Abschnitt nach ID mithilfe der While-Schleife in PHP auf? Ich habe eine MySQL-Tabelle mit diesen Spalten: Serien-ID, Serienfarbe, Produktname. In de...
Aus 2023-11-17 20:03:03
0
1
290
Verwenden von Triggern und Einschränkungen, um die Anzahl der Datensätze in MySQL zu begrenzen Ich habe eine Tabelle namens „BFFs“, in der Benutzer-IDs und Benutzer-IDs der besten Freun...
Aus 2023-11-14 13:35:02
0
4
434
Behebung des django.db.utils.NotSupportedError-Fehlers in Django Ich erhalte eine Fehlermeldung, wenn ich ein Projekt in Django ausführe. Das Problem ist l...
Aus 2023-11-14 11:59:20
0
1
246
Rufen Sie MySQL-Zeilen mit demselben Spaltenwert ab Betrachten wir die folgende Tabelle: IDScore19521003884100573 Ich bin ein absoluter SQL-Ne...
Aus 2023-11-10 21:53:39
0
2
331
Wie veröffentliche ich SQLite-Datenbankdaten in einer Online-MySQL-Datenbank in Flutter? Wie synchronisiere ich Offline-SQLite mit der MySQL-Onlinedatenbank in Flutter? Irgendwelc...
Aus 2023-11-10 12:36:40
0
2
247
verwandte Themen
Mehr>
Beliebte Empfehlungen
Beliebte Tutorials
Mehr>
Verwandte Tutorials
Beliebte Empfehlungen
Aktuelle Kurse
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage
Über uns Haftungsausschluss Sitemap
Chinesische PHP-Website：Online-PHP-Schulung für das Gemeinwohl，Helfen Sie PHP-Lernenden, sich schnell weiterzuentwickeln！