Différences

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

--- informatique:db:mysql [2012/07/15 16:30] – [Si ça se passe mal] nico
+++ informatique:db:mysql [2016/12/06 17:50] (Version actuelle) – [Réplication Master-Master] bn8
@@ Ligne 3: / Ligne 3: @@
 ===== Benchmark de MySQL =====
-  SELECT benchmark(100000000,1+2);
+<code sql>SELECT benchmark(100000000,1+2);</code>
 ===== Reset un mot de passe root perdu =====
-  sudo /etc/init.d/mysql stop
+<code bash>sudo /etc/init.d/mysql stop
-  sudo mysqld_safe --skip-grant-tables &
+sudo mysqld_safe --skip-grant-tables &
-  mysql
+mysql</code>
-  > update mysql.user set password = password('VOTRENOUVEAUMOTDEPASSE') where user = 'root';
+<code sql>
-  > flush privileges;
+update mysql.user set password = password('VOTRENOUVEAUMOTDEPASSE') where user = 'root';
-  > exit
+flush privileges;
-  sudo /etc/init.d/mysql stop
+exit</code>
-  sudo /etc/init.d/mysql start
+<code bash>sudo /etc/init.d/mysql stop
+sudo /etc/init.d/mysql start</code>
+===== Dump des privilèges des utilisateurs =====
+<code bash>mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
+'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
+) AS query FROM mysql.user" | \
+mysql $@ | \
+sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'</code>
 ===== Réplication Master-Master =====
@@ Ligne 31: / Ligne 39: @@
 log_bin = /var/log/mysql/mysql-bin.log</code>
       * Commenter la ligne : <code>bind-address = 127.0.0.1</code>
-    * Exécuter la requête SQL suivante : <code>grant replication slave on *.* to 'replication'@192.168.88.102 identified by 'mdpslave';</code>
+    * Exécuter la requête SQL suivante : <code sql>grant replication slave on *.* to 'replication'@192.168.88.102 identified by 'mdpslave';</code>
     * Redémarrer le service //MySQL//
   * Sur serveur 2 :
@@ Ligne 42: / Ligne 50: @@
 master-password = mdpslave
 master-port = 3306</code>
+<note>A partir de MySQL 5.7, cette configuration est faite à l'aide de la requête SQL suivante : <code sql>CHANGE MASTER TO MASTER_HOST='192.168.88.101', MASTER_USER='replication', MASTER_PASSWORD='mdpslave';</code></note>
     * Redémarrer le service //MySQL//
-    * Exécuter la requête SQL suivante : <code>start slave;</code>
+    * Exécuter la requête SQL suivante : <code sql>start slave;</code>
-    * Pour savoir si la réplication se passe correctement exécuter la requête suivante : <code>show slave status\G;</code>
+    * Pour savoir si la réplication se passe correctement exécuter la requête suivante : <code sql>show slave status\G;</code>
     * Le résultat de cette requête devrais être a peu près : <code>*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
@@ Ligne 60: / Ligne 71: @@
               ...</code>
     * Les valeurs //Slave_IO_Running// et //Slave_SQL_Running// doivent être à **Yes**.
-    * Cette vérification est également possible depuis serveur 1 en exécutant la requête SQL suivante : <code>show master status;</code>
+    * Cette vérification est également possible depuis serveur 1 en exécutant la requête SQL suivante : <code sql>show master status;</code>
     * Le résultat de cette requête devrais être a peu près : <code>+------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
@@ Ligne 67: / Ligne 78: @@
 +------------------+----------+--------------+------------------+
 row in set (0.00 sec)</code>
-    * Exécuter ensuite la requête SQL suivante : <code>grant replication slave on *.* to 'replication'@192.168.88.101 identified by 'mdpslave2';</code>
+    * Exécuter ensuite la requête SQL suivante : <code sql>grant replication slave on *.* to 'replication'@192.168.88.101 identified by 'mdpslave2';</code>
   * Sur serveur 1 :
     * Editer le fichier ///etc/mysql/my.cnf//
@@ Ligne 75: / Ligne 86: @@
 master-port = 3306</code>
     * Redémarrer le service //MySQL//
-    * Exécuter la requête SQL suivante : <code>start slave;</code>
+    * Exécuter la requête SQL suivante : <code sql>start slave;</code>
-    * Vérifier que la synchronisation se passe correctement dans se sens également en exécutant la requête SQL : <code>show slave status\G;</code>
+    * Vérifier que la synchronisation se passe correctement dans se sens également en exécutant la requête SQL : <code sql>show slave status\G;</code>
     * Le résultat de cette requête devrais être a peu près : <code>*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
@@ Ligne 92: / Ligne 103: @@
               ...</code>
     * Les valeurs //Slave_IO_Running// et //Slave_SQL_Running// doivent être à **Yes**.
