之前介绍过一种MySQL的高可用方案是DRDB方案，此次介绍一种MySql另外一种MMM方案。
MMM方案一般用于MySql主主复制方案中。文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/multi-master-replication-manager-mysq/
|
0 1 2 3 |
[root@DB1 ~]# tail -3 /etc/hosts 1.1.1.131 DB1.t4x.org 1.1.1.132 DB2.t4x.org 1.1.1.133 DB3.t4x.org |
|
0 1 2 3 4 5 6 7 |
[root@DB3 ~]# ln -s /opt/mysql-5.7.16 /usr/local/mysql [root@DB3 ~]# mv /etc/my.cnf /etc/my.cnf.$(date +%F) [root@DB3 ~]# mkdir /usr/local/mysql/tmp [root@DB3 ~]# chown -R mysql.mysql /usr/local/mysql/tmp/ [root@DB3 ~]# cp /byrd/tools/mysql-5.7.16/support-files/mysql.server /etc/init.d/mysqld [root@DB3 ~]# chmod +x /etc/init.d/mysqld [root@DB3 ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql [root@DB3 ~]# /etc/init.d/mysqld start |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> create user 'repl'@'1.1.%.%' identified by 'admin'; Query OK, 0 rows affected (0.03 sec) mysql> grant replication slave on *.* to 'repl'@'1.1.%.%'; Query OK, 0 rows affected (0.01 sec) mysql> grant replication client on *.* to mmm_monitor@'1.1.%.%' identified by 'admin'; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> grant super,replication client,process on *.* to mmm_agent@'1.1.%.%' identified by 'admin'; mysql> flush privileges; Query OK, 0 rows affected (0.03 sec) [root@DB1 ~]# /usr/local/mysql/bin/mysqldump --master-data --single-transaction --triggers --routines --all-databases --events >> all.sql [root@DB1 ~]# scp all.sql root@1.1.1.132:/tmp/ [root@DB1 ~]# scp all.sql root@1.1.1.133:/tmp/ [root@DB1 ~]# cat all.sql | head -22 | tail -1 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=759; |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[root@DB2 ~]# /usr/local/mysql/bin/mysql -root -p < /tmp/all.sql [root@DB3 ~]# /usr/local/mysql/bin/mysql -root -p < /tmp/all.sql mysql> change master to master_host='1.1.1.131', -> MASTER_PORT=3306, -> MASTER_USER='repl', -> MASTER_PASSWORD='admin', -> MASTER_LOG_FILE='mysql-bin.000002', -> MASTER_LOG_POS=759; Query OK, 0 rows affected, 2 warnings (0.07 sec) mysql> start slave; Query OK, 0 rows affected (0.07 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 |
|
0 1 2 3 4 5 6 |
mysql> change master to master_host='1.1.1.132', -> MASTER_PORT=3306, -> MASTER_USER='repl', -> MASTER_PASSWORD='admin', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=757950; Query OK, 0 rows affected, 2 warnings (0.06 sec) |
|
0 1 2 3 4 5 6 7 8 |
[root@DB3 ~]# wget http://mirrors.opencas.org/epel/epel-release-latest-6.noarch.rpm [root@DB3 ~]# rpm -ivh epel-release-latest-6.noarch.rpm [root@DB3 ~]# wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm [root@DB3 ~]# sed -i s/enabled=0/enabled=1/g /etc/yum.repos.d/remi.repo [root@DB3 ~]# yum install mysql-mmm-agent #都必须安装 #### cairo dejavu-fonts-common dejavu-lgc-sans-mono-fonts dejavu-sans-mono-fonts fontconfig fontpackages-filesystem freetype libX11 libX11-common libXau libXft libXrender libpng libthai libxcb mailcap mysql-mmm pango perl-Algorithm-Diff perl-Compress-Raw-Zlib perl-Compress-Zlib perl-DBD-MySQL perl-DBI perl-Date-Manip perl-Email-Date-Format perl-HTML-Parser perl-HTML-Tagset perl-IO-Compress-Base perl-IO-Compress-Zlib perl-Log-Dispatch perl-Log-Dispatch-FileRotate perl-Log-Log4perl perl-MIME-Lite perl-MIME-Types perl-Mail-Sender perl-Mail-Sendmail perl-MailTools perl-Net-ARP perl-Params-Validate perl-Proc-Daemon perl-Proc-ProcessTable perl-Time-HiRes perl-TimeDate perl-URI perl-XML-DOM perl-XML-Parser perl-XML-RegExp perl-YAML-Syck perl-libwww-perl pixman rrdtool rrdtool-perl #### [root@DB3 ~]# yum install mysql-mmm-monitor perl-Class-Singleton mysql-mmm-tools perl-Path-Class #监控结点 |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
[root@DB1 mysql-mmm]# pwd /etc/mysql-mmm [root@DB1 mysql-mmm]# scp mmm_common.conf root@1.1.1.132:/etc/mysql-mmm/mmm_common.conf [root@DB1 mysql-mmm]# scp mmm_common.conf root@1.1.1.133:/etc/mysql-mmm/mmm_common.conf [root@DB1 mysql-mmm]# cat mmm_ mmm_agent.conf mmm_common.conf [root@DB1 mysql-mmm]# cat 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 admin agent_user mmm_agent agent_password admin </host> <host db1> ip 1.1.1.131 mode master peer db2 </host> <host db2> ip 1.1.1.132 mode master peer db1 </host> <host db3> ip 1.1.1.133 mode slave </host> <role writer> hosts db1, db2 ips 1.1.1.100 mode exclusive </role> <role reader> hosts db1, db2, db3 ips 1.1.1.101,1.1.1.102,1.1.1.103 mode balanced </role> [root@DB1 mysql-mmm]# tail -1 /etc/mysql-mmm/mmm_agent.conf this db1 [root@DB2 ~]# tail -1 /etc/mysql-mmm/mmm_agent.conf this db2 [root@DB3 ~]# tail -1 /etc/mysql-mmm/mmm_agent.conf this db3 |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[root@DB3 mysql-mmm]# grep -v "^$\|#" mmm_mon.conf include mmm_common.conf <monitor> ip 127.0.0.1 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 1.1.1.131,1.1.1.132,1.1.1.133 auto_set_online 60 </monitor> <host default> monitor_user mmm_monitor monitor_password admin </host> debug 0 |
|
0 1 2 3 |
[root@DB1 ~]# /etc/init.d/mysql-mmm-agent start [root@DB2 ~]# /etc/init.d/mysql-mmm-agent start [root@DB3 mysql-mmm]# /etc/init.d/mysql-mmm-agent start [root@DB3 mysql-mmm]# /etc/init.d/mysql-mmm-monitor start |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[root@DB3 mysql-mmm]# mmm_control show db1(1.1.1.131) master/ONLINE. Roles: reader(1.1.1.101), writer(1.1.1.100) db2(1.1.1.132) master/ONLINE. Roles: reader(1.1.1.102) db3(1.1.1.133) slave/ONLINE. Roles: reader(1.1.1.103) [root@DB1 mysql-mmm]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:0f:01:e1 brd ff:ff:ff:ff:ff:ff inet 1.1.1.131/24 brd 1.1.1.255 scope global eth0 inet 1.1.1.101/32 scope global eth0 inet 1.1.1.100/32 scope global eth0 inet6 fe80::20c:29ff:fe0f:1e1/64 scope link valid_lft forever preferred_lft forever [root@DB3 mysql-mmm]# mmm_control show db1(1.1.1.131) master/ONLINE. Roles: reader(1.1.1.101), writer(1.1.1.100) db2(1.1.1.132) master/HARD_OFFLINE. Roles: db3(1.1.1.133) slave/ONLINE. Roles: reader(1.1.1.102), reader(1.1.1.103) |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
[client] port = 3306 socket = /usr/local/mysql/tmp/mysql.sock [mysqld] user = mysql port = 3306 socket = /usr/local/mysql/tmp/mysql.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data open_files_limit = 10240 back_log = 600 max_connections = 3000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 300 query_cache_size = 64M query_cache_limit = 4M query_cache_min_res_unit = 2k thread_stack = 192K transaction_isolation = READ-COMMITTED tmp_table_size = 256M max_heap_table_size = 256M long_query_time = 2 log-error = /usr/local/mysql/tmp/error.log pid-file = /usr/local/mysql/tmp/mysql.pid log-bin = /usr/local/mysql/data/mysql-bin relay_log = /usr/local/mysql/data/relay-bin relay-log-info-file = /usr/local/mysql/data/relay-log.info binlog_cache_size = 4M max_binlog_cache_size = 8M max_binlog_size = 512M expire_logs_days = 7 key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_repair_threads = 1 lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062,1007 #replicate-ignore-db = mysql server-id = 131 #gtid_mode = on #enforce_gtid_consistency = on #explicit_defaults_for_timestamp = true #ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION sql_mode=NO_ENGINE_SUBSTITUTION #sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES #sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION log-bin = /usr/local/mysql/data/mysql-bin log-slave-updates = 1 binlog_format = row #skip_slave_start = 1 log-slave-updates = true #enforce-gtid-consistency = true auto_increment_increment = 2 auto_increment_offset = 1 |
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
