# MYSQL



# PB SQL FAILOVER

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/prqridGeTknB9WLK-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/prqridGeTknB9WLK-image.png)

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/djudYGzktFnJjN31-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/djudYGzktFnJjN31-image.png)

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/kiy6ZqQJSl8qlUoA-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/kiy6ZqQJSl8qlUoA-image.png)

Clic droit --> Failover…

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/M48dlEqoKZV1zpgn-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/M48dlEqoKZV1zpgn-image.png)  
  
Next

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/c4axB676YCtwSXOW-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/c4axB676YCtwSXOW-image.png)

Next

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/NzfCn5AcCeF0pWcL-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/NzfCn5AcCeF0pWcL-image.png)

Finish

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/p0AMcj6Fet4ajcFL-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/p0AMcj6Fet4ajcFL-image.png)

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/hOh1XjcMrsEKj7a7-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/hOh1XjcMrsEKj7a7-image.png)

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/4bqvz1PfiytDMG0W-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/4bqvz1PfiytDMG0W-image.png)

Vérifier que le 1 est bien passé en Primary ainsi que le 2 en Secondary

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/cA3LzOnf7XftaggF-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/cA3LzOnf7XftaggF-image.png)

# Commandes General

<span style="color: rgb(45, 194, 107);">Poids base de doonnées</span>

```mysql
SELECT sum( data_length + index_length) / 1024 / 1024 "Nom-de-la-base Taille en Mo" FROM information_schema.TABLES WHERE table_schema = "nom-de-base" GROUP BY table_schema;
```

<span style="color: rgb(45, 194, 107);">Read permission</span>

```mysql
GRANTSELECTON DBNAME.TABLE_NAME TO USERNAME 
```

<span style="color: rgb(230, 126, 35);"><span style="color: rgb(45, 194, 107);">liste toutes les BDD</span>  
</span>

```mysql
show databases;
```

<span style="color: rgb(45, 194, 107);">se met sur la BDD qui nous intéresse </span>

```mysql
Use nomdelabdd; 
```

<span style="color: rgb(45, 194, 107);">Suppression de la BDD</span>

```mysql
Drop database nomdelabdd;
```

<span style="color: rgb(45, 194, 107);">Création de la BDD </span>

```mysql
Create database nomdelabdd;
```

<span style="color: rgb(45, 194, 107);">Dans le cas où le compte mysqldump n'a pas les droits de restauration de dump</span>

```mysql
grant all privileges on *.* to mysqldump@localhost;
```

<span style="color: rgb(230, 126, 35);"><span style="color: rgb(45, 194, 107);">Restauration du dump dans la BDD</span></span>

```mysql
mysql -u mysqldump -p "nomdelabdd" < "/production/mysql_dump/nomdelabdd.sql"
```

<span style="color: rgb(45, 194, 107);">Vérification de la bonne restauration du dump</span>

```bash
ls -alsh /production/mysql/nomdelabdd/ 
```

<span style="color: rgb(45, 194, 107);">Pour se connecter</span>

```bash
mysql
```

<span style="color: rgb(45, 194, 107);">Pour avoir la taille de toutes les BDD:</span>

```mysql
SELECT table_schema AS "Database",  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"  FROM information_schema.TABLES  GROUP BY table_schema;
```

<span style="color: rgb(45, 194, 107);">Pour avoir la taille de toutes les tables d'une BDD:</span>

```mysql
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "database_name" ORDER BY (data_length + index_length) DESC;
```

<span style="color: rgb(230, 126, 35);"><span style="color: rgb(45, 194, 107);">Reset Mot de passe Mysql:</span>  
</span>

```mysql
ALTER USER 'mysqldump'@'localhost' IDENTIFIED BY 'dump';
```

# Dump/Restore

<span style="color: rgb(45, 194, 107);">Création de dump Mysql: </span>

```bash
/usr/bin/mysqldump -u mysqldump -p name | gzip -c --best > /production/mysql_dump/Save_25.sql.gz
```

<span style="color: rgb(45, 194, 107);">Dezip d'un dump au format sql.gz : </span>

```bash
gunzip /production/mysql_dump/Save_25.sql.gz /tmp
```

