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 get problems you witch to the backup. 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.
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. 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:
FILEprivilege and permission to connect from all the slaves
my.cnfon the master add
log-binand 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
my.cnfon 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.
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:
LOAD DATA INFILEwill be handled properly as long as the file still resides on the master server at the time of update propogation.
LOAD LOCAL DATA INFILEwill be skipped.
.errfile. You should then connect to the slave manually, fix the cause of the error (eg. non-existent table), and then run
SLAVE STARTsql 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.
my.cnfor just excluse a set of databases with
SET SQL_LOG_BIN = 0will turn off replication (binary) logging on the master, and
SET SQL_LOG_BIN = 1will turn in back on - you must have the process privilege to do this.
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||Where to set||Example|
|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.||Master|| |
| Because the user could issue ||Master|| |
|Master hostname or IP address for replication. If not set, the slave thread will not be started||Master|| |
| The user the slave thread will authenticate as when connecting to
the master. The user must have ||Slave|| |
|The password the slave thread will authenticate with when connecting to the master. If not set, empty password is assumed||Slave|| |
| The port the master is listening on. If not set, the compiled setting of
|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.||Slave|| |
|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.||Slave|| |
| Tells the slave thread to restrict replication to the specified database. To sp
cify 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
|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.||Slave|| |
| If set, setting ||Master|| |
Replication can be controlled through the SQL interface. Below is the summary of commands:
|Command||Description||Where to run|
|Starts the slave thread.||Slave|
|Stops the slave thread.||Slave|
|Disables update logging||Master|
|Re-enable update logging||Master|
Go to the first, previous, next, last section, table of contents.