Mysql Les bases
But de l’article
Nous allons voir dans cet article les bases pour se déplacer dans les méandres d’une base de données Mysql.
Pour cet article nous travaillerons sur une base de données hébergeant un site wordpress.
Se connecter à MySql
Quelques règles de sécurité
Régles de base pour la connection :
- Les utilisateurs doivent avoir un mot de passe avec :
- Majuscule
- Minuscule
- Caractère spéciaux
- On ne tape jamais en clair un Mot de passe dans la console
- je conseil de créer un alias pour les connections mysql avec un mot de passe afin d’avoir un history vierge de tout mot de passe
En local
vi .bashrc
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# User specific environment
if ! [[ "$PATH" =~ "$HOME/.local/bin:$HOME/bin:" ]]
then
PATH="$HOME/.local/bin:$HOME/bin:$PATH"
fi
export PATH
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
alias dbconect='mysql -u <User> -p <Password>'
alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'
~
A distance
vi .bashrc
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# User specific environment
if ! [[ "$PATH" =~ "$HOME/.local/bin:$HOME/bin:" ]]
then
PATH="$HOME/.local/bin:$HOME/bin:$PATH"
fi
export PATH
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
alias dbconect='mysql -h <hostname> -u <User> -p <Password>'
alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'En tapant dbconnect dans la console, l’authentification à mysql sera automatique et transparente dans l’historique
Connection manuelle
En local
mysql -u <Utilisateur> -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 96981
Server version: 10.5.29-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]>A distance
mysql -h <Serveur distant> -u <Utilisateur> -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 96981
Server version: 10.5.29-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]>Environnement
Lister les variables
MariaDB [(none)]> SHOW GLOBAL VARIABLES;
+----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| allow_suspicious_udfs | OFF |
| alter_algorithm | DEFAULT |
| analyze_max_length | 4294967295…
|
| wsrep_trx_fragment_size | 0 |
| wsrep_trx_fragment_unit | bytes |
+----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
655 rows in set (0,001 sec)Affichage amélioré
Le G en fin de ligne permet de transposer chaque ligne de la table en ligne par champs.
MariaDB [(none)]> SHOW GLOBAL VARIABLESG;
*************************** 1. row ***************************
Variable_name: allow_suspicious_udfs
Value: OFF
*************************** 2. row ***************************
Variable_name: alter_algorithm
Value: DEFAULT
*************************** 3. row ***************************
Variable_name: analyze_max_length
Value: 4294967295
*************************** 4. row ***************************
Variable_name: analyze_sample_percentage
Value: 100.000000
*************************** 5. row ***************************
Variable_name: aria_block_size
Value: 8192…
*************************** 654. row ***************************
Variable_name: wsrep_trx_fragment_size
Value: 0
*************************** 655. row ***************************
Variable_name: wsrep_trx_fragment_unit
Value: bytes
655 rows in set (0,001 sec)Rechercher la valeur affectée à une variable
SHOW GLOBAL VARIABLES LIKE 'Nom Variable';Par exemple :
SHOW GLOBAL VARIABLES LIKE 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0,001 sec)Les databases
Lister les databases
Show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wordpress |
| wordpress_old |
+--------------------+
5 rows in set (0,001 sec)Se connecter à une databases
Use <nom database> ;Par exemple :
use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]>Les tables
Lister les tables
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| global_priv |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0,000 sec)Information sur la table
MariaDB [mysql]> desc user;
+------------------------+---------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host | char(60) | NO | | | |
| User | char(80) | NO | | | |
| Password | longtext | YES | | NULL | |
| Select_priv | varchar(1) | YES | | NULL | |
| Insert_priv | varchar(1) | YES | | NULL | |
| Update_priv | varchar(1) | YES | | NULL | |
| Delete_priv | varchar(1) | YES | | NULL | |
| Create_priv | varchar(1) | YES | | NULL | |
| Drop_priv | varchar(1) | YES | | NULL | |
| Reload_priv | varchar(1) | YES | | NULL | |
| Shutdown_priv | varchar(1) | YES | | NULL | |
| Process_priv | varchar(1) | YES | | NULL | |
| File_priv | varchar(1) | YES | | NULL | |
| Grant_priv | varchar(1) | YES | | NULL | |
| References_priv | varchar(1) | YES | | NULL | |
| Index_priv | varchar(1) | YES | | NULL | |
| Alter_priv | varchar(1) | YES | | NULL | |
| Show_db_priv | varchar(1) | YES | | NULL | |
| Super_priv | varchar(1) | YES | | NULL | |
| Create_tmp_table_priv | varchar(1) | YES | | NULL | |
| Lock_tables_priv | varchar(1) | YES | | NULL | |
| Execute_priv | varchar(1) | YES | | NULL | |
| Repl_slave_priv | varchar(1) | YES | | NULL | |
| Repl_client_priv | varchar(1) | YES | | NULL | |
| Create_view_priv | varchar(1) | YES | | NULL | |
| Show_view_priv | varchar(1) | YES | | NULL | |
| Create_routine_priv | varchar(1) | YES | | NULL | |
| Alter_routine_priv | varchar(1) | YES | | NULL | |
| Create_user_priv | varchar(1) | YES | | NULL | |
| Event_priv | varchar(1) | YES | | NULL | |
| Trigger_priv | varchar(1) | YES | | NULL | |
| Create_tablespace_priv | varchar(1) | YES | | NULL | |
| Delete_history_priv | varchar(1) | YES | | NULL | |
| ssl_type | varchar(9) | YES | | NULL | |
| ssl_cipher | longtext | NO | | | |
| x509_issuer | longtext | NO | | | |
| x509_subject | longtext | NO | | | |
| max_questions | bigint(20) unsigned | NO | | 0 | |
| max_updates | bigint(20) unsigned | NO | | 0 | |
| max_connections | bigint(20) unsigned | NO | | 0 | |
| max_user_connections | bigint(21) | NO | | 0 | |
| plugin | longtext | NO | | | |
| authentication_string | longtext | NO | | | |
| password_expired | varchar(1) | NO | | | |
| is_role | varchar(1) | YES | | NULL | |
| default_role | longtext | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+---------------------+------+-----+----------+-------+
47 rows in set (0,002 sec)Gestion des Utilisateurs
Lister utilisateurs
Les informations importantes
select User,Host,Password,password_expired from user;
+---------------+-----------+-------------------------------------------+------------------+
| User | Host | Password | password_expired |
+---------------+-----------+-------------------------------------------+------------------+
| mariadb.sys | localhost | | Y |
| root | localhost | invalid | N |
| mysql | localhost | invalid | N |
| wordpressuser | localhost | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | N |
| calou | % | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | N |
| admin | % | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | N |
+---------------+-----------+-------------------------------------------+------------------+
6 rows in set (0,004 sec)Les droits
Pour lister des droits de l’utilisateur admin :
SHOW GRANTS FOR 'admin'@'%';
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@% |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `admin`@`%` IDENTIFIED BY PASSWORD 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------+Et mon utilisateur :
SHOW GRANTS FOR 'wordpressuser'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for wordpressuser@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `wordpressuser`@`localhost` IDENTIFIED BY PASSWORD 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' |
| GRANT ALL PRIVILEGES ON `wordpress`.* TO `wordpressuser`@`localhost` |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0,000 sec)Compréhension du retour des 2 commandes
- Mon utilisateur admin à tous les droits sur toutes les databases
- Mon utilisateur wordpressuser à tous les droits uniquement sur la database wordpress
Gestion des utilisateurs
Création
CREATE USER ‘<Utilisateur>’@’Host’ IDENTIFIED BY ‘<Mot de passe>’;Affectation de droits
- Droits admins
- Droits restreints
Changer de mot de passe
ALTER USER ‘<Utilisateur>’@’Host’IDENTIFIED BY ‘<Nouveau mot de passe>’Application des modifications
FLUSH PRIVILEGES;