How to start replicating - the slave
- Add the following to the
configuration file on the slave:
The slave user and slave password are those to which you set when you granted
REPLICATION SLAVE permission on the master. The server-id must be a unique
number, different to the master or any other slaves in the system. There are
also two other options: master-port, used if the master is running on a
non-standard port (3306 is default), and master-connect-retry, a time in
seconds for the slave to attempt to reconnect if the master goes down. 60
seconds is default.
- Restore the data from the
master, either as you would normally restore a backup, or with the statement LOAD
DATA FROM MASTER. The latter will lock the master for the duration of the
operation, which could be quite lengthy, so you may not be able to spare the
Replication in action
Once the slave has
started, replication should begin. Besides the obvious SELECT queries, you can
make sure this is working correctly with the following statements:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
This is a
mature slave that has been running a while. The master is already on the 54th
binary log. You can see if the slave is running correctly by looking at the Slave_IO_Running
and Slave_SQL_Running. The most important field is the Last_error
mysql> SHOW MASTER STATUS;
| File | Position | Binlog_do_db | Binlog_ignore_db |
| master-bin.054 | 16664104 | | |
is from a master that has been running a while. It is already on binlog 54.
Starting to replicate from a particular
point in the binary logs
If you need to force the
slave to begin at a certain point, usually when the master has been running
with an active binary log, you can do so as follows. The following starts with
the 3rd binary log, as position 420. You can find the position using mysqlbinlog.
mysql> CHANGE MASTER TO MASTER_LOG_FILE='master-bin.003' MASTER_LOG_POS=420;
Query OK, 0 rows affected (0.00 sec)
mysql> SLAVE START
START and SLAVE STOP commands are used to manually stop and start the slave.
The slave will also always stop if it comes across an error while replicating.
Removing old binary logs
On active databases, the
binary logs tend to grow quite quickly. You may have used RESET MASTER in the
past to clear them, but you cannot do this to the master while replicating! The
statement to use is PURGE MASTER LOGS. First, make sure that all slaves have
replicated to at least the log beyond which you want to remove. For example, in
our earlier example, both the slave and the master are on log master-bin.054,
so we can safely remove master-log.053 and before, as follows:
mysql> PURGE MASTER LOGS TO 'master-bin.053';
will not allow you to remove a log that the master is still using though.
Replicating specific databases only
As mentioned earlier, the
master and slave database server do not need to be entirely in sync, with all
databases and all tables from the master replicated onto the slave. By default,
the slave will replicate everything, but you can change this behavior with the
following options in the slave configuration file:
replicate-do-db=db_name (replicate this database)
replicate-ignore-db=db_name (don't replicate this database)
replicate-do-table=db_name.table_name (replicate this table)
replicate-ignore-table=db_name.table_name (don't replicate this table)
(allows wildcards, e.g db% would be all databases beginning with db)
replicate-wild-ignore-table=db_name.table_name (ignore all specified tables, with wildcards)
options can all be used multiple times in a single configuration. A couple of
other useful options:
replicate-rewrite-db=master_db->slave_db (allows you to use map databases
that use different database names on each server)
log-slave-update (writes replicated statements to the slaves binary logs)
A few complexities
shutting down a slave server (mysqladmin-shutdown, not STOP SLAVE,
make sure it has no temporary tables open (these may be needed for a statement
to be replicated). You can do this with SHOW STATUS to see the value of Slave_open_temp_tables.
This annoying feature should be fixed soon, so please check the latest
UUID(), LOAD_FILE() and CONNECTION_ID() (before MySQL 4.1.1) functions do not work
reliably on the slave (they are replicated without changes).
4.1.1, FLUSH, ANALYZE, OPTIMIZE, and REPAIR statements are not replicated. This
means that if you change permissions on the master by editing the tables
directly, you will need to manually FLUSH PRIVILEGES on the slave too.
Make sure the
slave and the master are using the same character set.
is not the salvation it seems to be at first glance, and will improve greatly
in future versions of MySQL, but it is a useful addition to a DBA's armory. I
hope that you will find replication easy to get going. Good luck!
See All Articles by Columnist Ian Gilfillan