CentOS搭建MysqlMMM高可用架构_MySQL
CentOS
环境
CentOS
Mysql 5.1
前提
安装了EPEL，具体安装步骤请参照
http://blog.csdn.net/robinsonmhj/article/details/36184863
机器列表
机器IP 机器名 192.168.0.135 db1 192.168.0.136 monitor 192.168.0.137 db2 虚拟IP 作用 192.168.0.138 writter 192.168.0.139 reader 192.168.0.140 reader安装步骤
1. 在db1 和 db2 上安装mysql
yum install mysql-server
2. 修改配置文件
db1 的配置文件
[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 的配置文件
[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. 在db1和db2 上分别建立复制，监控用户
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. 把db1和db2互相设置成master和slave
4.1 在db1上执行，mysql命令
CHANGE MASTER TO master_host='192.168.0.137', master_port=3306, master_user='replication',master_password='replication', master_log_file='mysql-bin.000006', master_log_pos=106;注意：<span></span><pre name="code" class="html">master_log_file和master_log_pos的值通过如下命令察看在db2上执行如下mysql命令 show master status
CHANGE MASTER TO master_host='192.168.0.135', master_port=3306, master_user='replication',master_password='replication', master_log_file='mysql-bin.000006', master_log_pos=106;注意：<span></span><pre name="code" class="html">master_log_file和master_log_pos的值通过如下命令察看在db1上执行如下mysql命令 show master status
5. 在db1和db2上安装mysql-mmm-agent
yum install mysql-mmm-agent*
6. 在monitor上安装mysql-mmm-monitor
yum install mysql-mmm-monitor*
7. 修改配置文件
在db1，db2和monitor上修改
/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 replication replication_password replication agent_user mmm_agent agent_password agent</host><host db1> ip 192.168.0.135 mode master peer db2</host><host db2> ip 192.168.0.137 mode master peer db1</host><role writer> hosts db1,db2 ips 192.168.0.138 mode exclusive</role><role reader> hosts db1,db2 ips 192.168.0.139,192.168.0.140 mode balanced</role>
/etc/mysql-mmm/mmm_agent.conf
db1上的内容如下
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>
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>
修改配置monitor文件
/etc/mysql-mmm/mmm_mon.conf，内容如下
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><host default> monitor_user mmm_monitor monitor_password monitor</host>debug 0
8.在db1，db2上启动agent
# cd /etc/init.d/ # chkconfig mysql-mmm-agent on # service mysql-mmm-agent start
# cd /etc/init.d/ # chkconfig mysql-mmm-monitor on # service mysql-mmm-monitor start
10. 在monitor上察看
mmm_control -show status
10.2 把一台机器上线
mmm_control set_online db1
10.3 试验
把db1上的mysql停掉 service mysqld stop
察看状态mmm_control -show status，db1上的写ip应该飘到db2上
参考
http://blog.csdn.net/mydeman/article/details/6845567
http://hi.baidu.com/viewehsoitfmyzr/item/5024bec2ef02ccd196445280
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1664
14
1423
52
1318
25
1269
29
1248
24
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
Explain the role of InnoDB redo logs and undo logs.
Apr 15, 2025 am 12:16 AM
InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.
MySQL: An Introduction to the World's Most Popular Database
Apr 12, 2025 am 12:18 AM
MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.
MySQL's Place: Databases and Programming
Apr 13, 2025 am 12:18 AM
MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen
Why Use MySQL? Benefits and Advantages
Apr 12, 2025 am 12:17 AM
MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.
MySQL vs. Other Programming Languages: A Comparison
Apr 19, 2025 am 12:22 AM
Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
MySQL: From Small Businesses to Large Enterprises
Apr 13, 2025 am 12:17 AM
MySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.
How does MySQL index cardinality affect query performance?
Apr 14, 2025 am 12:18 AM
MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.
