Reliable MySQL Using Replication

1. Reliable MySQL Using Replication Issac Goldstand Mirimar Networks www.mirimar.net margol@beamartyr.net

2. What is replication? • Allows 2 or more databases to maintain state between them • MySQL 3.23 and up supports asynchronous replication • One server acts as a master for one or more slaves • Slaves pull data to be replicated from server’s binary logs and execute the relevant statements locally

3. Master Slave Slave Slave Slaves Replication basics… • One master supports multiple slaves • Each node on the network is assigned a unique identifying number • Each slave attempts to connect to the master and fetches data to be replicated

4. Master Slave Client Replication basics… DATA DATA INSERT INTO … • Clients perform data modification on master server • INSERT, SELECT, DELETE, LOAD DATA • EXECUTE in MySQL 5.0 and above

5. Master Slave Client Replication basics… DATA DATA Binary Log INSERT INTO … • Immediately following execution of command on master, the command is written to the local binary log • Additionally, the master records its unique ID (to prevent endless loops in circular replication scenarios) and the timestamp for use with statements which use NOW(), etc.

6. Master Slave Client Replication basics… DATA DATA INSERT INTO … Replication Thread INSERT INTO … Relay Log • If the slave is online, the command is transmitted to the slave in parallel (well, immediately following) to being written in the local binary log • Otherwise, when the slave next connects it will receive a list of all pending statements from the master server’s binary log • The slave’s replication IO thread stores the command in the local relay log

7. Master Slave Client Replication basics… DATA DATA DATA INSERT INTO … Replication Thread INSERT INTO … Relay Log • Once the data is received in the slave’s relay log, the slave SQL thread executes the command locally, bringing the slave up-to-date with the master • In MySQL 3.23, the IO and SQL threads were just one thread. In later versions this was changed to boost performance

8. Replication Strategies • Load balancing – single write, distributed read

9. Client INSERT INTO… Client INSERT INTO … Replication Strategies • Load balancing – single write, distributed read • Load balancing – circular read/write

10. Client Master Slave Replication Strategies • Load balancing – single write, distributed read • Load balancing – circular read/write • High availability (hot failover) DATA DATA DATA DATA DATA

11. Replication Strategies • Load balancing – single write, distributed read • Load balancing – circular read/write • High availability (hot failover) • Snapshot backups

12. Simple Replication Setup • Modify my.cnf to include a unique server-id for each node • On master server, ensure that log-bin (binary logging) is enabled in my.cnf • On slave, configure login credentials on master, either via my.cnf or CHANGE MASTER TO statement • Copy initial data snapshot from master to slave • Configure initial binary log position on slave • Start replication with SLAVE START command

13. Configure master my.cnf ------------- [mysqld] server-id = 1 log-bin

14. Configure slave my.cnf ------------- [mysqld] server-id = 2 master-user = someuser master-password = secret master-host = ip.of.master

15. Initial dataset • Binary log provides a record of all modifications to master database starting from a fixed point: when binary logging was activated • If all binary logs exist on master from initial install of MySQL, the slave(s) can use these to bring themselves up-to-date • Otherwise, a snapshot of the master must be taken, using mysqldump –master-data, to provide an initial dataset for the slave(s) • If only MyISAM tables are used, the LOAD DATA FROM MASTER statement may be used on the slave(s)

16. Configure log position MASTER mysql> SHOW MASTER STATUS; +---------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------------------+----------+--------------+------------------+ | vmware-mirimar-bin.000002 | 79 | | | +---------------------------+----------+--------------+------------------+ SLAVE mysql> CHANGE MASTER TO MASTER_LOG_FILE=‘vmware-mirimar-bin.000002’, MASTER_LOG_POS=79; SLAVE mysql> START SLAVE;

17. Using CHANGE MASTER TO • MASTER_HOST • MASTER_USER • MASTER_PASSWORD • MASTER_LOG_FILE • MASTER_LOG_POS

18. Advanced CHANGE MASTER TO • MASTER_PORT • RELAY_LOG_FILE • RELAY_LOG_POS • MASTER_SSL • SSL: CA/CAPATH/CERT/KEY/CIPHER

19. Confirming it works SLAVE mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: vmware-mirimar Master_User: someuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: vmware-mirimar-bin.000002 Read_Master_Log_Pos: 79 Relay_Log_File: vmware1-mirimar-relay-bin.000002 Relay_Log_Pos: 250 Relay_Master_Log_File: vmware-mirimar-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 79 Relay_Log_Space: 250 Seconds_Behind_Master: 0

20. Permissions • Slaves need REPLICATION SLAVE permission on master for basic usage • If LOAD TABLE FROM MASTER or LOAD DATA FROM MASTER statements are used, slave will also need SUPER and RELOAD privileges

21. Internal Threads • Since MySQL 4.0, replication slaves run two threads • IO thread continuously receives updates from master and writes to local relay log • SQL thread continuously executes statements in relay log

22. IO thread isolation • Isolating IO thread means that slave won’t have to wait for long-executing statements to finish executing before retrieving data from master • Also, slave will continue reading data from master if a statement creates a data conflict

23. SQL thread isolation • SQL thread isolation allows for replication in an environment without a continuous link between slave and masters • If master fails (or slave simply has no access), the IO thread will try to reconnect endlessly (waiting 60 seconds between attempts) • SQL thread will continue processing relay logs even while IO thread is unable to connect to master

24. Master Thread • Additionally, the master server runs the Binlog Dump thread • This thread is simply dedicated to scanning the binary logs on the master and sending updates to the connected slave • If this thread isn’t running, it means that replication isn’t running – more accurately, that no slaves are currently connected

25. Status files • 2 status files for replication’s use • Their use is to record the state of replication between server shutdown and startup • master.info records information about the slave’s master server • relay-log.info records information about the local relay logs

26. Information in master.info • Master log file • Read master log pos • Master Host • Master User • Password (will not be shown in SHOW SLAVE STATUS) • Master Port • Connect Retry • In MySQL 4.1+, SSL options are stored if SSL is used

27. Information in relay-log.info • Relay log file • Relay log pos • Relay master-log pos • Exec master-log pos

28. Backup master • Master backups can be accomplished with mysqldump • Care must be taken to ensure the following 2 special considerations: • Consistent snapshot of master date (via lock tables for MyISAM or single transaction for InnoDB) • Recording of binary log information, for use on slaves (master-data)

29. Backup master files • If a file-system level backup is required, care should be taken to manually record binary log name and position via SHOW MASTER STATUS statement. • To ensure consistency between backup and binary log position, the tables should be locked via FLUSH TABLES WITH READ LOCK immediately before backup (and SHOW MASTER STATUS) • LEAVE THE CLIENT CONNECTED!!! • After backup finishes, execute UNLOCK TABLES to release the read lock

30. Backup slave • Same idea as master file system backup • Instead of recording position, it’s enough to backup the master.info and relay-log.info files • Instead of acquiring global read lock, it’s enough to STOP SLAVE before backup and START SLAVE once backup finishes

31. Live demo • Time permitting, we’ll show a short demonstration of a simple unidirectional replication setup

32. For more information • MySQL documentation • 5.0 documentation http://mirror.mirimar.net/mysql/doc/refman/5.0/en/replication.html • 4.1 documentation http://mirror.mirimar.net/mysql/doc/refman/4.1/en/replication.html

33. Thank You! For more information: Issac Goldstand margol@mirimar.net http://www.beamartyr.net/ http://www.mirimar.net/