11 Replication in MySQL

This chapter describes the various replication features in MySQL. It serves as a reference to the options available with replication. You will be introduced to replication and learn how to implement it. Towards the end, there are some frequently asked questions and descriptions of problems and how to solve them.

11.1 Introduction

One way replication can be used is to increase both robustness and speed. For robustness you can have two systems and can switch to the backup if you have problems with the master. The extra speed is achieved by sending a part of the non-updating queries to the replica server. Of course this only works if non-updating queries dominate, but that is the normal case.

バージョン 3.23.15 から、MySQL はワンウェイ・レプリケーションを サポートしました。 1つのサーバーがマスターの役をつとめ，もう一方は、スレーブの役をつとめます。 1つのサーバーはマスターとして動くことも、他に対するスレーブとして動作することも可能です。 マスターサーバーは update のバイナリログを保持しています( 「23.4 The Binary Log」節参照.)。 また、インデックスファイルは、バイナリログのローテーションの切り出し記録を残すために保持されます。 The slave, upon connecting, informs the master where it left off since the last successfully propagated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates.

もしデータベースの複製を行なうなら、このデータベースに対する全ての更新は マスター上で行なわなくてはならないことに注意！

古いバージョンでは、単純な複製に 更新ログファイルを使用します。 「22.1 更新ログを使用したデータベースの複製」節参照.

Another benefit of using replication is that one can get live backups of the system by doing a backup on a slave instead of doing it on the master. 「22.2 データベースのバックアップ」節参照.

11.2 Replication Implementation Overview

MySQL のレプリケーションは、データベースの全ての変更(update, delete, etc)を サーバーでのバイナリログに保持し、スレーブサーバーはマスターサーバーの バイナリログから保存されているクエリを読みだし、データのコピーのために 同じクエリを実行します。

It is very important to realize that the binary log is simply a record starting from a fixed point in time (the moment you enable binary logging). マスター上でバイナリログを有効にしたその時に、 いかなるスレーブサーバーもマスターからの全てのデータのコピーが必要です。 スレーブのデータがマスターに存在するデータと違う状態でスレーブを起動したならば、 バイナリログが開始されたときに、スレーブは失敗するでしょう。

MySQL の将来のバージョン(4.0)では、新しいスレーブサーバーのために、 (できる限り、)データのスナップショットを不要にするつもりでいます。 ロックをしなくても live backyup を通してスレーブをセットアップできるように。 しかし現時点では、スナップショットを取る間は、マスターを read lock を使って全ての書き込みから保護したり、 シャットダウンしたりする必要があります。

一旦スレーブが適切に構成され、実行していれば、スレーブは単にマスターに接続して、 更新が処理されるのを待つでしょう。 もしマスターがなくなったり、または、スレーブがマスターとの接続性を失えば、 スレーブは再接続できるまで master-connect-retry 秒毎に接続を試み、 そして、更新の監視を再開します。

Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up-to-date at any given time.

The next section explains the master/slave setup process in more detail.

11.3 HOWTO

Below is a quick description of how to set up complete replication on your current MySQL server. It assumes you want to replicate all your databases and have not configured replication before. You will need to shutdown your master server briefly to complete the steps outlined below.

1. マスターとスレーブに、最新の MySQL (同じバージョン) をインストールします。 バージョン 3.23.29 以上を使用すること。 これより前のバージョンでは、バイナリログのフォーマットが違い、また、新しい バージョンで直っているバグも含まれます。 最新のバージョンで確かめるまでは、バグレポートを送らないでください。
2. マスター上に、レプリケーション用の特別なユーザーを登録します。 このユーザーには FILE 権限が必要です。全てのスレーブからこのユーザーで 接続できるようにします。 もしユーザーがレプリケーションのみ(推奨)を行なうなら、 他の権限は与える必要は有りません。 例えば、repl という名前のユーザー(マスターにアクセス可能なユーザー)を 作成するには、次のようにします：

   GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY '<password>';
   

3. マスター上の MySQL を停止。

   mysqladmin -u root -p<password> shutdown
   

