mysql5.7 Mgr Cluster construction

650) this.width=650; "Width=" "height=" 621 "src=" https://wuguiyunwei.com/wp-content/uploads/2017/06/ 1200px-mysql.svg_.png "class=" Attachment-full size-full wp-post-image "style=" border:none;vertical-align:middle; height:auto;width:655.771px; "/>

mysql5.7 MGR Cluster build deployment

This article is written by the team member (earnest)

This article is from the Turtle Operation official website wuguiyunwei.com

QQ Group 602183872

Recently looked at the mysql5.7 of the MGR cluster is very good, there are single and multi-master mode, and then set up a test of the effect is good, I mean a good build and maintenance are relatively simple. Most of the internet is a single main mode, of course, I also here, in order to deepen the impression, deliberately record the construction process, and so on to try more than the main mode, I believe we are now the database bottleneck is basically written, read and write separation although is a feasible solution, but if the volume of data is very large, write the same problem, Although some solutions can deploy multiple master nodes, can read and write at the same time, but the brain fissure is a serious problem, so here Mgr Cluster built-in automatic brain crack protection mechanism and got a lot of people's favor, here Mgr short for the MySQL Group Replication is a new high-availability and highly scalable solution that MySQL officially launched in December 2016. Note that this article no longer describes the original rational things.
Note: I use the method of compiling and installing here, if want to simple direct Yum installs mysql5.7 also line, MySQL compile installs need disk space still is relatively big, generally in 7G about, so want to plan well in advance, use three nodes to compare close to production environment, and more direct clear.
Detailed deployment information is as follows:

host name	ip address	install software	purpose
apache	192.168.2.25	cmake, Boost, MySQL	node
nginx	192.168.2.26	cmake, Boost, MySQL	node
Kibana	192.168.2.30	CMake, Boost, MySQL	Node

1, three machine preparation work

1. Rpm-qa MySQL mariadb

If there is the uninstall can!
Writes the Hosts file mapping relationship, which is used by the cluster
192.168.2.25 Apache
192.168.2.26 Nginx
192.168.2.30 Kibana
2. Install dependent packages

1. Yum Install gcc gcc-c++ ncurses-devel-y

3, Installation cmake,:https://cmake.org/download/

1. Tar zxvf cmake-3.7.2.tar.gz

2. CD make-3.7.2

3. ./configure

4. Gmake && gmake Install

4, install boost, because mysql5.7 need, note here download version is 1_59_0 and MySQL version is corresponding, if your MySQL version and my not the same, do not add-dwith_boost this parameter when it will tell you need to download the version of Boost.

1. Tar zxvf boost_1_59_0.tar.gz

2. Cp-r Boost_1_59_0/usr/local/boost

5. Installation mysql5.7.17 and initialization operation

2. Groupadd MySQL

4. Useradd-m-s/sbin/nologin mysql-g MySQL

6. Tar zxvf mysql-5.7.17.tar.gz

8. CD mysql-5.7.17

10. 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

12. Make

14. Make install

16. Chown-r Mysql.mysql/data/mysql

18. Mv/etc/my.cnf/etc/my.cnf.default

20. Cp/data/mysql/support-files/my-default.cnf/etc/my.cnf

22. /data/mysql/bin/mysqld–initialize–user=mysql–basedir=/data/mysql–datadir=/data/mysql/data//Note initialization generates a Random password, please keep in mind

24. echo "path= $PATH:/data/mysql/bin" >>/etc/profile

26. Source/etc/profile

28. Cp/data/mysql/support-files/mysql.server/etc/rc.d/init.d/mysqld

30. chmod +x/etc/rc.d/init.d/mysqld

The above steps need to be performed on three machines

6, start to build MGR Cluster environment, modify the first node of the my.cnf file, the contents are as follows:

2. # for advice The change settings

4. # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

6. # * * * Don't EDIT this FILE. It ' s a template which'll be copied to the

8. # * * * default location during install, and would be replaced if you

10. # * * * * Upgrade to a newer version of MySQL.

12. 
    

14. [Mysqld]

16. 
    

18. # Remove Leading # and set to the amount of RAM for the most important data

20. # Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.

22. # innodb_buffer_pool_size = 128M

24. 
    

26. # Remove Leading # to turn on a very important data integrity option:logging

28. # Changes to the binary log between backups.

30. # Log_bin

32. 
    

34. # These is commonly set, remove the # and set as required.

36. Basedir =/data/mysql

38. DataDir =/data/mysql/data

40. Port = 3306

42. Socket =/data/mysql/data/mysql.sock

44. Log-error =/data/mysql/data/mysqld.log

46. Pid-file =/data/mysql/data/mysqld.pid

48. 
    

50. # Remove Leading # To set options mainly useful for reporting servers.

52. # The server defaults is faster for transactions and fast selects.

54. # Adjust sizes as needed, experiment to find the optimal values.

56. # join_buffer_size = 128M

58. # sort_buffer_size = 2M

60. # read_rnd_buffer_size = 2M

62. 
    

64. Sql_mode=no_engine_substitution,strict_trans_tables

66. 
    

68. # Group Replication

70. server_id = 1

72. Gtid_mode = On

74. Enforce_gtid_consistency = On

76. Master_info_repository = TABLE

78. Relay_log_info_repository = TABLE

80. Binlog_checksum = NONE

82. Log_slave_updates = On

84. Log_bin = Binlog

86. binlog_format= ROW

88. 
    

90. Transaction_write_set_extraction = XXHASH64

92. Loose-group_replication_group_name = ' ce9be252-2b71-11e6-b8f4-00212844f856 '

94. Loose-group_replication_start_on_boot = Off

96. loose-group_replication_local_address = ' 192.168.2.25:33061 '

98. Loose-group_replication_group_seeds = ' 192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061′

