« Mysql » : différence entre les versions

De bricosoft
 
m Remplacement des balises <source> obsolètes par <syntaxhighlight> (bot)
 
(2 versions intermédiaires par 2 utilisateurs non affichées)
Ligne 1 : Ligne 1 :
[[Fichier:Common dolphin-1395.jpg|thumb|Un dauphin sauvage]]
[[Fichier:Common dolphin-1395.jpg|thumb|Un dauphin sauvage]]
[[Fichier:Tursiops truncatus-5813.jpg|thumb|Un autre dauphin emprisonné qui saute dans un cerceau]]
[[Fichier:Tursiops truncatus-5813.jpg|thumb|Un autre dauphin emprisonné qui saute dans un cerceau]]
<div class="thumb tright"><google uid="C06" position="left"></google></div>
'''Mysql''' pour les moules.
'''Mysql''' pour les moules.


Ligne 10 : Ligne 9 :
From : [http://serverfault.com/questions/160793/shell-mysql-backups-whats-your-solution/160808#160808]
From : [http://serverfault.com/questions/160793/shell-mysql-backups-whats-your-solution/160808#160808]


<source lang="bash">
<syntaxhighlight lang="bash">
!/bin/bash
!/bin/bash


Ligne 36 : Ligne 35 :
done
done


</source>
</syntaxhighlight>


will create the tree into :
will create the tree into :
  /home/MY_USER_LOGIN/backup
  /home/MY_USER_LOGIN/backup
<source lang="bash">
<syntaxhighlight lang="bash">
.
.
|
|
Ligne 51 : Ligne 50 :
     |-- www_tfz_me.gz
     |-- www_tfz_me.gz
     `-- www_tremulous_eu.gz
     `-- www_tremulous_eu.gz
</source>
</syntaxhighlight>


===script pour le backup des utilisateurs et des droits===
===script pour le backup des utilisateurs et des droits===


into .bash_aliases :
into .bash_aliases :
<source lang="bash">
<syntaxhighlight lang="bash">
mygrants()
mygrants()
{
{
Ligne 64 : Ligne 63 :
                       sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
                       sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}
}
</source>
</syntaxhighlight>


logout/login or :
logout/login or :
  $ source .bash_aliases
  $ source .bash_aliases


$ mygrants --user=root --password=totoche
$ mygrants --user=root --password=totoche
Ligne 151 : Ligne 149 :
bind-address            = 0.0.0.0
bind-address            = 0.0.0.0
</pre>
</pre>


=== Master : utilisateur backup ===
=== Master : utilisateur backup ===
Ligne 163 : Ligne 160 :
FLUSH PRIVILEGES;
FLUSH PRIVILEGES;
</pre>
</pre>


===Relancer mysql sur les deux serveurs ===
===Relancer mysql sur les deux serveurs ===
Ligne 220 : Ligne 216 :
=== CONCAT(NULL,something) returns NULL WTF?===
=== CONCAT(NULL,something) returns NULL WTF?===
[http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat It's not a bug, it's a feature], try [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws CONCAT_WS](',',something,NULL)
[http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat It's not a bug, it's a feature], try [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws CONCAT_WS](',',something,NULL)


=== remettre autoincrement à 0 ===
=== remettre autoincrement à 0 ===
Ligne 243 : Ligne 238 :
           and runlevel [345])
           and runlevel [345])
</pre>
</pre>
===perdu le mdp de root===
sudo service mysql stop
sudo mysqld_safe --skip-grant-tables --skip-syslog --skip-networking
...
autre terminal :
mysql -u root
UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
FLUSH PRIVILEGES;
ensuite :
mysqladmin shutdown
sudo service mysql start


==Syntaxes pas banales==
==Syntaxes pas banales==
Ligne 258 : Ligne 269 :
===import de données depuis un fichier .csv===
===import de données depuis un fichier .csv===
Au préalable, retirer la 1er ligne du csv (les noms de colonnes) :
Au préalable, retirer la 1er ligne du csv (les noms de colonnes) :
<source lang="sql">
<syntaxhighlight lang="sql">
load data local infile '/home/TOTO/donnees_fruits.csv' into table joli_table fruits terminated by ','
load data local infile '/home/TOTO/donnees_fruits.csv' into table joli_table fruits terminated by ','
enclosed by '"'
enclosed by '"'
lines terminated by '\n'
lines terminated by '\n'
(id,couleur,forme,masse)
(id,couleur,forme,masse)
</source>
</syntaxhighlight>

Dernière version du 8 novembre 2026 à 17:46

