Mysql mgr 参数_MYSQL-MGR架构配置

MGR安装:

机器列表:

pc-s4s4 --2

pc-s3s3 --1

pc-s1s1 --1

pc-s2s2 --1

1,为初始化搭建，2，为后续添加

对1 三个数据库先进行初始化

=============================================================================

vi /etc/security/limits.conf

mysql soft nproc 16384

mysql hard nproc 16384

mysql soft nofile 65536

mysql hard nofile 65536

mysql soft stack 10240

groupadd mysql

useradd -d /home/mysql -g mysql mysql

# passwd mysql

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

PATH=$PATH:$HOME/bin

export PATH

export PATH=/app/mysql/bin:/bin:/usr/bin:/usr/local/bin:/usr/sbin:/app/percona-xtrabackup/bin:/app/percona-toolkit/bin

export MYSQL_HOME=/app/mysql

export MYSQL_PS1="\\u@\\h [\\d] \\r:\\m:\\s>>>"

export MYSQL_UNIX_PORT=/tmp/mysql.sock

#export MYSQL_PWD

#export MYSQL_TCP_PORT=3306

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

mkdir -p /data/mysqldata

mkdir -p /data/logs

mkdir -p /data/redolog

mkdir -p /data/undolog

mkdir -p /data/ibdata

mkdir -p /data/binlog

chown -R mysql:mysql /app/mysql

chown -R mysql:mysql /data/mysqldata

chown -R mysql:mysql /data/logs

chown -R mysql:mysql /data/redolog

chown -R mysql:mysql /data/undolog

chown -R mysql:mysql /data/binlog

chown -R mysql:mysql /data/ibdata

tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz

ln -s /app/mysql-8.0.20-linux-glibc2.12-x86_64 /app/mysql

cd /app/mysql

cat>>my.cnf

--------------------------------------my.cnf 参数添加

#对于组复制，数据必须存储在InnoDB事务存储引擎中

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#组复制对于某些参数的要求，以下参数按照环境修改

server_id=1

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

#组复制设置参数

transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'

group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"#设置一个有效值即可，可以用select uuid()生成

group_replication_start_on_boot=off

group_replication_local_address= "s1:33061"

group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"

group_replication_bootstrap_group=off

s1 my.cnf 参考 ---后面两台根据这个调整ip，server-id ,组复制参数先不添加，初始化完成后，再添加重启。

[client]

port=3306

socket=/tmp/mysql.sock

default-character-set=utf8

[mysqld]

#***********************************common parameters******************************

#skip-slave-start

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

character-set-server=utf8

default-time-zone='+8:00'

default-storage-engine = INNODB

port=3306

pid-file=/data/mysqldata/mysql.pid

socket=/tmp/mysql.sock

basedir=/app/mysql

datadir=/data/mysqldata

transaction_isolation = READ-COMMITTED

#MGR

server-id = 1

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum=NONE

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= "s1:23306"

loose-group_replication_group_seeds= "s2:23306,s3:23306,s4:23306"

loose-group_replication_bootstrap_group= off

loose-group_replication_single_primary_mode=true

loose-group_replication_enforce_update_everywhere_checks=false

loose-group_replication_ip_whitelist='s1,s2,s3,s4' ---白名单添加需先停止该节点group_replication

#MGR

skip-name-resolve

skip_external_locking

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

innodb_strict_mode = 1

log_bin_trust_function_creators = 1

max_connections = 3000

max_user_connections=2000

back_log=512

max_connect_errors=99999

max_allowed_packet = 128M

max_heap_table_size = 16M

tmp_table_size = 16M

max_length_for_sort_data = 16k

lower-case-table-names=1

table_open_cache = 2048

table_open_cache_instances = 2

table_definition_cache = 2048

thread_cache_size = 512

wait_timeout=200

interactive_timeout=28800

#***********************************thread parameters******************************

net_buffer_length = 8K

read_buffer_size = 4M

read_rnd_buffer_size = 16M

sort_buffer_size = 2M

join_buffer_size = 4M

binlog_cache_size = 2M

#******************************* Logs related settings ***************************

#error and slow logs

log_timestamps=SYSTEM

log_error=/data/logs/error.log

long_query_time = 2

slow_query_log = 1

