Links
Serveur
|
# Connection with unix_socket authentication
sudo mysql
# Connection with user and password
mysql -u root -p
|
|
|
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
|
|
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]'
|
|
|
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;
# create db1
create database 'db1' character set 'utf8mb4' collate 'utf8mb4_general_ci';
# create db1 if it doesn't exist yet
create database if not exists db1;
# 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;
|
|
-- migrer la db vers un encoding utf8
ALTER DATABASE `db1`
CHARACTER SET utf8mb4
COLLATE utf8mb4_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";
|
|
|
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
character set 'utf8mb4' collate 'utf8mb4_general_ci';
|
 |
Columns with a primary key constraint are implicitly not null and have an index. |
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;
# rename the table
rename table `old_name`
to `new_name`;
# rename column
alter table `my_table`
rename column `current_name` to `new_name`;
# supprimer « ma_colonne » dans « ma_table »
alter table `ma_table`
drop `ma_colonne`;
# add / remove a unique constraint
alter table `my_table`
add constraint `uc_my_table_my_column` UNIQUE (`my_column`);
alter table `my_table`
drop constraint `uc_my_table_my_column`;
|
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. |
|
|
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. |
| 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
|
|
|
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
|
|
|
create table if not exists table1 (
type Enum('buy', 'sell') not null -- 0='', 1=buy, 2=sell
);
select *
from table1
where type = 2; -- sell
|
|
|
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_general_ci;
-- changer le charset et la collation d'une table
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- changer le charset et la collation d'une colonne
ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
Ajouter / modifier des données
|
|
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;
-- insert if not exists
insert into table1 (column1, column2)
select 'value1', 'value2'
where not exists
(
select * from table1
where column1 = 'value1'
);
-- set the last inserted id in the @id variable
SET @id = LAST_INSERT_ID();
|
|
|
update my_table
set column1 = 10,
column2 = column2 + 1
where column3 = 0;
-- update some rows only with values from another table and filter with a query on another table
update table1 as dest,
(
select column1, column2
from table2
) as src
set dest.column1 = src.column1
where dest.column2 = src.column2;
-- update some rows only, filter with a join to another table
update table1
join
(
select t1.id
from table1 as t1
join table2 as t2
on t2.id = t1.id
where t2.column1 = 'value'
group by t1.id
) as j1
on j1.id = table1.id
set columnX = 'valueX';
|
Delete
|
|
delete from my_table
where column1 = 'value1';
truncate table my_table;
-- table keyword is optional
|
|
|
-- 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
|
Multi select
|
|
select
'Value1',
(
select count(*) from table1
) as count_table1;
|
|
|
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;
|
|
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) |
|
|
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. |
|
|
WITH cte AS
(
SELECT a
FROM table1
WHERE b >= 'c'
)
SELECT *
FROM table2, cte
WHERE table2.c = cte.a;
|
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
|
| /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
|
|
|
-- 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 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;
|
| /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';
|
|
|
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
|
|
|
# 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
|
|
|
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
Allow a connection with the current Windows user name. Equivalent to the UNIX SOCKET.
|
|
# 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
|
|
# connection to the database
mariadb --protocol=PIPE
# the current Windows user is used and the default pipe name MySQL
|
|
REM get the current user
echo %USERNAME%
|
|
Connection String: "server=localhost;database=MyDb;user=WindowsUser;protocol=pipe" |
Features by version
| Version
|
Features
|
| 10.6 |
- Implement SQL-standard SELECT ... OFFSET ... FETCH
|
| 10.2 |
|
