IMPORTANT: Per accedir als fitxer de subversion: http://acacha.org/svn (sense password). Poc a poc s'aniran migrant els enllaços. Encara però funciona el subversion de la farga però no se sap fins quan... (usuari: prova i la paraula de pas 123456)

MySQL

De SergiTurWiki
Share/Save/Bookmark
Dreceres ràpides: navegació, cerca
TODO: Plantilla amb el logo
http://dev.mysql.com/doc/refman/5.5/en/

Contingut

Conceptes

Història de MySQL

http://ca.wikipedia.org/wiki/MySQL

Arquitectura

L'arquitectura de MySQL és Client/Servidor.

El paquet client és mysql-client-5.1 o similar.

El paquet del servidor és mysql-server

NOTA: El servidor requereix del client, però no al revés. De fet sovint des de una màquina client utilitzareu eines client per a connectar-se i gestionar el servidor


INNODB vs MYISAM

TODO

Instal·lació

Utilitzant tasksel. LAMP

Instal·lació manual

Fitxers instal·lats

Podeu consultar els paquets instal·lats:

$ dpkg -l | grep mysql
ii  libdbd-mysql-perl                     4.016-1                                           Perl5 database interface to the MySQL  
database
ii  libmysqlclient16                      5.1.49-1ubuntu8.1                                 MySQL database client library
ii  libqt4-sql-mysql                      4:4.7.0-0ubuntu4.3                                Qt 4 MySQL database driver
ii  mysql-client-5.1                      5.1.49-1ubuntu8.1                                 MySQL database client binaries
ii  mysql-client-core-5.1                 5.1.49-1ubuntu8.1                                 MySQL database core client binaries
ii  mysql-common                          5.1.49-1ubuntu8.1                                 MySQL database common files, e.g.  /etc/mysql/my.cnf
ii  mysql-server                          5.1.49-1ubuntu8.1                                 MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.1                      5.1.49-1ubuntu8.1                                 MySQL database server binaries and  system database setup
ii  mysql-server-core-5.1                 5.1.49-1ubuntu8.1                                 MySQL database server binaries
ii  php5-mysql                            5.3.3-1ubuntu9.5                                  MySQL module for php5

Client mysql

$ dpkg -L mysql-client-5.1 
/.
/usr
/usr/bin
/usr/bin/innochecksum
/usr/bin/innotop
/usr/bin/myisam_ftdump
/usr/bin/mysqlaccess
/usr/bin/mysqladmin
/usr/bin/mysqlbug
/usr/bin/mysql_client_test
/usr/bin/mysql_client_test_embedded
/usr/bin/mysqldump
/usr/bin/mysqldumpslow
/usr/bin/mysql_find_rows
/usr/bin/mysql_fix_extensions
/usr/bin/vmysqlimport]]
/usr/bin/mysqlreport
/usr/bin/mysqlshow
/usr/bin/mysqlslap
/usr/bin/mysqltest_embedded
/usr/bin/mysql_waitpid
/usr/share
/usr/share/man
/usr/share/man/man1
/usr/share/man/man1/mysqlaccess.1.gz
/usr/share/man/man1/mysqladmin.1.gz
/usr/share/man/man1/mysqlbug.1.gz
/usr/share/man/man1/mysql_client_test.1.gz
/usr/share/man/man1/mysqldump.1.gz
/usr/share/man/man1/mysql_find_rows.1.gz
/usr/share/man/man1/mysql_fix_extensions.1.gz
/usr/share/man/man1/mysqlimport.1.gz
/usr/share/man/man1/mysqlman.1.gz
/usr/share/man/man1/mysqlreport.1.gz
/usr/share/man/man1/mysqlshow.1.gz
/usr/share/man/man1/mysqlslap.1.gz
/usr/share/man/man1/mysql_tableinfo.1.gz
/usr/share/man/man1/mysql_waitpid.1.gz
/usr/share/man/man1/innotop.1.gz
/usr/share/man/man1/myisam_ftdump.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man8
/usr/share/man/man8/mysqlmanager.8.gz
/usr/share/lintian
/usr/share/lintian/overrides
/usr/share/lintian/overrides/mysql-client-5.1
/usr/share/doc
/usr/share/doc/mysql-client-5.1
/usr/share/doc/mysql-client-5.1/EXCEPTIONS-CLIENT.gz
/usr/share/doc/mysql-client-5.1/README.gz
/usr/share/doc/mysql-client-5.1/README.Debian
/usr/share/doc/mysql-client-5.1/copyright
/usr/share/doc/mysql-client-5.1/changelog.innotop.gz
/usr/share/doc/mysql-client-5.1/changelog.Debian.gz
/usr/sbin
/usr/sbin/mysqlmanager
/usr/bin/mysqloptimize
/usr/bin/mysqlanalyze
/usr/bin/mysqlrepair
/usr/share/man/man1/mysql_client_test_embedded.1.gz
/usr/share/man/man1/mysqloptimize.1.gz
/usr/share/man/man1/mysqlanalyze.1.gz
/usr/share/man/man1/mysqlrepair.1.gz

