mysql5.7.20组复制gtid的安装

测试环境介绍： 
三台机器ip     主机名称     mysql版本                                   是否主
192.168.11.81 alimysql01    5.7.20-log MySQL Community Server (GPL)     主
192.168.11.82 alimysql02    5.7.20-log MySQL Community Server (GPL)     备
192.168.11.83 alimysql03    5.7.20-log MySQL Community Server (GPL)     备


其中192.168.11.81 alimysql01信息如下:
[root@alimysql01 ~]# cat /etc/redhat-release 
CentOS Linux release 7.3.1611 (Core) 
[root@alimysql01 ~]# hostname -i
192.168.11.81
[root@alimysql01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.11.81 alimysql01
192.168.11.82 alimysql02
192.168.11.83 alimysql03
192.168.11.84 alimysql04
192.168.11.85 alimysql05


-----------------------------------------
到官方网站下载https://dev.mysql.com/downloads/file/?id=473559
mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
如果没有mysql用户就创建mysql用户如果没有mysql用户，请用useradd命令添加，并用passwd命令初始化密码）
[root@alimysql03 opt]# id mysql
uid=27(mysql) gid=27(mysql) 组=27(mysql)




修改mysql的配置文件（三台机器都要操作，每台的配置会有点不一样，如server_id不能重复）
[root@alimysql02 ~]# touch  /etc/my3307.cnf
[root@alimysql02 ~]# chown mysql:mysql  /etc/my3307.cnf
[root@alimysql03 opt]# cat  /etc/my3307.cnf
[mysqld]
user =mysql         #  mysql
plugin-dir=/usr/local/mysql3307/lib/plugin
basedir =/usr/local/mysql3307        #  /usr/local/mysql/
datadir =/opt/mysqldata_3307         #  /usr/local/mysql/data
server_id        =83        #  0
port          =3307            #  3307
log-error=/var/log/mysqld_3307.log
pid-file=/var/run/mysqld/mysqld_3307.pid
#group_replication_ssl_mode
symbolic-links=0
socket          =/opt/mysql/mysql3307.sock        #  /tmp/mysql.sock
#socket=/var/lib/mysql/mysql.sock
language        = /usr/local/mysql3307/share/english
auto_increment_increment    =1            #  1
auto_increment_offset      =1            #  1
lower_case_table_names      =1            #  0
secure_file_priv =            #  null
skip-grant-tables
skip-name-resolve 
max_heap_table_size = 64M
####: for binlog
binlog_format        =row            #  row
log_bin          =mysql-bin          #  off
binlog_rows_query_log_events    =on            #  off
log_slave_updates      =on            #  off
expire_logs_days      =15            #  0
binlog_cache_size      =32768            #  32768(32k)
binlog_checksum        =none            #  CRC32
sync_binlog        =1            #  1
skip-name-resolve 
max_connections = 512
max_connect_errors = 1000000
max_allowed_packet = 32M 
interactive_timeout = 1800
wait_timeout = 1800
tmp_table_size = 64M
read_buffer_size = 16M
read_rnd_buffer_size = 32M
####: for error-log
#log_error        =mysql-err.log          #  /usr/local/mysql/data/localhost.localdomain.err


####: for slow query log
 
####: for gtid
gtid_mode        =on            #  off
enforce_gtid_consistency    =on            #  off
 
 
####: for replication
master_info_repository      =table            #  file
relay_log_info_repository    =table            #  file
relay_log_recovery=1 
 
####: for group replication
transaction_write_set_extraction  =XXHASH64          #  off
loose-group_replication_group_name  ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"    #  
loose-group_replication_start_on_boot  =off            #  off
loose-group_replication_local_address  ="192.168.11.83:24903"        #
loose-group_replication_group_seeds  ="192.168.11.81:24901,192.168.11.82:24902,192.168.11.83:24903"
loose-group_replication_bootstrap_group  =off            #  off
loose-group_replication_single_primary_mode=true
loose-group_replication_enforce_update_everywhere_checks=false 
 
####: for innodb
innodb_page_cleaners = 8
default_storage_engine      =innodb            #  innodb
default_tmp_storage_engine    =innodb            #  innodb
innodb_data_file_path =ibdata1:12M:autoextend    #ibdata1:12M:autoextend默认会在datadir目录下创建ibdata1
innodb_temp_data_file_path  =ibtmp1:12M:autoextend   #ibtmp1:12M:autoextend
innodb_log_group_home_dir    =./            #  ./
innodb_log_files_in_group    =2            #  2
innodb_log_file_size      =48M            #  50331648(48M)
innodb_file_format      =Barracuda          #  Barracuda
innodb_file_per_table      =on             #  on
innodb_page_size      =16k            #  16384(16k)
innodb_thread_concurrency    =0            #  0
innodb_read_io_threads      =4            #  4
innodb_write_io_threads      =4            #  4
innodb_purge_threads      =4            #  4
innodb_print_all_deadlocks    =on            #  off
innodb_deadlock_detect      =on            #  on
innodb_lock_wait_timeout    =3600            #  50
innodb_spin_wait_delay      =6            #  6
innodb_autoinc_lock_mode    =2            #  1
innodb_stats_persistent      =on            #  on
innodb_stats_persistent_sample_pages  =20            #  20
innodb_adaptive_hash_index    =on            #  on
innodb_change_buffering      =all            #  all
innodb_change_buffer_max_size    =25            #  25
innodb_flush_neighbors      =1            #  1
innodb_flush_method      =O_DIRECT          #  
innodb_doublewrite      =on            #  on
innodb_log_buffer_size      =16M            #  16777216(16M)
innodb_max_undo_log_size = 4G
innodb_flush_log_at_timeout    =1            #  1
innodb_flush_log_at_trx_commit    =1            #  1
autocommit=1            #  1
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
############
innodb_buffer_pool_size=8096M
innodb_buffer_pool_instances=8
#innodb_additional_mem_pool_size=20M
join_buffer_size=128M
sort_buffer_size=32M
event_scheduler = 1
[client]
user=proxysql
password=proxysql 


创建文件和目录，并更改属组（三台机器都要操作）
[root@alimysql03 opt]# mkdir -p /opt/mysql/plugin_data3307
[root@alimysql03 opt]# chown -R mysql:mysql /opt/mysql/plugin_data3307
[root@alimysql03 opt]# mkdir -p /opt/mysqldata_3307
[root@alimysql03 opt]# chown -R mysql:mysql /opt/mysqldata_3307
[root@alimysql03 opt]# touch /var/log/mysqld3307.log
[root@alimysql03 opt]# chown mysql:mysql /var/log/mysqld3307.log
[root@alimysql03 opt]# touch /opt/mysql/mysql3307.sock
[root@alimysql03 opt]# chown mysql:mysql /opt/mysql/mysql3307.sock
[root@alimysql03 opt]# touch /var/run/mysqld/mysqld3307.pid
[root@alimysql03 opt]# chown mysql:mysql /var/run/mysqld/mysqld3307.pid


解压和安装（三台机器都要操作）
[root@alimysql03 opt]#tar -zxcf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz  -C /usr/local
[root@alimysql03 opt]#cd /usr/local
[root@alimysql03 opt]#mv mysql-5.7.20-linux-glibc2.12-x86_64  /usr/local/mysql3307
[root@alimysql02 local]# chown -R mysql:mysql /usr/local/mysql3307


#初始化数据（三台机器都要操作）
[root@alimysql01 ~]# /usr/local/mysql3307/bin/mysqld --defaults-file=/etc/my3307.cnf  --initialize  --datadir=/opt/mysqldata_3307 --port=3307 --socket=/opt/mysql/mysql3307.sock --user=mysql --pid-file=/var/run/mysqld/mysqld_3307.pid &


报错一：/usr/local/mysql3307/bin/mysqld --defaults-file=/etc/my3307.cnf  --port=3307 --socket=/opt/mysql/mysql3307.sock --user=mysql --pid-file=/var/run/mysqld/mysqld_3307.pid &                  
[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 160913 02:11:21 mysqld_safe mysqld from pid file /tmp/mysql.pid ended
Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist的解决方法
解决办法是添加二个参数：--initialize  --datadir=/opt/mysqldata_3307 initialize参数是初始化数据库吗，不然就没有初始库。


[root@alimysql03 ~]# /usr/local/mysql3307/bin/mysqld_safe --defaults-file=/etc/my3307.cnf --datadir=/opt/mysqldata_3307  --port=3307  --socket=/opt/mysql/mysql3307.sock --user=mysql --pid-file=/var/run/mysqld/mysqld_3307.pid &
 
        故障一：启动数据库报错：
2017-11-01T10:28:14.649847Z 0 [ERROR] Can't find error-message file '/usr/local/mysql_3307/share/errmsg.sys'. 
Check error-message file location and 'lc-messages-dir' configuration directive.
加入language配置项，由于我是使用二进制分发包进行安装的，而默认的情况下，
mysql会到/usr/local/mysql/share/english下查找该文件，
language        = /usr/local/mysql3306/share/english            #此处为language配置项
我的mysql是安装在/usr/local/mysql目录下，bin/mysqld_safe --user=mysql &启动后，从日志文件中发现如下错误:
110623 21:49:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110623 21:49:41 [ERROR] Can't find messagefile '/usr/share/errmsg.sys'
110623 21:49:41 [Warning] Can't create test file /var/lib/mysql/ubuntu.lower-test
110623 21:49:41 [Warning] Can't create test file /var/lib/mysql/ubuntu.lower-test
/usr/local/mysql/bin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2)
110623 21:49:41 [ERROR] Aborting
从日志上发现，mysql的启动目录不对。
修改启动命令：
sudo mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql &
即可启动成功: 
        故障二：启动数据库报错：
2017-11-02T09:12:50.042602Z 0 [ERROR] unknown option '--group_replication_ssl_mode'
2017-11-02T09:12:50.042654Z 0 [ERROR] Aborting
把配置文件中的参数注销group_replication_ssl_mode




检查mysqld进程和mysqld_safe进程是否存在（三台机器都要操作）
[root@alimysql03 ~]# ps -ef | grep mysql
root      6471  5218  0 19:20 pts/5    00:00:00 /bin/sh /usr/local/mysql3307/bin/mysqld_safe --defaults-file=/etc/my3307.cnf --datadir=/opt/mysqldata_3307 --port=3307 --socket=/opt/mysql/mysql3307.sock --user=mysql --pid-file=/var/run/mysqld/mysqld_3307.pid
mysql     7684  6471 14 19:20 pts/5    00:00:01 /usr/local/mysql3307/bin/mysqld --defaults-file=/etc/my3307.cnf --basedir=/usr/local/mysql3307 --datadir=/opt/mysqldata_3307 --plugin-dir=/opt/mysql/plugin_data_3307 --user=mysql --log-error=/var/log/mysqld_3307.log --pid-file=/var/run/mysqld/mysqld_3307.pid --socket=/opt/mysql/mysql3307.sock --port=3307


[root@alimysql03 opt]# /usr/local/mysql3307/bin/mysql_ssl_rsa_setup


每台机器都要安装mysql软件


---------
#下面操纵不写入二进制日志，避免修改传递给其他实例，先关闭（三台机器都要操作）
mysql> set SQL_LOG_BIN=0;
#创建拥有replication slave权限mysql用户。（三台机器都要操作）
mysql> create user 'repl'@'%' identified by 'repl';
mysql> grant replication slave on *.* to 'repl'@'%';
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1;
mysql> change master to master_user='repl',master_password='repl' for channel 'group_replication_recovery'; -- 构建group replication集群，（三台机器都要操作）
查看是否可以动态添加插件（三台机器都要操作）
mysql> show variables like "have_dynamic_%";
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
mysql>  show plugins;
确保binlog_format是row格式。（三台机器都要操作）
mysql> show variables like 'binlog_format'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)