4. マスターサーバーにある全てのデータのスナップショットを取る。 (Unixでは) もっとも簡単な方法は tar を使用してデータディレクトリの アーカーブを作成することです。 The exact data directory location depends on your installation.

   tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir
   

   Windows users can use WinZip or similar software to create an archive of the data directory.
5. マスターの my.cnf ファイル中の [mysqld] セクションに、 log-bin と server-id=unique number を加えます。 ここで指定するマスターの id 番号とスレーブの id 番号は違うものでなくてはなりません。 Think of server-id as something similar to the IP address - it uniquely identifies the server instance in the community of replication partners.

   [mysqld]
   log-bin
   server-id=1
   

6. Restart MySQL on the master.
7. スレーブの my.cnf ファイルに、次を追加します：

   master-host=<hostname of the master>
   master-user=<replication user name>
   master-password=<replication user password>
   master-port=<TCP/IP port for master>
   server-id=<some unique number between 2 and 2^32-1>
   

   replacing the values in <> with what is relevant to your system. server-id には、サーバーそれぞれで違う番号にしなくてはなりません。 (同じ複製のグループ内で)。 もし server-id を指定しなければ、 master-host が無い場合には 1 に なり、それ以外の場合には 2 に自動的になります。 マスターで server-id の記入漏れがあった場合、 マスターはスレーブからの接続を拒否します。 スレーブ上での記入漏れの場合には、スレーブはマスターへの接続を拒否します。 Thus, omitting server-id is only good for backup with a binary log.
8. スレーブのデータディレクトリにデータのスナップショットをコピーします。 ファイルとディレクトリのユーザーとパーミッションは確実に正しいものに してください。 これらのファイルに対して、 MySQL を実行している アカウントで、読み書きできるようにする必要があります。
9. スレーブの再起動。

上記を行なった後、スレーブはマスターに接続し、スナップショット以後の 更新をキャッチするはずです。

もしスレーブに server-id をセットし忘れた場合、エラーログファイルに 次のようなエラーが出るでしょう：

Warning: one should set server_id to a non-0 value if master_host is set.
The server will not act as a slave.

もしマスターにセットし忘れたなら、スレーブはマスターに接続できません。

もし何かの理由でスレーブが複製できなかったなら、スレーブ上のエラーログ ファイルに、エラーメッセージが出るでしょう。

一度スレーブがレプリケーションを始めたなら、 エラーログファイルと同じディレクトリに master.info ファイルを見ることができます。 master.info ファイルはスレーブに使用され、 これは、マスターのバイナリログのいくつまで処理したかを保持しています。 このファイルを消したり編集したりしては いけません。 (あなたがやろうとしていることが確実に分かるまでは)。 そういうやむを得ない場合には、 CHANGE MASTER TO コマンドの使用の方が良いです。

11.4 Replication Features and known problems

Below is an explanation of what is supported and what is not:

