最近看了一下mysql5.7的MGR叢集挺不錯的，有單主和多主模式，於是乎搭建測試了一下效果還不錯，我指的不錯是搭建和維護方面都比較簡單。網上絕大多數都是單主模式，當然我這裡也是，為了加深印象，特意記錄一下搭建過程，等以後再去嘗試多主模式，相信大家現在資料庫的瓶頸基本都是在寫，讀寫分離雖然是一種可行的解決方案，但是如果資料量很大，寫一樣會有問題，雖然有些解決方案能部署多個主節點，能同時進行讀寫，但是腦裂又是一個嚴重的問題，所以這裡MGR叢集內建了自動化腦裂防護機制又得到了很多人的青睞，這裡MGR簡稱MySQL Group Replication是MySQL官方於2016年12月推出的一個全新的高可用與高擴充套件的解決方案。注意本文這裡不再闡述原理性的東西。
注意：我這裡採用編譯安裝的方式，如果想簡單直接yum安裝mysql5.7也行，mysql編譯安裝需要的磁碟空間還是比較大的，一般在7G左右，所以要提前規劃好，用三個節點比較接近生產環境，而且更直接清晰。
詳細部署資訊如下：
| 主機名 | IP地址 | 安裝軟體 | 用途 |
| apache | 192.168.2.25 | cmake、boost、mysql | 節點 |
| nginx | 192.168.2.26 | cmake、boost、mysql | 節點 |
| kibana | 192.168.2.30 | cmake、boost、mysql | 節點 |
1、三臺機器準備工作
|
1 |
rpm -qa mysql mariadb |
如果有則解除安裝即可！
寫入hosts檔案對映關係，叢集用得到
192.168.2.25 apache
192.168.2.26 nginx
192.168.2.30 kibana
2、安裝依賴包
|
1 |
yum install gcc gcc-c++ ncurses-devel -y |
3、安裝cmake，下載地址：https://cmake.org/download/
|
1 2 3 4 |
tar zxvf cmake-3.7.2.tar.gz cd make-3.7.2 ./configure gmake && gmake install |
4、安裝boost，因為mysql5.7需要，注意這裡下載版本是1_59_0和mysql版本是對應的，如果你的MySQL版本和我的不一樣，不新增-DWITH_BOOST這個引數時它會報錯告訴你需要下載boost的哪個版本。
|
1 2 |
tar zxvf boost_1_59_0.tar.gz cp -r boost_1_59_0 /usr/local/boost |
5、安裝mysql5.7.17及初始化操作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
groupadd mysql useradd -M -s /sbin/nologin mysql -g mysql tar zxvf mysql-5.7.17.tar.gz cd mysql-5.7.17 cmake -DCMAKE_INSTALL_PREFIX=/data/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_BOOST=/usr/local/boost make make install chown -R mysql.mysql /data/mysql mv /etc/my.cnf /etc/my.cnf.default cp /data/mysql/support-files/my-default.cnf /etc/my.cnf /data/mysql/bin/mysqld --initialize --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data //注意初始化會生成一個隨機的密碼，請牢記 echo "PATH=$PATH:/data/mysql/bin" >> /etc/profile source /etc/profile cp /data/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld chmod +x /etc/rc.d/init.d/mysqld |
以上步驟在三臺機器上都需要執行
6、開始搭建MGR叢集環境，修改第一個節點的my.cnf檔案，內容如下：
|
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 |
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. basedir = /data/mysql datadir = /data/mysql/data port = 3306 socket = /data/mysql/data/mysql.sock log-error = /data/mysql/data/mysqld.log pid-file = /data/mysql/data/mysqld.pid # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # Group Replication server_id = 1 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log_bin = binlog binlog_format= ROW transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot = off loose-group_replication_local_address = '192.168.2.25:33061' loose-group_replication_group_seeds ='192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061' loose-group_replication_bootstrap_group = off [client] socket = /data/mysql/data/mysql.sock |
啟動mysql服務
/etc/init.d/mysqld start
|
1 2 3 4 5 6 7 8 9 10 11 |
set sql_log_bin=0; create user rpl_user@'%'; grant replication slave on *.* to rpl_user@'%' identified by 'rpl_pass'; flush privileges; set sql_log_bin=1; change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery'; install PLUGIN group_replication SONAME 'group_replication.so'; set global group_replication_bootstrap_group=ON; start group_replication; set global group_replication_bootstrap_group=OFF; select * from performance_schema.replication_group_members; |
顯示結果如下：
如果出現ONLINE，說明正常，這就是主節點，再搭建兩個從節點。
7、第二個節點加入叢集，複製剛剛的第一個節點的主配置檔案my.cnf，只需要修改兩個地方就行，已經用紅色標註
|
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 |
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. basedir = /data/mysql datadir = /data/mysql/data port = 3306 socket = /data/mysql/data/mysql.sock log-error = /data/mysql/data/mysqld.log pid-file = /data/mysql/data/mysqld.pid # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # Group Replication server_id = 2 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log_bin = binlog binlog_format= ROW transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot = off loose-group_replication_local_address = '192.168.2.26:33061' loose-group_replication_group_seeds ='192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061' loose-group_replication_bootstrap_group = off [client] socket = /data/mysql/data/mysql.sock |
第二個節點執行如下命令：
|
1 2 3 4 5 6 7 8 |
set sql_log_bin=0; create user rpl_user@'%'; grant replication slave on *.* to rpl_user@'%' identified by 'rpl_pass'; set sql_log_bin=1; change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery'; install plugin group_replication SONAME 'group_replication.so'; set global group_replication_allow_local_disjoint_gtids_join=ON; start group_replication; |
顯示結果如下：
同理第三個節點加入操作方法也和第二個節點一樣。
截圖如下：
查詢哪個是主節點：
從上圖來看很明顯apache主機是主節點。
測試步驟：
1、在主庫上建立一個庫，然後建立表，在兩個從庫上查詢資料是否同步？
2、兩個從庫只能執行查詢操作？
3、手動關閉主庫，確認兩個從庫其中一個是否會變成主庫？而且是MEMBER_ID第一個字母按優先順序排列的接管主庫？
日常維護步驟：
1、如果從庫某一節點關閉
|
1 |
start group_replication; |
2、如果所有的庫都關閉後，第一個庫作為主庫首先執行
|
1 2 |
set global group_replication_bootstrap_group=ON; start group_replication; |
剩下的庫直接執行即可！
|
1 |
start group_replication; |
3、如果主庫故障，會自動從兩個從庫選出一個主庫，主庫啟動後再次執行如下命令後會變成從庫
|
1 |
start group_replication; |