Archives de catégorie : MySQL

Mysql Clone test prod

#!/bin/bash
#llk
#Recuperation des dumps effectues sur le filer depuis jn1132 + import sur ce serveur
#lance par /etc/crontab

starttime=$("date")

#Recuperation du dernier backup
backup=$(ls -lastr /filer_backup/mylvmbackup/backup-*.tar.gz | tail -1 | awk '{print $10}')


echo "Demarrage de l'import des donnees Mysql le $starttime, le backup restore est $backup" > /filer_backup/import_dump.log

#cleanning mysql avant import
/etc/init.d/mysql stop;

if [ $? = 0 ]
then
        echo "Arret du service mysql : OK" >> /filer_backup/import_dump.log
else
        echo "Arret du service mysql : KO" >> /filer_backup/import_dump.log
fi;

sleep 15;

rm -rf /var/lib/mysql/data/*;

if [ $? = 0 ]
then
        echo "Supression des donnees dans /var/lib/mysql/data/ : OK" >> /filer_backup/import_dump.log
else
        echo "Supression des donnees dans /var/lib/mysql/data/ : KO" >> /filer_backup/import_dump.log
fi;

#import data sql
tar -I pigz -xvf $backup -C /var/lib/mysql/data/;

if [ $? = 0 ]
then
        echo "Decompression de l'archive $backup : OK" >> /filer_backup/import_dump.log
else
        echo "Decompression de l'archive $backup : KO" >> /filer_backup/import_dump.log
fi;

#mise en place des donnees importees
mv /var/lib/mysql/data/backup/* /var/lib/mysql/data/.;

if [ $? = 0 ]
then
        echo "Import des donnees : OK" >> /filer_backup/import_dump.log
else
        echo "Import des donnees : KO" >> /filer_backup/import_dump.log
fi;

#start mysql
/etc/init.d/mysql start

if [ $? = 0 ]
then
        echo "Demarrage du service Mysql : OK" >> /filer_backup/import_dump.log
else
        echo "Demarrage du service Mysql : KO" >> /filer_backup/import_dump.log
fi;

sleep 15;

# cmd mysql suite demande client
#mysql -e 'cmd mysql specifique au client;'
#if [ $? = 0 ]
#then
#        echo "Cmd mysql update : OK" >> /filer_backup/import_dump.log
#else
#        echo "Cmd mysql update ne s est pas deroulee correctement" >> /filer_backup/import_dump.log
#fi;

endtime=$("date")

echo "Fin du process d import des donness sql le $endtime" >> /filer_backup/import_dump.log

#rapport client

mail -s staggin1 your@mail.fr < /filer_backup/import_dump.log

Backup MySQL avancé

#!/bin/sh

date=`date +%Y%m%d`
day=`date +%u`
week=`date +%Y%U`
user=root
pass=password
dest=/var/backup_mysql
daily=$dest/data-day-$date

bases="mysql base1 base2"

if [ ! -d $daily ]
then
       mkdir -p $daily
fi

cd $daily
for base in $bases
do
	mysqldump -l --user $user --password=$pass --opt --databases $base > $base.sql
	tar -czf $base.tgz $base.sql
	rm $base.sql
done

mysqladmin -p$pass flush-tables

if [ $day == 1 ]
then
	weekly=$dest/data-week-$week
	if [ ! -d $weekly ]
	then
		mkdir $weekly
	fi
	cp $daily/*.tgz $weekly
fi

# On laisse 7 jours de backup
date=`date -d '7 days ago' +%Y%m%d`
rm -rf $dest/data-day-$date

# On laisse 6 mois de backup hebdo
date=`date -d '6 month ago' +%Y%U`
rm -rf $dest/data-week-$date

Backup MySQL Simple

#!/bin/sh

## Options de backup
opts="--skip-opt --event --triggers --routines --quick --extended-insert --create-options --set-charset --disable-keys"

# pour chaque base sauf performance_schema

for DBNAME in `mysql -u root -ppassword -B -e "show databases;" | sed 1d | grep -v performance_schema`
do
# on fait le backup et on compresse
        /usr/bin/mysqldump $opts -u root -ppassword $DBNAME | bzip2 > /mnt/mysql_backup/$DBNAME-$(date +%d%m%Y).sql.bz2
done

MySQL taille des tables

Affichage de la taille des bases de donnée

Taille en Mo

SELECT
 table_schema AS "Nom de la base", 
 ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS "Taille en Mo" 
 FROM information_schema.TABLES
 GROUP BY TABLE_SCHEMA;
Exemple de rendu :
+--------------------+--------------+
| Nom de la base     | Taille en Mo |
+--------------------+--------------+
| information_schema |         0.17 |
| mysql              |         0.65 |
| performance_schema |         0.00 |
+--------------------+--------------+
3 rows in set (0.01 sec)

Taille en Go

SELECT
 table_schema AS "Nom de la base", 
 ROUND(SUM( data_length + index_length ) / 1024 / 1024 /1024 , 2) AS "Taille en Go" 
 FROM information_schema.TABLES
 GROUP BY TABLE_SCHEMA;

MySQL Query Cache

Note : Le gain de performance de l’activation du cache des requêtes est très dépendant de l’applicatif, il sera très efficace pour les requêtes en lecture identiques. Et cela sera d’autant plus vrai pour des tables MyISAM. L’effet sera contre productif sur des charges d’écriture importante.

Demande si le cache pour les requêtes est activé

SHOW VARIABLES LIKE 'have_query_cache';

Résultat

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Paramétrage du query cache

SHOW GLOBAL VARIABLES LIKE 'query_cache%';

Résultat :

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

Désactivation du Qcache

SET GLOBAL query_cache_type=0; 
SET GLOBAL query_cache_size=0; 

Activation du Qcache

SET GLOBAL query_cache_type=1; 
SET GLOBAL query_cache_size = 16777216;

Statistiques du cache hit

SHOW STATUS LIKE "qcache%";

Résultat :

+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 0      |
| Qcache_free_memory      | 0      |
| Qcache_hits             | 130025 |
| Qcache_inserts          | 22837  |
| Qcache_lowmem_prunes    | 1915   |
| Qcache_not_cached       | 8382   |
| Qcache_queries_in_cache | 0      |
| Qcache_total_blocks     | 0      |
+-------------------------+--------+
8 rows in set (0.00 sec)

Paramétrage via my.cnf

Nécessite la relance de Mysql :

A VENIR

MySQL sauvegarde

Dump et restauration

Dump de database

mysqldump -u username -p DB_name > DB_name_save.sql

Restore via dump de la base

mysql -u username -p  DB_name < DB_name_save.sql

Dump avec plus d’options

mysqldump -u username -p  --single-transaction --flush-logs --events --ignore-table=mysql.event --all-databases > full_backup_db.sql

Compression du dump

mysqldump -u username -p  --single-transaction --flush-logs --events --ignore-table=mysql.event --all-databases | gzip > full_backup_db.sql.gz

MySQL activations des logs

Activation des logs

Modifications à effectuer dans le fichier /etc/mysql/my.cnf

[mysqld_safe]
#logging
log_error=/var/log/mysql/mysql_error.log

[mysqld]

log_error=/var/log/mysql/mysql_error.log #general_log_file=/var/log/mysql/mysql.log ##log toutes les requêtes ATTENTION #general_log = 1 log_slow_queries=/var/log/mysql/mysql_slow.log long_query_time = 10 log-queries-not-using-indexes # requêtes sans indexation

Différents moyen de relancer Mysql en fonction de la distribution.

service mysql restart
systemctl restart mysql
/etc/init.d/mysql restart

Résultats

/var/log/mysql
-rw-rw----  1 mysql adm    5287 Jan  8 15:44 mysql_error.log
-rw-rw----  1 mysql adm  719013 Jan  8 15:48 mysql.log
-rw-rw----  1 mysql adm    7516 Jan  8 15:47 mysql_slow.log

Activation à chaud des slow query

Cela ne fonctionne uniquement avec une version MySQL > 5.1

mysql> set global slow_query_log=1;
mysql> set global slow_query_log_file = '/var/log/mysql/mysql-slow-query';
mysql> set global long_query_time=5;

Vérification des variables

mysql> show variables like '%log%';

Gestion des log binaires

Purge des binary logs

mysql> PURGE BINARY LOGS BEFORE '2017-09-17 18:00:00';

Affiche le nombre de jour de conservation des bin log

mysql> SHOW VARIABLES WHERE Variable_Name='expire_logs_days';

Fixe la variable sans restart mysql, penser à éditer le my.cnf

mysql> SET GLOBAL expire_logs_days=1;

MySQL Gestion des droits

Connexion

Toutes ces commandes sont à exécuter dans le shell : mysql> que l’on obtient avec

mysql -u username -p
mysql -p
mysql -S /var/lib/mysql/mysql.socket -p 

Création d’utilisateur et sa base de donnée

CREATE USER 'USER'@'localhost' IDENTIFIED BY 'PASSWORD';
CREATE DATABASE IF NOT EXISTS database_name;
GRANT ALL PRIVILEGES ON database_name.* TO  'USER'@'localhost' ;
flush privileges ;

Toujours effectuer un flush privilèges après une modification des droits, sinon ils ne sont pas pris en compte.

Affiche des utilisateurs

select user, host, password from mysql.user;

Changement mot de passe

set password for 'username'@'localhost' = PASSWORD('s1234pass');

Affiche les droits

show grants for username ; 
show grants for 'user'@'%';
show grants for 'username'@'x.x.x.x';

Suppression Utilisateur

drop user  "username"@"localhost" ;

Attribution de droits sur une base

GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'x.x.x.x' IDENTIFIED BY 'lfgskill';

Résolution du problème de charset

Lors de la création de la table suivante, on se retrouve avec

CREATE DATABASE redmine CHARACTER SET utf8mb4;

Error: Specified key was too long; max key length is 767 bytes

solution

mysql -u root -p -e "CREATE DATABASE redmine DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;