Maison > base de données > tutoriel mysql > le corps du texte

Outil de basculement automatique MySQL - mysqlfailover

黄舟
Libérer: 2017-02-13 11:17:11
original
2149 Les gens l'ont consulté

mysqlfailover est une commande de haute disponibilité importante incluse dans la boîte à outils des utilitaires mysql, qui est utilisée pour effectuer une détection de l'état de l'architecture de réplication maître-esclave et implémenter un basculement automatique. Il détectera régulièrement l'état de santé de chaque nœud à des intervalles spécifiés. Une fois qu'il aura détecté que le nœud maître est indisponible, il déclenchera des actions liées au basculement et effectuera automatiquement un basculement vers le meilleur serveur esclave du moment. Dans le même temps, d'autres nœuds esclaves de l'ensemble de l'architecture maître-esclave pointeront vers le nouveau nœud maître, complétant ainsi automatiquement la mise à jour de la topologie maître-esclave.

Échauffement des points de connaissances associés
Construire un maître-esclave gtid basé sur mysqldump
Résumé de la gestion des erreurs MySQL GTID
Configurer la réplication maître-esclave MySQL GTID
Utiliser mysqldump pour exporter la base de données

One, les fonctionnalités de mysqlfailover

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

2. Exigences de mysqlfailover

    主从需要开启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权限
Copier après la connexion

3. Quelques paramètres importants

    --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线程错误)阻止故障转移。
Copier après la connexion

4. Construction du GTID maître-esclave

1. Environnement de démonstration

# 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
Copier après la connexion

2. Configuration des paramètres du nœud maître-esclave

======================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
Copier après la connexion

3. Allumez le nœud maître-esclave<🎜. >

--创建主从复制(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 |
+------+---------+
Copier après la connexion
4. Synchroniser les données Accédez au nœud esclave et activez la synchronisation

[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)
Copier après la connexion
5. Commutation de Mysqlfailover

1 Démarrez mysqlfailover en mode non-démon pour afficher le maître-esclave

[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
Copier après la connexion
2. Mode démon Démarrer le basculement

###主库端创建对象并不停插入记录实现初步模拟真实环境[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
Copier après la connexion
3. Vérifier le résultat après le changement

###从节点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)
Copier après la connexion
6.

1. Problème d'autorisation de refus d'accès

# 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;;
Copier après la connexion
2. Problème d'autorisations insuffisantes

# 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;
Copier après la connexion
3. . Plusieurs problèmes de démarrage du processus mysqlfailover

[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.

应对方案,需要在配置文件中增加上述参数,如本文之前描述
Copier après la connexion

.
Étiquettes associées:
source:php.cn
Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Tutoriels populaires
Plus>
Derniers téléchargements
Plus>
effets Web
Code source du site Web
Matériel du site Web
Modèle frontal