添加复制组的白名单ip（三台机器都要操作）
mysql>SET GLOBAL group_replication_ip_whitelist="192.168.11.1/100,127.0.0.1/8";


#server s1引导组，启动组复制程序（group_replication_bootstrap_group参数的复制组只启动一次就行，只需要在其中作为主机启动，192.168.11.81 alimysql0）
mysql>set global group_replication_bootstrap_group=ON;
mysql>start group_replication;
   报错一： 
   ERROR 3092 (HY000): The server is not configured properly to be an active member of the group.
   Please see more details on error log.
   日志文件信息：
   [ERROR] Plugin group_replication reported: 'Unable to announce tcp port 3307. Port already in use?'
   原因：mysql的端口是3397，但是配置文件中的参数loose-group_replication_local_address  ="192.168.11.83:3307"也
         配置为3307了，端口重复了。
   解决办法：把参数改成loose-group_replication_local_address  ="192.168.11.83:24903" ，更换一个端口就可以了。
   
   报错二：
   ERROR] Plugin group_replication reported: '[GCS] There is no local IP address matching the one configured for the local node (192.168.11.82:24901).
   loose-group_replication_local_address  ="192.168.11.83:24903"        #
   loose-group_replication_group_seeds  ="192.168.11.81:24901,192.168.11.82:24902,192.168.11.83:24903" 参数没有配正确
   解决办法：配置正确参数和端口
  
   报错三：
   ERROR 3096 (HY000): The START GROUP_REPLICATION command failed as there was an error when initializing the 
