Détail sur l’utilisation de la mémoire vive par MySQL

L’utilisation maximum de la mémoire vive par MySQL se calcule de la manière suivante d’après mysqltuner.pl :

key_buffer_size + max_tmp_table_size + ( read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size ) * max_connections

max_tmp_table_size : si tmp_table_size > max_heap_table_size -> max_heap_table_size sinon tmp_table_size

Import/Export MySQL « rapide »

export.sh

Les données sont exporté avec mysqldump et le paramètre -T

• -T : Creates tab separated textfile for each table to given path. (creates .sql and .txt files). NOTE: This only works if mysqldump is run on the same machine as the mysqld daemon.

Ce script doit donc etre executé directement sur le serveur MySQL

#!/usr/bin/env bash
DST='/var/backup/mysql/dump'

for bdd in `mysql --skip-column-names -B -e "show databases;" | egrep -v "^information_schema$|^mysql$"`; do
        echo -ne $bdd
        mysql --skip-column-names -B -e "SHOW CREATE DATABASE $bdd;" | cut -d" " -f2- > $DST/create-$bdd.sql
        for table in `mysql --skip-column-names -B $bdd -e "show tables;"`; do
                mkdir $DST/$bdd 2>/dev/null 1>&2
                chown mysql:mysql $DST/$bdd
                echo -ne "."
                mysqldump -T $DST/$bdd/ $bdd $table
        done
        echo -ne "\r\n"
done

import.sh

#!/usr/bin/env bash
SRC='/var/backup/mysql'

for bdd in `ls -1 $SRC`; do
        echo -ne $bdd
        for table in `ls -1 $SRC/$bdd | awk -F. '{print $1}' | sort | uniq`; do
                mysql $bdd -e " SET foreign_key_checks = 0;
                                SOURCE $SRC/$bdd/$table.sql;
                                LOAD DATA INFILE '$SRC/$bdd/$table.txt'
                                INTO TABLE $table;
                                SET foreign_key_checks = 1;"
                echo -ne "."
        done
        echo -ne "\r\n"
done

Convertir toutes les tables MyISAM en InnoDB

Pour toutes les tables de toutes les bases

« Version » séquentiel

la convertion de la table suivante sera faite que si la precedente est fini (ingnorer les erreurs pour les tables deja en  InnoDB )

#!/usr/bin/env bash
for db in `mysql --batch --skip-column-names -e "show databases;" | egrep -v "mysql|information_schema|performance_schema|lost"`; do
	echo "# "$db
	for table in `echo "show tables" | mysql --batch --skip-column-names $db`; do
		echo "-- "$table
		mysql $db -e "ALTER TABLE $table ENGINE = InnoDB;"
	done
done

« Version » Bourine

#!/usr/bin/env bash
for db in `mysql --batch --skip-column-names -e "show databases;" | egrep -v "mysql|information_schema|performance_schema|lost"`; do
	echo "# "$db
	for table in `echo "show tables" | mysql --batch --skip-column-names $db`; do
		echo "-- "$table
		mysql $db -e "ALTER TABLE $table ENGINE = InnoDB;" &
	done
done

Pour toutes les tables d’une base

Vous pouvez le faire de manière soft (Table par table)

#!/usr/bin/env bash
DATABASENAME="votrebdd"

for i in `echo "show tables" | mysql --batch --skip-column-names $DATABASENAME`; do
mysql $DATABASENAME -e "ALTER TABLE `$i` ENGINE = InnoDB;"
done

Ou en version “Multithread” (Bourine)

#!/usr/bin/env bash
DATABASENAME="votrebdd"

for i in `echo "show tables" | mysql --batch --skip-column-names $DATABASENAME`; do
mysql $DATABASENAME -e "ALTER TABLE `$i` ENGINE = InnoDB;" &
done

Extraire les requêtes SQL depuis un binlog au format RAW

Le format des log binaires RAW a un gros inconviant, lorsque vous voulez rejouer des requetes a partir des log binaires, l’extraction de celle ci est plus complexe.

#!/bin/sh
LIST=$(ls -1 /var/log/mysql/mysql-bin.* | grep -v index | awk -F "." '{print $2}')
DIRDST="/mnt/pad/"
DIRSRC="/var/log/mysql"
DATABASE="votrebdd"

echo -n > $DIRDST"/"result.txt

for i in $LIST; do
echo -ne $i
mysqlbinlog --database=$DATABASE --base64-output=DECODE-ROWS --verbose --start-datetime="2012-01-06 00:00:00" --stop-datetime="2012-01-06 13:00:00" $DIRSRC"/mysql-bin."$i | grep "^###" $DIRDST"/"result.txt
echo -ne "\tdone\r\n"
done

Supprimez ou modifiez les parametres suivant en fonction de vos besoin :

• –start-datetime : Date de debut d’extraction
• –stop-datetime : Apres cette date, les logs ne seront plus extrait

Le résultat est presque injectable directement dans MySQL, il nécessite encore quelque traitement

Les variables système

La liste des variables système MySQL se trouve sur http://dev.mysql.com[en].

Il y est indiqué si ces variables sont dynamique ou non, si elle le sont, vous pouvez les changer a chaud (ne pas oublier de mettre a jour le fichier de configuration MySQL ).

Afficher un ou des variables :

mysql> show global variables like '%sort_buffer%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| myisam_sort_buffer_size | 16777216 |
| sort_buffer_size        | 524280   |
+-------------------------+----------+
2 rows in set (0.00 sec)

Modifier une variable :

mysql> set global sort_buffer_size = 16 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)