Version du 29 août 2023 à 14:52

Links

Release notes

Serveur

# Connection with unix_socket authentication
sudo mysql

# Connection with user and password
mysql -u root -p

Dump

sudo mariadb-dump -x -e -B db1 -r /var/mariadb/dump/db1.sql
# -x / --lock-tables lock all of the tables before performing the backup
# -e bundle INSERT statements together for each table
# -A / --all-databases toutes les bases de données (même les bases système)
# -B / --databases db1 db2 dump the db1 and db2 only
# -r file path
# > file path
# -u user
# -p prompt password / -p'password'

# store the password into this variable, so you don't have to pass it to the command line
export MYSQL_PWD=mypwd

Si le mot de passe contient un des caractères suivant * ? [ < > & ; ! | $ ( ) il faut l'encadrer de quotes.

~/.my.cnf

# set username and password here, so you don't have to pass them to the command line
[mysqldump]
user=myuser
password=mypwd

Making Backups with mariadb-dump

Import

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

sudo mysql << EOF
    use 'MyDb';
    truncate MyTable;
    source MyFile.sql;
EOF

# déjà connecté
use 'base-de-données';
source fichier.sql;

Executer une commande

mysql -h [ip] -D [database name] -u [user] -p[password] -e "commande mysql" -vvv
# -vvv pour afficher la sortie mysql dans le terminal

mysql -u [user] -p[password] << EOF
commandes mysql
EOF

# il faut mettre le mot de passe entre simple quote s'il contient les caractères suivants : * ? [ < > & ; ! | $ ( )
mysql -u [user] -p'[password]'

Executer un fichier

cd /ou/se/trouve/mon/fichier
mysql -u utilisateur -pmot_de_passe -D base_de_données < mon_fichier.sql

# si on est déjà connecté au serveur MySql
use 'base-de-données';
source mon_fichier.sql

Create database and tables

Database

# 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;

Encoding

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

-- afficher l'encoding et la collation d'une bdd
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata 
WHERE schema_name = "db_name";

Size

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

Tables

# 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;

# list the columns of the specified table
show columns from MyTable;

Create

create table if not exists user (
    id tinyint unsigned auto_increment primary key, -- PK
    name varchar(10) not null unique,
    groupId tinyint unsigned not null references group (Id), --FK
    computedColumn int as (GroupId * 5) persistant,

    index ux_user_name (name),         -- UX

    constraint fk_user_group_groupId   -- another way to create a named FK: FK_<current table>_<foreign table>_<column>
        foreign key (groupId)          -- column of the current table
        references group (id)),        -- foreign table and its column

    constraint uc_user_name UNIQUE (name),  -- another way to create a name UC

    primary key (Id);                  -- another way to create a PK

Drop

drop table my_table;

# 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;

Alter

# 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`;

# modify my_column to be datetime not null
alter table `my_table`
modify `my_column` datetime not null;

# supprimer « ma_colonne » dans « ma_table »
alter table `ma_table`
drop `ma_colonne`;

Comptes utilisateur

# 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;

# grant read / write privileges on MyDb to MyUser
grant select,insert,update on MyDb.* to 'MyUser'@'localhost';
# 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.

Table Privileges

Reset password without access

sc-stop mysql

# set options to run without granting the tables and networking check
sudo systemctl set-environment MYSQLD_OPTS="--skip-networking --skip-grant-tables"

sc-start mysql

# connect without password
sudo mysql -u root
mysql

flush privileges;

# reset password
set password for 'mon_compte'@'localhost' = password('...');

flush privileges;
exit;

# unset options
sudo systemctl unset-environment MYSQLD_OPTS

# remove the modified system configuration
sudo systemctl revert mysql

sudo killall -u mysql
sudo systemctl restart mysql.service

# login
mysql -u mon_compte -p

Account with empty username may mask the user you use to login.
You should drop all the account with empty user names.

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	Range
DATE	'0000-00-00'	'1000-01-01' to '9999-12-31'
TIME	'00:00:00'
DATETIME	'0000-00-00 00:00:00'	'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP	'0000-00-00 00:00:00'	'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
YEAR	0000

Date and Time Functions

set @today = NOW();
set @first_day = DATE_FORMAT(@today, '%Y-%m-01');
set @last_day = LAST_DAY(@today);
set @previous_month = @today - INTERVAL 1 MONTH;

set @diff_in_days = DATEDIFF(@date1, @date2); -- compare only date part
set @diff_in_days = TIMESTAMPDIFF(DAY, @date2, @date1); -- compare the whole datetime 
set @formatted_date = DATE_FORMAT(@date1, '%e %b %Y %H:%i'); -- 1 Jan 2021 23:59

Enum

create table if not exists table1 (
  type Enum('buy', 'sell') not null -- 0='', 1=buy, 2=sell
);

select *
from table1
where type = 2; -- sell

Ip address

create table iptable (
    ip binary(4) not null
);

insert into iptable (ip)
values (inet6_aton('127.0.0.1'));

select inet6_ntoa(ip)
FROM iptable;

Charset et Collation

-- 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;

-- afficher la collation de toutes les colonnes d'une table
SHOW FULL COLUMNS FROM <table_name>;

-- 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;

Ajouter / modifier des données

Insert

insert into my_table (column1, column2)
values ('value1-1', 'value1-2'),
       ('value2-1', 'value2-2');

insert into table1 (column1, column2)
select
  column3,
  (
    select id
    from table3
    where name = 'xxx'
  ) as table3_id
from table2;

-- set the last inserted id in the @id variable
SET @id = LAST_INSERT_ID();

Update

update my_table
set column1 = 10,
    column2 = column2 + 1
where column3 = 0;

update my_table as dest,
(select Column1, Column2
from AnotherTable) as src
set dest.Column1 = src.Column1
where dest.Column2 = src.Column2

Delete

delete from my_table
where column1 = 'value1';

truncate table my_table;
-- table keyword is optional

Trigger

-- when rows are inserted in MyTable, update AnotherTable
CREATE TRIGGER MyTrigger AFTER INSERT ON MyTable
FOR EACH ROW
UPDATE AnotherTable
SET Column1 = NEW.Column1
WHERE Column2 = NEW.Column2;
-- NEW target the inserted row

drop trigger MyTrigger;

show triggers
like 'MyTable'; -- table names (not trigger names) to match

Requêtes sur les données

Limiter le nombre de résultats

-- retourne les 10 premières lignes
select colonne from ma_table
limit 10

join

Visual Representation of SQL Joins

Multi select

select
    'Value1',
    (
        select count(*) from table1
    ) as count_table1;

With rollup

Over / Window function

SELECT year, country, product, profit,
       SUM(profit) OVER() AS total_profit, -- sum of all profits for all the countries
       SUM(profit) OVER(PARTITION BY country) AS country_profit -- sum of profits by country
FROM sales
ORDER BY country, year, product, profit;

Where and alias

select Column1 as c1
from MyTable
where c1 > 10
-- Error Code: 1054. Unknown column 'c1' in 'where clause'

-- use having instead of where
select Column1 as c1
from MyTable
having c1 > 10

	WHERE is applied before GROUP BY, HAVING is applied after.

Common Table Expressions CTE

WITH cte AS
(
    SELECT a
    FROM table1
    WHERE b >= 'c'
) 
SELECT *
FROM table2, cte
WHERE table2.c = cte.a;

Temp table

Temporary tables are session specific, they are dropped automatically when the connection is closed.

DROP TEMPORARY TABLE IF EXISTS temp1;

CREATE TEMPORARY TABLE temp1 (
   column1 VARCHAR(255),
   column2 INT
);

CREATE TEMPORARY TABLE temp2
SELECT * FROM table2
LIMIT 0;

INSERT INTO temp1
SELECT 'Value1', 1;

DROP TEMPORARY TABLE temp1;

Configuration

# 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

innodb_buffer_pool_size

-- give how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment
-- run it after 1 week or 2 of usage
SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

Variables

show global status;
show variables;

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

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

Log

# 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

-- 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    # default 0
max_binlog_size  = 100M  # default 1G

# 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.

-- 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

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
sc-start 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.
sudo mysql_secure_installation

Maintenance

# vérifie toutes 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

# vérifie Ma_Table
check table Ma_Table;

# répare Ma_Table
repair table Ma_Table;

REPAIR TABLE

# 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

# 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

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;

Features by version

Version	Features
10.6	Implement SQL-standard SELECT ... OFFSET ... FETCH
10.2	CTE (10.2.1)

« MariaDB » : différence entre les versions

Version du 29 août 2023 à 14:52

Links

Serveur

Import

Executer une commande

Create database and tables

Database

Tables

Create

Drop

Alter

Comptes utilisateur

Charset et Collation

Ajouter / modifier des données

Delete

Requêtes sur les données

Limiter le nombre de résultats

Multi select

Configuration

Variables

Log

Audit

Erreurs

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

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

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

MySQL Workbench

Features by version

Menu de navigation

Rechercher

« MariaDB » : différence entre les versions