<p class="callout warning">Attention cela remplace le fichier d'origine en .sql</p>

Affichage de tous les droits sur une BDD:

```mysql
Use name;
SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;
```

Restauration d'un dump Mysql avec le dump au format .sql :

  
<span style="color: rgb(230, 126, 35);">show databases; </span> --&gt; liste toutes les BDD  
<span style="color: rgb(230, 126, 35);">Use nomdelabdd; </span> --&gt; On se met sur la BDD qui nous intéresse   
<span style="color: rgb(230, 126, 35);">Drop database nomdelabdd; </span> --&gt; Suppression de la BDD actuelle  
<span style="color: rgb(230, 126, 35);">Create database nomdelabdd; </span> --&gt; Création de la BDD   
<span style="color: rgb(230, 126, 35);">grant all privileges on \*.\* to mysqldump@localhost; </span> --&gt; Dans le cas où le compte mysqldump n'a pas les droits de restauration de dump  
<span style="color: rgb(230, 126, 35);">mysql -u mysqldump -p nomdelabdd &lt; /production/mysql\_dump/nomdelabdd.sql </span> --&gt; Restauration du dump dans la BDD nouvellement créée  
<span style="color: rgb(230, 126, 35);">ls -alsh /production/mysql/nomdelabdd/ </span> --&gt; Vérification de la bonne restauration du dump, voir s'il y a des éléments récents

Pour info, utiliser la commande pv lors de la restauration d'un dump afin de voir en live ce que fait la restauration:

# Mysql innodb cache info

Vérifier taille Buffer Pool Size:

```bash
mysql
```

```mysql
SELECT FORMAT (BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM (SELECT variable_value BufferPoolPages FROM information_schema.global_status WHERE variable_name ='Innodb_buffer_pool_pages_total') A, (SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name ='Innodb_page_size') B;
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/u6n5gzYWIlUQmbRP-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/u6n5gzYWIlUQmbRP-image.png)

Vérifier données en cache:

```bash
Mysql
```

```mysql
SELECT FORMAT (BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM (SELECT variable_value BufferPoolPages FROM information_schema.global_status WHERE variable_name ='Innodb_buffer_pool_pages_data') A, (SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name ='Innodb_page_size') B;
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/o5sKwIapCzjnA7nP-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/o5sKwIapCzjnA7nP-image.png)

Vérifier % d'utilisation en cache:

```mysql
SELECT CONCAT (FORMAT (DataPages*100.0/TotalPages,2),' %') BufferPoolDataPercentage FROM (SELECT variable_value DataPages FROM information_schema.global_status WHERE variable_name ='Innodb_buffer_pool_pages_data') A, (SELECT variable_value TotalPages FROM information_schema.global_status WHERE variable_name ='Innodb_buffer_pool_pages_total') B;
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/6VgxvLA3PMYv2cQ8-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/6VgxvLA3PMYv2cQ8-image.png)

  
Données supplémentaires sur le cache:

```mysql
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/MWX54sJYOtZlx84j-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/MWX54sJYOtZlx84j-image.png)

Statut Innodb:

```mysql
SHOW ENGINE INNODB STATUS;
```

Buffer hit ratio:

```mysql
SELECT round ((P2.variable_value /P1.variable_value),4),  P2.variable_value, P1.variable_value FROM information_schema.GLOBAL_STATUS P1, information_schema.GLOBAL_STATUS P2 WHERE P1. variable_name ='innodb_buffer_pool_read_requests'ANDP2. variable_name ='innodb_buffer_pool_reads'; 
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/2a0rFqK3Vtk0W097-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/2a0rFqK3Vtk0W097-image.png)

# Modification du compte mysql_monitor

```bash
mysql
```

```mysql
SHOW DATABASES;
```

```mysql
select user, host from mysql.users;
```

```mysql
UPDATE user SET password=PASSWORD('PASSWORD') WHERE user='mysql_monitor';
```

<p class="callout info">si la commande ci-dessous ne fonctionne pas</p>

```mysql
ALTER USER 'mysql_monitor' IDENTIFIED BY '************';
```

```mysql
FLUSH PRIVILEGES;
```