Replication Basic

Setting up a server as master
The steps that are needed to configure a server to be a master are:
1. Add log­bin and server­id options to my.cnf file
[mysqld]
server­id = 1
log­bin = master­bin.log

2. Start server and connect a client to the server
./mysqld ­­defaults­file=master.cnf
./mysql ­­defaults­file=master.cnf ­uroot

3. Add a replication user
master> CREATE USER repl_user@localhost

4. Give the replication user REPLICATION SLAVE privileges
master> GRANT REPLICATION SLAVE ON *.*
­> TO repl_user@localhost IDENTIFIED BY ‘xyzzy’;

Setting up a server as slave
To set up a server to act as a slave, the following steps have to be done:
1. Add configuration options for the relay log to the configuration file
[mysqld]
relay­log­index = slave­relay­bin.index
relay­log = slave­relay­bin

2. Direct the slave server to a master server
./mysqld ­­defaults­file=slave.cnf

3. Start the slave
./mysqld ­­defaults­file=slave.cnf
slave> CHANGE MASTER TO
­> MASTER_HOST = 'localhost',
­> MASTER_PORT = 12000,
­> MASTER_USER = 'repl_user',
­> MASTER_PASSWORD = 'xyzzy';
slave> START SLAVE;

4. Test that replication works
create a table and add some content on master,and select from slave

5. Check what hosts are connected to a master using SHOW SLAVE HOSTS

The binary log
how replication is done using the binary log.
1. Get a list of the binary logs on the master
2. Investigate(查看) contents of the binary log
3. What is the difference between the binary log formats?

Each binary log file consists of a sequence of event
If the binary log file active, there is no rotate event written last (yet),the header event indicates that this binary log file is not yet closed.
Whenever the binary logs are rotated, a rotate event is written last in the binary log, the binary
log is marked as complete in the header event, and a new binary log file is created and a
format description log event is written to it.


master> SHOW BINARY LOGS;

master> SHOW BINLOG EVENTS;
can be purged either by number or by date
mysql> PURGE BINARY LOGS TO 'master­bin.000002';
to purge all binary log files except the current one
mysql> PURGE BINARY LOGS BEFORE NOW();

mysql> set @id=100;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into stu values(@id);
Query OK, 1 row affected (0.06 sec)

mysql> select * from stu;
+------+
| id |
+------+
| 100 |
+------+
1 row in set (0.00 sec)

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
| mysql-bin.000006 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.26-rc-log, Binlog ver: 4 |
| mysql-bin.000006 | 106 | Query | 1 | 192 | use `etl`; create table stu(id int) |
| mysql-bin.000006 | 192 | User var | 1 | 235 | @`id`=100 |
| mysql-bin.000006 | 235 | Query | 1 | 324 | use `etl`; insert into stu values(@id) |
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
4 rows in set (0.00 sec)

Using mysqlbinlog
../bin/mysqlbinlog mysql-bin.000006

Replication files on slave
The most important files on the slave are the relay log files,The relay log is written by the slave I/O thread,and read by the slave SQL thread.
containing information about the progress of replication;
the files are master.info, and relay­log.info.
master.info:hold information about the master being replicated from and how much of the master binary log that has been replicated.
relay­log.info:keep track of how much of the binary log has been applied.
In this file, the figures give what position in the relay log that has been applied, and
what position and the master binary log that it corresponds to.
slave­relay­bin.000001 # Relay log name
856 # Relay log pos
master­bin.00001 # Master log name
802 # Master log pos

Basic replication use scenarios
1. Redundancy, or high-availability
2. Load balancing reads
3. Offline processing to avoid stopping the master

Using a slave for
taking a backup
reporting or analysis
read scale-out

Adding new slaves
we clone an existing slave by taking a backup of the slave, restore the
backup on the new slave, and then start the slave replicating from the position that
corresponds to the position in the binary log that the backup corresponds to.

How to clone a slave
decide on a point in the master binary log where we want to stop the slave and use the START SLAVE UNTIL command to have the slave stop exactly at a specified position.
The easiest way of getting a position in the master binary log
FLUSH TABLES WITH READ LOCK on the master to flush all tables and add a
read lock to the tables of the database,After we have that position, we can use the START
SLAVE UNTIL to have the slave stop at the right position..
The steps that has to be done to get a backup and a binlog position for what the backup
corresponds to are:
1. Stop slave
slave> STOP SLAVE;
2. Flush tables on master with a read lock
master> FLUSH TABLES WITH READ LOCK;
3. Get master position
master> SHOW MASTER STATUS;
+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­+
| master­bin.000003 | 742 | | |
+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­+
4. Unlock tables to let the master start running again
master> UNLOCK TABLES;
5. Start the slave to run until the binlog position from point 3
slave> START SLAVE UNTIL
­> MASTER_LOG_FILE='master­bin.000003',
­> MASTER_LOG_POS=742;
Query OK, 0 rows affected (0.01 sec)
6. Wait for the slave to stop
slave> DO MASTER_POS_WAIT('master­bin.000003', 742);
7. Take a backup of the slave and take a note of the binary log file and position that it corresponds to
$ cat >slave/data/binlog_pos.dat
master­bin.000003
742
$ tar cxf slave­backup.tar.gz slave/data
8. Start the slave again
slave> START SLAVE;

Creating the new slave
$ ./mysqld ­­defaults­file=slave2.cnf
$ tar zxf slave­backup.tar.gz slave2/data
$ cat slave2/data/binlog_pos.dat
$ ./mysql ­­defaults­file=slave1.cnf ­uroot
slave2> CHANGE MASTER TO
­> MASTER_HOST = 127.0.0.1,
­> MASTER_PORT = 12000,
­> MASTER_USER = 'repl_user',
­> MASTER_PASSWORD = 'xyzzy',
­> MASTER_LOG_FILE = 'master­bin.000003',
­> MASTER_LOG_POS = 742;
slave2> START SLAVE;

SHOW SLAVE STATUS

Replication for high-availability
Dual masters
[mysqld]
...
log­-slave­-updates

A commonly used but incorrect way to stop the slave
we assume that the master is under heavy use, and there are writes going on all the time

save_master_pos;
sync_with_master;
slave> DO MASTER_POS_WAIT('master­bin.000001', 440);

master> SHOW MASTER STATUS;
slave> DO MASTER_POS_WAIT(...);
slave> STOP SLAVE;
$ tar zcf slave­backup.tar.gz slave/data
slave> START SLAVE;
The problem is that the slave keeps running after the wait, which means
that there potentially can be several inserts done into the binary log between that statement
and stopping the slave.