Servidor mysql

$ dpkg -L mysql-server-5.1 
/.
/etc
/etc/apparmor.d
/etc/apparmor.d/force-complain
/etc/apparmor.d/usr.sbin.mysqld
/etc/init.d
/etc/logrotate.d
/etc/logrotate.d/mysql-server
/etc/mysql
/etc/mysql/conf.d
/etc/mysql/conf.d/mysqld_safe_syslog.cnf
/etc/mysql/debian-start
/etc/init
/etc/init/mysql.conf
/etc/logcheck
/etc/logcheck/ignore.d.workstation
/etc/logcheck/ignore.d.workstation/mysql-server-5_1
/etc/logcheck/ignore.d.server
/etc/logcheck/ignore.d.server/mysql-server-5_1
/etc/logcheck/ignore.d.paranoid
/etc/logcheck/ignore.d.paranoid/mysql-server-5_1
/usr
/usr/bin
/usr/bin/msql2mysql
/usr/bin/myisamchk
/usr/bin/myisamlog
/usr/bin/myisampack
/usr/bin/mysql_convert_table_format
/usr/bin/mysql_fix_privilege_tables
/usr/bin/mysql_secure_installation
/usr/bin/mysql_setpermission
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysql_zap
/usr/bin/mysqlbinlog
/usr/bin/mysqld_multi
/usr/bin/mysqld_safe
/usr/bin/mysqlhotcopy
/usr/bin/mysqltest
/usr/bin/perror
/usr/bin/replace
/usr/bin/resolve_stack_dump
/usr/bin/resolveip
/usr/share
/usr/share/mysql
/usr/share/mysql/debian-start.inc.sh
/usr/share/mysql/echo_stderr
/usr/share/mysql/errmsg.txt
/usr/share/mysql/mysqld_multi.server
/usr/share/mysql/mysql_fix_privilege_tables.sql
/usr/share/mysql/mysql_test_data_timezone.sql
/usr/share/mysql/config.huge.ini
/usr/share/mysql/config.medium.ini
/usr/share/mysql/config.small.ini
/usr/share/mysql/ndb-config-2-node.ini
/usr/share/apport
/usr/share/apport/package-hooks
/usr/share/apport/package-hooks/source_mysql-dfsg-5.1.py
/usr/share/doc
/usr/share/doc/mysql-server-5.1
/usr/share/doc/mysql-server-5.1/examples
/usr/share/doc/mysql-server-5.1/examples/my-large.cnf.gz
/usr/share/doc/mysql-server-5.1/examples/my-medium.cnf.gz
/usr/share/doc/mysql-server-5.1/examples/my-small.cnf
/usr/share/doc/mysql-server-5.1/examples/my-huge.cnf.gz
/usr/share/doc/mysql-server-5.1/examples/my-innodb-heavy-4G.cnf.gz
/usr/share/doc/mysql-server-5.1/mysqld.sym.gz
/usr/share/doc/mysql-server-5.1/README.Debian.gz
/usr/share/doc/mysql-server-5.1/changelog.Debian.gz
/usr/share/doc/mysql-server-5.1/copyright
/usr/share/doc/mysql-server-5.1/NEWS.Debian.gz
/usr/share/doc/mysql-server-5.1/EXCEPTIONS-CLIENT.gz
/usr/share/lintian
/usr/share/lintian/overrides
/usr/share/lintian/overrides/mysql-server-5.1
/usr/share/man
/usr/share/man/man1
/usr/share/man/man1/myisamlog.1.gz
/usr/share/man/man1/myisampack.1.gz
/usr/share/man/man1/mysqlbinlog.1.gz
/usr/share/man/man1/mysqld_multi.1.gz
/usr/share/man/man1/mysqld_safe.1.gz
/usr/share/man/man1/mysql_fix_privilege_tables.1.gz
/usr/share/man/man1/mysqlhotcopy.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz
/usr/share/man/man1/mysql_setpermission.1.gz
/usr/share/man/man1/mysqltest.1.gz
/usr/share/man/man1/mysql_zap.1.gz
/usr/share/man/man1/perror.1.gz
/usr/share/man/man1/replace.1.gz
/usr/share/man/man1/resolveip.1.gz
/usr/share/man/man1/resolve_stack_dump.1.gz 
/usr/share/man/man1/innochecksum.1.gz
/usr/share/man/man1/msql2mysql.1.gz
/usr/share/man/man1/myisamchk.1.gz
/usr/share/man/man1/mysql_convert_table_format.1.gz
/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
/usr/lib
/usr/lib/mysql
/usr/lib/mysql/plugin
/usr/lib/mysql/plugin/ha_innodb_plugin.so.0.0.0
/var
/var/lib
/var/lib/mysql-upgrade
/etc/init.d/mysql
/usr/share/man/man1/mysqltest_embedded.1.gz
/usr/lib/mysql/plugin/ha_innodb_plugin.so
/usr/lib/mysql/plugin/ha_innodb_plugin.so.0

