informatique:db: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"
  • informatique/db/mysql.txt
  • Dernière modification : 2016/12/06 17:50
  • de bn8