सोमवार, 17 जून 2019

MySQL ग्रुप प्रतिकृति

इसलिए MySQL का समूह प्रतिकृति MySQL 5.7 के साथ सामने आया। अब यह थोड़ा बाहर हो गया है जबकि लोग इसके बारे में अधिक पूछना शुरू कर रहे हैं।
नीचे इसको कैसे सेट किया जाए इसका एक उदाहरण है और कुछ दर्द बिंदु उदाहरण हैं जैसे मैंने इसके साथ चारों ओर पोक किया है।
मैं तीन अलग-अलग सर्वरों का उपयोग कर रहा हूं,

सर्वर 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
इसलिए अब हम और सर्वर जोड़ सकते हैं।
सर्वर 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)


सर्वर 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)


तो यह सब बहुत अच्छा है, लेकिन इसका मतलब यह नहीं है कि वे ऑनलाइन जाते हैं, वे अक्सर वसूली मोड में बैठ सकते हैं।
मैंने देखा है कि यह MySQL दुर्घटनाओं के साथ अभी तक इसे स्थिर रखने की आवश्यकता है।
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>
साइड नोट उन कारकों में से कुछ को संबोधित करने के लिए -
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)
- इसके बाद चेंज मास्टर कमांड से शुरुआत करें
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-more-executed-transactions-than-those-present-in-the-group/


[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


मेरे विचार...
ध्यान रखें कि समूह प्रतिकृति एकल प्राथमिक मोड या बहु-नोड में स्थापित की जा सकती है
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
यदि आप कोई भी प्राथमिक नोड नहीं लिखते हैं तो आपको निश्चित रूप से एक त्रुटि मिलेगी।


समूह-प्रतिकृति-एकल-प्राथमिक-मोड = बंद <- cnf फ़ाइलों में जोड़ा गया।
mysql> SELECT * FROM performance_schema.replication_group_members;
+ --------------------------- + --------------------- ----------------- + ------------- + ------------- + ---- ---------- + ------------- + ---------------- +
| चैनल का नाम               | सदस्य आईडी                             | Cross.tv_HOST | Cross.tv_PORT | Cross.tv_STATE | Cross.tv_ROLE | Cross.tv_VERSION |
+ --------------------------- + --------------------- ----------------- + ------------- + ------------- + ---- ---------- + ------------- + ---------------- +
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa     |         3306 | ठीक हो   | प्राथमिक     | 8.0.15         |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb     |         3306 | ऑनलाइन       | प्राथमिक     | 8.0.15         |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc     |         3306 | ठीक हो   | प्राथमिक     | 8.0.15         |
+ --------------------------- + --------------------- ----------------- + ------------- + ------------- + ---- ---------- + ------------- + ---------------- +

सेट में 3 पंक्तियाँ (0.00 सेकंड)


हालाँकि यह तब है जब आप अपने ट्रैफिक को संभालने के लिए अपने ट्रैफिक को संभालने के लिए कीपलाइज्ड, माईएसक्यूएल राउटर, प्रोक्सीएसक्यूएल आदि का इस्तेमाल करते हैं। हम नीचे से यह देख सकते हैं कि जब मैं प्राथमिक रुका था, तो वह तुरंत ही विफल हो गया।

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)


अब रिकवरी अभी भी एक मुद्दा था, क्योंकि यह केवल वापस शामिल नहीं होगा। फिर से सभी खातों और चरणों की समीक्षा करनी थी, लेकिन मैंने इसे अंततः वापस ले लिया।

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)


मुझे इसके साथ और अधिक परीक्षण करने की आवश्यकता है क्योंकि मैं अभी तक 100% बेचा नहीं गया हूं क्योंकि मुझे इसकी आवश्यकता है क्योंकि मैं गैलेरा प्रतिकृति की ओर झुक रहा हूं।

ब्याज का URLS


  • 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/
  • सोमवार, 3 जून 2019

    Max_connections 214 4.15.0-46-जेनेरिक # 49-उबंटू

    तो आपके my.cnf फ़ाइल में 214 तक के मान से सेट होने वाले अधिकतम_कनेक्ट का मुद्दा उबंटू पर थोड़ी देर के लिए रहा है।

    एक उदाहरण के रूप में, यह 2015 में यहां वापस नोट किया गया था



    मैं हाल ही में फिर से इसमें भाग गया और निम्न चरणों के साथ हल किया गया।


    # 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


    एक बार उन चरणों को पूरा करने के बाद MySQL कनेक्शन my.cnf फ़ाइल में दिए गए पैरामीटर पर स्थिर थे।