Fichier:Common dolphin-1395.jpg
Un dauphin sauvage
Fichier:Tursiops truncatus-5813.jpg
Un autre dauphin emprisonné qui saute dans un cerceau

Mysql pour les moules.

Backup

script pour le back up de toutes les databases

From : [2]

!/bin/bash

BACKUP_DIR="/home/MY_USER_LOGIN/backup"
MYSQL_USER=root
MYSQL_PASS=totoche
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

DB_LIST="$($MYSQL -u $MYSQL_USER -p$MYSQL_PASS -Bse 'show databases')"
for db in $DB_LIST;
do
    echo "$db";
    BACKUP_SUBDIR="$BACKUP_DIR/mysql_`date +%Y-%m-%d`"
    echo $BACKUP_SUBDIR;
    BACKUP_FILE="$BACKUP_SUBDIR/$db.gz"
    if [ ! -d $BACKUP_SUBDIR ]; then
        mkdir -p $BACKUP_SUBDIR
    fi
    if [ -f $BACKUP_FILE ]; then
        unlink $BACKUP_FILE
    fi
    $MYSQLDUMP -u $MYSQL_USER -p$MYSQL_PASS $db | $GZIP -9 > $BACKUP_FILE
done

will create the tree into :

/home/MY_USER_LOGIN/backup
.
|
`-- mysql_2010-08-30
    |-- mysql.gz
    |-- information_schema.gz
    |-- phpmyadmin.gz
    |-- www_bricosoft_com.gz
    |-- www_mediawiki_fr.gz
    |-- www_tfz_me.gz
    `-- www_tremulous_eu.gz

script pour le backup des utilisateurs et des droits

into .bash_aliases :

