MariaDB

De Banane Atomic
Aller à la navigationAller à la recherche

Links

Serveur

Bash.svg 
# Connection with unix_socket authentication
sudo mysql

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

Dump

Bash.svg 
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

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

sudo mysql << EOF
    use 'MyDb';
    truncate MyTable;
    source MyFile.sql;
EOF
Mysql.svg 
# déjà connecté
use 'base-de-données';
source fichier.sql;

Executer une commande

Bash.svg 
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

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

Create database and tables

Database

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

Encoding

Mysql.svg 
-- 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

Mysql.svg 
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

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;

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

Create

Mysql.svg 
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
Columns with a primary key constraint are implicitly not null and have an index.

Drop

Mysql.svg 
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

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

# 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

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;

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

Reset password without access

Bash.svg 
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
Mysql.svg 
flush privileges;

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

flush privileges;
exit;
Bash.svg 
# 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

Mysql.svg 
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

Mysql.svg 
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

Mysql.svg 
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

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;

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

Mysql.svg 
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

Mysql.svg 
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

Mysql.svg 
delete from my_table
where column1 = 'value1';

truncate table my_table;
-- table keyword is optional

Trigger

Mysql.svg 
-- 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

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

join

Visual sql joins.jpg

Multi select

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

With rollup

Over / Window function

Mysql.svg 
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

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause.
This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. (doc)
Mysql.svg 
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

Mariadb.svg 
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.

Mariadb.svg 
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

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

innodb_buffer_pool_size

Mysql.svg 
-- 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

Mysql.svg 
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

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

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

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

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;

Windows

  • C:\Program Files\MariaDB 11.3\data\my.ini

Named pipe authentication

Allow a connection with the current Windows user name. Equivalent to the UNIX SOCKET.

Mariadb.svg 
# install the plugin
INSTALL SONAME 'auth_named_pipe';

# check if named pipe authentication is enabled
SHOW GLOBAL VARIABLES LIKE 'named_pipe';

# create a new user which will identified himself with the named pipe
CREATE USER [WindowsUser]@localhost IDENTIFIED VIA named_pipe;
C:\Program Files\MariaDB 11.3\data\my.ini 
[mysqld]
# enable name pipe with pipe named MARIADB
enable-named-pipe
socket=MySQL
Ps.svg 
# connection to the database
mariadb --protocol=PIPE
# the current Windows user is used and the default pipe name MySQL
Dos.svg 
REM get the current user
echo %USERNAME%
Connection String: {{{1}}}

Features by version

Version Features
10.6
  • Implement SQL-standard SELECT ... OFFSET ... FETCH
10.2
Récupérée de « http://wiki.bananeatomic.fr/index.php?title=MariaDB&oldid=29709 »