Ce tutoriel aborde uniquement l'administration de MySQL, pour les requêtes SQL voir : sql

Pour que MySQL travaille avec un encodage UTF-8, insérer les lignes suivantes dans my.cnf dans la section [mysqld] :

character-set-server=utf8
default-collation=utf8_unicode_ci

Pour que MySQL travaille uniquement en minuscule, insérer les lignes suivantes dans my.cnf dans la section [mysqld] :

lower_case_table_names=1

Pour que l'importation de données soit fait en UTF-8, insérer les lignes suivantes dans my.cnf dans la section [mysql] :

default-character-set=utf8

Lancement du client avec authentification :

mysql --user=user_name --password=your_password db_name

ou bien

mysql -u root -p'your_password db_name' db_name

Pour obtenir par exemple de l'aide sur SHOW :

help SHOW;

Pour connaître les variables de mysql:

SHOW VARIABLES;

Pour lister les bases de données :

SHOW databases;

Pour voir les tables de la base de données :

SHOW tables;

Pour montrer les index de la table users :

SHOW INDEX FROM users;

Pour afficher le statut des tables d'une base :

use my_database
show table status;

Voici quelques syntaxes qui donnent un résultat équivalent à show table status mais qui permet de filtrer :

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();
SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.tables WHERE table_schema = DATABASE();
SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY TABLE_ROWS;
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY TABLE_ROWS;

Exporter un tables vers un fichier CSV :

SELECT * FROM my_table
INTO OUTFILE '/tmp/my_table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Pour sélectionner par exemple la base de données dbperso

use dbperso;

Pour voir la définition des champs de la table users :

describe users;

ou

desc users;

Pour voir le nombre de ligne par tables :

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'ma_base';

Ajouter un utilisateur myuser sur la base dbperso :

GRANT CREATE,INSERT,DELETE,UPDATE,SELECT on dbperso.* to 'myuser'@'localhost' IDENTIFIED BY '<mypassword>';

ou pour tous les droits sur la base :

GRANT ALL PRIVILEGES on dbperso.* to 'myuser'@'localhost' IDENTIFIED BY '<mypassword>';

Pour autoriser l'utilisateur à donner en plus des droits à un autre utilisation sur la base :

GRANT ALL PRIVILEGES on dbperso.* to 'myuser'@'localhost' IDENTIFIED BY '<mypassword>' WITH GRANT OPTION;

Pour que les droits soient pris en compte lancer :

FLUSH PRIVILEGES;

Pour retirer les droits à un utilisateur :

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'myuser'@'localhost';

Remarque : ceci supprime pas l'utilisateur.

Pour executer une requêtes dans un batch :

Ecrire la requête dans un fichier temporaire :

echo "SELECT email FROM dbperso.users WHERE username='user45696';" > select.sql

Lancer la requête :

mysql < select.sql

ou bien :

mysql -u root -p dbperso
source select.sql

En cas de problème d'accent, insérer la ligne suivante en tête du script SQL pour que l'import se fasse avec un encodage en UTF-8

SET NAMES utf8;

Affichage :

email
user45696@domaine.com

Pour ne pas afficher le nom des colonnes :

mysql --skip-column-names < select.sql

Pour faire un requête en mode non intéractif :

mysql --user=root --password='*******' -e "SHOW DATABASES;"

Pour voir les process mysql (dans la console MySQL) :

SHOW PROCESSLIST

Pour voir les process mysql avec les requêtes détaillées (dans la console MySQL) :

SHOW FULL PROCESSLIST

Pour voir les process mysql sans la console MySQL :

mysqladmin processlist

Si l'on a des doutes sur des requêtes SQL clientes, il peut être très utile de logger les requetes. L'acivation se fait ainsi :

Editer le fichier /etc/my.cnf et ajouter le paramètre suivant :

set-variable = log

ou

set-variable = log=/var/log/mysql-queries.log

Redémarrer Mysql :

/etc/init.d/mysql restart

Pour vérifier que l'option est bien activée :

mysql
SHOW VARIABLES;

Note : Si aucun chemin de fichier n'a été précisé pour la variable set-variable = log le fichier log se trouvera par défaut dans /var/lib/mysql/HOSTNAME.log.

Obtenir la taille des bases de données :

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

Obtenir la taille de chacunes des tables :

use ma_table;
SELECT 
   table_schema as `Database`, 
   table_name AS `Table`, 
   round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Pour vérifier s'il y a des corruptions sur toutes les tables des bases de données :

mysqlcheck -u root -p --all-databases

Pour vérifier et corriger les problèmes sur toutes les bases et tables :

mysqlcheck -u root -p --all-databases --auto-repair

Pour réparer une table

mysql -u root -p
use database
repair table table_name;

Voir http://tutos.tangui.eu.org/15-reparer-une-table-innodb-corrompue-sous-mysql pour réparer des table en InnoDB.

Pour réinitialiser il suffit d'arrêter MySQL et de le redémarrer manuellement en ajoutant l'option –skip-grant-tables :