• レプリケーションは AUTO_INCREMENT, LAST_INSERT_ID, TIMESTAMP 値を正しく扱います。
• 更新中の RAND() はうまく複製できません。 RAND(some_non_rand_expr) を更新には使用します。 例えば、UNIX_TIMESTAMP() を RAND()　の引数に使用するとか。
• LOAD DATA INFILE will be handled properly as long as the file still resides on the master server at the time of update propagation. LOAD LOCAL DATA INFILE will be skipped.
• ユーザー変数を使用したクエリは、(まだ) replication-safe ではありません。
• FLUSH コマンドはバイナリログに記録されません。 もちろん、スレーブに複製されません。 これは FLUSH が通常は何もデータを変更しないからです。 しかし、仮に MySQL の権限データベースを複製していて、 GRANT 文ではなくて直接 MySQL 権限テーブルを変更した場合には、 権限を反映させるために、スレーブ上で FLUSH PRIVILEGES を行なわなくては なりません。
• Temporary tables starting in 3.23.29 are replicated properly with the exception of the case when you shut down slave server ( not just slave thread), you have some temporary tables open, and the are used in subsequent updates. To deal with this problem, to shut down the slave, do SLAVE STOP, then check Slave_open_temp_tables variable to see if it is 0, then issue mysqladmin shutdown. If the number is not 0, restart the slave thread with SLAVE START and see if you have better luck next time. There will be a cleaner solution, but it has to wait until version 4.0. In earlier versions temporary tables are not being replicated properly - we recommend that you either upgrade, or execute SET SQL_LOG_BIN=0 on your clients before all queries with temp tables.
• MySQL は 1 つのマスターと複数のスレーブの構成のみをサポートします。 We will in 4.x add a voting algorithm to automatically change master if something goes wrong with the current master. We will also introduce 'agent' processes to help doing load balancing by sending select queries to different slaves.
• バージョン 3.23.26 から、log-slave-updates を有効にすることで、 マスターとスレーブの関係を輪っか状にすることが可能です。 しかし、多くのクエリは、この種のセットアップでは正しく動作しません。 違うシーケンス、違うサーバーで行なわれる更新によって引き起こる 潜在的な問題に対応するように、クライアントコードを注意して書かない限りは。 これは以下のようなセットアップを意味します：

  A -> B -> C -> A
  

  このセットアップはテーブル間での更新に何も衝突が無い場合にのみ、動作します。 仮に A と C でデータを insert するとき、C にinsertするレコードのキーと 競合するようなレコードを A に insert してはいけません。 You should also not update the sam rows on two servers if the order in which the updates are applied matters. Note that the log format has changed in Version 3.23.26 so that pre-3.23.26 slaves will not be able to read it.
• もしスレーブ上でクエリがエラーになると、スレーブのスレッドは停止され、 .err ファイルにメッセージが現われます。 この場合、スレーブを手動で接続し、エラーの原因を修正し(例えば、テーブルが無いとか)、 その後、 SLAVE START SQLコマンドを実行します (このコマンドはバージョン 3.23.16 以上から)。 バージョン 3.23.15 までは、サーバーを再起動しなくてはなりません。
• If connection to the master is lost, the slave will retry immediately, and then in case of failure every master-connect-retry (default 60) seconds. Because of this, it is safe to shut down the master, and then restart it after a while. The slave will also be able to deal with network connectivity outages.
• Shutting down the slave (cleanly) is also safe, as it keeps track of where it left off. Unclean shutdowns might produce problems, especially if disk cache was not synced before the system died. Your system fault tolerance will be greatly increased if you have a good UPS.
• If the master is listening on a non-standard port, you will also need to specify this with master-port parameter in my.cnf .
• In Version 3.23.15, all of the tables and databases will be replicated. Starting in Version 3.23.16, you can restrict replication to a set of databases with replicate-do-db directives in my.cnf or just exclude a set of databases with replicate-ignore-db. Note that up until Version 3.23.23, there was a bug that did not properly deal with LOAD DATA INFILE if you did it in a database that was excluded from replication.
• Starting in Version 3.23.16, SET SQL_LOG_BIN = 0 will turn off replication (binary) logging on the master, and SET SQL_LOG_BIN = 1 will turn in back on - you must have the process privilege to do this.
• バージョン 3.23.19 から、何かがおかしくなって、残っている複製をクリーンナップして、 まったくの最初から開始したいときのために、 FLUSH MASTER と FLUSH SLAVE コマンドが用意されました。 バージョン 3.23.26 から、これらの名前を RESET MASTER と RESET SLAVE に変更しました。 互換性のために、古い FLUSH もまだ動作します。 （訳注： このコマンドは、bin log を 001 からに強制してしまうため、 場合によっては複製をおかしくすることも有る。使用には注意。）
• Starting in Version 3.23.21, you can use LOAD TABLE FROM MASTER for network backup and to set up replication initially. We have recently received a number of bug reports concerning it that we are investigating, so we recommend that you use it only in testing until we make it more stable.
• Starting in Version 3.23.23, you can change masters and adjust log position with CHANGE MASTER TO.
• Starting in Version 3.23.23, you tell the master that updates in certain databases should not be logged to the binary log with binlog-ignore-db.
• Starting in Version 3.23.26, you can use replicate-rewrite-db to tell the slave to apply updates from one database on the master to the one with a different name on the slave.
• バージョン 3.23.28 から、 スレーブが実行している最中に、 古いログを取り去るために、 PURGE MASTER LOGS TO 'log-name' が使用できます。

