Shulou(Shulou.com)06/01 Report--

1. MySQL-MMM (Master-Master MySQL) MMM Overview dual master failover and daily management script program is composed of multiple mysql master servers and multiple mysql slave servers. Although it is called dual master replication, only one master is allowed to write to one master at a time in business, and the other alternative master provides partial read services. MMM is developed in Perl. Mainly used to monitor and manage MySQL Master-Master (dual master) replication of its internal additional tool scripts can also achieve multiple slave read load balancing.

Note:

Only one master is allowed to write at a time, and the extra master provides a partial read service.

Not suitable for situations where high data consistency is required (alternative product is: Heartbeat+DRBD+MySQL high availability scheme) MMM is composed of mmm_mond: monitoring process, responsible for all monitoring, decision and processing of all nodes mmm_agentd: agent process running in each MySQL database, monitoring local status and communicating with the monitoring side mmm_control: a script that provides commands for the mmm_mond process

Experimental environment:

Six Centos6, two master mysql (master01, master02), two slave mysql (slave01, slave02), one monitor (mmm_mond), one read / write scheduler (amoeba)

Deploy master master (master01 and master02) replication first

Master01

1. Preparation vim / etc/sysconfig/network-scripts/ifcfg-eth0 DEVICE=eth0 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=static IPADDR=192.168.1.10 NETMASK=255.255.255.0vim / etc/sysconfig/network-scripts/ifcfg-eth2 DEVICE=eth2 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=dhcpvim / etc/hosts 192.168.1.10 db1 192.168.1.20 db2 192.168.1.30 db3 192.168.1.40 db4vim / etc/sysconfig/network HOSTNAME=db1reboot2.YUM source configuration and install MMMrm-rf / etc/yum.repos.d/*wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repoyum-y install epel-releaseyum-y install mysql-mmm* mysql mysql-server mysql-devel3.MySQL configuration / etc/init.d/mysqld start & & chkconfig-- level 35 mysqld onmysqladmin-uroot password "123" cp / usr/share/doc/mysql-server-5.1. 73/my-medium.cnf / etc/my.cnfvim / etc/my.cnf [mysqld] 50 log-slave-updates/etc/init.d/mysqld restart4. Authorize and master to synchronize mysql-u root-pmysql > grant replication slave on *. * to 'slave'@'192.168.1.%' identified by' 123 synchronization investors MySQL > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000001 | 647 | +- + 1 row in set (0.00 sec) mysql > change master to master_host='192.168.1.20' Master_user='slave',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=481 / / the information followed by IP, log, pos and other information is the information of the second master: mysql > start slave;mysql > show slave status\ Gbank / check the synchronization status. It is correct if the thread status of I _ SQL and SQL is yes.

Master02

1. Preparation work vim / etc/sysconfig/network-scripts/ifcfg-eth0 DEVICE=eth0 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=static IPADDR=192.168.1.20 NETMASK=255.255.255.0vim / etc/sysconfig/network-scripts/ifcfg-eth2 DEVICE=eth2 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=dhcpvim / etc/hosts 192.168.1.10 db1 192.168.1.20 db2 192.168.1.30 db3 192.168.1.40 db4vim / etc/sysconfig/networkHOSTNAME=db2reboot2. YUM source configuration and install MMMrm-rf / etc/yum.repos.d/*wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repoyum-y install epel-releaseyum-y install mysql-mmm* mysql mysql-server mysql-devel3.MySQL configuration / etc/init.d/mysqld start & & chkconfig-- level 35 mysqld onmysqladmin-uroot password "123" cp / usr/share/doc/mysql-server-5.1.73/ My-medium.cnf / etc/my.cnfvim / etc/my.cnf [mysqld] 50 log-slave-updates 58 server-id = 2/etc/init.d/mysqld restart4. Authorize and master to synchronize mysql-u root-pmysql > grant replication slave on *. * to 'slave'@'192.168.1.%' identified by' 123 synchronization investors MySQL > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000001 | 481 | +- + 1 row in set (0.00 sec) mysql > change master to master_host='192.168.1.10' Master_user='slave',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=647 / / follow IP, log, pos and other information as the first master information mysql > start slave;mysql > show slave status\ G; deploy two pairs of master-slave replicas (M1 and S1 Magi m2 and S2)

Slave01

1. Preparation vim / etc/sysconfig/network-scripts/ifcfg-eth0 DEVICE=eth0 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=static IPADDR=192.168.1.30 NETMASK=255.255.255.0vim / etc/sysconfig/network-scripts/ifcfg-eth2 DEVICE=eth2 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=dhcpvim / etc/hosts 192.168.1.10 db1 192.168.1.20 db2 192.168.1.30 db3 192.168.1.40 db4vim / etc/sysconfig/network HOSTNAME=db3reboot2.YUM source configuration and install MMMrm-rf / etc/yum.repos.d/*wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repoyum-y install epel-releaseyum-y install mysql-mmm* mysql mysql-server mysql-devel3.MySQL configuration / etc/init.d/mysqld start & & chkconfig-- level 35 mysqld onmysqladmin-uroot password "123" cp / usr/share/doc/mysql-server-5.1. 73/my-medium.cnf / etc/my.cnfvim / etc/my.cnf [mysqld] 50 relay-log=relay-log-bin 51 relay-log-index=slave-relay-bin.index 59 server-id = 3/etc/init.d/mysqld restart4. Authorization and master-slave synchronization mysql- u root-pmysql > change master to master_host='192.168.1.10',master_user='slave',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=647;// follows IP, log, pos and other information is the first master information mysql > start slave;mysql > show slave status\ G

Slave02

1. Preparation vim / etc/sysconfig/network-scripts/ifcfg-eth0 DEVICE=eth0 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=static IPADDR=192.168.1.40 NETMASK=255.255.255.0vim / etc/sysconfig/network-scripts/ifcfg-eth2 DEVICE=eth2 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=dhcpvim / etc/hosts 192.168.1.10 db1 192.168.1.20 db2 192.168.1.30 db3 192.168.1.40 db42.YUM source configuration and security Install MMMrm-rf / etc/yum.repos.d/*wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repoyum-y install epel-releaseyum-y install mysql-mmm* mysql mysql-server mysql-devel3.MySQL configuration / etc/init.d/mysqld start & & chkconfig-- level 35 mysqld onmysqladmin-uroot password "123" cp / usr/share/doc/mysql-server-5.1.73/my-medium.cnf / Etc/my.cnfvim / etc/my.cnf [mysqld] 50 relay-log=relay-log-bin 51 relay-log-index=slave-relay-bin.index 59 server-id = 4/etc/init.d/mysqld restart4. Authorization and master-slave synchronization (m2recovers2) mysql- u root-pmysql > change master to master_host='192.168.1.20',master_user='slave',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=647;// follows IP, log, pos and other information is the second master information mysql > start slave;mysql > show slave status\ G; configure highly available MySQL-MMM

Master01

1. Create a new authorized user mysql > grant super,replication client,process on *. * to 'mmm_agent'@'192.168.1.%' identified by' 123 authorization from the master server and automatically synchronize mysql > grant replication client on *. * to 'mmm_monitor'@'192.168.1.%' identified by' 123' from the slave server / / Master server authorization, automatically synchronize mysql > grant all on *. * to 'test'@'192.168.1.%' identified by' 123 users from the server; / / create a new test user 2. Configure the MMM_COMMONvim / etc/mysql-mmm/mmm_common.conf active_master_role writer cluster_interface eth0 / / cluster IP-hosted interface pid_path / var/run/mysql-mmm/mmm_agentd.pid / / PID file location (the process number of the MMM) bin_path / usr/libexec/mysql-mmm/ run command location replication_user slave / / you need to use the authorized user replication_password 123agent_user mmm_agent / / proxy to connect to agent_password when master-slave synchronization is required 123 ip 192.168.1.10 mode master peer db2 / / when the db1 host is not available Automatically switch to db2 ip 192.168.1.20 mode master / / the current server as the primary server (write) peer db1 / / when the db2 host is not available Automatically switch to db1 ip 192.168.1.30 mode slave ip 192.168.1.40 mode slave / / current host as slave server (read) hosts db1 Db2 ips 192.168.1.250 / / write server VIP mode exclusive / / only one host can writer hosts db3, db4 ips 192.168.1.251 192.168.1.252 / read server VIP mode balanced / / multiple host can reader 3. Configure MMM_AGENTvim / etc/mysql-mmm/mmm_agent.confinclude mmm_common.confthis db14. Copy files to other hosts scp / etc/mysql-mmm/mmm_common.conf root@192.168.1.20:/etc/mysql-mmm/scp / etc/mysql-mmm/mmm_common.conf root@192.168.1.30:/etc/mysql-mmm/scp / etc/mysql-mmm/mmm_common.conf root@192.168.1.40:/etc/mysql-mmm//etc/init.d/mysql-mmm-agent restart & & chkconfig-- level 35 mysql-mmm-agent on

Master02

Configure MMM_AGENTvim / etc/mysql-mmm/mmm_agent.confthis db2/etc/init.d/mysql-mmm-agent restart & & chkconfig-- level 35 mysql-mmm-agent on

Slave01

Configure MMM_AGENTvim / etc/mysql-mmm/mmm_agent.confthis db3/etc/init.d/mysql-mmm-agent restart & & chkconfig-- level 35 mysql-mmm-agent on

Slave02

Configure MMM_AGENTvim / etc/mysql-mmm/mmm_agent.confthis db4/etc/init.d/mysql-mmm-agent restart & & chkconfig-- level 35 mysql-mmm-agent on Monitoring configuration (mmm_mon) 1. Preparation vim / etc/sysconfig/network-scripts/ifcfg-eth0 DEVICE=eth0 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=static IPADDR=192.168.1.50 NETMASK=255.255.255.0vim / etc/sysconfig/network-scripts/ifcfg-eth2 DEVICE=eth2 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=dhcpvim / etc/hosts 192.168.1.10 db1 192.168.1.20 db2 192.168.1.30 db3 192.168.1.40 db4reboot2.YUM source configuration and security Install MMMrm-rf / etc/yum.repos.d/*wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repoyum-y install epel-releaseyum-y install mysql-mmm* mysql3. Configure MMM_COMMONscp 192.168.1.10:/etc/mysql-mmm/mmm_common.conf / etc/mysql-mmm/4. Configure MMM_MONvim / etc/mysql-mmm/mmm_mon.conf include mmm_common.conf ip 127.0.0.1 ping_ips 192.168.1.10192.168.1.20192.168.1.30192.168.1.40 monitor_user mmm_monitor monitor_password 123 debug 05. Start the service and verify / etc/init.d/mysql-mmm-monitor restart & & chkconfig-- level 35 mysql-mmm-monitor on / / Monitor starts mmm_control show / / View node status mysql- u test-p-h 192.168.1.250

Error reporting solution:

[root@localhost] # mysql-u test-p-h 192.168.1.250Enter password:ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.254' (113)

Primary server:

Grant super,replication client,process on *. * to 'mmm_agent'@'db1' identified by' 123 grant super,replication client,process on *. * to 'mmm_agent'@'db2' identified by' 123 grant super,replication client,process on *. * to 'mmm_agent'@'db3' identified by' 123 grant super,replication client,process on *. * to 'mmm_agent'@'db4' identified by' 123'

Log view:

Agent log on the tail-f / var/log/mysql-mmm/mmm_agentd.log / / MySQL side tail-f / var/log/mysql-mmm/mmm_mond.log / / the Monitor log on the monitor side deploys the read-write scheduler Amoeba1. Environment preparation vim / etc/sysconfig/network-scripts/ifcfg-eth0 DEVICE=eth0 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=static IPADDR=192.168.1.254 NETMASK=255.255.255.02. Install jdk and amoebayum-y erase java-*chmod + x jdk-6u14-linux-x64.bin./jdk-6u14-linux-x64.binmv jdk1.6.0_14/ / usr/local/jdk1.6vim / etc/profileexport JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/binsource / etc/profile & java-versionmkdir / usr/local/amoebatar zxvf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/chmod-R 755 / usr/local/amoeba/3. Create a new authorized user mysql-u root-p mysql > grant all on *. * to @'192.168.1.%' identified by '123 administrator 4 in the main Mysql database. Edit amoeba profile vim / usr/local/amoeba/conf/amoeba.xml 30 hehe / / set connection Amoeba user 31 32 123 / / set connection Amoeba user 115 slaves116117 master Note 118 slaves / / define read server pool Vim / usr/local/amoeba/conf/dbServers.xml 25 26 / / set the user connecting to Mysql 27 28 123 / / set the password to connect to mysql Note 43 44 45 46 192.168.1.254 / define write server IP 47 48 49 50 51 52 192.168.1.30 / define read server IP 53 54 55 56 57 58 192.168.1.40 / define read server IP 59 60 61 62 68 slave1 Slave2 / / defines the host 69 amoeba start & netstat-utpln that enters the slaves read server pool | grep 80665. Connection Test client:mysql-u hehe-p-h 192.168.1.254-P 8066

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.