slow_query_log_file=/data/logs/slow-query.log

#binlog

log-bin=/data/binlog/mysql-bin

expire_logs_days = 7

sync_binlog=1

server-id=1003

#******************************* Replication related settings **********************

gtid-mode=on # GTID only

enforce-gtid-consistency=true # GTID only

log-slave-updates

explicit_defaults_for_timestamp=true

# ***************************** INNODB Specific options *****************************

#### Data options

innodb_data_home_dir =/data/ibdata

innodb_data_file_path=ibdata1.dbf:2048M;ibdata2.dbf:2048M:autoextend

innodb_file_per_table = 1

#### Buffer Pool options

innodb_buffer_pool_size = 12G

innodb_buffer_pool_instances=8

innodb_max_dirty_pages_pct = 50

innodb_adaptive_flushing = ON

innodb_flush_neighbors = 0

innodb_change_buffering = ALL

innodb_old_blocks_time = 1000

#### Redo options

innodb_log_group_home_dir=/data/redolog

innodb_log_files_in_group = 4

innodb_log_buffer_size=128M

innodb_log_file_size=1024M

innodb_flush_log_at_trx_commit = 1

#### Transaction options

innodb_lock_wait_timeout = 120

innodb_rollback_on_timeout = 1

#### IO options

performance_schema=off

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_io_capacity = 2000

innodb_use_native_aio = 1

#### Undo options

innodb_undo_directory =/data/undolog

innodb_purge_threads = 4

innodb_purge_batch_size = 512

innodb_max_purge_lag = 65536

innodb_undo_log_truncate=on

innodb_max_undo_log_size=2048M

#### temp tablespace

innodb_temp_data_file_path=temp01.dbf:512M;temp02.dbf:512M

#### optimizer

optimizer_switch='index_condition_pushdown=on'

optimizer_switch='mrr=on'

optimizer_switch='batched_key_access=on'

# ***************************** OTHER Specific options *****************************

[mysql]

#no-auto-rehash

auto-rehash

show-warnings

prompt="\\u@\\h : \\d \\r:\\m:\\s>>> "

default-character-set = utf8

[mysqld_safe]

user=root

open-files-limit = 8192

[mysqldump]

quick

max_allowed_packet = 64M

default-character-set = utf8

初始化及启动

./bin/mysqld --defaults-file=my.cnf --initialize-insecure

./bin/mysqld_safe --defaults-file=my.cnf &

节点1：

SET SQL_LOG_BIN=0;

create user rpl@'%' identified WITH sha256_password by 'rpl';

grant replication slave on *.* to rpl@'%';

SET SQL_LOG_BIN=1;

change master to master_user='rpl',master_password='rpl' for channel 'group_replication_recovery';

install plugin group_replication soname 'group_replication.so'; --安装组复制插件

select * from mysql.plugin;

SET GLOBAL group_replication_bootstrap_group=ON; --启动组引导

START GROUP_REPLICATION; --启动组复制

SET GLOBAL group_replication_bootstrap_group=OFF;

select * from performance_schema.replication_group_members;--查看组成员

节点2,3:

SET SQL_LOG_BIN=0;

create user rpl@'%' identified WITH sha256_password by 'rpl';

grant replication slave on *.* to rpl@'%';

SET SQL_LOG_BIN=1;

change master to master_user='rpl',master_password='rpl' for channel 'group_replication_recovery';

install plugin group_replication soname 'group_replication.so'; --安装组复制插件

select * from mysql.plugin;

START GROUP_REPLICATION; --启动组复制

select * from performance_schema.replication_group_members;--查看组成员

运行一段时间后

MGR节点添加:

s4

数据量小,MYSQLDUMP同步数据：

数据量较大:xtrabackup 或其他方式同步数据 -----xtrabackup 同步数据后需要在新加节点点，设置 gtid_purged

修改 原有MGR成员组白名单及组成员信息

添加新加节点信息

group_replication_group_seeds

group_replication_ip_whitelist ----需停止该节点组复制后才能更改。

---

新节点数据导入后启动组复制--

change master to master_user='rpl',master_password='rpl' for channel 'group_replication_recovery';

install plugin group_replication soname 'group_replication.so';

start group_replication;

查看组成员信息:

select * from performance_schema.replication_group_members;