11.5 Replication Options in my.cnf

レプリケーションを使用する場合は、我々は MySQL Version 3.23.30 以降を 推奨します。 これより古いバージョンでも動きますが、古い物はバグがあったり機能が無かったりします。

マスターとスレーブの両方に、 server-id オプションが必要です。 これは一意のレプリケーションid をセットします。 マスター、スレーブそれぞれのために、一意な値を、1 から 2^32-1 までの間から 選ばねばなりません。 Example: server-id=3

The following table has the options you can use for the MASTER:

The following table has the options you can use for the SLAVE:

11.6 SQL Commands Related to Replication

Replication can be controlled through the SQL interface. Below is the summary of commands:

CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4;

PURGE MASTER LOGS TO 'mysql-bin.010'

11.7 Replication FAQ

Q: Why do I sometimes see more than one Binlog_Dump thread on the master after I have restarted the slave?

A: Binlog_Dump is a continuous process that is handled by the server in the following way:

• Catch up on the updates.
• Once there are no more updates left, go into pthread_cond_wait(), from which we can be awakened either by an update or a kill.
• On wake up, check the reason. If we are not supposed to die, continue the Binlog_dump loop.
• If there is some fatal error, such as detecting a dead client, terminate the loop.

So if the slave thread stops on the slave, the corresponding Binlog_Dump thread on the master will not notice it until after at least one update to the master (or a kill), which is needed to wake it up from pthread_cond_wait(). In the meantime, the slave could have opened another connection, which resulted in another Binlog_Dump thread.

The above problem should not be present in Version 3.23.26 and later versions. In Version 3.23.26 we added server-id to each replication server, and now all the old zombie threads are killed on the master when a new replication thread connects from the same slave

Q: replication log をどうやってローテートするの?

A: バージョン 3.23.28 からでは、PURGE MASTER LOGS TO コマンド を使用するべきです。 実行前には、どのログが消せれるか確認し、場合によっては バックアップを取っておきます。 In earlier versions the process is much more painful, and cannot be safely done without stopping all the slaves in the case that you plan to re-use log names. You will need to stop the slave threads, edit the binary log index file, delete all the old logs, restart the master, start slave threads, and then remove the old log files.

Q: How do I upgrade on a hot replication setup?

A: If you are upgrading pre-3.23.26 versions, you should just lock the master tables, let the slave catch up, then run FLUSH MASTER on the master, and FLUSH SLAVE on the slave to reset the logs, then restart new versions of the master and the slave. Note that the slave can stay down for some time - since the master is logging all the updates, the slave will be able to catch up once it is up and can connect.

After 3.23.26, we have locked the replication protocol for modifications, so you can upgrade masters and slave on the fly to a newer 3.23 version and you can have different versions of MySQL running on the slave and the master, as long as they are both newer than 3.23.26.

Q: What issues should I be aware of when setting up two-way replication?

A: MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In in other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that will make the update of client A work differently than it did on co-master 1. Thus when the update of client A will make it to co-master 2, it will produce tables that will be different than what you have on co-master 1, even after all the updates from co-master 2 have also propagated. So you should not co-chain two servers in a two-way replication relationship, unless you are sure that you updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.

You must also realize that two-way replication actually does not improve performance very much, if at all, as far as updates are concerned. Both servers need to do the same amount of updates each, as you would have one server do. The only difference is that there will be a little less lock contention, because the updates originating on another server will be serialized in one slave thread. This benefit, though, might be offset by network delays.

Q: How can I use replication to improve performance of my system?

A: You should set up one server as the master, and direct all writes to it, and configure as many slaves as you have the money and rackspace for, distributing the reads among the master and the slaves. You can also start the slaves with --skip-bdb, --low-priority-updates and --delay-key-write-for-all-tables to get speed improvements for the slave. In this case the slave will use non-transactional MyISAM tables instead of BDB tables to get more speed.

