Mysql
Mysql pour les moules.
Backup
- La liste officielle des méthodes de backup est ici :
script pour le back up de toutes les databases
From : [2]
<source lang="bash"> !/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
</source>
will create the tree into :
/home/MY_USER_LOGIN/backup
<source lang="bash"> . | `-- 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
</source>
script pour le backup des utilisateurs et des droits
into .bash_aliases : <source lang="bash"> 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;}'
} </source>
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 :
- créer votre nouvel emplacement de données
- $ mkdir /srv/datas/mysql/datas
- $ sudo chown mysql: /srv/datas/mysql/datas
- stopper mysqld (hé oui malheureusement)
- /etc/init.d/mysql stop
- copier tous les fichiers en conservant le propriétaire:groupe des répertoires/fichiers
- $ cp -r * /var/lib/mysql/* /srv/datas/mysql/datas/
- changer datadir dans /etc/mysql/my.cnf
- remplacer datadir= /var/lib/mysql par datadir = /srv/datas/mysql/datas
- relancer mysqld et prier
- /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
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) : <source lang="sql"> 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) </source>