MMM规划
192.168.3.12 master192.168.3.13 slave1192.168.3.198 slave2MMM部署步骤
1.配置主主复制及主从同步集群2.安装主从节点所需要的支持包3.安装及配置MMM工具集4.运行MMM监控服务5.测试一、环境初始化
主机: master执行命令
# sed -i 's/HOSTNAME=.*/HOSTNAME=master/g' /etc/sysconfig/network && hostname master主机: slave01执行命令# sed -i 's/HOSTNAME=.*/HOSTNAME=slave01/g' /etc/sysconfig/network && hostname slave01主机: slave02执行命令(centos7.3)# hostnamectl set-hostname slave022、主机名解析
在manager上执行如下命令
[root@master ~]# cat >> /etc/hosts << EOF192.168.3.200 manager192.168.3.12 master192.168.3.13 slave01192.168.3.198 slave02EOF[root@master ~]# scp -o StrictHostKeyChecking=no /etc/hosts root@slave01:/etc/[root@master ~]# scp -o StrictHostKeyChecking=no /etc/hosts root@slave02:/etc/
二、规划mysql
①安装mysql1.yum安装相关依赖
yum -y install gcc gcc-c++ compat-gcc-34-g77 autoconf automake zlib zlib-devel libxml2-devel ncurses-devel libmcrypt-devel libmcrypt libtool-ltdl-devel* make cmake bison git openssl openssl-devel
2.编译安装
groupadd mysqluseradd -r -g mysql mysqltar xf mysql-5.6.34.tar.gzcd mysql-5.6.34cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mydata -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DWITH_LIBWRAP=0 -DDEFAULT_COLLATION=utf8_general_cimake && make installmkdir /data/mydata -pmkdir /data/binlogs/
# slave节点要创建/data/relaylogs
mkdir /data/relaylogschown -R mysql.mysql /usr/local/mysqlchown -R mysql.mysql /data/cd /usr/local/mysqlscripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mydatacp support-files/mysql.server /etc/rc.d/init.d/mysqldcp support-files/my-default.cnf /etc/my.cnf
修改配置
mysql master配置：[root@master ~]# cat /etc/my.cnf [client]port = 3306socket = /tmp/mysql.sockdefault-character-set = utf8mb4[mysqld]port = 3306innodb_file_per_table = 1auto-increment-increment = 2auto-increment-offset = 1binlog-format=ROWlog-slave-updates=truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers=4binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1server-id=100report-port=3306log-bin=/data/binlogs/master-binmax_binlog_size = 200Mdatadir=/data/mydatasocket=/tmp/mysql.socksql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESinit-connect = 'SET NAMES utf8mb4'character-set-server = utf8mb4skip-name-resolveskip-external-lockingback_log = 300max_connections = 1024max_connect_errors = 6000open_files_limit = 65535table_open_cache = 128max_allowed_packet = 4Mbinlog_cache_size = 1Mmax_heap_table_size = 8Mtmp_table_size = 16Mread_buffer_size = 2Mread_rnd_buffer_size = 8Msort_buffer_size = 8Mjoin_buffer_size = 8Mkey_buffer_size = 4Mthread_cache_size = 8query_cache_type = 1query_cache_size = 16Mquery_cache_limit = 2Mft_min_word_len = 4expire_logs_days = 10performance_schema = 0explicit_defaults_for_timestampdefault_storage_engine = InnoDBinnodb_open_files = 500innodb_buffer_pool_size = 64Minnodb_write_io_threads = 4innodb_read_io_threads = 4innodb_thread_concurrency = 4innodb_purge_threads = 1innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 32Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120bulk_insert_buffer_size = 8Mmyisam_sort_buffer_size = 8Mmyisam_max_sort_file_size = 512Mmyisam_repair_threads = 1interactive_timeout = 28800wait_timeout = 28800[mysqldump]quickmax_allowed_packet = 16M[myisamchk]key_buffer_size = 8Msort_buffer_size = 8Mread_buffer = 4Mwrite_buffer = 4M
mysql slave01 配置：
[root@slave01 src]# cat /etc/my.cnf [client]port = 3306socket = /tmp/mysql.sockdefault-character-set = utf8mb4[mysqld]port = 3306innodb_file_per_table = 1binlog-format=ROWread_only=onlog-slave-updates=trueauto-increment-increment = 2auto-increment-offset = 2master-info-repository=tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers=4binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1server-id=200report-port=3306log-bin=/data/binlogs/master-binrelay-log=/data/relaylogs/relay-binmax_binlog_size = 200Mdatadir=/data/mydatasocket=/tmp/mysql.socksql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESinit-connect = 'SET NAMES utf8mb4'character-set-server = utf8mb4skip-name-resolveskip-external-lockingback_log = 300max_connections = 1024max_connect_errors = 6000open_files_limit = 65535table_open_cache = 128max_allowed_packet = 4Mbinlog_cache_size = 1Mmax_heap_table_size = 8Mtmp_table_size = 16Mread_buffer_size = 2Mread_rnd_buffer_size = 8Msort_buffer_size = 8Mjoin_buffer_size = 8Mkey_buffer_size = 4Mthread_cache_size = 8query_cache_type = 1query_cache_size = 16Mquery_cache_limit = 2Mft_min_word_len = 4expire_logs_days = 10performance_schema = 0explicit_defaults_for_timestampdefault_storage_engine = InnoDBinnodb_open_files = 500innodb_buffer_pool_size = 64Minnodb_write_io_threads = 4innodb_read_io_threads = 4innodb_thread_concurrency = 4innodb_purge_threads = 1innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 32Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120bulk_insert_buffer_size = 8Mmyisam_sort_buffer_size = 8Mmyisam_max_sort_file_size = 512Mmyisam_repair_threads = 1interactive_timeout = 28800wait_timeout = 28800[mysqldump]quickmax_allowed_packet = 16M[myisamchk]key_buffer_size = 8Msort_buffer_size = 8Mread_buffer = 4Mwrite_buffer = 4M
Mysql slave02配置
[root@slave02 ~]# cat /etc/my.cnf [client]port = 3306socket = /tmp/mysql.sockdefault-character-set = utf8mb4[mysqld]port = 3306innodb_file_per_table = 1binlog-format=ROWlog-slave-updates=truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers=4binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1server-id=198report-port=3306log-bin=/data/binlogs/master-binrelay-log=/data/relaylogs/relay-bin # 从节点要配置relay-logmax_binlog_size = 200Mdatadir=/data/mydatasocket=/tmp/mysql.socksql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESinit-connect = 'SET NAMES utf8mb4'character-set-server = utf8mb4skip-name-resolveskip-external-lockingback_log = 300max_connections = 1024max_connect_errors = 6000open_files_limit = 65535table_open_cache = 128max_allowed_packet = 4Mbinlog_cache_size = 1Mmax_heap_table_size = 8Mtmp_table_size = 16Mread_buffer_size = 2Mread_rnd_buffer_size = 8Msort_buffer_size = 8Mjoin_buffer_size = 8Mkey_buffer_size = 4Mthread_cache_size = 8query_cache_type = 1query_cache_size = 16Mquery_cache_limit = 2Mft_min_word_len = 4expire_logs_days = 10performance_schema = 0explicit_defaults_for_timestampdefault_storage_engine = InnoDBinnodb_open_files = 500innodb_buffer_pool_size = 64Minnodb_write_io_threads = 4innodb_read_io_threads = 4innodb_thread_concurrency = 4innodb_purge_threads = 1innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 32Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120bulk_insert_buffer_size = 8Mmyisam_sort_buffer_size = 8Mmyisam_max_sort_file_size = 512Mmyisam_repair_threads = 1interactive_timeout = 28800wait_timeout = 28800[mysqldump]quickmax_allowed_packet = 16M[myisamchk]key_buffer_size = 8Msort_buffer_size = 8Mread_buffer = 4Mwrite_buffer = 4M
配置从数据库服务器推荐配置项：log-slave-updates = onread_only=on [建议]masterjnfo—repository =TABLE [建议]relay-log-info-repository=TABLE [建议]
主主配置相关参数：
master参数：auto-increment-increment = 2 # 步长auto-increment-offset = 1 # 起始值slave01备用主库：
auto-increment-increment = 2auto-increment-offset = 2每个节点的server-id必须不同
server-id=198*************************************************启动服务，保证master和两个slave节点都能正常启动
/etc/init.d/mysqld start3.配置master、slave01和slave02之间基于binlog的主从复制
在MySQL5.6 的Replication配置中，master端同样要开启两个重要的选项，server-id和log-bin,并且选项server-id在全局架构中并且唯一，不能被其它主机使用，这里采用主机ip地址的最后一位充当server-id的值；slave端要开启relay-log在master建立主从复制账号
mysql> create user repl@'192.168.3.%' identified by 'replpass';mysql> grant replication slave on *.* to repl@'192.168.3.%';
如果数据库有数据的情况下需要先对数据库进行备份，然后恢复到几个从库中：
方法①:
[root@master ~]# mysqldump --single-transaction --set-gtid-purged=OFF --master-data=2 --triggers --routines --all-databases -uroot -p >all2.sqlEnter password:
在slave01和slave02上还原
mysql -uroot -p < all2.sql方法二：percona-xtrabackup
详情参考：http://www.cnblogs.com/reblue520/p/6894481.html4.在slave01和slave02上执行主从同步
master:mysql> show master status;+-------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+------------------------------------------+| master-bin.000017 | 191 | | | 4c6237f8-a7da-11e6-9966-000c29f333f8:1-2 |+-------------------+----------+--------------+------------------+------------------------------------------+
slave01:
mysql> change master to master_host='192.168.3.12',master_user='repl',master_password='replpass',master_log_file='master-bin.000017',master_log_pos=191;mysql> start slave;mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.3.12Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000017Read_Master_Log_Pos: 191Relay_Log_File: relay-bin.000002Relay_Log_Pos: 315Relay_Master_Log_File: master-bin.000017Slave_IO_Running: Yes # 表示主从okSlave_SQL_Running: Yes
slave02:
mysql> change master to master_host='192.168.3.12',master_user='repl',master_password='replpass',master_log_file='master-bin.000017',master_log_pos=191;mysql> start slave;mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.3.12Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000017Read_Master_Log_Pos: 191Relay_Log_File: relay-bin.000002Relay_Log_Pos: 315Relay_Master_Log_File: master-bin.000017Slave_IO_Running: Yes Slave_SQL_Running: Yes
配置master和slave01之间的主主复制：
slave01上查看状态
mysql> show master status\G*************************** 1. row ***************************File: master-bin.000016Position: 120Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 17062665-3b2a-11e7-99fe-000c29ba4e78:1-2202,4c6237f8-a7da-11e6-9966-000c29f333f8:3-681:684-6901 row in set (0.00 sec)
master上执行主从同步：
mysql> change master to master_host='192.168.3.13',master_user='repl',master_password='replpass',master_log_file='master-bin.000016',master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.22 sec)mysql> start slave;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.3.13Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000016Read_Master_Log_Pos: 120Relay_Log_File: master-relay-bin.000002Relay_Log_Pos: 284Relay_Master_Log_File: master-bin.000016Slave_IO_Running: YesSlave_SQL_Running: Yes
三、安装配置mmm
1.在每个节点上都安装mmm客户端
# yum install -y mysql-mmm-agent.noarch
2.slave02上安装mysql-mmm即监控端monitor
# yum install mysql-mmm* -y
注意：经过测试在centos7上yum方式安装监控无法启动，源码编译安装也有各种问题，最好使用centos6
3.主库上建立mmm_agent和mmm_monitor用户：
创建mmm_agent用户mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.3.%' identified by '123456';创建mmm_monitor用户mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.3.%' identified by '123456';
4.修改配置
①修改/etc/mysql-mmm/mmm_common.conf配置文件，并拷贝到slave01和slave02上
# vim /etc/mysql-mmm/mmm_common.conf active_master_role writercluster_interface eth0pid_path /var/run/mysql-mmm/mmm_agentd.pidbin_path /usr/libexec/mysql-mmm/replication_user repl # replication_password replpassagent_user mmm_agentagent_password 123456 ip 192.168.3.12mode masterpeer db2 ip 192.168.3.13mode masterpeer db1 ip 192.168.3.198mode slave hosts db1, db2ips 192.168.3.90mode exclusive hosts db1, db2, db3ips 192.168.3.91,192.168.3.92,192.168.3.93mode balanced
②修改/etc/mysql-mmm/mmm_agent.conf master、slave01和slave02内容分别是 this db1/this db2/this db3
master
# cat /etc/mysql-mmm/mmm_agent.conf include mmm_common.confthis db1
slave01
# cat /etc/mysql-mmm/mmm_agent.conf include mmm_common.confthis db2
slave01
# cat /etc/mysql-mmm/mmm_agent.conf include mmm_common.confthis db3
③修改slave02即monitor的配置
[root@slave02 ~]# cat /etc/mysql-mmm/mmm_mon.conf include mmm_common.confip 127.0.0.1pid_path /run/mysql-mmm-monitor.pidbin_path /usr/libexec/mysql-mmmstatus_path /var/lib/mysql-mmm/mmm_mond.statusping_ips 192.168.3.12,192.168.3.13,192.168.3.198 # 三个节点的IPauto_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 123456 # 监控密码 debug 0
启动所有节点上的mmm_agent服务
# /etc/init.d/mysql-mmm-agent start启动slave02的monitor服务
# /etc/init.d/mysql-mmm-monitor start四、进行主主切换测试：
监控节点执行检测功能，可以看到master有3.90写角色和3.92读的角色[root@slave02 mysql-mmm]# mmm_control showdb1(192.168.3.12) master/ONLINE. Roles: reader(192.168.3.92), writer(192.168.3.90)db2(192.168.3.13) master/ONLINE. Roles: reader(192.168.3.93)db3(192.168.3.198) slave/ONLINE. Roles: reader(192.168.3.91)
1.停止master上的mysql服务
[root@master ~]# /etc/init.d/mysqld stopShutting down MySQL.. [ OK ][root@master ~]# /etc/init.d/mysqld statusMySQL is not running [FAILED]
再次查看监控节点状态，可以看到主节点已经切换到了slave01上
[root@slave02 mysql-mmm]# mmm_control showdb1(192.168.3.12) master/HARD_OFFLINE. Roles: db2(192.168.3.13) master/ONLINE. Roles: reader(192.168.3.93), writer(192.168.3.90)db3(192.168.3.198) slave/ONLINE. Roles: reader(192.168.3.91), reader(192.168.3.92)
通过在slave02上查看从库状态也可以看到主服务器已经重新指向了slave02
2.重新启动原master节点上的mysql服务，主节点也不会切回原节点
[root@slave02 mysql-mmm]# mmm_control showdb1(192.168.3.12) master/ONLINE. Roles: reader(192.168.3.91)db2(192.168.3.13) master/ONLINE. Roles: reader(192.168.3.93), writer(192.168.3.90)db3(192.168.3.198) slave/ONLINE. Roles: reader(192.168.3.92)