[MySQL] Quelques infos en vrac .. - iZero

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 les schémas de db sans les datas

$ mysqldump -u root -p --no-data nom_de_la_db > /tmp/schema.sql

1	$ mysqldump -u root -p --no-data nom_de_la_db > /tmp/schema.sql

Révoquer les droits d’un utilisateurs a une db

D’abord lister les droits de l’utilisateur

MariaDB [(none)]>  show grants for user@'localhost';

1	MariaDB [(none)]> show grants for user@'localhost';

Pour révoquer toutes les permissions d’un utilisateur

MariaDB [(none)]>  REVOKE ALL ON base_de_donnee.* FROM user@'localhost';

1	MariaDB [(none)]> REVOKE ALL ON base_de_donnee.* FROM user@'localhost';

Afficher en MO la taille d’une base de données MySQL

MariaDB [(none)]> SELECT sum( data_length + index_length) / 1024 / 1024 " Taille en Mo" FROM information_schema.TABLES WHERE table_schema = "<strong>nom_de_la_db</strong>" GROUP BY table_schema;

1	MariaDB [(none)]> SELECT sum( data_length + index_length) / 1024 / 1024 " Taille en Mo" FROM information_schema.TABLES WHERE table_schema = "<strong>nom_de_la_db</strong>" GROUP BY table_schema;

+--------------+
| Taille en Mo |
+--------------+
|  73.66721249 |
+--------------+
1 row in set, 1 warning (0.01 sec)

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

| Taille en Mo |

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

| 73.66721249 |

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

1 row in set, 1 warning (0.01 sec)

Sur mariadb, éditer le fichier 50-server.cnf et modifier la valeur ci dessous

$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

1	$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

max_allowed_packet=<del>16M</del> <strong>64M</strong>

1	max_allowed_packet=<del>16M</del> <strong>64M</strong>

Exporter une base de donnée

$ sudo mysqldump -u root -p ma_bdd | gzip > /tmp/export_ma_bdd.sql.gz

1	$ sudo mysqldump -u root -p ma_bdd \| gzip > /tmp/export_ma_bdd.sql.gz

Articles lus : 1 548

No votes yet.

Please wait...

Voting is currently disabled, data maintenance in progress.

Laisser un commentaire Annuler la réponse