\n–Two MYSQL DB servers doing MASTER TO MASTER replication in fail over scenerio with replication synch and failure protection<\/em>
\n–Keepalived will be using a custom misc_chk script which does a TCP, PORT, MYSQL SERVICE RUNNING, and TABLE WRITE check. Should this fail 3 times, it fails to the secondary DB.<\/em>
\n -Setting mysql backups as well, you will be able to do backups and restores without breaking replication or stopping mysql.<\/em><\/p>\n
DIRECT ROUTE<\/strong><\/p>\n – The virtual IP address is shared by real servers and the load balancer. The load balancer has an interface configured with the virtual IP address too, which is used to accept request packets, and it directly route the packets to the chosen servers. All the real servers have their non-arp alias<\/strong> interface configured with the virtual IP address or redirect packets destined for the virtual IP address to a local socket, so that the real servers can process the packets locally. The load balancer and the real servers must have one of their interfaces physically linked by a HUB\/Switch. The architecture of virtual server via direct routing is illustrated as follows:<\/em><\/p>\n <\/p>\n 1. Provision yourself some servers using Debian or Ubuntu just for the keepalived stuff. I find that redhat has issues with compiling and its a bit slow. Your DB’s can be whatever distro you want.<\/p>\n Note. Ensure that you have two nics setup on your VM’s and that they are on the same vlan’s. This setup is single armed. If you are Doing NAT you will need two VLANS.<\/p>\n 2. COMPILE YOUR KEEPALIVED AND SETUP<\/span><\/strong><\/p>\n If you decided to use debian you will need the following packages to compile from source. I would recommend compiling from source, so you don\u2019t have to worry about updates from the OS repo affecting your environments by accident. Ensure that you have the Kernel Headers installed, as you will need them.<\/p>\n apt-get install gcc libssl-dev libpopt-dev libnl-dev ipvsadm<\/em><\/p>\n .\/configure –with-kernel-dir=\/usr\/src\/linux-headers-2.6.35-23-server\/<\/em><\/p>\n make && make install<\/em><\/p>\n Once this installed you will need to make some modification the startup scripts<\/p>\n mv \/etc\/init.d\/keepalived \/etc\/init.d\/keepalived.bak<\/em> Update the \/etc\/init.d\/keepalived<\/em> script<\/p>\n updated \/etc\/init.d\/keepalived<\/em> # Source function library # Source configuration file (we set KEEPALIVED_OPTIONS there)<\/p>\n #. \/etc\/sysconfig\/keepalived Next you will need to edit the file below on both lvs servers<\/p>\n \/etc\/sysctl.conf on both LVS1 and LVS2 save the file and run ‘sysctl -p<\/em>‘ to load it to the running config 3. Configure your nics on LVS1 and LVS2 # This file describes the network interfaces available on your system #This line adds a static route for the entire subnet so traffic can reach the real servers #properly, this line is not necessary, however if the packets are dropping at the destination, #this line helps correct that, it usually better to have this in place. # The primary network interface # dns-* options are implemented by the resolvconf package, if installed iface eth1 inet static address 172.16.98.2 4. Now what I have done is setup a reload script that will update LVS2 upon reload so I dont have to keep updating both LVS server everytime I have to do a graceful reload, not sure why keepalive has not implemented this feature yet. Developer, don’t think like systems engineers and vice versa.<\/p>\n You should be able to ssh now ‘ssh root@hostname<\/span>‘ and it should login. If it didn’t go google why \ud83d\ude1b<\/p>\n 5. A) Setup keepalived and automating the reload process<\/p>\n Login into LVS1 Create a keepalived.conf in \/etc\/keepalived\/ and copy past the following in it, you will need to update the varibles according to your own setup<\/p>\n sample keepalived.conf global_defs {<\/em> vrrp_instance external_linux {<\/em> #When removing servers from the pool comment starting below this line<\/em> }<\/em> I have broken the keepalived.conf file into three sections for the purpose of automating & config updates to LVS2<\/p>\n Create<\/em><\/strong> ================== Global-Directives<\/strong> * Shared Directives<\/strong><\/p>\n Example \/etc\/keepalived\/head (whatever you put in your keepalived.conf for the globaldefs put in your head file copy past it here is a sample ones.<\/p>\n http:\/\/www.nikktailor.com\/files\/headfilelvs1 \/etc\/keepalived\/virtual_ips.conf <- Contains the VIP ip’s, whatever you have in your keepalived.conf create this file and copy paste it.<\/p>\n https:\/\/www.nicktailor.com\/files\/virtual_ips.conf #VIP’s are listed and or Gateway ip is listed here if using NAT for DB boxes<\/em> inside the conf.d\/ directory you will have the real server config. Make sure you never have duplicates<\/strong> as the reload script will mess up your keepalived.conf file if do and you can bring down your VIPS<\/p>\n https:\/\/www.nicktailor.com\/files\/testmschk.conf (Example) #DO NOT RELOAD CONFIG FROM LVS2<\/em> virtual_server 172.16.10.1 3306 {<\/em> #this is the failover server if it fails over to this traffic is forced there are no checks done on this server by keepalived<\/em> #When removing servers from the pool comment starting below this line<\/em> }<\/em> #make sure to comment above last bracket when removing above server from pool, do not comment out below bracket<\/em> B) Automation Reload Script<\/strong> \/etc\/init.d\/keepalivedreload (LVS1) a) this will copy the virtual ips file and conf.d directory to lvs2 and reload the config lvs2 and make a backup of the config prior to update. KADIR=\/etc\/keepalived echo Making a backup of the conf file as $BACKUPFILE.gz echo “Adding gateways and virtual IPs…”<\/p>\n cat $KADIR\/head $KADIR\/virtual_ips.conf > $CONFFILE echo “Adding virtual servers…”<\/p>\n for i in $KADIR\/conf.d\/* echo “Copying gateways, virtual IPs, and virtual servers to lvs2nicktailor.com…” for i in $KADIR\/conf.d\/* echo “Reloading keepalived config…” echo “Issuing reload on lvs2nicktailor.com…” Now I have made the second one so that you can’t make reloads updates to the primary lvs from the secondary, just because of human error. You’re free to do this however you please. I found this way to be beneficial.<\/p>\n <\/p>\n (LVS2) \/etc\/init.d\/keepalivedreload this script will just make a backup and reload on lvs2 and not update back to lvs1.<\/p>\n https:\/\/www.nicktailor.com\/files\/keepalivedreloadlvs2 KADIR=\/etc\/keepalived echo Making a backup of the conf file as $BACKUPFILE.gz echo “Adding gateways and virtual IPs…”<\/p>\n cat $KADIR\/head $KADIR\/virtual_ips.conf > $CONFFILE echo “Adding virtual servers…”<\/p>\n for i in $KADIR\/conf.d\/* #echo “Copying gateways, virtual IPs, and virtual servers to lvsdevl22.webi.it.ubc.ca…” #for i in $KADIR\/conf.d\/* echo “Reloading keepalived config…” #echo “Issuing reload on lvs2nicktailor.com…” 6. Test the reload by running it and seeing if the upates take effect from lvs1 to lvs2. You should see the keepalived.conf and \/etc\/keepalived\/conf.d directory all matching.<\/p>\n MYSQL DATABASE SERVER DEPLOYMENT AND REPLICATION SETUP<\/strong><\/span><\/p>\n 1. Provision your mysql DB servers instal mysql IE. redhat<\/p>\n \/etc\/sysconfig\/network-scripts\/ifcfg-lo:0 5. Update \/etc\/sysctl.conf<\/p>\n net.ipv4.conf.default.arp_ignore=1<\/em> and run ‘sysctl -p<\/em>‘<\/p>\n Note: The reason for this is it will make sure the server doesn\u2019t loop back and start ARP requests for the VIP. Master 2\/Slave 1 ip : 192.168.16.5<\/p>\n <\/p>\n Step 2: [mysqld] log-bin server-id=1<\/p>\n [mysql.server] mysql> grant replication slave on *.* to ‘replication’@192.168.16.5 \\<\/em> and restart the mysql master1.<\/p>\n <\/p>\n Step 4: [mysqld] server-id=2<\/p>\n master-host = 192.168.16.4 [mysql.server] [mysqld_safe] mysql> start slave;<\/em> Slave_IO_State: Waiting for master to send event <\/p>\n Step 6: mysql> show master status;<\/em> The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.<\/p>\n <\/p>\n Step 7: [mysqld] master-host = 192.168.16.4 log-bin #information for becoming master added [mysql.server] [mysqld_safe] <\/p>\n Step 8: mysql> grant replication slave on *.* to ‘replication’@192.168.16.4 identified by ‘slave2’;<\/em><\/p>\n <\/p>\n Step 9: [mysqld] # Default to using old password format for compatibility with mysql 3.x server-id=1 [mysql.server]user=mysqlbasedir=\/var\/lib<\/p>\n <\/p>\n Step 10: On mysql master1:<\/p>\n mysql> start slave;<\/em><\/p>\n On mysql master2:<\/p>\n mysql > show master status;<\/em><\/p>\n On mysql master 1:<\/p>\n mysql> show slave status\\G;<\/em> ERROR: ===========================================================================<\/p>\n NOW SETUP MYSQL FOR KEEPALIVED TO BE ABLE TO WRITE TO IT.<\/span><\/strong><\/p>\n Note – if you setup replication correctly, this will replicate over to your secondary DB and you wont have to worry about setting this up on your secondary. – You are now creating a DB for keepalived to write to<\/p>\n Copy Paste at MySQL prompt logged in as MySQL root<\/p>\n ===========================================================<\/p>\n CREATE DATABASE `healthcheck` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;<\/em> use healthcheck;<\/em><\/p>\n insert into keepalived (data) values (“hello”);<\/em> GRANT ALL ON *.* TO ‘lvs2’@’lvs2.nicktailor.com’ identified by ‘password’;<\/em><\/p>\n ============================================================<\/p>\n 2. A)Log back into LVS1 touch \/var\/log\/keepalived\/dbcheckprod.log<\/p>\n create a directory called \/root\/keepalived\/ \/root\/keepalived\/dbcheck.sh $host = $ARGV[$0] ;<\/em> $database = “healthcheck”;<\/em> open (LOG_FILE,”>>$logdir”);<\/em><\/p>\n if ($lvs_host eq “lvs1.nicktailor.com”)<\/em>![\"\"\/](\"http:\/\/www.linuxvirtualserver.org\/Joseph.Mack\/linuxexpo99\/VS-DR.png\")
<\/p>\n
\n cd \/etc\/init.d\/<\/em>
\n ln -s \/usr\/local\/etc\/rc.d\/init.d\/keepalived keepalived<\/em>
\n mv \/usr\/sbin\/keepalived \/usr\/sbin\/keepalived.original<\/em>
\n cd \/usr\/sbin<\/em>
\n ln -s \/usr\/local\/sbin\/keepalived keepalived<\/em><\/p>\n
\n=================================
\n#!\/bin\/sh
\n#
\n# Startup script for the Keepalived daemon
\n#
\n# processname: keepalived
\n# pidfile: \/var\/run\/keepalived.pid
\n#config: \/usr\/local\/etc\/keepalived
\n# chkconfig: – 21 79
\n# description: Start and stop Keepalived
\nPATH=\/sbin:\/bin:\/usr\/sbin:\/usr\/bin
\nDAEMON=\/usr\/local\/sbin\/keepalived
\nNAME=keepalived
\nDESC=keepalived
\nCONFIG=\/etc\/keepalived\/keepalived.conf<\/p>\n
\n#. \/etc\/rc.d\/init.d\/functions
\n. \/lib\/lsb\/init-functions<\/p>\n
\n. \/usr\/local\/etc\/sysconfig\/
\n====================================================<\/p>\n
\nnet.ipv4.ip_forward = 1
\n==========================================<\/p>\n
\n\/etc\/network\/interfaces (The ip’s listed below are example only please dont use them hoping it will work)<\/p>\n
\n# and how to activate them. For more information, see interfaces(5).
\n# The loopback network interface
\nauto lo
\niface lo inet loopback<\/p>\n
\nup ip route add 172.16.0.1\/26 dev eth0;:<\/em><\/p>\n
\nauto eth0 eth1
\niface eth0 inet static
\naddress 172.16.0.1
\nnetmask 255.255.255.192
\nnetwork 172.16.0.1
\nbroadcast 172.16.0.1
\ngateway 172.16.0.1<\/p>\n
\ndns-nameservers 172.16.0.1 172.16.0.1
\ndns-search sa.it.nicktailor.com<\/p>\n
\n#This interface is on the same vlan as the primary eth0 interface on purpose. This i#nterface is soley used by keepalived as the sync interface \/ hearbeat for keepalived #daemon to sync to the second director lvs2.<\/em><\/p>\n
\n#This ip should be an non routable address you define, just make sure that lvs2 is on the #same subnet as this non routeble address, so if this is lvs1 then lvs2 will be 172.16.98.3 #kind of thing.
\n<\/em> netmask 255.255.255.0
\n=============================================================<\/p>\n\n
\ncd \/etc\/keepalived\/
\nmkdir conf.d\/
\nmkdir backups\/<\/p>\n
\nhttps:\/\/www.nicktailor.com\/files\/keepalived.conf.lvs1
\nhttps:\/\/www.nicktailor.com\/files\/keepalived.conf.lvs2
\n##WARNING:
\n#If this configuration is copied over to the SLAVE LVS as is stuff will EXPLODE
\n### There are elements within that need to stay unique on each server.
\n### Specifically:
\n### -lvs id
\n### -state
\n### -priority
\n#########################################################################<\/p>\n
\n notification_email {<\/em>
\n nick.tailor@nicktailor.com<\/em>
\n }<\/em>
\n notification_email_from root@lvs1<\/em>
\n smtp_server 127.0.0.1<\/em>
\n smtp_connect_timeout 30<\/em>
\n lvs_id lvs1<\/em>
\n }<\/em><\/p>\n
\n state MASTER<\/em>
\n smtp_alert<\/em>
\n interface eth0<\/em>
\n lvs_sync_daemon_interface eth0<\/em>
\n virtual_router_id 42<\/em>
\n priority 150<\/em>
\n advert_int 1<\/em>
\n preempt<\/em>
\n authentication {<\/em>
\n auth_type PASS<\/em>
\n auth_pass nicktailor<\/em>
\n }<\/em>
\n virtual_ipaddress {<\/em>
\n #VIP’s are listed and or Gateway ip is listed here if using NAT for DB boxes<\/em>
\n 172.16.10.1<\/em>
\n }<\/em>
\n virtual_server 172.16.10.1 3306 {<\/em>
\n delay_loop 10<\/em>
\n lb_algo rr<\/em>
\n lb_kind DR<\/em>
\n protocol TCP<\/em>
\n # persistence_timeout 30 (this is not needed for db with replication setup,<\/em>
\n # only enabled if you absolutely have to)<\/em><\/p>\n
\n real_server 142.103.18.2 3306 {<\/em>
\n MISC_CHECK {<\/em>
\n misc_path “\/root\/keepalived\/dbcheck.sh 142.103.18.2”<\/em>
\n misc_timeout 30
\n<\/em>}<\/em><\/p>\n
\n #make sure to comment above bracket when removing above server from pool<\/em>
\n real_server 142.103.18.3 3306 {<\/em>
\n MISC_CHECK {<\/em>
\n misc_path “\/root\/keepalived\/dbcheck.sh 142.103.18.2”<\/em>
\n misc_timeout 30<\/em>
\n }<\/em>
\n }<\/em>
\n #make sure to comment above last bracket when removing above server from pool, do not comment out below bracket<\/em>
\n }<\/em>
\n================================================================<\/p>\n
\n\/etc\/keepalived\/head<\/em> <- Contains the Global LVS directives for the directors(director means LVS1 or LVS2) there are links below for example files.<\/p>\n
\nWarning: Directives within the GLOBAL section MUST differ on each server in a failover setup!!!<\/p>\n
\n * Unique Directives:<\/strong><\/p>\n\n
\n
\nhttps:\/\/www.nicktailor.com\/files\/headfilelvs2
\n====================
\nglobal_defs {<\/em>
\n notification_email {<\/em>
\n nick.tailor@nicktailor.com<\/em>
\n }<\/em>
\n notification_email_from root@lvs1.nicktailor.com<\/em>
\n smtp_server 127.0.0.1<\/em>
\n smtp_connect_timeout 30<\/em>
\n lvs_id lvs1<\/em>
\n }<\/em>
\n vrrp_instance external_linux {<\/em>
\n state BACKUP<\/em>
\n smtp_alert<\/em>
\n interface eth0<\/em>
\n lvs_sync_daemon_interface eth1<\/em>
\n virtual_router_id 42<\/em>
\n priority 50<\/em>
\n advert_int 1<\/em>
\n preempt<\/em>
\n authentication {<\/em>
\n auth_type PASS<\/em>
\n auth_pass nicktailor<\/em>
\n }<\/em>
\n===================<\/p>\n
\n================================
\nvirtual_ipaddress {<\/em><\/p>\n
\n 172.16.10.1 #Development VIP<\/em>
\n 172.16.10.2 #Production VIP<\/em>
\n }<\/em>
\n================================<\/p>\n
\n\/etc\/keepalived\/conf.d\/testmschkscript.conf
\n==================================================
\n#reload config with ‘\/etc\/init.d\/keepalive_reload’ this will update lvs2 with changes automatically<\/em><\/p>\n
\n #To see pool use ‘watch ipvsadm -L -n’<\/em>
\n #’ip addr’ will show you which lvs the vip is bound to<\/em><\/p>\n
\n delay_loop 10<\/em>
\n lb_algo rr<\/em>
\n lb_kind DR<\/em>
\n protocol TCP<\/em>
\n # persistence_timeout 30 (this is not needed for db with replication setup, only enabled if you absolutely have to)<\/em><\/p>\n
\n sorry_server 142.103.18.2 3306<\/em><\/p>\n
\n real_server 142.103.18.1 3306 {<\/em>
\n MISC_CHECK {<\/em>
\n misc_path “\/root\/keepalived\/dbcheck.sh 142.103.18.1”<\/em>
\n misc_timeout 30<\/em><\/p>\n
\n }<\/em><\/p>\n
\n }<\/em>
\n================================================================<\/p>\n
\ncreate the following file \/etc\/init.d\/keepalivedreload and give it executable permissions.
\ncreate \/etc\/keepalived\/backups directory as well.<\/p>\n
\nhttps:\/\/www.nicktailor.com\/files\/keepalivedreloadlvs1<\/p>\n
\n===============================
\n#!\/bin\/bash<\/p>\n
\nBACKUPFILE=”$KADIR\/backups\/`date +’%Y%m%d-%H%M’`.conf”
\nCONFFILE=$KADIR\/keepalived.conf<\/p>\n
\ncp $KADIR\/keepalived.conf $BACKUPFILE
\ngzip $BACKUPFILE<\/p>\n
\necho “}” >> $CONFFILE
\n#echo “}” >> $CONFFILE
\necho “##################################################” >> $CONFFILE<\/p>\n
\ndo
\ncat $i >> $CONFFILE
\ndone<\/p>\n
\nscp $KADIR\/virtual_ips.conf root@lvs2nicktailor.com:\/etc\/keepalived\/<\/p>\n
\ndo
\nscp $i root@lvs2nicktailor.com:\/etc\/keepalived\/conf.d\/
\ndone<\/p>\n
\n\/etc\/init.d\/keepalived reload<\/p>\n
\necho “==============================”
\nssh root@lvs2nicktailor.com \/etc\/init.d\/keepalived_reload
\necho “Done.”
\n============================================<\/p>\n
\n====================================
\n#!\/bin\/bash<\/p>\n
\nBACKUPFILE=”$KADIR\/backups\/`date +’%Y%m%d-%H%M’`.conf”
\nCONFFILE=$KADIR\/keepalived.conf<\/p>\n
\ncp $KADIR\/keepalived.conf $BACKUPFILE
\ngzip $BACKUPFILE<\/p>\n
\necho “}” >> $CONFFILE
\n#echo “}” >> $CONFFILE
\necho “##################################################” >> $CONFFILE<\/p>\n
\ndo
\ncat $i >> $CONFFILE
\ndone<\/p>\n
\n#scp $KADIR\/virtual_ips.conf
\n#root@lvs2nicktailor.com:\/etc\/keepalived\/<\/p>\n
\n#do
\n#scp $i root@lvs2nicktailor.com:\/etc\/keepalived\/conf.d\/
\n#done<\/p>\n
\n\/etc\/init.d\/keepalived reload<\/p>\n
\n#echo “==============================”
\n#ssh root@lvs2nicktailor.com \/root\/bin\/keepalive_reload
\n#ssh root@lvs2nicktailor.com \/etc\/init.d\/keepalived reload
\necho “Done.”<\/p>\n
\n2. Ensure that your DB servers are on the same vlan as the LVS
\n3. Configure the nics
\n4. configure the loopback interface so the VIP is bound to it, since we are doing Direct ROUTE<\/p>\n
\n=================
\nDEVICE=lo:0
\nIPADDR=142.103.18.1
\nNETMASK=255.255.255.255
\n# If you’re having problems with gated making 127.0.0.0\/8 a martian,
\n# you can change this to something else (255.255.255.255, for example)
\nONBOOT=yes
\nNAME=loopback<\/p>\n
\n net.ipv4.conf.default.arp_announce=2<\/em>
\n net.ipv4.conf.all.arp_ignore=1<\/em>
\n net.ipv4.conf.all.arp_announce=2<\/em><\/p>\n
\nMYSQL REPLICATION SETUP – i was too lazy to write it all out so I found his great link to explain how to set it up
\nhttp:\/\/www.howtoforge.com\/mysql_master_master_replication
\n=============================================
\nMaster 1\/Slave 2 ip: 192.168.16.4<\/p>\n
\nOn Master 1, make changes in my.cnf:<\/p>\n
\ndatadir=\/var\/lib\/mysql
\nsocket=\/var\/lib\/mysql\/mysql.sock
\nold_passwords=1<\/p>\n
\nbinlog-do-db=<database name> # input the database which should be replicated
\nbinlog-ignore-db=mysql # input the database that should be ignored for replication
\nbinlog-ignore-db=test<\/p>\n
\nuser=mysql
\nbasedir=\/var\/lib
\n[mysqld_safe]
\nerr-log=\/var\/log\/mysqld.log
\npid-file=\/var\/run\/mysqld\/mysqld.pid
\nStep 3:
\nOn master 1, create a replication slave account in mysql.<\/p>\n
\n identified by ‘slave’;<\/em><\/p>\n
\nNow edit my.cnf on Slave1 or Master2 :<\/p>\n
\ndatadir=\/var\/lib\/mysql
\nsocket=\/var\/lib\/mysql\/mysql.sock
\nold_passwords=1<\/p>\n
\nmaster-user = replication
\nmaster-password = slave
\nmaster-port = 3306<\/p>\n
\nuser=mysql
\nbasedir=\/var\/lib<\/p>\n
\nerr-log=\/var\/log\/mysqld.log
\npid-file=\/var\/run\/mysqld\/mysqld.pid
\nStep 5:
\nRestart mysql slave 1 and at<\/p>\n
\nmysql> show slave status\\G;<\/em>
\n*************************** 1. row ***************************<\/p>\n
\nMaster_Host: 192.168.16.4
\nMaster_User: replica
\nMaster_Port: 3306
\nConnect_Retry: 60
\nMaster_Log_File: MASTERMYSQL01-bin.000009
\nRead_Master_Log_Pos: 4
\nRelay_Log_File: MASTERMYSQL02-relay-bin.000015
\nRelay_Log_Pos: 3630
\nRelay_Master_Log_File: MASTERMYSQL01-bin.000009
\nSlave_IO_Running: Yes
\nSlave_SQL_Running: Yes
\nReplicate_Do_DB:
\nReplicate_Ignore_DB:
\nReplicate_Do_Table:
\nReplicate_Ignore_Table:
\nReplicate_Wild_Do_Table:
\nReplicate_Wild_Ignore_Table:
\nLast_Errno: 0
\nLast_Error:
\nSkip_Counter: 0
\nExec_Master_Log_Pos: 4
\nRelay_Log_Space: 3630
\nUntil_Condition: None
\nUntil_Log_File:
\nUntil_Log_Pos: 0
\nMaster_SSL_Allowed: No
\nMaster_SSL_CA_File:
\nMaster_SSL_CA_Path:
\nMaster_SSL_Cert:
\nMaster_SSL_Cipher:
\nMaster_SSL_Key:
\nSeconds_Behind_Master: 1519187
\n1 row in set (0.00 sec)
\nAbove highlighted rows must be indicate related log files and Slave_IO_Running and Slave_SQL_Running: must be to YES.<\/p>\n
\nOn master 1:<\/p>\n
\n+————————+———-+————–+——————+
\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
\n+————————+———-+————–+——————+
\n|MysqlMYSQL01-bin.000008 | 410 | adam | |
\n+————————+———-+————–+——————+
\n1 row in set (0.00 sec)<\/p>\n
\nOn Master2\/Slave 1, edit my.cnf and master entries into it:<\/p>\n
\ndatadir=\/var\/lib\/mysql
\nsocket=\/var\/lib\/mysql\/mysql.sock
\n# Default to using old password format for compatibility with mysql 3.x
\n# clients (those using the mysqlclient10 compatibility package).
\nold_passwords=1
\nserver-id=2<\/p>\n
\nmaster-user = replication
\nmaster-password = slave
\nmaster-port = 3306<\/p>\n
\nbinlog-do-db=adam<\/p>\n
\nuser=mysql
\nbasedir=\/var\/lib<\/p>\n
\nerr-log=\/var\/log\/mysqld.log
\npid-file=\/var\/run\/mysqld\/mysqld.pid<\/p>\n
\nCreate a replication slave account on master2 for master1:<\/p>\n
\nEdit my.cnf on master1 for information of its master.<\/p>\n
\ndatadir=\/var\/lib\/mysql
\nsocket=\/var\/lib\/mysql\/mysql.sock<\/p>\n
\n# clients (those using the mysqlclient10 compatibility package).
\nold_passwords=1
\nlog-bin
\nbinlog-do-db=adam
\nbinlog-ignore-db=mysql
\nbinlog-ignore-db=test<\/p>\n
\n#information for becoming slave.
\nmaster-host = 192.168.16.5
\nmaster-user = replication
\nmaster-password = slave2
\nmaster-port = 3306<\/p>\n
\nRestart both mysql master1 and master2.<\/p>\n
\n*************************** 1. row ***************************
\nSlave_IO_State: Waiting for master to send event
\nMaster_Host: 192.168.16.5
\nMaster_User: replica
\nMaster_Port: 3306
\nConnect_Retry: 60
\nMaster_Log_File: Mysql1MYSQL02-bin.000008
\nRead_Master_Log_Pos: 410
\nRelay_Log_File: Mysql1MYSQL01-relay-bin.000008
\nRelay_Log_Pos: 445
\nRelay_Master_Log_File: Mysql1MYSQL02-bin.000008
\nSlave_IO_Running: Yes
\nSlave_SQL_Running: Yes
\nReplicate_Do_DB:
\nReplicate_Ignore_DB:
\nReplicate_Do_Table:
\nReplicate_Ignore_Table:
\nReplicate_Wild_Do_Table:
\nReplicate_Wild_Ignore_Table:
\nLast_Errno: 0
\nLast_Error:
\nSkip_Counter: 0
\nExec_Master_Log_Pos: 410
\nRelay_Log_Space: 445
\nUntil_Condition: None
\nUntil_Log_File:
\nUntil_Log_Pos: 0
\nMaster_SSL_Allowed: No
\nMaster_SSL_CA_File:
\nMaster_SSL_CA_Path:
\nMaster_SSL_Cert:
\nMaster_SSL_Cipher:
\nMaster_SSL_Key:
\nSeconds_Behind_Master: 103799
\n1 row in set (0.00 sec)<\/p>\n
\nNo query specified<\/p>\n
\n1. this is so keepalived msc_chk will be able to test to see if the db can be written to and it put a timestamp in the tables.<\/p>\n
\n CREATE TABLE `healthcheck`.`keepalived` (<\/em>
\n `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,<\/em>
\n `data` VARCHAR( 100 ) NULL ,<\/em>
\n PRIMARY KEY ( `id` )<\/em>
\n ) ENGINE = MYISAM;<\/em>
\n CREATE TABLE `healthcheck`.`keepalived2` (<\/em>
\n `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,<\/em>
\n `data` VARCHAR( 100 ) NULL ,<\/em>
\n PRIMARY KEY ( `id` )<\/em>
\n ) ENGINE = MYISAM;<\/em><\/p>\n
\n insert into keepalived (data) values (“hello”);<\/em>
\n insert into keepalived2 (data) values (“hello”);<\/em>
\n insert into keepalived2 (data) values (“hello”);<\/em>
\n GRANT ALL ON *.* TO ‘lvs1’@’lvs1.nicktailor.com’ identified by ‘password’;<\/em><\/p>\n
\nB)Also do the same for LVS2 with below steps.<\/p>\n
\n– this is where your msck_chk script lives
\n– it will check tcp, port, mysql service and table write) 3 times and log it if it fails keepalived will fail to the secondary
\n– please note the UPDATE table section in this script needs to match the mysql ID you have in mysql below in the script<\/p>\n
\nhttps:\/\/www.nicktailor.com\/files\/dbcheck.sh
\n=======================================
\n#!\/usr\/bin\/perl<\/em>
\n #use strict;<\/em>
\n #use warnings;<\/em>
\n use DBI;<\/em>
\n use POSIX;<\/em>
\n #use Mysql;<\/em>
\n use Sys::Hostname;<\/em><\/p>\n
\n $lvs_host=hostname;<\/em><\/p>\n
\n $tablename = “keepalived”;<\/em>
\n $user = “lvs1”;<\/em>
\n $pw = “password”;<\/em>
\n $dbinfo = “DBI:mysql:$database;host=$host;mysql_connect_timeout=2”;<\/em>
\n $timestamp = strftime “%b%e %Y %H:%M:%S”, localtime;<\/em>
\n $logdir = “\/var\/log\/keepalived\/dbcheckprod.log”;<\/em><\/p>\n
\n {<\/em>
\n $tablename = “keepalived”;<\/em>
\n }<\/em>
\n elsif ($lvs_host eq “lvs2.nicktailor.com”)<\/em>