Base de données

MySQL

MySQL

Réplication MySQL

Réplication MySQL

Voir l'état d'une réplication

Il faut taper la commande

SHOW SLAVE STATUS\G;

Problème de réplication suite au redémarrage du serveur MySQL

'You cannot 'ALTER' a log table

Si vous avez ce message

Erreur : CRIT Slave_IO_Running: Yes Slave_SQL_Running: No Last_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query  

Alors connectez vous sur vos serveurs MySQL (master and slave) et tapez les commandes ci dessous :

STOP SLAVE;  
SET GLOBAL slow_query_log = "OFF";  
START SLAVE;  
SET GLOBAL slow_query_log = "ON";  

The server is not configured as slave

Dans ce genre de cas, pas besoin de pleurer. Il faut juste avoir le mot de passe de l'utilisateur replication. Si vous ne l'avez pas, changez le sur vos deux serveurs :

MySQL 1 (Master)

set password for 'replication'@'192.168.1.%' = password('dfihgartdccuvbg');

MySQL 2 (Slave)

set password for 'replication'@'192.168.1.%' = password('dfihgartdccuvbg');

Ensuite, faire un dump du serveur MySQL 1 et récupérer la position de ses logs :

Dans un premier terminal, connectez vous sur le master MySQL et tapez la commande

FLUSH TABLES WITH READ LOCK

Sans fermer le terminal (sinon vous perdrez le lock et donc des données), passez sur un autre terminal

mysqldump --master-data=1 -u root -p --single-transaction --routines --triggers --events --all-databases > /var/tmp/mysql1.dump
mysql -u root -p -e "show master status;"

Le show master status devrait vous retourner des valeurs de la forme :

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000076 |   791681 |              |                  |
+------------------+----------+--------------+------------------+

Quittez le terminal 1 pour enlever le lock.

Réimporter le dump dans le serveur MySQL2 Une fois l'import réalisé, tapez la commande :

CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='replication', MASTER_PASSWORD='dfihgartdccuvbg', MASTER_LOG_FILE='mysql-bin.000076', MASTER_LOG_POS=791681;

Cette méthode peut fonctionner sur de la réplication croisée mais avec plus de risque.

Écart avec la réplication croisée MySQL

Une réplication MySQL croisée c'est quand les deux serveurs MySQL sont et master et slave. Il est donc en l'état impossible de savoir quel serveur est plus en avance sur l'autre. Dans une partie des cas, la réplication croisée n'est utilisé que lorsque un des deux serveurs se plante, le second serveur prend la relève. Cela est soit géré dans le code de l'application, soit avec un transfert d'IP entre les deux serveurs (soit pas du tout géré mais chuuuut).

Pour réparer la réplication croisée, on va devoir verouiller à l'écriture les tables. On a rien sans rien même si une majorité des gens pensent qu'il est possible de ne gérer aucune coupure pour réparer la réplication...

Dans l'exemple ci dessous, on a deux serveurs MySQL

SQL1 : 192.168.1.101 SQL2 : 192.168.1.102

On se connecte donc sur les deux serveurs MySQL en root et on tape :

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;

On ouvre maintenant deux autres terminal.

Sur un des deux terminal, connectez vous sur le serveur SQL1. Verifions d'abord l'ID de ce master

SHOW VARIABLES LIKE 'server_id';

Il va renvoyer cette valeur ci

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+

On peut donc faire un dump de la base de donnée

mysqldump --master-data=1 -u root -p --single-transaction --routines --triggers --events --all-databases --insert-ignore > /var/tmp/mysql1.dump

Remplcer bien entendu le 1 de --master-data=1 par la valeur server_id (qui peut bien entendu être différente).

Sur le second terminal, connectez vous sur le serveur SQL2, verifiez également le server_id puis faites un dump de la base de donnée.

mysqldump --master-data=2 -u root -p --single-transaction --routines --triggers --events --all-databases --insert-ignore > /var/tmp/mysql2.dump

Le --insert-ignore aura pour effet de modifier les entrées différente (genre une entrée supprimé, une entrée mise à jour...) mais n'effacera pas les entrées déjà existantes (aucun DROP TABLE ou autre). Cela n'est donc pas destructif.

