Build a Mysql MMM high-availability architecture using 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=mysql<span style="font-size:18px;"><strong>server-id=1</strong></span>table_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=mysql<span style="font-size:18px;"><strong>server-id=2</strong></span>table_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: <span> </span> <pre name = "code" class = "html"> master_log_file and master_log_pos can be viewed by running the following command on db2 to execute the mysql Command 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: <span> </span> <pre name = "code" class = "html"> master_log_file and master_log_pos 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      writerModify 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.<span style="font-size:18px;"><strong>this db1</strong></span>
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.<span style="font-size:18px;"><strong>this db2</strong></span>



Modify the monitor configuration file
/Etc/mysql-mmm/mmm_mon.conf. The content is as follows:

include mmm_common.conf<monitor>    ip                  <span style="font-size:18px;"><strong>192.168.0.136</strong></span>    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            <span style="font-size:18px;"><strong>192.168.0.135,192.168.0.137</strong></span>    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>



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