group communication layer.
日志信息：
2017-11-03T00:50:27.360617Z 4 [ERROR] Plugin group_replication reported: 'MySQL server does not have SSL support and group_replication_ssl_mode is "REQUIRED", START GROUP_REPLICATION will abort'
2017-11-03T00:50:27.360759Z 4 [ERROR] Plugin group_replication reported: 'Error on group communication engine initialization'
2017-11-03T00:50:27.360776Z 4 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2017-11-03T00:50:27.360784Z 4 [ERROR] Plugin group_replication reported: 'Error calling group communication interfaces while trying to leave the group'
   解决办法：因为没有ssl文件也没有开启，所以如果配置了这个group_replication_ssl_mode参数，需要在配置文件去掉该参数。或者正确启动SSL
   
   报错四： ERROR 3092 (HY000): The server is not configured properly to be an active member of the group.
    Please see more details on error log. 
    日志文件信息：
    Error connecting using SSL 2000114 114.
    [ERROR] Plugin group_replication reported: '[GCS] 
    Error on opening a connection to 192.168.11.82:24912 on local port: 24913. Error= 0'
    2017-11-03T08:48:31.354773Z 0 [ERROR] Plugin group_replication reported: '[GCS] 
    Error connecting using SSL 2000114 114.
    Error connecting to all peers. Member join failed. Local port: 24913'
    [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 24913'
    报错原因：连接SSL的时候报错，配置文件加了group_replication_ssl_mode参数
    解决办法：在配置文件去掉group_replication_ssl_mode参数或者正确启动SSL
    


mysql>set global group_replication_bootstrap_group=OFF;




#检测组是否创建并已加入新成员
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 5a037111-bfc6-11e7-8dd0-000c29cd182a | alimysql01  |        3307 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
mysql> create database test;
2017-03-31T23:23:45.535115Z 8 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection'
Query OK, 1 row affected (0.03 sec)
mysql> use test;
Database changed


mysql> create table t1(c1 int primary key,c2 text not null);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values (1 , 'Luis');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)
mysql> show binlog events;






