一、系统规划
二、目录创建
创建系统目录并更改目录权限。
[root@mgr1 datas]# mkdir -p /mysql/datas
[root@mgr1 datas]# chown -R mysql.mysql /mysql/datas/
三、配置hosts文件
[root@mgr1 bin]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.3.51 mgr1
192.168.3.52 mgr2
192.168.3.53 mgr3
四、系统用户的创建
[root@mgr3 bin]# useradd mysql
[root@mgr3 bin]# echo "mysql" | passwd --stdin mysql
Changing password for user mysql.
passwd: all authentication tokens updated successfully.
五、参数文件的配置
[client]
port=3306
socket=/mysql/datas/mysql.sock
default-character-set=utf8
[mysqld]
port=3306
basedir=/mysql/mysql-8.0.28-el7-x86_64/
datadir=/mysql/datas/
log-error=/mysql/datas/error.log
socket=/mysql/datas/mysql.sock
pid_file=/mysql/datas/mysql.pid
character-set-server=utf8
autocommit=1
log_bin=binlog
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="0c6d3e5f-90e2-11e6-802e-842b2b5909d6"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.3.51:33061"
loose-group_replication_group_seeds= "192.168.3.51:33061,192.168.3.52:33061,192.168.3.53:33061"
loose-group_replication_bootstrap_group=OFF
report_host=192.168.3.51
report_port=3306
注意:不同的节点间 server_id、loose-group_replication_local_address、report_host 三个参数不同。
六、节点初始化
将安装包解压到规划的目录下,首先将一节点进行初始化,并通过错误日志检查初始化过程是否有报错。
[root@mgr1 opt]# mv mysql-8.0.28-el7-x86_64 /mysql/
[root@mgr1 opt]# ll
total 2415360
-rw-r--r--. 1 root root 1236664320 Feb 24 15:33 mysql-8.0.28-el7-x86_64.tar
-rw-r--r--. 1 7161 31415 833555286 Dec 18 01:39 mysql-8.0.28-el7-x86_64.tar.gz
-rw-r--r--. 1 7161 31415 102230992 Dec 18 01:36 mysql-router-8.0.28-el7-x86_64.tar.gz
-rw-r--r--. 1 7161 31415 300874814 Dec 18 01:38 mysql-test-8.0.28-el7-x86_64.tar.gz
[root@mgr1 datas]# /mysql/mysql-8.0.28-el7-x86_64/bin/mysqld --initialize-insecure --basedir=/mysql/mysql-8.0.28-el7-x86_64/ --datadir=/mysql/datas --user=mysql
[root@mgr1 datas]# more error.log
2022-03-18T07:57:43.481814Z 0 [Warning] [MY-011068] [Server] The syntax 'log_slave_updates' is deprecated and will be removed in a future release. Please use log_replica_updates instead.
2022-03-18T07:57:43.481821Z 0 [Warning] [MY-011069] [Server] The syntax '--master-info-repository' is deprecated and will be removed in a future release.
2022-03-18T07:57:43.481824Z 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-repository' is deprecated and will be removed in a future release.
2022-03-18T07:57:43.481827Z 0 [Warning] [MY-011069] [Server] The syntax '--transaction-write-set-extraction' is deprecated and will be removed in a future release.
2022-03-18T07:57:43.481887Z 0 [System] [MY-013169] [Server] /mysql/mysql-8.0.28-el7-x86_64/bin/mysqld (mysqld 8.0.28) initializing of server in progress as process 7566
2022-03-18T07:57:43.482444Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in or
der to be unambiguous.
2022-03-18T07:57:43.529789Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-03-18T07:57:44.640888Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-03-18T07:57:46.076475Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_group_name=0c6d3e5f-90e2-11e6-802e-842b2b5909d6'.
2022-03-18T07:57:46.076486Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_start_on_boot=OFF'.
2022-03-18T07:57:46.076492Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_local_address=192.168.3.51:33061'.
2022-03-18T07:57:46.076496Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_group_seeds=192.168.3.51:33061,192.168.3.52:33061,192.168.3.53:33061'.
2022-03-18T07:57:46.076500Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_bootstrap_group=OFF'.
2022-03-18T07:57:46.077231Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@mgr1 datas]#
通过错误日志可发现character-set-server 配置的’utf8’目前是字符集UTF8MB3的别名,但在未来的版本中将是UTF8MB4的别名。 mysql建议使用UTF8MB4或 更明确一点。
七、启动mysql
配置mysql的服务并启动一节点的mysql,连接mysql修改root密码。
[root@mgr1 support-files]# cp /mysql/mysql-8.0.28-el7-x86_64/support-files/mysql.server /etc/init.d/
[root@mgr1 support-files]# chmod +x /etc/init.d/mysql.server
[root@mgr1 support-files]# service mysql.server start
Starting MySQL.. SUCCESS!
[root@mgr1 support-files]# su - root
Last login: Fri Mar 18 14:34:27 CST 2022 from gateway on pts/0
[root@mgr1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql> alter user root@'localhost' identified by 'root';
Query OK, 0 rows affected (0.02 sec)
八、复制专用账号的创建
组复制和mysql的传统复制一样,需要一个专门用来复制的账号 ,创建专用账号并授权。
mysql> CREATE USER repl@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.14 sec)
mysql> GRANT BACKUP_ADMIN ON *.* TO repl@'%';
Query OK, 0 rows affected (0.22 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
九、复制插件的安装
Mysql8的组复制和半同步一样是通过插件来实现的,所以需要安装专门的组复制插件。
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.09 sec)
mysql> SHOW PLUGINS;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql>
十、启动组复制
一节点已经完成初始化和插件安装,启动一节点的组复制并创建test数据库和students表进行测试是否可以同步到其他节点。
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.10 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> create table students (id int,name varchar(30),english int,PRIMARY KEY (id));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students |
+----------------+
1 row in set (0.00 sec)
mysql> insert into students values (1,'yao',99);
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
+----+------+---------+
| id | name | english |
+----+------+---------+
| 1 | yao | 99 |
+----+------+---------+
1 row in set (0.00 sec)
mysql>
十一、2和3节点配置
参照一节点,依次将二、三节点分别进行初始化、配置启动服务、安装插件。然后指定恢复渠道并开启组复制,三节点操作记录如下:
[root@mgr3 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.09 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.87 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 176a3ead-a691-11ec-8e5e-080027ecfeec | 192.168.3.51 | 3306 | ONLINE | PRIMARY | 8.0.28 | XCom |
| group_replication_applier | ea9ffb53-a69b-11ec-bf0e-0800272de01c | 192.168.3.53 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom |
| group_replication_applier | ed66ef33-a699-11ec-9d49-080027e2d08e | 192.168.3.52 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
mysql> SHOW VARIABLES WHERE Variable_name = 'hostname';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname | mgr3 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select * from students;
+----+------+---------+
| id | name | english |
+----+------+---------+
| 1 | yao | 99 |
+----+------+---------+
1 row in set (0.00 sec)
###三节点支撑插入语句报错
mysql> insert into students values (2,'kaka',89);
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>
mysql> show variables like 'super_read_only';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| super_read_only | ON |
+-----------------+-------+
1 row in set (0.00 sec)
mysql>
十二、报错记录
未安装组复制插件报错
2022-03-18T09:05:38.852391Z 8 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2022-03-18T09:06:00.526884Z 8 [ERROR] [MY-010381] [Repl] Group Replication plugin is not installed.
最后修改时间:2022-05-17 22:03:46
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。