Servidor

Configuració

Canviar la paraula de pas de root

Consulteu MySQL#Canviar_el_password_de_root

Bases de dades inicials

mysql

TODO

Control d'accés

Mostrar els permisos
$ sudo mysql -p
mysql> SHOW GRANTS;
Crear una base de dades i un usuari que hi tingui accés per línia d'ordres
$ sudo mysql -p
mysql> create database wikidb;
mysql> grant create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';
mysql> flush privileges;
mysql> exit
Crear un usuari amb tots els permisos
$ mysql -u root -p

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
Crear un usuaris amb tots els permisos a una base de dades
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
Canviar la password de l'usuari
SET PASSWORD FOR 'ebreinventory'@'localhost' = PASSWORD('cleartext password')

Còpies de seguretat

Consulteu:

Fitxers de log

Fitxers binaris de log

Consulteu:

Eines gràfiques

PhpMyAdmin

Mysql-navigator

mysql-admin

$ sudo apt-get install mysql-admin

Recursos:

També s'instal·len:

MySQL Workbench

$ sudo apt-get install libctemplate0 libzip1 python-pysqlite2 mysql-client python-crypto python-paramiko
$ sudo dpkg -i mysql-workbench-gpl-5.2.27-1ubu1004-i386.deb

Paquets

$ sudo apt-get install mysql-     tabular dos cops...
mysql-admin               mysql-cluster-client-5.1  mysql-mmm-agent           mysql-proxy               mysql-testsuite
mysql-client              mysql-cluster-server      mysql-mmm-common          mysql-query-browser
mysql-client-5.1          mysql-cluster-server-5.1  mysql-mmm-monitor         mysql-server              
mysql-client-core-5.1     mysql-common              mysql-mmm-tools           mysql-server-5.1          
mysql-cluster-client  mysql-gui-tools-common    mysql-navigator           mysql-server-core-5.1

Client

Comandes

TODO. Un apartat per comanda.

/usr/bin/innochecksum
/usr/bin/innotop
/usr/bin/myisam_ftdump
/usr/bin/mysqlaccess
/usr/bin/mysqladmin
/usr/bin/mysqlbug
/usr/bin/mysql_client_test
/usr/bin/mysql_client_test_embedded
/usr/bin/mysqldump
/usr/bin/mysqldumpslow
/usr/bin/mysql_find_rows
/usr/bin/mysql_fix_extensions
/usr/bin/vmysqlimport]]
/usr/bin/mysqlreport
/usr/bin/mysqlshow
/usr/bin/mysqlslap
/usr/bin/mysqltest_embedded
/usr/bin/mysql_waitpid

/usr/sbin/mysqlmanager
/usr/bin/mysqloptimize
/usr/bin/mysqlanalyze
/usr/bin/mysqlrepair

Exemples

Utilitzar variables en scripts SQL

$ mysql -uuser-ppw -e "set @1:='2007-06-28'; source run.sql"
$ mysql -e "set @firstname:='$1'; set @lastname='$2'; source run.sql;"

Recursos:


Bash script per carregar dades LOAD DATA

