MySQL Hands-On Lab: Getting Started with MySQL Replication Sven Sandberg, Luis Soares MySQL Replication Team Presented by Ben Krug Senior MySQL Support Engineer 1
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Agenda Replication Basics Hands on Exercises to Set Up MySQL Replication More Resources
3
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics MySQL Replication Components MySQL Master Server -
Changes data
-
Logs changes (Events) into a file (Binary Log)
MySQL Slave Server
4
-
Retrieves events from the master
-
Replays the events on the slave's databases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics MySQL Replication Components: Binary Log The Binary Log -
File based log that records the changes on the master.
-
Statement or Row based format (may be intermixed).
-
Split into transactional groups.
BEGIN
5
Ev1 Ev2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
...
COMMIT
BEGIN
Insert Information Protection Policy Classification from Slide 12
Event Layout on a Binary Log File Ev1 Ev2
...
COMMIT
Replication Basics MySQL Replication Components: Binary Log Binary log files: Under mysql-bin.000001, mysql-bin.000002, … the Hood - The actual data. Binary Log
Index: mysql-bin.index - An index over the binary log files. Log coordinate: - binlog file name + event offset in the file (3.23.15+) - Global Transaction Identifiers (5.6+) 6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Binary log files
Index
Replication Basics MySQL Replication Architecture Slave
Master Session Session Session
Dump
I/O
SQL
Maste r info
Relay log info Relay log
Binary log
I/O and SQL Thread Replication Metadata is stored in files. Starting in MySQL 5.6 the Metadata can be stored in tables. 7
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Changes Propagation Asynchronous Replication (MySQL 3.23.15+) -
Transactions are committed and externalized without interaction with the replication layer.
-
Events are sent to slave after the commit operation is acknowledged.
-
Faster but vulnerable to lost updates on server crashes and inconsistency.
-
Built into the server.
Semi-synchronous Replication (MySQL 5.5+) -
8
Master commits transaction but waits until one slave acknowledges having received and stored the event before replying to the client.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Hands-On
9
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Getting MySQL Head to -
http://dev.mysql.com
... and get the latest MySQL 5.6 generic tar.gz package from there … or alternatively, you can find MySQL 5.6.6 linux generic package at
your local directory /opt/ .
10
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Getting MySQL
Create a directory for storing the binaries (e.g., under your home).
$ mkdir $HOME/rephol/ $ cd $HOME/rephol/ Unpack the package (e.g., using tar).
$ tar xfvz /opt/mysql-....tar.gz --directory=$HOME/rephol
Rename the directory for simplicity $ mv $HOME/rephol/mysql-... $HOME/rephol/mysql-5.6 11
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Setting Up MySQL $ mysql_install_db --basedir=/usr/ --datadir=$HOME/rephol/master --user=`whoami` $ mysql_install_db --basedir=/usr/ --datadir=$HOME/rephol/slave --user=`whoami`
12
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Create Two two Data Data Directories
Replication Basics Setting Up MySQL Master Server Create a defaults file called $HOME/rephol/master.cnf
Set the unique server id. [mysqld] server-id=1 log-bin=master-bin log-error=master.err port=12000 socket=/tmp/master.sock 13
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Turn ON the binary log.
Replication Basics Setting Up MySQL Master Server $ mysqld --defaults-file=$HOME/rephol/master.cnf --lc-messages-dir=/usr/share/mysql --datadir=$HOME/rephol/master/ $ mysql -u root --port 12000 --protocol=tcp --prompt='master> ' Log in to the master server. 14
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Start the master server.
Replication Basics Inspecting the Master Status master> SHOW BINARY LOGS; ... master> SHOW BINLOG EVENTS; ... master> SHOW MASTER STATUS;
15
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
What binlog files are in use? What's in the first binary log? What binary log is in use what is its position?
Replication Basics Inspecting the Master Status master> SHOW BINARY LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 120 | +-------------------+-----------+ 1 row in set (0.00 sec) +-------------------+-----+-------------+-----------+-------------+-----------------------------------------+ master> SHOW BINLOG EVENTS; | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+-------------+-----------+-------------+-----------------------------------------+ | master-bin.000001 | 4 | Format_desc | 1| 120 | Server ver: 5.6.6-m9-log, Binlog ver: 4 | +-------------------+-----+-------------+-----------+-------------+-----------------------------------------+ 1 row in set (0.00 sec) +-------------------+----------+--------------+------------------+-------------------+ |master> File SHOW | Position MASTER | Binlog_Do_DB STATUS; | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 120 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 16
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Binary Log Layout Format description
Format description
Format description
Log Events
Log Events
Log Events
Rotate
17
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Rotate
Insert Information Protection Policy Classification from Slide 12
Multiple Files. Files begin with Format Description event. Each log file ends with a Rotate event.
Replication Basics Setting Up Replication User (that the slave will connect to the master with)
We could use any user.
master> CREATE USER `rpl_user`@`localhost`; master> GRANT REPLICATION SLAVE ON *.* Better use TO `rpl_user`@`localhost` a dedicated IDENTIFIED BY 'secret'; user for connecting the slave. master> FLUSH PRIVILEGES; Needs replication grants to read any change on the master 18
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Setting Up MySQL Slave Server Create a defaults file called $HOME/rephol/slave.cnf
Set the unique server id. [mysqld] server-id=2 relay-log=slave-relay-bin log-error=slave.err port=12001 socket=/tmp/slave.sock 19
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Set the name for the relay log.
Replication Basics Starting Up The MySQL Slave Server $ mysqld --defaults-file=$HOME/rephol/slave.cnf --lc-messages-dir=/usr/share/mysql --datadir=$HOME/rephol/slave/
$ mysql -u root --port 12001 --protocol=tcp --prompt='slave> ' Log in to the slave server. 20
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Start the slave server.
Replication Basics Starting the Slave Threads. slave> CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_PORT = 12000, MASTER_USER = 'rpl_user', MASTER_PASSWORD = 'secret';
Start the slave threads.
21
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Point the slave server to the master server.
slave> START SLAVE; Insert Information Protection Policy Classification from Slide 12
Use the credentials we had granted before.
Replication Basics Inspecting the Slave Status
Inspect the slave status.
slave> SHOW SLAVE STATUS\G (...) Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: rpl_user Master_Port: 12000 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 120 Relay_Log_File: slave-relay-bin.000003 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes (...) 22
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Both slave threads are up and running!
Replication Basics Replicating From Master to Slave.
Use the test db.
master> USE test; master> CREATE TABLE t1 (a INT); slave> USE test; slave> SHOW TABLES; master> INSERT INTO t1 VALUES(1); slave> SELECT * FROM t1;
23
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Create a table. Slave has replicated the table. Slave has replicated the data.
Replication Basics What's in the Binary Log? master> SHOW BINLOG EVENTS [IN 'master-bin.0000002']; The “CREATE TABLE” statement. +-------------------+-----+-------------+-----------+-------------+-----------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+-------------+-----------+-------------+-----------------------------------------+ | master-bin.000002 | 4 | Format_desc | 1| 120 | Server ver: 5.6.6-m9-log, Binlog ver: 4 | | master-bin.000002 | 120 | Query | 1| 217 | use `test`; CREATE TABLE t1 (a INT) | | master-bin.000002 | 217 | Query | 1| 296 | BEGIN | | master-bin.000002 | 296 | Query | 1| 395 | use `test`; INSERT INTO t1 VALUES (1) | | master-bin.000002 | 395 | Xid | 1| 426 | COMMIT /* xid=21 */ | +-------------------+-----+-------------+-----------+-------------+-----------------------------------------+ 5 rows in set (0.00 sec)
24
The “INSERT” statement.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Replicating From Master to Slave – binary log formats. • Three formats: -
Statement – every change logged as a statement.
-
Row – every change logged as data.
-
Data changes are applied on the slave.
MIXED – every change logged either as data or statements.
25
Reexecuted on the slave.
Automatically switches from statement to row on nondeterministic statements.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Replicating From Master to Slave – binary log formats. master> set binlog_format=ROW; master> INSERT INTO t1 VALUES(2); master> SHOW BINLOG EVENTS; +-------------------+-----+-------------+-----------+-------------+-----------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+-------------+-----------+-------------+-----------------------------------------+ | master-bin.000002 | 4 | Format_desc | 1| 120 | Server ver: 5.6.6-m9-log, Binlog ver: 4 | | master-bin.000002 | 120 | Query | 1| 217 | use `test`; CREATE TABLE t1 (a INT) | | master-bin.000002 | 217 | Query | 1| 296 | BEGIN | | master-bin.000002 | 296 | Query | 1| 395 | use `test`; INSERT INTO t1 VALUES (1) | | master-bin.000002 | 395 | Xid | 1| 426 | COMMIT /* xid=21 */ | | master-bin.000002 | 426 | Query | 1| 498 | BEGIN | | master-bin.000002 | 498 | Table_map | 1| 543 | table_id: 70 (test.t1) | | master-bin.000002 | 543 | Write_rows | 1| 583 | table_id: 70 flags: STMT_END_F | | master-bin.000002 | 583 | Xid | 1| 614 | COMMIT /* xid=27 */ | +-------------------+-----+-------------+-----------+-------------+-----------------------------------------+ 9 rows in set (0.00 sec)
26
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Let's change the format.
The second “INSERT” statement.
Replication Basics Master replication files. $ ls -la master/ The binary log files. (...) -rw-rw---- 1 XXXXXX XXXXXX -rw-rw---- 1 XXXXXX XXXXXX -rw-rw---- 1 XXXXXX XXXXXX (...)
143 Sep 26 11:10 master-bin.000001 614 Sep 26 11:40 master-bin.000002 40 Sep 26 11:10 master-bin.index
Index file over the existing binary log files.
27
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Replication Basics Slave replication files.
Persists IO thread replication metadata (master host, username, … and positioning on the master's binlog).
$ ls -la slave/ (...) -rw-rw---- 1 XXXXXXX XXXXXXX (...) -rw-rw---- 1 XXXXXXX XXXXXXX (...) -rw-rw---- 1 XXXXXXX XXXXXXX -rw-rw---- 1 XXXXXXX XXXXXXX -rw-rw---- 1 XXXXXXX XXXXXXX (...)
128 Sep 26 11:40 master.info 57 Sep 26 11:40 relay-log.info 337 Sep 26 11:10 slave-relay-bin.000002 778 Sep 26 11:40 slave-relay-bin.000003 50 Sep 26 11:10 slave-relay-bin.index
Persists SQL thread replication metadata. Basically, the positioning in the relay log. 28
The relay log files.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Index file over the existing binary log files.
More Resources White Paper: MySQL Replication: An Introduction
http://www.mysql.com/why-mysql/white-papers/mysql-replication-introduction/ White Paper: MySQL Replication Tutorial: Configuration, Provisioning and Management http://www.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/ Documentation: Replication
http://dev.mysql.com/doc/refman/5.6/en/replication.html MySQL Forums: Replication http://forums.mysql.com/list.php?26
29
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
