Présentation de l'environnement

Installation de MySQL sur chaque nœud

Désinstallation des bases existantes

rpm -qa | grep mysql 
rpm -qa | grep mariadb
rpm -e --nodeps nom_paquet

Configuration du réseau

172.20.10.3 s1
172.20.10.4 s2
172.20.10.5 s3

Gestion du pare-feu

systemctl stop firewalld
systemctl disable firewalld

Création utilisateur système

groupadd mysql
useradd -r -g mysql -s /sbin/false mysql

Installation du serveur

tar -xvf mysql-8.0.34-linux-glibc2.17-x86_64.tar.gz
mv mysql-8.0.34-linux-glibc2.17-x86_64 mysql
mkdir -p mysql/{data,logs}
chown -R mysql:mysql /soft/mysql
chmod -R 755 /soft/mysql

Fichier de configuration principle

[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql
port=3306
basedir=/soft/mysql
datadir=/soft/mysql/data
socket=/tmp/mysql.sock
log_timestamps=system
log_error=/soft/mysql/logs/error.log
disabled_storage_engines=MyISAM,BLACKHOLE,FEDERATED,MEMORY
sql_require_primary_key=ON
report_host=172.20.10.3
report_port=3306
server_id =330603
log_bin =/soft/mysql/logs/mysql-bin
binlog_format =ROW
gtid_mode =ON
enforce_gtid_consistency =ON
log_slave_updates =ON
master_info_repository =TABLE
relay_log_info_repository =TABLE
binlog_transaction_dependency_tracking =WRITESET
super_read_only =ON

transaction_write_set_extraction =XXHASH64
replica_parallel_type =LOGICAL_CLOCK
replica_parallel_workers =4
replica_preserve_commit_order =ON

plugin_load_add =group_replication.so
loose_group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose_group_replication_start_on_boot =OFF
loose_group_replication_local_address =172.20.10.3:33061
loose_group_replication_group_seeds =172.20.10.3:33061,172.20.10.4:33061,172.20.10.5:33061
loose_group_replication_bootstrap_group =OFF
loose_group_replication_recovery_get_public_key =ON

Initialisation et démarrage

/soft/mysql/bin/mysqld --initialize --user=mysql --basedir=/soft/mysql --datadir=/soft/mysql/data
ln -vs /soft/mysql/support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start

Variables d'environnement

PATH=$PATH:$HOME/bin:/soft/mysql/bin
export PATH
source ~/.bash_profile

Première connexion

mysql -uroot -p
alter user 'root'@'localhost' identified by 'root@123';
flush privileges;

Configuration du groupe de réplication

Vérification des plugins

show plugins;

Initialisation du groupe

set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;
select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;

Utilisateur de réplicatino

create user 'repl'@'%' identified with 'mysql_native_password' by 'repl@123';
grant replication slave,backup_admin on *.* to 'repl'@'%';
flush privileges;

Canal de récupération

change master to master_user='repl',master_password='repl@123' for channel 'group_replication_recovery';

Ajout des membres au groupe

change master to master_user='repl',master_password='repl@123' for channel 'group_replication_recovery';
start group_replication;
select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;

Changement de mode de réplication

Mono-primary vers multi-primary

select group_replication_switch_to_multi_primary_mode();

Multi-primary vers mono-primary

select group_replication_switch_to_single_primary_mode('141e2b7e-6e25-11ee-9483-0800276dee47');

Suivi de progression

select event_name,work_completed,work_estimated from performance_schema.events_stages_current where event_name like '%stage/group_rpl%' \G;

Surveillance du groupe

Statut des membres

select * from performance_schema.replication_group_members;

Statistiques détaillées

select * from performance_schema.replication_group_member_stats\G;

Délai des secondaires

select case when min_commit_timstamp is null then 0
else unix_timestamp(now(6))-unix_timestamp(min_commit_timstamp)
end as seconds_behind_master
from (
select min(applying_transaction_original_commit_timestamp) as min_commit_timstamp
from performance_schema.replication_applier_status_by_worker
where applying_transaction<>'') t;

Utilisation mémoire

select name,enabled from performance_schema.setup_instruments where name like 'memory/group_rpl%';