One way replication can be used both to increase robustness and speed. For robustness you have two systems and 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.
Starting in 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Note that one server could play the roles of master in one pair and slave in the other. The master server keeps a binary log of updates and an index file to binary logs to keep track of log rotation. The slave upon connecting informs the master where it left off sinse the last successfully propogated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates.
Note that if you are replicating a database, all updates to this database should be done through the master!
On older servers one can use the update log to do simple replication. See section 21.1 Database replication with update log.
MySQL internal replication uses the master-slave approach. One
server is designated as the master, while the other ( or others) as
slave(s). The master keeps a binary log of updates. See section 21.4 The binary log.
The slave connects to the master, catches up on the missed updates, and
then starts receiving updates immediately as they come to the master. If
the connection is lost, the slave will reconnect. If the master goes
down, the slave will keep trying to connect every
master-connect-retry seconds until the master comes back up and
the connection can be established. The slave keeps track of where it
left off in the replication process, so it can use the info in the case
it goes down and gets restarted later.
Below is a quick HOWTO on how to set up replication on your current system:
FILE
privilege and permission to connect from all the slaves. If the user is
only doing replication, you don't need to grant him other privileges.
LOAD TABLE FROM MASTER. Until 3.23.23, though, it has a serious
bug, and we recommend that you should not use it until you have upgraded .
my.cnf on the master add log-bin and restart it. Make
sure there are no important updates to the master between the time you
have taken the snapshot and the time master is restarted with
log-bin option
my.cnf on the slave(s):
master-host=<hostname of the master> master-user=<replication user name> master-password=<replication user password>replacting the values in <> with what is relevant to your system. Starting in version 3.23.26, you must also have on both master and slave
server-id=<some unique number between 1 and 2^32-1>
server-id must be different for each server participating in
replication
After you have done the above, the master and the slave(s) should be in sync.
Below is an explanation of what is supported and what is not:
AUTO_INCREMENT,
LAST_INSERT_ID, and TIMESTAMP values
LOAD DATA INFILE will be handled properly as long as the file
still resides on the master server at the time of update
propogation. LOAD LOCAL DATA INFILE will be skipped.
log-slave-updates enabled.
Note, however, that many queries will not work right in this kind of
setup unless your client code is written to take care of the potential
problems that can happen from updates that occur in different sequence
on different servers
.err file. You should
then connect to the slave manually, fix the cause of the error
(eg. non-existent table), and then run SLAVE START sql command (
available starting in 3.23.16, in 3.23.15 you will have to restart the
server).
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.
master-port parameter in my.cnf .
replicate-do-db directives in my.cnf or just excluse a set
of databases with replicate-ignore-db. Note that up until
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.
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.
FLUSH MASTER
and FLUSH SLAVE commands
CHANGE MASTER
TO
binlog-ignore-db
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
The table below explains the replications options in my.cnf . All
of the are available starting in 3.23.15 unless indicated otherwise.
| Option | Description |
log-bin
| Should be set on the master. Tells it to keep a binary update log.
If a parameter is specified, the log will be written to the specified
location. Note that if you give it a parameter with an extention
(eg. log-bin=/mysql/logs/replication.log ) versions up to
3.23.24 will not work right during replication if you do
FLUSH LOGS . The problem is fixed
in 3.23.25. If you are using this kind of log name, FLUSH LOGS
will be ignored on binlog. To clear the log, run FLUSH MASTER,
and do not forget to run FLUSH SLAVE on all slaves.
|
log-bin-index
| Because the user could issue FLUSH LOGS command, we need to
know which log is currently active and which ones have been rotated out
and it what sequence. This info is stored in the binary log index file.
The default is `hostname`.index . You can use this option
if you want to be a rebel.
(Set on Master, Example: log-bin-index=db.index)
|
master-host
| Master hostname or IP address for replication. If not set, the slave
thread will not be started.
(Set on Slave, Example: master-host=db-master.mycompany.com)
|
master-user
| The user the slave thread will authenticate as when connecting to
the master. The user must have FILE privilige. If the master user
is not set, user test is assumed.
(Set on Slave, Example: master-user=scott)
|
master-password
| The password the slave thread will authenticate with when connecting
to the master. If not set, empty password is assumed
(Set on Slave, Example: master-password=tiger)
|
master-port
| The port the master is listening on. If not set, the compiled setting
of MYSQL_PORT is assumed. If you have not tinkered with configure
options, this should be 3306.
(Set on Slave, Example: master-port=3306)
|
master-connect-retry
| The number of seconds the slave thread will sleep before retrying to
connect to the master in case the master goes down or the connection is lost.
Default is 60.
(Set on Slave, Example: master-connect-retry=60)
|
master-info-file
| The location of the file that remembers where we left off on the master
during the replication process. The default is master.info in the data
directory. Sasha: The only reason I see for ever changing the default
is the desire to be rebelious.
(Set on Slave, Example: master-info-file=master.info)
|
replicate-do-db
| Tells the slave thread to restrict replication to the specified database.
To specify more than one database, use the directive multiple times, once for
each database. Note that this will only work if you do not use cross-database
queries such as UPDATE some_db.some_table SET foo='bar' while having
selected a different or no database.
(Set on Slave, Example: replicate-do-db=some_db)
|
replicate-ignore-db
| Tells the slave thread to not replicate to the specified database. To
specify more than one database to ignore, use the directive multiple times,
once for each database. You must not use cross database updates for this
option.
(Set on Slave, Example: replicate-ignore-db=some_db)
|
sql-bin-update-same
| If set, setting SQL_LOG_BIN to a value will automatically set
SQL_LOG_UPDATE to the same value and vice versa.
(Set on Master, Example: sql-bin-update-same)
|
log-slave-updates
| Tells the slave to log the updates from the slave thread to the binary
log. Off by default. You will need to turn it on if you plan to daisy-chain
the slaves
(Set on Slave, Example: log-slave-updates)
|
binlog-do-db
| Tells the master it should log updates for the specified database, and
exclude all others not explicitly mentioned.
(Set on Master, Example: binlog-do-db=some_database)
|
binlog-ignore-db
| Tells the master that updates to the given database should not be logged
to the binary log
(Set on Master, Example: binlog-ignore-db=some_database)
|
replicate-rewrite-db
| Tells the slave to apply updates to a database with a different
name than the original ( Set on Slave, Example:
replicate-rewrite-db=master_db_name->slave_db_name
|
skip-slave-start
| Tells the slave server not to start the slave on the startup.
The user can start it later with SLAVE START
|
server-id
| Sets the unique replicaiton numeric server id. You should pick one to assign.
The range is from 1 to 2^32-1. (Set on both Master and
Slave. Example: server-id=3)
|
Replication can be controlled through the SQL interface. Below is the summary of commands:
| Command | Description |
SLAVE START
| Starts the slave thread. (Slave) |
SLAVE STOP
| Stops the slave thread. (Slave) |
SET SQL_LOG_BIN=0
| Disables update logging (Master) |
SET SQL_LOG_BIN=1
| Re-enable update logging (Master) |
FLUSH MASTER
| Deletes all binary logs listed in the index file, resetting the binlog index file to be empty. (Master) |
FLUSH SLAVE
| Makes the slave forget its replication position in the master logs. (Slave) |
LOAD TABLE tblname FROM MASTER
| Downloads a copy of the table from master to the slave. (Slave) |
CHANGE MASTER TO master_def_list
| Changes the master parameters to the values specified in
master_def_list and restarts the slave thread. master_def_list
is a comma-separated list of master_def where master_def is
one of the following: MASTER_HOST, MASTER_USER,
MASTER_PASSWORD, MASTER_PORT, MASTER_CONNECT_RETRY,
MASTER_LOG_FILE, MASTER_LOG_POS. Example:
CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306;You only need to specify the values that need to be changed. The values that you omit will stay the same with the exception of when you change the host or the port. In that case, the slave will assume that since you are connecting to a different host or a different port, the master is different, therefore, the old values of log and position are not applicable anymore, and will automatically be reset to an empty string and 0, respectively (the start values). Note that if you restart the slave, it will remember its last master. If this is not desirable, you should delete the `master.info' file before restarting, and the slave will read its master from my.cnf or the
command line. (Slave)
|
SHOW MASTER STATUS
| Provides status info on the binlog of the master. (Master) |
SHOW SLAVE STATUS
| Provides status info on essential parameters of the slave thread. (Slave) |
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 the following way:
pthread_cond_wait(),
from which we can be woken up either by an update or a kill
Binlog_dump 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 3.23.26 and later versions.
In 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: 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.
We plan to make post 3.23.26 versions to be backwards compatible for replication down to 3.23.26, so upgrade should be just a matter of plug and play. Of course, as one joke goes, plug and play works usually only 50% of the time - just the plug part. We hope to do much better than that, though.
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 propogates 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 propogated. 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.
Until we implement server_id variable, you cannot have more than
two servers in a co-master replication relationship, and you must
run mysqld without log-slave-updates (default) to avoid
infinite update loops.
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.
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 awlays 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 application 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 benefitial 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 consist 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 to 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 infitine network bandwidth, and neglected several other factors that could turn out to be signficant 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:
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:
CHANGE MASTER TO command
nsupdate to dynamically update your DNS
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; FLUSH 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 intergrating an automatic master election system into MySQL, but until it is ready, you will have to create your own monitoring tools .
Go to the first, previous, next, last section, table of contents.