MySql – info

Maintenance base domotique

Se connecter root, sur MariaDB 10, puis sur la base domo

SELECT * FROM graph_tbl WHERE ftimestamp < ‘2023-11-01 00:00:00’

create table graph_tbl_2022_1101_2023_1030 as SELECT * FROM graph_tbl WHERE ftimestamp < ‘2023-11-01 00:00:00’

delete FROM graph_tbl WHERE ftimestamp < ‘2023-11-01 00:00:00’

CREATE UNIQUE INDEX idx_mesure ON graph_tbl2022_1101_2023_1030 (ffeed(15),ftimestamp)

drop INDEX idx_mesure ON graph_tbl

CREATE UNIQUE INDEX idx_mesure ON graph_tbl (ffeed(15),ftimestamp)

———————————————————-

create table graph_conso_2022_1001 as SELECT * FROM graph_conso WHERE ftimestamp < ‘2022-10-01 00:00:00’

delete FROM graph_conso WHERE ftimestamp < ‘2022-10-01 00:00:00’

CREATE UNIQUE INDEX idx_mesure ON graph_conso_2022_1001 (ffeed(15),ftimestamp)

drop INDEX idx_mesure ON graph_conso

CREATE UNIQUE INDEX idx_mesure ON graph_conso (ffeed(15),ftimestamp)

Remplacement d’une valeur par une autre dans un champ

But :
Remplacer fougere.myds.me:8080 par les-fougs.com dans les champs post_content et guid de la table wp_posts

create table wp_posts_old as select * from wp_posts

SELECT post_content FROM wp_posts WHERE post_content like ‘%http://fougere.myds.me:8080/recettes%’

