MySQL Replication Tips and Tricks João Gramacho (
[email protected]) Software Developer, MySQL Replication Core Team Copyright Copyright©©2015, 2015,Oracle Oracleand/or and/oritsitsaffiliates. affiliates.All Allrights rightsreserved. reserved.|
Safe Harbor Statement 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.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
3
Program Agenda 1
MySQL Replication 101
2
Replication Basics
3
The Binary Log
4
Tips
5
Tricks
6
Summary Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
4
Program Agenda 1
MySQL Replication 101
2
Replication Basics
3
The Binary Log
4
Tips
5
Tricks
6
Summary Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
5
MySQL Replication 101 Background: Replication Components Insert... Client
Sender thread
Receiver thread
binary log
Applier threads
relay log
binary log
Insert...
Insert...
Insert...
Network
A
B
master
slave Receiver Meta-data Update
Applier Meta-data Update
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
6
MySQL Replication 101 Background: 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; • Control events: Format Description, Previous_gtids, Rotate, Gtid, ...
Binary log file content FD
PREV_GTID …
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
ROTATE
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
7
MySQL Replication 101 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;
• Semi-synchronous Replication (MySQL 5.5+): – Master commits transaction but waits until one slave (or “N” slaves in 5.7+) acknowledges having received and stored the events before replying to the client;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
8
MySQL Replication 101 Setups: the basics: a master and a slave
M
S
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
9
MySQL Replication 101 Setups: a server can be a slave and also a master at the same time
M
M/S
S
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
10
MySQL Replication 101 Setups: a master can replicate to multiple slaves
S
M
M/S
S
S
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
11
MySQL Replication 101 Setups: a slave can replicate from multiple masters (5.7 new feature)
M
M
M
M
S
M
M
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
12
MySQL Replication 101 Setups: circular replication
M/S
M/S
M/S
M/S
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
13
MySQL Replication 101 What is Replication Used For?
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
14
MySQL Replication 101 What is Replication Used For? Read scale-out S
M
S
More reads? More slaves!
M
S
S write clients write clients
read clients
read clients
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
15
MySQL Replication 101 What is Replication Used For? Redundancy: if master crashes, promote slave to master
B
B Uh Oh!
A C
B Whew!
A Crash
A
C
C B is the new master
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
16
MySQL Replication 101 What is Replication Used For? On-line Backup and Reporting
S M
write clients
business intelligent client applications reporting client applications big queries client applications Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
17
MySQL Replication 101 What is Replication Used For? Geographic Replication: Disaster Recovery A C
A B B C
Image from www.ginkgomaps.com
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
18
Program Agenda 1
MySQL Replication 101
2
Replication Basics
3
The Binary Log
4
Tips
5
Tricks
6
Summary Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
19
Replication Basics Getting MySQL
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
20
Getting MySQL • Get the latest MySQL 5.7 generic tar.gz package from here: – http://dev.mysql.com/downloads/mysql/; – Used on this presentation: mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz;
• Extract MySQL into a working directory; • Rename MySQL directory for simplicity;
$ mkdir ${HOME}/tut5467/ ~/tut5467 $ cd ${HOME}/tut5467/ ~/tut5467 $ tar -xzf ${HOME}/Downloads/mysql-....tar.gz ~/tut5467 $ mv $HOME/tut5467/mysql-... $HOME/tut5467/mysql-57
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
21
Replication Basics Setting up a master server
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
22
Setting up a master server • Create a master data directory (procedure changed in 5.7): – https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html Note: we used --initialize-insecure only to easy the design of this presentation!
~/tut5467 $ mysql-57/bin/mysqld --initialize-insecure --user=$( whoami ) \ --basedir=$HOME/tut5467/mysql-57 \ --datadir=$HOME/tut5467/master 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 0 [Warning] InnoDB: New log files created, LSN=45790 0 [Warning] InnoDB: Creating foreign key constraint system tables. 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4d0afcc1-78d7-11e5-9fe2-f01faf18153b. 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
23
Setting up a master server • Create a configuration file, master.cnf, with the appropriate options: – Binary log is enabled; – Server id is set;
• Start the server; ~/tut5467 $ cat master.cnf [mysqld] server-id=1 log-bin=master-bin log-error=master.err port=12000 ~/tut5467 $ mysql-57/bin/mysqld --defaults-file=$HOME/tut5467/master.cnf \ --lc-messages-dir=$HOME/tut5467/mysql-57/share \ --datadir=$HOME/tut5467/master/
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
24
Setting up a master server • Connect to the master;
~/tut5467 $ mysql-57/bin/mysql -u root --port 12000 --protocol=tcp --prompt='master> ' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.9-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. master>
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
25
Setting up a master server • Check master status: – SHOW MASTER STATUS; master> SHOW MASTER STATUS; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 154 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
26
Setting up a master server • Check binary log status: – SHOW BINARY LOGS; – SHOW BINLOG EVENTS; master> SHOW BINARY LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 154 | +-------------------+-----------+ 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 | 123 | Server ver: 5.7.9-log, Binlog ver: 4 | | master-bin.000001 | 123 | Previous_gtids | 1 | 154 | | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ 2 rows in set (0,02 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
27
Replication Basics Setting up a slave server
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
28
Setting up a slave server • Create a slave data directory;
~/tut5467 $ mysql-57/bin/mysqld --initialize-insecure --user=$( whoami ) \ --basedir=$HOME/tut5467/mysql-57 \ --datadir=$HOME/tut5467/slave 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 0 [Warning] InnoDB: New log files created, LSN=45790 0 [Warning] InnoDB: Creating foreign key constraint system tables. 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 065cf0c0-78db-11e5-91d8-f01faf18153b. 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
29
Setting up a slave server • Create a configuration file, slave.cnf, with the appropriate options: – Server id is set and is unique in the replication topology; – The name for the relay log is set (optional, but recommended);
• Start the server; ~/tut5467 $ cat slave.cnf [mysqld] server-id=2 relay-log=slave-relay-bin log-error=slave.err port=12001 ~/tut5467 $ mysql-57/bin/mysqld --defaults-file=$HOME/tut5467/slave.cnf \ --lc-messages-dir=$HOME/tut5467/mysql-57/share \ --datadir=$HOME/tut5467/slave/
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
30
Setting up a slave server • Connect to the slave;
~/tut5467 $ mysql-57/bin/mysql -u root --port 12001 --protocol=tcp --prompt='slave> ' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.9-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. slave>
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
31
Replication Basics Setting up replication between master and slave servers
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
32
Setting up replication between master and slave servers • Create an user to be used by replication; • Setup slave privileges for the user;
master> CREATE USER `rpl_user`@`localhost`; Query OK, 0 rows affected (0,07 sec) master> GRANT REPLICATION SLAVE ON *.* -> TO `rpl_user`@`localhost` -> IDENTIFIED BY 'secret'; Query OK, 0 rows affected, 1 warning (0,07 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
33
Setting up replication between master and slave servers • Make a backup of the master to be restored on slave: – Physical backup: • • • •
Typically faster; Offline: tar + gzip; Online: snapshot (LVM, ZFS); MySQL Enterprise Backup;
– Logical backup: • Using mysqldump client program;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
34
Setting up replication between master and slave servers • Make a backup of the master to be restored on slave: – Physical backup: • • • •
Typically faster; Offline: tar + gzip; Online: snapshot (LVM, ZFS); MySQL Enterprise Backup;
– Logical backup: • Using mysqldump client program;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
35
Setting up replication between master and slave servers • Perform the backup: – Use --master-data=2 to log master positions on the backup file as a comment; – Use --single-transaction if you have only transactional tables (i.e. InnoDB);
• Take note of the master binary log position that was logged on the backup;
~/tut5467 $ mysql-57/bin/mysqldump –u root --host=127.0.0.1 --port=12000 \ --master-data=2 --single-transaction \ --lock-all-tables --all-databases > master-backup.sql ~/tut5467 $ grep '\-\- CHANGE MASTER TO' master-backup.sql -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=799;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
36
Setting up replication between master and slave servers • Provision the slave; • Configure the slave to connect to the master; slave> SHOW WARNINGS; +-------+------+----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------+ | Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. | | Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is | | | | therefore not recommended. Please consider using the USER and PASSWORD connection options for START| | | | SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. | +-------+------+----------------------------------------------------------------------------------------------------+ 2 rows in set (0,00 sec)
~/tut5467 $ mysql-57/bin/mysql -u root --host=127.0.0.1 --port=12001 \ < master-backup.sql slave> CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_PORT = 12000, -> MASTER_USER = 'rpl_user', MASTER_PASSWORD = 'secret', -> MASTER_LOG_FILE = 'master-bin.000001', MASTER_LOG_POS = 799; Query OK, 0 rows affected, 2 warnings (0,65 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
37
Setting up replication between master and slave servers • Start the slave; • Check replication status (SHOW SLAVE STATUS); slave> START SLAVE; Query OK, 0 rows affected (0,09 sec) slave> SHOW SLAVE STATUS \G *************************** 1. Slave_IO_State: ... Master_Port: Master_Log_File: Read_Master_Log_Pos: ... Slave_IO_Running: Slave_SQL_Running: ... Master_Server_Id: Master_UUID: Slave_SQL_Running_State: ... 1 row in set (0,00 sec)
row *************************** Waiting for master to send event 12000 master-bin.000001 799 Yes Yes 1 4d0afcc1-78d7-11e5-9fe2-f01faf18153b Slave has read all relay log; waiting for more updates
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
38
Setting up replication between master and slave servers • Testing replication between master and slave; master> CREATE DATABASE test; Query OK, 1 row affected (0,05 sec) master> USE test; Database changed master> CREATE TABLE t1 (a INT); Query OK, 0 rows affected (0,44 sec) slave> USE test; Database changed slave> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
39
Replication Basics Tips
• Configure options for my.cnf: – log-bin: enable the binary log on the master; – server-id: must be unique per server deployed;
• On the master, set sane replication user privileges, do not connect slave server as SUPER: – GRANT REPLICATION SLAVE TO ...;
• On the slave, point it to the master and start replication: – CHANGE MASTER TO ...; – START SLAVE; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
40
Program Agenda 1
MySQL Replication 101
2
Replication Basics
3
The Binary Log
4
Tips
5
Tricks
6
Summary Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
41
The Binary Log • File based log that records the changes on the master; • Composed by two types of files: – Index: contains a list of the current available binary log files; master> SHOW BINARY LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 1168 | | master-bin.000002 | 154 | +-------------------+-----------+ 2 rows in set (0,00 sec) ~/tut5467 $ cat master/master-bin.index ./master-bin.000001 ./master-bin.000002
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
42
The Binary Log • File based log that records the changes on the master; • Composed by two types of files: – Index: contains a list of the current available binary log files; – Binary log files: contains the events that are capable of reproducing the changes;
master-bin.000001 FD
PREV_GTID …
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
ROTATE
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
ROTATE
master-bin.000002 FD
PREV_GTID …
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
43
The Binary Log Binary Log Files
• Each transaction (or database change) is split into a set of events; • Control events: Format Description, Previous GTIDs, GTID, Rotate, ... • Can be inspected using: – SHOW BINLOG EVENTS [IN '']; – Command line tool: mysqlbinlog;
FD
PREV_GTID …
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
ROTATE
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
44
The Binary Log Binary Log Files: SHOW BINLOG EVENTS [IN '']
master> SHOW BINLOG EVENTS IN 'master-bin.000002'; +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | master-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.9-log, Binlog ver: 4 | | master-bin.000002 | 123 | Previous_gtids | 1 | 154 | | | master-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000002 | 219 | Query | 1 | 291 | BEGIN | | master-bin.000002 | 291 | Table_map | 1 | 336 | table_id: 140 (test.t1) | | master-bin.000002 | 336 | Write_rows | 1 | 376 | table_id: 140 flags: STMT_END_F | | master-bin.000002 | 376 | Xid | 1 | 407 | COMMIT /* xid=442 */ | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ 7 rows in set (0,00 sec)
FD
PREV_GTID …
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
ROTATE
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
45
The Binary Log Binary Log Files: Command line tool: mysqlbinlog
• Decodes events in the binary log files; • Good for debugging, recovery and even auditing; • Outputs valid SQL commands: – When piped through mysql client program can replay binary logs; – Can be used as a tool for implementing point-in-time recovery;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
46
The Binary Log Binary Log Files: Command line tool: mysqlbinlog ~/tut5467 $ mysql-57/bin/mysqlbinlog -vv master/master-bin.000002 ... # at 4 #151023 2:27:32 server id 1 end_log_pos 123 CRC32 0xa7565408 Start: binlog v 4, server v 5.7.9-log created 151023 2:27:32 BINLOG ' BI0pVg8BAAAAdwAAAHsAAAAAAAQANS43LjktbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AQhUVqc= '/*!*/; ... # at 154 #151023 3:01:45 server id 1 end_log_pos 219 CRC32 0xf7c79d5b Anonymous_GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #151023 3:01:45 server id 1 end_log_pos 291 CRC32 0x27c41b42 Query thread_id=2 exec_time=0 error_code=0 ... BEGIN /*!*/; # at 291 #151023 3:01:45 server id 1 end_log_pos 336 CRC32 0xb4829a68 Table_map: `test`.`t1` mapped to number 140 # at 336 #151023 3:01:45 server id 1 end_log_pos 376 CRC32 0xa0db9d28 Write_rows: table id 140 flags: STMT_END_F
BINLOG ' CZUpVhMBAAAALQAAAFABAAAAAIwAAAAAAAEABHRlc3QAAnQxAAEDAAFomoK0 CZUpVh4BAAAAKAAAAHgBAAAAAIwAAAAAAAEAAgAB//4BAAAAKJ3boA== '/*!*/; ### INSERT INTO `test`.`t1` ...
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
47
The Binary Log Binary Log Format: Controls the way the changes are logged into the binary log
• STATEMENT (default up to MySQL 5.6): – Every change logged as a statement; – Re-executed on the slave;
• ROW (default in MySQL 5.7): – Every change logged as data; – Data changes are applied on the slave;
• MIXED: – Every change logged either as data or statements; – Automatically switches from STATEMENT to ROW on non-deterministic statements; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
48
The Binary Log Binary Log Format: Controls the way the changes are logged into the binary log
• Format can be changed dynamically in runtime: – SET BINLOG_FORMAT= [ROW|STATEMENT|MIXED]; master> SET BINLOG_FORMAT=STATEMENT; Query OK, 0 rows affected (0,00 sec) master> INSERT INTO t1 VALUES (2); Query OK, 1 row affected (0,20 sec) master> SHOW BINLOG EVENTS IN 'master-bin.000003' FROM 154; +-------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+---------------------------------------+ | master-bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000003 | 219 | Query | 1 | 298 | BEGIN | | master-bin.000003 | 298 | Query | 1 | 397 | use `test`; INSERT INTO t1 VALUES (2) | | master-bin.000003 | 397 | Xid | 1 | 428 | COMMIT /* xid=446 */ | +-------------------+-----+----------------+-----------+-------------+---------------------------------------+ 6 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
49
The Binary Log Statement Based Replication (SBR)
• Statements are logged almost verbatim; • Statements are re-executed on the slave; • Is vulnerable to non-deterministic behavior, e.g. UDF usage, UUID(), ...; • Logged after the statement is executed and before the transaction is committed; • DDL is always logged in STATEMENT format;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
50
The Binary Log Statement Based Replication (SBR)
• Query_log_event may be preceded by additional context events: master> SET @my_var=1; Query OK, 0 rows affected (0,00 sec) master> INSERT INTO t2 (c2, c3) VALUES (@my_var, RAND()*100); Query OK, 1 row affected, 1 warning (0,13 sec) master> SHOW BINLOG EVENTS IN 'master-bin.000004‘ FROM 154; +-------------------+-----+----------------+-----------+-------------+----------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+----------------------------------------------+ | master-bin.000004 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000004 | 219 | Query | 1 | 298 | BEGIN | | master-bin.000004 | 298 | Intvar | 1 | 330 | INSERT_ID=1 | | master-bin.000004 | 330 | RAND | 1 | 369 | rand_seed1=917287539,rand_seed2=246064842 | | master-bin.000004 | 369 | User var | 1 | 421 | @`my_var`=1 | | master-bin.000004 | 421 | Query | 1 | 547 | use `test`; INSERT INTO t2 (c2, c3) | | | | | | | VALUES (@my_var, RAND()*100) | | master-bin.000004 | 547 | Xid | 1 | 578 | COMMIT /* xid=451 */ | +-------------------+-----+----------------+-----------+-------------+----------------------------------------------+ 9 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
51
The Binary Log master> SHOW WARNINGS; Statement Based Replication (SBR)
+-------+------+----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------+ | Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. | | | | Statement is unsafe because it uses a system function that may return a different value on the | | | | slave. | +-------+------+----------------------------------------------------------------------------------------------------+ 1master> row in SET set @my_var=1; (0,00 sec) Query OK, 0 rows affected (0,00 sec)
• Query_log_event may be preceded by additional context events: master> INSERT INTO t2 (c2, c3) VALUES (@my_var, RAND()*100); Query OK, 1 row affected, 1 warning (0,13 sec)
master> SHOW BINLOG EVENTS IN 'master-bin.000004‘ FROM 154; +-------------------+-----+----------------+-----------+-------------+----------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+----------------------------------------------+ | master-bin.000004 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000004 | 219 | Query | 1 | 298 | BEGIN | | master-bin.000004 | 298 | Intvar | 1 | 330 | INSERT_ID=1 | | master-bin.000004 | 330 | RAND | 1 | 369 | rand_seed1=917287539,rand_seed2=246064842 | | master-bin.000004 | 369 | User var | 1 | 421 | @`my_var`=1 | | master-bin.000004 | 421 | Query | 1 | 547 | use `test`; INSERT INTO t2 (c2, c3) | | | | | | | VALUES (@my_var, RAND()*100) | | master-bin.000004 | 547 | Xid | 1 | 578 | COMMIT /* xid=451 */ | +-------------------+-----+----------------+-----------+-------------+----------------------------------------------+ 9 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
52
The Binary Log Statement Based Replication (SBR)
• Unsafe/non-deterministic statements: – User-defined functions (UDF); – UUID(), FOUND_ROWS(), RAND(), USER(); – Updates using LIMIT; – ...
• Unsafe statements make master and slave diverge; • Any unsafe statement produces a warning that is logged in the error log;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
53
The Binary Log Row Based Replication (RBR)
• Introduced in MySQL 5.1, default since MySQL 5.7; • Replicates the actual row changes (in binary format); • Handles non-deterministic statement seamlessly: – UDF, UUID(), ...;
• Can be used together with MySQL Cluster; • Several log events: – Table_map, Write_rows, Update_rows, Delete_rows;
• Only DML is logged in this format; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
54
The Binary Log Row Based Replication (RBR)
• Row events have one or more pairs of images per record changed: – Before image (BI): used to locate the row at the slave; – After image (AI): used to replay the changes from the master;
• Delete_rows and Write_rows have only one image;
• Update_rows have both images; Before Image
After Image
Changed Columns Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
55
Before Image
After Image
The Binary Log Row Based Replication (RBR): Write_rows
Changed Columns
master> SET BINLOG_FORMAT=ROW; Query OK, 0 rows affected (0,00 sec) master> INSERT INTO t2 (c2, c3) VALUES (@my_var, RAND()*100); Query OK, 1 row affected (0,06 sec) master> SHOW BINLOG EVENTS IN 'master-bin.000005' FROM 154; +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | master-bin.000005 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000005 | 219 | Query | 1 | 291 | BEGIN | | master-bin.000005 | 291 | Table_map | 1 | 338 | table_id: 141 (test.t2) | | master-bin.000005 | 338 | Write_rows | 1 | 386 | table_id: 141 flags: STMT_END_F | | master-bin.000005 | 386 | Xid | 1 | 417 | COMMIT /* xid=459 */ | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ 5 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
56
Before Image
After Image
The Binary Log Row Based Replication (RBR): Write_rows
Changed Columns
~/tut5467 $ mysql-57/bin/mysqlbinlog -vv master/master-bin.000005 --start-position=219 # at 219 #151023 4:05:28 server id 1 end_log_pos 291 CRC32 0x5abf43f0 Query thread_id=2 exec_time=0 error_code=0 ... BEGIN /*!*/; # at 291 #151023 4:05:28 server id 1 end_log_pos 338 CRC32 0xc5dcb756 Table_map: `test`.`t2` mapped to number 141 # at 338 #151023 4:05:28 server id 1 end_log_pos 386 CRC32 0xd9677c75 Write_rows: table id 141 flags: STMT_END_F BINLOG ' +KMpVhMBAAAALwAAAFIBAAAAAI0AAAAAAAEABHRlc3QAAnQyAAMDAwMABla33MU= +KMpVh4BAAAAMAAAAIIBAAAAAI0AAAAAAAEAAgAD//gDAAAAAQAAAD0AAAB1fGfZ '/*!*/; ### INSERT INTO `test`.`t2` ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=1 is_null=0 */ ### @3=61 /* INT meta=0 nullable=1 is_null=0 */ # at 386 #151023 4:05:28 server id 1 end_log_pos 417 CRC32 0xb82068ab Xid = 459 COMMIT/*!*/; ...
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
57
Before Image
After Image
The Binary Log Row Based Replication (RBR): Update_rows
Changed Columns
master> UPDATE t2 SET c2=@my_var + 1, c3= RAND()*100 WHERE c1=2; Query OK, 1 row affected (0,15 sec) Rows matched: 1 Changed: 1 Warnings: 0 master> SHOW BINLOG EVENTS IN 'master-bin.000006' FROM 154; +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | master-bin.000006 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000006 | 219 | Query | 1 | 291 | BEGIN | | master-bin.000006 | 291 | Table_map | 1 | 338 | table_id: 141 (test.t2) | | master-bin.000006 | 338 | Update_rows | 1 | 400 | table_id: 141 flags: STMT_END_F | | master-bin.000006 | 400 | Xid | 1 | 431 | COMMIT /* xid=462 */ | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ 5 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
58
Before Image
After Image
The Binary Log Row Based Replication (RBR): Update_rows
Changed Columns
~/tut5467 $ mysql-57/bin/mysqlbinlog -vv master/master-bin.000006 --start-position=219 # at 219 #151023 4:14:20 server id 1 end_log_pos 291 CRC32 0x51d97016 Query thread_id=2 exec_time=0 error_code=0 ... BEGIN /*!*/; # at 291 #151023 4:14:20 server id 1 end_log_pos 338 CRC32 0xfa9e5f19 Table_map: `test`.`t2` mapped to number 141 # at 338 #151023 4:14:20 server id 1 end_log_pos 400 CRC32 0xa857b09f Update_rows: table id 141 flags: STMT_END_F BINLOG ' DKYpVhMBAAAALwAAAFIBAAAAAI0AAAAAAAEABHRlc3QAAnQyAAMDAwMABhlfnvo= DKYpVh8BAAAAPgAAAJABAAAAAI0AAAAAAAEAAgAD///4AgAAAAEAAAAoAAAA+AIAAAACAAAAVgAA AJ+wV6g= '/*!*/; ### UPDATE `test`.`t2` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=1 is_null=0 */ ### @3=40 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ ### @3=86 /* INT meta=0 nullable=1 is_null=0 */ # at 400 #151023 4:14:20 server id 1 end_log_pos 431 CRC32 0x499b6ec2 Xid = 462 COMMIT/*!*/; ...
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
59
Before Image
After Image
The Binary Log Row Based Replication (RBR): Delete_rows
Changed Columns
master> DELETE FROM t2 WHERE c1=2; Query OK, 1 row affected (0,16 sec) master> SHOW BINLOG EVENTS IN 'master-bin.000007' FROM 154; +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | master-bin.000007 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000007 | 219 | Query | 1 | 291 | BEGIN | | master-bin.000007 | 291 | Table_map | 1 | 338 | table_id: 141 (test.t2) | | master-bin.000007 | 338 | Delete_rows | 1 | 386 | table_id: 141 flags: STMT_END_F | | master-bin.000007 | 386 | Xid | 1 | 417 | COMMIT /* xid=465 */ | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ 5 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
60
Before Image
After Image
The Binary Log Row Based Replication (RBR): Delete_rows
Changed Columns
~/tut5467 $ mysql-57/bin/mysqlbinlog -vv master/master-bin.000007 --start-position=219 # at 219 #151023 4:19:28 server id 1 end_log_pos 291 CRC32 0x8cd76e4f Query thread_id=2 exec_time=1 error_code=0 ... BEGIN /*!*/; # at 291 #151023 4:19:28 server id 1 end_log_pos 338 CRC32 0x0c63595a Table_map: `test`.`t2` mapped to number 141 # at 338 #151023 4:19:28 server id 1 end_log_pos 386 CRC32 0xc0475692 Delete_rows: table id 141 flags: STMT_END_F BINLOG ' QKcpVhMBAAAALwAAAFIBAAAAAI0AAAAAAAEABHRlc3QAAnQyAAMDAwMABlpZYww= QKcpViABAAAAMAAAAIIBAAAAAI0AAAAAAAEAAgAD//gCAAAAAgAAAFYAAACSVkfA '/*!*/; ### DELETE FROM `test`.`t2` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ ### @3=86 /* INT meta=0 nullable=1 is_null=0 */ # at 386 #151023 4:19:28 server id 1 end_log_pos 417 CRC32 0xc1e6a888 Xid = 465 COMMIT/*!*/; ...
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
61
The Binary Log Mixed Format Based Replication
• Introduced in MySQL 5.1; • Every statement is logged in STATEMENT format, unless it is unsafe; • Unsafe statements: trigger an automatic switch to ROW format;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
62
The Binary Log Tips
• The binary log format is flexible: – STATEMENT: small storage and network footprint, but vulnerable to unsafe statements; – ROW: safe, robust, but may generate big events (e.g. updating an entire table); – MIXED format comes in as a balance, but needs to be evaluated on a per case basis;
• mysqlbinlog tool can inspect the content of the binary logs: – Useful for replaying binary logs and for point-in-time recovery;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
63
Program Agenda 1
MySQL Replication 101
2
Replication Basics
3
The Binary Log
4
Tips
5
Tricks
6
Summary Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
64
Tip #1: Storing Replication Metadata in Tables a.k.a. Crash-Safe Replication or Crash-Safe Slaves
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
65
Storing Replication Metadata Receiver (a.k.a. I/O Thread): Event grained Receiver thread
Applier thread
relay log master-bin.000006
AGTID
154
BEGIN
219
Ev1 Ev2
Network
COMMIT
291 338 400
AGTID BEGIN Ev1 Ev2 COMMIT
431
B slave
Receiver Meta-data
Applier Meta-data
master-bin.000006 154 431 400 338 291 219 File: master.info
File: relay-log.info
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
66
Storing Replication Metadata Applier (a.k.a. SQL Thread): Transaction grained Receiver thread
Applier thread
relay log master-bin.000006
AGTID
154
BEGIN
219
Ev1 Ev2
Network
COMMIT
291 338 400
AGTID BEGIN Ev1 Ev2 COMMIT
431
B slave
Receiver Meta-data
Applier Meta-data
master-bin.000006 154 431 400 338 291 219
master-bin.000006 431 154
File: master.info
File: relay-log.info
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
67
Storing Replication Metadata Applier (a.k.a. SQL Thread): Transaction grained Receiver thread
Applier thread
relay log master-bin.000006
AGTID
154
BEGIN
219
Ev1 Ev2
Network
COMMIT
291 338 400
AGTID BEGIN Ev1 Ev2 COMMIT
431
CRASH B slave
Receiver Meta-data
Applier Meta-data
master-bin.000006 154 431 400 338 291 219
master-bin.000006 154
File: master.info
File: relay-log.info
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
68
Storing Replication Metadata Applier (a.k.a. SQL Thread): Transaction grained Receiver thread
Applier thread
relay log master-bin.000006
AGTID
154
BEGIN
219
Ev1 Ev2
Network
COMMIT
291 338 400
AGTID BEGIN Ev1 Ev2 COMMIT
431
B slave
Receiver Meta-data
Applier Meta-data
master-bin.000006 154 431 400 338 291 219
master-bin.000006 154
File: master.info
File: relay-log.info
After restarting, the applier will begin from this position
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
69
Storing Replication Metadata in Tables • Store replication metadata in tables, within the context of regular transactions: – Commit both data and replication positions together; – Rollback both data and replication positions together; – Robust, highly available setups; – No metadata in files (master.info and relay-log.info); – Slave knows a valid position to resume after a crash;
• Non-transactional engines will not take the above benefits;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
70
Storing Replication Metadata in Tables Applier (a.k.a. SQL Thread): Crash-Safe Replication Receiver thread
Applier thread
relay log master-bin.000006
AGTID
154
BEGIN
219
Ev1 Ev2
Network
COMMIT
291 338 400
AGTID BEGIN Ev1 Ev2 COMMIT
431
B slave
Receiver Meta-data
Applier Meta-data
master-bin.000006 154 431 400 338 291 219
master-bin.000006 431 154
mysql.slave_master_info
mysql.slave_relay_log_info
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
71
Storing Replication Metadata in Tables Configuring the slave to use tables as metadata repository
• Stop the slave replication; • Configure slave to use tables using the global variables; • Start the slave again; slave> slave> STOP SLAVE; Query OK, 0 rows affected (0,16 sec)
slave> SET @@GLOBAL.master_info_repository=TABLE; Query OK, 0 rows affected (0,17 sec) slave> SET @@GLOBAL.relay_log_info_repository=TABLE; Query OK, 0 rows affected (0,10 sec)
slave> START SLAVE; Query OK, 0 rows affected (0,09 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
72
Storing Replication Metadata in Tables Configuring the slave to use tables as metadata repository
• Stop the slave replication; • Configure slave to use tables using the global variables; • Start the slave again; • Add the changed options to the slave configuration file; ~/tut5467 $ cat slave.cnf [mysqld] server-id=2 ... relay-log-info-repository=TABLE master-info-repository=TABLE
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
73
Storing Replication Metadata in Tables Configuring the slave to use tables as metadata repository
• Check if the receiver and the applier metadata repositories were changed;
slave> SELECT @@GLOBAL.master_info_repository, @@GLOBAL.relay_log_info_repository; +---------------------------------+------------------------------------+ | @@GLOBAL.master_info_repository | @@GLOBAL.relay_log_info_repository | +---------------------------------+------------------------------------+ | TABLE | TABLE | +---------------------------------+------------------------------------+ 1 row in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
74
Tip #2: Tuning Row-based Replication
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
75
Tuning Row-based Replication • Two optimizations: – RBR can be configured to exclude BLOBS only (when not needed); – RBR can also work with partial rows in addition to full rows: • Before image: only fields required to find the row; • After image: only fields that actually changed;
• Reduces memory footprint; • Reduces network bandwidth usage; • Reduces binary log files size;
• Default configuration is to not use optimizations;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
76
Tuning Row-based Replication Before Image
After Image
Full Rows
Rows without Blobs Blob
Minimal Rows Primary Key
Changed Columns
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
77
Tuning Row-based Replication SET binlog_row_image = [ MINIMAL | NOBLOB | FULL ]
• Prepare the comparison: – Set RBR; – Create a new table; – Populate the new table; master> SET binlog_format=ROW; Query OK, 0 rows affected (0,00 sec) master> CREATE TABLE t3 (i1 INT PRIMARY KEY AUTO_INCREMENT, i2 INT, f FLOAT, t TEXT); Query OK, 0 rows affected (0,41 sec) master> INSERT INTO t3 VALUES (1, 1, 1.0, 'Hello World!'); Query OK, 1 row affected (0,10 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
78
Tuning Row-based Replication SET binlog_row_image = FULL master> UPDATE t3 SET i2=2 WHERE i1=1; Query OK, 1 row affected (0,08 sec) Rows matched: 1 Changed: 1 Warnings: 0 ~/tut5467 $ mysql-57/bin/mysqlbinlog -vv master/master-bin.000008 --start-position=786 ... # at 786 #151023 11:53:00 server id 1 end_log_pos 836 CRC32 0xb6977004 Table_map: `test`.`t3` mapped to number 142 # at 836 #151023 11:53:00 server id 1 end_log_pos 926 CRC32 0xe80787db Update_rows: table id 142 flags: STMT_END_F ... ### UPDATE `test`.`t3` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=1 is_null=0 */ ### @3=1 /* FLOAT meta=4 nullable=1 is_null=0 */ ### @4='Hello World!' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ ### @3=1 /* FLOAT meta=4 nullable=1 is_null=0 */ ### @4='Hello World!' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */ ...
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
79
Tuning Row-based Replication SET binlog_row_image = NOBLOB master> UPDATE t3 SET i2=3 WHERE i1=1; Query OK, 1 row affected (0,07 sec) Rows matched: 1 Changed: 1 Warnings: 0 ~/tut5467 $ mysql-57/bin/mysqlbinlog -vv master/master-bin.000008 --start-position=1094 ... # at 1094 #151023 11:58:12 server id 1 end_log_pos 1144 CRC32 0x5781868f Table_map: `test`.`t3` mapped to number 142 # at 1144 #151023 11:58:12 server id 1 end_log_pos 1206 CRC32 0x058d864c Update_rows: table id 142 flags: STMT_END_F ... '/*!*/; ### UPDATE `test`.`t3` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ ### @3=1 /* FLOAT meta=4 nullable=1 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=3 /* INT meta=0 nullable=1 is_null=0 */ ### @3=1 /* FLOAT meta=4 nullable=1 is_null=0 */ ...
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
80
Tuning Row-based Replication SET binlog_row_image = MINIMAL
master> UPDATE t3 SET i2=4 WHERE i1=1; Query OK, 1 row affected (0,06 sec) Rows matched: 1 Changed: 1 Warnings: 0 ~/tut5467 $ mysql-57/bin/mysqlbinlog -vv master/master-bin.000008 --start-position=1374 ... # at 1374 #151023 12:00:16 server id 1 end_log_pos 1424 CRC32 0xdd705721 Table_map: `test`.`t3` mapped to number 142 # at 1424 #151023 12:00:16 server id 1 end_log_pos 1470 CRC32 0x55fd4af5 Update_rows: table id 142 flags: STMT_END_F BINLOG ' QBMqVhMBAAAAMgAAAJAFAAAAAI4AAAAAAAEABHRlc3QAAnQzAAQDAwT8AgQCDiFXcN0= QBMqVh8BAAAALgAAAL4FAAAAAI4AAAAAAAEAAgAEAQL+AQAAAP4EAAAA9Ur9VQ== '/*!*/; ### UPDATE `test`.`t3` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @2=4 /* INT meta=0 nullable=1 is_null=0 */ ...
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
81
Tip #3: Better Debugging of Row-based Replication
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
82
Better Debugging of Row-based Replication
master> SHOW BINLOG EVENTS IN 'master-bin.000008' FROM 649; +-------------------+------+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+------+----------------+-----------+-------------+--------------------------------------+ | master-bin.000008 | 649 | Anonymous_Gtid | 1 | 714 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000008 | 714 | Query | 1 | 786 | BEGIN | | master-bin.000008 | 786 | Table_map | 1 | 836 | table_id: 142 (test.t3) | | master-bin.000008 | 836 | Update_rows | 1 | 926 | table_id: 142 flags: STMT_END_F | | master-bin.000008 | 926 | Xid | 1 | 957 | COMMIT /* xid=491 */ | | master-bin.000008 | 957 | Anonymous_Gtid | 1 | 1022 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000008 | 1022 | Query | 1 | 1094 | BEGIN | | master-bin.000008 | 1094 | Table_map | 1 | 1144 | table_id: 142 (test.t3) | | master-bin.000008 | 1144 | Update_rows | 1 | 1206 | table_id: 142 flags: STMT_END_F | | master-bin.000008 | 1206 | Xid | 1 | 1237 | COMMIT /* xid=493 */ | | master-bin.000008 | 1237 | Anonymous_Gtid | 1 | 1302 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000008 | 1302 | Query | 1 | 1374 | BEGIN | | master-bin.000008 | 1374 | Table_map | 1 | 1424 | table_id: 142 (test.t3) | | master-bin.000008 | 1424 | Update_rows | 1 | 1470 | table_id: 142 flags: STMT_END_F | | master-bin.000008 | 1470 | Xid | 1 | 1501 | COMMIT /* xid=495 */ | +-------------------+------+----------------+-----------+-------------+--------------------------------------+ 15 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
83
Better Debugging of Row-based Replication • Log the original query along with the RBR events: – Enhances auditing and debugging; – Extra event preceding the Rows events; – Replicated everywhere in the topology, together with the Rows events themselves; – User can turn the behavior on and off;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
84
Better Debugging of Row-based Replication
master> SET binlog_rows_query_log_events=ON; Query OK, 0 rows affected (0,00 sec) master> UPDATE t3 SET i2=5 WHERE i1=1; Query OK, 1 row affected (0,06 sec) Rows matched: 1 Changed: 1 Warnings: 0 master> SHOW BINLOG EVENTS IN 'master-bin.000009' FROM 154; +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ | master-bin.000009 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000009 | 219 | Query | 1 | 291 | BEGIN | | master-bin.000009 | 291 | Rows_query | 1 | 344 | # UPDATE t3 SET i2=5 WHERE i1=1 | | master-bin.000009 | 344 | Table_map | 1 | 394 | table_id: 142 (test.t3) | | master-bin.000009 | 394 | Update_rows | 1 | 440 | table_id: 142 flags: STMT_END_F | | master-bin.000009 | 440 | Xid | 1 | 471 | COMMIT /* xid=500 */ | +-------------------+-----+----------------+-----------+-------------+--------------------------------------+ 6 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
85
Better Debugging of Row-based Replication
~/tut5467 $ mysql-57/bin/mysqlbinlog -vv master/master-bin.000009 --start-position=291 # at 291 #151023 12:09:09 server id 1 end_log_pos 344 CRC32 0x5f0d7cc4 Rows_query # UPDATE t3 SET i2=5 WHERE i1=1 # at 344 #151023 12:09:09 server id 1 end_log_pos 394 CRC32 0xc1644006 Table_map: `test`.`t3` mapped to number 142 # at 394 #151023 12:09:09 server id 1 end_log_pos 440 CRC32 0x3c6f9483 Update_rows: table id 142 flags: STMT_END_F BINLOG ' VRUqVh0BAAAANQAAAFgBAACAAB1VUERBVEUgdDMgU0VUIGkyPTUgV0hFUkUgaTE9McR8DV8= VRUqVhMBAAAAMgAAAIoBAAAAAI4AAAAAAAEABHRlc3QAAnQzAAQDAwT8AgQCDgZAZME= VRUqVh8BAAAALgAAALgBAAAAAI4AAAAAAAEAAgAEAQL+AQAAAP4FAAAAg5RvPA== '/*!*/; ### UPDATE `test`.`t3` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @2=5 /* INT meta=0 nullable=1 is_null=0 */ ...
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
86
Tip #4: Automatic Fail-over and Replication Positioning: Global Transaction Identifiers
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
87
Global Transaction Identifiers (GTIDs) • Servers sometimes crash (hardware error, bug, meteor hit...); • Promote a slave to replace a crashed master;
• Method to represent positions in replication stream is CRUCIAL! • Traditional: FILENAME + OFFSET; – Local, absolute, manual;
• We introduced (since MySQL 5.6): TRANSACTION IDENTIFIERS – Global, logical, automatic;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
88
Global Transaction Identifiers (GTIDs) • On the master: – Generate a global transaction identifier (GTID) on commit; – Composed by server_uuid:number: 4d0afcc1-78d7-11e5-9fe2-f01faf18153b:1 • server_uuid: identifies the server, globally unique, automatically generated when not defined; • number: is incremented by 1 for each transaction on this server;
– Write the GTID to the binary log with the transaction;
• GTID is preserved when a server (i.e. slave) re-executes the transaction;
FD
PREV_GTID …
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
GTID
BEGIN
Ev1 Ev2 …
COMMIT
…
ROTATE
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
89
Global Transaction Identifiers (GTIDs) • Replication protocol: FILENAME + OFFSET: – Slave sends a pair containing the binary log file name and the position from which the master should start sending events; – This information may change when switching between masters;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
90
Global Transaction Identifiers (GTIDs) • Replication protocol: GTID: – Slave sends a GTID set with the transaction identifiers it already received and applied: • This information should be the same, regardless of the master;
– Master sends transactions that are missing on slave; binary log
relay log id1,id2
id1, trx1 id2, trx2 id3, trx3
A
id1, trx1 id2, trx2
id3, trx3
master
B slave
Sender thread
Receiver thread Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
91
Enabling Global Transaction Identifiers (GTIDs) • Cleanup non-GTID states on master’s binary log; • Shutdown the master; • Stop replication receiver and applier threads on slave; • Cleanup non-GTID states on slave’s relay log; • Shutdown the slave;
• Adjust master and slave’s configurations; • Start the master;
MySQL 5.6
• Start the slave;
• Configure the slave to replicate from the master using automatic positions; • Start replication receiver and applier threads on slave; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
92
Enabling Global Transaction Identifiers (GTIDs) • Cleanup non-GTID states on master’s binary log; • Shutdown the master; • Stop replication receiver and applier threads on slave; • Cleanup non-GTID states on slave’s relay log; • Shutdown the slave;
• Adjust master and slave’s configurations; • Start the master server;
MySQL 5.7
• Start the slave server;
• Configure the slave to replicate from the master using automatic positions; • Start replication receiver and applier threads on slave; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
93
Enabling Global Transaction Identifiers (GTIDs) On-line http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html (steps 1 and 2)
• Adjust master and slave’s configurations: – Enable ENFORCE_GTID_CONSISTENCY on all server;
master> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON; Query OK, 0 rows affected (0,01 sec)
slave> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON; Query OK, 0 rows affected (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
94
Enabling Global Transaction Identifiers (GTIDs) On-line http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html (step 3)
• Adjust master and slave’s configurations: – Enable ENFORCE_GTID_CONSISTENCY on all server; – Switch GTID mode to OFF_PERMISSIVE;
master> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE; Query OK, 0 rows affected (0,17 sec)
slave> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE; Query OK, 0 rows affected (0,01 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
95
Enabling Global Transaction Identifiers (GTIDs) On-line http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html (step 4)
• Adjust master and slave’s configurations: – Enable ENFORCE_GTID_CONSISTENCY on all server; – Switch GTID mode to OFF_PERMISSIVE; – Switch GTID mode to ON_PERMISSIVE;
master> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE; Query OK, 0 rows affected (0,19 sec)
slave> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE; Query OK, 0 rows affected (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
96
Enabling Global Transaction Identifiers (GTIDs) On-line http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html (steps 5, 6 and 7)
• Adjust master and slave’s configurations: – Enable ENFORCE_GTID_CONSISTENCY on all server; – Switch GTID mode to OFF_PERMISSIVE; – Switch GTID mode to ON_PERMISSIVE; – Wait until all servers finished their anonymous transactions; master> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | Ongoing_anonymous_transaction_count | 0 | +-------------------------------------+-------+ slave> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | Ongoing_anonymous_transaction_count | 0 | +-------------------------------------+-------+
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
97
Enabling Global Transaction Identifiers (GTIDs) On-line http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html (step 8)
• Adjust master and slave’s configurations: – Enable ENFORCE_GTID_CONSISTENCY on all server; – Switch GTID mode to OFF_PERMISSIVE; – Switch GTID mode to ON_PERMISSIVE; – Wait until all servers finished their anonymous transactions; – Switch GTID mode to ON;
master> SET @@GLOBAL.GTID_MODE = ON; Query OK, 0 rows affected (0,21 sec)
slave> SET @@GLOBAL.GTID_MODE = ON; Query OK, 0 rows affected (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
98
Enabling Global Transaction Identifiers (GTIDs) On-line http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html (step 9)
• Adjust master and slave’s configurations: – Enable ENFORCE_GTID_CONSISTENCY on all server; – Switch GTID mode to OFF_PERMISSIVE; – Switch GTID mode to ON_PERMISSIVE; – Wait until all servers finished their anonymous transactions; – Switch GTID mode to ON; – Add “gtid-mode=ON” and “enforce_gtid_consistency=ON” to each server .cnf file; ~/tut5467 $ cat master.cnf [mysqld] ... gtid_mode=ON enforce_gtid_consistency=ON
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
99
Enabling Global Transaction Identifiers (GTIDs) On-line http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html (step 9)
• Stop replication on slave; • Configure the slave to replicate from the master using automatic positions; • Start replication on slave; slave> STOP SLAVE; Query OK, 0 rows affected (0,07 sec) slave> CHANGE MASTER TO MASTER_AUTO_POSITION=1; Query OK, 0 rows affected (0,37 sec) slave> START SLAVE; Query OK, 0 rows affected (0,37 sec) slave> SHOW SLAVE STATUS \G *************************** 1. Slave_IO_State: ... Slave_IO_Running: Slave_SQL_Running: ... Slave_SQL_Running_State: ... Auto_Position: 1 row in set (0,00 sec)
row *************************** Waiting for master to send event Yes Yes Slave has read all relay log; waiting for more updates 1
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
100
Enabling Global Transaction Identifiers (GTIDs) On-line • Execute some queries on the master; master> CREATE TABLE t4 (c1 INT); Query OK, 0 rows affected (0,90 sec) master> INSERT INTO t4 VALUES (1); Query OK, 1 row affected (0,07 sec)
master> DROP DATABASE db3; Query OK, 1 row affected (0,40 sec) master> DROP DATABASE db2; Query OK, 1 row affected (0,27 sec) master> DROP DATABASE db1; Query OK, 1 row affected (0,30 sec) master> SHOW MASTER STATUS; +-------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+------------------------------------------+ | master-bin.000013 | 1053 | | | 4d0afcc1-78d7-11e5-9fe2-f01faf18153b:1-5 | +-------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
101
Enabling Global Transaction Identifiers (GTIDs) On-line • Check the status on the slave side; slave> SELECT THREAD_ID, NAME, PROCESSLIST_STATE FROM performance_schema.threads WHERE NAME LIKE '%slave%'; +-----------+----------------------+--------------------------------------------------------+ | THREAD_ID | NAME | PROCESSLIST_STATE | +-----------+----------------------+--------------------------------------------------------+ | 47 | thread/sql/slave_io | Waiting for master to send event | | 48 | thread/sql/slave_sql | Slave has read all relay log; waiting for more updates | +-----------+----------------------+--------------------------------------------------------+ 2 rows in set (0,07 sec) slave> SELECT SERVICE_STATE, SOURCE_UUID, RECEIVED_TRANSACTION_SET, LAST_ERROR_MESSAGE -> FROM performance_schema.replication_connection_status; +---------------+--------------------------------------+------------------------------------------+--------------------+ | SERVICE_STATE | SOURCE_UUID | RECEIVED_TRANSACTION_SET | LAST_ERROR_MESSAGE | +---------------+--------------------------------------+------------------------------------------+--------------------+ | ON | 4d0afcc1-78d7-11e5-9fe2-f01faf18153b | 4d0afcc1-78d7-11e5-9fe2-f01faf18153b:1-5 | | +---------------+--------------------------------------+------------------------------------------+--------------------+ 1 row in set (0,00 sec) slave> SELECT SERVICE_STATE, LAST_SEEN_TRANSACTION, LAST_ERROR_MESSAGE -> FROM performance_schema.replication_applier_status_by_worker; +---------------+----------------------------------------+--------------------+ | SERVICE_STATE | LAST_SEEN_TRANSACTION | LAST_ERROR_MESSAGE | +---------------+----------------------------------------+--------------------+ | ON | 4d0afcc1-78d7-11e5-9fe2-f01faf18153b:5 | | +---------------+----------------------------------------+--------------------+ 1 row in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
102
Enabling Global Transaction Identifiers (GTIDs) On-line • Inspect master’s binary log file;
master> SHOW BINLOG EVENTS IN 'master-bin.000013' FROM 154; +-------------------+-----+------------+...+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type |...| End_log_pos | Info | +-------------------+-----+------------+...+-------------+-------------------------------------------------------------------+ | master-bin.000013 | 154 | Gtid |...| 219 | SET @@SESSION.GTID_NEXT= '4d0afcc1-78d7-11e5-9fe2-f01faf18153b:1' | | master-bin.000013 | 219 | Query |...| 317 | use `test`; CREATE TABLE t4 (c1 INT) | | master-bin.000013 | 317 | Gtid |...| 382 | SET @@SESSION.GTID_NEXT= '4d0afcc1-78d7-11e5-9fe2-f01faf18153b:2' | | master-bin.000013 | 382 | Query |...| 461 | BEGIN | | master-bin.000013 | 461 | Query |...| 560 | use `test`; INSERT INTO t4 VALUES (1) | | master-bin.000013 | 560 | Xid |...| 591 | COMMIT /* xid=537 */ | | master-bin.000013 | 591 | Gtid |...| 656 | SET @@SESSION.GTID_NEXT= '4d0afcc1-78d7-11e5-9fe2-f01faf18153b:3' | | master-bin.000013 | 656 | Query |...| 745 | DROP DATABASE db3 | | master-bin.000013 | 745 | Gtid |...| 810 | SET @@SESSION.GTID_NEXT= '4d0afcc1-78d7-11e5-9fe2-f01faf18153b:4' | | master-bin.000013 | 810 | Query |...| 899 | DROP DATABASE db2 | | master-bin.000013 | 899 | Gtid |...| 964 | SET @@SESSION.GTID_NEXT= '4d0afcc1-78d7-11e5-9fe2-f01faf18153b:5' | | master-bin.000013 | 964 | Query |...| 1053 | DROP DATABASE db1 | +-------------------+-----+------------+...+-------------+-------------------------------------------------------------------+ 12 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
103
Fail-over with Global Transaction Identifiers • Create a spare server: – Slave of master server, but have its binary log enabled; – Can replace the master in the case of failures;
spare
master
slave
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
104
Fail-over with Global Transaction Identifiers • Create a new server data directory for the “spare”;
~/tut5467 $ mysql-57/bin/mysqld --initialize-insecure --user=$( whoami ) \ --basedir=$HOME/tut5467/mysql-57 \ --datadir=$HOME/tut5467/spare 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 0 [Warning] InnoDB: New log files created, LSN=45790 0 [Warning] InnoDB: Creating foreign key constraint system tables. 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1410c855-79c2-11e5-a868-f01faf18153b. 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
105
Fail-over with Global Transaction Identifiers • Create a configuration file, spare.cnf, with the appropriate options: – Binary log and log slave updates are enabled; – Server id is set; – GTID and enforcing GTID consistency are enabled; ~/tut5467 $ cat spare.cnf [mysqld] server-id=4 log-bin=spare-bin log_slave_updates relay-log=spare-relay-bin log-error=spare.err port=12003 gtid_mode=ON enforce_gtid_consistency=ON
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
106
Fail-over with Global Transaction Identifiers • Start the “spare” server; • Connect to the new “spare” server; ~/tut5467 $ mysql-57/bin/mysqld --defaults-file=$HOME/tut5467/spare.cnf \ --lc-messages-dir=$HOME/tut5467/mysql-57/share \ --datadir=$HOME/tut5467/spare/ ~/tut5467 $ mysql-57/bin/mysql -u root --port 12003 --protocol=tcp --prompt='spare> ' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.9-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. spare>
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
107
Fail-over with Global Transaction Identifiers • Make the master read-only, so that it blocks updates; • Perform the backup;
• No need to take note of the master binary log position!!! • Make the master server writable again;
master> FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON; ~/tut5467 $ mysql-57/bin/mysqldump -u root --host=127.0.0.1 --port=12000 \ --lock-all-tables --all-databases > master-backup.sql master> SET GLOBAL read_only = OFF; UNLOCK TABLES;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
108
Fail-over with Global Transaction Identifiers • Provision the spare; • Configure the spare to connect to the master;
• Start the slave threads on spare; ~/tut5467 $ mysql-57/bin/mysql -u root --host=127.0.0.1 --port=12003 \ < master-backup.sql spare> CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_PORT = 12000, -> MASTER_USER = 'rpl_user', MASTER_PASSWORD = 'secret', -> MASTER_AUTO_POSITION = 1; Query OK, 0 rows affected, 2 warnings (0,65 sec) spare> START SLAVE; Query OK, 0 rows affected (0,21 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
109
Fail-over with Global Transaction Identifiers • Insert two rows into the master; • Stop slave receiver;
• Insert a third row into the master; • Kill the master; • Promote the spare to slave’s master: – Change master on slave pointing to spare (hostname, port, user and password, but no binary log file names or positions!!!); – Restart the receiver on the slave;
spare id1, trx1 id2, trx2 -9 id3, trx3kill master
id1,id2
id1, trx1 id2, trx2 id3, trx3 id3, trx3
slave
id1, trx1 id2, trx2 id3, trx3
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
110
Tip #5: Improving the Slave Scalability: Multi-Threaded Slave (MTS) Applier
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
111
Multi-Threaded Slave (MTS) Applier • Multiple execution threads to apply replication events to the slave(s); • Splits processing between worker threads based on: – Schema (MySQL 5.6+): • SET GLOBAL slave_parallel_type = DATABASE; • Multi-tenant systems friendly;
– Locking-based parallelism (new in MySQL 5.7): • SET GLOBAL slave_parallel_type = LOGICAL_CLOCK; • Intra-schema parallelization; • Based on information collected during master’s commit of the transactions;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
112
Multi-Threaded Slave (MTS) Applier slave_parallel_type = DATABASE Receiver thread
Applier threads
relay log Ev1 Ev2 Ev3 Ev1 Ev2 Ev3 Ev1 Ev2 Ev3
worker 1
db1 trx db2 trx db3 trx
master-bin.000009
Network
worker 2
worker 3 db1 trx
db2 trx
B
slave
db3 trx Receiver Meta-data
Applier Meta-data
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
113
Multi-Threaded Slave (MTS) Applier slave_parallel_type = DATABASE Receiver thread
Applier threads
relay log Ev1 Ev2 Ev3 Ev1 Ev2 Ev3 Ev1 Ev2 Ev3
worker 1
db1 trx db2 trx db3 trx
master-bin.000009
Network
worker 2
worker 3 db1 trx
db2 trx
B
slave
db3 trx Receiver Meta-data
Applier Meta-data
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
114
Multi-Threaded Slave (MTS) Applier slave_parallel_type = DATABASE
• Stop the slave applier only; • Configure the amount of workers to enable MTS; • Monitor workers activities; slave> STOP SLAVE SQL_THREAD; Query OK, 0 rows affected (0,10 sec) slave> SET GLOBAL slave_parallel_workers=3; Query OK, 0 rows affected (0,00 sec)
slave> SELECT * FROM performance_schema.replication_applier_status_by_worker; +--------------+-----------+-----------+---------------+-----------------------+-------------------+...+----------------------+ | CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION | LAST_ERROR_NUMBER |...| LAST_ERROR_TIMESTAMP | +--------------+-----------+-----------+---------------+-----------------------+-------------------+...+----------------------+ | | 1 | NULL | OFF | | 0 |...| 0000-00-00 00:00:00 | | | 2 | NULL | OFF | | 0 |...| 0000-00-00 00:00:00 | | | 3 | NULL | OFF | | 0 |...| 0000-00-00 00:00:00 | +--------------+-----------+-----------+---------------+-----------------------+-------------------+...+----------------------+ 3 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
115
Multi-Threaded Slave (MTS) Applier slave_parallel_type = DATABASE
• Create parallelizable content on the master; master> CREATE DATABASE db1; master> CREATE DATABASE db2; master> CREATE DATABASE db3; master> CREATE TABLE db1.t1 (c1 INT); master> CREATE TABLE db2.t1 (c1 INT); master> CREATE TABLE db3.t1 (c1 INT); master> INSERT INTO db1.t1 SELECT sleep(5); Query OK, 1 row affected, 1 warning (5,10 sec) master> INSERT INTO db2.t1 SELECT sleep(5); Query OK, 1 row affected, 1 warning (5,07 sec) master> INSERT INTO db3.t1 SELECT sleep(5); Query OK, 1 row affected, 1 warning (5,08 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
116
Multi-Threaded Slave (MTS) Applier slave_parallel_type = DATABASE
• Inspect master’s binary log;
master> SHOW BINLOG EVENTS IN 'master-bin.000016' FROM 442; +-------------------+------+------------+...+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type |...| End_log_pos | Info | +-------------------+------+------------+...+-------------+--------------------------------------------------------------------+ | master-bin.000016 | 442 | Gtid |...| 507 | SET @@SESSION.GTID_NEXT= '4d0afcc1-78d7-11e5-9fe2-f01faf18153b:30' | | master-bin.000016 | 507 | Query |...| 581 | BEGIN | | master-bin.000016 | 581 | Query |...| 684 | INSERT INTO db1.t1 SELECT sleep(5) | | master-bin.000016 | 684 | Xid |...| 715 | COMMIT /* xid=35 */ | | master-bin.000016 | 715 | Gtid |...| 780 | SET @@SESSION.GTID_NEXT= '4d0afcc1-78d7-11e5-9fe2-f01faf18153b:31' | | master-bin.000016 | 780 | Query |...| 854 | BEGIN | | master-bin.000016 | 854 | Query |...| 957 | INSERT INTO db2.t1 SELECT sleep(5) | | master-bin.000016 | 957 | Xid |...| 988 | COMMIT /* xid=36 */ | | master-bin.000016 | 988 | Gtid |...| 1053 | SET @@SESSION.GTID_NEXT= '4d0afcc1-78d7-11e5-9fe2-f01faf18153b:32' | | master-bin.000016 | 1053 | Query |...| 1127 | BEGIN | | master-bin.000016 | 1127 | Query |...| 1230 | INSERT INTO db3.t1 SELECT sleep(5) | | master-bin.000016 | 1230 | Xid |...| 1261 | COMMIT /* xid=37 */ | +-------------------+------+------------+...+-------------+--------------------------------------------------------------------+ 12 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
117
Multi-Threaded Slave (MTS) Applier slave_parallel_type = DATABASE
• Start slave applier; • Monitor workers activities;
slave> START SLAVE SQL_THREAD; Query OK, 0 rows affected (0,23 sec) slave> SELECT * FROM performance_schema.replication_applier_status_by_worker; +--------------+-----------+-----------+---------------+-----------------------------------------+-------------------+... | CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION | LAST_ERROR_NUMBER |... +--------------+-----------+-----------+---------------+-----------------------------------------+-------------------+... | | 1 | 44 | ON | 4d0afcc1-78d7-11e5-9fe2-f01faf18153b:32 | 0 |... | | 2 | 45 | ON | 4d0afcc1-78d7-11e5-9fe2-f01faf18153b:31 | 0 |... | | 3 | 46 | ON | 4d0afcc1-78d7-11e5-9fe2-f01faf18153b:30 | 0 |... +--------------+-----------+-----------+---------------+-----------------------------------------+-------------------+... 3 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
118
Multi-Threaded Slave (MTS) Applier slave_parallel_type = LOGICAL_CLOCK
• Leverage parallelization information obtained from the execution on the master: – Transactions that prepare on the same “version” of the database, are assigned the same timestamp;
• Meanwhile, at the slave: – Transactions with overlapping commit window can be executed in parallel; – Concurrent transactions commit independently, thus no waiting involved.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
119
Multi-Threaded Slave (MTS) Applier slave_parallel_type = LOGICAL_CLOCK T1 and T2 execute in parallel on the slave.
Concurrent Execution History on the Master T1
T2 and T3 execute in parallel on the slave.
T2 T3
Time Execution Commit
Commit Finishes Last Lock Acquired
• T2 is scheduled to execute together with T1. • T3 is scheduled to execute as soon as T1 finishes.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
120
Tip #6: BGC tuning options – MTS Enhancers
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
121
BGC tuning options – MTS Enhancers • Group Commit runs in three stages: S
– FLUSH, SYNC, COMMIT;
L
S FLUSH QUEUE
• Multiple sessions (S) in the pipeline;
F
L
• For each group: – A session acts as the leader (L); – Several other sessions are followers (F);
• The leader does all the work, followers wait;
F
F
SYNC QUEUE
F
L COMMIT QUEUE
Group Commit Procedure Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
122
BGC tuning options – MTS Enhancers • FLUSH stage leader continues to flush while there are transactions in the queue;
F S
L
S FLUSH QUEUE
F
L
F
SYNC QUEUE
F
L COMMIT QUEUE
Group Commit Procedure Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
123
BGC tuning options – MTS Enhancers • Options to tweak the pipeline:
F
– binlog_group_commit_sync_delay: • Introduce an artificial delay in the SYNC stage to allow more sessions to be grouped; • Default is 0; – binlog_group_commit_sync_no_delay_count: • Define a limit in the group size; • Once reaching the defined parameter, the group is considered complete regardless of sync delay option; • Default is 0;
S
L
S FLUSH QUEUE
F
L
F
SYNC QUEUE
F
L COMMIT QUEUE
Group Commit Procedure Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
124
BGC tuning options – MTS Enhancers • Tip: set binlog_group_commit_sync_delay > 0: – More sessions being grouped together may call storage procedures less often and with higher granularity; – On the slave side, more sessions being grouped together means more opportunity to obtain benefits using LOGICAL_CLOCK based MTS;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
125
Tip #7: Multi-Source Replication
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
126
Multi-Source Replication • New feature in MySQL 5.7; • A server (slave) can replicate from multiple sources (masters);
• Each source should be configured in individual channels; • Each channel: – Is identified by a channel_name; – Has its own set of thread (receiver and applier(s)) and its own relay log; – Can be started, stopped of configured individually; – Has its own multi-threaded applier set of threads;
• Requires: storing replication metadata in tables; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
127
Setting up a new master server, named ‘partner’ • Create a new master data directory;
~/tut5467 $ mysql-57/bin/mysqld --initialize-insecure --user=$( whoami ) \ --basedir=$HOME/tut5467/mysql-57 \ --datadir=$HOME/tut5467/partner 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 0 [Warning] InnoDB: New log files created, LSN=45790 0 [Warning] InnoDB: Creating foreign key constraint system tables. 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 34259f02-79a5-11e5-bbfa-f01faf18153b. 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
128
Setting up a new master server, named ‘partner’ • Create a configuration file, partner.cnf, with the appropriate options: – Binary log is enabled; – Server id is set; – GTID enabled; – Enforcing GTID consistency; ~/tut5467 $ cat partner.cnf [mysqld] server-id=3 log-bin=partner-bin log-error=partner.err port=12002 gtid_mode=ON enforce_gtid_consistency=ON
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
129
Setting up a new master server, named ‘partner’ • Start the server; • Connect to the new master; ~/tut5467 $ mysql-57/bin/mysqld --defaults-file=$HOME/tut5467/partner.cnf \ --lc-messages-dir=$HOME/tut5467/mysql-57/share \ --datadir=$HOME/tut5467/partner/ ~/tut5467 $ mysql-57/bin/mysql -u root --port 12002 --protocol=tcp --prompt='partner> ' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.9-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. partner>
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
130
Setting up a new master server, named ‘partner’ • Create an user to be used by replication; • Set up slave privileges for the user;
partner> CREATE USER `rpl_partner`@`localhost`; Query OK, 0 rows affected (0,01 sec) partner> GRANT REPLICATION SLAVE ON *.* -> TO `rpl_partner`@`localhost` -> IDENTIFIED BY 'secret'; Query OK, 0 rows affected, 1 warning (0,01 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
131
Multi-Source Replication
master
slave
partner
• Configure a new replication channel on slave; • Start the slave thread for the ‘partner’ channel;
slave> CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_PORT = 12002, -> MASTER_USER = 'rpl_partner', MASTER_PASSWORD = 'secret', -> MASTER_AUTO_POSITION = 1 FOR CHANNEL 'partner'; Query OK, 0 rows affected, 2 warnings (0,68 sec) slave> START SLAVE FOR CHANNEL 'partner'; Query OK, 0 rows affected (0,21 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
132
Multi-Source Replication
master
slave
partner
• Check replication status for the new channel; slave> SHOW SLAVE STATUS FOR CHANNEL 'partner' \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: rpl_partner Master_Port: 12002 ... Master_Log_File: partner-bin.000001 Read_Master_Log_Pos: 665 ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Master_Server_Id: 3 Master_UUID: 34259f02-79a5-11e5-bbfa-f01faf18153b ... Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates ... Retrieved_Gtid_Set: 34259f02-79a5-11e5-bbfa-f01faf18153b:1-2 Executed_Gtid_Set: 34259f02-79a5-11e5-bbfa-f01faf18153b:1-2,4d0afcc1-78d7-11e5-9fe2-f01faf18153b:1-5 Auto_Position: 1 ... Channel_Name: partner 1 row in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
133
Multi-Source Replication
master
slave
partner
• Testing replication between partner and slave; partner> CREATE DATABASE dbp; Query OK, 1 row affected (0,03 sec) partner> USE dbp; Database changed partner> CREATE TABLE t1 (a INT); Query OK, 0 rows affected (0,42 sec) slave> USE dbp; Database changed slave> SHOW TABLES; +---------------+ | Tables_in_dbp | +---------------+ | t1 | +---------------+ 1 row in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
134
Tip #8: Better Replication Monitoring
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
135
Better Replication Monitoring • SHOW SLAVE STATUS: – Returns 56 columns per row (and we often want just a few), one row per channel; – Mix receiver and applier statuses; – Do not have information about MTS workers;
• MySQL 5.7 introduced the Performance Schema Replication Tables: – Access monitoring information through SQL interface; – Write stored functions or procedures with input from replication internals; – Logically unrelated information into different places; – Flexible and easier to extend and adapt as new feature get into the server; – More user friendly names identifying the monitoring fields; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
136
Better Replication Monitoring (Slave) Receiver and Applier Status
Connection Configuration
Connection Status
Applier Configuration
Applier Status
Applier / Coordinator Status
Workers Status
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
137
Better Replication Monitoring • Check all replication channels receivers statuses; • Ensure that all replication channels are using GTID based auto positioning; slave> SELECT CHANNEL_NAME, SERVICE_STATE, LAST_HEARTBEAT_TIMESTAMP, RECEIVED_TRANSACTION_SET -> FROM performance_schema.replication_connection_status; +--------------+---------------+--------------------------+------------------------------------------+ | CHANNEL_NAME | SERVICE_STATE | LAST_HEARTBEAT_TIMESTAMP | RECEIVED_TRANSACTION_SET | +--------------+---------------+--------------------------+------------------------------------------+ | | ON | 2015-10-23 18:22:04 | 4d0afcc1-78d7-11e5-9fe2-f01faf18153b:1-5 | | partner | ON | 2015-10-23 18:21:44 | 34259f02-79a5-11e5-bbfa-f01faf18153b:1-4 | +--------------+---------------+--------------------------+------------------------------------------+ 2 rows in set (0,00 sec) slave> SELECT CHANNEL_NAME, AUTO_POSITION -> FROM performance_schema.replication_connection_configuration; +--------------+---------------+ | CHANNEL_NAME | AUTO_POSITION | +--------------+---------------+ | | 1 | | partner | 1 | +--------------+---------------+ 2 rows in set (0,00 sec)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
138
Program Agenda 1
MySQL Replication 101
2
Replication Basics
3
The Binary Log
4
Tips
5
Tricks
6
Summary Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
139
Trick #1: Remote Backup of Binary Logs
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
140
Remote Backup of Binary Logs • mysqlbinlog can: – Connect to a remote master and retrieve binary logs on-line; – Output in raw format;
• Thence, it can act as a “slave” and save a copy of the binary logs locally in the disk;
• As other MySQL client programs, the mysqlbinlog can connect to the remote master using SSL; ~/tut5467 $ mysql-57/bin/mysqlbinlog --raw --read-from-remote-server \ --stop-never --host --port \ -u -p
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
141
Trick #2: Slave Delayed Replication
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
142
Slave Delayed Replication • The slave can delay the process of applying transactions for a configurable delay; • If any statement on the master is wrongly executed (e.g., wrong drop database), then the user can go to the slave and recover quickly from an old snapshot in time;
slave> STOP SLAVE;
slave> CHANGE MASTER TO MASTER_DELAY = 20; slave> START SLAVE;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
143
Trick #3: Replication Filters
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
144
Replication Filters • MySQL 5.7 introduced CHANGE REPLICATION FILTER: – Sets one or more replication filtering rules on the slave without requiring restarting the server to take effect; – Need only that the slave applier be stopped using STOP SLAVE SQL_THREAD first (and restarted with START SLAVE SQL_THREAD afterwards);
slave> CHANGE REPLICATION FILTER -> REPLICATE_DO_DB = (d1), -> REPLICATE_IGNORE_DB = (d2);
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
145
Program Agenda 1
MySQL Replication 101
2
Replication Basics
3
The Binary Log
4
Tips
5
Tricks
6
Summary Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
146
Summary • This Tutorial demonstrated how to get started with replication; • It presented tips regarding the MySQL replication features. It highlighted: – Crash-Safe Replication; – Optimized Row-based Replication; – Global Transaction Identifiers (GTID); – Multi-Threaded Slave and Binlog Group Commit; – Multi-Source Replication; – Replication Monitoring;
• Finally, it presented some tricks based on MySQL 5.7 replication features.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
147
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
148
Keynote: Monday, 4.00-6.00 pm, YBCA Theater • State of the Dolphin • Rich Mason, SVP & General Manager MySQL GBU, Oracle • Tomas Ulin, VP MySQL Engineering, Oracle
• Customer Experiences
Hari Tatrakal, Director of Database Services, Live Nation Olaniyi Oshinowo, MySQL & Open Source Technologies Leader, GE Ernie Souhrada & Rob Wultsch, Database Engineers, Pinterest Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
149
MySQL Community Reception @ Oracle OpenWorld Celebrate, Have Fun and Mingle with Oracle’s MySQL Engineers & Your Peers
• Tuesday, October 27: 7 pm • Jillian’s at Metreon: 175 Fourth Street, San Francisco At the corner of Howard and 4th st.; only 2-min walk from Moscone Center (same place as last year)
Join us! Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
150
Keep Learning with Oracle University
Classroom Training
Cloud
Learning Subscription
Technology
Live Virtual Class
Applications
Training On Demand
Industries
education.oracle.com Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
151
Oracle University MySQL Training Services Enable Reliable and High-Performance Web-Based Database Applications in Your Organization MySQL Cluster Training – Available NOW! Learn how to install and configure an open source, ACID-compliant transactional database designed to deliver real-time in-memory performance and 99.999% availability. Find out more at: education.oracle.com/MySQL MySQL Student Quotes: • “Slides are excellent. I was so happy to get so much detail. The information was relevant and written in a way that was easy to understand and absorb.” • “The prepared scripts to run for executing testing was very nice to have.” • “The course content was amazing and the instructor's real-world solutions really helped.”
Premier Support customers eligible to save 20% on learning credits.
Benefits Expert-led training to support your MySQL learning needs
Flexibility to train in the classroom or online Hands-on experience to gain real world experience Key skills needed for database administrators and developers
Top Courses for Administrators and Developers MySQL for Beginners MySQL for Database Administrators MySQL Performance Tuning MySQL Cluster MySQL and PHP - Developing Dynamic Web Applications MySQL for Developers MySQL Developer Techniques
Top Certifications MySQL 5.6 Database Administrator MySQL 5.6 Developer To find out more about available MySQL Training & Certification offerings, go to: education.oracle.com/mysql Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
152
Session Surveys Help us help you!! • The organizing committee OW would like to invite you to take a moment to give us your session feedback. Your feedback will help us to improve your conference. • Please be sure to add your feedback for your attended sessions by using the Mobile Survey or in Schedule Builder.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Central @ Oracle Open World 2015 - San Francisco
153
