informatique:db:mysql [Zionetrix]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== MySQL ======


===== Benchmark de MySQL =====
<code sql>SELECT benchmark(100000000,1+2);</code>
  
===== Reset un mot de passe root perdu =====

<code bash>sudo /etc/init.d/mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql</code>
<code sql>
update mysql.user set password = password('VOTRENOUVEAUMOTDEPASSE') where user = 'root';
flush privileges;
exit</code>
<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 =====

  * Serveur 1 : 192.168.88.101
  * Serveur 2 : 192.168.88.102

**Principe : ** Le serveur 1 sera à la fois master pour le serveur 2 et le slave pour serveur 2 et inversement.

**Remarque : ** Il est possible en adaptant un peu cette configuration de synchroniser que certaine bases.

  * Instalation sur les deux machines des paquets **mysql-server** et **mysql-client**
  * Sur serveur 1 : 
    * Editer le fichier ///etc/mysql/my.cnf//
      * Ajouter dans //[mysqld]// : <code>server-id = 1
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 sql>grant replication slave on *.* to 'replication'@192.168.88.102 identified by 'mdpslave';</code>
    * Redémarrer le service //MySQL//
  * Sur serveur 2 :
    * Editer le fichier ///etc/mysql/my.cnf//
      * Ajouter dans //[mysqld]// : <code>server-id = 2
log_bin = /var/log/mysql/mysql-bin.log</code>
      * Commenter la ligne : <code>bind-address = 127.0.0.1</code>
      * Ajouter dans //[mysqld]// : <code>master-host = 192.168.88.101
master-user = replication
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 sql>start slave;</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
                  Master_Host: 192.168.88.101
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 263
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 408
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              ...</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 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 |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)</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//
      * Ajouter dans //[mysqld]// : <code>master-host = 192.168.88.102
master-user = replication
master-password = mdpslave2
master-port = 3306</code>
    * Redémarrer le service //MySQL//
    * 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 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
                  Master_Host: 192.168.88.102
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 263
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 408
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              ...</code>
    * Les valeurs //Slave_IO_Running// et //Slave_SQL_Running// doivent être à **Yes**.
  * 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.

===== Si ça se passe mal =====

==== 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 :

  * 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>

===== Extraire les dumps DB par DB d'un dump complet =====

Le script ci-dessous automatise cela :

<code bash>#!/bin/bash

IN="$1"
[ -z "$IN" -o ! -r "$IN" ] && echo "Usage : $0 input.sql" && exit 1

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
}

IFS="
"
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>