SELECT REPLACE (post_content, ‘http://fougere.myds.me:8080/recettes’ , ‘http://les-fougs.com/recettes’) FROM wp_posts WHERE post_content like ‘%http://fougere.myds.me:8080/recettes%’

update wp_posts set post_content = REPLACE (post_content, ‘fougere.myds.me:8080’ , ‘les-fougs.com’) WHERE post_content like ‘%fougere.myds.me:8080%’

SELECT guid FROM wp_posts WHERE guid like ‘%http://fougere.myds.me:8080/recettes%’

SELECT REPLACE (guid, ‘http://fougere.myds.me:8080/recettes’ , ‘http://les-fougs.com/recettes’) FROM wp_posts WHERE guid like ‘%http://fougere.myds.me:8080/recettes%’

update wp_posts set guid = REPLACE (guid, ‘fougere.myds.me:8080’ , ‘les-fougs.com’) WHERE guid like ‘%fougere.myds.me:8080%’

Source :
REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

mysql> SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);
-> ‘WwWwWw.mysql.com’
This function is multibyte safe.

Query Mysql en ligne de commande

/soft/mysql55/bin/mysql -u user -ppassword -e « show databases »;

/soft/mysql55/bin/ = chemin pour accéder au bin mysql

Pour trouver le chemin :
find / -name mysql -type f

[root@pdtb7 ~]# /soft/mysql55/bin/mysql -u root -pxxxxxx -e "show databases";
+--------------------+
| Database |
+--------------------+
| information_schema |
| FT_DATAMART_TST |
| FT_DATASTAGE_TST |
| mysql |
| performance_schema |
| redsbx |
+--------------------+

my_coldbackup_database.sh

w_date=$(date '+%Y_%m_%d_%H_%M_%S')

# avec generation log
# su mysql --command="mysqlbackup --user=root --password=motdepasse --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup /etc/mysql/my.cnf /data/backups/mysql" > /exploit/logs/my-backup-${w_date}.log

# sans log pour remonter dans VTOM
su mysql --command="mysqlbackup --user=root --password=motdepasse --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup /etc/mysql/my.cnf /data/backups/mysql"

[root@pdtb2 scripts]# cat my_coldbackup_database.sh
#w_date=$(date '+%Y_%m_%d_%H_%M_%S')
w_date=$(date '+%A')

BACKUPDIR=/data/backups/mysql/${w_date}_coldbackup
DATADIR=/data/mysql

echo "rm -rf $BACKUPDIR"
rm -rf $BACKUPDIR

echo "stop mysql database"
/exploit/scripts/my_stop_database.sh

su mysql --command="mkdir $BACKUPDIR"

echo "copy my.cnf"
cp -p /etc/mysql/my.cnf $BACKUPDIR/

echo "copy database sans compression"
time cp -rp $DATADIR/ $BACKUPDIR/

echo "start mysql database"
/exploit/scripts/my_start_database.sh

sleep 120

my_backup_database.sh

w_date=$(date '+%Y_%m_%d_%H_%M_%S')

# avec generation log
# su mysql --command="mysqlbackup --user=root --password=motdepasse --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup /etc/mysql/my.cnf /data/backups/mysql" > /exploit/logs/my-backup-${w_date}.log

# sans log pour remonter dans VTOM
su mysql --command="mysqlbackup --user=root --password=motdepasse --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup /etc/mysql/my.cnf /data/backups/mysql"

my_alertlog.sh

#!/bin/sh
echo "----------------------------------------------"
echo " Recherche des erreurs dans les log"
echo "----------------------------------------------"
echo " "

fic=$1 # nom du fichier
day=$2 # jour recherché
error="$3" # chaine erreur

#fic=/data/mysql/databases/pdtb2.err
#day=131222
#error="ERROR"

echo "File : $fic"
echo "day : $day"
echo "error : $error"

nbligne=`cat $fic | wc -l`
echo "nom de lignes dans le fichier log : " $nbligne

numligne=`grep -n "$day " $fic |head -1 | awk -F ':' '{print $1}'`
if [ "x$numligne" == "x" ]; then
 echo "Pas d erreur trouvée"
 exit 0
else
 echo "Erreur trouvée en ligne : " $numligne
fi

# on compte le nombre d'erreurs trouvées et on affiche les 5 lignes suivantes du log
cpt=`tail -n +$numligne $fic | grep -C 5 $error |wc -l`
if [ $cpt == 0 ]; then
 # si pas d'erreur on sort
 exit 0
else
 # On affiche les erreur pour le log Vtom
 tail -n +$numligne $fic | grep -C 5 $error
 exit $cpt
fi

my_stop_database.sh

mysqladmin -pmotdepasse shutdown

my_start_database.sh

mysqld_safe --user=mysql &

my_SISCAT_reload.sh

database=SISCAT
dumpfile=/exploit/dumps/my-dump-SISCAT.sql

mysqladmin -pmotdepasse drop $database;
mysqladmin -pmotdepasse create $database; 
mysql -pmotdepasse --database $database < $dumpfile

my_exp_SISCAT_pdtb2.sh

w_date=$(date '+%Y_%m_%d_%H_%M_%S')

#mysqldump -h pdtb2.ft.grp -u 64serviceprd -pmotdepasse --log-error=/exploit/logs/my-dump-SISCAT-${w_date}.log SISCAT > /exploit/dumps/my-dump-SISCAT.sql
mysqldump -h pdtb2.ft.grp -u 64serviceprd -pmotdepasse SISCAT > /exploit/dumps/my-dump-SISCAT.sql

my_imp_database.sh

# ==================================================
# Nom :
# Objet :
# Auteur :
# Cree le :
# Syntaxe : user pwd database_FROM_name database_TO_name
# ==================================================

user=$1
pwd=$2
databasefrom=$3
databaseto=$4

w_date=$(date '+%Y%m%d_%H%M%S')
logpath=/exploit/logs
logfile=my_import_${databaseto}_${w_date}.log
dumpfile=/exploit/dumps/my_exp_${databasefrom}.sql
sqlfile=/exploit/logs/my_imp_${databaseto}_${w_date}.sql
# ----------------------------------
# Fonction d'affichage de la syntaxe
# ----------------------------------
syntaxe()
{
 echo "Usage : $0 $1 user $2 pwd $3 database_FROM_name $4 database_TO_name"
}
# ----------------------------------

# --------------------------
# Verification de la syntaxe
# --------------------------
#if [ $# -ne 3 -a $# -ne 4 ]
if [ $# -lt 3 ]
then
 echo "=================================================="
 echo "ERREUR : Nombre d'arguments incorrects !"
 syntaxe
 echo "=================================================="
 exit 1
fi
echo "-------------------------------------------------"
echo " DROP and CREATE DATABASE $databaseto;"
echo "-------------------------------------------------"
# tee $logpath/$logfile;

#echo "DROP DATABASE if exists $databaseto;">$sqlfile;
#echo "CREATE DATABASE IF NOT EXISTS $databaseto;">>$sqlfile;
#echo "show databases;">>$sqlfile;
#echo "use $databaseto;">>$sqlfile;
#echo "source $dumpfile;">>$sqlfile;
#echo ""

#cat $sqlfile;

#mysql --show-warnings -u $user -p${pwd} <$sqlfile >$logpath/$logfile;

mysql --show-warnings -u $user -p${pwd} << EOF
 warnings;
 DROP DATABASE if exists $databaseto;
 CREATE DATABASE IF NOT EXISTS $databaseto;
 show databases;
 use $databaseto;
 source $dumpfile;
EOF
echo "-------------------------------------------------"
# cat $logpath/$logfile

my_exp_database.sh

#!/bin/bash
# ==================================================
# Nom : my_exp_database.sh
# Objet : export database
# Auteur : N.LAMOTTE
# Cree le : 08/02/2012
# Syntaxe : my_exp_database.sh user pwd database_name (otheroption1, otheroption2, otheroption1otheroption3 not mandatory)
# ==================================================

user=$1
pwd=$2
database=$3
otheroption1=$4
otheroption2=$5
otheroption3=$6

w_date=$(date '+%Y_%m_%d_%H_%M_%S')
logpath=/exploit/logs
dumppath=/exploit/dumps
logfile=my_exp_${database}_${w_date}.log
dumpfile=my_exp_${database}.sql

# ----------------------------------
# Fonction d'affichage de la syntaxe
# ----------------------------------
syntaxe()
{
 echo "Usage : $0 $1 user $2 pwd $3 database_name ($4 otheroption1 $5 otheroption2 $6 otheroption3 not mandatory)"
}
# ----------------------------------

# --------------------------
# Verification de la syntaxe
# --------------------------
#if [ $# -ne 3 -a $# -ne 4 ]
if [ $# -lt 3 ]
then
 echo "=================================================="
 echo "ERREUR : Nombre d'arguments incorrects !"
 syntaxe
 echo "=================================================="
 exit 1
fi

if [ $# -gt 4 ]
then
 echo "mysql with option $otheroption1 $otheroption2 $otheroption3"
 dumpfile=my_exp_${database}_withoption.sql
 logfile=my_exp_${database}_withoption_${w_date}.log
fi

#mysqldump -u $user -p${pwd} $database $otheroption1 $otheroption2 $otheroption3 --log-error=$logpath/$logfile > $dumppath/$dumpfile
mysqldump -u $user -p${pwd} -v $database $otheroption1 $otheroption2 $otheroption3 > $dumppath/$dumpfile

my_exp_database_host.sh

#!/bin/bash
# ==================================================
# Nom : my_exp_database.sh
# Objet : export database
# Auteur : N.LAMOTTE
# Cree le : 08/02/2012
# Syntaxe : my_exp_database.sh user pwd database_name (otheroption1, otheroption2, otheroption1otheroption3 not mandatory)
# ==================================================

user=$1
pwd=$2
database=$3
host=$4
otheroption1=$5
otheroption2=$6
otheroption3=$7

w_date=$(date '+%Y_%m_%d_%H_%M_%S')
logpath=/exploit/logs
dumppath=/exploit/dumps
logfile=my_exp_${database}_${w_date}.log
dumpfile=my_exp_${database}.sql

# ----------------------------------
# Fonction d'affichage de la syntaxe
# ----------------------------------
syntaxe()
{
 echo "Usage : $0 $1 user $2 pwd $3 database_name $4 host ($5 otheroption1 $6 otheroption2 $7 otheroption3 not mandatory)"
}
# ----------------------------------

# --------------------------
# Verification de la syntaxe
# --------------------------
#if [ $# -ne 3 -a $# -ne 4 ]
if [ $# -lt 4]
then
 echo "=================================================="
 echo "ERREUR : Nombre d'arguments incorrects !"
 syntaxe
 echo "=================================================="
 exit 1
fi

if [ $# -gt 5 ]
then
 echo "mysql with option $otheroption1 $otheroption2 $otheroption3"
 dumpfile=my_exp_${database}_withoption.sql
 logfile=my_exp_${database}_withoption_${w_date}.log
fi

#mysqldump -u $user -p${pwd} $database $otheroption1 $otheroption2 $otheroption3 --log-error=$logpath/$logfile > $dumppath/$dumpfile
mysqldump -h $host -u $user -p${pwd} -v $database $otheroption1 $otheroption2 $otheroption3 > $dumppath/$dumpfile

my_ET_tst.sh

w_date=$(date '+%Y_%m_%d_%H_%M_%S')
nb_jour=$1 # Nombre de jour

cmd=$(mysql -u redsrv -pmotdepasse redmine --skip-column-names << EOF
select concat('RAILS_ENV=production rake redmine:plugins:project_extras:reminder_by_ids issues="',group_concat(t1.id SEPARATOR ' '),'" message="blabla" user="ET"')
from issues t1
where datediff(current_date , t1.updated_on) <= $1;
EOF)
echo $cmd
$cmd

my_drop_database_mig.sh

mysqladmin -pmotdepass drop FT_DATAMART_PROD;

my_backup_database.sh

w_date=$(date '+%Y_%m_%d_%H_%M_%S')

# avec generation log
# su mysql --command="mysqlbackup --user=root --password=motdepasse --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup /etc/mysql/my.cnf /data/backups/mysql" > /exploit/logs/my-backup-${w_date}.log

# sans log pour remonter dans VTOM
su mysql --command="mysqlbackup --user=root --password=motdepasse --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup /etc/mysql/my.cnf /data/backups/mysql"

my_backup_binlog.sh

w_date=$(date '+%Y_%m_%d_%H_%M_%S')

BINLOGPATH=/data/mysql/databases
BINLOGNAME=mysql-bin.index
BACKUPDIR=/data/backups/mysql/${w_date}_binlog
# sans log pour remonter dans VTOM
#su mysql --command="mysqlbackup --user=root --password=zsxedc --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup /etc/mysql/my.cnf /data/backups/mysql"

echo "Flush Logs"
mysql -pzsxedc --batch --skip-column-names --execute="flush logs"

echo "Copying binlogs"

su mysql --command="mkdir $BACKUPDIR"
for FILE in `cat $BINLOGPATH/$BINLOGNAME`
do
SFILE=(${FILE/*\//})
#echo "$SFILE"
su mysql --command="cp -p $BINLOGPATH/$SFILE $BACKUPDIR/"
#gzip $/$SFILE
done

my_alter_view_mig.sh

#!/bin/bash
file=/exploit/scripts/my_alter_view_mig.sql
mysql -pzsxedc <$file

my_alter_engine_mig.sh

#!/bin/bash
file=/exploit/scripts/my_alter_table_engine_mig.sql
file2=/exploit/scripts/my_alter_table_engine_mig_spe.sql
mysql -pzsxedc --batch --skip-column-names --execute="select concat('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') from information_schema.TABLES where table_schema not in ('information_schema', 'mysql', 'performance_schema') and table_type <> 'VIEW' and engine <> 'InnoDB' and table_name not in ('General Ledger','Controlling#temporary','Overdue-DrillThrough')" > $file
cat $file
time mysql -pzsxedc <$file
cat $file2
time mysql -pzsxedc <$file2

my_alertlog.sh

#!/bin/sh
echo "----------------------------------------------"
echo " Recherche des erreurs dans les log"
echo "----------------------------------------------"
echo " "

fic=$1 # nom du fichier
day=$2 # jour recherche
error=$3 # chaine erreur

#fic=/data/mysql/databases/pdtb2.err
#day=140114
#error=ERROR

echo "File : $fic"
echo "day : $day"
echo "error : $error"

nbligne=`cat $fic | wc -l`
echo "nom de lignes dans le fichier log : " $nbligne

numligne=`grep -n "$day" $fic |head -1 | awk -F ':' '{print $1}'`
if [ "x$numligne" == "x" ]; then
 echo "Pas d erreur trouvee"
 exit 0
else
 echo "Erreur trouvee en ligne : " $numligne
fi

# on compte le nombre d'erreurs trouvees et on affiche les 5 lignes suivantes du log
cpt=`tail -n +$numligne $fic | grep -C 5 $error |wc -l`
if [ $cpt == 0 ]; then
 # si pas d'erreur on sort
 exit 0
else
 # On affiche les erreur pour le log Vtom
 tail -n +$numligne $fic | grep -C 5 $error
 exit $cpt
fi