MySQL

Serveur

# 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 en unix_socket authentication avec l'utilisateur root sudo mysql # 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 bdd1 bdd2 # -h hôte # --all-databases toutes les bases de données (même les bases système) # > fichier.sql au lieu de -rfichier.sql # 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]'

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;

Renommer ou copier une base de données avec PhpMyAdmin

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;

Table

# 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

Columns with a primary key constraint are implicitly not null and have an index.

• index
• foreign key
• generated column
• Storage engines

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

-- add a new column and its associated foreign key alter table `table1` add `table2_id` smallint unsigned not null default 1 references `table2` (`id`) on delete CASCADE after `column3`; # modify my_column to be datetime not null alter table `my_table` modify `my_column` datetime not null; -- remove a column and its associated foreign key alter table `table1` drop column `table2_id`, drop foreign key `table2_ibfk_1`;

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

Type	Size
VARCHAR	max 65'536 (2^16)
TEXT	65'536 (2^16)
MEDIUMTEXT	16'777'216 (2^24)
LONGTEXT	4'294'967'296 (2^32)
BLOB	65 MB

65'536 bytes = 65 MB = 21'844 utf8 characters

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;

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

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.

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

• max_binlog_size
• expire_logs_days

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;