Q: What should I do to prepare my client code to use performance-enhancing replication?

A: If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with the replicated setup should be very smooth and easy - just change the implementation of your database access to read from some slave or the master, and to always write to the master. If your code does not have this level of abstraction, setting up a replicated system will give you an opportunity/motivation to it clean up. You should start by creating a wrapper library /module with the following functions:

• safe_writer_connect()
• safe_reader_connect()
• safe_reader_query()
• safe_writer_query()

safe_ means that the function will take care of handling all the error conditions.

You should then convert your client code to use the wrapper library. It may be a painful and scary process at first, but it will pay off in the long run. All applications that follow the above pattern will be able to take advantage of one-master/many slaves solution. The code will be a lot easier to maintain, and adding troubleshooting options will be trivial. You will just need to modify one or two functions, for example, to log how long each query took, or which query, among your many thousands, gave you an error. If you have written a lot of code already, you may want to automate the conversion task by using Monty's replace utility, which comes with the standard distribution of MySQL, or just write your own Perl script. Hopefully, your code follows some recognizable pattern. If not, then you are probably better off re-writing it anyway, or at least going through and manually beating it into a pattern.

Note that, of course, you can use different names for the functions. What is important is having unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.

Q: When and how much can MySQL replication improve the performance of my system?

A: MySQL replication is most beneficial for a system with frequent reads and not so frequent writes. In theory, by using a one master/many slaves setup you can scale by adding more slaves until you either run out of network bandwidth, or your update load grows to the point that the master cannot handle it.

In order to determine how many slaves you can get before the added benefits begin to level out, and how much you can improve performance of your site, you need to know your query patterns, and empirically (by benchmarking) determine the relationship between the throughput on reads (reads per second, or max_reads) and on writes max_writes) on a typical master and a typical slave. The example below will show you a rather simplified calculation of what you can get with replication for our imagined system.

Let's say our system load consists of 10% writes and 90% reads, and we have determined that max_reads = 1200 - 2 * max_writes, or in other words, our system can do 1200 reads per second with no writes, our average write is twice as slow as average read, and the relationship is linear. Let us suppose that our master and slave are of the same capacity, and we have N slaves and 1 master. Then we have for each server (master or slave):

reads = 1200 - 2 * writes (from bencmarks)

reads = 9* writes / (N + 1) (reads split, but writes go to all servers)

9*writes/(N+1) + 2 * writes = 1200

