MGR安装配置

最新推荐文章于 2024-06-21 16:48:45 发布
最新推荐文章于 2024-06-21 16:48:45 发布
阅读量154 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/lvxqxin/p/9407080.html
版权

 

同一个集群中，/etc/hosts文件最好直接配置成一样，把集群中所有ip都配置进去

初始化，这边是三个节点

[root@localhost data]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --initialize

启动第一个节点，登陆修改root初始密码

[root@bogon data]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf &
[1] 44900

 

[root@bogon data]# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sock
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> alter user root@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> exit
Bye

 

[root@bogon data]# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sock
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000002 | 386 | | | 8ec33d06-2bf2-11e8-a45d-000c29e0df19:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

# 这个动作不是必须的，这里只是为了让binlog看着更干净
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 150 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

创建复制用户
mysql> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user repl@'%' identified by 'repl4slave';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='repl', master_password='repl4slave' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.06 sec)

# 只在第一个节点操作，表示是集群中的第一个节点
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 (2.05 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 24b4f050-2c17-11e8-a9fc-000c29e0df19 | bogon | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> set global group_replication_bootstrap_group = off;
Query OK, 0 rows affected (0.00 sec)

 

接下来配置第二个节点，同样需要初始化

[root@localhost data]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --initialize
[root@localhost data]# cat error.log | grep password
2018-03-20T08:36:06.902818Z 1 [Note] A temporary password is generated for root@localhost: qslBuo,q81VX
[root@localhost data]# mysqld --defaults-file=/etc/my3306.cnf &
[1] 50387
[root@localhost data]# 
[root@localhost data]# mysql -uroot -p -S /tmp/mysql3306.sock
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> alter user user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| mysql-bin.000003 | 442 | | | 3db33b36-0e51-409f-a61d-c99756e90155:1-11,
bba76cb3-2c19-11e8-9012-000c296cd2f8:1 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user repl@'%' identified by 'repl4slave'; 
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> install plugin group_replication soname 'group_replication.so';

mysql> set sql_log_bin =1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='repl', master_password='repl4slave' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 150 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (4.82 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 24b4f050-2c17-11e8-a9fc-000c29e0df19 | bogon | 3306 | ONLINE |
| group_replication_applier | bba76cb3-2c19-11e8-9012-000c296cd2f8 | localhost.localdomain | 3306 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
2 rows in set (0.00 sec)

 

继续添加第三个节点，步骤与第二个节点完全一样，只需要注意配置文件里loose-group_replication_group_name需要填写自身ip

[root@localhost data]# mysqld --defaults-file=/etc/my3306.cnf --initialize 
[root@localhost data]# 
[root@localhost data]# 
[root@localhost data]# cat error.log | grep password
2018-03-20T08:48:27.641519Z 1 [Note] A temporary password is generated for root@localhost: r#kyjqZrP0Zw
[root@localhost data]# mysqld --defaults-file=/etc/my3306.cnf &
[1] 50384
[root@localhost data]# 
[root@localhost data]# mysql -uroot -p -S /tmp/mysql3306.sock
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> 
mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.07 sec)

mysql> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user repl@'%' identified by 'repl4slave';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> install plugin group_replication soname 'group_replication.so';

mysql> set sql_log_bin = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='repl', master_password='repl4slave' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)


mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.07 sec)

mysql> start group_replication;
Query OK, 0 rows affected (3.07 sec)

mysql>

 

 

配置文件参考

#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql3306.sock

[mysql]
prompt="\\u@\\h:\\p  [\\d]>
#pager="less -i -n -S"
#tee=/home/mysql/query.log
no-auto-rehash

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mgr/data
port = 3306
socket = /tmp/mysql3306.sock
event_scheduler = 0

tmpdir=/data/mgr/tmp
#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
#
explicit_defaults_for_timestamp
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_error_verbosity=3
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1

#binlog
binlog_format = row
log-bin = /data/mgr/logs/mysql-bin
binlog_cache_size = 1M
max_binlog_size = 200M
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10
#group replication
server_id=1323306
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="3db33b36-0e51-409f-a61d-c99756e90155"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.80.132:23306"
loose-group_replication_group_seeds= "192.168.80.132:23306,192.168.80.128:23306,192.168.80.133:23306"
loose-group_replication_bootstrap_group= off

loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on

#relay log
skip_slave_start = 1
max_relay_log_size = 500M
relay_log_purge = 1
relay_log_recovery = 1
#slave-skip-errors=1032,1053,1062

#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

 

转载于:https://www.cnblogs.com/lvxqxin/p/9407080.html

weixin_30389003
关注
mysql高可用集群MGR组复制的介绍、部署及配置说明
唯有热爱 可抵万难
05-10 1103
MGR组复制mysql高可用集群搭建部署及测试
MySQL MGR集群配置文件
11-09
可通过同一命令，使用相同配置文件，不同id，启动不同server。 /usr/local/mysql-8.0.30-linux-glibc2.12-x86_64/bin/mysqld --defaults-group-suffix=.1 --defaults-file=/home/var/my.cnf --log-output=file --...
MySQL之MGR原理+保姆级配置部署
weixin_44508518的博客
09-12 3683
如上图所示，由3个节点组成一个复制组，Consensus层为一致 性协议层，在事务提交过程中，发生组间通讯，由2个节点决议(certify)通过这个事务，事务 才能够最终得以提交并响应。**高灵活性：**提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主 节点进行，多主模式支持多节点写入。**高扩展性：**节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增 量数据，直到与其他节点数据一致；**高一致性：**基于分布式paxos协议实现组复制，保证数据一致性；
MGR配置proxySQL-2.5.4
06-21 462
proxySQL
MySQL进阶：MGR最优化配置推荐，建议收藏
m0_58476125的博客
06-22 1423
MySQL进阶：MGR最优化配置推荐，建议收藏 前言 写本篇文章的主要是希望给想用MGR的朋友提供一个参数配置参考，本次内容整理参考了3306π社区广州站《MGR Best Practice》万里DB CTO娄帅分享和一些个人的理解认识。 MySQL InnoDB Cluster架构 上图引用于官方手册。该架构中包含：MySQL Shell、MySQL router、MySQL Servers。 MySQL Shell：用于搭建和管理MGR集群的一个客户端工具集。该工具基本是整合了原来官方的mysql
万字详解 MySQL MGR 高可用集群搭建
Coder Xu
06-01 2956
MGR（MySQL Group Replication）是MySQL 5.7.17版本诞生的，是MySQL自带的一个插件，可以灵活部署。保证数据一致性又可以自动切换，具备故障检测功能、支持多节点写入。集群是多个MySQL Server节点共同组成的分布式集群，每个Server都有完整的副本，它是基于ROW格式的二进制日志文件和GTID特性。在单主模式下, 组复制具有自动选主功能，每次只有一个 server成员可以作为主节点。单主模式 group 内只有一台节点可写可读，其他节点只可以读。
mysql_install:MySQL 8.0 MGR自动安装配置脚本
05-19
mysql_install MySQL8.0 自动安装脚本 mysql8_install.sh my_test.cnf mysql-8.0.20-linux-glibc2.12-x86_...注：先把3个节点MySQL实例启动后再开始搭建mgr，同时修改脚本里的ip地址和端口和hosts对应的主机名和地址 #
MySQL 8.0 MGR自动安装配置脚本.zip
最新发布
09-03
MySQL 8.0 MGR自动安装配置脚本
Mysql MGR搭建
qq_41270538的博客
02-06 1380
也就是说，虽然系统分配了大量的内存给mysqld，但其实际上并不需要用到那么多内存，内存可能产生了碎片，内存没有及时GC。它是一个通用的malloc 实现，侧重于减少内存碎片和提升高并发场景下内存的分配效率，其目标是能够替代malloc。系统中查看mysqld已用内存量远远超出了innodb_buffer_pool_size配置的数倍之多。另外在问题发生时，查看mysqld中的连接数仅500多，且大部分连接为sleep状态。所以，尝试将mysqld的内存管理器改为使用 jemalloc 来提升内存效能。
mysql mgr安装_MySQL5.7 MGR安装配置
weixin_31130435的博客
01-19 194
MySQL5.7 MGR安装配置一、服务器规划mysql_mgr_01192.168.10.223mysql_mgr_02192.168.10.224mysql_mgr_03192.168.10.225二、配置文件1. mysql_mgr_01[root@mysql_mgr_01 tmp]# cat /etc/my.cnf[mysqld]sql_mode = STRICT_TRANS_TABLES...
MYSQL--架构--MGR--部署--03--配置
zhou920786312的博客
06-01 1126
loose 前缀的意义在于第一次启动时还没加载组复制的plugin,可以让mysql server忽略该参数,继续启动。尝试从供体池连接到供体时，加入该组的服务器尝试的次数是10启动时加载插件，这里配置的是组复制的插件。是否使用克隆插件。Mysql服务ID将master的连接信息写入表：mysql.salve_master_info将中继日志的信息写入表:mysql.slave_realy_log_info从服务器 从 主服务器 收到的更新 记入到 从服务器 自己的二进制日志文件中。以行的格式记录将MySQ
mysql mgr 8.0_7.mysql8.0版本MGR搭建
weixin_42149145的博客
02-07 518
搭建MGR1、配置文件loose-group_replication_ip_whitelist = 192.168.124.0/24loose-group_replication_start_on_boot = OFFloose-group_replication_bootstrap_group = OFFloose-group_replication_group_name ...
MySQL复制介绍及MGR安装与配置
无双-放飞的自我
12-29 944
官网 https://dev.mysql.com/doc/refman/5.7/en/group-replication.html MySQL异步复制 master事务的提交不需要经过slave的确认，slave是否接收到master的binlog，master并不care。slave接收到master binlog后先写relay log，最后异步地去执行relay log中的sql应用到自...
MGR安装部署
雅冰石的专栏
11-27 2652
一 MGR介绍 mysql组复制简称MGR，是MySQLServer5.7.17及更高版本提供的一个内置MySQL插件（Replication）。mysql组复制提供了一个高可用、高弹性、高可靠性的mysql服务。当主节点故障后，集群自动选举新的主节点，只要不是全部或大多数，系统仍然可用。有一致性，以及不依赖外部组件实现的自动切换、可多点并发写入等特点。 它们由一个组成员服务跟踪，该服务依赖于...
MGR的搭建部署
weixin_33835690的博客
05-29 365
1. MGR介绍 MySQL Group Replication（下简称：MGR）是MySQL官方推出的一种基于Paxos协议的状态机复制。在MGR出现之前，用户常见的MySQL高可用方式，无论怎么变化架构，本质就是Master-Slave架构。MySQL 5.7版本开始支持无损半同步复制（lossless semi-sync replication），从而进一步提示数据复制的强一致性 1.1 ...
Mysql组复制配置MGR（单主）
飘雪
08-07 4076
MySQL组复制配置（单主） – MySQL组复制 mysql5.7.17，组内N/2+1个节点收到relay log，master才commit – 2018年7月29日，18:00:42 – 1 部署 – 1.1 安装（此处用的是二进制包安装） – 单机部署3个实例，版本：MySQL 5.7.17 – 1.2 创建数据目录及日志目录： mkdir -p D:\mysql\mgr\...
Ceph Mimic/Nautilus - 启用Dashboard功能
YoFog的博客
12-28 7010
Ceph的监控可视化界面方案很多----grafana、Kraken。但是从Luminous开始，Ceph提供了原生的Dashboard功能，通过Dashboard可以获取Ceph集群的各种基本状态信息。 1.查看ceph状态 [root@controller ~]# ceph -s cluster: id: 25dae5c3-3fa7-44a4-9f35-7f0b2...
mgr-安装部署（多主）
weixin_30666753的博客
08-21 1700
操作系统：centos7 mysql版本：mysql5.7.27 主机名　　 IP地址 角色 mgr1　　 10.10.20.92 primary mgr2 10.10.20.104 second mgr3 10.10.20.88 second 1、准备工作 关闭防火墙，修改SELinux为disabled，添...
手动部署 ceph mgr (luminous 版)
Terry Tsang
11-21 8507
目标 使用 ceph luminous 版新特性 手动部署 mgr 完成 ceph 监控 新建用户 创建用户 openstack 用于 MGR 监控 [root@cephsvr-128040 ceph]# ceph auth get-or-create mgr.openstack mon 'allow *' osd 'allow *' mds...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值