GreatSQL 部署 MGR 集群，添加新节点及MGR搭建过程中遇到的一些故障（超详细）

最新推荐文章于 2024-07-24 09:46:40 发布

seaskyccl

最新推荐文章于 2024-07-24 09:46:40 发布

阅读量1.1k

点赞数 19

分类专栏：数据库文章标签： adb android

本文链接：https://blog.csdn.net/seaskyccl/article/details/136183338

版权

本文详述了GreatSQL部署MGR集群的步骤，包括安装配置、节点添加与切换，并列举了在部署过程中遇到的常见问题及其解决方案，如故障1至故障9，涉及组复制的优化和故障排查技巧。

摘要由CSDN通过智能技术生成

GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。

GreatSQL在高可靠方面的主要提升是针对MGR做了大量的改进和提升工作，进一步提升MGR的高可靠等级。包括但不限于以下提升：

地理标签，提升多机房架构数据可靠性。
读写节点动态VIP，高可用切换更便捷。
仲裁节点，用更低的服务器成本实现更高可用。
快速单主模式，在单主模式下更快，性能更高。
智能选主，高可用切换选主机制更合理。
全新流控算法，使得事务更平稳，避免剧烈抖动。
优化了节点加入、退出时可能导致性能剧烈抖动的问题。
解决磁盘空间爆满时导致MGR集群阻塞的问题。
解决了长事务造成无法选主的问题。
优化事务认证队列清理算法，规避每60s抖动问题。
修复了recover过程中长时间等待的问题。

一、MGR组复制测试

测试环境，单机-多实例（centos 7.6)

| GreatSQL-01 | 3306 | /data/GreatSQL/mgr01/
| GreatSQL-02 | 3307 | /data/GreatSQL/mgr02/
| GreatSQL-03 | 3308 | /data/GreatSQL/mgr03/

1、安装

yum install libaio
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
tar xvJf GreatSQL-8.0.25-17-Linux-glibc2.17-x86_64.tar.xz -C /usr/local/
cd /usr/local
ln -s GreatSQL-8.0.25-17-Linux-glibc2.17-x86_64 mysql

#添加环境变量
#cat >>/etc/profile<< EOF
export PATH=/usr/local/mysql/bin:$PATH
EOF
#source /etc/profile

1.1、配置文件

#vim /etc/my.cnf
[mysql]
prompt = "\u@mysqldb \R:\m:\s [\d]> "
no_auto_rehash
loose-skip-binary-as-hex

[mysqld]
basedir=/usr/local/mysql
log_timestamps=SYSTEM
user = mysql
log_error_verbosity = 3

log-bin=binlog
binlog-format=row
log_slave_updates=ON
binlog_checksum=CRC32

master-info-repository=TABLE
relay-log-info-repository=TABLE
gtid-mode=on
enforce-gtid-consistency=true
binlog_transaction_dependency_tracking=writeset
transaction_write_set_extraction=XXHASH64
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers=4 #可以设置为逻辑CPU数量的2-4倍
sql_require_primary_key=1
slave_preserve_commit_order=1
slave_checkpoint_period=2
#mgr
loose-plugin_load_add='mysql_clone.so'
loose-plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
loose-group_replication_group_seeds= "127.0.0.1:33061,127.0.0.1:33071,127.0.0.1:33081"
loose-group_replication_start_on_boot=off
loose-group_replication_bootstrap_group=off
loose-group_replication_exit_state_action=READ_ONLY
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_single_primary_mode=ON

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld
log = /data/GreatSQL/mysqld_multi.log
mysqladmin = /usr/local/mysql/bin/mysqladmin
user=root

[mysqld3306]
datadir=/data/GreatSQL/mgr01
socket=/data/GreatSQL/mgr01/mysql.sock
port=3306
server_id=3306
log-error=/data/GreatSQL/mgr01/error.log
loose-group_replication_local_address= "127.0.0.1:33061"

[mysqld3307]
datadir=/data/GreatSQL/mgr02
socket=/data/GreatSQL/mgr02/mysql.sock
port=3307
server_id=3307
log-error=/data/GreatSQL/mgr02/error.log
loose-group_replication_local_address= "127.0.0.1:33071"

[mysqld3308]
datadir=/data/GreatSQL/mgr03
socket=/data/GreatSQL/mgr03/mysql.sock
port=3308
server_id=3308
log-error=/data/GreatSQL/mgr03/error.log
loose-group_replication_local_address= "127.0.0.1:33081"

1.2、创建目录，权限

mkdir -p /data/GreatSQL/{mgr01,mgr02,mgr03}
chown -R mysql.mysql /data/GreatSQL

#初始化
/usr/local/mysql/bin/mysqld --no-defaults --datadir=/data/GreatSQL/mgr01 --initialize-insecure --user=mysql
/usr/local/mysql/bin/mysqld --no-defaults --datadir=/data/GreatSQL/mgr02 --initialize-insecure --user=mysql
/usr/local/mysql/bin/mysqld --no-defaults --datadir=/data/GreatSQL/mgr03 --initialize-insecure --user=mysql
# 启动3个节点
/usr/local/mysql/bin/mysqld_multi start 3306
/usr/local/mysql/bin/mysqld_multi start 3307
/usr/local/mysql/bin/mysqld_multi start 3308
示例：
[root@localhost /root]#/usr/local/mysql/bin/mysqld --no-defaults --datadir=/data/GreatSQL/mgr01 --initialize-insecure --user=mysql
2023-05-10T07:27:57.398912Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.25-17) initializing of server in progress as process 16452
2023-05-10T07:27:57.407728Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-05-10T07:27:59.437333Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-05-10T07:28:02.095226Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

2、创建用户等

# 这里初始化没有使用密码，直接回车登录
[root@localhost /root]#/usr/local/mysql/bin/mysql -uroot -p -P3306 -S /data/GreatSQL/mgr01/mysql.sock
root@mysqldb 15:38:  [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc.123.yhq';
root@mysqldb 15:40:  [(none)]> flush privileges;
#另外2个节点一样
[root@localhost /root]#/usr/local/mysql/bin/mysql -uroot -p -P3307 -S /data/GreatSQL/mgr02/mysql.sock
root@mysqldb 15:38:  [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc.123.yhq';
root@mysqldb 15:40:  [(none)]> flush privileges;
[root@localhost /root]#/usr/local/mysql/bin/mysql -uroot -p -P3308 -S /data/GreatSQL/mgr03/mysql.sock
root@mysqldb 15:38:  [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc.123.yhq';
root@mysqldb 15:40:  [(none)]> flush privileges;

root@mysqldb 15:40:  [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

root@mysqldb 15:40:  [(none)]> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 |      642 |              |                  | 32218282-ef04-11ed-b942-005056a63376:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

root@mysqldb 15:40:  [(none)]> show plugins;
| clone                           | ACTIVE   | CLONE              | mysql_clone.so       | GPL     |
| group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+---------------------------------+----------+--------------------+----------------------+---------+

root@mysqldb 15:41:  [(none)]> reset master; reset slave all;
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

root@mysqldb 15:44:  [(none)]> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 15:45:  [(none)]> CREATE USER repl@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'repl.abc';
Query OK, 0 rows affected (0.02 sec)

root@mysqldb 15:45:  [(none)]> GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO `repl`@`%`;
Query OK, 0 rows affected (0.01 sec)

节点2，3

root@mysqldb 15:43:  [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc.123.yhq';
Query OK, 0 rows affected (0.02 sec)

root@mysqldb 15:44:  [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 15:44:  [(none)]> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 |      643 |              |                  | 627f6c88-ef04-11ed-818c-005056a63376:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+