mysql high availability
DESCRIPTION
MySQL HA slides...TRANSCRIPT
![Page 2: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/2.jpg)
MYSQL HA
• Possible HA solutions with MySQL
• replications
• clusters
• Combinations :: Third party solutions
• LVS/Heartbeat
• DRBD
![Page 3: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/3.jpg)
MYSQL REPLICATION
![Page 4: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/4.jpg)
MYSQL :: REPLICATION
• Natively support ONE WAY - ASYNCHRONOUS replication
• One Master + N number of Slaves
• Asynchronous ::
• does not take place in real time
• no guarantee data from master replicated to slaves
![Page 5: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/5.jpg)
MYSQL :: REPLICATION
• Semi-Synchronous Replication :: MySQL 5.5+
• Master waits until at least on semi-synchronous slave acknowledges a commit transaction
• slave acknowledge only after event has been written to relay log and flushed to disk
• if timeout occurs without any semi-synchronous slave acknowledgment - master reverts to asynchronous
![Page 6: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/6.jpg)
MYSQL :: REPLICATION
• Semi-Synchronous Replication :: MySQL 5.5+
• when one semi-synchronous slave catch-up - master returns to semi-synchronous
• semi-synchronous must be enabled on both master and slave sides
• missing / not enabled on either master or at least one slave - master uses asynchronous replication
![Page 7: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/7.jpg)
MYSQL :: REPLICATION
• With Asynchronous Replication
• master writes event to its binary log
• slave request them when they are ready
• no guarantee any event will ever reach any slave
![Page 8: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/8.jpg)
MYSQL :: REPLICATION
• With Fully Synchronous Replication
• master commits a transaction
• all slaves also will have to committed the transaction before the master return to the session that perform the transaction
• might be a lot of delay to complete a transaction
![Page 9: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/9.jpg)
MYSQL :: REPLICATION
• With Semi-Synchronous Replication - is in between asynchronous and fully synchronous
• master commits a transaction
• wait for at least one slave acknowledge the transaction
• no need to wait for all slaves acknowledgment
![Page 10: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/10.jpg)
MYSQL :: REPLICATION
• Asynchronous
• High Speed
• Less Data Integrity
![Page 11: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/11.jpg)
MYSQL :: REPLICATION
• Fully Synchronous
• Less Speed
• Higher Data Integrity
![Page 12: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/12.jpg)
MYSQL :: REPLICATION
• Semi - Synchronous
• Speed
• Data Integrity
![Page 13: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/13.jpg)
MYSQL :: REPLICATION
• Advantages
• operate across different platforms
• in asynchronous mode
• you can start or stop at any time
• suitable over slower links or partial links
• across geographical boundaries - DRC, etc
![Page 14: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/14.jpg)
MYSQL :: REPLICATION
• Advantages
• one master - many slaves
• suitable for read intensive application such as Web Services by spreading load across multiple servers
![Page 15: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/15.jpg)
MYSQL :: REPLICATION
• Disadvantages
• data can only be written to master
• no guarantee master and slaves is consistent at a given point of time
• asynchronous - small delay
• application must be replication - aware (write only to master, and read from slaves)
![Page 16: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/16.jpg)
MYSQL :: REPLICATION
• Recommended Uses
• Scale-out (horizontal scaling) solutions that require large number of reads but fewer writes
• Logging/data analysis of live data
• by replicating to slave, will not disturb/degrade/affecting master operation
![Page 17: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/17.jpg)
MYSQL :: REPLICATION
• Recommended Uses
• Online backup (availability)
• Offline backup
• after a while having a reliable snapshots, take the slave down
• run again to catch up
![Page 18: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/18.jpg)
MYSQL :: REPLICATION
• How It Works
• master writes updates to its binary log files
• serve as record of updates to be sent to slave
• slave connects to master
• determines last position of last successful update
• receives new updates taken since last update
![Page 19: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/19.jpg)
MYSQL :: REPLICATION
![Page 20: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/20.jpg)
MYSQL - REPLICATIONLAB EXERCISE
![Page 21: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/21.jpg)
MYSQL :: REPLICATION
![Page 22: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/22.jpg)
MYSQL :: REPLICATION
• # mkdir /etc/mysql
• /etc/mysql/master.cnf
• # mysql_install_db --defaults-file=/etc/mysql/master.cnf
• # mysqld --defaults-file=/etc/mysql/master.cnf &
[mysqld]server-id=1log-bin=black-bin.logdatadir=/home/mysql/master/datasync_binlog=1user=mysql
![Page 23: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/23.jpg)
MYSQL :: REPLICATION
• # mysql -u root
mysql> CREATE USER [email protected]> GRANT REPLICATION SLAVE ON -> *.* TO [email protected] -> IDENTIFIED BY ‘password’;
![Page 24: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/24.jpg)
MYSQL :: REPLICATION
• # mkdir /etc/mysql
• /etc/mysql/slave.cnf
• # mysqld --defaults-file=/etc/mysql/slave.cnf &
[mysqld]server-id=2master-host=192.168.0.31master-user=replmaster-password=passwordrelay-log-index=slave-relay-bin.indexrelay-log=slave-relay-bindatadir=/home/mysql/slave/data
![Page 25: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/25.jpg)
MYSQL :: REPLICATION
• # mysql -u root
• replication error at this point, why?
• binlog-ignore-db=dbname
• exclude database from replication
mysql> start slave;mysql> show slave status\G
![Page 26: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/26.jpg)
MYSQL :: REPLICATION
• mysql> show slave status\G Slave_IO_Running: YesSlave_SQL_Running: No...... Last_Error: Error ‘Duplicate entry’...
![Page 27: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/27.jpg)
MYSQL :: REPLICATION
• purging slave relay logmysql> stop slave;mysql> reset slave;mysql> show slave status\G
...Read_Master_Log_Pos: 4...
![Page 28: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/28.jpg)
MYSQL :: REPLICATION
• purging master binary log
• look at master position value
mysql> reset master;
mysql> show master status;
![Page 29: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/29.jpg)
MYSQL :: REPLICATION
• restart slave - reconnect to mastermysql> start slave;mysql> show slave status\G
...Read_Master_Log_Pos: 98... Slave_IO_Running: Yes Slave_SQL_Running: Yes...
![Page 30: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/30.jpg)
MYSQL :: REPLICATION
• testing the replication - at master
# mysql -u root
mysql> create database dummy;mysql> use dummy;mysql> create table profile (id int(3), name varchar(30));mysql> insert into profile values (1,‘Abdullah’);mysql> insert into profile values (2,‘John Doe’);mysql> select * from profile;
![Page 31: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/31.jpg)
MYSQL :: REPLICATION
• testing the replication - at slave
# mysql -u root
mysql> show databases;mysql> use dummy;mysql> show tables;mysql> select * from profile;
![Page 32: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/32.jpg)
MYSQL :: REPLICATION
• testing the replication - at slave# rm -rf /home/mysql/slave/data/dummy
# mysql -u root
mysql> flush tables;mysql> show databases;mysql> show slave status\Gmysql> stop slave;mysql> change master to master_log_pos=98;mysql> start slave;mysql> show slave status\Gmysql> show databases;mysql> use dummy;mysql> select * from profile;
![Page 33: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/33.jpg)
MYSQL :: REPLICATION
• at master - mysqlbinlog command can be used to view master binary log and position related
• purging binary log
• oldest kept log
# mysqlbinlog black-bin.000001 | more
# mysql -pmysql> purge master logs to “black-bin.000201”;
![Page 34: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/34.jpg)
MYSQL :: REPLICATION
• Exercise
• add another slave to master
• set server-id=3
• make sure at master grant permission to same replication user but with different IP address
![Page 35: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/35.jpg)
MYSQL :: REPLICATION
• How to recover in this kind of situation?
• master crashed
• you are running a single slave
• slave used as read and write during master recovery
• master server now ready - how to sync?
![Page 36: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/36.jpg)
MYSQL :: REPLICATION
• Conclusion from this exercise
• learn how to create a simple master-slave replication
• understand how mysql replication work internally via binary log
• how to recover when master or slave fails
![Page 37: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/37.jpg)
MYSQL :: REPLICATION
Master => Slave
![Page 38: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/38.jpg)
MYSQL :: REPLICATION
Master => Slaves
![Page 39: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/39.jpg)
MYSQL :: REPLICATION
Master => Slave => Slaves
![Page 40: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/40.jpg)
MYSQL :: REPLICATION
Master => Slave (Multi-Source)
![Page 41: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/41.jpg)
MYSQL :: REPLICATION
Master => Master
![Page 42: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/42.jpg)
MYSQL :: REPLICATION
Circular (Multi-Master)
![Page 43: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/43.jpg)
• In Multi-Master Environment
• Never Load Balance Multi Master
• Only Write to Single Master, in case of failure failover to another master. Never write to multiple master
• possibilities out of sync
• no “conflict resolution” in MySQL replication
MYSQL :: REPLICATION
![Page 44: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/44.jpg)
MYSQL :: REPLICATION
• Configuring a Slave as “Master” ready
• make sure log-bin configured in configuration file
• ready any time for slave connection
• create user and grant acess
[mysqld]...log-bin=black-bin.logsync_binlog=1...
![Page 45: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/45.jpg)
AUTOMATIC FAILOVERWITH MYSQL REPLICATION
![Page 46: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/46.jpg)
AUTOMATIC FAILOVERINTRODUCTION TO LINUX VIRTUAL SERVER (LVS)
• project started 1998
• mission to provide high scaleability, reliability and serviceability
• advanced IP load balancing software
• included in standard kernel since 2.4
• used to build high available and scaleable network services
• web, email, media services, VOIP, etc
![Page 47: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/47.jpg)
AUTOMATIC FAILOVERINTRODUCTION TO LINUX VIRTUAL SERVER (LVS)
• 3 types of LVS load balancing
• Network Address Translation (NAT)
• IP Tunneling
• redirecting to a different IP address
• Direct Routing
![Page 48: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/48.jpg)
AUTOMATIC FAILOVERINTRODUCTION TO LINUX HEARTBEAT
• Using Linux HA project also known as Linux Heartbeat
• implements a heartbeat protocol
• message sent at a regular interval to one or more nodes
• not received - node assumed failed
• trigger a fail over action
![Page 49: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/49.jpg)
AUTOMATIC FAILOVERINTRODUCTION TO LINUX HEARTBEAT
• Heartbeat Method/Medium
• serial interface (/dev/ttyS[0-9])
• broadcast
• multicast - group (224.0.0.0 - 239.0.0.0)
• unicast - udp
![Page 50: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/50.jpg)
AUTOMATIC FAILOVERUSING LINUX LVS/HEARTBEAT + MYSQL
Linux LVS / Heartbeat
virtual IP 192.168.0.1
Replication
Master Slave
192.168.0.31 192.168.0.34
Linux LVS
![Page 51: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/51.jpg)
AUTOMATIC FAILOVERUSING LINUX LVS/HEARTBEAT + MYSQL
Linux LVS / Heartbeat
virtual IP 192.168.0.1
Replication
Master Slave
192.168.0.31 192.168.0.34
Linux LVS + Heartbeat (HA)
![Page 52: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/52.jpg)
AUTOMATIC FAILOVERUSING LINUX LVS/HEARTBEAT + WEB + MYSQL
DB/MySQL
virtual IP 192.168.0.1
Replication
Master Slave
Linux LVS + Heartbeat (HA)
HTTP/WEB
virtual IP 192.168.0.2
DB Server :192.168.0.1
![Page 53: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/53.jpg)
AUTOMATIC FAILOVERLAB EXERCISE
USING LVS DIRECT ROUTING
![Page 54: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/54.jpg)
AUTOMATIC FAILOVERUSING LINUX LVS/HEARTBEAT - DIRECT ROUTING
LVS Server IP192.168.0.101
LVS Server IP192.168.0.102
Heart Beat IP172.16.0.101
Heart Beat IP172.16.0.102
DB Virtual IP192.168.0.1
Replication
Master Slave
Master DB IP192.168.0.11
NO ARP lo:0 IP192.168.0.1
Slave DB IP192.168.0.12
NO ARP lo:0 IP192.168.0.1
DB Virtual IP192.168.0.1
Active LVS Passive LVS
DB Connectionrequest viaVirtual IP
192.168.0.1
DB Server Reply Directly to Connection/Requestor
by passing LVS
![Page 55: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/55.jpg)
AUTOMATIC FAILOVERCONFIGURING HEARTBEAT
• yum install heartbeat
• /etc/ha.d/
• resource.d/
• ha.cf
• authkeys
• haresources
![Page 56: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/56.jpg)
AUTOMATIC FAILOVERCONFIGURING HEARTBEAT
• Heartbeat medium : : broadcast
• /etc/ha.d/ha.cf
# udpport port# bcast devudpport 694bcast eth0
![Page 57: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/57.jpg)
AUTOMATIC FAILOVERCONFIGURING HEARTBEAT
• Heartbeat medium : : multicast
• /etc/ha.d/ha.cf# mcast [dev] [mcast group] [port] [ttl] [loop]# # [dev] device to send/recv heartbeat on# [mcast group] multicast group to join - Class D# [port] udp port to sendto/recvfrom# [ttl] ttl value for outbound heartbeat# how far multicast packet to propogate# [loop] toggle loopback for outbound multicast# heartbeats - loopback to interface it # was sent on
mcast eth0 225.0.0.1 694 1 0
![Page 58: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/58.jpg)
AUTOMATIC FAILOVERCONFIGURING HEARTBEAT
• Heartbeat medium : : unicast
• /etc/ha.d/ha.cf# ucast [dev] [peer-ip-addr]# # [dev] device to send/recv heartbeat on# [peer-ip-addr] IP address of peer to send packets to
udpport 694ucast eth0 172.16.0.102
![Page 59: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/59.jpg)
AUTOMATIC FAILOVERCONFIGURING HEARTBEAT
• /etc/ha.d/ha.cf :: main heartbeat configuration file
debugfile /var/log/ha-debuglogfile /var/log/ha-loglogfacility local0keepalive 2deadtime 5# udpport 694# bcast eth0mcast eth0 225.0.0.1 694 1 0# ucast eth0 172.16.0.102auto_failback onnode primemaster.xjutsu.comnode secondmaster.xjutsu.com
![Page 60: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/60.jpg)
AUTOMATIC FAILOVERCONFIGURING HEARTBEAT
• /etc/ha.d/authkeys :: authentication method and password type/password between heartbeat nodes
• chmod 600 authkeys
auth 11 md5 secretpassword
![Page 61: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/61.jpg)
AUTOMATIC FAILOVERCONFIGURING HEARTBEAT
• /etc/ha.d/haresources :: node resources monitored/loaded
# node-name resource1::options resourceN::options# # node-name: from uname -n# resource: managed - script refer to /etc/ha.d/resources.d# options: parameters/arguments passed to resource script
primemaster.xjutsu.com ldirectord::ldirectord.cf \ IPaddr2::192.168.0.1/24/eth0:0/192.168.0.255
![Page 62: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/62.jpg)
AUTOMATIC FAILOVERCONFIGURING LDIRECTORD - LOAD BALANCER
• /etc/ha.d/ldirectord.cfchecktimeout=10checkinterval=20autoreload=yeslogfile=”/var/log/ldirectod.log”quiescent=yesvirtual=192.168.0.1:3306 fallback=192.168.0.12:3306 gate real=192.168.0.11:3306 gate service=mysql scheduler=wlc protocol=tcp checktype=connect## man ipvsadm for detail scheduler options
![Page 63: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/63.jpg)
AUTOMATIC FAILOVERCONFIGURING LDIRECTORD - LOAD BALANCER
• The linux directors must be able to route traffics to real server
• by enabling IPV4 packet forwarding
• /etc/sysctl.conf
net.ipv4.ip_forward = 1
# sysctl -p
![Page 64: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/64.jpg)
AUTOMATIC FAILOVERCONFIGURING REAL MYSQL SERVERS (MASTER/SLAVE)
• ip aliasing on localhost device
• interface lo:0 (/etc/sysconfig/netwrok-scripts/ifcfg-lo:0)
DEVICE=lo:0IPADDR=192.168.0.1NETMASK=255.255.255.255ONBOOT=yesNAME=loopback
![Page 65: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/65.jpg)
AUTOMATIC FAILOVERCONFIGURING REAL MYSQL SERVERS (MASTER/SLAVE)
• disable ARP broadcast
• /etc/sysctl.confnet.ipv4.conf.all.arp_ignore = 1net.ipv4.conf.eth0.arp_ignore = 1net.ipv4.conf.all.arp_announce = 2net.ipv4.conf.eth0.arp_announce = 2
# sysctl -p# ifup lo:0
![Page 66: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/66.jpg)
AUTOMATIC FAILOVERLDIRECTORD + HEARTBEAT
• starting the service
• /etc/init.d/heartbeat [start, stop, restart]
• ifconfig
• virtual interface eth0:0 automatically initialized
• ipvsadm -L
• list virtual server table
![Page 67: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/67.jpg)
HIGH AVAILABILITYWITH MYSQL REPLICATION + DRBD
![Page 68: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/68.jpg)
HIGH AVAILABILITYWITH MYSQL REPLICATION + DRBD
• Distributed Replicated Block Device (DRBD)
• mirroring a device via network
• network based raid 1
• integrated into linux kernel starting from 2.6.33
![Page 69: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/69.jpg)
HIGH AVAILABILITYWITH MYSQL REPLICATION + DRBD
Dedicated Interface
Active Server Passive Server
MySQL Datadrbd device formated with cluster
filesystem (GFS2/OCFS2) making two way read/write
possible with DRBD
![Page 70: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/70.jpg)
HIGH AVAILABILITYWITH MYSQL REPLICATION + DRBD + LVS/HEARTBEAT
Dedicated Interface
Active Server Passive Server
MySQL Datadrbd device formated with cluster
filesystem (GFS2/OCFS2)making two way read/write
possible with DRBD
Linux LVS / Heartbeat
virtual IP 192.168.0.1
![Page 71: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/71.jpg)
MYSQL CLUSTER
![Page 72: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/72.jpg)
MYSQL CLUSTERWHAT IS MYSQL CLUSTER?
• Relational Database Technology
• enables clustering of in-memory and disk-based tables
• with shared nothing technology
• protect against single point of failure
• if node fail, others nodes can be used to reconstruct datas
• shared disk is not required
![Page 73: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/73.jpg)
MYSQL CLUSTERWHAT IS MYSQL CLUSTER?
• Relational Database Technology
• synchronous replication with two phase commit mechanism
• guarantee that data is written to multiple nodes upon committing the data
![Page 74: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/74.jpg)
MYSQL CLUSTERCORE CONCEPTS
• Relational Database Technology
• every part of the cluster is a node
• three types of cluster nodes
• Management Node
• Data Node
• SQL/Application Node
![Page 75: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/75.jpg)
MYSQL CLUSTERCORE CONCEPTS
![Page 76: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/76.jpg)
MYSQL CLUSTERCORE CONCEPTS - MANAGEMENT NODE
• Manage the other nodes within the cluster
• providing configuration data
• starting / stoping nodes
• should be started first before other nodes
• started with the command ndb_mgmd
![Page 77: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/77.jpg)
MYSQL CLUSTERCORE CONCEPTS - DATA NODE
• stores cluster data
• started with the command ndbd
• starting from version 7.0 ndbmtd can also be used for data node process
• multi threaded data node daemon
• at least 2 data nodes - 1 possible but no replica
![Page 78: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/78.jpg)
MYSQL CLUSTERCORE CONCEPTS - SQL NODE
• node that access the cluster data
• a traditional mysql server that uses NDBCLUSTER storage engine
• mysql started with --ndbcluster and --ndb-connectstring options
• like a API node which accesses MySQL cluster data
![Page 79: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/79.jpg)
MYSQL CLUSTERNODES, NODE GROUPS, REPLICAS + PARTITIONS
• Partition
• is a portion of data stored by the cluster
• each node is responsible for keeping at least one copy of any partitions assigned to it
![Page 80: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/80.jpg)
MYSQL CLUSTERNODES, NODE GROUPS, REPLICAS + PARTITIONS
• (Data) Node - an ndbd process
• stores a replica
• copy of the partition assigned to the node group
• which the node is member
![Page 81: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/81.jpg)
MYSQL CLUSTERNODES, NODE GROUPS, REPLICAS + PARTITIONS
• Node Group
• consist of one or more nodes
• stores partitions or sets of replicas
![Page 82: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/82.jpg)
MYSQL CLUSTERNODES, NODE GROUPS, REPLICAS + PARTITIONS
![Page 83: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/83.jpg)
MYSQL CLUSTERNODES, NODE GROUPS, REPLICAS + PARTITIONS
![Page 84: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/84.jpg)
MYSQL CLUSTERHARDWARE, SOFTWARE + NETWORKING REQUIREMENTS
• can run on commodity hardware
• However data nodes required large amount of RAM
• all live data storage is done is memory
• can reduce RAM requirement by using Disk Data Tables
• only for non indexed columns of NDB tables
• multiple faster CPU can enhance performance
![Page 85: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/85.jpg)
MYSQL CLUSTERHARDWARE, SOFTWARE + NETWORKING REQUIREMENTS
• communication between nodes via TCP/IP networking
• minimum expected for each host is a standard 100Mbps ethernet controller
• recommend that MySQL to be run on its own subnet
• not sharing with machines not forming part of the cluster
• software requirement is simple, what needed is production release of MySQL 5.1.51-ndb-7.0.21 or 5.1.51-ndb-7.1.10
![Page 86: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/86.jpg)
MYSQL CLUSTERTYPICAL USE CASES
• subscriber databases
• DNS/DHCP for broadband
• Telecoms Application Servers
• Service Delivery Platforms
• AAA Databases
• deploying FreeRadius with MySQL database
![Page 87: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/87.jpg)
MYSQL CLUSTERLAB EXERCISE
![Page 88: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/88.jpg)
MYSQL CLUSTERINSTALL IN ALL SERVERS
• locate the tar ball downloaded
• extract using tar command
• tar xvf mysql-cluster-gpl-7.1.9-linux-x86_64-glibc23.tar.gz
• mv mysql-cluster-gpl-7.1.9-linux-x86_64-glibc23 /usr/local
• ln -s mysql-cluster-gpl-7.1.9-linux-x86_64-glibc23 mysqlc
• short name
![Page 89: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/89.jpg)
MYSQL CLUSTERCONFIGURE
• create data and configuration folder in mysql cluster manager server
• mkdir /etc/mysqlc
• create and set ownership for data folder in all servers
• /home/mysqlc
• chown mysql.mysql /home/mysqlc
![Page 90: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/90.jpg)
MYSQL CLUSTERCONFIGURE
• example setup with 5 nodesmanagement node - 192.168.1.5data node = 192.168.1.101data node = 192.168.1.102api node = 192.168.1.5api node = 192.168.1.5
![Page 91: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/91.jpg)
MYSQL CLUSTERCONFIGURE
• configure the node cluster manager - /etc/mysqlc/config.ini[ndb_mgmd]NodeId=1Hostname=192.168.1.5
[ndbd default]NoOfReplicas=2Datadir=/home/mysqlc
[ndbd]NodeId=3Hostname=192.168.1.101[ndbd]NodeId=4Hostname=192.168.1.102
[mysqld]NodeId=5Hostname=192.168.1.5[mysqld]NodeId=6Hostname=192.168.1.5
![Page 92: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/92.jpg)
MYSQL CLUSTERCONFIGURE
• start/reload the node cluster manager
• start the data nodes (in all configured nodes)
• display cluster status from management node
# ndb_mgmd -f /etc/mysqlc/config.ini --initial --configdir=/etc/mysqlc
# ndb_mgmd -f /etc/mysqlc/config.ini --reload
# ndbd -c 192.168.1.5:1186
# ndb_mgm -e show
![Page 93: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/93.jpg)
MYSQL CLUSTERCONFIGURE SQL NODE
• /etc/mysqlc/my.cnf
• create mysql default database
• start SQL/API node
[mysqld]basedir=/usr/local/mysqlcdatadir=/home/mysqlcndbclusterndb-connectstring=”192.168.1.5:1186”socket=/home/mysqlc/mysql.socklog-error=error.logmemlock
# cd /usr/local/mysqlc# scripts/mysql_install_db --no-defaults --datadir=/home/mysqlc
# /usr/local/mysqlc/bin/mysqld --defaults-file=/etc/mysqlc/my.cnf &
![Page 94: Mysql High Availability](https://reader033.vdocuments.us/reader033/viewer/2022061302/54e811f74a7959a46e8b4add/html5/thumbnails/94.jpg)
MYSQL CLUSTERTESTING SQL/API NODE
• in API server start mysql CLI# /usr/local/mysqlc/bin/mysql -h 127.0.0.1# /usr/local/mysqlc/bin/mysql -S /home/mysqlc/mysql.sock
mysql> create database clusterdb1;mysql> use clusterdb1;mysql> create table dummy1 (id int(10) ket, name varchar(100)) engine=ndbcluster;mysql> insert into dummy1 values (1, ‘John Doe’);
# in mgm server - ndb_desc to view partition informationndb_desc -c 192.168.1.5 -d clusterdb1 dummy1 -p