Une fois les deux dump fait, transférer les sur l'autre serveur (le dump de SQL2 devra aller sur SQL1 et vice versa).

Sur SQL1

mysql -u root -p < /var/tmp/mysql2.dump
mysql -u root -p -e "RESET SLAVE"

Sur SQL2

mysql -u root -p < /var/tmp/mysql1.dump
mysql -u root -p -e "RESET SLAVE"

Une fois les deux imports réalisés et le RESET SLAVE executé, retournee sur les deux premiers terminaux et fermez les.

Il vous reste donc plus que deux terminaux, connectés respectivement sur SQL1 et SQL2. Sur chacun des terminaux tapez :

mysql -u root -p -e "START SLAVE"

Puis verifiez si tout vas bien avec

SHOW SLAVE STATUS\G;

Normalement tout est réglé.

MySQL

Sauvegarde d'un serveur MySQL

Sauvegarde d'un serveur MySQL

mysqldump -u backup --password=xxx --single-transaction --routines --triggers --events --all-databases

Ajouter --master-data=1 s'il s'agit d'une réplication

MySQL

Activer les slow logs MySQL

Dans la vie d'un site web en production, le temps de réponse à la base peut augmenter pour divers raisons.

Pour identifier les requêtes il existe deux méthodes.

Méthode à chaud

Connectez vous à votre base de donnée avec des droits avancé (si possible root).

Activez les slow logs

SET GLOBAL slow_query_log = 'ON';

Definissez le temps d'execution qu'une requête peut avoir avant d'être considéré comme lente

SET GLOBAL long_query_time = 5;

En remplaçant 5 par la valeur que vous souhaitez en seconde.

Choississez dans quel dossier doit être écrit ces logs

SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

Puis testez

SELECT SLEEP(10);

Vous devriez avoir le résultat dans le fichier /var/log/mysql/mysql-slow.log.

Pour le désactivez passer la variable slow_query_log à OFF

SET GLOBAL slow_query_log = 'OFF';

Méthode à froid

Éditez le fichier /etc/mysql/my.cnf

Ajoutez les lignes ci dessous :

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 5

slow_query_log Active les slow query slow_query_log_file Définie le chemin dans lequel MySQL va écrire ces logs long_query_time Définie le temps d'execution qu'une requête peut avoir avant d'être considéré comme lente

Relancez MySQL

systemctl restart mysql

Pour testez, connectez vous sur votre base de données puis tapez la commande suivante

SELECT SLEEP(10);

Vous devriez voir une occurence appraitre dans le fichier /var/log/mysql/mysql-slow.log

Pour désactivez ça, retirez les directives ajoputé dans /etc/mysql/my.cnf et redémarrez MySQL.

PostgreSQL

PostgreSQL

Commandes utiles avec PostgreSQL

Créer une base de donnée

create database mydb  OWNER myroles;

Lister les bases de données

Pour lister les bases de données tapez dans PostgreSQL

\l

Pour lister les bases de données avec plus d'information, tapez

\l+

Se connecter à une base de donnée

\c dbname

Lister les tables

\dt

Décrire la table

\d+ tablename

Lister les utilisateurs

\du
PostgreSQL

PostgreSQL et les connexions en cours