#!/bin/bash
if [ $# = 2 ]
then
   if [ $1 = " " -o $2 = " " ]
   then
       echo "Wrong or No arguments"
   else
       SOURCE_DIR=$HOME/Logdump
	LOG_DIR=$HOME/admin/log
	LOG_FILE=LoadSQL.log
       DATAFILE=$1
       TABLENAME=$2
       DST_FILE=$HOME/dump.ctl
       echo LOAD DATA > $DST_FILE
       echo INFILE "'"$SOURCE_DIR"/"$DATAFILE"'" >> $DST_FILE
       echo APPEND INTO TABLE $TABLENAME >> $DST_FILE
       echo FIELDS TERMINATED BY "','" OPTIONALLY ENCLOSED BY "'\"'" >>$DST_FILE
       echo TRAILING NULLCOLS >>$DST_FILE
       echo "(" >>$DST_FILE
       echo datestamp date '"Mon DD RRRR  HH:MIAM"',>>$DST_FILE
       echo request_file, >>$DST_FILE
       echo product_no, >>$DST_FILE
       echo card_no, >>$DST_FILE
       echo key_no, >>$DST_FILE
       echo message char"("1000")", >>$DST_FILE
       echo id sequence"("max,1")" >>$DST_FILE
       echo ")" >>$DST_FILE
       $HOME/admin/bin/.runme
       \rm dump.ctl
	echo "*****************************************************************" >> $LOG_DIR/$LOG_FILE  
	date '+DATE: %m/%d/%y TIME:%H:%M:%S' >> $LOG_DIR/$LOG_FILE
	echo $1 "Sucessfully Loaded into Database" >> $LOG_DIR/$LOG_FILE 
	echo "*****************************************************************" >> $LOG_DIR/$LOG_FILE 
   fi
else
   clear
   echo "*************************************************************"
   echo "*****       	<Your Comments/Company name>                          *******"
   echo "*****                             Load SQL              	                    *******"
   echo "*****         Version 1.00- 14-Jun-2004                                           *******"
   echo "*************************************************************"

echo "Usage: LoadSQL <data file name> " fi

Importacions/exportacions i migracions de dades

mysqldump

Vegeu mysqldump

mysqlhotcopy

mysqlhotcopy --user={username} --password={password} {database} {path}

O en una màquina remota:

mysqlhotcopy --method=scp --user={username} --password={password} {database} {ssh-username}@{ssh-server}:{path}

TODO: Programar importació inventari INS Ebre

  • Crear l'esquema SQL amb mdbtools
  • Base de dades comuna? Servidor web? Usuaris per grups
  • Millores:
  • incorporar Id autonùmèric a totes les taules
  • Espais i llocs. Gestió d'espais, els espais poden tenir espais pares. Crear superspais: Planta 1, edifici annex, etc...
  • Incorporar a tots els registres:
  • Dates automàtiques?
  • Codi usuari creació del objecte: no pot ser el codi de professor només: codi + Període Acadèmic + Nom complet + un número
  • Data creació
  • Codi usuari creació del objecte: no pot ser el codi de professor només: codi + Període Acadèmic + Nom complet + un número
  • Codi usuari modificació del objecte: no pot ser el codi de professor només: codi + Període Acadèmic + Nom complet + un número
  • Data última modificació
  • Codi usuari baixa del objecte: no pot ser el codi de professor només: codi + Període Acadèmic + Nom complet + un número
  • Data baixa lògica
  • Taula de municipis i províncies treure-la d'Internet

Taules extres:

  • Taula de porti la llista del pròxim codi a assignar segons família,material,tipus de material


Script de migració:

  • Fer un bash script amb mysql, mdbtools ... per fer l'importació de dades a partir de un mdb
  • Agregació de dades dels diferents MDB: per no repetir insercions cal definir quins camps han de ser únics. Impedir repetits
  • Caldrà executar les agregacions de forma que mirin si ja existeix aquell registre

Brainstorming

  • Gestió d'espais--> Passar a format Ldap, estructura jeràrquica/arbre
  • Informàtica: utilitzar OCS/GLPI? --> Migració automàtica a l'aplicació d'inventari del centre.
  • Aplicació final: Mòdul de gosa?
  • Inconvenients: Ldap, no base de dades SQL! Pot ser un mòdul de gosa amb SQL?
  • Aprofitar la cerca d'objectes, llistes
  • Faltaria incorporar informes a PDF (etiquetes i llistes d'inventari...)
  • Avantatge: ja tenim els usuaris. Incorporar un ID únic de professor que no depengui de l'any: DNI?
  • A sistemes podria ser una pestanya extra per afegir la info especifica de l'inventari de centre

"Províncias y Municipios"

Convert mdb to csv. mdbtools

$ sudo apt-get install mdbtools

Us proporcionarà les següents ordres:

$ dpkg -L mdbtools | grep bin
/usr/bin
/usr/bin/mdb-array
/usr/bin/mdb-export
/usr/bin/mdb-header
/usr/bin/mdb-hexdump
/usr/bin/mdb-parsecsv
/usr/bin/mdb-prop
/usr/bin/mdb-schema
/usr/bin/mdb-sql
/usr/bin/mdb-tables
/usr/bin/mdb-ver

Anem a veure alguns exemples, per exemple podeu utilitzar mdb-tables per mostrar les taules d'una base de dades MDB:

$ mdb-tables INVENTARI_ADMINISTRACIO.MDB 
Aula Familia Inventari Lloc Poblacions Procedencia Proveïdors Tipus Material Nomenclator 

Podeu veure la resta d'opcions al manual:

$ man mdb-tables

Per exemple veure també les taules de sistema:

$ mdb-tables -S INVENTARI_ADMINISTRACIO.MDB 
MSysObjects
MSysACEs
MSysQueries
MSysRelationships
...

O mostrar les taules una per línia:

$ mdb-tables -1 INVENTARI_ADMINISTRACIO.MDB 
Aula
Familia
Inventari
Lloc
Poblacions
Procedencia

O canviar el delimitador:

$ mdb-tables -d ";" INVENTARI_ADMINISTRACIO.MDB 
Aula;Familia;Inventari;Lloc;Poblacions;Procedencia;Proveïdors;Tipus;Material;Nomenclator;

Un cop sabem quines són les taules, podeu obtenir la taula en format CSV amb:

$ mdb-export INVENTARI_ADMINISTRACIO.MDB Aula

Si ho voleu guardar en un fitxer:

$ mdb-export INVENTARI_ADMINISTRACIO.MDB Aula > aula.csv

Les opcions són:

-H     Supress header row
-Q     Don't wrap text-like fields (text, memo, date) in quotes.  If not specified text fiels will be surrounded by " (double 
quote) characters.
-d     Specify an alternative column delimiter If no delimiter is specified, table names will be delimited by a , (comma) 
character.
-R     Specify a row delimiter
-I     INSERT statements (instead of CSV)
-D     Set the date format (see strftime(3) for details)
-S     Sanitize names (replace spaces etc. with underscore)
-q     Use to wrap text-like fields. Default is ".
-X     Use to escape quoted characters within a field.  Default is doubling.

Segurament un altre de les interessants és la que et crear INSERTs en formal SQL:

$ mdb-export -I INVENTARI_ADMINISTRACIO.MDB Aula  | head
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E1","3R ESO A")
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E3","3R ESO C")
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E4","1R ESO A")
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E2","3R ESO B")
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E5","1R ESO D")
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E6","1R ESO B")
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E7","1R ESO C")
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E8","2N ESO D")
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E9","2N ESO A")
INSERT INTO Aula (Codi_aula, Aula) VALUES ("E10","2N ESO B")

Podeu arribar a fer quelcom similar a:

$ mdb-export -I database.mdb table_name | sed -e 's/)$/)\;/' | mysql -u username -p database_name
NOTA: L'ordre sed és important per tal d'afegir el ; al final de línia

Podeu crear també esquemes SQL a partir de les taules mdb:

$ mdb-schema INVENTARI_ADMINISTRACIO.MDB | more
-------------------------------------------------------------
-- MDB Tools - A library for reading MS Access database files
-- Copyright (C) 2000-2004 Brian Bruns
-- Files in libmdb are licensed under LGPL and the utilities under
-- the GPL, see COPYING.LIB and COPYING files respectively.
-- Check out http://mdbtools.sourceforge.net
------------------------------------------------------------- 

DROP TABLE Aula;
CREATE TABLE Aula
 ( 
	Codi_aula			Text (100), 
	Aula			Text (100)
);
-- CREATE ANY INDEXES ...

DROP TABLE Familia;
CREATE TABLE Familia
 ( 
	Codi_familia			Text (100), 
	Familia			Text (100)
);
-- CREATE ANY INDEXES ...
...

També podeu utilitzar l'ordre mdb-sql, sense opcions entreu al mode interactiu:

$ mdb-sql INVENTARI_ADMINISTRACIO.MDB
1 => 

Està esperant a que li indiqueu les ordres a seguir, del manual:

$ man mdb-sql

Les opcions són:

  • connect to <database>: Si no indiqueu cap base de dades cal fer-ho amb connect
  • disconnect: Surt de la base de dades seleccionada
  • go: Similar a un commit
  • list tables: Mostra les taules d'una base de dades
  • describe table NOM_TAULA: Mostra les columnes d'una taula
  • quit: sortir

A part podeu fer consultes SQL de tipus SELECT.

SELECT INTO OUTFILE

IMPORTANT: L'usuari de base de dades ha de tenir el permís FILE
SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products

Utilitzar mysqldump per exportar a CSV

$ mysqldump phpmyadmin --user=root --tab="csv data" --fields-terminated-by="," --fields-enclosed-by="\"" --fields-escaped-by="\"\"" --lines-terminated-by="\r\n" --no-create-db --no-create-info -p -T /tmp


Resol·lució de problemes

mysqldump: Got error: 1: Can't create/write to file XXX (Errcode: 2) when executing 'SELECT INTO OUTFILE'. Apparmor

Pot ser que només es poden fer operacions FILE a una carpeta molt especifica del sistema. secure_file_priv (/tmp)


Pot ser apparmor

NOTA: En el pitjor dels casos podeu aturar temporalment apparmor: sudo /etc/init.dapparmor stop

Segurament és causa de apparmor. Consulteu si s'està forçant que MySQL apliqui el perfil de apparmor:

$ sudo aa-status | grep -B 5  mysqld
3 processes are in enforce mode :
  /usr/sbin/cupsd (1924) 
  /usr/sbin/mysqld (1253) 

Per sol·lucionar canvieu el perfil de apparmor

$ sudo joe /etc/apparmor.d/usr.sbin.mysqld

Afegiu una carpeta a la que mysql pugui escriure:

/usr/sbin/mysqld {
   ...
   /var/log/mysql/ r,
   /var/log/mysql/* rw,
   /var/run/mysqld/mysqld.pid w,
   /var/run/mysqld/mysqld.sock w,
    
   /home/usuari/mysql r,
   /home/usuari/mysql * rw,
}

I apliqueu els canvis:

$ sudo /etc/init.d/apparmor reload
NOTA: També caldrà que l'usuari mysql tingui permisos a la carpeta /home/usuari/mysql

LOAD DATA INTO

> mysql -vv -u webfaltes -p webfaltes < carrega_dades_basiques_tmp.sql
Enter password: 
--------------
LOAD DATA
INFILE '/tmp/dades_basiques/grup.txt'
INTO TABLE grup
--------------  

Query OK, 72 rows affected, 106 warnings
Records: 72  Deleted: 0  Skipped: 0  Warnings: 78 

--------------
SET foreign_key_checks = 0
--------------  

Query OK, 0 rows affected 

-------------- 
LOAD DATA
INFILE '/tmp/dades_basiques/alumne.txt'
INTO TABLE alumne
--------------  

Query OK, 618 rows affected, 1854 warnings
Records: 618  Deleted: 0  Skipped: 0  Warnings: 1854  

--------------
LOAD DATA 
INFILE '/tmp/dades_basiques/alumne_telefon.txt'
INTO TABLE alumne_telefon
--------------

Query OK, 618 rows affected
Records: 618  Deleted: 0  Skipped: 0  Warnings: 0

--------------
LOAD DATA 
INFILE '/tmp/dades_basiques/professor.txt'
INTO TABLE professor
(codi_professor, usuari, @variable, nom_professor, cognom1_professor, cognom2_professor)
SET password = md5(@variable)
--------------

Query OK, 138 rows affected, 1 warning
Records: 138  Deleted: 0  Skipped: 0  Warnings: 1 

--------------
LOAD DATA 
INFILE '/tmp/dades_basiques/assignatura.txt'
INTO TABLE assignatura
-------------- 

Query OK, 125 rows affected
Records: 125  Deleted: 0  Skipped: 0  Warnings: 0

Bye

La comanda wc pot ser útil per saber el número de línies dels fitxers originals i comparar que la importació s'ha fet correctament. Per exemple:

$ wc -l 
72 /tmp/dades_basiques/grup.txt

El fitxer originals és:

/* carreguem les dades b�siques */

/* TRUNCATE `grup`;  */
LOAD DATA
INFILE '/tmp/dades_basiques/grup.txt'
INTO TABLE grup;

/* TRUNCATE `alumne`;  */
SET foreign_key_checks = 0;
LOAD DATA
INFILE '/tmp/dades_basiques/alumne.txt'
INTO TABLE alumne;

/* TRUNCATE `alumne_telefon`;  */
LOAD DATA 
INFILE '/tmp/dades_basiques/alumne_telefon.txt'
INTO TABLE alumne_telefon; 
/* en el fitxer de les dades del professorat ha d'estar el password sense encriptar, i ja l'encriptem al carregar les dades */

/* TRUNCATE `professor`;  */
LOAD DATA 
INFILE '/tmp/dades_basiques/professor.txt'
INTO TABLE professor
(codi_professor, usuari, @variable, nom_professor, cognom1_professor, cognom2_professor)
SET password = md5(@variable);

/* TRUNCATE `assignatura`;  */     
LOAD DATA 
INFILE '/tmp/dades_basiques/assignatura.txt'
INTO TABLE assignatura;

mysqlimport

Seguretat

Esborrar l'historial de mysql

Esborrem l'historial del MySQL per tal d'evitar que les contrasenyes es puguin llegir a simple vista:

$ rm -f ~/.mysql_history
NOTA: Al menys els fitxers només són visibles per a l'usuari o els administradors de sistema

Fitxers de configuració a Debian

/etc/apparmor.d/usr.sbin.mysqld
/etc/init.d/mysql
/etc/logrotate.d/mysql-server
/etc/mysql/conf.d/mysqld_safe_syslog.cnf
/etc/mysql/debian-start
/etc/logcheck/ignore.d.workstation/mysql-server-5_1
/etc/logcheck/ignore.d.server/mysql-server-5_1
/etc/logcheck/ignore.d.paranoid/mysql-server-5_1


Moure bases de dades innodb entre servidors MYSQL

$ sudo tail -f /var/log/syslog 
Jan 18 12:08:40 negrell mysqld: 100118 12:08:40 [ERROR] Cannot find or open table mediawiki/mediawiki_objectcache from
Jan 18 12:08:40 negrell mysqld: the internal data dictionary of InnoDB though the .frm file for the
Jan 18 12:08:40 negrell mysqld: table exists. Maybe you have deleted and recreated InnoDB data
Jan 18 12:08:40 negrell mysqld: files but have forgotten to delete the corresponding .frm files
Jan 18 12:08:40 negrell mysqld: of InnoDB tables, or you have moved .frm files to another database?
Jan 18 12:08:40 negrell mysqld: or, the table contains indexes that this version of the engine
Jan 18 12:08:40 negrell mysqld: doesn't support.
Jan 18 12:08:40 negrell mysqld: See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
Jan 18 12:08:40 negrell mysqld: how you can resolve the problem.
Jan 18 12:08:40 negrell mysqld: 

Charset

http://www.phpwact.org/php/i18n/utf-8/mysql 

Per saber el charset que s'està utilitzant per defecte.

NOTA: Aquest és el charset per defecte de la base de dades! Cada taula al seu CREATE TABLE pot especificar un altre tipus de charset diferent
$ sudo mysql -p
> status;
--------------
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:		57316
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		
Using delimiter:	;
Server version:	 	5.0.75-0ubuntu10 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			4 days 15 hours 14 min 30 sec

Threads: 1  Questions: 1391896  Slow queries: 12  Opens: 31888  Flush tables: 1  Open tables: 64  Queries per second avg: 3.476

Convertir de latin1 a UTF-8

Convert latin1 to UTF-8 in MySQL

Tunning, performance, rendiment

Notes

Una cosa que si que he vist és que la MySQL té activat el suport per INNODB, però en canvi no hi ha cap taula que faci servir aquest motor. És millor desactivar-lo (directiva [skip-innodb] a l'arxiu de configuració.) Mantenir viu l'Innodb requereix memòria per als buffers i per als apuntadors dels logs binaris.

En la imatge facilitada, desactivar-ho redueix en 18Mb la càrrega de memòria RAM comparant una execució despres de fer un reset al servei, modificant el parametre i fent un altre reset. És molt poc pero a mesura que la my va executant querys es van omplint les memòries cau.

Al desactivar-ho també permet que s'esborrin els arxius ibdata1, ib_logfile0, ib_logfile1, que en total consumeixen 20Mb de disc dur (pecata minuta).

Si bé és correcte fer servir el paràmetre bind-address=127.0.0.1 per a evitar connexions no locals, és encara millor fer servir el skip-networking i treballar només a través de socket/pipes locals. Això fa que no es carregui la infraestructura de xarxa del servei i que tampoc s'esperin interrupcions per aquest cantó. Consumeix un pessic menys de ram però sobretot augmenta el rendiment de les connexions locals.

Si la maquina servidora té prou RAM es recomanable configurar fstab per a que la carpeta /tmp (o la que es vulgui que sigui la temporal de la My) sigui un mount de tipus tmpfs, aixo agilitzarà moltissim totes aquelles query's d'agrupació (GROUP BY) o d'ordenació (ORDER BY).

En la màquina virtual hi ha configurada una partició de SWAP d'1.5Gb, s'hauria d'estudia si aquesta swap és o no realment necessaria depenent de la RAM fisica del servidor. El swap no sempre és bo, i si bé malgrat la directiva de swaping de linux és infinitament més bona que windows no sempre és perfecte. Aquest punt s'hauria d'estudiar ja que de fet en un servidor ben dimensionat no s'hauria de fer servir.

En qualsevol cas per a un anàlisis més profund em farien falta els arxius de configuració amb que esta corrent el servei actualment si no son els que duu la imatge.


Límit de 100 connexions simultànies

Also, MySQL database server is limited by default to 100 simultaneous connections. So, if the Communication server handles more than 100 simultaneous requests for inventory, it will not be able to answer all. You can upgrade this value by updating the “max_connections” MySQL variable for mysqld daemon.

Here is sample recommdations found on MySQL web site, using server with different amount of physical memory.

Parameter

800 MB
	
1.7 GB
	
2.4 GB
Table_cache 	64M 	64M 	64M
Key_buffer 	128M 	256M 	256M
Sort_buffer_size 	2M 	2M 	2M
Read_buffer_size 	2M 	2M 	2M
Read_rnd_buffer_size 	4M 	4M 	4M
Myisam_sort_buffer_size 	64M 	64M 	64M
Query_cache_size 	128M 	128M 	128M
InnoDB_buffer_pool_size 	384M 	1024M 	1700M
InnoDB_additional_mem_pool_size 	20M 	20M 	20M
InnoDB_log_buffer_size 	8M 	8M 	8M

Refer to MySQL tuning guidelines on MySQL web site (http://dev.mysql.com/tech-resources/articles/) for more information. You may be also interested in [MySQL Tuning Primer] script

Mysql Tunning primer

MySQL Replication

TODO

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

For answers to some questions often asked by those who are new to MySQL Replication, see Section B.13, “MySQL 5.0 FAQ: Replication”.

The target uses for replication in MySQL include:

  • Scale-out solutions: spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.
  • Data security: because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
  • Analytics: live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.
  • Long-distance data distribution' - if a branch office would like to work with a copy of your main data, you can use replication to create a local copy of the data for their use without requiring permanent access to the master.
IMPORTANT: Replication in MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster (see Chapter 16, MySQL Cluster).

Master

Cal activar la connexió al servidor per TCP/IP (skip_networking a versions antigues de MySQL):

$ sudo joe /etc/mysql/my.cnf
[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
[...]

Escollim la base de dades:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exemple
server-id=1

Apliqueu els canvis:

$ sudo service mysqld restart

Creeu un usuari amb privilegis amb:

$ sudo mysql -p
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

L'última comanda mostrarà quelcom similar a:

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183      | exampledb    |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

Apunteu aquesta informació! Sortiu de la consola SQL:

> quit;

Obteniu una copia de la base de dades:

$ mysqldump -u root -p<password> --opt exampledb > exampledb.sql 

Finalment:

$ mysql -u root -p
Enter password:
> UNLOCK TABLES;
> quit;

Slave

Editeu la configuració de l'esclau:

$ sudo joe /etc/mysql/my.cnf

[...]
server-id=2
master-connect-retry=60
replicate-do-db=exampledb
[...]

El server-id ha de ser únic si teniu múltiples repliques.

$ sudo /etc/init.d/mysql restart

Ara creem la base de dades a partir de la copia de la master:

$ mysql -u root -p
> CREATE DATABASE exampledb;
> quit;

I:

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave 
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

Connecteu un altre cop:

$ mysql -u root -p

I executeu:

> CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=19467;
IMPORTANT: Canvieu el valors segons el que vau obtenir amb l'ordre SHOW MASTER STATUS al master

On:

  • MASTER_HOST: és la IP del master
  • MASTER_USER: és l'usuari que té permisos per fer repliques.
  • MASTER_PASSWORD:la paraula de pas de l'usuari anterior.
  • MASTER_LOG_FILE: és el nom del fitxer que vau obtenir amb l'ordre SHOW MASTER STATUS al master
  • MASTER_LOG_POS: és la posició que vau obtenir amb l'ordre SHOW MASTER STATUS al master

Ara activeu l'esclau:

START SLAVE;

comproveu l'estat:

SHOW SLAVE STATUS \G
Un exemple:
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.100
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 19467
               Relay_Log_File: mysqld-relay-bin.000002
               Relay_Log_Pos: 251
       Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
             Replicate_Do_DB: exampledb
         Replicate_Ignore_DB:
          Replicate_Do_Table:
      Replicate_Ignore_Table:
     Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                  Last_Errno: 0
                  Last_Error:
                Skip_Counter: 0
         Exec_Master_Log_Pos: 19467
             Relay_Log_Space: 407
             Until_Condition: None
              Until_Log_File:
               Until_Log_Pos: 0
          Master_SSL_Allowed: No
          Master_SSL_CA_File:
          Master_SSL_CA_Path:
             Master_SSL_Cert:
           Master_SSL_Cipher:
              Master_SSL_Key:
       Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
               Last_IO_Errno: 0
               Last_IO_Error:
              Last_SQL_Errno: 0
              Last_SQL_Error:
1 row in set (0.00 sec)

És important que tingueu els valors:

Slave_IO_Running i Slave_SQL_Running a Yes 

Si teniu errors mireu el fitxer de log /var/log/syslog.

mysql> quit;


Ja està. Comproveu que funciona.

MySQL Cluster

Resolució de problemes. Troubleshooting

Reparar bases de dades

Reparar base de dades INNODB

Es pot mirar de solucionar posant l'opció:

innodb_force_recovery = 1

Dins de l'apartat mysqld i reiniciant mysql

$ sudo /etc/init.d/mysql restart

Un cop solucionat el problema cal treure la línia innodb_force_recovery.

Esborrar paraules de pas que queden als registres de MySQL

Consulteu #Esborrar l'historial de mysql

Canviar el password de root

Si mai s'ha establert una paraula de pas per a l'usuari root:

$ mysqladmin -u root -p password PARAULA_DE_PAS

Sinó (quan volem canviar una paraula de pas existent) cal fer:

$ mysqladmin -u root -p'oldpassword' password newpass

Per exemple, si la paraula de pas antiga és 1234 i la nova és paraula aleshores:

$ mysqladmin -u root -p'1234' password 'paraula'

Recuperar la paraula de pas de root

A les últimes versions del paquet MySQL per a Debian/Ubuntu podeu utilitzar:

$ sudo dpkg-reconfigure mysql-server-5.1

I us tornarà a preguntar quina paraula de pas voleu per a l'usuari de root de MySQL.

Consulteu també un altre mètode:

o

/etc/init.d/mysql stop    (stop mysql)
nano mysql-init    (create a new file and enter this line:)
  SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
mysqld_safe --init-file=~/mysql-init &
/etc/init.d/mysql start   (start mysql)
rm mysql-init

Recursos:

Recuperar la paraula de pas de root amb l'usuari debian-sys-maint

Podeu trobar l'usuari debian-sys-maint i la paraula de pas a:

$ sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = 9SX1IdXCjLUgM67k
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 39SX1IewCjLTgM67k
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr


skip-grant-tables. Executar MYSQLD temporalment sense taules de control d'accés

Atureu el servidor:

$ /etc/init.d/mysql stop

I l'executeu amb l'opció --skip-grant-tables:

/bin/sh /usr/bin/mysqld_safe --mysqld=mysqld --user=mysql --pid-file=/var/lib/mysql/mysqld.pid --socket=/var/lib/mysql/mysql.sock --skip-grant-tables --skip-
networking --datadir=/var/lib/mysql

També podeu cercar al fitxer:

/etc/init.d/mysql

On s'executa mysql_safe i afegiu el paràmetre temporalment. Un cop fet això executeu el servidor amb:

$ /etc/init.d/mysql stop
IMPORTANT: While in --skip-grant-tables mode, anyone can log into the server and do as they please. When starting with this flag, you may also wish to use --skip-
networking.
IMPORTANT: Per seguretat cal tornar a deixar-ho tot com estava abans de les modificacions fetes en aquest apartat.
NOTA: Amb skip-grant-tables no es poden crear usuaris.

Vegeu també

== Enllaços externs ==
OpenFPnet
IES Nicolau Copèrnic