Building a high-availability architecture of MysqlMMM using CentOS _ MySQL

CentOS build MysqlMMM high availability architecture CentOS

Environment

CentOS

For Mysql 5.1

Prerequisites

EPEL is installed. for detailed installation steps, see

Http://blog.csdn.net/robinsonmhj/article/details/36184863

Machine List

Machine IP address	Machine name
192.168.0.135	Db1
192.168.0.136	Monitor
192.168.0.20.	Db2

Virtual IP address	Function
192.168.0.138	Writter
192.168.0.139	Reader
192.168.0.140	Reader

Installation steps

1. install mysql on db1 and db2

Yum install mysql-server

2. modify the configuration file

Db1 configuration file

[mysqld]datadir = /data/mysqlsocket=/data/mysql/mysql.sockuser=mysqlserver-id=1table_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 64Mthread_concurrency = 8log-bin=mysql-binbinlog_format = mixedmax_connections=2048character_set_server=utf8wait_timeout=1800interactive_timeout=1800skip-show-databaseskip-name-resolvetmp_table_size = 512Mmax_heap_table_size = 512Mbinlog-ignore-db = mysqlreplicate-ignore-db = mysqlbinlog-ignore-db = information_schemareplicate-ignore-db = information_schemabinlog-ignore-db = performance_schemareplicate-ignore-db = performance_schemabinlog-ignore-db = testreplicate-ignore-db = testinnodb_data_home_dir=/data/mysql/innodbinnodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir=/data/mysql/innodb/loginnodb_file_per_table=1innodb_buffer_pool_size = 1000Minnodb_additional_mem_pool_size = 20Minnodb_log_file_size = 100Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 50default-storage-engine = MyISAM#default-storage-engine = INNODB[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysqldump]quickmax_allowed_packet = 16Msocket=/data/mysql/mysql.sock[mysql]no-auto-rehashsocket=/data/mysql/mysql.sock

Db2 configuration file

[mysqld]datadir = /data/mysqlsocket=/data/mysql/mysql.sockuser=mysqlserver-id=2table_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 64Mthread_concurrency = 8log-bin=mysql-binbinlog_format = mixedmax_connections=2048character_set_server=utf8wait_timeout=1800interactive_timeout=1800skip-show-databaseskip-name-resolvetmp_table_size = 512Mmax_heap_table_size = 512Mbinlog-ignore-db = mysqlreplicate-ignore-db = mysqlbinlog-ignore-db = information_schemareplicate-ignore-db = information_schemabinlog-ignore-db = performance_schemareplicate-ignore-db = performance_schemabinlog-ignore-db = testreplicate-ignore-db = testinnodb_data_home_dir=/data/mysql/innodbinnodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir=/data/mysql/innodb/loginnodb_file_per_table=1innodb_buffer_pool_size = 1000Minnodb_additional_mem_pool_size = 20Minnodb_log_file_size = 100Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 50default-storage-engine = MyISAM#default-storage-engine = INNODB[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysqldump]quickmax_allowed_packet = 16Msocket=/data/mysql/mysql.sock[mysql]no-auto-rehashsocket=/data/mysql/mysql.sock

3. Create copies on db1 and db2 respectively to monitor users

    GRANT REPLICATION CLIENT                 ON *.* TO 'mmm_monitor'@'192.168.0.136' IDENTIFIED BY 'monitor';      GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.0.%'   IDENTIFIED BY 'agent';      GRANT REPLICATION SLAVE                  ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY 'replication';  

4. set db1 and db2 to master and slave.

4.1 run the mysql command on db1.

Change master to master_host = '2017. 168.0.20.', master_port = 3306, master_user = 'replicase', master_password = 'replicase', master_log_file = 'MySQL-bin.000006 ', master_log_pos = 106; note:
The master_log_file and master_log_pos values can be viewed through the following command: execute the following mysql command on db2: show master status
 

4.2 run the mysql command on db2
Change master to master_host = '2017. 168.0.135 ', master_port = 3306, master_user = 'replicase', master_password = 'replicase', master_log_file = 'MySQL-bin.000006', master_log_pos = 106; note:
The master_log_file and master_log_pos values can be viewed by running the following command on db1: execute the following mysql command: show master status


 



5. install mysql-mmm-agent on db1 and db2
Yum install mysql-mmm-agent *


6. install mysql-mmm-monitor on monitor
Yum install mysql-mmm-monitor *

7. modify the configuration file
Modify on db1, db2, and monitor
/Etc/mysql-mmm/mmm_common.conf. the modification is as follows:
active_master_role      writer
   
        cluster_interface       eth0    pid_path                /var/run/mysql-mmm/mmm_agentd.pid    bin_path                /usr/libexec/mysql-mmm/    replication_user        replication    replication_password    replication    agent_user              mmm_agent    agent_password          agent
   
   
        ip      192.168.0.135    mode    master    peer    db2
   
   
        ip      192.168.0.137    mode    master   peer    db1
   
   
        hosts   db1,db2    ips     192.168.0.138    mode    exclusive
   
   
        hosts   db1,db2    ips     192.168.0.139,192.168.0.140    mode    balanced
   
Modify configuration file
/Etc/mysql-mmm/mmm_agent.conf
The content on db1 is as follows:
include mmm_common.conf# The 'this' variable refers to this server.  Proper operation requires # that 'this' server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf.this db1
The content on db2 is as follows:
include mmm_common.conf# The 'this' variable refers to this server.  Proper operation requires # that 'this' server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf.this db2

Modify the monitor configuration file
/Etc/mysql-mmm/mmm_mon.conf. the content is as follows:
include mmm_common.conf
   
        ip                  
    192.168.0.136    pid_path            /var/run/mysql-mmm/mmm_mond.pid    bin_path            /usr/libexec/mysql-mmm    status_path         /var/lib/mysql-mmm/mmm_mond.status    ping_ips            
    192.168.0.135,192.168.0.137    auto_set_online     60    # The kill_host_bin does not exist by default, though the monitor will    # throw a warning about it missing.  See the section 5.10 "Kill Host    # Functionality" in the PDF documentation.    #    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host    #
   
   
        monitor_user        mmm_monitor    monitor_password    monitor
   debug 0

8. start the agent on db1 and db2
    # cd /etc/init.d/      # chkconfig mysql-mmm-agent on      # service mysql-mmm-agent start  

9. start monitor on monitor
    # cd /etc/init.d/      # chkconfig mysql-mmm-monitor on      # service mysql-mmm-monitor start  
10. View on monitor
Mmm_control-show status
10.2 launch a machine
Mmm_control set_online db1

10.3 Test
Stop mysql on db1 from service mysqld stop
Check the status of mmm_control-show status. the write ip address on db1 should flow to db2.

Reference
Http://blog.csdn.net/mydeman/article/details/6845567
Http://hi.baidu.com/viewehsoitfmyzr/item/5024bec2ef02ccd196445280