MySQL/Replication

Summary

MySQL Replication

"MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves."

An Introduction to Database Replication in MySQL

Commands

The SHOW PROCESSLIST statement provides information that tells you what is happening on the master and on the slave regarding replication:

mysql> SHOW PROCESSLIST;

Show the slave status:

mysql> SHOW SLAVE STATUS\G

Start/stop the slave threads:

 mysql> START SLAVE;
 mysql> STOP SLAVE;

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.

Show the master status:

mysql> SHOW MASTER STATUS\G

How To Setup Replication

1. Make sure master and slave are same version (or compatible versions).
2. Create account on master that slave can connect to with REPLICATION SLAVE privilege.
   • mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.mydomain.com' IDENTIFIED BY 'password';
3. Flush all the tables and block write statements by executing a FLUSH TABLES WITH READ LOCK statement:
   • mysql> FLUSH TABLES WITH READ LOCK;
4. Take a snapshot of the data on your master server. The easiest way to create a snapshot is to use an archiving program to make a binary backup of the databases in your master's data directory.
   • tar -cvf /tmp/mysql-snapshot.tar ./this_db
5. Read the value of the current binary log name and offset on the master. They represent the replication coordinates at which the slave should begin processing new updates from the master.
   • mysql> SHOW MASTER STATUS;
6. re-enable write activity on the master:
   • mysql> UNLOCK TABLES;
7. Extract snapshot into slave servers data directory
   • tar -xvf /tmp/mysql-snapshot.tar
8. Start the slave server. If it has been replicating previously, start the slave server with the --skip-slave-start option so that it doesn't immediately try to connect to its master. You also may want to start the slave server with the --log-warnings option to get more messages in the error log about problems (for example, network or connection problems). The option is enabled by default, but aborted connections are not logged to the error log unless the option value is greater than 1.
   • /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-slave-start &
9. If you made a backup of the master server's data using mysqldump, load the dump file into the slave server:
   • shell> mysql -u root -p < dump_file.sql
10. Execute the following statement on the slave, replacing the option values with the actual values relevant to your system:
    • mysql> CHANGE MASTER TO
      -> MASTER_HOST='master_host_name',
      -> MASTER_USER='replication_user_name',
      -> MASTER_PASSWORD='replication_password',
      -> MASTER_LOG_FILE='recorded_log_file_name',
      -> MASTER_LOG_POS=recorded_log_position;
11. Start the slave threads:
    • mysql> START SLAVE;

Master server configuration /etc/my.cnf:

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

Slave server configuration /etc/my.cnf:

# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
server-id = 2
#
# The replication master for this slave - required
master-host = 10.10.100.100
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user = <user>
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
master-port = 3306
#
# binary logging - not required for slaves, but recommended
log-bin=mysql-bin
# database to replicate
replicate-do-db = cca

LOAD DATA FROM MASTER Syntax

LOAD DATA FROM MASTER Syntax [1]

This feature is deprecated. We recommend not using it anymore. It is subject to removal in a future version of MySQL.

GRANT SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'replication'@'%' IDENTIFIED BY 'password';

Binary Logs

CHANGE MASTER TO MASTER_LOG_FILE='master-bing.003' MASTER_LOG_POS=420

PURGE MASTER LOGS TO 'master-bin.053'

Other Replication Information

An introduction to replication [2]

What Replication Is Not

• Replication is not a backup policy. A mistyped DELETE statement will be replicated on the slave too, and you could end up with two, perfectly synchronized, empty databases. Replication can help protect against hardware failure though.
• Replication is not an answer to all performance problems. Although updates on the slave are more optimized than if you ran the updates normally, if you use MyISAM tables, table-locking will still occur, and databases under high-load could still struggle.
• Replication is not a guarantee that the slave will be in sync with the master at any one point in time. Even assuming the connection is always up, a busy slave may not yet have caught up with the master, so you can't simply interchange SELECT queries across master and slave servers.

Starting to replicate from a particular point in the binary logs

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

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.

See MySQL/Binary Logs