82.83机器操作其他机器（2台）需要加入复制组，给组添加机器
#添加到复制组（不用再设置启动，该组已在是s1时启动）
mysql>set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql>start group_replication;
mysql>set global group_replication_allow_local_disjoint_gtids_join=OFF;
#检测组是否创建并已加入新成员
mysql>  select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 5a037111-bfc6-11e7-8dd0-000c29cd182a | alimysql01  |        3307 | ONLINE       |
| group_replication_applier | 63a60df4-bfca-11e7-aeed-000c29f16297 | alimysql02  |        3307 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
测试同步是否正常
mysql> select * from test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)


最后检查三台机器是否都在一个组里
mysql>  select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 5a037111-bfc6-11e7-8dd0-000c29cd182a | alimysql01  |        3307 | ONLINE       |
| group_replication_applier | 63a60df4-bfca-11e7-aeed-000c29f16297 | alimysql02  |        3307 | ONLINE       |
| group_replication_applier | 73c749ca-bef3-11e7-95d3-000c292d708f | alimysql03  |        3307 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.01 sec)


192.168.11.81上查看，因为只读是关闭的状态，说明可以写和读，是主库
mysql> show variables like '%only%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_optimize_fulltext_only | OFF   |
| innodb_read_only              | OFF   |
| read_only                     | OFF   |
| super_read_only               | OFF   |
| tx_read_only                  | OFF   |
+-------------------------------+-------+
5 rows in set (0.01 sec)






192.168.11.82和83上查看，因为只读是开启的状态，说明只能读，都是备库
mysql> show variables like '%only%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_optimize_fulltext_only | OFF   |
| innodb_read_only              | OFF   |
| read_only                     | ON    |
| super_read_only               | ON    |
| tx_read_only                  | OFF   |
+-------------------------------+-------+
5 rows in set (0.00 sec)


三台机器的复制组搭建好了。






--------------------------------------------------------------------------------