MySQL

De Banane Atomic
Aller à la navigationAller à la recherche

Serveur

Bash.svg
# Démarrer le serveur MySQL
sudo systemctl start mysqld.service

# Démarrer le serveur MySQL au démarrage du système
sudo systemctl enable mysqld.service

# Redémarrer le serveur MySQL
sudo systemctl restart mysqld.service

# Connexion au serveur avec l'utilisateur root (-u) et demande du mot de passe (-p)
mysql -u root -p

# Export
mysqldump -u utilisateur -p'mot_de_passe' -rfichier.sql base_de_données base_de_données2 base_de_données3
# -h hôte
# --all-databases toutes les bases de données (même les bases système)
# > fichier.sql au lieu de -rfichier.sql
Si le mot de passe contient un des caractères suivant * ? [ < > & ; ! | $ ( ) il faut l'encadrer de quotes.

Import

Bash.svg
mysql -u utilisateur -pmot_de_passe -D base_de_données --verbose < fichier.sql > fichier.log
# -D définit la bdd dans laquelle sera exécuté le fichier sql
# --one-database définit l'unique bdd à importer depuis un fichier sql qui contient le backup de plusieurs bdd
Mysql.svg
# déjà connecté
use 'base-de-données';
source fichier.sql;

Executer une commande

Bash.svg
mysql -u utilisateur -pmot_de_passe -D base_de_données -e "commande"

Executer un fichier

Bash.svg
cd /ou/se/trouve/mon/fichier
mysql -u utilisateur -pmot_de_passe -D base_de_données < mon_fichier.sql
Mysql.svg
# si on est déjà connecté au serveur MySql
use 'base-de-données';
source mon_fichier.sql

Requêtes

Base de données

Mysql.svg
# 3 styles de commentaires sont possibles
-- celui-ci requiert un espace après le double tiret
/* et celui-ci peut s'intercaller dans une ligne */

# liste les bases de données du serveur
show databases;

# créé ma_base_de_donnees
create database ma_base_de_donnees;
# créé ma_base_de_donnees si elle n'existe pas déjà
create database if not exists ma_base_de_donnees;

# supprime ma_base_de_donnees
drop database ma_base_de_donnees;
# evite l'erreur database ma_base_de_donnees doesn't exist
drop database if exists ma_base_de_donnees;

# choix d'une base de données
use ma_base_de_donnees;
Renommer ou copier une base de données avec PhpMyAdmin

Tables

Mysql.svg
# liste les tables de la bases de données sélectionnée
show tables;
# liste les tables de la bases de données MaBdD
show tables from MaBdD;

# liste toutes les tables commençant par xxx_ dans la bases de données MaBdD
select table_name from information_schema.tables
where table_schema = 'MaBdD' and table_name like 'xxx_%';
# même requête mais sort le résultat sur une seule ligne avec , comme séparateur
select group_concat(table_name) from information_schema.tables
where table_schema = 'MaBdD' and table_name like 'xxx_%';

# affiche en sortie la commande qui permet de drop toutes les tables commençant par xxx_ dans la bases de données MaBdD
select concat('drop table ', group_concat(table_name), ';')
from information_schema.tables where table_schema = 'MaBdD' and table_name like 'xxx_%';

# drop toutes les tables commençant par xxx_ dans la bases de données MaBdD
SET @TABLES = (SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema = 'MaBdD' and table_name LIKE 'xxx_%');
PREPARE drop_statement FROM 'DROP TABLE @tables';
EXECUTE drop_statement USING @TABLES;
DEALLOCATE PREPARE drop_statement;

Colonnes

Mysql.svg
# ajoute « nouvelle_colonne » de type « VARCHAR(32) NOT NULL » à « ma_table »
ALTER TABLE `ma_table` ADD `nouvelle_colonne` VARCHAR(32) NOT NULL AFTER `autre_colonne`;

# supprimer « ma_colonne » dans « ma_table »
ALTER TABLE `ma_table` DROP `ma_colonne`

Comptes utilisateur

Mysql.svg
# Syntaxe : 'user_name'@'host_name'
# 'user_name' seul est équivalent à 'user_name'@'%'
# % est un joker qui signifie n'importe quel hôte

# créé mon_compte
create user 'mon_compte'@'localhost' identified by 'mon_mot_de_passe';
# créé un compte qui s'authentifie automatiquement sans mdp si le nom d'utilisateur est le même
create user 'mon_compte'@'localhost' identified with 'unix_socket';

# Supprime mon_compte
drop user 'mon_compte'@'localhost';

# lister les utilisateurs
select host, user, password, plugin from mysql.user;

# accorde tous les droits à mon_compte sur la base ma_base_de_donnees
grant all on ma_base_de_donnees.* to 'mon_compte'@'localhost';
# accorde tous les droits à mon_compte sur toutes les bdd
grant all on *.* to 'mon_compte'@'localhost' with grant option;
# with grant option = gives the user the ability to give to other users any privileges the user has at the specified privilege level.

# supprime tous les droits d'un utilisateur sur une base
revoke all privileges on ma_base_de_donnees.* from 'mon_compte'@'localhost';

# liste les droits de mon_compte
show grants FOR 'mon_compte'@'localhost';

# changer un mot de passe
set password for 'mon_compte'@'localhost' = password('...');
# ERROR 1133 (28000): Can't find any matching row in the user table
flush privileges;
Échapper les \ avec un deuxième \ dans les mots de passe.

Limiter le nombre de résultats

Mysql.svg
# retourne les 10 premières lignes
select colonne from ma_table
limit 10

Storage Engines

doc

Encoding

Mysql.svg
-- migrer la db vers un encoding utf8
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- afficher l'encoding d'un schema
SELECT default_character_set_name FROM information_schema.SCHEMATA 
WHERE schema_name = "schemaname";

Types de données

Type Bytes Minimum Value Maximum Value
TINYINT 1 -128 127
SMALLINT 2 -32'768 32'767
MEDIUMINT 3 -8'388'608 8'388'607
INT 4 -2'147'483'648 2'147'483'647
BIGINT 8 -9'223'372'036'854'775'808 9'223'372'036'854'775'807
Data Type “Zero” Value
DATE '0000-00-00'
TIME '00:00:00'
DATETIME '0000-00-00 00:00:00'
TIMESTAMP '0000-00-00 00:00:00'
YEAR 0000

Charset et Collation

Mysql.svg
-- afficher le charset et la collation d'une bdd
USE MyDb;
SELECT @@character_set_database, @@collation_database;

-- afficher le charset et la collation de toutes les tables d'une bdd
SHOW TABLE STATUS;

-- changer le charset et la collation d'une bdd
ALTER DATABASE <db_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- changer le charset et la collation d'une table
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- changer le charset et la collation d'une colonne
ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Configuration

Bash.svg
# exécutez le script de configuration
sudo mysql_secure_installation

Pour une utilisation avec php, activez l'un des trois modules:

/etc/php/php.ini
extension=mysqli.so
;extension=pdo_mysql.so
;extension=mysql.so
  • mysql : driver de base pour BdD MySQL
  • mysqli : nouvelles fonctionnalités par rapport à mysql : OO-API + Support new features of MySQL >= 4.1
  • pdo_mysql : même nouvelles fonctionnalités que mysqli + PDO : même API quelque soit le type de BdD utilisé
/etc/mysql/my.cnf
[client]
# Définit le port par défaut
port		= 3306

# autorise seulement les connexions depuis la machine hôte
skip-networking

[mysqld]
skip-external-locking
max_connections = 200
read_buffer_size = 1M
sort_buffer_size = 1M
# Set key_buffer to 5 - 50% of your RAM depending on how much you use MyISAM tables,
# but keep key_buffer_size + InnoDB buffer pool size < 80% of your RAM
key_buffer_size = 512M

InnoDB

/etc/mysql/my.cnf
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:12M:autoextend:max:500M
innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1G
# innodb_additional_mem_pool_size was deprecated in MySQL 5.6.3 and removed in MySQL 5.7.4. 
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 250M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Replication basée sur les global transaction identifiers (GTIDs)

Replication de données de la bdd master vers une bdd slave en asynchrone.

Log

Bash.svg
# afficher les messages de log mysql depuis le boot
journalctl -b | grep mysqld

# afficher les messages en temps réel
journalctl -f
# et redémarrer mysqld
sudo systemctl restart mysqld
Mysql.svg
-- supprime tous les logs sauf ces des 3 derniers jours
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

Gérer la taille des logs

/etc/mysql/my.cnf
# limite la taille des fichiers /var/lib/mysql/mysql-bin.xxxxxx
expire_logs_days = 10
max_binlog_size  = 100M

# fichiers /var/lib/mysql/ib_logfile0 et /var/lib/mysql/ib_logfile1
innodb_log_file_size = 64M
# innodb_log_file_size ≈ 25 % of buffer pool size
# innodb_buffer_pool_size ≈ 50 - 80 % of RAM

# désactive le binary logging en commentant les lignes suivantes
# attention plus  de replication ni de data recovery possible
#log-bin=mysql-bin
#binlog_format=mixed

Audit

Permet de loguer toutes les requêtes.

Mysql.svg
-- afficher les variables de log
SHOW VARIABLES LIKE "general_log%";

-- définir le fichier de log l'audit
SET GLOBAL general_log_file = '/var/run/mysqld/mysqld.log';

-- démarrer l'audit
SET GLOBAL general_log = 'ON';
-- arrêter l'audit
SET GLOBAL general_log = 'OFF';

Install

Bash.svg
sudo pacman -S mariadb

# You need to initialize the MariaDB data directory prior to starting the service.
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

# démarrer mysql pour test
sudo systemctl start mysqld

# démarrer mysql au démarrage du système
sudo systemctl enable mysqld

# change le mot de passe root, autorise ou non l'accès distant avec le compte root,
# supprime ou pas la base de données de test et l'utilisateur anonyme créé par défaut. 
# Recommandé pour un serveur de production.
mysql_secure_installation

Maintenance

Bash.svg
# vérifie tous les table d'une base de données InnoDB
mysqlcheck -u root -p --check --databases [nom de la base de données]

Erreurs

Specified key was too long; max key length is 767 bytes

Les indexes InnoDB ont une taille maximale de 767 octets.

Encoding Nombre d'octets par caractère Exemple
latin1 1 varchar(255) → 255 octets
utf8 3 varchar(255) → 765 octets
utf8mb4 4 varchar(191) → 764 octets

Error: 145 Table is marked as crashed and should be repaired

Mysql.svg
# vérifie Ma_Table
check table Ma_Table;

# répare Ma_Table
repair table Ma_Table;

REPAIR TABLE

Bash.svg
# check toutes les tables d'une base de données
mysqlcheck MaBaseDeDonnées -u Utilisateur -pMotDePasse

mysqlcheck

1 client is using or hasn't closed the table properly

Bash.svg
# vérifie toute les tables de Ma_Base_de_Donnée et liste celles qui posent problème
myisamchk --silent --fast /var/lib/mysql/Ma_Base_de_Donnée/*.MYI
# --silent → Only print errors.
# --fast → Check only tables that haven’t been closed properly.

# check all MyISAM tables and repair any that are corrupted
myisamchk --silent --force --fast --update-state \
    --key_buffer_size=64M --sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M /var/lib/mysql/Ma_Base_de_Donnée/*.MYI
La commande myisamchk doit être lancée avec l'utilisateur root afin d'avoir accès au contenu du dossier /var/lib/mysql

myisamchk

MySQL Workbench

MySQL Workbench : design de BdD

PK Primary Key
NN Not Null
BIN Binary (stores data as binary strings. There is no character set so sorting and comparison is based on the numeric values of the bytes in the values.)
UN Unsigned (non-negative numbers only. so if the range is -500 to 500, instead its 0 - 1000, the range is the same but it starts at 0)
UQ Create/remove Unique Key
ZF Zero-Filled (if the length is 5 like INT(5) then every field is filled with 0’s to the 5th value. 12 = 00012, 400 = 00400, etc. )
AI Auto Increment
G Generated column. i.e. value generated by a formula based on the other columns

Convertir une table de MyISAM à InnoDB

Mysql.svg
alter table ma_table engine=InnoDB;

-- génération du script pour migrer toutes les tables d'une base de données
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = 'ma_database'
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name;