100. Loose-group_replication_bootstrap_group = Off

102. 
     

104. [Client]

106. Socket =/data/mysql/data/mysql.sock

start the MySQL service
/etc/init.d/mysqld start

1. Set sql_log_bin=0;

2. Create user [email protected] '% ';

3. Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' rpl_pass ';

4. Flush privileges;

5. Set sql_log_bin=1;

6. Change Master to master_user= ' Rpl_user ', master_password= ' Rpl_pass ' for Channel ' group_replication_recovery ';

7. Install PLUGIN group_replication SONAME ' group_replication.so ';

8. Set global group_replication_bootstrap_group=on;

9. Start group_replication;

10. Set global Group_replication_bootstrap_group=off;

11. SELECT * from Performance_schema.replication_group_members;

The results appear as follows:

650) this.width=650; "class=" Alignnone size-full wp-image-837 "src=" http://wuguiyunwei.com/wp-content/uploads/2017/ 06/23916356_14967352001r96.png "width=" 784 "height=" 109 "style=" Border:none;vertical-align:middle;height:auto; margin:5px 0px 20px; "/>

If online is present, it is normal, this is the master node, and then two slave nodes are built.

7, the second node joins the cluster, copy the first node's main configuration file my.cnf, only need to modify two places on the line, has been marked in red

2. # for advice The change settings

4. # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

6. # * * * Don't EDIT this FILE. It ' s a template which'll be copied to the

8. # * * * default location during install, and would be replaced if you

10. # * * * * Upgrade to a newer version of MySQL.

12. 
    

14. [Mysqld]

16. 
    

18. # Remove Leading # and set to the amount of RAM for the most important data

20. # Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.

22. # innodb_buffer_pool_size = 128M

24. 
    

26. # Remove Leading # to turn on a very important data integrity option:logging

28. # Changes to the binary log between backups.

30. # Log_bin

32. 
    

34. # These is commonly set, remove the # and set as required.

36. Basedir =/data/mysql

38. DataDir =/data/mysql/data

40. Port = 3306

42. Socket =/data/mysql/data/mysql.sock

44. Log-error =/data/mysql/data/mysqld.log

46. Pid-file =/data/mysql/data/mysqld.pid

48. 
    

50. # Remove Leading # To set options mainly useful for reporting servers.

52. # The server defaults is faster for transactions and fast selects.

54. # Adjust sizes as needed, experiment to find the optimal values.

56. # join_buffer_size = 128M

58. # sort_buffer_size = 2M

60. # read_rnd_buffer_size = 2M

62. 
    

64. Sql_mode=no_engine_substitution,strict_trans_tables

66. 
    

68. # Group Replication

70. server_id = 2

72. Gtid_mode = On

74. Enforce_gtid_consistency = On

76. Master_info_repository = TABLE

78. Relay_log_info_repository = TABLE

80. Binlog_checksum = NONE

82. Log_slave_updates = On

84. Log_bin = Binlog

86. binlog_format= ROW

88. 
    

90. Transaction_write_set_extraction = XXHASH64

92. Loose-group_replication_group_name = ' ce9be252-2b71-11e6-b8f4-00212844f856 '

94. Loose-group_replication_start_on_boot = Off

96. loose-group_replication_local_address = ' 192.168.2.26:33061 '

98. Loose-group_replication_group_seeds = ' 192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061′

100. Loose-group_replication_bootstrap_group = Off

102. 
     

104. [Client]

106. Socket =/data/mysql/data/mysql.sock

The second node executes the following command:

1. Set sql_log_bin=0;

2. Create user [email protected] '% ';

3. Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' rpl_pass ';

4. Set sql_log_bin=1;

5. Change Master to master_user= ' Rpl_user ', master_password= ' Rpl_pass ' for Channel ' group_replication_recovery ';

6. Install plugin group_replication SONAME ' group_replication.so ';

7. Set global group_replication_allow_local_disjoint_gtids_join=on;

8. Start group_replication;

The results appear as follows:

650) this.width=650; "class=" Alignnone size-full wp-image-838 "src=" http://wuguiyunwei.com/wp-content/uploads/2017/ 06/23916356_1496738002d6j5.png "width=" 782 "height=" "style=" Border:none;vertical-align:middle;height:auto; margin:5px 0px 20px; "/>

Similarly, the third node join operation method is the same as the second node.
As follows:

650) this.width=650; "class=" Alignnone size-full wp-image-839 "src=" http://wuguiyunwei.com/wp-content/uploads/2017/ 06/23916356_1496738093rr66.png "width=" 789 "height=" 141 "style=" Border:none;vertical-align:middle;height:auto; margin:5px 0px 20px; "/>

Query which is the primary node:

650) this.width=650; "class=" Alignnone size-full wp-image-840 "src=" http://wuguiyunwei.com/wp-content/uploads/2017/ 06/23916356_14967381613k9p.png "width=" 865 "height=" 109 "style=" Border:none;vertical-align:middle;height:auto; margin:5px 0px 20px; "/>

It is obvious from the point of view that the Apache host is the master node.

Test steps:
1, create a library on the main library, and then create a table, in two from the library to query whether the data is synchronized?
2, two can only perform query operations from the library?
2, manually close the main library, confirm two from the library will one of them become the main library? And is member_id the first letter prioritized by the takeover Main library?

Routine Maintenance steps:
1. If a node is closed from a library

1. Start group_replication;

2. If all libraries are closed, the first library is executed as the main library first

1. Set global group_replication_bootstrap_group=on;

2. Start group_replication;

The rest of the library can be executed directly!

1. Start group_replication;

3, if the main library failure, will automatically select a main library from the two libraries, the main library started after the execution of the following command will become from the library

1. Start group_replication;

mysql5.7 Mgr Cluster construction