mysql 集群 MGR

mysql安装（3台服务）

1下载
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
2解压mysql
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
3 重命名文件夹
mv mysql-8.0.11-linux-glibc2.12-x86_64 mysql-8.0.11
4创建data文件夹存储文件
[root@localhost mysql]# cd mysql-8.0.11
[root@localhost mysql-8.0.11]# mkdir data
5创建用户组以及用户和密码

[root@localhost mysql-8.0.11]# groupadd mysql
[root@localhost mysql-8.0.11]# useradd -g mysql mysql

6授权用户

[root@localhost mysql-8.0.11]# chown -R mysql.mysql /home/mysql/mysql-8.0.11

7切换到bin目录下

[root@localhost mysql-8.0.11]# cd bin

8 初始化基础信息

./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --initialize

得到临时密码记的保存
A temporary password is generated for root@localhost: xZnuY,)Rv1oK
xZnuY,)Rv1oK
,
9 编辑my.cnf文件
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
character-set-server=utf8
lower-case-table-names=0
default_authentication_plugin=mysql_native_password
innodb_deadlock_detect = off
innodb_lock_wait_timeout = 120
innodb_rollback_on_timeout = on
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M
innodb_buffer_pool_size = 1073741824
innodb_log_buffer_size = 134217728

max_connections=500

max_user_connections=500

wait_timeout=200

[client]
port = 3306

10 添加mysqld服务到系统
cp -a ./support-files/mysql.server /etc/init.d/mysql

11授权以及添加服务

[root@localhost mysql]# chmod +x /etc/init.d/mysql
[root@localhost mysql]# chkconfig --add mysql

12.启动mysql

[root@localhost mysql]# service mysql start
Starting MySQL..... SUCCESS!
常见错误
my_print_defaults: [ERROR] Found option without preceding group in config file /etc/my.cnf at line 1!
my_print_defaults: [ERROR] Fatal error in defaults handling. Program aborted!
Starting MySQL.my_print_defaults: [ERROR] Found option without preceding group in config file /etc/my.cnf at line 1!
my_print_defaults: [ERROR] Fatal error in defaults handling. Program aborted!
my_print_defaults: [ERROR] Found option without preceding group in config file /etc/my.cnf at line 1!
my_print_defaults: [ERROR] Fatal error in defaults handling. Program aborted!
Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
.... ERROR! The server quit without updating PID file (/usr/local/mysql/data/localhost.localdomain.pid).
编辑my.cnf文件参考编辑my.cnf文件

13.查看启动状态

[root@localhost mysql]# service mysql status
SUCCESS! MySQL running (62693)

14.将mysql命令添加到服务

[root@localhost mysql]# ln -s /usr/local/mysql/bin（安装包路径） /usr/local/bin
ln -s /usr/local/mysql/bin/mysql /usr/local/bin
15.登录mysql mysql -uroot -p 密码使用之前随机生成的密码

[root@localhost mysql-8.0.11]# mysql -uroot -p
报错：-bash: mysql: command not found
注意：ln -s /usr/local/mysql/bin/mysql（安装包路径bin/mysql） /usr/local/bin

16.修改root密码其中123456是新的密码自己设置

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

17.执行使密码生效

mysql> flush privileges;

18.选择mysql数据库

mysql> use mysql;

19.修改远程连接并生效

mysql> update user set host='%' where user='root';

mysql> flush privileges;

基于MGR方式搭建MySQL集群

1 修改mysql配置文件信息（三个节点均需配置）

vi /etc/my.cnf

新添信息内容如下(以第一个节点为例)：

gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

#每台不能一样

server_id=1

plugin_load="group_replication=group_replication.so"

#此参数是在server收集写集合的同时以便将其记录到二进制日志。写集合基于每行的主键，
#并且是行更改后的唯一标识此标识将用于检测冲突。
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#启动mysql时不自动启动组复制
loose-group_replication_start_on_boot=off
loose-group_replication_ip_whitelist = '192.30.71.2/24'
#本台mysq IP
loose-group_replication_local_address="192.30.71.130:3316"
#三台mysql IP
loose-group_replication_group_seeds="192.30.71.128:3316,192.30.71.129:3316,

192.30.71.130:3316"
#是否自动引导组。此选项只能在一个server实例上使用，通常是首次引导组时(或在整组成员关闭的情况下)，
#如果多次引导，可能出现脑裂。
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode = on
loose-group_replication_auto_increment_increment=1
loose-group_replication_member_weight=60
loose-group_replication_transaction_size_limit=0

2 创建复制账号（三个节点均需配置）
登录mysql，各节点执行如下命令

复制代码
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER mgruser@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO mgruser@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='mgruser', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

3 安装MGR插件（三个节点均需配置）
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
-- 查看group replication组件
mysql> show plugins;

4 主节点操作
在主节点（当前主节点为：192.30.71.128:3316）启动MGR，执行如下命令

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

5 从节点操作（另两台）

mysql> START GROUP_REPLICATION;

6 查看MGR组信息

SELECT * FROM performance_schema.replication_group_members;