Mysql Serveur

Install

fedora core20

wget http://dev.mysql.com/get/mysql-community-release-fc20-5.noarch.rpm
sudo yum localinstall mysql-community-release-fc20-{version-number}.noarch.rpm
sudo yum install mysql-server

Old fedo

pour localiser les versions et les package
yum search mysql

Pour installer le client
yum install mysql

mysql est sous /usr/bin/mysql

Pour installer le serveur
yum install mysql-server

mysql est sous /usr/bin/mysql
Pour configurer les droits minimum lancer le script
mysql_install_db

pour démarrer le serveur

systemctl start mysqld.service
systemctl status mysqld.service

En cas de problèmes de type:
Can't find file: './mysql/plugin.frm' (errno:13)
chown -R mysql:mysql /varlib/mysql

Pour que le serveur redemarre tous seul (et verifier)
systemctl enable mysqld.service

fedorea <15

chkconfig mysqld on
chkconfig --list mysqld

Lancer mysql

On peux demarre mysql-server de differente facons

Daemon (mysqld)

Solution à préféré pour le démararge automatique et pour l'integréation au syteme
Lancement pour la session en cours
/etc/initd/mysqld start ou service mysqld start
noter: les commande satus et stop sont aussi dipos comem sur tous les services
Demarage automatique
Pour qu'un service comme mysql demarre automatiquement faire:
chkconfig mysqld on
pour verifier
chkconfig --list mysqld

Script mysqld_safe (pour la culture)

Lancement avec le system par default
mysqld_safe &
Le script est d'ailleur utilisé par mysqld
Un ps -edf revele 2 process
/bin/sh /usr/bin/mysqld_safe
/usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log -
le safe mysqld_safe est en charge de relancer le process mysqld en cas de probleme pour arreter le serveur lancer:
mysqladmin -u root shutdown

Verifier l'Install

Verification mysql

Info dtb
mysqladmin version

Voir les dtb dispos sur ce port (instance)
mysqlshow

Voir les tables dispos sur cette dtb
mysqlshow mysql

Vue des droits des dtb presente sur ce serveur
mysql -u root mysql -e "SELECT Host,Db,User FROM db"
mysql -u root mysql -e "SELECT * FROM user \G;";


le mysql -e ""
permet d'executer la commande mysql sur la base mysql depsui la ligne de commande

--skip-secure-auth : pour que un client sup>5 puisse parller a un serveur <5

Changer les Droits

Regarder les droit

Renvoie une erreur si la table est vide
mysql -u root -h127.0.0.1
select * from mysql.user \G;
select * from mysql.db \G;
select * from tables_priv \G;

Creation utilisateur

Creation d'utilisateure
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'tata';
la premiere ligne permet de recopier le hash code d'une autre base
la deuxiéme de rentrée un nouveau pass

Droit root

mysqladmin -u root -p'oldpassword' password newpassword
mysqladmin -u root password root

Restorer droit root

ps -edf | grep mysql | grep madatabase
Relever la comande pour lancer
/bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/mysql/myzenith.cnf --pid-file=/var/run/mysqld/mysqldzenith.pid --basedir=/usr
Arréter mysql
kill -9 (mysqld_safe +mysql)
Redémarrer sans le pass
/bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/mysql/myzenith.cnf --pid-file=/var/run/mysqld/mysqldzenith.pid --basedir=/usr --skip-grant-tables --skip-networking
Remetre un pass
mysql -uroot mysql; update user set password=PASSWORD("pass") where User='root';
rekill la base la relancer

Connection par socket

Si vote base n'est pas sur 3306 et que l'acces en avec - h hostname n'est pas possible (que le root ne sois pas accesible de l'extérieure)
mysql -uroot --socket=/var/lib/mysql/mysqlzenith.sock -e "show databases;"
update user set password=PASSWORD("pass") where User='root';

Droit par table

Ce connecter sur la base en local et changer les droit
mysql -u root -h127.0.0.1 mysql

Pour connaitre les droits actuel
select * from user \G;

Il existe 2 methode pour insérer des droits sur le serveur mysql
Plus une methode pour les insérer par dtb
Done tous les droit sur le serveur mysql au user lionel avec gadille pass
Le % indique de qu'elle que machine que ce sois