mygrants()
{
    mysql -B -N $@ -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') 
                       AS query FROM mysql.user" | \
                       mysql $@ | \
                       sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

logout/login or :

$ source .bash_aliases

$ mygrants --user=root --password=totoche

## Grants for backyp@% ##
GRANT SELECT, RELOAD, FILE, SUPER, REPLICATION SLAVE, EVENT, TRIGGER ON *.* TO 'backyp'@'%' IDENTIFIED BY PASSWORD 'TOTOCHE';

## Grants for root@127.0.0.1 ##
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD 'TOTOCHE' WITH GRANT OPTION;

## Grants for root@yourserver.zz ##
GRANT ALL PRIVILEGES ON *.* TO 'root'@'13h.be' IDENTIFIED BY PASSWORD 'TOTOCHE' WITH GRANT OPTION;

## Grants for debian-sys-maint@localhost ##
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD 'TOTOCHE' WITH GRANT OPTION;

## Grants for phpmyadmin@localhost ##
GRANT USAGE ON *.* TO 'phpmyadmin'@'localhost' IDENTIFIED BY PASSWORD 'TOTOCHE';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost';

## Grants for root@localhost ##
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'TOTOCHE' WITH GRANT OPTION;

## Grants for wikiuser@localhost ##

etc.

source : [3]

Réplication

C'est assez SM, il faut un master(source) et un slave(destination) ...

Dans les *exemples* de configuration les IPs seront :

master : 10.4.57.4
slave  : 192.168.51.43

Pré-requis

  • Savoir où se trouve la doc : [4]

Configuration coté Master

[mysqld]
log                     = /var/log/mysql/mysql.log
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog-do-db            = www_bricosoft_com
binlog-do-db            = www_mediawiki_fr
binlog_do_db            = www_super_base
binlog_ignore_db        = *

Pour les versions récentes Les logs d'erreurs sont dans le syslog (Daubian*).

Pour garder les utilisateurs et leurs droits rajoutez :

binlog-do-db            = mysql

Configuration coté Slave

[mysqld]
log                     = /var/log/mysql/mysql.log
log_error               = /var/log/mysql/mysql-error.log
server-id               = 2
relay-log               =slave-relay-bin
relay-log-index         =slave-relay-index
relay-log-purge         =1
max-relay-log-size      =300M
master-host             =10.4.57.4
master-port             =3306
master-user             =backup
master-password         =plop
master-connect-retry    =60
report-host             =192.168.51.43

Configuration sur le Master et le Slave

[mysqld]
bind-address            = 0.0.0.0

Master : utilisateur backup

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO backup@'%' IDENTIFIED BY 'plop';
GRANT FILE ON *.* TO backup@'%' IDENTIFIED BY 'plop';
GRANT SELECT ON *.* TO backup@'%' IDENTIFIED BY 'plop';
GRANT SUPER ON *.* TO backup@'%' IDENTIFIED BY 'plop';
GRANT RELOAD ON *.* TO backup@'%' IDENTIFIED BY 'plop';
FLUSH PRIVILEGES;

Relancer mysql sur les deux serveurs

master# /etc/init.d/mysql restart
slave# /etc/init.d/mysql restart

Pré créer les databases/tables sur le slave

C'est long et à mon avis c'est une étape superflue mais vous devez créer les databases et leurs tables avant la première réplication :

master$ mysqldump -u root -p www_bricosoft_com > www_bricosoft_com.sql
master$ scp -p 6587 www_bricosoft_com.sql.gz gedebore@slave:
...
CTRL+D
slave$ mysql -u root -p www_bricosoft_com < www_bricosoft_com.sql

zieuter les logs

Le log est très verbeux, attention aux yeux :

tail -f /var/log/mysql/mysql.log

Une connexion du slave :

100831 12:11:37	     54 Connect     backup@192.168.51.43 on 
		     54 Query       SELECT UNIX_TIMESTAMP()
		     54 Query       SHOW VARIABLES LIKE 'SERVER_ID'
		     54 Binlog Dump Log: 'mysql-bin.000003'  Pos: 98

Conversions

timestamp vers ISO

SELECT DATE( FROM_UNIXTIME( ts_mydate ) ) AS iso_mydate FROM mytable
2012-11-08

changement de l'emplacement des données

Alors ? Vous avez plus de place ?

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda1            2.4G  2.3G     0 100% /

/var/lib/mysql bouffe 4 Go ? Une seule solution changer l'endroit où se trouve vos données mysql.

Cela se passe en 5 étapes :

  1. créer votre nouvel emplacement de données
  • $ mkdir /srv/datas/mysql/datas
  • $ sudo chown mysql: /srv/datas/mysql/datas
  1. stopper mysqld (hé oui malheureusement)
    1. /etc/init.d/mysql stop
  1. copier tous les fichiers en conservant le propriétaire:groupe des répertoires/fichiers
  • $ cp -r * /var/lib/mysql/* /srv/datas/mysql/datas/
  1. changer datadir dans /etc/mysql/my.cnf
  • remplacer datadir= /var/lib/mysql par datadir = /srv/datas/mysql/datas
  1. relancer mysqld et prier
    1. /etc/init.d/mysql start

Problèmes et solutions

CONCAT(NULL,something) returns NULL WTF?

It's not a bug, it's a feature, try CONCAT_WS(',',something,NULL)

remettre autoincrement à 0

Remettre à zéro votre compteur de rateaux :

ALTER TABLE rateaux AUTO_INCREMENT=0;
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

empecher mysqld de se lancer au démarrage

Voir mysqld dans votre top alors que vous n'avez pas utilité ? Modifiez :

/etc/init/mysql.conf

Retrouvez la section :

start on (net-device-up
          and local-filesystems
          and runlevel [2345])

Enlevez le runlevel 2 :

start on (net-device-up
          and local-filesystems
          and runlevel [345])

perdu le mdp de root

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables --skip-syslog --skip-networking
...

autre terminal :

mysql -u root
UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
FLUSH PRIVILEGES;

ensuite :

mysqladmin shutdown
sudo service mysql start

Syntaxes pas banales

Fichier:Lca-prof-hammond-0-53-59.jpeg
Prof. Hammond : Je vais vous raconter une histoire pas banale... [1]

contrainte d'unicité sur plusieurs colonnes

Par exemple, il ne peut y avoir qu'un seul avion à une latitude, longitude et altitude données sinon boum ! Pensez-y si vous concevez un logiciel pour controleur aerien ;-)

ALTER TABLE avion_position ADD UNIQUE (latitude,longitude,altitude);

Et si t'insert un doublon, bim ! Une violation de contrainte d'intégrité (15 ans au pénal) :

Failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1337' for key 2

Si tu le catches pas, par flemmardise, incompétence ou manque de temps met un IGNORE :

INSERT IGNORE avion_position (avion_id, latitude, longitude, altitude) values ('AF_666','43.436904','5.214987','0')
INSERT IGNORE avion_position (avion_id, latitude, longitude, altitude) values ('AF_777','43.436904','5.214987','0')

Et voilà, tu peux avoir plusieurs avions au meme endroit sur le tarmac de l'aéroport de Marignane \o/

import de données depuis un fichier .csv

Au préalable, retirer la 1er ligne du csv (les noms de colonnes) :

load data local infile '/home/TOTO/donnees_fruits.csv' into table joli_table fruits terminated by ','
enclosed by '"'
lines terminated by '\n'
(id,couleur,forme,masse)