writes = 1200/(2 + 9/(N+1)

So if N = 0, which means we have no replication, our system can handle 1200/11, about 109 writes per second (which means we will have 9 times as many reads due to the nature of our application).

If N = 1, we can get up to 184 writes per second.

If N = 8, we get up to 400.

If N = 17, 480 writes.

Eventually as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only 8 servers, we increased it almost 4 times already.

Note that our computations assumed infinite network bandwidth, and neglected several other factors that could turn out to be significant on your system. In many cases, you may not be able to make a computation similar to the one above that will accurately predict what will happen on your system if you add N replication slaves. However, answering the following questions should help you decided whether and how much, if at all, the replication will improve the performance of your system:

• What is the read/write ratio on your system?
• How much more write load can one server handle if you reduce the reads?
• How many slaves do you have bandwidth for on your network?

Q: How can I use replication to provide redundancy/high availability?

A: With the currently available features, you would have to set up a master and a slave (or several slaves), and write a script that will monitor the master to see if it is up, and instruct your applications and the slaves of the master change in case of failure. Some suggestions:

• To tell a slave to change the master use the CHANGE MASTER TO command.
• A good way to keep your applications informed where the master is by having a dynamic DNS entry for the master. With bind you can use nsupdate to dynamically update your DNS.
• You should run your slaves with the log-bin option and without log-slave-updates. This way the slave will be ready to become a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO on the other slaves. It will also help you catch spurious updates that may happen because of misconfiguration of the slave (ideally, you want to configure access rights so that no client can update the slave, except for the slave thread) combined with the bugs in your client programs (they should never update the slave directly).

We are currently working on integrating an automatic master election system into MySQL, but until it is ready, you will have to create your own monitoring tools.

11.8 Troubleshooting Replication

If you have followed the instructions, and your replication setup is not working, first eliminate the user error factor by checking the following:

• Is the master logging to the binary log? Check with SHOW MASTER STATUS. If it is, Position will be non-zero. If not, verify that you have given the master log-bin option and have set server-id.
• Is the slave running? Check with SHOW SLAVE STATUS. The answer is found in Slave_running column. If not, verify slave options and check the error log for messages.
• If the slave is running, did it establish connection with the master? Do SHOW PROCESSLIST, find the thread with system user value in User column and none in the Host column, and check the State column. If it says connecting to master, verify the privileges for the replication user on the master, master host name, your DNS setup, whether the master is actually running, whether it is reachable from the slave, and if all that seems ok, read the error logs.
• If the slave was running, but then stopped, look at SHOW SLAVE STATUS output and check the error logs. It usually happens when some query that succeeded on the master fails on the slave. This should never happen if you have taken a proper snapshot of the master, and never modify the data on the slave outside of the slave thread. If it does, it is a bug, read below on how to report it.
• If a query on that succeeded on the master refuses to run on the slave, and a full database resync ( the proper thing to do ) does not seem feasible, try the following:
  • First see if there is some stray record in the way. Understand how it got there, then delete it and run SLAVE START
  • If the above does not work or does not apply, try to understand if it would be safe to make the update manually ( if needed) and then ignore the next query from the master.
  • If you have decided you can skip the next query, do SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; to skip a query that does not use auto_increment, last_insert_id or timestamp, or SET SQL_SLAVE_SKIP_COUNTER=2; SLAVE START; otherwise
  • If you are sure the slave started out perfectly in sync with the master, and no one has updated the tables involved outside of slave thread, report the bug, so you will not have to do the above tricks again.
• Make sure you are not running into an old bug by upgrading to the most recent version.
• If all else fails, read the error logs. If they are big, grep -i slave /path/to/your-log.err on the slave. There is no generic pattern to search for on the master, as the only errors it logs are general system errors - if it can, it will send the error to the slave when things go wrong.

When you have determined that there is no user error involved, and replication still either does not work at all or is unstable, it is time to start working on a bug report. We need to get as much info as possible from you to be able to track down the bug. Please do spend some time and effort preparing a good bug report. Ideally, we would like to have a test case in the format found in mysql-test/t/rpl* directory of the source tree. If you submit a test case like that, you can expect a patch within a day or two in most cases, although, of course, you mileage may vary depending on a number of factors.

Second best option is a just program with easily configurable connection arguments for the master and the slave that will demonstrate the problem on our systems. You can write one in Perl or in C, depending on which language you know better.

If you have one of the above ways to demonstrate the bug, use mysqlbug to prepare a bug report and send it to bugs@lists.mysql.com. If you have a phantom - a problem that does occur but you cannot duplicate "at will":

• Verify that there is no user error involved. For example, if you update the slave outside of the slave thread, the data will be out of sync, and you can have unique key violations on updates, in which case the slave thread will stop and wait for you to clean up the tables manually to bring them in sync.
• Run slave with log-slave-updates and log-bin - this will keep a log of all updates on the slave.
• Save all evidence before resetting the replication. If we have no or only sketchy information, it would take us a while to track down the problem. The evidence you should collect is:
  • All binary logs on the master
  • All binary log on the slave
  • The output of SHOW MASTER STATUS on the master at the time you have discovered the problem
  • The output of SHOW SLAVE STATUS on the master at the time you have discovered the problem
  • Error logs on the master and on the slave
• Use mysqlbinlog to examine the binary logs. The following should be helpful to find the trouble query, for example:

  mysqlbinlog -j pos_from_slave_status /path/to/log_from_slave_status | head
  

Once you have collected the evidence on the phantom problem, try hard to isolate it into a separate test case first. Then report the problem to bugs@lists.mysql.com with as much info as possible.

Go to the first, previous, next, last section, table of contents.