segunda-feira, 17 de junho de 2019

Replicação de Grupo do MySQL

Então a replicação de grupo do MySQL foi lançada com o MySQL 5.7. Agora isso já está um pouco fora, enquanto as pessoas estão começando a perguntar mais sobre isso.
Abaixo está um exemplo de como configurar isso e alguns exemplos de pontos de dor como eu cutuquei com ele.
Eu estou usando três servidores diferentes

Servidor CENTOSA

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)

vi my.cnf
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
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.111.17:33061"
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"
group_replication_bootstrap_group=off

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER repl@'%' IDENTIFIED BY 'replpassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;


CHANGE MASTER TO
MASTER_USER='repl',
MASTER_PASSWORD='replpassword'
FOR CHANNEL 'group_replication_recovery';


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 (3.11 sec)


mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT * FROM performance_schema.replication_group_members \G

*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1
MEMBER_HOST: centosa
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.15
Então agora podemos adicionar mais servidores.
Servidor CENTOSB

vi my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
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
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.111.89:33061"
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"
group_replication_bootstrap_group=off

mysql> CHANGE MASTER TO
MASTER_USER='repl',
MASTER_PASSWORD='replpassword'
FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;
Query OK, 0 rows affected (0.02 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.03 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | RECOVERING | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)


Servidor CENTOSC

vi my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
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
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.111.124:33061"
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"
group_replication_bootstrap_group=off

mysql> CHANGE MASTER TO
-> MASTER_USER='repl',
-> MASTER_PASSWORD='replpassword'
-> FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;
Query OK, 0 rows affected (0.02 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.58 sec)
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1
MEMBER_HOST: centosa
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.15

*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 572ca2fa-5eff-11e9-8df9-08002712f4b1
MEMBER_HOST: centosb
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.15

*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: c5f3d1d2-8dd8-11e9-858d-08002773d1b6
MEMBER_HOST: centosc
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.15
3 rows in set (0.00 sec)


Então tudo isso é ótimo, mas nem sempre significa que eles ficam on-line, eles geralmente podem se sentar no modo de recuperação.
Eu vi essa falha com falhas do MySQL até agora, então preciso garantir isso estável.
mysql> create database testcentosb;<br> ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement<br>
Nota lateral para abordar alguns desses fatores -
mysql> START GROUP_REPLICATION;
ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start.

mysql> reset slave all;
Query OK, 0 rows affected (0.03 sec)
- Então, comece novamente do comando Change master
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.111.17:33061 on local port: 33061.'
[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: c5f3d1d2-8dd8-11e9-858d-08002773d1b6:1-4 >
[ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

https://ronniethedba.wordpress.com/2017/04/22/this-member-has-mais-transações-executadas-do-que-presente-no-grupo/


[ERROR] [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error during the recovery process of Group Replication. The server will leave the group.'
[ERROR] [MY-013173] [Repl] Plugin group_replication reported: 'The plugin encountered a critical error and will abort: Fatal error during execution of Group Replication'

SELECT * FROM performance_schema.replication_connection_status\G


Meus pensamentos...
Tenha em mente que a replicação de grupo pode ser configurada no modo primário único ou em vários nós
mysql> select @@group_replication_single_primary_mode\G
*************************** 1. row ***************************
@@group_replication_single_primary_mode: 1

mysql> create database testcentosb;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
você obviamente obterá um erro se escrever em nenhum nó primário.


group-replication-single-primary-mode = off <- adicionado aos arquivos cnf.
mysql> SELECT * FROM performance_schema.replication_group_members;
+ --------------------------- + --------------------- ----------------- + ------------- + ------------- + ---- ---------- + ------------- + ---------------- +
| NOME DO CANAL               | MEMBER_ID                             | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+ --------------------------- + --------------------- ----------------- + ------------- + ------------- + ---- ---------- + ------------- + ---------------- +
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa     |         3306 | RECUPERANDO   | PRIMÁRIO     | 8,0.15         |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb     |         3306 | CONECTADOS       | PRIMÁRIO     | 8,0.15         |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc     |         3306 | RECUPERANDO   | PRIMÁRIO     | 8,0.15         |
+ --------------------------- + --------------------- ----------------- + ------------- + ------------- + ---- ---------- + ------------- + ---------------- +

3 linhas no set (0,00 seg)


Agora é, no entanto, se você usa Keepalived, MySQL router, ProxySQL etc para lidar com o tráfego para rolar automaticamente em caso de um failover. Podemos ver de baixo que falhou imediatamente quando parei o primário.

mysql> SELECT * FROM performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | SECONDARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

[root@centosa]# systemctl stop mysqld

mysql> SELECT * FROM performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

[root@centosa]# systemctl start mysqld
[root@centosa]# mysql
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.34 sec)

mysql> SELECT * FROM performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | RECOVERING | SECONDARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)


Agora a recuperação ainda era um problema, já que não seria simplesmente voltar atrás. Tive que rever todas as contas e etapas novamente, mas eu consegui de volta eventualmente.

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | SECONDARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)


Eu preciso testar mais com isso, como eu não estou 100% vendido ainda como a necessidade disso como eu me inclino para replicação Galera ainda.

URLs de interesse


  • https://dev.mysql.com/doc/refman/8.0/en/group-replication.html
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html
  • http://datacharmer.blogspot.com/2017/01/mysql-group-replication-vs-multi-source.html
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-launching.html
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-configuring-instances.html
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html
  • https://ronniethedba.wordpress.com/2017/04/22/how-to-setup-mysql-group-replication/
  • https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_group_seeds
  • https://bugs.mysql.com/bug.php?id=90534
  • https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/
  • https://lefred.be/content/mysql-group-replication-is-sweet-but-can-be-sour-if-you-misunderstand-it/
  • https://www.youtube.com/watch?v=IfZK-Up03Mw
  • https://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/

  • segunda-feira, 3 de junho de 2019

    Max_connections 214 4.15.0-46-genérico # 49-Ubuntu



    Assim, o problema de max_connections cair do valor definido em seu arquivo my.cnf para 214 existe há algum tempo no Ubuntu.

    Como exemplo, foi observado aqui em 2015



    Eu corri para isso novamente recentemente e foi resolvido com os seguintes passos.


    # cp /lib/systemd/system/mysql.service /etc/systemd/system/
    # cd /etc/systemd/system/
    # vi mysql.service

    LimitNOFILE=infinity
    LimitMEMLOCK=infinity

    # systemctl daemon-reload
    # systemctl restart mysql


    Uma vez que essas etapas foram concluídas, as conexões do MySQL ficaram estáveis ​​no parâmetro fornecido no arquivo my.cnf.