Skip to content

Procédures diverses pour mariadb et mysql

Auteur : Philippe Le Van - @plv@framapiaf.org

Création : 8 novembre 2021

Dernière modification : 30 novembre 2021

Introduction

Cette page contient quelques procédures en vrac qui peuvent être utiles pour mysql et mariadb.

Créer un user en readonly

Se connecter à la base en root :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql -uroot -pxxxx

MariaDB [(none)]> CREATE USER 'myuser_readonly'@'%' IDENTIFIED BY 'jxxxxxxxxxxxxxxA';
Query OK, 0 rows affected (0.030 sec)

MariaDB [(none)]> GRANT SELECT ON *.* TO 'myuser_readonly'@'%';
Query OK, 0 rows affected (0.017 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.016 sec)

Table 'xxx' is marked as crashed

Lors d'un dump de la base, j'ai l'erreur suivante qui est remontée :

1
2
3
/usr/bin/mysqldump --opt --all-databases -uroot -pxxx > /backup/dump_mysql/dump.sql

mysqldump: Got error: 145: Table './yyyy/my_table' is marked as crashed and should be repaired when using LOCK TABLES

Concrêtement il y a la table "my_table" corrompue dans la base "yyyy".

Pour corriger le problème :

  • se connecter à la base concernée
  • lancer les commandes de réparation CHECK TABLE et REPAIR TABLE : cf ci-dessous.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> CHECK table my_table;
+----------------------------------+-------+----------+----------------------------------------------------------+
| Table                            | Op    | Msg_type | Msg_text                                                 |
+----------------------------------+-------+----------+----------------------------------------------------------+
| yyyy.my_table | check | warning  | Table is marked as crashed                               |
    | yyyy.my_table | check | warning  | 4 clients are using or haven't closed the table properly | 
| yyyy.my_table | check | error    | Record at pos: 765052 is not remove-marked               | 
| yyyy.my_table | check | error    | record delete-link-chain corrupted                       | 
| yyyy.my_table | check | error    | Corrupt                                                  | 
+----------------------------------+-------+----------+----------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> REPAIR table my_table;
+----------------------------------+--------+----------+----------+
| Table                            | Op     | Msg_type | Msg_text |
+----------------------------------+--------+----------+----------+
| yyyy.my_table | repair | status   | OK       | 
+----------------------------------+--------+----------+----------+
1 row in set (0.00 sec)

mysql> CHECK table my_table;
+----------------------------------+-------+----------+----------+
| Table                            | Op    | Msg_type | Msg_text |
+----------------------------------+-------+----------+----------+
| yyyy.my_table | check | status   | OK       | 
+----------------------------------+-------+----------+----------+
1 row in set (0.00 sec)