PostgreSQL est un outil génial, si génial qu'il permet d'avoir des connexions entre un service et lui (oui je sais, c'est logique ^^ )

Prérequis

Il est recommandé d'avoir un accès superuser pour effectuer ces actions. Généralement on les executent lorsqu'il y a des problèmes d'accès à la base de donnée (des connexions ouverte mais morte).

Connaitre le nombre de connexion en simultanée sur une base de donnée

Pour connaitre le nombre de connexion à une base de donnée, tapez la commande suivante avec <DBNAME> votre base de donnée

select datname, usename, pid, application_name, client_addr, client_hostname, client_port, backend_start, query_start, query  
from pg_stat_activity
where datname = '<DBNAME>';

Vous devez avoir un retour similaire :

mastodon=# select datname, usename, pid, application_name, client_addr, client_hostname, client_port, backend_start, query_start, query  
from pg_stat_activity
where datname = 'mastodon';
 datname  | usename  |  pid  |          application_name          | client_addr | client_hostname | client_port |         backend_start         |          query_start          |                                                                                                                                                                                     query                                                                                                                                                                                      
----------+----------+-------+------------------------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 mastodon | mastodon | 31057 | puma: cluster worker 1: 8526 [www] | 10.10.10.3  |                 |       49250 | 2020-01-28 17:25:39.587535+01 | 2020-01-28 17:35:04.662738+01 | SELECT  "accounts".* FROM "accounts" WHERE "accounts"."uri" = $1 LIMIT $2
 mastodon | mastodon | 29827 | puma: cluster worker 3: 8526 [www] | 10.10.10.3  |                 |       49216 | 2020-01-28 17:24:23.619165+01 | 2020-01-28 17:35:16.13175+01  | SELECT  "accounts".* FROM "accounts" WHERE "accounts"."uri" = $1 LIMIT $2
 mastodon | mastodon |  3180 | puma: cluster worker 3: 8526 [www] | 10.10.10.3  |                 |       50088 | 2020-01-28 17:35:12.39345+01  | 2020-01-28 17:35:12.426536+01 | SELECT "status_pins"."status_id" FROM "status_pins" WHERE 1=0 AND "status_pins"."account_id" = $1
 mastodon | mastodon | 31061 | sidekiq 5.2.7 www [0 of 50 busy]   | 10.10.10.3  |                 |       49260 | 2020-01-28 17:25:39.714381+01 | 2020-01-28 17:35:16.176494+01 | COMMIT

Couper des connexions

TIMBER !

Pour couper une connexion, rien de plus simple :

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    pid <> pg_backend_pid() 
    AND pid = '<PID>'
    AND datname = '<DBNAME>';

Avec <PID> le PID que vous souhaitez couper (et que vous avez récupéré plus haut) et <DBNAME> le nom de votre base de donnée. La ligne pid <> pg_backend_pid() permet de ne pas se couper sa propre connexion (mesure de sécurité).

Pour couper toutes les connexions d'une base de donnée :

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    pid <> pg_backend_pid() 
    AND datname = '<DBNAME>';
PostgreSQL

Gérer ses utilisateurs sur PostgreSQL

Cette page est loin d'être complète et pour le moment indique uniquement les commandes utilisé régulièrement

Créer un utilisateur

PostgreSQL fonctionne avec un système de rôle et d'utilisateur. Ici je vais créer un utilisateur qui va nativement créer un rôle:

create user myuser with password 'mypass';

Autoriser un utilisateur à utiliser une base de donnée

grant all privileges on database mydb to myuser;

Ajouter et enlever les droits super utilisateur d'un rôle

Pour ajouter le droit super user:

ALTER ROLE "USER" WITH SUPERUSER;

Pour enlever ce droit:

ALTER ROLE "USER" WITH NOSUPERUSER;
PostgreSQL

Les sauvegardes avec PostgreSQL

PostgreSQL permet plusieurs types de sauvegarde :

La sauvegarde logique

La méthode de sauvegarde la plus simple. Elle va extraire toutes la base de donnée dans un fichier remplis de requêtes SQL pour l'importer par la suite.
C'est la méthode la plus simple à mettre en oeuvre et convient aux petites bases de données (5-10Go) ayant très peu de requêtes (5 requêtes secondes max).
L'inconvénient de cette méthode est qu'elle verouille les tables durant toutes la durée de la sauvegarde est rend donc très peu utilisable la base de donnée.

Sauvegarder la base de donnée

Rien de plus simple

pg_dump dbname > /var/backup/dbname.dump

Restaurer une base de donnée

psql -U <username> -d <dbname> -1 -f <filename>.sql

Ou

pg_restore -U <username> -d <dbname> -1 <filename>.dump

La sauvegarde physique à chaud

Todo (tip, use pgbarman)

La sauvegarde physique à froid

Il s'agit de recupérer uniquement le contenu de /var/lib/postgresql/<version>/main/base avec rsync ou autre pendant que la base est éteinte.