Différences

Ci-dessous, les différences entre deux révisions de la page.

--- informatique:sgbd:mysql:replication [21/03/2021 15:42] – [Sur le slave] cyrille
+++ informatique:sgbd:mysql:replication [21/01/2022 10:09] (Version actuelle) – [mysql replication] cyrille
@@ Ligne 1: / Ligne 1: @@
 ====== mysql replication ======
+Des questions:
-Les questions:
   * vérifier automatiquement que la réplication fonctionne
   * le slave est désynchroniser car il ne pouvait pas communiquer avec le master
   * le fichier bin_log est trop petit pour contenir les requêtes le temps que le slave les récupère
+Wording / Vocabulaire : **master & slave -> primary & replica**.
+  * mariadb.com/kb
+    * [[https://mariadb.com/kb/en/setting-up-replication/|Setting Up Replication]]
 Voir [[/informatique/sgbd/mysql|MySql]].
+===== Quelques configs & manips =====
+  * [[https://mariadb.com/kb/en/binary-log/|Binary Log ]]
+    * Lui donner un nom --**log-basename** pour qu'il ne soit pas dépendant du hostname.
+    * Définissez **expire_logs_days** plus long que tout décalage possible du replica.
+    * max_binlog_size : default & max = 1GB
+  * [[https://mariadb.com/kb/en/relay-log/|Relay Log]]
+    * **max_relay_log_size** : default = 0
+[[https://mariadb.com/kb/en/using-and-maintaining-the-binary-log/#safely-purging-binary-log-files-while-replicating|Safely Purging Binary Log Files While Replicating]]
+To be sure replication is not broken while deleting log files, perform the following steps:
+  * Get a listing of binary log files on the primary by running **SHOW BINARY LOGS**.
+  * Go to each replica server and run **SHOW SLAVE STATUS** to check which binary log file each replica is currently reading.
+  * Find the earliest log file still being read by a replica. No log files before this one will be needed.
+  * If you wish, make a backup of the log files to be deleted
+  * Purge all log files before (not including) the file identified above.
 ===== Essais de réplication =====
@@ Ligne 49: / Ligne 70: @@
 </code>
+==== Configuration mysql ====
+Pour le master:
+<code>
+#./master.cnf.d/master.cnf
+[server]
+server_id=1
+# activer le log des requêtes pour la réplication
+log-bin
+# default
+#binlog-format=row
+# N'exporter que la table test
+binlog-do-db=test
+#report-host=the-master
+#log-basename=the-master
+</code>
+Pour le slave:
+<code>
+# ./slave.cnf.d/slave.cnf
+[server]
+server_id=2
+report-host=the-slave
+# TODO:
+#relay_log=master-default
+</code>
+=== Documentation ===
+  * MySql 8
+    * [[https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html|17.1.6.3 Replica Server Options and Variables]]
+    * [[https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html|17.1.6.4 Binary Logging Options and Variables]]
+  * MariaDB
+    * [[https://mariadb.com/kb/en/standard-replication/|MariaDB Replication]]
+    * [[https://mariadb.com/kb/en/setting-up-replication/|Setting Up Replication]]
+== binlog-do-db ==
+Sans autre précision, tout sera répliqué, par exemple un conflit de ''PRIMARY KEY'' sur la table ''mysql.user'' peut arrêter la réplication ''[ERROR] Slave SQL: Could not execute Write_rows_v1 event on table mysql.user; Duplicate entry 'localhost-root' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysqld-bin.000001, end_log_pos 26196, Gtid 0-1-36, Internal MariaDB error code: 1062''
+==== Démarrer le tout ====
 On lance les services
@@ Ligne 91: / Ligne 160: @@
 === Sur le slave ===
-Déclarer le master :
+Déclarer le master.
+Attention à changer ''MASTER_HOST='192.168.0.21''' par l'ip de votre machine.
 <code sql>
- CHANGE MASTER TO MASTER_LOG_POS=<XXX>, MASTER_HOST='<host ip>', MASTER_PORT=3308, MASTER_USER='replication-slave', MASTER_PASSWORD='secret';
+STOP SLAVE ;
+CHANGE MASTER TO MASTER_LOG_POS=<XXX>, MASTER_HOST='192.168.0.21', MASTER_PORT=3308, MASTER_USER='replication-slave', MASTER_PASSWORD='secret';
+START SLAVE ;
 </code>
 db-slave_1 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--log-basename=#' or '--relay-log=mysqld-relay-bin' to avoid this problem.
-Documentation:
+=== Documentation ===
   * syntaxe [[https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html|CHANGE MASTER]]
     * notamment pour MASTER_CONNECT_RETRY, MASTER_RETRY_COUNT