¼Ü¹¹×é¼þ¼ò½é
MySQL-Proxy¼ò½é
MySQL-ProxyÊÇ´¦ÔÚÄãµÄMySQLÊý¾Ý¿â¿Í»§ºÍ·þÎñ¶ËÖ®¼äµÄ³ÌÐò£¬Ëü»¹Ö§³ÖǶÈëÐԽű¾ÓïÑÔLua¡£Õâ¸ö´úÀí¿ÉÒÔÓÃÀ´·ÖÎö¡¢¼à¿ØºÍ±ä»»£¨transform£©Í¨ÐÅÊý¾Ý£¬ËüÖ§³Ö·Ç³£¹ã·ºµÄʹÓó¡¾°£º
¸ºÔØÆ½ºâºÍ¹ÊÕÏ×ªÒÆ´¦Àí
²éѯ·ÖÎöºÍÈÕÖ¾
SQLºê£¨SQL macros£©
²éÑ¯ÖØÐ´£¨query rewriting£©
Ö´ÐÐshellÃüÁî
MySQL Proxy¸üÇ¿´óµÄÒ»ÏÄÜÊÇʵÏÖ¡°¶Áд·ÖÀ루Read/Write Splitting£©¡±¡£»ù±¾µÄÔÀíÊÇÈÃÖ÷Êý¾Ý¿â´¦ÀíÊÂÎñÐÔ²éѯ£¬¶ø´ÓÊý¾Ý¿â´¦ÀíSELECT²éѯ¡£Êý¾Ý¿â¸´ÖƱ»ÓÃÀ´°ÑÊÂÎñÐÔ²éѯµ¼Öµıä¸üͬ²½µ½¼¯ÈºÖеĴÓÊý¾Ý¿â¡£
MMM¼ò½é
MMM¼´Master-Master Replication Manager for MySQL£¨mysqlÖ÷Ö÷¸´ÖƹÜÀíÆ÷£©£¬ÊǹØÓÚmysqlÖ÷Ö÷¸´ÖÆÅäÖÃµÄ¼à¿Ø¡¢¹ÊÕÏ×ªÒÆºÍ¹ÜÀíµÄÒ»Ì׿ÉÉìËõµÄ½Å±¾Ì×¼þ£¨ÔÚÈκÎʱºòÖ»ÓÐÒ»¸ö½Úµã¿ÉÒÔ±»Ð´È룩£¬Õâ¸öÌ×¼þÒ²ÄÜ»ùÓÚ±ê×¼µÄÖ÷´ÓÅäÖõÄÈÎÒâÊýÁ¿µÄ´Ó·þÎñÆ÷½øÐжÁ¸ºÔؾùºâ£¬ËùÒÔÄã¿ÉÒÔÓÃËüÀ´ÔÚÒ»×é¾ÓÓÚ¸´ÖƵķþÎñÆ÷Æô¶¯ÐéÄâip£¬³ý´ËÖ®Í⣬Ëü»¹ÓÐʵÏÖÊý¾Ý±¸·Ý¡¢½ÚµãÖ®¼äÖØÐÂͬ²½¹¦ÄܵĽű¾¡£
MySQL±¾ÉíûÓÐÌṩreplication failoverµÄ½â¾ö·½°¸£¬Í¨¹ýMMM·½°¸ÄÜʵÏÖ·þÎñÆ÷µÄ¹ÊÕÏ×ªÒÆ£¬´Ó¶øÊµÏÖmysqlµÄ¸ß¿ÉÓá£
MMMÏîÄ¿À´×Ô Google£ºhttp://code.google.com/p/mysql-master-master
¹Ù·½ÍøÕ¾Îª£ºhttp://mysql-mmm.org
MMMÖ÷Òª¹¦ÄÜÓÉÏÂÃæÈý¸ö½Å±¾Ìṩ£º
mmm_mond £º¸ºÔðËùÓÐµÄ¼à¿Ø¹¤×÷µÄ¼à¿ØÊØ»¤½ø³Ì£¬¾ö¶¨½ÚµãµÄÒÆ³ýµÈµÈ
mmm_agentd £ºÔËÐÐÔÚmysql·þÎñÆ÷ÉϵĴúÀíÊØ»¤½ø³Ì£¬Í¨¹ý¼òµ¥Ô¶³Ì·þÎñ¼¯Ìṩ¸ø¼à¿Ø½Úµã
mmm_control£ºÍ¨¹ýÃüÁîÐйÜÀímmm_mond½ø³Ì
¹ØÓڴ˼ܹ¹µÄÓÅȱµã£º
Óŵ㣺°²È«ÐÔ¡¢Îȶ¨ÐԸߣ¬¿ÉÀ©Õ¹ÐÔºÃ,µ±Ö÷·þÎñÆ÷¹ÒµôÒÔºó£¬ÁíÒ»¸öÖ÷Á¢¼´½Ó¹Ü£¬ÆäËûµÄ´Ó·þÎñÆ÷ÄÜ×Ô¶¯Çл»£¬²»ÓÃÈ˹¤¸ÉÔ¤¡£
ȱµã£ºÖÁÉÙÈý¸ö½Úµã£¬¶ÔÖ÷»úµÄÊýÁ¿ÓÐÒªÇó£¬ÐèҪʵÏÖ¶Áд·ÖÀ룬¿ÉÒÔÔÚ³ÌÐòÀ©Õ¹ÉϱȽÏÄÑʵÏÖ¡£Í¬Ê±¶ÔÖ÷´Ó(Ë«Ö÷)ͬ²½ÑÓ³ÙÒªÇó±È½Ï¸ß£¡Òò´Ë²»ÊʺÏÊý¾Ý°²È«·Ç³£ÑϸñµÄ³¡ºÏ¡£
ʵÓó¡Ëù£º¸ß·ÃÎÊÁ¿£¬ÒµÎñÔö³¤¿ì£¬²¢ÇÒÒªÇóʵÏÖ¶Áд·ÖÀëµÄ³¡¾°¡£
¼Ü¹¹Ä£Ê½£¨Ë«masterÁãslave£©¼ò½é
Ö÷»úÐÅÏ¢£º
ÐéÄâip·ÖÅ䣺
´Ë¼Ü¹¹ÒÔmysql×÷ΪÊý¾Ý¿â£¬Á½Ì¨Êý¾Ý¿âDB01¡¢DB02Ö÷´Ó¸´ÖÆ£¨Master-Slave£©µÄ·½Ê½À´Í¬²½Êý¾Ý£¬ÔÙͨ¹ý¶Áд·ÖÀ루MySQL-Proxy£©+HA£¨mysql-mmm£©À´ÌáÉýºó¶ËÊý¾Ý¿âµÄ²¢·¢¸ºÔؼ°haÄÜÁ¦¡£
ǰ¶ËÓ¦ÓÃͨ¹ý·ÃÎÊmysql-proxyÖж¨ÒåµÄproxy-address¼°¶Ë¿Ú·ÃÎÊÊý¾Ý¿â£¬´Ë¼ämysql-proxy»áͨ¹ýÆäÅäÖÃÎļþÖж¨ÒåµÄproxy-backend-addressesºÍproxy-read-only-backend-addresses·ÖÅäµ½ÏàÓ¦µÄÊý¾Ý¿â¡£Èç¹û½«proxy-backend-addressesºÍproxy-read-only-backend-addresses¶¨Òå³Émysql-mmmÖж¨ÒåµÄvip£¬¾Í¿ÉÒÔÓëmysql-mmmµÄ¹ÊÕÏÇл»¹¦ÄÜÏà½áºÏ¡£¾ßÌåʵʩ¹ý³Ì¼ûÏÂÎÄ¡£
Ò»¡¢ MySQL¸´ÖÆÊµÏÖ»¥ÎªÖ÷´ÓË«»úÈȱ¸
MysqlÖ÷´Ó¸´ÖÆÓ¦¸Ã²»ÄÑ£¬ÔÚÕâÀïÎҾͲ»ÔÙ׸Êö¡£ÏÂÃæÊÇһЩÐèҪעÒâµÄµØ·½£º
1.1·þÎñÆ÷²ÎÊý
[DB01 ·þÎñÆ÷]
################# master ######################### server-id = 1 binlog-ignore-db=mysql replicate-ignore-db=mysql log-bin=/usr/local/mysql/binlog/master-bin binlog_format=mixed expire_logs_days= 30 ##################### slave########################## relay-log=/usr/local/mysql/binlog/slave-relay-bin |
[DB02 ·þÎñÆ÷]
################# master ######################### server-id = 2 binlog-ignore-db=mysql replicate-ignore-db=mysql log-bin=/usr/local/mysql/binlog/master-bin binlog_format=mixed expire_logs_days= 30 ##################### slave########################## relay-log=/usr/local/mysql/binlog/slave-relay-bin |
1.2 ²Ù×÷²½Öè
ÒÔϲÙ×÷¾ùÔÚrootÓû§µÇ¼mysqlÖ®ºó²Ù×÷¡£
# DB01 DB02 ·þÎñÆ÷ֹͣͬ²½ STOP SLAVE; # DB01 DB02 ·þÎñÆ÷Çå¿ÕMasterÈÕÖ¾ RESET MASTER; # DB01 DB02 ·þÎñÆ÷ÊÚȨͬ²½ÕË»§ GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave'; FLUSH PRIVILEGES; # DB01 DB02 ·þÎñÆ÷Ëø±í£¨Ëø±í״̬ϲ»ÄÜÖÕÖ¹mysql½ø³Ì£¬
·ñÔò»áʧ°Ü;Ò²¿ÉÒÔ²»Ëø±í£¬¹Ø±Õǰ̨µÄËùÓÐweb·þÎñÆ÷£© FLUSH TABLES WITH READ LOCK; # ×ö¸´ÖÆÖ®Ç°×îºÃÊÖ¶¯Í¬²½Ò»´ÎÊý¾Ý¿â¡£ # ²é¿´ DB01 ·þÎñÆ÷Ö÷»ú״̬£¨¼Ç¼¶þ½øÖÆ¿ªÊ¼Îļþ£¬Î»Öã© SHOW MASTER STATUS; #DB02·þÎñÆ÷Ëø±í£¨Ëø±í״̬ϲ»ÄÜÖÕÖ¹mysql½ø³Ì£¬·ñÔò»áʧ°Ü£© FLUSH TABLES WITH READ LOCK; # ÐÞ¸Ä DB02 ·þÎñÆ÷ÅäÖà CHANGE MASTER TO MASTER_HOST='192.168.9.157',MASTER_USER='slave',
MASTER_PASSWORD='slave',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=107; # ¿ªÆô DB02 ·þÎñÆ÷ͬ²½½ø³Ì START SLAVE; # ²é¿´ DB02 ·þÎñÆ÷ͬ²½×´Ì¬ÊÇ·ñÕý³£ show slave status\G; # ²é¿´ DB02 ·þÎñÆ÷Ö÷»ú£¨¼Ç¼¶þ½øÖÆ¿ªÊ¼Îļþ£¬Î»Öã© SHOW MASTER STATUS; # ÐÞ¸Ä DB01 ·þÎñÆ÷ÅäÖà CHANGE MASTER TO MASTER_HOST='192.168.9.158',MASTER_USER='slave',
MASTER_PASSWORD='slave',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=107; # ¿ªÆô DB01·þÎñÆ÷ͬ²½½ø³Ì START SLAVE; # ·Ö±ð²é¿´ DB01¡¢DB02 ·þÎñÆ÷ͬ²½×´Ì¬£¬È·¶¨ÊÇ·ñ³É¹¦ show slave status\G; SHOW MASTER STATUS; # ½âËø DB01¡¢DB02 ·þÎñÆ÷ UNLOCK TABLES; # Êý¾Ý²âÊÔ·Ö±ðÔÚ DB01¡¢DB02 ·þÎñÆ÷ÉÏ´´½¨±í²åÈëÊý¾Ý²âÊÔ |
1.3 ÐèҪעÒâµÄµØ·½
Êý¾Ý¿âĿ¼ÏµÄmaster.infoµÄÄÚÈݻḲ¸ÇÃüÁîÐлòmy.cnfÖÐÖ¸¶¨µÄ²¿·ÖÑ¡Ï¸ü¸ÄÅäÖÃÐèɾ³ýmaster.info
my.cnfÖеÄmasterÅäÖÃÔÚMySQL 6.0ÒÔºó»áÈ¡Ïû£¬¹Ù·½½¨ÒéʹÓö¯Ì¬µÄCHANGE MASTER
Èç¹ûÖ»Ö¸¶¨ignore-db¶ø²»Ö¸¶¨do-db¡£Ôò´´½¨Êý¾Ý¿âµÄ²Ù×÷Ò²»áͬ²½¡£
Ë«Ö÷ģʽʹÓÃlog-slave-updates²ÎÊý£¬»áµ¼ÖÂÊý¾Ý²»Ò»ÖÂ
log-slave-updates ÆôÓôÓÊô·þÎñÆ÷ÉϵÄÈÕÖ¾¹¦ÄÜ£¬Ê¹Õą̂¼ÆËã»ú¿ÉÒÔÓÃÀ´¹¹³ÉÒ»¸ö¾µÏñÁ´(A->B->C)¡£
»¥ÎªÍ¬²½ÅäÖÃʵÀý£º
1. DB01 DB02 »¥ÎªÖ÷´Óͬ²½test,²»Í¬²½mysql£º
Á½¸öÊý¾Ý¿âÅäÖÃÖоùÉèÖãºbinlog-do-db=test,binlog-ignore-db=mysql£¬replicate-do-db=test£¬replicate-ignore-db=mysql
2. DB01 DB02 »¥ÎªÖ÷´Óֻͬ²½test£¬²»Í¬²½ÆäËûÊý¾Ý¿â£¬Ð´´½¨µÄÒ²²»»áͬ²½
Á½¸öÊý¾Ý¿âÅäÖÃÖоùÉèÖãºbinlog-do-db=test£¬replicate-do-db=test
3. DB01 DB02 »¥ÎªÖ÷´Ó²»Í¬²½mysql,ͬ²½ÆäËûÊý¾Ý¿â£¬Æ©Èç´´½¨µÄÐÂÊý¾Ý¿âÒ²»áͬ²½
Á½¸öÊý¾Ý¿âÅäÖÃÖоùÉèÖãºbinlog-ignore-db=mysql£¬replicate-ignore-db=mysql
4. DB01 DB02»¥ÎªÖ÷´Óͬ²½ËùÓÐÊý¾Ý¿â£¬°üÀ¨Ð½¨µÄÊý¾Ý¿â
Á½¸öÊý¾Ý¿âÅäÖÃÖоù²»ÉèÖÃÉÏÊöËÄÏî
¶þ¡¢mysql¶Áд·ÖÀë
2.1 ³¡¾°ÃèÊö
Êý¾Ý¿âMasterÖ÷·þÎñÆ÷DB01£º192.168.9.157
Êý¾Ý¿âMasterÖ÷·þÎñÆ÷DB02£º192.168.9.158
MySQL-Proxyµ÷¶È·þÎñÆ÷£º192.168.9.159
ÒÔϲÙ×÷£¬¾ùÊÇÔÚ192.168.10.132¼´MySQL-Proxyµ÷¶È·þÎñÆ÷ÉϽøÐеġ£
2.2 °²×°Éý¼¶ÏµÍ³ËùÐèÈí¼þ°ü
yum -y install gcc gcc-c++ autoconf mysql-devel libtool pkgconfig ncurses ncurses-devel
wget make glibc glibc-devel gettext automake ntp hdparm dmidecode openssh-clients telnet traceroute pciutils |
2.3 mysql-proxy°²×°
2.3.1 °²×°libevent
wget http://monkey.org/~provos/libevent-2.0.10-stable.tar.gz tar xvfz libevent-2.0.10-stable.tar.gz cd libevent-2.0.10-stable ./configure make&&make install cd .. |
2.3.2°²×°glib-2
wget http://ftp.gnome.org/pub/gnome/sources/glib/2.22/glib-2.22.5.tar.gz tar xvfz glib-2.22.5.tar.gz cd glib-2.22.5 ./configure make && make install |
MySQL-ProxyµÄ¶Áд·ÖÀëÖ÷ÒªÊÇͨ¹ýrw-splitting.lua½Å±¾ÊµÏֵģ¬Òò´ËÐèÒª°²×°lua¡£
lua¿Éͨ¹ýÒÔÏ·½Ê½»ñµÃ
´Óhttp://www.lua.org/download.htmlÏÂÔØÔ´Âë°ü
Lua°²×°Ö®Ç°ÐèÒªÏȰ²×°readline6.1£¬²»È»»á±¨´íȱÉÙÍ·Îļþ£º
wget ftp://ftp.cwru.edu/pub/bash/readline-6.1.tar.gz tar xvfz readline-6.1.tar.gz cd readline-6.1 ./configure make&&make install cd .. #wget http://www.lua.org/ftp/lua-5.2.0.tar.gz wget http://www.lua.org/ftp/lua-5.1.4.tar.gz tar zxvf lua-5.1.4.tar.gz cd lua-5.1.4 # 64λϵͳ£¬ÐèÔÚCFLAGSÀï¼ÓÉÏ-fPIC vi src/Makefile CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) make linux make install # pkg-config »·¾³±äÁ¿ cp etc/lua.pc /usr/local/lib/pkgconfig/ export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/local/lib/pkgconfig cd .. |
2.3.4 °²×°mysql-proxy
MySQL-Proxy¿Éͨ¹ýÒÔÏÂÍøÖ·»ñµÃ£ºhttp://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.2.tar.gz tar xvfz mysql-proxy-0.8.2.tar.gz cd mysql-proxy-0.8.2 ./configure make && make install #±àÒë°²×°µÄmysql-proxyÃ²ËÆÃ»ÓжÁдʵÏÖ·ÖÀëÏà¹ØµÄ½Å±¾£¬ËùÒÔÐèÒªÏÂÔØ°²×° wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz tar xvfz mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz cd mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit/share/doc mv mysql-proxy /usr/local/share/ |
2.4ÅäÖÃÆô¶¯mysql-proxy
2.4.1ÅäÖÃmysql-proxy
vi /etc/mysql-proxy.cnf
#´´½¨ÅäÖÃÎļþ£¬ÄÚÈÝÈçÏÂ
[mysql-proxy] proxy-address=192.168.9.159:3306 proxy-backend-addresses=192.168.9.154:3306 proxy-read-only-backend-addresses=192.168.9.155:3306 proxy-read-only-backend-addresses=192.168.9.156:3306 proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua keepalive=true log-level=error log-file=/home/logs/mysql-proxy.log |
2.4.2 ´´½¨mysql-proxyÆô¶¯½Å±¾
#´´½¨Æô¶¯½Å±¾£¬ÄÚÈÝÈçÏ£º
vi /etc/init.d/mysql-proxy
#!/bin/sh # # mysql-proxy This script starts and stops the mysql-proxy daemon # # chkconfig: - 78 30 # processname: mysql-proxy # description: mysql-proxy is a proxy daemon to mysql # Source function library. . /etc/rc.d/init.d/functions #LUA_PATH=/usr/local/lib/mysql-proxy/lua/?.lua PROXY_PATH=/usr/local/bin prog="mysql-proxy" # Source networking configuration. . /etc/sysconfig/network # Check that networking is up. [ ${NETWORKING} = "no" ] && exit 0 # Set default mysql-proxy configuration. PROXY_OPTIONS="--daemon --defaults-file=/etc/mysql-proxy.cnf" PROXY_PID=/var/run/mysql-proxy.pid # Source mysql-proxy configuration. if [ -f /etc/sysconfig/mysql-proxy ]; then . /etc/sysconfig/mysql-proxy fi PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH # By default it's all good RETVAL=0 # See how we were called. case "$1" in start) # Start daemon. echo -n $"Starting $prog: " daemon $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --pid-file $PROXY_PID RETVAL=$? echo if [ $RETVAL = 0 ]; then touch /var/lock/subsys/mysql-proxy fi ;; stop) # Stop daemons. echo -n $"Stopping $prog: " killproc $prog RETVAL=$? echo if [ $RETVAL = 0 ]; then rm -f /var/lock/subsys/mysql-proxy rm -f $PROXY_PID fi ;; restart) $0 stop sleep 3 $0 start ;; condrestart) [ -e /var/lock/subsys/mysql-proxy ] && $0 restart ;; status) status mysql-proxy RETVAL=$? ;; *) echo "Usage: $0 {start|stop|restart|status|condrestart}" RETVAL=1 ;; esac exit $RETVAL |
2.4.3 Æô¶¯mysql-proxy
chmod +x /etc/init.d/mysql-proxy #´´½¨ÈÕ־Ŀ¼ cd /home mkdir logs chmod 0777 logs # Æô¶¯mysql-proxy /etc/init.d/mysql-proxy start # Í£Ö¹mysql-proxy /etc/init.d/mysql-proxy stop # ÖØÆômysql-proxy /etc/init.d/mysql-proxy restart #Ìí¼Óµ½·þÎñ chkconfig mysql-proxy on #²é¿´mysql-proxyµÄ°ïÖúÎļþ mysql-proxy ¨Chelp-all #µÇ¼mysql-proxy mysql -uroot -p -h192.168.9.159 ¨CP3306 #²é¿´ÒѾ´úÀíµÄÊý¾Ý¿â show processlist; |
2.5 ²âÊÔÑéÖ¤¶Áд·ÖÀë
#1.·Ö±ðµÇ¼DB01 DB02 stop slave£» mysql -uroot -p mysql> stop slave£» #2.ÔÚDB01µÄtestÊý¾Ý¿â½¨Á¢Ò»¸ö±íhh mysql> use test; mysql> create table hh(id int(5),address char(255)); #ÔÚDB02µÄtestÊý¾Ý¿â½¨Á¢Ò»¸ö±íhh mysql> use test; mysql> create table hh(id int(5),name char(255)); #Á½¸ö±íÃû×ÖÒ»ÑùÈ´Óв»Í¬µÄ×ֶΡ£ #3.ÔÚproxyÉÏ¿ªÆômysql-proxy²¢µÇ¼ /etc/init.d/mysql-proxy start mysql -uroot -p -h192.168.9.159 ¨CP3306 #¶Ô±í½øÐвéѯ£¬½á¹ûÏÔʾDB02ÉÏ´´½¨µÄ±íµÄ½á¹¹£» mysql> use test; mysql> desc hh; +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | int(5) | YES | | NULL | | | name | char(255) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) #ÔÚµ±Ç°»·¾³Ï´´½¨Ò»¸ö±í mysql> create table hr_boy(id int(5),name char(255)); Query OK, 0 rows affected (0.01 sec) #ÔÚDB01ÉÏ¶Ô±í½øÐвéѯ£¬½á¹û mysql> use test; mysql> desc hr_boy; +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | int(5) | YES | | NULL | | | name | char(255) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
Èý¡¢ °²×°²¿ÊðMMM
»·¾³ÃèÊö£º
MMM·þÎñ¶Ë:192.168.9.159
MMM¿Í»§¶Ë£º192.168.9.157£¬192.168.9.158
3.1°²×°MMM·þÎñ¶Ë
±àÒë°²×°ÐèÒªÒÔÏÂËĸö×é¼þ£º
wget http://ftp.osuosl.org/pub/nslu2/sources/Algorithm-Diff-1.1902.tar.gz wget http://ftp.riken.go.jp/pub/pub/lang/CPAN/modules/by-module/Proc/EHOOD/Proc-Daemon-0.03.tar.gz wget http://mysql-master-master.googlecode.com/files/mysql-master-master-1.2.6.tar.gz yum -y install perl-DBD-MySQL |
ÔÚÕâÀïÎÒÃDzÉÓÃyum·½Ê½°²×°¡£
3.1.1 ÏȰ²×°Ïà¹ØµÄ°ü
yum -y install liblog-log4perl-perl libmailtools-perl liblog-dispatch-perl libclass-singleton
-perl libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl |
3.1.2 °²×°MMM
wget http://mirrors.ustc.edu.cn/fedora/epel//6/x86_64/epel-release-6-5.noarch.rpm rpm -ivh epel-release-6-5.noarch.rpm yum install -y mysql-mmm-agent mysql-mmm-monitor mysql-mmm-tool |
3.2 °²×°MMM¿Í»§¶Ë(DB01 DB02)
wget http://mirrors.ustc.edu.cn/fedora/epel//6/x86_64/epel-release-6-5.noarch.rpm rpm -ivh epel-release-6-5.noarch.rpm yum install -y mysql-mmm-agent |
Èý̨Ö÷»ú°²×°ÒÔÉÏÈí¼þºó£¬¼´¿É½øÐÐÅäÖÃ
3.3 ÅäÖÃMMM¿Í»§¶Ë(DB01 DB02)
3.3.1 ÐÞ¸Ämmm_common.conf
#All generic configuration-options are grouped in a separate file called
/etc/mysql-mmm/mmm_common.conf. This file will be the same on all hosts in the system: vi /etc/mysql-mmm/mmm_common.conf #----------------- active_master_role writer <host default> cluster_interface eth0 pid_path /var/run/mysql-mmm/mmm_agentd.pid bin_path /usr/libexec/mysql-mmm/ replication_user slave replication_password slave agent_user mmm_agent agent_password RepAgent </host> <host db1> ip 192.168.9.157 mode master peer db2 </host> <host db2> ip 192.168.9.158 mode master peer db1 </host> #<host db3> # ip 192.168.100.51 # mode slave #</host> <role writer> hosts db1, db2 ips 192.168.9.154 mode exclusive </role> <role reader> hosts db1, db2 ips 192.168.9.155£¬192.168.9.156 mode balanced </role> #------------------- #Don't forget to copy this file to all other hosts (including the monitoring host). |
3.3.2 ÐÞ¸Ämmm_agent.conf
On the database hosts we need toedit /etc/mysql-mmm/mmm_agent.conf.
Change ¡°db1¡± accordingly on the other hosts:
vi /etc/mysql-mmm/mmm_agent.conf #--------------------DB1---------------------------------- include mmm_common.conf this db1 #--------------------------------------------------------- #--------------------DB2---------------------------------- include mmm_common.conf this db2 #--------------------------------------------------------- |
3.4 ÅäÖÃMMM·þÎñ¶Ë
3.4.1 ÐÞ¸Ämmm_mon.conf
On the monitor host we need toedit /etc/mysql-mmm/mmm_mon.conf
vi /etc/mysql-mmm/mmm_mon.conf #--------------------------------------------------------- include mmm_common.conf <monitor> ip 127.0.0.1 pid_path /var/run/mmm_mond.pid bin_path /usr/lib/mysql-mmm/ status_path /var/lib/misc/mmm_mond.status ping_ips 192.168.9.157,192.168.9.158,192.168.9.254 auto_set_online 10 </monitor> <host default> monitor_user mmm_monitor monitor_password RepMonitor </host> debug 0 #--------------------------------------------------------- |
3.5 ÉèÖÃȨÏÞ£¨MMM¿Í»§¶Ë£©
ÔÚËùÓÐMMM¿Í»§¶ËΪ¼à¿Ø³ÌÐò´´½¨ÊÚȨÕʺÅ
GRANT ALL PRIVILEGES on *.* to'mmm_agent'@'%' identified by 'RepAgent'; GRANT ALL PRIVILEGES on *.* to'mmm_monitor'@'%' identified by 'RepMonitor'; flush privileges; |
3.6 MMM²âÊÔ
3.6.1 Æô¶¯MMM¿Í»§¶Ë£¨DB01 DB02£©
½«mysql-mmm-agentÌí¼ÓΪ·þÎñ£º
chkconfig mysql-mmm-agent on /etc/init.d/mysql-mmm-agent start Starting MMM Agent Daemon:
[ OK ] |
ÒÔÉÏÐÅϢ˵Ã÷¿Í»§¶ËÆô¶¯Õý³£
3.6.2 Æô¶¯MMM·þÎñÆ÷¶Ë£¨monitor£©
#(On the monitoring host) Edit/etc/default/mysql-mmm-monitor
to enable the monitor:
vi /etc/default/mysql-mmm-monitor #--------------------------------------------------------- ENABLED=1 #--------------------------------------------------------- #Then start it: chkconfig mysql-mmm-monitor on /etc/init.d/mysql-mmm-monitor start |
3.6.3 MMM״̬¼ì²é
£¨1£©#Wait some seconds formmmd_mon to start up. After
a few seconds you can use mmm_control to check thestatus
of the cluster:
[root@Proxy soft]# mmm_control show # Warning: agent on host db2 is not reachable db1(192.168.9.157) master/HARD_OFFLINE. Roles: db2(192.168.9.158) master/HARD_OFFLINE. Roles: |
£¨2£©ÉèÖÃhosts online (db1first, because the slaves replicate
from this host):
[root@Proxy soft]#mmm_control set_online db1 OK: State of 'db1' changed to ONLINE. Now you can wait some time and check its new roles! [root@Proxy soft]#mmm_control set_online db2 OK: State of 'db2' changed to ONLINE. Now you can wait some time and check its new roles! [root@ Proxy soft]# mmm_control checks all db2 ping [last change: 2013/09/10 21:52:01] OK db2 mysql [last change: 2013/09/10 21:52:04] OK db2 rep_threads [last change: 2013/09/10 21:20:22] OK db2 rep_backlog [last change: 2013/09/10 21:20:22] OK: Backlog is null db1 ping [last change: 2013/09/10 21:20:22] OK db1 mysql [last change: 2013/09/10 21:20:22] OK db1 rep_threads [last change: 2013/09/10 22:16:10] OK db1 rep_backlog [last change: 2013/09/10 21:20:22] OK: Backlog is null |
3.6.4Ä£Äâå´»ú²âÊÔ
Ëæ±ãÕÒÒ»¸ö¿Í»§¶Ë£¬Ö´ÐÐд²Ù×÷:
@client[root@mysql-1 ~]# vi /usr/local/mysql/binlog/inserting-into-db.sh #!/bin/bash while true; do mysql -uxxxx -pxxxxxx -h192.168.9.154 ¨CP3306 --database= test -e "insert into test values(null);" sleep 1 ; done; [root@mysql-1 ~]# ./inserting-into-db.sh & |
¿ÉÒÔ¿´µ½Á½¸ödbÖеÄbinlogÏÔʾµÄserver id¶¼ÊÇ1£¬Ò²¾ÍÊÇ˵µ±Ç°Çé¿öÏÂdb01ÊÇ×÷Ϊд¿â¡£
#Í£Ö¹db01
[root@DB01 ~]# /etc/init.d/mysqld stop Shutting down MySQL.. SUCCESS! |
Á¢¼´»Ö¸´DB1ºóproxyÉϲ鿴mmm¼¯Èº×´Ì¬
¼ì²émmmDB1µÄÈÕÖ¾£º
[root@DB01 binlog]# tail -f /var/log/mysql-mmm/mmm_agentd.log 2012/02/03 17:46:10 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.9.157:3306,
user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111 2012/02/03 17:46:13 INFO We have some new roles added or old rules deleted! 2012/02/03 17:46:13 INFO Deleted: reader(192.168.9.156), writer(192.168.9.154) 2012/02/03 17:46:13 FATAL Couldn't deny writes: ERROR: Can't connect to MySQL (host = 192.168.9.157:3306,
user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111 2012/02/03 17:59:48 INFO We have some new roles added or old rules deleted! 2012/02/03 17:59:48 INFO Added: reader(192.168.9.155) 2012/02/03 18:01:12 INFO We have some new roles added or old rules deleted! |
´ÓÈÕÖ¾¿ÉÒÔ¿´³ö£¬db1Í£Ö¹Ö®ºó£¬mmmÌáʾconnect error£¬ÓÉÓÚµ±Ç°µÄд¿âÊÇdb1£¬ÓÚÊÇmmmÈÏΪdb2ÉϵÄÊý¾ÝÒѾ²»ÄܺÍdb1±£³ÖÒ»ÖÂÁË£¬¹Ê°Ñdb2µÄ¶Á½ÇÉ«(reader)Ç¨ÒÆµ½db1ÉÏ¡£±ä³ÉÁË£º
db1(192.168.9.157) master/ONLINE. Roles: reader(192.168.9.157) db2(192.168.9.158) master/ONLINE. Roles: reader(192.168.9.156), writer(192.168.9.154£© |
µ«ÊÇ£¬ÈôDB1δÁ¢¼´»Ö¸´¹¤×÷£¬mmmµÄ¡±mysql¡±¼ì²éÏîÔÚ10Ãëºó³öÏÖ±¨¾¯£¬ÈÏΪdb1ÒѾ³¹µ×ʧ°Ü£¬Òò´Ë»á°Ñdb1ÉèÖÃ״̬Ϊhard_offline£¬°Ñ
db2´Óreplication_fail״̬Çл»µ½online״̬£¨ÒòΪdb2µÄmysqlÖÁÉÙ»¹»î×Å£©Í¬Ê±°ÑÉÏÃæµÄËùÓнÇÉ«Çл»µ½db2ÉÏ¡£×´Ì¬×î
ÖÕ±äΪ:
[root@Proxy mysql-mmm]# mmm_control show db1(192.168.9.157) master/HARD_OFFLINE. Roles: db2(192.168.9.158) master/ONLINE. Roles: reader(192.168.9.155), reader(192.168.9.156), writer(192.168.9.154) |
ºÜÏÔÈ»£¬µ±DB1»òDB2ÖÐµÄÆäÖÐһ̨崻úÖ®ºó£¬mmm¶¼»áÁ¢¼´½«å´»úµÄÖ÷»úµÄ½Çɫȫ²¿×ª»»µ½Áíһ̨DB¡£
×Ðϸ·ÖÎöMmmµÄ´¦Àí²½Öè´óÖÂÊÇ£º
db1µÄ¡°mysql¡±check»Ö¸´Õý³££¬È»ºó°Ñdb1Çл»µ½awaiting_recovery״̬¡£È»ºómmmÅжÏdb6µÄå´»úʱ¼äÔÚÕý³£·¶Î§ÄÚ£¬²»ÊôÓÚÒì³£Çé¿ö£¬Òò´Ë×Ô¶¯Çл»Îªonline״̬¡£
°Ñdb2ÖеÄÒ»¸öreader½ÇÉ«Ç¨ÒÆµ½db1ÉÏ¡£
Ŀǰд¿âÊÇdb2¡£
×¢£º¿ÉÒÔÔÚexclusive µÄ<rolewriter>ÖÐÉèÖÃprefer=db1£¬ÕâÑùÔÚdb1»Ö¸´Õý³£Ö®ºó£¬¾Í¿ÉÒÔÔٴα»Çл»ÎªÐ´¿âÁË¡£
|