Récuperer la commande lancé par le script d'init de MySQL :

ps aux |grep mysql

Exemple de résultat attendu :

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock

La ligne à récupérer est celle contenant toutes les options.

Arrêter le service MySQL :

/etc/init.d/mysql stop

Lancer la commande manuellement avec l'option –skip-grant-tables :

/usr/sbin/mysqld --skip-grant-tables --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock &

Lancer le client mysql et réinitialiser le mot de passe :

mysql
UPDATE mysql.user SET Password=PASSWORD('MyNewPassword') WHERE User='root';
quit

Arrêter MySQL manuellement :

kill $(cat /var/run/mysqld/mysqld.pid)

Démarrer MySQL :

/etc/init.d/mysql start

Pour sauvegarder toutes les bases :

./mysqldump -u root -A -p > /repertoire/svg_SQL/Toutes_les_bases.sql

Pour sauvegarder une base et insérer les DROP de base et de table :

mysqldump -u root -p ma_base --add-drop-database --add-drop-table -p > dump_ma_base.sql

Pour exporter uniquement le schémas de la base (pas les données):

mysqldump --no-data -u root -p ma_base

Pour sauvegarder et compresser un export de base :

mysqldump -u root -p ma_base --add-drop-database --add-drop-table -p |gzip > dump_ma_base.sql.gz

Pour restaurer un fichier sql.gz :

zcat dump_ma_base.sql.gz | mysql -u root -p ma_base

Pour exporter certaines tables :

mysqldump -u root -p ma_base table1 table2 > dump_mes_tables.sql

Pour exporter selon un critère “WHERE” dans une table :

mysqldump --where "country = 'France'" ma_base table1 > dump_where_France.sql

Pour exporter une base de données à travers SSH :

mysqldump -u root -p database | gzip -c | ssh root@192.168.1.1 'cat > /tmp/export.sql.gz'

Se positionner sur le répertoire dans lequel vous souhaitez stocker les certificats :

Puis créer les certificats CA, serveur et client :

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

openssl req -newkey rsa:1024 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

openssl req -newkey rsa:1024 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Modifier le fichier my.cnf en ajoutant les chemins des certificats:

ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

Sur le client, il faut préciser le certificat client pour se connecter au serveur, par exemple :

mysql -h server.domain.com -u mysqluser -P 4020 --ssl-ca=/etc/mysql/certs/client-mysql-cert.pem -p

Note : La réplication se met en place une fois la configuration de Mysql terminée et validée et une fois les données importées sur le serveur maître.

Se connecter sur la base du serveur maître :

mysql -u root -p'motdepasse'

Créer les utilisateurs de réplication sur la base de façon à pouvoir répliquer dans les deux sens :

grant replication client on *.* to 'userrepl'@'Serveur-Mysql-Maître' identified by 'xxxxxxxx';
grant replication client on *.* to 'userrepl'@'Serveur-Mysql-Esclave' identified by 'xxxxxxxx';

Se déconnecter de la base, éditer le fichier de configuration /etc/my.cnf puis ajouter dans la section [mysqld] les lignes suivantes :

server-id=1
log-bin=redo-bin

Arrêter et démarrer le service MySQL

Se connecter de nouveau sur la base du serveur maître :

mysql -u root -p'motdepasse'

Verrouiller les tables contre les écritures

flush tables with read lock;

Relever la position dans les redo de mysql :

show master status;

+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| redo-bin.000005     |       98 |              |                  |
+---------------------+----------+--------------+------------------+

Arrêter le service MySQL

Recopier les données de la base maître vers la base esclave :

scp -rp /var/lib/mysql/* Serveur-Mysql-Esclave:/var/lib/mysql/

Démarrer le service MySQL

Editer le fichier de configuration /etc/my.cnf puis ajouter dans la section [mysqld] les lignes suivantes :

server_id=2
log-bin=redo-bin
log-slave-updates
master-host=Serveur-Mysql-Maître
master-port=4020
master-user=userrepl
master-password=xxxxxxxxx

Démarrer le service MySQL

Se connecter à la base :

mysql -u root -p'motdepasse'

Initialiser la configuration de réplication :

change master to
  master_host='Serveur-Mysql-Maître',
  master_user='userrepl',
  master_port='4020'
  master_password='xxxxxxxxx',
  master_log_file='redo-bin.000005',
  master_log_pos='98'
  ;

Lancer la réplication :

start slave;

Pour connaître le statut de la réplication :

show slave status\G;

Quantité de mémoire que MySQL peut mettre des requêtes en buffer. La valeur recommandé est 25% de la mémoire disponible. Exemple si le serveur dispose de 4 Go de mémoire dédié à MySQL :

Editer /etc/mysql/my.cnf et ajouter/modifier :

key_buffer 1G 

Exemple pour augmenter le nombre de table en mémoire cache :

table_cache            = 2000

• Description de SQL : http://fr.wikipedia.org/wiki/Structured_Query_Language
• Syntaxes de définition des données : http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
• Syntaxes de manipulation des données : http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-manipulation.html