主从配置 [英] Master Slave configuration

问题描述

我需要为 mysql 设置一个主从设置.

I need to set-up a master slave setup for mysql.

1 个主和 1 个从(复制).

1 Master and 1 Slave ( replication ).

现在这里的主要问题是:是否可以在 master 失败时将 slave 提升为 master.那么当旧的主站起来时，它现在就是从站了.

Now the main question here is : Is it possible to promote a slave as master when master fails. Then when the old master is up it will be the slave now.

推荐答案

您的问题的答案是是"，并且有 必须遵循的说明在这里.本质上，假设 Andrew 是当前的奴隶，而 Abe 是当前的主人.交换两者:

The answer to your question is "yes" and there are instructions that must be followed here. Essentially, assuming Andrew is the current slave and Abe the current master. To swap the two:

检查 Andrew 以确保 sql 线程已尽可能多地完成处理.在 mysql 提示符下，运行 SHOW PROCESSLIST.(您也可以通过使用 MySQL Administrator 查看当前连接来执行此操作.)IO 线程和 sql 线程都应列为 system_users.IO 线程应该有一个状态在失败的主事件读取后重新连接".如果sql线程完全被赶上，它的状态应该是已读取所有中继日志；等待从属 I/O 线程更新它".如果这说明了其他事情(例如处理中继日志)，那么从站仍在追赶，您需要让它运行直到追上为止.

Check Andrew to make sure that the sql-thread has finished processing as much as it can. At the mysql prompt, run SHOW PROCESSLIST. (You can also do this by using MySQL Administrator to view the current connections.) Both the IO thread and the sql thread should be listed, as system_users. The IO thread should have a state "Reconnecting after a failed master event read". If the sql thread is completely caught up, it’s state should be "Has read all relay log; waiting for the slave I/O thread to update it". If this says something else (like processing relay log), then the slave is still catching up, and you need to let it run until it’s caught up.

执行此检查的另一种方法是运行命令 SHOW SLAVE STATUS \G，然后查看中继日志和位置.然后您可以验证这确实是最新的中继日志，并使用 mysqlbinlog 实用程序来验证该位置是否是最新的.请注意，您不能在从站状态输出中使用 SECONDS_BEHIND_MASTER 变量 - 如果从站无法连接到主站，它将为空.

An alternate way to do this check would be to run the command SHOW SLAVE STATUS \G, and look at the relay log and position. You can then verify that this is indeed the latest relay log, and use the mysqlbinlog utility to verify that the position is the latest one. Note that you cannot use the SECONDS_BEHIND_MASTERvariable in the slave status output - it will be null if the slave cannot connect to the master.

一旦安德鲁被追上，重置它.在 MySQL 提示符下，运行 RESET SLAVE;.这将删除 master.info 文件，并使安德鲁摆脱束缚.

Once Andrew is caught up, reset it. At the MySQL prompt, run RESET SLAVE;. This will delete the master.info file, and free Andrew from the chains of bondage.

接下来，将所有二进制日志移动到一个临时目录.二进制日志看起来像andrew-bin.000001，其中andrew 是主机名.这一步不是绝对必要的，但我不喜欢删除日志文件.

Next, move all of the binary logs to a temporary directory. The binary logs will look like andrew-bin.000001, where andrew is the host name. This step isn’t strictly necessary, but I have an aversion to deleting log files.

现在为 Andrew 重置二进制日志.这将删除所有二进制日志，这就是我们在上一步中备份它们的原因.当我们把 Abe 带回来时，这也将使 Abe 作为奴隶连接到 Andrew 变得容易.为此，请运行 RESET MASTER；在安德鲁身上.

Now reset the binary logs for Andrew. This will remove all binary logs, which is why we backed them up in the previous step. It will also make it easy for Abe to connect as a slave to Andrew when we bring Abe back. To do this, run RESET MASTER; on Andrew.

在 Andrew 上编辑 my.cnf，以注释掉 read_only 系统变量(如果已设置).

Edit my.cnf on Andrew, to comment out the read_only system variable (if you have it set).

在 Andrew 上重启 mysql:/etc/init.d/mysql restart

Restart mysql on Andrew: /etc/init.d/mysql restart

将 masterdb.janitors.com cname 更改为指向 Andrew，而不是 Abe.此步骤要求您的应用程序指向之前的 setupmasterdb.janitors.com，而不是类似 andrew.janitors.com 的内容.

Change the masterdb.janitors.com cname to point to Andrew, instead of Abe. This step requires that your application is pointing to the previously setupmasterdb.janitors.com, instead of something like andrew.janitors.com.

Web 应用程序现在应该成功连接到作为主数据库的 Andrew.

The web application should now be succesfully connecting to Andrew as the master database.

一旦安倍康复，我们就可以把他培养成安德鲁的奴隶.第一步是在Abe上编辑my.cnf，取消read_only系统变量的注释.

Once Abe has recovered, we can bring him up as a slave to Andrew. First step is to edit the my.cnf on Abe, and uncomment the read_only system variable.

在 Abe 上启动 mysql(与上面的命令相同).请注意，这意味着不应将 mysql 设置为在机器启动时自动启动.这是一项额外的安全措施.我不知道 mysql 是否会尝试任何类型的错误恢复，例如完成由于崩溃而无法完成的事务 - 但我们不希望它做任何会使它与 Andrew 不同的事情(它可能已经略有不同，但我们对此无能为力).因此，通过在启动之前将其设置为只读，我们可以确保对数据库的唯一更新是来自新主服务器的更新.

Start mysql on Abe (same command as above). Note that this means mysql should not be set to start automatically on machine boot. This is an extra safety measure. I don’t know if mysql will attempt any type of error recovery, such as finishing transactions that it was unable to complete because of the crash - but we don’t want it to do anything that will make it any more different than Andrew (it may already be slightly different, but nothing we can do about that). So, by setting it to read only before we start it up, we make sure that the only updates to the db are those that come from the new master.

让阿贝成为安德鲁的奴隶.在 Abe 上，运行

Make Abe a slave to Andrew. On Abe, run

将 MASTER 更改为 MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_HOST='andrew.janitors.com', MASTER_LOG_FILE='andrew-bin.0000001', MASTER_LOG_POS=4;

我们知道日志文件中的确切位置，因为我们在步骤 4 中将其重置.此外，我们将 master_host 设置为 andrew.janitors.com 而不是 masterdb.janitors.com，因为我们不一定希望任何故障转移到单独的master(因为不同的master会有不同的二进制日志).

We know the exact position in the log file because we reset it back in step 4. Also, we are setting master_host to andrew.janitors.com instead ofmasterdb.janitors.com because we don’t necessarily want any failover to a separate master (because different masters will have different binary logs).

这篇关于主从配置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！