Pratique de réplication maître-esclave MySQL - Partage de code de réplication basé sur GTID

Cet article présente principalement MySQL la pratique de réplication maître-esclave - La réplication basée sur GTID est une nouvelle méthode de réplication après MySQL 5.6.

Réplication basée sur GTID

Introduction

La réplication basée sur GTID est une nouvelle méthode de réplication après MySQL 5.6.

GTID (global transaction identifier) ​​​​est l'ID de transaction global, qui garantit que chaque transaction soumise sur la base de données principale a un ID unique dans le cluster.

Dans la réplication basée sur les journaux d'origine, la bibliothèque esclave doit informer la bibliothèque maître à partir de quel décalage effectuer la synchronisation incrémentielle. Si l'erreur spécifiée est spécifiée, les données seront omises, ce qui entraînera une incohérence des données.

Dans la réplication basée sur GTID, la bibliothèque esclave le fera. informer La valeur du GTID de la transaction qui a été exécutée par la bibliothèque principale, puis la bibliothèque principale renverra la liste des GTID de toutes les transactions non exécutées à la bibliothèque esclave et elle peut garantir que la même transaction n'est exécutée qu'une seule fois. dans la bibliothèque esclave spécifiée.

Combat pratique

1 Créez un compte de réplication sur la base de données principale et accordez les autorisations

La réplication basée sur GTID copiera automatiquement les données de la base de données esclave vers la base de données esclave. La transaction exécutée est relue, ne créez donc pas le même compte sur d'autres bibliothèques esclaves. Si le même compte est créé, cela peut provoquer des erreurs de lien de réplication. .

mysql> create user 'repl'@'172.%' identified by '123456';

Notez que le mot de passe en production doit suivre les spécifications pertinentes pour atteindre une certaine force de mot de passe et stipuler que la base de données principale n'est accessible que sur un segment de réseau spécifique sur la base de données esclave.

mysql> grant replication slave on *.* to 'repl'@'172.%';

Afficher l'utilisateur

mysql> select user, host from mysql.user;
+-----------+-----------+
| user  | host  |
+-----------+-----------+
| prontera | %   |
| root  | %   |
| mysql.sys | localhost |
| root  | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

Afficher l'autorisation

mysql> show grants for repl@'172.%';
+--------------------------------------------------+
| Grants for repl@172.%       |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.%' |
+--------------------------------------------------+
1 row in set (0.00 sec)

2. Configurer le serveur de base de données principal

[mysqld]
log_bin = /var/log/mysql/mysql-bin
log_bin_index = /var/log/mysql/mysql-bin.index
binlog_format = row
server_id = 101
gtid_mode = ON
enforce_gtid_consistency = ON
#log_slave_updates = ON

REMARQUE : C'est une bonne habitude de séparer les journaux et les données, et il est préférable de les placer dans différentes partitions de données

enforce_gtid_consistency Forcer la cohérence GTID, après avoir activé, ce qui suit les commandes ne peuvent plus être utilisées

créer une table ... sélectionner ...

mysql> create table dept select * from departments;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

Parce qu'il s'agit en fait de deux

événements indépendants, ils ne peuvent être divisés qu'en créez d'abord une table, puis insérez les données dans la table

create temporary table

Les tables temporaires ne peuvent pas être créées dans une transaction

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table dept(id int);
ERROR 1787 (HY000): Statement violates GTID consistency: 
CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. 
These statements are also not allowed in a function or trigger because functions and triggers are also 
considered to be multi-statement transactions.

Mettre à jourTable de transactions et non -table de transaction (MyISAM) dans la même transaction

