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.
 * 3) * mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.mydomain.com' IDENTIFIED BY 'password';
 * 4) Flush all the tables and block write statements by executing a FLUSH TABLES WITH READ LOCK statement:
 * 5) * mysql> FLUSH TABLES WITH READ LOCK;
 * 6) 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.
 * 7) * tar -cvf /tmp/mysql-snapshot.tar ./this_db
 * 8) 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.
 * 9) * mysql> SHOW MASTER STATUS;
 * 10) re-enable write activity on the master:
 * 11) * mysql> UNLOCK TABLES;
 * 12) Extract snapshot into slave servers data directory
 * 13) * tar -xvf /tmp/mysql-snapshot.tar
 * 14) 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.
 * 15) * /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-slave-start &
 * 16) If you made a backup of the master server's data using mysqldump, load the dump file into the slave server:
 * 17) * shell> mysql -u root -p < dump_file.sql
 * 18) Execute the following statement on the slave, replacing the option values with the actual values relevant to your system:
 * 19) * 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;
 * 20) Start the slave threads:
 * 21) * mysql> START SLAVE;

Master server configuration /etc/my.cnf:

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

Slave server configuration /etc/my.cnf:

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

LOAD DATA FROM MASTER Syntax
LOAD DATA FROM MASTER Syntax

'''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

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