mysql5.6基于主从复制的mmm高可用架构详解
MMM规划
192.168.3.12 master
192.168.3.13 slave1
192.168.3.198 slave2
MMM部署步骤
1.配置主主复制及主从同步集群
2.安装主从节点所需要的支持包
3.安装及配置MMM工具集
4.运行MMM监控服务
5.测试
一、环境初始化
1、修改主机名
主机: 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 slave02
2、主机名解析
在manager上执行如下命令
[root@master ~]# cat >> /etc/hosts << EOF 192.168.3.200 manager 192.168.3.12 master 192.168.3.13 slave01 192.168.3.198 slave02 EOF [root@master ~]# scp -o StrictHostKeyChecking=no /etc/hosts root@slave01:/etc/ [root@master ~]# scp -o StrictHostKeyChecking=no /etc/hosts root@slave02:/etc/
二、规划mysql
①安装mysql
1.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 mysql useradd -r -g mysql mysql tar xf mysql-5.6.34.tar.gz cd mysql-5.6.34 cmake . -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_ci make && make install mkdir /data/mydata -p mkdir /data/binlogs/
# slave节点要创建/data/relaylogs
mkdir /data/relaylogs chown -R mysql.mysql /usr/local/mysql chown -R mysql.mysql /data/ cd /usr/local/mysql scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mydata cp support-files/mysql.server /etc/rc.d/init.d/mysqld cp support-files/my-default.cnf /etc/my.cnf
修改配置
mysql master配置：
[root@master ~]# cat /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock default-character-set = utf8mb4 [mysqld] port = 3306 innodb_file_per_table = 1 auto-increment-increment = 2 auto-increment-offset = 1 binlog-format=ROW log-slave-updates=true master-info-repository=table relay-log-info-repository=table sync-master-info=1 slave-parallel-workers=4 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=100 report-port=3306 log-bin=/data/binlogs/master-bin max_binlog_size = 200M datadir=/data/mydata socket=/tmp/mysql.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 skip-name-resolve skip-external-locking back_log = 300 max_connections = 1024 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 128 max_allowed_packet = 4M binlog_cache_size = 1M max_heap_table_size = 8M tmp_table_size = 16M read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 4M thread_cache_size = 8 query_cache_type = 1 query_cache_size = 16M query_cache_limit = 2M ft_min_word_len = 4 expire_logs_days = 10 performance_schema = 0 explicit_defaults_for_timestamp default_storage_engine = InnoDB innodb_open_files = 500 innodb_buffer_pool_size = 64M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 4 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 512M myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M
mysql slave01 配置：
[root@slave01 src]# cat /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock default-character-set = utf8mb4 [mysqld] port = 3306 innodb_file_per_table = 1 binlog-format=ROW read_only=on log-slave-updates=true auto-increment-increment = 2 auto-increment-offset = 2 master-info-repository=table relay-log-info-repository=table sync-master-info=1 slave-parallel-workers=4 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=200 report-port=3306 log-bin=/data/binlogs/master-bin relay-log=/data/relaylogs/relay-bin max_binlog_size = 200M datadir=/data/mydata socket=/tmp/mysql.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 skip-name-resolve skip-external-locking back_log = 300 max_connections = 1024 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 128 max_allowed_packet = 4M binlog_cache_size = 1M max_heap_table_size = 8M tmp_table_size = 16M read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 4M thread_cache_size = 8 query_cache_type = 1 query_cache_size = 16M query_cache_limit = 2M ft_min_word_len = 4 expire_logs_days = 10 performance_schema = 0 explicit_defaults_for_timestamp default_storage_engine = InnoDB innodb_open_files = 500 innodb_buffer_pool_size = 64M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 4 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 512M myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M
Mysql slave02配置
[root@slave02 ~]# cat /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock default-character-set = utf8mb4 [mysqld] port = 3306 innodb_file_per_table = 1 binlog-format=ROW log-slave-updates=true master-info-repository=table relay-log-info-repository=table sync-master-info=1 slave-parallel-workers=4 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=198 report-port=3306 log-bin=/data/binlogs/master-bin relay-log=/data/relaylogs/relay-bin # 从节点要配置relay-log max_binlog_size = 200M datadir=/data/mydata socket=/tmp/mysql.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 skip-name-resolve skip-external-locking back_log = 300 max_connections = 1024 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 128 max_allowed_packet = 4M binlog_cache_size = 1M max_heap_table_size = 8M tmp_table_size = 16M read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 4M thread_cache_size = 8 query_cache_type = 1 query_cache_size = 16M query_cache_limit = 2M ft_min_word_len = 4 expire_logs_days = 10 performance_schema = 0 explicit_defaults_for_timestamp default_storage_engine = InnoDB innodb_open_files = 500 innodb_buffer_pool_size = 64M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 4 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 512M myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M
配置从数据库服务器推荐配置项：
log-slave-updates = on
read_only=on [建议]
masterjnfo—repository =TABLE [建议]
relay-log-info-repository=TABLE [建议]
主主配置相关参数：
master参数：
auto-increment-increment = 2 # 步长
auto-increment-offset = 1 # 起始值
slave01备用主库：
auto-increment-increment = 2
auto-increment-offset = 2
每个节点的server-id必须不同
server-id=198
*************************************************
启动服务，保证master和两个slave节点都能正常启动
/etc/init.d/mysqld start
3.配置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.sql Enter password:
在slave01和slave02上还原
mysql -uroot -p < all2.sql
方法二：percona-xtrabackup
详情参考：http://www.cnblogs.com/reblue520/p/6894481.html
4.在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 event Master_Host: 192.168.3.12 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000017 Read_Master_Log_Pos: 191 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 315 Relay_Master_Log_File: master-bin.000017 Slave_IO_Running: Yes # 表示主从ok Slave_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 event Master_Host: 192.168.3.12 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000017 Read_Master_Log_Pos: 191 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 315 Relay_Master_Log_File: master-bin.000017 Slave_IO_Running: Yes Slave_SQL_Running: Yes
配置master和slave01之间的主主复制：
slave01上查看状态
mysql> show master status\G *************************** 1. row *************************** File: master-bin.000016 Position: 120 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 17062665-3b2a-11e7-99fe-000c29ba4e78:1-2202, 4c6237f8-a7da-11e6-9966-000c29f333f8:3-681:684-690 1 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 event Master_Host: 192.168.3.13 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000016 Read_Master_Log_Pos: 120 Relay_Log_File: master-relay-bin.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000016 Slave_IO_Running: Yes Slave_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 writer <host default> cluster_interface eth0 pid_path /var/run/mysql-mmm/mmm_agentd.pid bin_path /usr/libexec/mysql-mmm/ replication_user repl # replication_password replpass agent_user mmm_agent agent_password 123456 </host> <host db1> ip 192.168.3.12 mode master peer db2 </host> <host db2> ip 192.168.3.13 mode master peer db1 </host> <host db3> ip 192.168.3.198 mode slave </host> <role writer> hosts db1, db2 ips 192.168.3.90 mode exclusive </role> <role reader> hosts db1, db2, db3 ips 192.168.3.91,192.168.3.92,192.168.3.93 mode balanced </role>
②修改/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.conf this db1
slave01
# cat /etc/mysql-mmm/mmm_agent.conf include mmm_common.conf this db2
slave01
# cat /etc/mysql-mmm/mmm_agent.conf include mmm_common.conf this db3
③修改slave02即monitor的配置
[root@slave02 ~]# cat /etc/mysql-mmm/mmm_mon.conf include mmm_common.conf <monitor> ip 127.0.0.1 pid_path /run/mysql-mmm-monitor.pid bin_path /usr/libexec/mysql-mmm status_path /var/lib/mysql-mmm/mmm_mond.status ping_ips 192.168.3.12,192.168.3.13,192.168.3.198 # 三个节点的IP 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 123456 # 监控密码 </host> 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 show db1(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 stop Shutting down MySQL.. [ OK ] [root@master ~]# /etc/init.d/mysqld status MySQL is not running [FAILED]
再次查看监控节点状态，可以看到主节点已经切换到了slave01上
[root@slave02 mysql-mmm]# mmm_control show db1(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 show db1(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)
