Table des matières

MySQL

MySQL

Benchmark de MySQL

SELECT benchmark(100000000,1+2);

Reset un mot de passe root perdu

sudo /etc/init.d/mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql

UPDATE mysql.user SET password = password('VOTRENOUVEAUMOTDEPASSE') WHERE USER = 'root';
FLUSH privileges;
exit

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

Dump des privilèges des utilisateurs

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;}'

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

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

Commenter la ligne :
```
bind-address = 127.0.0.1
```

Exécuter la requête SQL suivante :

GRANT replication slave ON *.* TO 'replication'@192.168.88.102 IDENTIFIED BY 'mdpslave';

Redémarrer le service MySQL

Sur serveur 2 :

Editer le fichier /etc/mysql/my.cnf

Ajouter dans [mysqld] :

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log

Commenter la ligne :
```
bind-address = 127.0.0.1
```

Ajouter dans [mysqld] :

master-host = 192.168.88.101
master-user = replication
master-password = mdpslave
master-port = 3306

A partir de MySQL 5.7, cette configuration est faite à l'aide de la requête SQL suivante :

CHANGE MASTER TO MASTER_HOST='192.168.88.101', MASTER_USER='replication', MASTER_PASSWORD='mdpslave';

Redémarrer le service MySQL
Exécuter la requête SQL suivante :
```
START slave;
```
Pour savoir si la réplication se passe correctement exécuter la requête suivante :
```
SHOW slave STATUS\G;
```

Le résultat de cette requête devrais être a peu près :

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

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 :
```
SHOW master STATUS;
```

Le résultat de cette requête devrais être a peu près :

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Exécuter ensuite la requête SQL suivante :

GRANT replication slave ON *.* TO 'replication'@192.168.88.101 IDENTIFIED BY 'mdpslave2';

Sur serveur 1 :

Editer le fichier /etc/mysql/my.cnf

Ajouter dans [mysqld] :

master-host = 192.168.88.102
master-user = replication
master-password = mdpslave2
master-port = 3306

Redémarrer le service MySQL
Exécuter la requête SQL suivante :
```
START slave;
```
Vérifier que la synchronisation se passe correctement dans se sens également en exécutant la requête SQL :
```
SHOW slave STATUS\G;
```

Le résultat de cette requête devrais être a peu près :

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

Les valeurs Slave_IO_Running et Slave_SQL_Running doivent être à Yes.
Sur serveur 2, la requête SQL suivante
```
SHOW master STATUS;
```
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 :
```
stop slave;
```
Sur le master :
- Exécuter :
```
FLUSH logs;
SHOW master STATUS;
```
- Noter le nom du log et la position

Sur le slave :

CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;
START slave;

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 :
```
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
```
- Copier le résultat de la dernière requête (File & Position)
- Faire un dump des bases :
```
mysqldump --all-databases > /tmp/mysql.sql
```
- Exécuter la commande SQL suivante :
```
UNLOCK TABLES;
```
Sur l'autre machine :
- Exécuter la commande SQL suivante :
```
STOP SLAVE;
```
- Importer le dump généré sur l'autre machine :
```
mysql < /tmp/mysql.sql
```
- 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 :
```
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
START SLAVE;
```
- Vérifier que la synchro est bien reparti à l'aide de la requête SQL :
```
SHOW slave STATUS\G;
```

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 :

#!/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

Source : http://blog.barfoo.org/2008/09/19/defragmenting-all-fragmented-myisam-tables/

Recrée l'utilisateur debian-sys-maint

GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION;

Extraire les dumps DB par DB d'un dump complet

Le script ci-dessous automatise cela :

#!/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"