Database Replication in MySQL - Page 2
May 18, 2004
How to start replicating - the slave server
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 *************************** Master_Host: masterhostname.co.za Master_User: slave_server Master_Port: 3306 Connect_retry: 60 Master_Log_File: master-bin.054 Read_Master_Log_Pos: 16664104 Relay_Log_File: slave-relay-bin.045 Relay_Log_Pos: 17657643 Relay_Master_Log_File: master-bin.054 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: vne Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 16664104 Relay_log_space: 17657643
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 field.
mysql> SHOW MASTER STATUS; +----------------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +----------------+----------+--------------+------------------+ | master-bin.054 | 16664104 | | | +----------------+----------+--------------+------------------+
The above 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
The SLAVE 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';
MySQL 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) replicate--wild-do-table=db_name.table_name (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)
These 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
Replication 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!