-  * Sur serveur 2, la requête SQL suivante <code>show master status;</code> devrais retourner à peut près la même chose que sur serveur 1
+  * Sur serveur 2, la requête SQL suivante <code sql>show master status;</code> devrais retourner à peut près la même chose que sur serveur 1
 La synchronisation est normalement opérationnel.
@@ Ligne 98: / Ligne 109: @@
 ===== Si ça se passe mal =====
-      *Si vous rencontrez une erreur du type :
+==== Could not find first log file name in binary log index file ====
+Si vous rencontrez une erreur du type :
    Last_IO_Errno: 1236
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
-Voici la solution à appliquer
+Voici la solution à appliquer :
+  * Sur le slave : <code sql>stop slave;</code>
+  * Sur le master :
+    * Exécuter : <code sql>flush logs;
+show master status;</code>
+    * Noter le nom du log et la position
+  *Sur le slave : <code sql>CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;
+start slave;</code>
+==== Resynchro complète d'une des machines sur l'autre ====
+  * Sur une des deux machines que l'on choisira comme étant **master** :
+    * Exécuter les commandes SQL suivantes : <code sql>RESET MASTER;
+FLUSH TABLES WITH READ LOCK;
+SHOW MASTER STATUS;</code>
+    * Copier le résultat de la dernière requête (//File// & //Position//)
+    * Faire un dump des bases : <code bash>mysqldump --all-databases > /tmp/mysql.sql</code>
+    * Exécuter la commande SQL suivante : <code sql>UNLOCK TABLES;</code>
+  * Sur l'autre machine :
+    * Exécuter la commande SQL suivante : <code sql>STOP SLAVE;</code>
+    * Importer le dump généré sur l'autre machine : <code bash>mysql < /tmp/mysql.sql</code>
+    * Exécuter les commandes SQL suivantes en adaptant les variables //MASTER_LOG_FILE// et //MASTER_LOG_POS// avec les informations obtenu lors des premières requêtes sur la machine master : <code sql>RESET SLAVE;
+CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
+START SLAVE;</code>
+    * Vérifier que la synchro est bien reparti à l'aide de la requête SQL : <code sql>show slave status\G;</code>
+===== Optimiser MySQL =====
+Utiliser l'outil **mysqltuner** qui vous dira que faire. Si il vous en parle, pour défragmenter vos tables, utiliser le script suivant :
+<code bash>#!/bin/bash
+# Get a list of all fragmented tables
+FRAGMENTED_TABLES="$( mysql -e "use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0;" | grep -v '^+' | sed 's,\t,.,' )"
+# Run Optimize on
+for fragment in $FRAGMENTED_TABLES; do
+  database="$( echo $fragment | cut -d. -f1 )"
+  table="$( echo $fragment | cut -d. -f2 )"
+  [ $fragment != 'TABLE_SCHEMA.TABLE_NAME' ] && mysql -e "USE $database; OPTIMIZE TABLE $table;" > /dev/null 2>&1
+done</code>
+**Source : ** http://blog.barfoo.org/2008/09/19/defragmenting-all-fragmented-myisam-tables/
+===== Recrée l'utilisateur debian-sys-maint =====
+<code sql>GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION;</code>
-      *Sur le slave :
+===== Extraire les dumps DB par DB d'un dump complet =====
-   stop slave;
+Le script ci-dessous automatise cela :
-      *Sur le master :
+<code bash>#!/bin/bash
-   flush logs;
+IN="$1"
-   show master status; — Noter le nom du log et la position
+[ -z "$IN" -o ! -r "$IN" ] && echo "Usage : $0 input.sql" && exit 1
-      *Sur le slave :
+currentdb=""
+dbline=""
+endline=""
+nbextracted=0
+headend=""
+function extract() {
+        headend="$1"
+        db="$2"
+        f="$3"
+        e="$4"
+        file=db_$( echo "$db"|sed 's/[^A-Za-z\-\_]//g' ).sql
+        echo -n "Extract DB $db (from $f to $e) to $file ... "
+        sed -n 1,${headend}p $IN > $file
+        sed -n ${f},${e}p $IN >> $file
+        echo "end."
+        let nbextracted=nbextracted+1
+}
-   CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;
+IFS="
-   start slave;
+"
+for line in $( grep -n '\-\- Current Database:' $IN )
+do
+        nbline=$( echo "$line"|cut -d':' -f1 )
+        if [ ! -z "$currentdb" ]
+        then
+                let f=dbline-1
+                let e=nbline-2
+                extract "$headend" "$currentdb" $f $e
+        else
+                let headend=nbline-2
+        fi
+        dbline=$nbline
+        currentdb=$( echo "$line"|cut -d'`' -f2 )
+done
+if [ ! -z "$currentdb" ]
+then
+        let f=dbline-1
+        e=$( wc -l $IN|awk '{print $1}' )
+        extract "$headend" "$currentdb" $f $e
+fi
+echo "$nbextracted extracted DB(s) from $IN"</code>