MySQL est un système de base de données relationnelle open source qui fonctionne sur de nombreux systèmes d'exploitation, notamment Windows, Linux, macOS et FreeBSD. C'est probablement le SGBDR OpenSource le plus populaire et un composant central des piles LAMP et LEMP. De nombreuses applications dépendent de MySQL comme WordPress, Joomla, TYPO3, phpBB et bien d'autres.
Plusieurs nouvelles fonctionnalités ont été ajoutées à MySQL 8, notamment la prise en charge de JSON, le dictionnaire de données transactionnelles, la configuration d'exécution persistante, le magasin de documents, les conseils d'optimisation, les rôles SQL, les CTE, les fonctions de fenêtre, les index invisibles et bien d'autres.
Qu'allez-vous apprendre ?
Dans ce didacticiel, vous apprendrez des choses liées au serveur MySQL, et ci-dessous, vous trouverez des détails sur :
- Installer MySQL Server 8 sur Debian Buster 10
- Configurer le mot de passe racine MySQL
- Déploiement sécurisé du serveur MySQL (modification de l'adresse de liaison, du port, etc.)
- Configurer SSL et activer la connexion à distance pour le serveur MySQL
- Installer et configurer phpMyAdmin
- Requête MySQL de base pour créer un utilisateur, une base de données, etc.
- Configurer des bases de données MySQL de sauvegarde automatique
Prérequis
Nous installerons le serveur MySQL 8.04 sur la dernière Debian Buster 10 avec 2 Go de RAM et 4 processeurs pour ce guide. De plus, nous avons besoin du privilège root pour installer les packages et modifier les configurations système.
Étape 1 - Ajouter un référentiel MySQL
Pour ce tutoriel, nous allons installer MySQL Server 8 à partir du référentiel MySQL officiel. Pour ce faire, nous devons ajouter le référentiel MySQL officiel à notre serveur Debian.
Avant d'ajouter le référentiel MySQL, installez le package "GnuPG" à l'aide de la commande apt ci-dessous.
sudo apt install gnupg -y
Une fois l'installation terminée, téléchargez le référentiel MySQL APT et ajoutez-le à votre système.
wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb
Laissez le référentiel par défaut et sélectionnez 'OK '.
Et le référentiel MySQL officiel a été ajouté.
Ensuite, mettez à jour tous les référentiels disponibles sur le système Debian à l'aide de la commande suivante.
sudo apt update
Et vous obtiendrez le référentiel MySQL officiel dans la liste ci-dessous.
Comme on peut le voir, le référentiel MySQL officiel a été ajouté et nous sommes prêts à installer MySQL Server 8.0 sur Debian Buster 10.
Étape 2 - Installer le serveur MySQL
Pour installer MySQL Server 8.0 sur le système Debian, exécutez la commande apt ci-dessous.
sudo apt install mysql-server
Tapez maintenant le mot de passe root pour votre installation MySQL et choisissez 'OK '.
Répétez le mot de passe pour votre utilisateur racine MySQL.
Après cela, il vous sera demandé de configurer le plugin d'authentification MySQL par défaut. Sélectionnez le 'Cryptage fort du mot de passe ' comme recommandé et choisissez 'OK '.
Et l'installation de MySQL Server 8.0 est terminée.
Vérifiez maintenant le service MySQL à l'aide de la commande suivante.
systemctl is-enabled mysql
systemctl status mysql
Vous trouverez ci-dessous le résultat que vous obtiendrez.
En conséquence, le service MySQL est opérationnel sur le serveur Debian. Le service s'exécutera automatiquement à chaque démarrage du système.
Étape 3 - Déploiement sécurisé de MySQL à l'aide de mysql_secure_password
Dans cette étape, nous allons améliorer la sécurité de l'installation du serveur MySQL en utilisant le 'mysql_secure_installation ' outil de ligne de commande.
Le 'mysql_secure_installation ' vous aidera à améliorer votre déploiement MySQL sous différents aspects, tels que la configuration de la force du mot de passe, la désactivation de l'accès à distance et la suppression des utilisateurs anonymes par défaut et des bases de données de test.
Exécutez maintenant le 'mysql_secure_installation ' sur votre shell.
mysql_secure_installation
Au début de l'installation de MySQL, vous avez déjà configuré le mot de passe root pour MySQL. Tapez maintenant le mot de passe pour continuer le processus.
Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD
Après cela, tapez 'Y ' pour configurer le 'VALIDER LE MOT DE PASSE ' composant.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of the password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: Y
Choisissez maintenant le niveau de politique de validation du mot de passe pour tous les utilisateurs. Tapez '2 ' pour configurer le 'STRONG ' politique de niveau de mot de passe.
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters, and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Using existing password for root.
Maintenant, la commande 'mysql_secure_installation' vérifiera la force de votre mot de passe actuel. Comme on peut le voir, la force de notre mot de passe actuel est '50 '. Tapez maintenant 'Y ' pour changer le mot de passe actuel avec un nouveau mot de passe plus fort.
Estimated strength of the password: 50
Change the password for root? ((Press y|Y for Yes, any other key for No) : Y
Tapez maintenant votre mot de passe fort et assurez-vous d'avoir le score 100 , puis tapez 'O ' pour continuer avec le nouveau mot de passe.
New password:
Re-enter new password:
Estimated strength of the password: 100
Do you wish to continue with the password provided? (Press y|Y for Yes, any other key for No) : Y
En conséquence, le composant MySQL "VALIDATE PASSWORD" a été configuré avec une stratégie de mot de passe fort et le nouveau mot de passe MySQL a été créé.
Ensuite, il vous sera demandé de supprimer l'utilisateur anonyme par défaut. Tapez 'O ' pour supprimer l'utilisateur anonyme par défaut de votre installation.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without creating a user account. This is intended only for testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Ensuite, tapez 'Y ' à nouveau pour désactiver la connexion à distance MySQL et si vous souhaitez que le serveur MySQL soit accessible uniquement localement.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Après cela, supprimez la base de données par défaut "test" de votre serveur MySQL. Tapez 'O ' et appuyez sur Entrée pour continuer.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
Ensuite, tapez 'Y' à nouveau pour recharger les tables de privilèges. En effet, et de nouvelles modifications de configuration MySQL ont été appliquées.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
En conséquence, le déploiement sécurisé du serveur MySQL à l'aide du script 'mysql_secure_installation' est terminé. Et à ce stade, vous avez activé le composant 'VALIDATE PASSWORD' avec la politique de mot de passe fort de niveau '2', le nouveau mot de passe root fort pour MySQL a également été configuré.
Étape 4 - Sécuriser le serveur MySQL à l'aide d'une configuration supplémentaire
Pour ce tutoriel, nous allons ajouter une configuration supplémentaire pour améliorer le déploiement du serveur MySQL. Nous spécifierons l'adresse de liaison du serveur MySQL, modifierons le port par défaut, désactiverons le chargement du fichier de données et mettrons en place une politique d'expiration du mot de passe pour tous les utilisateurs MySQL.
Allez maintenant dans le répertoire '/etc/mysql/conf.d/' et créez une nouvelle configuration 'mysqld.cnf' en utilisant l'éditeur vim.
cd /etc/mysql/conf.d/
vim mysqld.cnf
Collez-y la configuration suivante.
[mysqld]
# MySQL running on 127.0.0.1 with port 8320
bind-address=127.0.0.1
port=8320
# Disable LOAD DATA LOCA
local-infile=0
# Password Expiration to 60 Days
default_password_lifetime=60
# MySQLX Plugin running on 127.0.0.1 with port 8321
mysqlx_bind_address=127.0.0.1
mysqlx_port=8321
Enregistrez et fermez.
Ensuite, redémarrez le service MySQL et vérifiez les ports ouverts sur le système à l'aide de la commande suivante.
systemctl restart mysql
ss -plnt
Vous trouverez ci-dessous le résultat que vous obtiendrez.
Comme on peut le voir, le serveur MySQL est opérationnel sur l'adresse IP locale '127.0.0.1' sur le port TCP non par défaut '8320'.
Ensuite, pour vous connecter au shell MySQL avec le port personnalisé, vous pouvez utiliser la commande 'mysql' avec l'option '-P port' comme ci-dessous.
mysql -u root -P 8320 -p
Tapez le mot de passe root pour le serveur MySQL.
Une fois connecté, exécutez la requête suivante pour vérifier la configuration du port du serveur MySQL.
show variables like 'port';
Et vous obtiendrez que le serveur MySQL s'exécute sur le port personnalisé '8320' comme ci-dessous.
Par conséquent, vous avez configuré le serveur MySQL pour qu'il s'exécute sur l'adresse IP locale "127.0.0.1" avec le port TCP personnalisé "8320".
Étape 5 - Sécurisez et activez la connexion à distance MySQL à l'aide de SSL
Supposons que vous souhaitiez déployer le serveur MySQL sur le réseau interne et autoriser d'autres hôtes sur le même réseau à accéder aux bases de données sur le serveur MySQL. Dans ce cas, il est recommandé d'activer SSL pour votre déploiement de serveur MySQL.
Dans cette étape, vous apprendrez à configurer et à activer SSL et l'accès à distance sur le serveur MySQL.
Générer des certificats SSL
Par défaut, MySQL Server 8.0 génère automatiquement des certificats SSL lors de l'installation. Tous les certificats SSL par défaut sont disponibles dans le répertoire '/var/lib/mysql'.
Pour cette étape, nous allons générer un nouveau certificat SSL pour le serveur MySQL en utilisant le 'mysql_ssl_rsa_setup ' outil de ligne de commande.
Créez maintenant un nouveau répertoire SSL '/etc/mysql/certs' et changez la propriété de ce répertoire en l'utilisateur 'mysql'.
mkdir -p /etc/mysql/certs/
sudo chown -R mysql:mysql /etc/mysql/certs
Ensuite, générez de nouveaux certificats SSL pour le serveur MySQL à l'aide de la commande suivante. Et assurez-vous de changer le '--suffix=STRING ' avec votre nom commun.
sudo mysql_ssl_rsa_setup --suffix=HakasMySQLSrv --uid=mysql --datadir=/etc/mysql/certs/
Une fois terminé, de nouveaux certificats SSL pour le serveur MySQL ont été générés dans le répertoire '/etc/mysql/certs'.
Vérifiez vos certificats SSL à l'aide de la commande suivante.
ls -lah /etc/mysql/certs/
openssl verify -CAfile /etc/mysql/certs/ca.pem /etc/mysql/certs/server-cert.pem /etc/mysql/certs/client-cert.pem
Vous trouverez ci-dessous le résultat que vous obtiendrez.
Les nouveaux certificats SSL pour MySQL Server ont été générés dans le répertoire '/etc/mysql/certs/'.
Activer SSL et l'accès à distance au serveur MySQL
Pour activer le SSL et l'accès à distance sur le serveur MySQL, vous devrez modifier l'option 'bind-address' avec l'adresse IP de votre réseau interne et ajouter la configuration SSL pour MySQL.
Allez maintenant dans le répertoire '/etc/mysql/conf.d/' et modifiez la configuration que nous avons créée en haut de 'mysqld.cnf' à l'aide de l'éditeur vim.
cd /etc/mysql/conf.d/
vim mysqld.cnf
Remplacez 'bind-address' par votre adresse IP interne comme ci-dessous.
bind-address=172.16.0.3
Ensuite, ajoutez la configuration SSL pour le serveur MySQL ci-dessous et modifiez le chemin SSL avec le vôtre.
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
De plus, vous pouvez spécifier la version TLS de tous vos clients et les forcer à utiliser uniquement la connexion SSL sécurisée en utilisant la configuration suivante.
#require_secure_transport=ON
tls_version=TLSv1.1,TLSv1.2
Enregistrez et fermez.
Ensuite, redémarrez le service MySQL pour appliquer la nouvelle configuration et vérifiez l'adresse IP du serveur MySQL à l'aide de la commande suivante.
systemctl restart mysql
ss -plnt
Et vous obtiendrez le serveur MySQL opérationnel sur l'adresse IP du réseau interne avec SSL activé dessus.
Configurer le client et tester la connexion SSL
Pour vous connecter au serveur MySQL à partir d'un autre hôte/client, vous devez créer un nouvel utilisateur MySQL, copier les certificats du client et ajouter la configuration MySQL pour le client.
Connectez-vous au shell MySQL à l'aide de la commande suivante.
mysql -u root -P 8320 -p
Créez maintenant un nouvel utilisateur MySQL pour la connexion à distance en utilisant la requête suivante.
CREATE USER 'remoteuser'@'%' IDENTIFIED WITH mysql_native_password BY 'MyRemoteUserPasswordMySQL39==' REQUIRE SSL;
GRANT ALL PRIVILEGES ON *.* TO 'remoteuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Tapez 'quitter ' pour vous déconnecter du shell MySQL.
Ensuite, vous devez copier le certificat et la clé de l'autorité de certification et du client sur la machine cliente à l'aide de la commande SCP comme ci-dessous.
scp /etc/mysql/certs/ca.pem [email protected]:/certificates/directory/
scp /etc/mysql/certs/client-cert.pem [email protected]:/certificates/directory/
scp /etc/mysql/certs/client-key.pem [email protected]:/certificates/directory/
Après cela, connectez-vous à votre ordinateur client et créez une nouvelle configuration à l'aide de l'éditeur vim.
vim ~/.my.cnf
Collez la configuration du client MySQL comme ci-dessous et modifiez le chemin du certificat SSL du client avec le vôtre.
[client]
ssl-ca=/home/gpk/certs/ca.pem
ssl-cert=/home/gpk/certs/client-cert.pem
ssl-key=/home/gpk/certs/client-key.pem
Enregistrez et fermez.
Pour vous connecter au serveur MySQL distant avec un port personnalisé, exécutez la commande 'mysql' ci-dessous.
mysql -u remoteuser -h 172.16.0.3 -P 8320 -p
Une fois connecté, vérifiez l'état de votre connexion à l'aide de la requête suivante.
STATUS
SELECT * FROM performance_schema.session_status
WHERE VARIABLE_NAME IN ('Ssl_version','Ssl_cipher');
Vous trouverez ci-dessous le résultat que vous obtiendrez.
Comme on peut le voir, vous vous êtes connecté avec une connexion sécurisée SSL TLSv1.2 au serveur MySQL.
En conséquence, l'accès à distance avec une connexion SSL sécurisée pour MySQL Server a été activé.
Étape 6 - Installer et configurer phpMyAdmin
Dans cette étape, vous apprendrez à installer la dernière version de phpMyAdmin avec le serveur Web Apache. Vous apprendrez également à vous connecter au serveur MySQL sur l'hôte distant avec un port personnalisé et à vous connecter en toute sécurité via SSL à partir de phpMyAdmin.
Avant d'aller plus loin, installez certaines dépendances de packages à l'aide de la commande apt ci-dessous.
sudo apt install apache2 libapache2-mod-php php-imagick php-phpseclib php-php-gettext php-common php-mysql php-gd php-imap php-json php-curl php-zip php-xml php-mbstring php-bz2 php-intl php-gmp unzip
Ensuite, allez dans le répertoire '/var/www/html' et téléchargez le dernier code source de phpMyAdmin.
cd /var/www/html/
wget -q https://www.phpmyadmin.net/downloads/phpMyAdmin-latest-all-languages.tar.gz
Maintenant, extrayez le code source de phpMyAdmin et renommez-le dans le nouveau répertoire nommé 'phpMyAdmin'.
tar -xf phpMyAdmin-latest-all-languages.tar.gz
mv phpMyAdmin-5.0.2-all-languages phpMyAdmin
Ensuite, allez dans le répertoire '/var/www/phpMyAdmin' et copiez le fichier de configuration par défaut dans 'config.inc.php'.
cd /var/www/html/phpMyAdmin/
cp config.sample.inc.php config.inc.php
Modifiez le fichier de configuration 'config.inc.php' à l'aide de l'éditeur vim.
vim config.inc.php
Générez le code secret blowfish en utilisant ce lien et collez le code dans la ligne 'blowfish_secret' comme ci-dessous.
$cfg['blowfish_secret'] = '6}2]RFleN-JT=/vXD}L/0j0v.YALV/e3'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
Ensuite, saisissez les détails de votre serveur MySQL sous la ligne "Configuration du serveur" comme ci-dessous.
/**
* Servers configuration
*/
$i = 0;
.....
/* Custom MySQL Server Configuration */
$cfg['Servers'][$i]['host'] = '172.16.0.3';
$cfg['Servers'][$i]['port'] = '8320';
.....
Après cela, collez la configuration suivante pour activer la communication SSL de phpMyAdmin vers MySQL Server. Et assurez-vous de changer le chemin des certificats client et CA par le vôtre et assurez-vous que l'utilisateur "www-data" peut accéder aux certificats CA et client.
/* SSL Configuration */
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['ssl_key'] = '/opt/mysql/certs/client-key.pem';
$cfg['Servers'][$i]['ssl_cert'] = '/opt/mysql/certs/client-cert.pem';
$cfg['Servers'][$i]['ssl_ca'] = '/opt/mysql/certs/ca.pem';
/* Disable SSL Verification for Self-signed certificates */
/* The Good: Connection between phpMyAdmin and MySQL Server are Encrypted */
/* But you're still vulnerable with the MiTM */
$cfg['Servers'][$i]['ssl_verify'] = false;
/* Hide Some Databases */
$cfg['Servers'][$i]['hide_db'] = '(information_schema|performance_schema|sys|mysql)';
Enregistrez et fermez.
Changez maintenant la propriété du répertoire 'phpMyAdmin' en l'utilisateur 'www-data'.
sudo chown -R www-data:www-data /var/www/html/phpMyAdmin
Et l'installation et la configuration de phpMyAdmin de phpMyAdmin sont terminées.
Connectez-vous maintenant au serveur MySQL, vérifiez le statut MySQL SSL, créez une nouvelle base de données et un nouvel utilisateur pour phpMyAdmin.
Connectez-vous au serveur MySQL à l'aide de la commande suivante.
mysql -u root -P 8329 -p
Vérifiez maintenant le statut SSL à l'aide des requêtes suivantes.
SHOW VARIABLES LIKE '%ssl%';
STATUS
Vous trouverez ci-dessous le résultat que vous obtiendrez.
Comme on peut le voir, le serveur MySQL est opérationnel avec SSL activé, et il utilise les certificats SSL que nous avons générés. Nous sommes également connectés au serveur MySQL via la connexion sécurisée SSL en utilisant le fichier de configuration '~/.my.cnf'.
Ensuite, créez une nouvelle base de données et un utilisateur nommé 'phpmyadmin' en utilisant la requête suivante.
CREATE DATABASE phpmyadmin;
CREATE USER 'phpmyadmin'@'%' IDENTIFIED WITH mysql_native_password BY 'phpMyAdminAdmn352#$';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Tapez 'quitter ' pour vous déconnecter de MySQL Shell.
Ouvrez maintenant votre navigateur Web et tapez l'adresse IP du serveur avec le chemin de 'phpMyAdmin' dans la barre d'adresse.
http://server-ip/phpMyAdmin/
Sur la page de connexion de phpMyAdmin, tapez l'utilisateur 'phpmyadmin' avec le mot de passe que nous avons créé en haut et cliquez sur le bouton 'GO'.
Et vous obtiendrez le tableau de bord phpMyAdmin comme ci-dessous.
En conséquence, l'installation et la configuration de phpMyAdmin sont terminées.
Étape 7 - Créer un utilisateur et une base de données MySQL
Pour cette étape, vous apprendrez l'utilisation principale des requêtes MySQL pour gérer l'utilisateur, la base de données et la commande de base mysqldump pour la sauvegarde de la base de données.
Se connecter au serveur MySQL et vérifier l'état de la connexion
Pour vous connecter au serveur MySQL, vous pouvez utiliser la ligne de commande 'mysql'. Et par défaut, toutes les connexions depuis le serveur MySQL local utiliseront le socket Unix, que la connexion SSL ne sécurise pas davantage.
Connectez-vous au serveur MySQL avec la configuration par défaut à l'aide de la commande suivante.
mysql -u username -p
Si votre serveur MySQL s'exécute sur le port personnalisé, utilisez l'option '-P port' ci-dessous.
mysql -u root -P 8320 -p
Si votre serveur MySQL s'exécute sur le réseau local avec le port personnalisé, utilisez les options 'h host/IP Address' et '-P port' ci-dessous.
mysql -u root -h 172.16.0.3 -P 8320 -p
Une fois que vous êtes connecté à votre serveur MySQL, exécutez la requête suivante pour vérifier l'état de la connexion.
STATUS
Et vous obtiendrez le résultat suivant.
Et vous êtes connecté avec succès au serveur MySQL.
Créer et afficher une base de données
Après vous être connecté au shell MySQL, vous apprendrez à créer la base de données MySQL, à sélectionner la base de données, à créer des tables et à insérer des données.
Pour créer une nouvelle base de données MySQL, utilisez la requête suivante.
CREATE DATABASE hakase_db;
CREATE DATABASE yume_db;
CREATE DATABASE juice_db;
Vérifiez maintenant toutes les bases de données de liste sur votre serveur MySQL en utilisant la requête suivante.
SHOW DATABASES;
SHOW DATABASES LIKE "%db%";
Ensuite, vous pouvez basculer et sélectionner la base de données à l'aide de la requête suivante.
USE hakase_db;
Ensuite, vous pouvez créer une nouvelle table sur votre base de données en utilisant la requête 'CREATE TABLE' comme ci-dessous.
CREATE TABLE users (first VARCHAR(20), last VARCHAR(20),
email VARCHAR(20), birth DATE, country VARCHAR(20));
Vous pouvez maintenant utiliser 'DESCRIBE table-name' pour vérifier tous les champs de votre base de données.
DESCRIBE users;
Et vous obtiendrez tous les champs de détails sur la table 'utilisateurs'.
Ensuite, vous pouvez insérer des données manuellement dans votre table en utilisant la requête "INSERT" comme ci-dessous.
INSERT INTO users
VALUES ('Mark','Mine','[email protected]','1999-03-20','Canada');
Vous avez maintenant inséré avec succès des données dans la base de données MySQL. Ensuite, vérifiez toutes les données de la table à l'aide de la requête "SELECT" comme ci-dessous.
SELECT * FROM users;
SELECT email, birth, country FROM users;
Et vous obtiendrez toutes les données de votre table.
Créer un utilisateur MySQL
Après avoir créé la base de données, vous apprendrez à créer un utilisateur sur le serveur MySQL et à accorder des privilèges d'utilisateur aux bases de données.
La requête MySQL de base pour créer un utilisateur consiste à utiliser la requête "CREATE USER". Ensuite, vous pouvez accorder des privilèges de la base de données à votre utilisateur en utilisant la commande 'GRANT' suivie de ses options. Et le dernier, vous devez recharger les tables de subventions pour appliquer les nouvelles modifications.
- La base
Créez un nouvel utilisateur nommé '[email protected]' avec le mot de passe 'MyHakasePasswordMySQL39==' et accordez tous les privilèges de 'hakase_db' à l'utilisateur en utilisant la requête suivante.
CREATE USER 'hakase'@'localhost' IDENTIFIED BY 'MyHakasePasswordMySQL39==';
GRANT ALL PRIVILEGES ON hakase_db.* TO 'hakase'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Informations complémentaires, le nom d'hôte "[email protected]" fait partie du nom du compte. Le '@localhost' permet uniquement à l'utilisateur de se connecter à partir du serveur local. S'il n'est pas fourni, la valeur par défaut est '%', ce qui autorise les utilisateurs de partout.
- Créer un utilisateur pour l'accès à distance avec un plug-in d'authentification spécifique
Nous allons créer un nouvel utilisateur qui ne peut se connecter qu'à partir de l'adresse IP de l'hôte spécifique et utiliser le plug-in d'authentification particulier.
Nous allons créer un nouvel utilisateur nommé 'yume' se connectant via l'IP client '172.16.0.4' avec le plugin d'authentification 'mysql_native_password', et l'utilisateur sera autorisé à accéder à la base de données 'yume_db' uniquement.
CREATE USER 'yume'@'172.16.0.4' IDENTIFIED WITH mysql_native_password BY 'MyYumePasswordMySQL39==';
GRANT ALL PRIVILEGES ON yume_db.* TO 'yume'@'172.16.0.4' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Par conséquent, l'utilisateur 'yume' ne se connectera qu'au serveur MySQL avec l'adresse IP '172.16.0.4'. Sinon, l'authentification de l'utilisateur sera refusée par le serveur MySQL.
- Créer un utilisateur et forcer à utiliser une connexion SSL cryptée
Pour forcer l'utilisateur à utiliser la connexion sécurisée SSL, vous pouvez utiliser l'option 'REQUIRE SSL'. Si la connexion sécurisée échoue, vous ne pouvez pas vous connecter au serveur MySQL (impossible d'établir les connexions).
Pour un scénario plus sécurisé, vous pouvez utiliser la requête "REQUIRE X509", ce qui signifie que la connexion au serveur MySQL n'est établie que lorsque l'utilisateur/la machine cliente a fourni les certificats client et CA.
Créez un nouvel utilisateur nommé 'juice' avec le nom d'hôte '%'. Cette option de nom d'hôte '%' permet à l'utilisateur 'juice' de se connecter à partir de n'importe quelle adresse IP, mais la connexion sera établie lorsque la machine cliente fournira les certificats client et CA.
CREATE USER 'juice'@'%' IDENTIFIED WITH mysql_native_password BY 'MyJuicePasswordMySQL39==' REQUIRE X509;
GRANT ALL PRIVILEGES ON juice_db.* TO 'juice'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Par conséquent, l'utilisateur "juice" peut se connecter au serveur MySQL lorsque la machine a fourni des certificats client et CA.
Afficher tous les utilisateurs et subventions disponibles
Après avoir créé tous les utilisateurs, vérifiez tous les utilisateurs à l'aide des requêtes suivantes.
SELECT User, Host FROM mysql.user;
SELECT User, Host, plugin FROM mysql.user;
Vous obtiendrez maintenant les détails de l'utilisateur, de l'hôte et des plugins d'authentification utilisés par les utilisateurs.
Ensuite, vérifiez tous les privilèges des utilisateurs à l'aide de la requête "SHOW GRANTS" comme ci-dessous.
SHOW GRANTS FOR 'hakase'@'localhost';
SHOW GRANTS FOR 'yume'@'172.16.0.3';
SHOW GRANTS FOR 'juice'@'%';
Vous obtiendrez maintenant les privilèges détaillés de vos utilisateurs.
En conséquence, vous avez appris l'utilisation principale de MySQL Server, y compris la création d'une base de données, la création d'une table, l'insertion de données dans la table, la création et l'affichage d'utilisateurs et l'affichage des détails des privilèges de chaque utilisateur.
Étape 8 - Supplémentaire :Créer une base de données MySQL de sauvegarde incrémentielle
Pour cette étape, vous apprendrez l'utilisation principale de la commande 'mysqldump' pour sauvegarder toutes les bases de données MySQL. La ligne de commande 'mysqldump' vous permet de sauvegarder toutes les bases de données, spécifiques ou multiples, et les tables de sauvegarde.
Avant d'aller plus loin, créez le répertoire de sauvegarde et allez-y.
mkdir -p ~/backup; cd ~/backup
Et vous êtes prêt à créer une sauvegarde des bases de données MySQL à l'aide de l'utilitaire "mysqldump".
Sauvegarder toutes les bases de données
Pour sauvegarder toutes les bases de données sur le serveur MySQL, utilisez la commande mysqldump avec l'option '--add-databases' ci-dessous.
mysqldump -u root -P 8320 --all-databases -p > alldbs.sql
Maintenant, vous obtiendrez toutes les sauvegardes de vos bases de données dans le seul fichier SQL nommé 'alldbs.sql'.
Sauvegarder une base de données spécifique
Pour sauvegarder la base de données MySQL spécifique, incluez le nom de votre base de données dans la commande mysqldump ci-dessous.
mysqldump -u root -P 8320 -p hakase_db > backup_hakase_db.sql
En conséquence, vous obtiendrez le fichier de sauvegarde SQL à partir de la base de données 'hakase_db' nommée 'backup_hakase_db.sql' dans le répertoire de sauvegarde.
Sauvegarder plusieurs bases de données
Si vous souhaitez créer une sauvegarde de plusieurs bases de données (pas toutes les bases de données sur le serveur MySQL), utilisez l'option '--databases' ci-dessous.
mysqldump -u root -P 8320 -p --databases yume_db juice_db > multi-db-once.sql
En conséquence, le fichier de sauvegarde de vos deux bases de données 'yume_db' et 'juice_db' a été créé, nommé fichier 'multi-db-once.sql'.
Base de données MySQL de sauvegarde automatique
Pour mettre en place une sauvegarde automatique pour MySQL, nous utiliserons le "cron" Linux. Nous allons créer un nouveau script bash pour les bases de données MySQL et l'exécuter automatiquement à un moment précis à l'aide de Linux Cron.
Avant d'aller plus loin, connectez-vous au shell MySQL à l'aide de la commande suivante.
mysql -u root -P 8320 -p
Créez un nouvel utilisateur de sauvegarde avec certains privilèges à l'aide de la requête suivante.
CREATE USER 'backup'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyBackupPasswordMySQL39==';
GRANT PROCESS, SELECT, LOCK TABLES, SHOW VIEW, EVENT ON *.* TO 'backup'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Tapez maintenant 'exit' pour vous déconnecter du shell MySQL.
Ensuite, créez un nouveau répertoire de sauvegarde '/opt/backup/mysql' pour stocker tous vos fichiers de sauvegarde de base de données.
mkdir -p /opt/backup/mysql
Après cela, allez dans le répertoire '/usr/local/sbin' et créez un nouveau fichier bash 'auto_backup.sh' en utilisant l'éditeur vim.
cd /usr/local/sbin/
vim auto_backup.sh
Modifiez les détails de l'utilisateur, du mot de passe et du port avec les vôtres et collez-y la configuration.
#!/bin/bash
# Basic configuration: datestamp e.g., YYYYMMDD
DATE=$(date +"%Y%m%d")
# Location of your backups (create the directory first!)
BACKUP_DIR="/opt/backup/mysql"
# MySQL login details
MYSQL_USER="backup"
MYSQL_PASSWORD="MyBackupPasswordMySQL39=="
# MySQL executable locations (no need to change this)
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
# MySQL databases you wish to skip
SKIPDATABASES="Database|information_schema|performance_schema|mysql"
# Number of days to keep the directories (older than X days will be removed)
RETENTION=14
# ---- DO NOT CHANGE BELOW THIS LINE ------------------------------------------
#
# Create a new directory into the backup directory location for this date
mkdir -p $BACKUP_DIR/$DATE
# Retrieve a list of all databases
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`
# Dumb the databases in seperate names and gzip the .sql file
for db in $databases; do
echo $db
$MYSQLDUMP --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done
# Remove files older than X days
find $BACKUP_DIR/* -mtime +$RETENTION -delete
Enregistrez et fermez.
Now make the 'auto_backup.sh' script executable and run the script for testing purposes.
chmod +x auto_backup.sh
./auto_backup.sh
As a result, you will get your database backup files on the '/opt/mysql/backup-current-date' directory. Vérifiez à l'aide de la commande suivante.
ls -lah /opt/backup/mysql/
ls -lah /opt/backup/mysql/datedir/
Vous trouverez ci-dessous le résultat que vous obtiendrez.
As a result, you will get compressed files of your database backup, which means the script is working.
Next, create a new cronjob that will be running the MySQL auto backup script 'auto_backup.sh' every day at the time '00:00'.
Set the default editor to 'vim' and create a new cronjob as user root using the following command.
export EDITOR=vim
crontab -u root -e
Collez-y maintenant la configuration suivante.
0 0 * * * sh /usr/local/sbin/auto_backup.sh
Enregistrez et fermez.
The new cronjob for user root has been created. Check all cronjob list for the root user using the following command.
crontab -u root -l
And you will get details cron configuration for the user root.
As a result, you've successfully set up the automatic MySQL database backup using the bash script and Linux Cron.
Finally, you've learned how to install and secure the MySQL Database Server on the Debian Buster 10 with phpMyAdmin web-based database management. Also, you've learned the basic MySQL query for managing users, create database and tables, and using the mysqldump utility for backup databases.
Références
- https://dev.mysql.com/doc/refman/en/
- https://docs.phpmyadmin.net/en/latest/index.html