mysql> CREATE TABLE `dept_innodb` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `dept_myisam` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE = `MyISAM`;
Query OK, 0 rows affected (0.03 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dept_innodb(id) value(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into dept_myisam(id) value(1);
ERROR 1785 (HY000): Statement violates GTID consistency: 
Updates to non-transactional tables can only be done in either autocommitted statements or 
single-statement transactions, and never in the same statement as updates to transactional tables.

Il est donc recommandé de choisir Innodb comme moteur de base de données par défaut.

log_slave_updates Cette option est nécessaire pour la réplication basée sur GTID dans MySQL 5.6, mais cela augmente la charge IO du serveur esclave, et cette option n'est plus nécessaire dans MySQL 5.7

3 , configurez le serveur esclave

master_info_repository et relay_log_info_repository

Avant MySQL 5.6.2, les informations du maître enregistrées par l'esclave et les informations du journal binaire de l'application esclave étaient stockées dans le fichier, c'est-à-dire master.info et relay-log.info. Après la version 5.6.2, la connexion aux tables est autorisée. Les tables correspondantes sont mysql.slave_master_info et mysql.slave_relay_log_info, et les deux tables sont des tables du moteur innodb

4. 🎜>

[mysqld]
log_bin = /var/log/mysql/mysql-bin
log_bin_index = /var/log/mysql/mysql-bin.index
server_id = 102
# slaves
relay_log  = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = /var/log/mysql/relay-bin.info
enforce_gtid_consistency = ON
log_slave_updates = ON
read_only = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

Sauvegardez d'abord les données sur la base de données principale Le code est le suivant :

--master-data=2 Cette option ajoute l'emplacement et le nom du fichier binlog du serveur actuel dans le fichier de sortie (afficher l'état du maître). S'il est 1, le décalage est intégré à la commande CHANGE MASTER. S'il est 2, les informations de décalage de sortie seront commentées.

--all-databases Étant donné que la réplication basée sur GTID enregistrera toutes les transactions, cette option est recommandée pour créer un vidage complet

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases --events -u root -p > backup.sql

Erreurs courantes

Lors de l'importation de SQL depuis la base de données, apparaît. Le code est le suivant :

À ce stade, entrez la ligne de commande MySQL à partir de la base de données et utilisez reset master

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

5. Démarrez la réplication basée sur GTID

Maître existant@172.20.0.2 et esclave@172.20.0.3, et les données ont été synchronisées avec la base de données esclave via mysqldump Maintenant dans. la base de données esclave Configurer un lien de réplication sur le serveur esclave

Démarrer la réplication

mysql> change master to master_host='master', master_user='repl', master_password='123456', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

Après un démarrage réussi, vérifier l'état

de l'esclave

mysql> start slave;

Slave_IO_Running, Slave_SQL_Running est OUI,

et Slave_SQL_Running_State est Slave a lu tous les journaux de relais ; en attente de plus de mises à jour, cela signifie que le lien de réplication est construit avec succès

mysql> show slave status\G
*************************** 1. row ***************************
    Slave_IO_State: Queueing master event to the relay log
     Master_Host: master
     Master_User: repl
     Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000002
   Read_Master_Log_Pos: 12793692
    Relay_Log_File: relay-bin.000002
    Relay_Log_Pos: 1027
  Relay_Master_Log_File: mysql-bin.000002
    Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
    Replicate_Do_DB:
   Replicate_Ignore_DB:
   Replicate_Do_Table:
  Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
     Last_Errno: 0
     Last_Error:
     Skip_Counter: 0
   Exec_Master_Log_Pos: 814
    Relay_Log_Space: 12794106
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
    Master_SSL_Cert:
   Master_SSL_Cipher:
    Master_SSL_Key:
  Seconds_Behind_Master: 5096
Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
    Master_Server_Id: 101
     Master_UUID: a9fd4765-ec70-11e6-b543-0242ac140002
    Master_Info_File: mysql.slave_master_info
     SQL_Delay: 0
   SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Reading event from the relay log
   Master_Retry_Count: 86400
     Master_Bind:
  Last_IO_Error_Timestamp:
  Last_SQL_Error_Timestamp:
    Master_SSL_Crl:
   Master_SSL_Crlpath:
   Retrieved_Gtid_Set: a9fd4765-ec70-11e6-b543-0242ac140002:1-39
   Executed_Gtid_Set: a9fd4765-ec70-11e6-b543-0242ac140002:1-4
    Auto_Position: 1
   Replicate_Rewrite_DB:
     Channel_Name:
   Master_TLS_Version:
1 row in set (0.00 sec)

6. Résumé

Avantages

Comme il n'est pas nécessaire de définir manuellement le décalage du journal, le basculement peut être facilement effectué

1. Si log_slave_updates est activé, la bibliothèque esclave ne perdra aucune modification sur la bibliothèque maître

2. Inconvénients

Il existe certaines restrictions sur le SQL exécuté

1. Prend en charge uniquement les versions après MySQL 5.6, et il n'est pas recommandé d'utiliser des versions 5.6 antérieures

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!