Methode 1:
mysql -u root -h127.0.0.1;
GRANT ALL PRIVILEGES ON *.* TO 'lionel'@'127.0.01' IDENTIFIED BY 'gadille' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'lionel'@'localhost' IDENTIFIED BY 'gadille' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'lionel'@'%' IDENTIFIED BY 'gadille' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;
Methode 2:
mysql -u root -h127.0.0.1;
INSERT INTO user VALUES ('localhost','lionel',PASSWORD('gadille'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('127.0.0.1','lionel',PASSWORD('gadille'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('%','lionel',PASSWORD('gadille'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
FLUSH PRIVILEGES;

mysqladmin

Stop du serveur
mysqladmin -u root shutdown

Satistic sur le serveur
mysqladmin status

Info de base

La conf par default ce trouve sous
/etc/my.cnf
Ce qui permet de voir que logs est sous
/var/log/mysqld.log
Le consulter en cas de probleme
On trouve des indique comme port occupé pas les droits sur dir ect
my.cnf exemple
[[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

Utf8

voir la config de la base
mysql -u root
show variables like 'char%';
modifier (apres arret de mysqld)
sudo vi /etc/my.cnf
rajouter dans la section mysqld
character-set-server=utf8
skip-character-set-client-handshake
la deuxiéme ligne force le mode utf8 pour le client les reponsse ect ...

Configurer et Demarrer/Arreter son Instance

Le but est de creer une instance sur le port par default 3306 mais avec c'est propre conf
Voici ma reference je veux avoir toute les info et donné regrouper sous /opt
tree /opt/mysql3306/
/opt/mysql3306/
├── conf
│   └── my.cnf
├── data
│   ├── ibdata1
│   ├── ib_logfile0
│   ├── ib_logfile1
│   └── mysql
................

Creer les tables system

!!Attention il faut copier la base mysql contenant les droits minimum sur le nouvelle emplacement
Prend le repertoire complet
mkdir -p /opt/mysql3306/data
cp -r /var/lib/mysql/* /opt/mysql3306/data
cd /opt chown -R mysql:mysql mysql3306

Creer un my.cnf

vi /opt/mysql3306/conf/my.cnf
Exemple de my.cnf
 
[mysqld]
default_character_set=utf8
datadir=/opt/mysql3306/data
tmpdir=/opt/mysql3306/tmp
socket=/opt/mysql3306/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/opt

[mysqld_safe]
log-error=/opt/mysql3306/log/mysqld.log
pid-file=/opt/mysql3306/mysqld.pid
creer le rep de log comme definit (log-error=/opt/mysql3306/log ...)

pour que le serveur sois connectable depuis l'extérieure rajouter dans mysqls le nom de la machine
bind-address = spokeniece-vm-costRevenue

Demarrage par safe_mysql

mysqld_safe --defaults-file=/opt/mysql3306/conf/my.cnf
mysqladmin -S/opt/mysql3306/mysql.sock shutdown
-S pour socket configurer dans le my.cnf
Si sa foire regarder le log (log-error)

Creation d'un mysqld perso

fedora 17 +

/etc/systemd/system/multi-user.target.wants
fedora<16
copier le current vers un nouveaux cp /etc/init.d/mysqld /etc/init.d/mysqld3306 puis modifier les ligne suivante pour utiliser votre nouveaux my.cnf
ref:
get_mysql_option mysqld datadir "/var/lib/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid"
mypidfile="$result"
exemple:
get_mysql_option "/opt/mysql3306/conf/my.cnf" datadir "/opt/mysql3306/data"
datadir="$result"
get_mysql_option "/opt/mysql3306/conf/my.cnf" socket "$datadir/mysql.sock"
socketfile="$result"
get_mysql_option "/opt/mysql3306/conf/my.cnf" log-error "/var/log/mysqld.log"
errlogfile="$result"
get_mysql_option "/opt/mysql3306/conf/my.cnf" pid-file "/var/run/mysqld/mysqld.pid"
mypidfile="$result"
Il va sans dire que le my.cnf pourrais pointer une instance 3307 ou autre
et que vous pourriez géré plusieurs instance séparément

Base MYSQL en ligne de cmd

Base Visualisation/Creation/connection/Drop de Base/Table

Connection par les sock
mysqlshow -S/opt/mysql3306/mysql.sock
mysql -ulionel -pgadille -hlocalhost -S /opt/mysql3306/mysql.sock
Normal
mysql -ulionel -pgadille -hlocalhost mabase
mysql -ulionel -pgadille -P38770 -hgadille.sourceforge.fr mabase
CREATE DATABASE test;
DROP DATABASE test;

USE test;
CREATE TABLE example(id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),nom VARCHAR(30),prenom VARCHAR(30),age INT);
Sshow create table example;
DROP TABLE example;

show tables;
desc tableExemple;

Creation

Pour connaître la requête sql qui a permit de créer la table groups faire:
show create table groupname;

Créer un table dont la colonne pere réference l'id pour créer un arbre
CREATE TABLE groupe_structurant_dossier (
    id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY (id)),nom varchar(255)
    ,pere_id INT, FOREIGN KEY (pere_id) REFERENCES groupe_structurant_dossier(id))
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Cette permet de faire la liaisons avec une autre table qui contient des noms de groupes et des informations sur les groupes CREATE TABLE groupe_structurant (     groupname varchar(255),pere_id INT,     FOREIGN KEY (groupname) REFERENCES groups(groupname),     FOREIGN KEY (pere_id) REFERENCES groupe_structurant_dossier(id) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

groups
groupnamedescription
groupe1contient les individus de type1
groupe2contient les individus de type2

groupe_struturant
groupnamepere_id
groupe11
groupe21

groupe_struturant_dossier
idnomid_pere
1rootnull
2dossier11

Select

Exemple 1

Group by: On groupes les réponses par nonAnnuaire mais on peut compter les groupements effectué par le count
select nomAnnuaire,count(nomAnnuaire) as cmpt from refens.IdentificationAnnuaire where nomAnnuaire like "%2012%" group by nomAnnuaire order by cmpt desc Sortie:
nomAnnuairecmpt
WMMEDPF_2012_S5_CM_G12
4MMMPA_2012_S3_CM_G12
5MMTDTRD_2012_S5_TD_G12
WMMCIF_2012_S5_CM_G11
5MMLRP_2012_S5_PROJET_G11
5MMECIS_2012_S5_PROJET_G41
3MMTINF_2012_S2_CM_G11

Exemple 2

select distinct target_id, count(distinct COUNTRY)
 from merchant_reviews group by target_id
 having count(distinct COUNTRY) > 1
 limit 10

Ayant un nombre de country distinct sup a 1
Le having s'aplique au groupe constitué
select distinct target_id,count(target_id),moderation_status,(moderation_status=2)
 from product_reviews where country="FR"
 group by target_id
 having count(distinct is_quick_rated)>1 and count(target_id)>3 and (moderation_status=2)=1
(moderation_status=2) si mod_stat=2 renvoie true sois le chiffre 1 (0 pour false)
on ne peux placer dans le where (moderation_status=2)=1 car suprimer tous les tuplesayant un moderation status diferent de 2
or le quick_rated et forcement a false sur mod_st=2

Exemple 3 Concat

Concat permet de cocaténer du texte entre le resulte de colonne
mysql -hlocalhost -uroot -proot zenith -e "select concat(\"update Utilisateur set login = '\", agln_id, \"' where username = '\", username, \"';\") from users where username != agln_id;" > Utilisateur.sql
update Enseignant set idAgalan = 'toto' where username = 'totos';
update Enseignant set idAgalan = 'tita' where username = 'tata';

Insertion Delete de data

Insert into example (id, nom, prenom, age) values (1,"toto","tata",33);
Insert into example (nom, prenom, age) values ("lionel" ,"gadille", 33);
delete from example;
update example set nom = "nom" where id = 1;
Comme le champ id est en auto_Increment le inser ne tient pas compta de la valeur passer
VALUES or VALUE do the same things

Cas des dates

CREATE TABLE `dateplayground` (
`dp_name` varchar(100) NOT NULL default '',
`dp_year` year(4) NOT NULL default '0000',
`dp_date` date NOT NULL default '0000-00-00',
`dp_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
`dp_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='4 different ways to store a date in MySQL';

INSERT INTO dateplayground (dp_name, dp_date)VALUES ('DATE: Manual Date', '2020-2-14');
INSERT INTO dateplayground (dp_name, dp_date) VALUE ('DATE: Auto CURDATE()', CURDATE());
INSERT INTO dateplayground (dp_name, dp_year) VALUES ('YEAR: Manual Year', '2011');
INSERT INTO dateplayground (dp_name, dp_year) VALUE ('YEAR: Auto CURDATE()', CURDATE() );
INSERT INTO dateplayground (dp_name, dp_datetime)VALUES ('DATETIME: Manual DateTime', '1776-7-4 04:13:54');
INSERT INTO dateplayground (dp_name, dp_datetime)VALUE ('DATETIME: Auto CURDATE()', CURDATE());
INSERT INTO dateplayground (dp_name, dp_datetime)VALUE ('DATETIME: Auto NOW()', NOW());

INSERT INTO dateplayground (dp_name, dp_timestamp)VALUE ('TIMESTAMP: Auto NOW()', NOW())
INSERT INTO dateplayground (dp_name, dp_timestamp)VALUES ('TIMESTAMP: Manual Timestamp', '1776-7-4 04:13:54')
INSERT INTO dateplayground (dp_name, dp_timestamp)VALUE ('TIMESTAMP: Auto CURTIME()', CURTIME())
Cause insert of null timestamp

Alter table

Le propos ici est de renommer une colonne pere_id en id (comme elle contient une clef étrangère on doit la supprimer et la recréer) )
ALTER TABLE groups_structurant DROP FOREIGN KEY groups_structurant_ibfk_2; alter table groups_structurant change pere_id id int; alter table groups_structurant ADD CONSTRAINT `groups_structurant_ibfk_2` FOREIGN KEY (`id`) REFERENCES `groups_structurant_dossier` (`id`)

Connection automatic/bash

Si on est en local trouver le mysql.sock de sont instance pour eviter de taper le user/pass
mysqlshow -S /var/lib/mysql/mysql.sock
mysql -S /var/lib/mysql/mysql.sock -e "show databes;"

idem mais en tapants le tous a la main
mysql -uuser -ppasword -hgadille.mysql.free.fr -e "show databases"
mysql -uuser -ppasword -hgadille.mysql.free.fr madtb -e "show tables;"

Perf admin

mysqladmin processlist -S/opt/mysql3306/mysql.sock
mysqladmin -S/opt/mysql3306/mysql.sock status
Lancer les serveur avec:
--log-slow-queries[=file_name]
Ou en modifiant le my.cnf

Dump/Restaur

Dump

ref
mysqldump [options] db_name table1 table2 > toto.mysql
mysqldump [options] --databases DB1 DB2 DB3... > toto.mysql
mysqldump [options] --all-databases > toto.mysql
Attention bien placer les options juste apres le mysqldump les user pasword ensuite
options
--no-data, -d ==> ne fournit que la structure pas les données
mysqldump -d -uUser -pPassword -hgadille.free.fr gadille > gadilledtb.sql
mysqldump -uUser -pPassword -hgadille.free.fr gadille list > gadillelist.sql
la premier ligne dump la database complète mais sans les donnée
la deuxième la table list dans la database

mysqldump -h database1 -P 38770 -u gadille -plionel -hgadille.free.fr --skip-opt -e --set-charset --ignore-table=database1.tableIgnore database1 --create-options >> dumpDatabase1.sql
--extended-insert, -e : Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.
--opt: This option is shorthand. It is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.
   enable by default --skip-opt: deseable opt
--set-charset : force le charset (utf8)
--create-options: récupère les informations de table comme les incrément

echo "update Utilisateur set mdpHash=MD5('toto');" >> dumpDatabase1.sql
Permet de remplacer les mots de passe

Restaur

Recréer manuellement si nécessaire la database vide
create database trafficAcquisition
mysql -uUser -pAutrePass trafficAcquisition < ta.sql