Mysql Archives - iZero

[Mysql] Se connecter a Mariadb depuis un autre utilisateur

6 septembre 20206 septembre 2020 BEF Laisser un commentaire

Se connecter a Mariadb depuis un autre utilisateur Article original Publié le : 06 septembre 2020 Mise a jour le : – Vérifier les comptes utilisateurs sur le SGDB

MariaDB [(none)]> USE mysql;

1	MariaDB [(none)]> USE mysql;

MariaDB [(none)]> SELECT user, host, plugin FROM mysql.user;

1	MariaDB [(none)]> SELECT user, host, plugin FROM mysql.user;

+------+-----------+-------------+
| user | host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+

+------+-----------+-------------+

| user | host | plugin |

+------+-----------+-------------+

| root | localhost | unix_socket |

+------+-----------+-------------+

Créer un compte utilisateur système et ajouter lui les privilèges et droits nécessaires

MariaDB [(none)]> CREATE USER '<strong>bef</strong>'@'localhost' IDENTIFIED BY '';

1	MariaDB [(none)]> CREATE USER '<strong>bef</strong>'@'localhost' IDENTIFIED BY '';

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO '<strong>bef</strong>'@'localhost';

1	MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO '<strong>bef</strong>'@'localhost';

MariaDB [(none)]> UPDATE user SET plugin='auth_socket' WHERE User='<strong>bef</strong>';

1	MariaDB [(none)]> UPDATE user SET plugin='auth_socket' WHERE User='<strong>bef</strong>';

MariaDB [(none)]> UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket';

1	MariaDB [(none)]> UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket';

MariaDB [(none)]> FLUSH PRIVILEGES;

1	MariaDB [(none)]> FLUSH PRIVILEGES;

Revérifier […]

[Mysql] Se connecter sans entrer de mot de passe

6 septembre 20206 septembre 2020 BEF Laisser un commentaire

Se connecter sans entrer de mot de passe sous mariadb/mysql Article original Publié le : 06 septembre 2020 Mise a jour le : – Créer le fichier a la racine du compte utilisateur, pour l’exemple sous le compte root et ajouter le crédential

# vim /root/.my.cnf

1	# vim /root/.my.cnf

[client]
user=root
password=mot_de_passe

[client]

user=root

password=mot_de_passe

[MySQL] Quelques infos en vrac ..

29 avril 20183 février 2021 BEF Laisser un commentaire

Quelques infos en vrac .. Changer le mot de passe d’un utilisateur

USE mysql;

1	USE mysql;

UPDATE user SET password='<strong>mon_nouveau_mdp</strong>' WHERE user=<strong>mon_user</strong>';

1	UPDATE user SET password='<strong>mon_nouveau_mdp</strong>' WHERE user=<strong>mon_user</strong>';

FLUSH PRIVILEGES;

1	FLUSH PRIVILEGES;

Export requête vers fichier csv Ajouter a la fin de la requête

INTO OUTFILE '/tmp/export_requete.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

INTO OUTFILE '/tmp/export_requete.csv'

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n';

Pour lister les comptes utilisateurs.

MariaDB [(none)]> SELECT User,Host FROM mysql.user;

1	MariaDB [(none)]> SELECT User,Host FROM mysql.user;

+-------------------+
| User  | Host      |
+-------------------+
| root  | 127.0.0.1 |
| root  | ::1       |
| user1 | localhost |
| user2 | localhost |
| root  | localhost |
+-------------------+
 5 rows in set (0.00 sec)

+-------------------+

| User | Host |

+-------------------+

| root | 127.0.0.1 |

| root | ::1 |

| user1 | localhost |

| user2 | localhost |

| root | localhost |

+-------------------+

5 rows in set (0.00 sec)

Supprimer un compte utilisateur.

MariaDB [(none)]> DROP USER 'user2'@'localhost';

1	MariaDB [(none)]> DROP USER 'user2'@'localhost';

Lister les db.

MariaDB [(none)]>  SHOW DATABASES;

1	MariaDB [(none)]> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test-db            |
+--------------------+
4 rows in set (0.00 sec)

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test-db |

+--------------------+

4 rows in set (0.00 sec)

Supprimer une db.

MariaDB [(none)]> DROP DATABASE test-db;

1	MariaDB [(none)]> DROP DATABASE test-db;

MariaDB [(none)]> DROP DATABASE IF EXISTS test-db;

1	MariaDB [(none)]> DROP DATABASE IF EXISTS test-db;

Exporter […]

[Mysql] ERROR 2006 (HY000) at line : MySQL server has gone away

28 décembre 201728 décembre 2017 BEF Laisser un commentaire

ERROR 2006 (HY000) at line : MySQL server has gone away Suite a la restauration d’une bdd, je rencontre l’erreur

$ sudo mysql --user=mon_utilisateur --password=mon_mot_de_passe ma_bdd < mon_dump.sql

1	$ sudo mysql --user=mon_utilisateur --password=mon_mot_de_passe ma_bdd < mon_dump.sql

ERROR 2006 (HY000) at line 714: MySQL server has gone away

1	ERROR 2006 (HY000) at line 714: MySQL server has gone away

Mon dump .sql pèse presque 15mo, il faut donc rajouter la limite max_allowed_packet et définir une taille dans le fichier my.cnf

$ sudo vim /etc/my.cnf

1	$ sudo vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
<strong>max_allowed_packet=64M</strong>

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

<strong>max_allowed_packet=64M</strong>

Puis redémarrer le service

$ sudo systemctl restart mariadb

1	$ sudo systemctl restart mariadb

[…]